MySQL二进制日志

mysql复制功能解决了什么问题

• 实现在不同服务器上的数据分布
• 实现数据读取的负载均衡
• 增强了数据安全性
• 实现数据库高可用和故障切换
• 实现数据库在线升级

mysql二进制日志

服务层日志

• 二进制日志
• 慢查日志
• 通用日志
• 存储引擎层日志
• innodb重做日志
• innodb回滚日志

二进制日志

MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的MySQL语句。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。

二进制日志的格式（段）

基于段的格式binlog_format=STATEMENT

• 优点：日志记录量相对较小,节约磁盘及网络I/O
• 缺点：可能造成MySQL复制的主备服务器数据不一致

查看是否开启
SHOW VARIABLES LIKE 'log_bin';
可以在my.ini文件中添加配置，需要重新启动
[mysqld]
log_bin = mysql_bin #日志文件名称
修改为段日志
set session binlog_format=statement;

二进制日志的格式（行）

基于行的日志格式binlog_ format= ROW
,row格式可以避免MySQL复制中出现的主从不一致问题

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.02 sec)

同一SQL语句修改了10000条数据的情况下
基于段的日志格式只会记录这个SQL语句
基于行的日志会有10000条记录分别记录每一行的数据修改

误操作而修改了数据库中的数据,同时又没有备份可以
恢复时,我们就可以通过分析二进制日志,对日志中记录
的数据修改操作做反向处理的方式来达到恢复数据的目的

如果开启了二进制日志可以，可以查看二进制日志记录的内容。

mysqlbinlog --vv mysql-bin.000003 | more

缺点

• 记录日志的量比较大

二进制日志的格式（混合）

混合日志格式binlog_format= MIXED
特点

• 根据SQL语句由系统决在基于段和基于行
  的日志格式中进行选择
• 数据量的大小由所执行的SQL语句决定

mysql二进制日志格式对复制的影响

基于SQL语名的复制( SBR )

二进制日志格式使用的是statement格式

基于SQL段的日志是在从库上重新执行记录的SQL

优点

• 生成的日志量少,节约网络转输I/O
• 并不强制要求主从数据库的表定义完全相同
• 相比于基于行的复制方式更为灵活

缺点

• 对于非确定性事件,无法保证主从复制数据的一致性
• 对于存储过程,触法器，自定义函数进行的修改也可
  能造成数据不一致
• 相比于基于行的复制方式在从上执行时需要更多的行锁

基于行的复制( RBR )

二进制日志格式使用的是基于行的日志格式

基于行的日志则是在从库上直接应用对数据库行的修改

优点

• 可以应用于任何SQL的复制包括非确定函数,存储过
  程等
• 可以减少数据库锁的使用

缺点

• 要求主从数据库的表结构相同,否则可能会中断复制
• 无法在从上单独执行触法器（基于行的复制是直接在从服务器上面应用，主数据库对行的修改，而不是在从服务器上面重新执行sql）

混合模式

根据实际内容在以上两者间切换

mysql复制工作方式

• 1.主将变更写入二进制日志
• 2.从读取主的二进制日志变更并写入到relay_log中（基于日志点的复制、基于GTID的复制）
• 3.在从上重放relay_log中的日志

基于日志点的复制

基于日志点的复制配置步骤

在主DB服务器上建立复制账号

CREATE USER 'repl' @' IP段' identified by 'PassWord';
GRANT REPLICATION SLAVE ON *.* TO 'repl' @ 'IP段';

配置主数据库服务器

log_bin = mysql-bin
server_id = 1

配置从数据库服务器

log_bin = mysql-bin
server_id = 2
relay_log = mysql-relay-bin
log_slave_update = on [可选]
read_only = on [可选]

relay_log：默认是主机的名称，如果不指定的话，日后修改主机名称会出现问题
log_slave_update：（连路复制，如果需要把从服务器当作主服务器进行复制的话就需要配置）
read_only：避免由于从服务器配置错误，造成对从服务器数据修改的发生。

初始化从服务器数据

mysqldump对主数据库进行备份，该方式是需要对数据库进行加锁的,如果业务访问量非常频繁的话，使用该方式会造成阻塞。会影响数据库的并发性。master-data记录了二进制日志文件的偏移量信息。--single-transaction混合的存储引擎就需要使用其他的参数。

xtrabackup是一个数据库热备份软件，如果存储引擎都是innodb的话进行备份是不会产出阻塞的。混合的存储引擎就会。

mysqldump --master-data=2 --single-transaction
xtrabackup --slave-info

启动复制连路,在从库中使用

CHANGE MASTER TO MASTER_HOST= 'master_host_ip',
MASTER_USER= 'repl'
MASTER_PASSWORD= 'PassWord'
MASTER_LOG_FILE= 'mysql_log_file_name',
MASTER_LOG_POS=4; 

实际操作(yum安装的mysql 5.7)

主库创建复制用户和授权

create user repl@'192.168.233.%' identified by '123456';
grant replication slave on *.* to repl@' 192.168.233.%';

修改my.cnf配置文件，在/etc/目录下
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kAXEQ6Ue-1623547394676)(15A27AAEB9884646A5362FB19E1E0560)]

重启数据库

备份数据库之前需要把主库给锁了，然后再进行备份。否则在备份还没导入的时间段又插入数据就会造成数据不一致的问题。

# 加锁
flush tables with read lock;
# 解锁
unlock tables;

备份主库数据库，使用mysqldump的方式

• 如果主从数据库的版本不一致的话最好是只备份业务数据库，如果把系统数据库也备份的话可能会因为版本不一致出现问题。

mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot -p >> /opt/all.sql;

• mysql5.6使用mysqldump备份时，指定备份的具体库，使用–database
• mysql5.7使用mysqldump备份时，指定备份的具体库，使用–databases

从129服务器把all.sql发送到130上面

scp -r all.sql root@192.168.233.130:/opt/

从服务器修改重启
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nIu2S58i-1623547394678)(9970044E4BC44AC3804B0E2243640C89)]

导入从库，使得主从数据一致。

[root@hadoop130 opt]# mysql -uroot -p<all.sql

登陆mysql，配置复制连路，MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=154，文件以及偏移量，可以从all.sql文件里面查看。

或者使用mysql命令行查看

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |     154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)

mysql> CHANGE MASTER TO MASTER_HOST= '192.168.233.129',
    -> MASTER_USER= 'repl',
    -> MASTER_PASSWORD= '123456',
    -> MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

启动

mysql> start slave;

mysql> show slave status \G

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wau27Ijj-1623547394679)(6BD592B857624192AD499AAD70AC2DFF)]

优点

• 是MySQL最早支持的复制技术, Bug相对较少
• 对SQL查询没有任何限制
• 故障处理比较容易

缺点

• 故障转移时重新获取新主的日志点信息比较困难

基于GTID的复制

从5.6版本开始有

基于日志点的复制如果偏移量弄错了有可能出现重复复制或者漏复制的情况，而基于GTID的复制同一事务只在从库中执行一次。

基于GTID的配置步骤

GTID即全局事务ID ,其保证为每一个在主库上提交的事务
在复制集群中可以生成一个唯一的ID
GTID=source_id:transaction_id

配置主数据库服务器

#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
    
#binlog
log_bin = mysql-bin
log-slave-updates = on
binlog_format = row
sync-master-info = 1
sync_binlog = 1

enforce-gtid-consiste(强制gtid一致性)
影响：1.不能使用create table ... select 这样的语句
     2.在事务中使用Create temporar table建立临时表使用关联更新事务表和非事务表会报错
log-slave-updates = on（在从服务器记录主服务器传来的修改日志）
注意：mysql>=5.7可以不需要这个参数了。
参考：https://blog.51cto.com/hcymysql/1579197

配置从数据库服务器

#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on

#binlog
log_bin = mysql-bin
log-slave-updates = on
binlog_format = row
sync-master-info = 1
sync_binlog = 1

#relay log
skip_slave_start = 1
read_only = on[建议]

master_info_repository =TABLE [建议]
relay_log_info_repository =TABLE [建议]


后面两个配置指定从服务器连接主服务器的信息，以及中间日志的存储方式，默认是存储在文件中的，通过上面的配置可以把信息存储到数据库的表中，当出现数据库崩溃时可以通过innodb事务的特性进行恢复，以保证从服务器可以正确的位置进行复制。

skip_slave_start = 1 [建议]
参考：https://www.cnblogs.com/liang545621/p/9400031.html

创建复制账号

初始化从服务器数据库，保持主从一致

启动基于GTID的复制

CHANGE MASTER TO MASTER_HOST= 'master_host_ip' ,
MASTER_USER= 'repl',
MASTER_PASSWORD= 'PassWord',
MASTER_AUTO_POSITION = 1;

案例操作

1.在129的主库中创建用于复制的用户，在上面已经创建过了。

2.主库添加参数，相比于之前新增两个

重启mysql

3.从库添加参数

4.初始化数据，把129服务器上面数据恢复到130上面

mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot -p >> /opt/all_gtid.sql;

把sql文件发送到130上面

scp -r all_gtid.sql root@192.168.233.130:/opt/

导入从库，使得主从数据一致。

[root@hadoop130 opt]# mysql -uroot -p123456<all_gtid.sql

5.在130的从库上面配置主从,需要先关闭

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> CHANGE MASTER TO MASTER_HOST= '192.168.233.129',
    -> MASTER_USER= 'repl',
    -> MASTER_PASSWORD= '123456',
    -> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

优点

• 可以很方便的进行故障转移(全局唯一的事务id)
• 从库不会丢失主库上的任何修改

缺点

• 故障处理比较复杂
• 对执行的SQL有一定的限制

选择复制模式要考虑的问题

• 所使用的MySQL版本
• 复制架构及主从切换的方式
• 所使用的高可用管理组件
• 对应用的支持程度

复制架构

MySQL5.7之前, 一个从库只能有一个主库

MySQL5.7之后支持一从多主架构

一主多从的架构

优点

• 配置简单
• 可以用多个从库分担读负载

用途

• 为不同业务使用不同的从库
• 将一台从库放到远程IDC,用作灾备恢复
• 分担主库的读负载

Mysql复制常见问题处理

由于数据损坏或丢失所引|起的主从复制错误

• 主库或从库意外宕机弓|起的错误

使用跳过二进制日志事件
注入空事务的方式先恢复中断的复制链路
再使用其它方法来对比主从服务器上的数据

• 主库上的二进制日志损坏

• 备库上的中继日志损坏

• 在从库上进行数据修改造成的主从复制错误

• 不唯一的server_id或server_uuid

server_uuid是记录在数据目录中的auto.cnf文件中

• max_allow_packet设置引起的主从复制错误