Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My question is: how to fix broken mysql replication on slave server (error 1236)?

server 1 (192.168.1.105) server 2 (192.168.1.106)

I ran these steps to re-sync:

At the master - server 1 (192.168.1.105):

mysql -u root -p
  1. RESET MASTER;
  2. FLUSH TABLES WITH READ LOCK;
  3. SHOW MASTER STATUS;
  4. Note > File: mysql-bin.000001 and Position: 107
  5. mysqldump -uroot -p --events --ignore-table=mysql.events --all-databases > /var/backups/mysqldump.sql
  6. UNLOCK TABLES;
  7. Copy mysql dump file to server 2
    (scp -p /var/backups/mysqldump.sql root@192.168.1.106:/tmp)

At the slave server 1 (192.168.1.106):

  1. STOP SLAVE;
  2. mysql -uroot -p < /tmp/mysqldump.sql
  3. RESET SLAVE;
  4. CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword';
  5. CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
  6. START SLAVE;

I still got this error on server 1

Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

share|improve this question

migrated from stackoverflow.com Jul 12 '13 at 9:21

This question came from our site for professional and enthusiast programmers.

add comment

3 Answers

I believe the log file and position need to be specified with the host.

Try:

CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
share|improve this answer
    
This worked! Thank You myninjaname! –  onastvar Jul 14 '13 at 5:39
    
It worked, but now I see error 1050: Last_SQL_Error: Error 'Table 'attempts_login' already exists' on query. Default database: 'dbispconfig2'. Query: 'CREATE TABLE attempts_login ( ip varchar(39) NOT NULL, times int(11) DEFAULT NULL, login_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8' How to fix this? –  onastvar Jul 15 '13 at 6:15
    
Your slave and master server's databases are out of sync. I'd start by creating another fresh copy on the slave server, basically: Lock and mysqldump the databases (on the master). Write down the MASTER_LOG_POS, then unlock the database. Restore the mysqldump file on the slave. Then perform the CHANGE MASTER TO with the new MASTER_LOG_POS. –  myninjaname Jul 23 '13 at 3:33
add comment

If restoring the database on the slave takes too long, the log file mysql-bin.000001 may be purged from the master by the time you START SLAVE. When you get the error on the slave, run SHOW BINARY LOGS on the master and see if mysql-bin.000001 is gone.

You should also look at the setting for expire_logs_days to make sure the binlog files aren't being expired too quickly.

Another thing you could do to compensate is the following, on the slave, as soon as you UNLOCK TABLES on the master:

  1. STOP SLAVE;
  2. RESET SLAVE;
  3. CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword';
  4. CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
  5. START SLAVE IO_THREAD;
  6. copy mysql dump from master
  7. mysql -uroot -p < /tmp/mysqldump.sql
  8. START SLAVE SQL_THREAD;

That should start the downloading of binary logs while you're doing the restore, so you don't miss out on any.

share|improve this answer
    
I tried these steps, same issue, appreciate your time. Thanks Bill! –  onastvar Jul 14 '13 at 5:40
add comment

Fix For GTID base replication (5.6) ERROR: 'Got fatal error 1236

mysql 5.6 GTID replication 'Got fatal error 1236 from master when reading data from binary log'

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.