Monday, September 22, 2014

MySQL Replication setup in 10 minutes

  ________ ________
| | | |
| | | |
| master|>----->----->----->| slave |
| | | |
|_______| |_______|
     


1. configure master's /etc/mysql/my.cnf and add following line in [mysqld] section:
server-id=1 binlog-format = mixed log-bin=mysql-bin datadir=/var/lib/mysql innodb_flush_log_at_trx_commit=1 sync_binlog=1
2. Restart mysqld server and create replication user that your slave will use to authenticate and connect to master:


CREATE USER replicant@<slave-server-ip>;
GRANT REPLICATION SLAVE ON *.* TO replicant@<<slave-server-ip>> IDENTIFIED BY 'choose-secret-password';


3. Create backup file with the binlog position. It will affect performance of your existing database server, but won't lock your tables:


mysqldump -u root -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --events --master-data=2 -A > ~/mysql_full_dump.sql


4. Now, find out MASTER_LOG_FILE and MASTER_LOG_POS values from "mysql_full_dump.sql" using following command, we will use them later:


head dump.sql -n80 | grep "MASTER_LOG_POS"


Above command output look like following:


-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;


5. scp mysql_full_dump.sql user@slave-server-ip:~/

6. Edit slave server's /etc/my.cnf file to add following lines:


server-id = 101 binlog-format = mixed log_bin = mysql-bin relay-log = mysql-relay-bin log-slave-updates = 1 read-only = 1


7. Restart the mysql slave daemon and then import SQL dump: 


mysql -u root -p < ~/mysql_full_dump.sql


8. Log into mysql console on slave and run following command to setup replication:


mysql> CHANGE MASTER TO MASTER_HOST='master-server-ip',MASTER_USER='replicant',MASTER_PASSWORD=''slave-server-password', MASTER_LOG_FILE='Above head command output', MASTER_LOG_POS='Above head command output';


9. Run command command to start slave:


mysql> START SLAVE;


10. Check replication progress:


mysql> SHOW SLAVE STATUS \G


If all is well then you will see "Waiting for master to send event"

Notes: My mistake if you accidentally change any row or data on slave server, then revert your change and run following command:


mysql> STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;START SLAVE;