Rebuild MySQL Master Master Replication after receiving the error below. This procedure can also be used to setup Mysql Master / Master replication.
Possible error in the mysql error log:
Got fatal error 1236 from master when reading data from binary log: 'Found old binary log without GTIDs while looking for the oldest binary log that contains any GTID that is not in the given gtid set', Error_code: 1236
This post is about the following configuration. A pair of MySQL servers running CENTOS 6.5 and MySQL 5.6.
The MySQL servers are running salves to one another Multi-Master. The SQL clients write to a Virtual IP which is configured to float between the MASTER and SLAVE in the event of a failover.
Reference Architecture:
MYSQLSERVER01 – Primary Master
MYSQLSERVER02 – Backup Master
Keepalived – Virtual IP
STEP 1: ON MYSQLSERVER02
Log into MySQL Workbench and execute the following commands
STOP SLAVE; RESET SLAVE; RESET MASTER;
STEP 2: ON MYSQLSERVER01
Log into MySQL Workbench and execute the following commands
STOP SLAVE; RESET SLAVE; RESET MASTER;
STEP 3: ON MYSQLSERVER01 take a backup with mysqldump from the master:
SSH into the server and execute the following
mysqldump --all-databases --single-transaction --triggers --routines --events --user=root -p > /tmp/dump.sql
STEP 4: ON MYSQLSERVER01 transfer the mysqldump backup file form MYSQLSERVER01 to MYSQLSERVER02
SSH into the server and execute the following
scp /tmp/dump.sql root@MYSQLSERVER02:/tmp/dump.sql
STEP 5: ON MYSQLSERVER01 load the mysqldump file
SSH into the server and execute the following
mysql -u root -p
STEP 6: ON MYSQLSERVER02
CHANGE MASTER TO MASTER_HOST='MYSQLSERVER01.lab.net', MASTER_USER='repl', MASTER_PASSWORD='replPassword', MASTER_AUTO_POSITION = 1;
STEP 7. ON MYSQLSERVER02
Verify the Slave_IO_Running and Slave_SQL_Running both have a status of YES
SHOW SLAVE STATUS; start slave; SHOW SLAVE STATUS;
STEP 8. ON MYSQLSERVER01
Log into MySQL Workbench and execute the following commands
CHANGE MASTER TO MASTER_HOST='MYSQLSERVER02.lab.net', MASTER_USER='repl', MASTER_PASSWORD='replPassword', MASTER_AUTO_POSITION = 1;
STEP 9. ON MYSQLSERVER01 start the slave and check the status of the slave operations
Log into MySQL Workbench and execute the following commands
Verify the Slave_IO_Running and Slave_SQL_Running both have a status of YES
SHOW SLAVE STATUS; start slave; SHOW SLAVE STATUS;
STEP 10. Additional testing can be done to confirm that the bi directional replication is working by inserting a record into the testdb.
Log into MySQL Workbench and execute the following commands
Perform insert test on MYSQLSERVER01, verify that the record has replicated to MYSQLSERVER02.