MySQL5.6 parameters binlog-do-db and log-slave-updates cross-Library synchronization considerations

MySQL5.6.20 on the master Main Library configuration file/etc/my.cnf specify that the database is synchronized to slave using parameters binlog-do-db log-slave-updates considerations from the Library:

I. When Binlog-format = mixed is in mixed mode on Master Main Library

MySQL Master Main Library:/etc/my.cnf when binlog-format = MIXED bit blending mode:
1.1 Configuration file Parameters:

[[email protected] etc]# egrep "binlog-format|server-id|log-bin|binlog-do-db|log-slave-updates" /etc/my.cnfbinlog-format = MIXEDserver-id = 1131053306log-bin = /data/mysql/binlog/mysql-bin.logbinlog-do-db=ranzhidblog-slave-updates=1

1.2 Login to Master library master, cut into RANZHIDB library operation

mysql> use RANZHIDB; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> INSERT INTO Droa_attend (id,account,date,status) VALUES (' + ', ' YuYu ', ' 2018-01-20 ', ' Rest '); Query OK, 1 row affected, Warnings (0.00 sec) mysql> Select * from Droa_attend, +----+------------+------------+---- ------+----------+--------+-----------------+---------+----------+----------+-----------+--------+------+------ --------+------------+---------------------+| ID | Account | Date | SignIn | SignOut | Status | IP | Device | Client | Manualin | Manualout | Reason | Desc | Reviewstatus | Reviewedby | Revieweddate |+----+------------+------------+----------+----------+--------+-----------------+---------+-------  ---+----------+-----------+--------+------+--------------+------------+---------------------+| 9 | Xiaowang | 2018-01-21 | 00:00:00 | 00:00:00 |           Rest |      |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 10 | Xiaowu | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 11 | Wangwu | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 12 | Zhangsan | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 13 | Lisan | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 14 | YuYu | 2018-01-20 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 | 00:00: 00 |      |              |            | | 0000-00-00 00:00:00 |+----+------------+------------+----------+----------+--------+-----------------+---------+ ----------+----------+-----------+--------+------+--------------+------------+---------------------+14 rows in Set (0.00 sec)

1.3 is viewed from the library slave:

Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | RANZHIDB |+--------------------+mysql> select * from ranzhidb.droa_attend;+----+------------+------------+--- -------+----------+--------+-----------------+---------+----------+----------+-----------+--------+------+----- ---------+------------+---------------------+| ID | Account | Date | SignIn | SignOut | Status | IP | Device | Client | Manualin | Manualout | Reason | Desc | Reviewstatus | Reviewedby | Revieweddate |+----+------------+------------+----------+----------+--------+-----------------+---------+-------  ---+----------+-----------+--------+------+--------------+------------+---------------------+| 9 | Xiaowang | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-0000:00:00 | | 11 | Wangwu | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 12 | Zhangsan | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 13 | Lisan | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 14 | YuYu | 2018-01-20 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 |+----+------------+------------+----------+----------+--------+-----------------+---------+ ----------+----------+-----------+--------+------+--------------+------------+---------------------+13 rows in Set (0.00 sec)

Synchronization succeeded

1.4 Login Master Main library for cross-library operation
However, when logging into master Main library for cross-Library operation, a record is inserted in the Master Main Library Ranzhidb.droa_attend table, but the records inserted on master are not synchronized to slave

The demo is as follows:

mysql> use itop;database changedmysql> select Database (); +------------+| Database () |+------------+| Itop |+------------+1 row in Set (0.00 sec) mysql> INSERT into Ranzhidb.droa_attend (id,account,date,status) value S (' + ', ' Wenwen ', ' 2018-01-18 ', ' rest '); Query OK, 1 row affected, Warnings (0.00 sec) mysql> SELECT * from ranzhidb.droa_attend;+----+------------+---------- --+----------+----------+--------+-----------------+---------+----------+----------+-----------+--------+------ +--------------+------------+---------------------+| ID | Account | Date | SignIn | SignOut | Status | IP | Device | Client | Manualin | Manualout | Reason | Desc | Reviewstatus | Reviewedby | Revieweddate |+----+------------+------------+----------+----------+--------+-----------------+---------+-------  ---+----------+-----------+--------+------+--------------+------------+---------------------+| 9 | Xiaowang | 2018-01-21 | 00:00:00 | 00:00:00 |    Rest |             |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 10 | Xiaowu | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 11 | Wangwu | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 12 | Zhangsan | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 13 | Lisan | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 14 | YuYu | 2018-01-20 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00|        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 15 | Fangfang | 2018-01-18 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 16 | Wenwen | 2018-01-18 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 |+----+------------+------------+----------+----------+--------+-----------------+---------+ ----------+----------+-----------+--------+------+--------------+------------+---------------------+16 rows in Set (0.00 sec)

But logged on slave, the record with ID 16 is not synced to slave

Mysql> SELECT * from ranzhidb.droa_attend;+----+------------+------------+----------+----------+--------+------ -----------+---------+----------+----------+-----------+--------+------+--------------+------------+----------- ----------+| ID | Account | Date | SignIn | SignOut | Status | IP | Device | Client | Manualin | Manualout | Reason | Desc | Reviewstatus | Reviewedby | Revieweddate |+----+------------+------------+----------+----------+--------+-----------------+---------+-------  ---+----------+-----------+--------+------+--------------+------------+---------------------+| 9 | Xiaowang | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 11 | Wangwu | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 || 12 | Zhangsan | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 13 | Lisan | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 14 | YuYu | 2018-01-20 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 15 | Fangfang | 2018-01-18 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 |+----+------------+------------+----------+----------+--------+-----------------+---------+ ----------+----------+-----------+--------+------+--------------+------------+---------------------+14 rows in Set (0.00 sec)

Users created on master cannot be synced to slave when the RANZHIDB creates an administrative user on master on a cross-library operation to a synchronized database.
However, after you cut into the RANZHIDB library on master and created an administrative user for the library RANZHIDB, the created user is able to sync to the slave
Demonstrate:
Operation on Master:

mysql> use itop;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> mysql> mysql> grant all on ranzhidb.* to [email protected]‘%‘ identified by ‘test#558996‘;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

Operation on Slave:

mysql> select user,host from mysql.user;+------------+-----------+| user       | host      |+------------+-----------+| testuser03 | %         || testuser04 | %         || root       | 127.0.0.1 || root       | localhost |+------------+-----------+4 rows in set (0.00 sec)

Txtuser01 User not synced over

Operation on Master:

mysql> use ranzhidb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> grant all on ranzhidb.* to [email protected]‘%‘ identified by ‘test#558996‘;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

View on Slave:

mysql> select user,host from mysql.user;+------------+-----------+| user       | host      |+------------+-----------+| testuser03 | %         || testuser04 | %         || txtuser02  | %         || root       | 127.0.0.1 || root       

The TXTUSER02 user has synced to the slave.

Two. mysql Master Main Library:/etc/my.cnf when Binlog-format = MIXED is mixed mode

When master Main Library configuration file/etc/my.cnf: Specify binlog-format= row mode, the main parameters are as follows:

[[email protected] etc]# egrep "binlog-format|server-id|log-bin|binlog-do-db|log-slave-updates" /etc/my.cnfbinlog-format = rowserver-id = 1131053306log-bin = /data/mysql/binlog/mysql-bin.logbinlog-do-db=ranzhidblog-slave-updates=1

2.1 Log on to the MySQL Master Library Master for cross-Library operations:

mysql> use itopreading table information for completion of table and column namesyou can turn off this feature to get a Quicker startup with-adatabase changedmysql> Select Database (); +------------+| Database () |+------------+| Itop |+------------+1 row in Set (0.00 sec) mysql> INSERT into Ranzhidb.droa_attend (id,account,date,status) value S (' n ', ' Fangfang ', ' 2018-01-18 ', ' rest '); Query OK, 1 row affected, Warnings (0.00 sec) mysql> SELECT * from ranzhidb.droa_attend;+----+------------+---------- --+----------+----------+--------+-----------------+---------+----------+----------+-----------+--------+------ +--------------+------------+---------------------+| ID | Account | Date | SignIn | SignOut | Status | IP | Device | Client | Manualin | Manualout | Reason | Desc | Reviewstatus | Reviewedby | Revieweddate |+----+------------+------------+----------+----------+--------+-----------------+---------+------- ---+----------+-----------+--------+------+--------------+------------+---------------------+| 9 | Xiaowang | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 10 | Xiaowu | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 11 | Wangwu | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 12 | Zhangsan | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 13 | Lisan | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 14 | YuYu | 2018-01-20 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 15 | Fangfang | 2018-01-18 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 |+----+------------+------------+----------+----------+--------+-----------------+---------+ ----------+----------+-----------+--------+------+--------------+------------+---------------------+15 rows in Set (0.00 sec)

The

is viewed from the library slave:

Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | RANZHIDB |+--------------------+mysql> select * from ranzhidb.droa_attend;+----+------------+------------+--- -------+----------+--------+-----------------+---------+----------+----------+-----------+--------+------+----- ---------+------------+---------------------+| ID | Account | Date | SignIn | SignOut | Status | IP | Device | Client | Manualin | Manualout | Reason | Desc | Reviewstatus | Reviewedby | Revieweddate |+----+------------+------------+----------+----------+--------+-----------------+---------+-------  ---+----------+-----------+--------+------+--------------+------------+---------------------+| 9 | Xiaowang | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-0000:00:00 | | 11 | Wangwu | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 12 | Zhangsan | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 13 | Lisan | 2018-01-21 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 14 | YuYu | 2018-01-20 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 | | 15 | Fangfang | 2018-01-18 | 00:00:00 | 00:00:00 |                 Rest |         |          | | 00:00:00 |        00:00:00 |      |              |            | | 0000-00-00 00:00:00 |+----+------------+------------+----------+----------+--------+-----------------+---------+----------+----------+-----------+--------+------+----- ---------+------------+---------------------+14 rows in Set (0.00 sec)

Synchronization succeeded.

2.2 However, the authorized user is not synchronized to slave when the RANZHIDB is created for the synchronized database on Master .
Operation on Master:

mysql> select database();+------------+| database() |+------------+| itop       

View on Slave:

mysql> select user,host from mysql.user;+------+-----------+| user | host      |+------+-----------+| root | 127.0.0.1 || root | localhost |+------+-----------+2 rows in set (0.00 sec)

User Testuser02 created on the main library is not synced to slave

2.3 Switch to sync database ranzhidb on Master to create an authorized user

mysql> use ranzhidb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> grant all on ranzhidb.* to ‘testuser03‘@‘%‘ identified by ‘test#558996‘;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

Log on slave to view:

mysql> select user,host from mysql.user;+------------+-----------+| user       | host      |+------------+-----------+| testuser03 | %         || root       | 127.0.0.1 || root       | localhost |+------------+-----------+3 rows in set (0.00 sec)mysql> 同步完成

MySQL5.6 parameters binlog-do-db and log-slave-updates cross-Library synchronization considerations