首页 > 数据库技术 > 详细

mysql主从之binlog的工作模式

时间：2019-07-05 01:20:47      阅读：152      评论：0      收藏：0      [点我收藏+]

一 三种模式介绍

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()的话，数据会不一致 

row   #基于行的主从方式，保证数据一致性，binlog里面保存的是更改信息。binlog日志量大。阿里云默认使用这种方式（5.7以后默认的方式）

mixed  #statement和row格式的结合，一般使用statement

二 三种模式验证

主从复制的方式可以动态更改，无需重启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 status\G;
*************************** 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调整记录方式

mysql主从之binlog的工作模式

原文：https://www.cnblogs.com/zyxnhr/p/11135878.html
踩
(0)
赞
(0)
   
举报
评论 一句话评论（0）
分享档案
更多>
2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)
最新文章
更多>
教程昨日排行
更多>
友情链接
汇智网   PHP教程   插件网  
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣，分享程序人生！