Posts tagged ‘binlog_format’

MySQL row方式的复制relay

上次老大问我row方式的binlog复制到备机可不可能记录为statement。根据我对复制的了解和对row方式的理解,我回答的是不可能。因为MySQL的源码中,我记得row方式的处理是table map,row_log_event分开的,然后row_log_event中记录的是行的数据(包括bitmap对应对应的列在该row_log_event有没有记录,整个row_log_event的长度,每个列的长度后面跟上列的具体数据等),这些东西记录下载,MySQL的开发者如果要把它还原成statement方式,并且将相关的auto_increment,var,rand等还原出来难度还是非常大的,并且,row方式实际上已经毁坏了statement的结构(比如:update tbl1 set c1=3 where id=3记录为row的话,正常情况下,row_log_event不会只记录了更新的这一列c1,它会记录id或者其他的列,虽然id或者其他的列值并没有更新。原因可能是为了正确的更新对应的行。),如果想完全还原成和主机上提交的statement一模一样基本是不可能的。另外,我也没有看到MySQL源码中的相关代码有将row转换成statement的相关痕迹,所以判断row方式在备机中log_slave_updates会也被记录为row方式。
但是,口说无凭,实践致胜,我在主备机环境下测试了上面的这个情况。确实如上所述,row方式的binlog,备机利用log_slave_updates记录到本机binlog也是row方式。即使你设置备机的binlog_format为statement。下面是我的测试描述和结果。

1、主机上设置binlog_format为row.
root@localhost : alitest 10:01:09> set binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

root@localhost : alitest 10:01:25> show variables like ‘bin%’;
+——————-+———+
| Variable_name     | Value   |
+——————-+———+
| binlog_cache_size | 2097152 |
| binlog_format     | ROW     |
+——————-+———+
2 rows in set (0.00 sec)

2、备机上设置binlog_format为statement
root@localhost : (none) 10:06:44> set global binlog_format=’statement’;
Query OK, 0 rows affected (0.00 sec)
root@localhost : (none) 10:07:04> set binlog_format=’statement’;
Query OK, 0 rows affected (0.00 sec)

root@localhost : (none) 10:07:11> show variables like ‘bin%’;
+——————-+———–+
| Variable_name     | Value     |
+——————-+———–+
| binlog_cache_size | 2097152   |
| binlog_format     | STATEMENT |
+——————-+———–+
2 rows in set (0.00 sec)

3、主机上创建测试表:
root@localhost : (none) 10:00:02> use alitest;
Database changed

root@localhost : alitest 10:00:50> create table test9 (c1 int unsigned primary key, c2 varchar(24));
Query OK, 0 rows affected (0.20 sec)

4、备机上查看目前的日志位置:
root@localhost : (none) 10:04:49> show master logs;
+——————+———–+
| Log_name         | File_size |
+——————+———–+
| mysql-bin.000001 |       125 |

| mysql-bin.000085 | 362605228 |
+——————+———–+
85 rows in set (0.00 sec)

4、主机上生成测试的row方式日志:
root@localhost : alitest 10:01:27> insert into test9 (c1,c2) values (44, “343434”);
Query OK, 1 row affected (0.00 sec)

root@localhost : alitest 10:02:53> insert into test9 (c1,c2) values (3, “343434”);
Query OK, 1 row affected (0.00 sec)

5、备机查看自己生成binlog:
root@localhost : (none) 10:07:53> show binlog events in ‘mysql-bin.000085′ from 362605228;
+——————+———–+————+———–+————-+——————————–+
| Log_name         | Pos       | Event_type | Server_id | End_log_pos | Info                           |
+——————+———–+————+———–+————-+——————————–+
| mysql-bin.000085 | 362605228 | Query      |         1 |   362605287 | BEGIN                          |
| mysql-bin.000085 | 362605287 | Table_map  |         1 |   362605337 | table_id: 27 (alitest.test9)   |
| mysql-bin.000085 | 362605337 | Write_rows |         1 |   362605378 | table_id: 27 flags: STMT_END_F |
| mysql-bin.000085 | 362605378 | Xid        |         1 |   362605405 | COMMIT /* xid=23537907 */      |
| mysql-bin.000085 | 362605405 | Query      |         1 |   362605464 | BEGIN                          |
| mysql-bin.000085 | 362605464 | Table_map  |         1 |   362605514 | table_id: 27 (alitest.test9)   |
| mysql-bin.000085 | 362605514 | Write_rows |         1 |   362605555 | table_id: 27 flags: STMT_END_F |
| mysql-bin.000085 | 362605555 | Xid        |         1 |   362605582 | COMMIT /* xid=23537917 */      |
+——————+———–+————+———–+————-+——————————–+
8 rows in set (0.00 sec)

由上可以看到,备机虽然设置自己的binlog_format为statement,binlog日志中记录从主机过来的binlog仍然为row。写到这里,我突然想起,binlog_format是否只是对在本机提交的sql才有效,于是我测试了以下两种情况:
1、主机为statement,备机为statement。 结果备机记录为statement.
2、主机为statement,备机为row。结果备机记录为statement.
也就是说,备机记录的从主机复制过来的binlog不随自己的binlog_format方式改变,而是忠实的依照主机记录的方式来记录。
下面是简单的测试结果:
测试1 主机为statement,备机为statement。 结果备机记录为statement.
1、主机上设置binlog_format为row并插入一行
root@localhost : alitest 10:42:41> set binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)

2、备机上设置为statement
root@localhost : (none) 10:06:44> set global binlog_format=’statement’;
Query OK, 0 rows affected (0.00 sec)
root@localhost : (none) 10:07:04> set binlog_format=’statement’;
Query OK, 0 rows affected (0.00 sec)

root@localhost : (none) 10:07:11> show variables like ‘bin%’;
+——————-+———–+
| Variable_name     | Value     |
+——————-+———–+
| binlog_cache_size | 2097152   |
| binlog_format     | STATEMENT |
+——————-+———–+
2 rows in set (0.00 sec)

3、主机上插入一行
root@localhost : alitest 10:42:54> insert into test9 (c1,c2) values (4, “343434”);
Query OK, 1 row affected (0.00 sec)

4、备机上查看日志:
root@localhost : (none) 10:35:48> show binlog events in ‘mysql-bin.000085’ from 362605228;
+——————+———–+————+———–+————-+—————————————————————+
| Log_name         | Pos       | Event_type | Server_id | End_log_pos | Info                                                          |
+——————+———–+————+———–+————-+—————————————————————+
| mysql-bin.000085 | 362605228 | Query      |         1 |   362605287 | BEGIN                                                         |
| mysql-bin.000085 | 362605287 | Table_map  |         1 |   362605337 | table_id: 27 (alitest.test9)                                  |
| mysql-bin.000085 | 362605337 | Write_rows |         1 |   362605378 | table_id: 27 flags: STMT_END_F                                |
| mysql-bin.000085 | 362605378 | Xid        |         1 |   362605405 | COMMIT /* xid=23537907 */                                     |
| mysql-bin.000085 | 362605405 | Query      |         1 |   362605464 | BEGIN                                                         |
| mysql-bin.000085 | 362605464 | Table_map  |         1 |   362605514 | table_id: 27 (alitest.test9)                                  |
| mysql-bin.000085 | 362605514 | Write_rows |         1 |   362605555 | table_id: 27 flags: STMT_END_F                                |
| mysql-bin.000085 | 362605555 | Xid        |         1 |   362605582 | COMMIT /* xid=23537917 */                                     |
| mysql-bin.000085 | 362605582 | Query      |         1 |   362605641 | BEGIN                                                         |
| mysql-bin.000085 | 362605641 | Query      |         1 |   362605753 | use alitest; insert into test9 (c1,c2) values (4, “343434”) |
| mysql-bin.000085 | 362605753 | Xid        |         1 |   362605780 | COMMIT /* xid=23537922 */                                     |
+——————+———–+————+———–+————-+—————————————————————+
11 rows in set (0.00 sec)

测试2 主机为statement,备机为row。结果备机记录为statement.
1、主机上设置binlog_format为row并插入一行
root@localhost : alitest 10:42:41> set binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)

2、备机上设置为statement
root@localhost : (none) 10:46:23> set binlog_format=’row’;
Query OK, 0 rows affected (0.00 sec)

root@localhost : (none) 10:46:28> set global binlog_format=’row’;
Query OK, 0 rows affected (0.00 sec)

root@localhost : (none) 10:46:37> show variables like ‘bin%’;
+——————-+———+
| Variable_name     | Value   |
+——————-+———+
| binlog_cache_size | 2097152 |
| binlog_format     | ROW     |
+——————-+———+
2 rows in set (0.00 sec)

3、主机上插入一行
root@localhost : alitest 10:43:02> insert into test9 (c1,c2) values (5, “343434”);
Query OK, 1 row affected (0.00 sec)

4、备机上查看日志:
root@localhost : (none) 10:46:43> show binlog events in ‘mysql-bin.000085’ from 362605228;
+——————+———–+————+———–+————-+—————————————————————+
| Log_name         | Pos       | Event_type | Server_id | End_log_pos | Info                                                          |
+——————+———–+————+———–+————-+—————————————————————+
| mysql-bin.000085 | 362605228 | Query      |         1 |   362605287 | BEGIN                                                         |
| mysql-bin.000085 | 362605287 | Table_map  |         1 |   362605337 | table_id: 27 (alitest.test9)                                  |
| mysql-bin.000085 | 362605337 | Write_rows |         1 |   362605378 | table_id: 27 flags: STMT_END_F                                |
| mysql-bin.000085 | 362605378 | Xid        |         1 |   362605405 | COMMIT /* xid=23537907 */                                     |
| mysql-bin.000085 | 362605405 | Query      |         1 |   362605464 | BEGIN                                                         |
| mysql-bin.000085 | 362605464 | Table_map  |         1 |   362605514 | table_id: 27 (alitest.test9)                                  |
| mysql-bin.000085 | 362605514 | Write_rows |         1 |   362605555 | table_id: 27 flags: STMT_END_F                                |
| mysql-bin.000085 | 362605555 | Xid        |         1 |   362605582 | COMMIT /* xid=23537917 */                                     |
| mysql-bin.000085 | 362605582 | Query      |         1 |   362605641 | BEGIN                                                         |
| mysql-bin.000085 | 362605641 | Query      |         1 |   362605753 | use alitest; insert into test9 (c1,c2) values (4, “343434”) |
| mysql-bin.000085 | 362605753 | Xid        |         1 |   362605780 | COMMIT /* xid=23537922 */                                     |
| mysql-bin.000085 | 362605780 | Query      |         1 |   362605839 | BEGIN                                                         |
| mysql-bin.000085 | 362605839 | Query      |         1 |   362605951 | use alitest; insert into test9 (c1,c2) values (5, “343434”) |
| mysql-bin.000085 | 362605951 | Xid        |         1 |   362605978 | COMMIT /* xid=23537929 */                                     |
+——————+———–+————+———–+————-+—————————————————————+
14 rows in set (0.00 sec)

附上binlog_format变量的介绍
–binlog-format={ROW|STATEMENT|MIXED}
Version Introduced     5.1.5
Command-Line Format     –binlog-format
Config-File Format     binlog-format
Option Sets Variable     Yes, binlog_format
Variable Name     binlog_format
Variable Scope     Both
Dynamic Variable     Yes
Permitted Values (>= 5.1.5, <= 5.1.7)
Type     enumeration
Default     STATEMENT
Valid Values     ROW, STATEMENT
Permitted Values (>= 5.1.8, <= 5.1.11)
Type     enumeration
Default     STATEMENT
Valid Values     ROW, STATEMENT, MIXED
Permitted Values (>= 5.1.12, <= 5.1.28)
Type     enumeration
Default     MIXED
Valid Values     ROW, STATEMENT, MIXED
Permitted Values (>= 5.1.29)
Type     enumeration
Default     STATEMENT
Valid Values     ROW, STATEMENT, MIXED

Specify whether to use row-based, statement-based, or mixed replication (statement-based was the default prior to MySQL 5.1.12; in 5.1.12, the default was changed to mixed replication; in 5.1.29, the default was changed back to statement-based). See Section 16.1.2, “Replication Formats”. This option was added in MySQL 5.1.5.
Important

Setting the binary logging format without enabling binary logging prevents the MySQL server from starting. This is a known issue in MySQL 5.1 which is fixed in MySQL 5.5. (Bug#42928)

MySQL Cluster.  The default value for this option in all MySQL Cluster NDB 6.1, 6.2, 6.3, and later 6.x releases is MIXED. See Section 17.6.2, “MySQL Cluster Replication: Assumptions and General Requirements”, for more information.

may your success.

Statement is not safe to log in statement format

今天收到MySQL的报警:
100607 20:55:23 [Warning] Statement is not safe to log in statement format. Statement: update users set Status = ‘6’ , gmt_modified = now() where ecpnumber = ‘057181933005’ limit 1
以前没有遇到过,觉得非常奇怪。仔细一看,update limit 1。
MySQL的limit 1返回的1行数据并不能保证下次执行返回的还是同一行,估计MySQL就是由于这个原因报警出来。提醒我们:
update users set Status = ‘6’ , gmt_modified = now() where ecpnumber = ‘057181933005’ limit 1在主机执行,并以statement记录到本机的话是不安全的。为什么列,因为它是以statement方式记录下来的。以这个方式记录下来的binlog在备机上执行将同样经过SQL解析等步骤和我们手工提交到备机的情况基本一样。这样的话,备机更新的这条数据可能不是主机更新的那条数据。结果导致主备机数据不一致。
检查了数据库的设置,果然binlog_format为STATEMENT。
问题修复
1、对已经发生的问题修复:检查发现where ecpnumber = ‘057181933005’的条件对应的数据只有一条,也就是说,limit 1和不加limit 1都是同一个数据
2、避免新问题的发生。
a)跟应用方确认,请他们修改掉limit 1的这种方式。通过select查询到底有几条,到底需要更新哪个数据,从而确切的修改那一条数据。
b)修改MySQL数据库参数,数据库这边的修改很简单。set global binlog_format=mix;

may you success