binlog_format之 row 和 statment最佳实践-亲试ok
binlog_format 值有三个 statement row mixed，其中row通常和 binlog_row_image=minial 组合使用
目前使用的日志条目
mysql> show master status\G;
*************************** 1. row ***************************
File: log-bin.000071
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
目前的binlog 格式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
进入测试数据库
mysql> use myown;
Database changed
#创建测试表
mysql> create table my_tab(n1 int,n2 varchar(10));
Query OK, 0 rows affected (0.02 sec)
#插入测试数据
mysql> insert into my_tab values(1,'aaa'),(2,'bbb');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
#更新数据
mysql> update my_tab set n2='aaaaaa' where n1=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
确认binlog
[root@localhost binlog]# mysqlbinlog -vv log-bin.000071
#切换数据库
use `myown`/*!*/;
#建表
create table my_tab(n1 int,n2 varchar(10)) /*!*/;
# at 342
#插入测试数据
### INSERT INTO `myown`.`my_tab`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='aaa' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### INSERT INTO `myown`.`my_tab`
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='bbb' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 591
#更新数据
### UPDATE `myown`.`my_tab`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='aaa' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='aaaaaa' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 875
可见row格式会把数据变更前和变更后的数据 都拿到
修改binlog_format 为 statement
mysql> set session binlog_format='statement';
Query OK, 0 rows affected (0.01 sec)
mysql> use myown;
Database changed
#创建测试表
mysql> create table my_tab(n1 int,n2 varchar(10));
Query OK, 0 rows affected (0.01 sec)
#插入测试数据
mysql> insert into my_tab values(1,'aaa'),(2,'bbb');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
#更新测试数据
mysql> update my_tab set n2='aaaaaa' where n1=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> show master status\G;
*************************** 1. row ***************************
File: log-bin.000071
Position: 1895
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
再次确认binlog
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
#切换数据库
mysql> use myown;
Database changed
#创建测试表
mysql> create table my_tab(n1 int,n2 varchar(10));
Query OK, 0 rows affected (0.01 sec)
#插入测试数据
mysql> insert into my_tab values(1,'aaa'),(2,'bbb');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
#更新测试数据
mysql> update my_tab set n2='aaaaaa' where n1=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#确认目前的binlog
mysql> show master status\G;
*************************** 1. row ***************************
File: log-bin.000071
Position: 1895
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
#确认日志内容
[root@localhost binlog]# mysqlbinlog -vv log-bin.000071
#创建测试表
create table my_tab(n1 int,n2 varchar(10))
/*!*/;
# at 1285
#插入测试数据
insert into my_tab values(1,'aaa'),(2,'bbb')
/*!*/;
#更新测试数据
update my_tab set n2='aaaaaa' where n1=1
/*!*/;
# at 1864
哈哈 satement格式的 binlog 只会拿到被操作的那个字段。哈哈 真low比
创建时间：2021-12-22 10:25
넶浏览量：0
