MySQL Replication Troubleshooting

Question

Here's what I did. On the master, change /etc/my.cnf:

[mysqld]
server-id=1
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Save and restart mysql, then log in.

>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123';
Mysql> flush table with read lock;//  Start copy data master (myisam) chuyển sang slave .
Mysql> show master status;

On the slave, change /etc/my.cnf.

[mysqld]
server-id=2
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Save and restart mysql. Create a database on the slave (Snapshop master). Move data from master to slave.

Mysql> stop slave;
Mysql>CHANGE MASTER TO
MASTER_HOST='server2',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='xxxx',
MASTER_LOG_POS=xxx;

On the master:

Mysql> unlock tables;

On the slave:

Mysql> start slave;
Mysql> show slave status /G

My problem is: I forget answer command belows.

Mysql> show master status;

when I start the slave. The error is

236 | Got fatal error 1236 from mast er when reading data from binary log: 'Could not find first log file name in binary log index file' |

What does the error mean, and how can I solve it?

Answer 1

Something tells me that when you run SHOW SLAVE STATUS\G you get

Slave_IO_Running=No
Slave_SQL_Running=Yes

It is a connectivity issue back to the master. This is a not connectivty due to the network but with the binary logging mechanism of the master. This is definitely the case because, the error message (1236) is complaining about the first entry of the master's list of binary logs.

When you had setup the line

[mysqld]
log-bin=mysql-bin

This would naturally create a file called mysql-bin.index, a files that would contain the list of all binary logs that starts with mysql-bin.

Just run this on the master:

SHOW BINARY LOGS;

and you will see the list of binary logs on the master. It shold look something like this:

+------------------+------------+
| mysql-bin.000001 | 1073741824 |
| mysql-bin.000002 | 1073741824 |
| mysql-bin.000003 | 1073741824 |
    .
    .
    .

There are a few scenarios that could have occurred:

• Not every entry in the master's mysql-bin.index existing in the master's datadir
• The master had a problem writing a binary log that the slave needs
• One of the master's binary logs is either zero-length or corrupt

You may need to sync the data like this:

• service mysql stop on the slave
• Run RESET MASTER on the master to zap the master's binary logs
• Perform rsync of data from master to slave multiple times until rsync < 1 min
• service mysql stop on the master
• Perform final rsync of data from master to slave
• Find the master's log file and its filesize
• service mysql start on the master
• service mysql start --skip-slave-start on the slave
• On the slave, perform change master to using master_log_file='mysql-bin.000001',master_log_pos=filesize of master's log fiie
• Perform START SLAVE; on the slave

Give it a Try !!!

I answered another question back on Feb 23, 2011 about creating a slave from a running master : MySQL Replication - Introduce new Slave to replication

Answer 2

There is another condition in which this error can arise.

Consider: You have two servers which we will imaginatively call Master and Slave. Master has expire_logs_days set to one day. Replication breaks on Slave, and you don't notice until the next evening. You find the conflict, fix it, and restart replication.

Woe! The binary log Slave needs has expired from Master.

Joy! Being a prudent sort, you have backups. You restore the three expired binlogs you need, hand-edit mysql-bin.index, and restart Slave's io_thread. Everything is good.

Woe! No it isn't. A few hours later, replication breaks again. "Could not find ...", says Master. You look in the binlog directory again. It's RIGHT THERE.

Aaaah, yes. So it is. But mysqld doesn't KNOW it's there. Because you hand-edited mysql-bin.index, mysqld's memory of the contents of mysql-bin.index does not match its ACTUAL contents, as you can see by issuing a SHOW BINARY LOGS on Master. And therein lies the problem.

So how do you fix this, young padawan? Well, this is very simple. You simply SET GLOBAL expire_logs_days temporarily to long enough that none of your newly-restored binary logs will be purged, and issue a FLUSH LOGS on Master. Master, among other things, re-reads mysql-bin.index and verifies that it reflects reality, making any necessary adjustments in the process.

Joy! The world is consistent again. You START SLAVE IO_THREAD on slave. Joy! Replication is running again and all is well.

"And then I went home."