一 三种模式介绍
1.1 查看mysql主库的binlog格式
binlog仅在主库设置即可，从库无需设置
binlog的默认方式为STATEMENT ( show variables like '%binlog_format%'; )
mysql> mysql> show variables like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | #默认ROW +---------------+-------+
1.2 mysql主库的binlog有三种方式
statement #最古老的方式，基于sql的主从复制。binlog里面保存的都是sql语句，binlog日志量小。但如果像uuid()的话，数据会不一致
优点：statement level 下的优点，首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin‐log 日志量，节约io，提高性能。因为他只需要记录在master 上所执行的语句的细节，以及执行语句时候的上下文的信息。
缺点：由于它是记录的执行语句，所以为了让这些语句在slave 端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave 端被执行的时候能够得到和在master 端执行时候相同的结果。另外就是,由于mysql 现在发展比较快，很多的新功能加入，使mysql 的复制遇到了不小的挑战,自然复制的时候涉及到越复杂的内容，bug 也就越容易出现。在statement level 下，目前已经发现的就有不少情况会造成mysql 的复制问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如sleep()在有些版本就不能正确复制。
row #基于行的主从方式，保证数据一致性，binlog里面保存的是更改信息。binlog日志量大。阿里云默认使用这种方式（5.7以后默认的方式）
优点：bin‐log 中可以不记录执行的sql 语句的上下文相关的信息，仅仅只需要记录哪一条记录被修改了，修改成什么样了。所以row level 的日志的内容会非常清楚的记录下每一行数据修改的细节。
缺点：row level 下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改记录，这样可能会产生大量的日志内容，比如有这样一条update 语句：update product set owner_member_id='d',执行之后，日志中记录的不是这条update 语句所对应的event 事件(mysql 是以事件的形式来记录bin‐log 日志)，而是这条语句所更新的每一条记录的变化情况，这样就记录了很多条记录被更新的事件。因此，bin‐log日志的量会很大。
mixed :
实际上就是前两种模式的结合，在mixed 模式下，mysql 会根据执行的每一条具体的sql 语句来区分对待记录的日志形式，也就是在statement 和row 之间选一种。新版本中的statement level 还是和以前一样，仅仅记录执行的语句。而新版本的mysql 中对row level 模式被做了优化，并不是所有的修改都会以row level 来记录，像遇到表结构变更的时候就会以statement 模式来记录，如果sql 语句确实就是update或者delete 等修改数据的语句，那么还是会记录所有行的变更。
二 三种模式验证
主从复制的方式可以动态更改，无需重启mysql
2.1 statement模式
把模式改为statement
mysql> set global binlog_format = STATEMENT; #针对当前的会话，会话退出后就会还原成STATEMENT Query OK, 0 rows affected (0.00 sec) mysql> set binlog_format = statement #针对当前的会话，会话退出后就会还原成STATEMENT -> ; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%binlog_format%'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+
mysql配置文件新增binlog_format = STATEMENT #永久把binlog格式替换为statement
[mysqld] bind-address=0.0.0.0 port=3306 datadir=/data/mysql socket=/data/mysql/mysql.sock user=mysql skip-name-resolve slow_query_log=on long_query_time=1 slow_query_log_file=/data/mysql/mysql-slow.log innodb-file-per-table=1 innodb_flush_log_at_trx_commit = 2 log_warnings = 1 connect_timeout = 60 net_read_timeout = 120 performance_schema_max_table_instances = 400 server-id = 1 log-bin binlog_format = STATEMENT
使用命令，查看binlog记录的内容
mysql> show master logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | mater1-bin.000001 | 31050 | +-------------------+-----------+ 1 row in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> show master logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | mater1-bin.000001 | 31098 | | mater1-bin.000002 | 154 | +-------------------+-----------+ 2 rows in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | darren | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> create database darren1; Query OK, 1 row affected (0.00 sec) mysql> use darren1; Database changed mysql> create table test (id int); Query OK, 0 rows affected (0.01 sec) mysql> insert into test values (1); Query OK, 1 row affected (0.01 sec) mysql> update test set id=3 where id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test; +------+ | id | +------+ | 3 | +------+ 1 row in set (0.00 sec) mysql> show binlog events in 'mater1-bin.000002'; +-------------------+------+----------------+-----------+-------------+--------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+------+----------------+-----------+-------------+--------------------------------------------------+ | mater1-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 | | mater1-bin.000002 | 123 | Previous_gtids | 1 | 154 | | | mater1-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mater1-bin.000002 | 219 | Query | 1 | 322 | create database darren1 | | mater1-bin.000002 | 322 | Anonymous_Gtid | 1 | 387 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mater1-bin.000002 | 387 | Query | 1 | 493 | use `darren1`; create table test (id int) | | mater1-bin.000002 | 493 | Anonymous_Gtid | 1 | 558 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mater1-bin.000002 | 558 | Query | 1 | 643 | BEGIN | | mater1-bin.000002 | 643 | Query | 1 | 750 | use `darren1`; insert into test values (1) | | mater1-bin.000002 | 750 | Xid | 1 | 781 | COMMIT /* xid=659 */ | | mater1-bin.000002 | 781 | Anonymous_Gtid | 1 | 846 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mater1-bin.000002 | 846 | Query | 1 | 931 | BEGIN | | mater1-bin.000002 | 931 | Query | 1 | 1044 | use `darren1`; update test set id=3 where id = 1 | | mater1-bin.000002 | 1044 | Xid | 1 | 1075 | COMMIT /* xid=660 */ | +-------------------+------+----------------+-----------+-------------+--------------------------------------------------+
从端也同步
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | darren | | darren1 | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.132.121 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mater1-bin.000002 Read_Master_Log_Pos: 1075 Relay_Log_File: slave1-relay-bin.000004 Relay_Log_Pos: 1290 Relay_Master_Log_File: mater1-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes
row格式主要是记录了更改，statement记录的是现实的sql语句，数据不安全
ROW格式数据安全，但binlog的日志量比较大，试想，如果删除、更新了上百万行的话
statement会导致数据不一致，如下面语句
mysql> alter table test add age varchar(50); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into test values (2,UUID()); #插入一个随机数 Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from test; +------+--------------------------------------+ | id | age | +------+--------------------------------------+ | 3 | NULL | | 2 | 2d64d412-9e72-11e9-a2f9-000c2991dd19 | +------+--------------------------------------+ 从端： mysql> select * from darren1.test; +------+--------------------------------------+ | id | age | +------+--------------------------------------+ | 3 | NULL | | 2 | 2d666978-9e72-11e9-8aca-000c2963fd11 | +------+--------------------------------------+
发现数据不一致
2.2 使用ROW格式
ysql> delete from test; Query OK, 2 rows affected (0.01 sec) mysql> select * from test; Empty set (0.00 sec) mysql> insert into test values (2,UUID()); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +------+--------------------------------------+ | id | age | +------+--------------------------------------+ | 2 | 74ee4ad0-9e73-11e9-a2f9-000c2991dd19 | +------+--------------------------------------+ 从端： mysql> select * from darren1.test; +------+--------------------------------------+ | id | age | +------+--------------------------------------+ | 2 | 74ee4ad0-9e73-11e9-a2f9-000c2991dd19 | +------+--------------------------------------+
完全一致的
使用mysqlbinlog命令可以查看row格式的binlog
[root@master mysql]# mysqlbinlog -vv --base64-output=decode-rows master-bin.000001
# at 1857 #190704 11:50:33 server id 1 end_log_pos 1922 CRC32 0x365a53d7 Anonymous_GTID last_committed=7 sequence_number=8 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1922 #190704 11:50:33 server id 1 end_log_pos 1997 CRC32 0x9d17ec5e Query thread_id=133 exec_time=0 error_code=0 SET TIMESTAMP=1562255433/*!*/; BEGIN /*!*/; # at 1997 #190704 11:50:33 server id 1 end_log_pos 2050 CRC32 0x91bb97b1 Table_map: `darren1`.`test` mapped to number 110 # at 2050 #190704 11:50:33 server id 1 end_log_pos 2127 CRC32 0x8c5aca9e Write_rows: table id 110 flags: STMT_END_F ### INSERT INTO `darren1`.`test` ### SET ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ ### @2='74ee4ad0-9e73-11e9-a2f9-000c2991dd19' /* VARSTRING(50) meta=50 nullable=1 is_null=0 */ #记录的是一个变化值 # at 2127 #190704 11:50:33 server id 1 end_log_pos 2158 CRC32 0x7f33c342 Xid = 695 COMMIT/*!*/; # at 2158 #190704 11:53:53 server id 1 end_log_pos 2206 CRC32 0xd55138bd Rotate to mater1-bin.000003 pos: 4 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file
2.3 MIXED模式
混合格式的binlog，想让binlog日志量小，而且数据一致还有保证
主端操作
mysql> set binlog_format = 'MIXED'; Query OK, 0 rows affected (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.00 sec) mysql> create database darren; Query OK, 1 row affected (0.00 sec) mysql> use darren; Database changed mysql> create table test( id int, name varchar(100) ); Query OK, 0 rows affected (0.01 sec) mysql> insert into test values (1, 'darren'); Query OK, 1 row affected (0.00 sec) mysql> update test set id = 3 where id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> insert into test values (2, UUID()); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +------+--------------------------------------+ | id | name | +------+--------------------------------------+ | 3 | darren | | 2 | b86c02cf-9e74-11e9-a2f9-000c2991dd19 | +------+--------------------------------------+
从端对比
mysql> select * from darren.test; +------+--------------------------------------+ | id | name | +------+--------------------------------------+ | 3 | darren | | 2 | b86c02cf-9e74-11e9-a2f9-000c2991dd19 | +------+--------------------------------------+
数据也是一致的
日志记录
mysql> mysql> show binlog events in 'mater1-bin.000004'; +-------------------+------+----------------+-----------+-------------+--------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+------+----------------+-----------+-------------+--------------------------------------------------------------+ | mater1-bin.000004 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 | | mater1-bin.000004 | 123 | Previous_gtids | 1 | 154 | | | mater1-bin.000004 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mater1-bin.000004 | 219 | Query | 1 | 319 | create database darren | | mater1-bin.000004 | 319 | Anonymous_Gtid | 1 | 384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mater1-bin.000004 | 384 | Query | 1 | 508 | use `darren`; create table test( id int, name varchar(100) ) | | mater1-bin.000004 | 508 | Anonymous_Gtid | 1 | 573 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mater1-bin.000004 | 573 | Query | 1 | 656 | BEGIN | | mater1-bin.000004 | 656 | Query | 1 | 771 | use `darren`; insert into test values (1, 'darren') | | mater1-bin.000004 | 771 | Xid | 1 | 802 | COMMIT /* xid=728 */ | | mater1-bin.000004 | 802 | Anonymous_Gtid | 1 | 867 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mater1-bin.000004 | 867 | Query | 1 | 950 | BEGIN | | mater1-bin.000004 | 950 | Query | 1 | 1063 | use `darren`; update test set id = 3 where id = 1 | | mater1-bin.000004 | 1063 | Xid | 1 | 1094 | COMMIT /* xid=729 */ | | mater1-bin.000004 | 1094 | Anonymous_Gtid | 1 | 1159 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mater1-bin.000004 | 1159 | Query | 1 | 1233 | BEGIN | | mater1-bin.000004 | 1233 | Table_map | 1 | 1285 | table_id: 114 (darren.test) | | mater1-bin.000004 | 1285 | Write_rows | 1 | 1362 | table_id: 114 flags: STMT_END_F | | mater1-bin.000004 | 1362 | Xid | 1 | 1393 | COMMIT /* xid=730 */ | +-------------------+------+----------------+-----------+-------------+--------------------------------------------------------------+
根据SQL调整记录方式