Broken mysql replication on slave server (error 1236)

Question

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 [email protected]:/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'

Answer 1

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;

Answer 2

I needed to fix an existing slave which had gotten so far out of sync that a simple restart was not possible "1236 'Could not find first log file name in binary log index file'" type problem. After reading the MySQL documentation, I've simplified the process for this type of issue. Again, this is assuming existing configuration was working before, now just restore the slave's copy of the DB, any old mysql_bin.xxxxx files copied over, and get sync started again.

Create a backup of the master (and all databases in my case) :

    master# mysqldump --master-data --all-databases > mydump.db

Then on the slave:

1) stop mysql, and restart it without slave running:

    slave# service mysqld stop
    slave# service mysqld start --skip-slave-start

2) make certain slave is stopped:

    slave# mysql -u root -p 
    mysql> stop slave;
    mysql> exit;

3) Restore configuration from master onto slave, with master status magic already baked in:

    slave# MySQL < mydump.db
    slave# mysql
    mysql> START SLAVE;

4) Confirm sync has at least begun (give it 30-90 seconds):

    mysql> SHOW SLAVE STATUS;

Answer 3

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.

Answer 4

If you need to do this with minimal downtime and have 100% confidence that you have consistent slave here are step by step direction how to rebuild the slave using Linux LVM.

Preparation

Set innodb max dirty pages percent to zero on the master. This will force MySQL to write all the pages to the disk which will significantly speed up the restart.

set global innodb_max_dirty_pages_pct = 0;

To monitor the number of dirty pages run the command

mysqladmin ext -i10 | grep dirty

Once the number stop decreasing you have reach the point to continue. Next reset the master to clear the old bin logs / relay logs:

RESET MASTER;

Rebuilding the slave using LVM

Execute lvdisplay to get LV Path

lvdisplay

Output will look like this

--- Logical volume ---
LV Path                /dev/vg_mysql/lv_data
LV Name                lv_data
VG Name                vg_mysql

Shutdown the master database with command

service mysql stop

Next take a snaphot, mysql_snapshot will be the new logical volume name.

lvcreate --size 10G --snapshot --name mysql_snapshot /dev/vg_mysql/lv_data

Start master again with command

service mysql start

Restore dirty pages setting to the default

set global innodb_max_dirty_pages_pct = 75;

Run lvdisplay again to make sure the snapshot is there and visible

lvdisplay

Output:

--- Logical volume ---
LV Path                /dev/vg_mysql/mysql_snapshot
LV Name                mysql_snapshot
VG Name                vg_mysql

Mount the snapshot

mkdir /mnt/mysql_snapshot
mount /dev/vg_mysql/mysql_snapshot /mnt/mysql_snapshot

If you have an existing MySQL slave running you need to stop it

service mysql stop

Next you need to clear MySQL data folder

cd /var/lib/mysql
rm -fr *

Back to master. Now rsync the snapshot to the MySQL slave

rsync --progress -harz /mnt/mysql_snapshot/ targethostname:/var/lib/mysql/

Once rsync has completed you may unmount and remove the snapshot

umount /mnt/mysql_snapshot
lvremove -f /dev/vg_mysql/mysql_snapshot

Create replication user on the master if the old replication user doesn't exist or password is unknown

GRANT REPLICATION SLAVE on *.* to 'replication'@'[SLAVE IP]' identified by 'YourPass';

Verify that /var/lib/mysql data files are owned by the mysql user, if so you can omit the following command:

chown -R mysql:mysql /var/lib/mysql

Next record the binlog position

ls -laF | grep mysql-bin

You will see something like

..
-rw-rw----     1 mysql mysql  1073750329 Aug 28 03:33 mysql-bin.000017
-rw-rw----     1 mysql mysql  1073741932 Aug 28 08:32 mysql-bin.000018
-rw-rw----     1 mysql mysql   963333441 Aug 28 15:37 mysql-bin.000019
-rw-rw----     1 mysql mysql    65657162 Aug 28 16:44 mysql-bin.000020

Here the master log file is the highest file number in sequence and bin log position is the file size. Record these values:

master_log_file=mysql-bin.000020
master_log_post=65657162

Next start the slave MySQL

service mysql start

Execute change master command on the slave by executing the following:

CHANGE MASTER TO 
master_host="10.0.0.12", 
master_user="replication", 
master_password="YourPass", 
master_log_file="mysql-bin.000020", 
master_log_pos=65657162; 

Finally start the slave

SLAVE START;

Check slave status:

SHOW SLAVE STATUS\G

Make sure Slave IO is running and there are no connection errors. Good luck!

My blog has some additional info on this subject but the core story is the same!

http://www.juhavehnia.com/2015/05/rebuilding-mysql-slave-using-linux-lvm.html

Answer 5

I encountered the same error. In my case the cause of the problem was white space at the end of the file name. So, in case you did some changes in frame of replication you should check possible white characters (in master_log_file name);

Reproduce the problem:

 # white space after the name of file.
 mysql>change master to master_log_file='archive.000006 ';
 # how to check
 cat /var/lib/mysql/master.info | sed 's/^/START--/g' | sed 's/$/--END/g'
 START--archive.000006 --END

Solution:

 mysql>change master to master_log_file='archive.000006';
 cat /var/lib/mysql/master.info | sed 's/^/START--/g' | sed 's/$/--END/g'
 START--archive.000006--END