MySQL 5.6 quick and dirty replication
Some assumptions:
master> = mysql commands on master
slave> = mysql commands on slave
root@master = bash commands on master
root@slave = bash commands on slave
/var/lib/mysql/master01-bin.* = name of the replication logs, depending on your config
master> STOP MASTER
slave> STOP SLAVE
root@master: service mysqld stop
root@slave: service mysqld stop
Take a snapshot of servers if possible
root@master: service mysqld start
master> RESET MASTER (this deletes binary files under /var/lib/mysql/master01-bin.XXXXXX and reset the position, be careful this might lock the DB for a couple of seconds or minutes)
root@master: service mysqld stop
Rsync /var/lib/mysql from master to slave
On the slave remove:
- /var/lib/mysql/auto.cnf (contains node UUID, file will be regenerated upon mysql start)
- /var/lib/mysql/master01-bin.*
- /var/lib/mysql/mysqld-relay.*
Start both master and slave
master> START MASTER
slave> CHANGE MASTER TO MASTER_HOST=“172.24.252.137”, MASTER_USER=“replication_account”, MASTER_PASSWORD=“replication_password”, MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;
slave> START SLAVE
slave> SHOW SLAVE STATUS\G
Now you should see this:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
No error should be reported.
*_Gtid_Set info should match closely between master and slave:
mysql> show master status\G
*************************** 1. row ***************************
File: master01-bin.000003
Position: 41308364
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 0fa3a859-0c73-11e6-a40a-005056bb5198:1-100643
1 row in set (0,00 sec)
```
mysql> show slave status\G
[…]
Retrieved_Gtid_Set: 0fa3a859-0c73-11e6-a40a-005056bb5198:1-100671
Executed_Gtid_Set: 0fa3a859-0c73-11e6-a40a-005056bb5198:1-100671
[…]