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配置文件参数:
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上操作:
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上查看:
txtuser02用户已经同步到slave上了。
二.mysql master主库:/etc/my.cnf 当binlog-format = MIXED 为混合模式
当master主库配置文件/etc/my.cnf:指定binlog-format= row 模式,主要参数如下:
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上查看:
主库上创建的用户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上查看: