参数设置,主从都设置成"ROW"模式
从库:
更改从库数据,使主从不一致(id是主键)
从库同步成功:
这过程发生了什么呢?
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
从库:
mysql> show variables like '%log_slave_update%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | ON |
+-------------------+-------+
1 row in set (0.00 sec)
更改从库数据,使主从不一致(id是主键)
mysql> select * from tb_rpl;
+----+--------+
| id | name |
+----+--------+
| 1 | wubx |
| 2 | mysql1 |
| 3 | test |
+----+--------+
3 rows in set (0.00 sec)
mysql> update tb_rpl set name='mysql22' where name = 'mysql1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
这时更改主库数据
mysql> select * from tb_rpl;
+----+--------+
| id | name |
+----+--------+
| 1 | wubx |
| 2 | mysql1 |
| 3 | test |
+----+--------+
3 rows in set (0.00 sec)
mysql> update tb_rpl set name='mysql11' where name = 'mysql1';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
从库同步成功:
mysql> select * from tb_rpl;
+----+---------+
| id | name |
+----+---------+
| 1 | wubx |
| 2 | mysql11 |
| 3 | test |
+----+---------+
3 rows in set (0.00 sec)
这过程发生了什么呢?
先分析下主库log
如果用这个语句去更新从库数据,显然不行。
所以还需要分析下从库的日志
真相大白,从库执行时把@2这个变量给改了,这中间应该还有我没看到事发生。
这过程与我想象中的where里只有主键的条件不一样。
补充:
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.21-log |
+------------+
1 row in set (0.03 sec)