MySQL's log-slave-updates parameters

1. Introduction

When using MySQL's replication mechanism to achieve master-slave synchronization, it was implemented by three threads, one I/O thread from the library, one I/O thread and one SQL thread. At configuration time, the main library needs to start the Bin-log parameter, which is to add Log-bin =/data/3307/mysql-bin The line in the configuration file, but when we need to implement cascade synchronization, this is a pattern,a>b> C When implementing level three synchronization, the AB library needs to add a parameter in addition to setting the Log-bin parameter: log-slave-updates

The log-slave-updates parameter is turned off by default, and if not set manually, bin-log only records SQL statements that are executed directly on that library. The SQL statements executed by the replication SQL thread reading Relay-log are not logged to Bin-log, so the above level three cascade synchronization cannot be achieved.

See the MySQL website for an introduction to this parameter: http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_log-slave-updates

Reference: http://815632410.blog.51cto.com/1544685/1420156

When I recently learned MySQL replication, the log-slave-updates parameter has not figured out what this parameter means. On the internet to check a bit, found that the Netizen explained, I also did not understand. I guess this guy is stupid. I am more stupid and more love to drill horns. When others may listen to the teacher, they will hear it directly. When I was in class, I felt nothing wrong. But when I think about it in bed at night, I find there is still doubt. Think of several geniuses to understand. Although this parameter let me spend a lot of time to understand, but my heart is very happy ....

If the writing is not correct, please criticize it. Thank you

This article needs to verify the question:

The Log-slave-updates parameter is required to be added from the library when it is the main library from the library, because the parameter must be added from the library to be the main library from the library. This parameter is to let from the library to copy data from the main library can be written to the Binlog log, why use this parameter to write Binlog log, not in the configuration file Log-bin =/data/3307/mysql-bin option is OK?

A: The Log-bin parameter is opened from the library, if the data is written directly to the library, the Log-bin log can be recorded, but the main library binary log file is read from the library through the I0 thread, and then the data written by the SQL thread is not recorded in the Binlog log. In other words, the data copied from the library from the main library is not written to the Binlog log from the library. Therefore, you need to add the Log-slave-updates parameter to the configuration file when you make the library from the library as the other main library.

Verification process:

The experimental environment is multi-instance master-slave synchronization, 3306 is the main library, and 3307 is from the library.

First, when the Log-slave-updates parameter is not added:

1, two databases have been able to master-slave synchronization, and master-slave data has been consistent, from the library to open the Log-bin function.

Mysql-slave>system mysql-uroot-p ' 123 '-s/data/3307/mysql.sock-e "show slave Status\g" |egrep "Seconds_Behind_ Master|_running "

Slave_io_running:yes

Slave_sql_running:yes

seconds_behind_master:0

Check that the master-slave data is consistent:

Main Library:

Mysql-master>show databases;

+--------------------+

| Database |

+--------------------+

|information_schema |

|mysql |

|oldboy |

|performance_schema |

|test |

+--------------------+

5 Rowsin Set (0.00 sec)

From library:

Mysql-slave>show databases;

+--------------------+

| Database |

+--------------------+

|information_schema |

|mysql |

|oldboy |

|performance_schema |

|test |

+--------------------+

5 Rowsin Set (0.01 sec)

View the main library file

[Email protected]]# ll

total68

drwxr-xr-x6 mysql mysql 4096 Apr 03:49 data

-rw-r--r--1 MySQL MySQL 1899 Apr 23:41 my.cnf

-rwx------1 MySQL mysql 1304 Apr 23:35 MySQL

-RW-RW----1 MySQL mysql 126 Apr 07:35mysql-bin.000001

-RW-RW----1 MySQL mysql 126 Apr 22:59mysql-bin.000002

-RW-RW----1 MySQL mysql 698 Apr 23:36mysql-bin.000003

-RW-RW----1 MySQL mysql 126 Apr 23:42mysql-bin.000004

-RW-RW----1 mysqlmysql 1034 APR 03:49 mysql-bin.000005

-RW-RW----1 MySQL mysql April 23:42mysql-bin.index

srwxrwxrwx1 mysql MySQL 0 Apr 23:42mysql.sock

-rw-r-----1 mysql root 14731 Apr 23:51mysql_oldboy3306.err

-RW-RW----1 MySQL mysql 6 Apr 23:42mysqld.pid

-RW-RW----1 MySQL mysql 107 Apr 23:42relay-bin.000003

-RW-RW----1 MySQL mysql April 23:42relay-bin.index

-RW-RW----1 MySQL mysql Apr 23:51relay-log.info

From Library profile time:

[Email protected]]# ll

total72

drwxr-xr-x6 mysql mysql 4096 Apr 03:49 data

-rw-r--r--1 MySQL MySQL 1905 Apr 03:47 my.cnf

-rwx------1 MySQL mysql 1304 Apr 23:47 MySQL

-RW-RW----1 mysql mysql 203 Apr 03:47mysql-bin.000001

-RW-RW----1 mysqlmysql 107 APR 03:47 mysql-bin.000002

-RW-RW----1 Mysqlmysql 03:47 Mysql-bin.index

srwxrwxrwx1 mysql MySQL 0 Apr 03:47mysql.sock

-rw-r-----1 mysql root 23291 Apr 03:47mysql_oldboy3307.err

-RW-RW----1 MySQL mysql 5 Apr 03:47mysqld.pid

-RW-RW----1 MySQL mysql 03:47relay-bin.000005

-RW-RW----1 mysqlmysql 413 APR 03:49 relay-bin.000006

-RW-RW----1 MySQL mysql Apr 03:47relay-bin.index

-RW-RW----1 MySQL mysql 03:49relay-log.info

Test: Create a database on the main library to see if the binary log is written from the library.

[[Email protected] 3306]# Date

Sun APR 04:02:26 CST 2014

[Email protected]]# ll

total68

drwxr-xr-x7 mysql mysql 4096 Apr 04:02 data

-rw-r--r--1 MySQL MySQL 1899 Apr 23:41 my.cnf

-rwx------1 MySQL mysql 1304 Apr 23:35 MySQL

-RW-RW----1 MySQL mysql 126 Apr 07:35mysql-bin.000001

-RW-RW----1 MySQL mysql 126 Apr 22:59mysql-bin.000002

-RW-RW----1 MySQL mysql 698 Apr 23:36mysql-bin.000003

-RW-RW----1 MySQL mysql 126 Apr 23:42mysql-bin.000004

-RW-RW----1 mysqlmysql 1115 APR 04:02 mysql-bin.000005 #时间和操作前不一样了 to prove that the main library has written binlog logs.

-RW-RW----1 MySQL mysql April 23:42mysql-bin.index

srwxrwxrwx1 mysql MySQL 0 Apr 23:42mysql.sock

-rw-r-----1 mysql root 14731 Apr 23:51mysql_oldboy3306.err

-RW-RW----1 MySQL mysql 6 Apr 23:42 mysqld.pid

-RW-RW----1 MySQL mysql 107 Apr 23:42relay-bin.000003

-RW-RW----1 MySQL mysql April 23:42relay-bin.index

-RW-RW----1 MySQL mysql Apr 23:51relay-log.info

Time change from library profile:

[Email protected]]# ll

total72

drwxr-xr-x7 mysql mysql 4096 Apr 04:02 data

-rw-r--r--1 MySQL MySQL 1905 Apr 03:47 my.cnf

-rwx------1 MySQL mysql 1304 Apr 23:47 MySQL

-RW-RW----1 mysql mysql 203 Apr 03:47mysql-bin.000001

-RW-RW----1 mysqlmysql 107 Apr 03:47 mysql-bin.000002 #时间和操作前的是一样的, confirm that no Binlog log is written

-RW-RW----1 MySQL mysql Apr 03:47mysql-bin.index

srwxrwxrwx1 mysql MySQL 0 Apr 03:47mysql.sock

-rw-r-----1 mysql root 23291 Apr 03:47mysql_oldboy3307.err

-RW-RW----1 MySQL mysql 5 Apr 03:47mysqld.pid

-RW-RW----1 MySQL mysql 03:47relay-bin.000005

-RW-RW----1 mysqlmysql 494 APR 04:02 relay-bin.000006 #证明从库已经写relay-log Log

-RW-RW----1 MySQL mysql Apr 03:47relay-bin.index

-RW-RW----1 mysqlmysql 04:02 relay-log.info  

To write data directly from the library:

[[Email protected] 3307]# Date

Sun APR 04:09:29 CST2014

Mysql-slave>create database QQ;

Queryok, 1 Row Affected (0.00 sec)

Mysql-slave>show databases;

+--------------------+

| Database |

+--------------------+

|information_schema |

|CBD |

|mysql |

|oldboy |

|performance_schema |

|QQ |

|test |

+--------------------+

7 Rowsin Set (0.00 sec)

To view the change in time from the library log file:

[Email protected]]# ll

total72

drwxr-xr-x8 mysql mysql 4096 Apr 04:10 data

-rw-r--r--1 MySQL MySQL 1905 Apr 03:47 my.cnf

-rwx------1 MySQL mysql 1304 Apr 23:47 MySQL

-RW-RW----1 mysql mysql 203 Apr 03:47mysql-bin.000001

-RW-RW----1 mysqlmysql 186 Apr 04:10 mysql-bin.000002 #和在从库上创建qq数据库的时间一致, the proof has been written Bin-log log.

-RW-RW----1 MySQL mysql Apr 03:47mysql-bin.index

srwxrwxrwx1 mysql MySQL 0 Apr 03:47mysql.sock

-rw-r-----1 mysql root 23291 Apr 03:47mysql_oldboy3307.err

-RW-RW----1 MySQL mysql 5 Apr 03:47mysqld.pid

-RW-RW----1 MySQL mysql 03:47relay-bin.000005

-RW-RW----1 MySQL mysql 494 Apr 04:02relay-bin.000006

-RW-RW----1 MySQL mysql Apr 03:47relay-bin.index

-RW-RW----1 MySQL mysql 04:02relay-log.info

Second, after adding the log-slave-updates parameter

1. Create operations on the main library

[[Email protected] 3307]# Date

Sun APR 04:19:10 CST2014

Mysql-master>create database Pang;

Queryok, 1 Row Affected (0.00 sec)

Mysql-master>show databases;

+--------------------+

| Database |

+--------------------+

|information_schema |

|CBD |

|mysql |

|oldboy |

| Pang |

|performance_schema |

|test |

+--------------------+

7 Rowsin Set (0.00 sec)

Check the synchronization situation:

Mysql-slave>show databases;

+--------------------+

| Database |

+--------------------+

|information_schema |

|CBD |

|mysql |

|oldboy |

| pang | #已经同步完成

|performance_schema |

|QQ |

|test |

+--------------------+

8 Rowsin Set (0.00 sec)

Check configuration file time changes:

Main Library profile Time:

[Email protected]]# ll

total68

drwxr-xr-x8 mysql mysql 4096 Apr 04:20 data

-rw-r--r--1 MySQL MySQL 1899 Apr 23:41 my.cnf

-rwx------1 MySQL mysql 1304 Apr 23:35 MySQL

-RW-RW----1 MySQL mysql 126 Apr 07:35mysql-bin.000001

-RW-RW----1 MySQL mysql 126 Apr 22:59mysql-bin.000002

-RW-RW----1 MySQL mysql 698 Apr 23:36mysql-bin.000003

-RW-RW----1 MySQL mysql 126 Apr 23:42mysql-bin.000004

-RW-RW----1 mysqlmysql 1198 APR 04:20 mysql-bin.000005 #主库写入时的时间, the time that the Log-bin log was written when the database was created

-RW-RW----1 MySQL mysql April 23:42mysql-bin.index

srwxrwxrwx1 mysql MySQL 0 Apr 23:42 mysql.sock

-rw-r-----1 mysql root 14731 Apr 23:51mysql_oldboy3306.err

-RW-RW----1 MySQL mysql 6 Apr 23:42mysqld.pid

-RW-RW----1 MySQL mysql 107 Apr 23:42relay-bin.000003

-RW-RW----1 MySQL mysql April 23:42relay-bin.index

-RW-RW----1 MySQL mysql Apr 23:51relay-log.info

Time change from library profile:

[Email protected]]# ll

Total80

drwxr-xr-x9 mysql mysql 4096 Apr 04:20 data

-rw-r--r--1 MySQL MySQL 1922 Apr 04:16 my.cnf

-rwx------1 MySQL mysql 1304 Apr 23:47 MySQL

-RW-RW----1 mysql mysql 203 Apr 03:47mysql-bin.000001

-RW-RW----1 MySQL mysql 205 Apr 04:16mysql-bin.000002

-RW-RW----1 mysql mysql 04:20 mysql-bin.000003 #从库已经开始写binlog日志了, and in the same time as the main library.

-RW-RW----1 MySQL mysql Apr 04:16mysql-bin.index

srwxrwxrwx1 mysql MySQL 0 Apr 04:16mysql.sock

-rw-r-----1 mysql root 25871 Apr 04:16mysql_oldboy3307.err

-RW-RW----1 MySQL mysql 5 Apr 04:16mysqld.pid

-RW-RW----1 MySQL mysql 04:16relay-bin.000007

-RW-RW----1 MySQL mysql 336 Apr 04:20relay-bin.000008

-RW-RW----1 MySQL mysql Apr 04:16relay-bin.index

-RW-RW----1 mysql mysql 04:20 relay-log.info #relay-log log file also changed

As can be known from the above experiment,

1, from the library only to open the Log-bin function, do not add log-slave-updates parameters, the data from the library copied from the main library will not be written to the Log-bin log file.

2. When writing data directly from the library, it is written to the Log-bin log.

3. When the Log-slave-updates parameter is turned on, data copied from the library from the main library is written to the Log-bin log file. This is also the function of this parameter.

MySQL's log-slave-updates parameters