Tuesday, October 11, 2011

MySQL Replication

​Setting up MySQL replication is an easy process to do. First you need to edit your my.cnf files on your two servers and add the following:
Server 1
    server-id=1
    auto_increment_offset=1
    auto_increment_increment=3
    log-bin=mysql_log
Server 2
    server-id=2
    auto_increment_offset=1
    auto_increment_increment=3
    log-bin=mysql_log
Make sure that the server id’s in the my.cnf file are unique for each server and the auto_increment_increment is n+1 more than the total amount of servers in your environment. This way your slave servers will update correctly. Once that is complete, restart the MySQL Service
    service mysqld restart
To configure your slave user, log into the master and type the following:
    mysql> create user slaveuser@’slavehost.example.com’ identified by ‘somepassword’;
    mysql> grant replication slave on *.* to slaveuser@’slavehost.example.com’
    mysql> flush privileges;
The next step is to dump the database from your primary server and import it on the slave server. To dump the database:
    mysqldump -u root -p –lock-tables database > database.sql
Import the database on the slave server:
    mysql -u root -p database < database.sql
We need to get the log file and position information from the master server in order to sync it with the slave. First lock the tables so no changes can be made and then show the status.
    mysql> FLUSH TABLES WITH READ LOCK;
    mysql> show master status;
    +——————+————–+———————-+—————————+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +——————+————–+———————-+—————————+
    | mysql_log.000006 | 156005305 | | |
    +——————+————–+———————-+—————————+
    1 row in set (0.00 sec)
Configure the slave server to attach to the master with the correct credentials. Note the MASTER_LOG_FILE and the MASTER_LOG_POS information need to be identical as the master.
    mysql> CHANGE MASTER TO
    -> MASTER_HOST=’masterhost.example.com’,
    -> MASTER_USER=’slaveuser’,
    -> MASTER_PASSWORD=’somepassword’,
    -> MASTER_LOG_FILE=’mysql-bin.000006′,
    -> MASTER_LOG_POS=156005305;
Next start the replication
    mysql> START SLAVE;
Unlock the tables on the master
    mysql> UNLOCK TABLES;
Check to make sure that it is running properly
    mysql> show slave status\G;
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: masterhost.example.com
    Master_User: slaveuser
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql_log.000006
    Read_Master_Log_Pos: 156005305
    Relay_Log_File: mysqld-relay-bin.000146
    Relay_Log_Pos: 107097880
    Relay_Master_Log_File: mysql_log.000006
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 155994592
    Relay_Log_Space: 107097880
    Until_Condition: None
    Until_Log_File:
    Until_Log_Pos: 0
    Master_SSL_Allowed: No
    Master_SSL_CA_File:
    Master_SSL_CA_Path:
    Master_SSL_Cert:
    Master_SSL_Cipher:
    Master_SSL_Key:
    Seconds_Behind_Master: 0
    1 row in set (0.00 sec)
To set up a master-master replication, repeat the process on the second MySQL server.
That’s it!