Rebuild MySQL Master Master Replication after ERROR: Got fatal error 1236 from master when reading data from binary log

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s