MySQL5.6参数binlog-do-db和log-slave-updates跨库同步注意事项

MySQL5.6.20上在master主库配置文件/etc/my.cnf里指定数据库同步到slave从库上使用参数binlog-do-db log-slave-updates 注意事项:

一. master主库上binlog-format = MIXED为混合模式时

mysql master主库:/etc/my.cnf 当binlog-format = MIXED 位混合模式时:
1.1配置文件参数:

[root@cacti etc]# egrep "binlog-format|server-id|log-bin|binlog-do-db|log-slave-updates" /etc/my.cnf
binlog-format = MIXED
server-id = 1131053306
log-bin = /data/mysql/binlog/mysql-bin
binlog-do-db=ranzhidb
log-slave-updates=1
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

1.2登陆主库master,切入到ranzhidb库操作

mysql> use ranzhidb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into droa_attend (id,account,date,status) values ('14','yuyu','2018-01-20','rest');
Query OK, 1 row affected, 10 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.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.

1.3在从库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-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 |
+----+------------+------------+----------+----------+--------+-----------------+---------+----------+----------+-----------+--------+------+--------------+------------+---------------------+
13 rows in set (0.00 sec)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.

同步成功

1.4登陆master主库进行跨库操作
但是登陆master主库进行跨库操作的时候,在master主库ranzhidb.droa_attend表里面插入一条记录,但是在master上插入的记录没有同步到slave上

演示如下:

mysql> use itop;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| itop       |
+------------+
1 row in set (0.00 sec)

mysql> insert into ranzhidb.droa_attend (id,account,date,status) values ('16','wenwen','2018-01-18','rest');
Query OK, 1 row affected, 10 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)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.

但是登陆slave上查看,id是16的记录没有同步到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)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.

同时在master上跨库操作给同步的数据库ranzhidb创建管理用户时,master上创建的用户是不能同步到slave 上的。
但是在master上切入到ranzhidb库,给库ranzhidb创建管理用户后,创建的用户是可以同步到slave 上的
演示:
master上操作:

mysql> use itop;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> 
mysql> grant all on ranzhidb.* to txtuser01@'%' identified by 'test#558996';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.

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)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.

txtuser01用户没有同步过来

master上操作:

mysql> use ranzhidb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> grant all on ranzhidb.* to txtuser02@'%' identified by 'test#558996';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

slave上查看:

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

mysql> 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.

txtuser02用户已经同步到slave上了。

二.mysql master主库:/etc/my.cnf 当binlog-format = MIXED 为混合模式

当master主库配置文件/etc/my.cnf:指定binlog-format= row 模式,主要参数如下:

[root@cacti etc]# egrep "binlog-format|server-id|log-bin|binlog-do-db|log-slave-updates" /etc/my.cnf
binlog-format = row
server-id = 1131053306
log-bin = /data/mysql/binlog/mysql-bin
binlog-do-db=ranzhidb
log-slave-updates=1
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

2.1登陆MySQL主库master上进行跨库操作:

mysql> use itop
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select database();
+------------+
| database() |
+------------+
| itop       |
+------------+
1 row in set (0.00 sec)

mysql> insert into ranzhidb.droa_attend (id,account,date,status) values ('15','fangfang','2018-01-18','rest');
Query OK, 1 row affected, 10 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)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.

在从库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-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)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.

同步成功。

2.2但是此时在master上跨库给同步的数据库ranzhidb创建授权用户时,授权用户是没有同步到slave上的
master上操作:

mysql> select database();
+------------+
| database() |
+------------+
| itop       |
+------------+
1 row in set (0.00 sec)
mysql> grant all on ranzhidb.* to 'testuser02'@'%' identified by 'test#558996';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.

slave上查看:

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.

主库上创建的用户testuser02没有同步到slave 上

2.3在master上切换到同步数据库ranzhidb,创建授权用户

mysql> use ranzhidb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 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)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.

登陆slave上查看:

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> 

同步完成
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
posted @ 2018-01-21 20:21  勤奋的蓝猫  阅读(7)  评论(0编辑  收藏  举报  来源