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;