Fix increasing Seconds_Behind_Master issue

The problem

Sometimes on innodb replication the slave is being late behind the master. This can be observed by the Seconds_Behind_Master value always increasing when the master is inserting/updating rows.

Exemple :

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
 Master_Host: masterdb.somewhere.com
 Master_User: replication
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.007781
 Read_Master_Log_Pos: 365235732
 Relay_Log_File: mysqld-3306-relay-bin.004386
 Relay_Log_Pos: 259967669
 Relay_Master_Log_File: mysql-bin.007781
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
(...)
 Seconds_Behind_Master: 33
1 row in set (0.00 sec)

In this example we can see that the slave is 33s behind the master while both CPU and IO are not overloaded.

Why ?

Because the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. Writing and flushing operations are time expensive for only one transaction. It will be better to perform these operations for several transactions instead of only one.

How to do it ?

Just change the value of innodb_flush_log_at_trx_commit from 1 (default) to 0 :

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 0 ;

And add it in the my.cnf in order to take this setting in account at the next restart of mysql engine.

More information :  http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

synchronize a slave with the master without stopping the master

# 1. Stop Mysql server on the slave :

/etc/init.d/mysqld stop

# 1. On the Master, flush all the tables and block write statements by executing

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

# 1. While the read lock placed by FLUSH TABLES WITH READ LOCK is in effect, read the value of the current binary log name and offset on the master:

mysql>  SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mysql-bin.000017 |   685677 |              |                  |
+--------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

# 1. While the read lock is placed on the master, copy the data from the master to the slave (command to execute on the slave):

SLAVE> rsync --delete --exclude 'mysql' --exclude '*.info' -Wave ssh  root@MASTER:/opt/mysql/data/ /opt/mysql/data/

# 1. After you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master:

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
# 1. Remove the *.info files on the slave :
rm /opt/mysql/data/*info

# 1. Start Mysql server on the slave :

/etc/init.d/mysqld start

# 1. Stop the slave threads on the slave server :

mysql> slave stop;
Query OK, 0 rows affected (0.00 sec)

# 1. Execute the following statement on the slave, replacing the option values with the actual values relevant to your system:

mysql> CHANGE MASTER TO
->     MASTER_HOST='master_host_name',
->     MASTER_USER='replication_user_name',
->     MASTER_PASSWORD='replication_password',
->     MASTER_LOG_FILE='mysql-bin.000017',
->     MASTER_LOG_POS=685677;

# 1. Start the slave threads on the slave server :

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

# 1. Check the slave threads status on the slave server with command ‘

mysql> show slave status\G;

Slave_IO_Running and Slave_SQL_Running must be equal to yes .