mysql 复制状态说明_mysql 架构篇系列 3 复制运行状态监控与选项参数说明

一. 概述

在上一篇中，搭建了一主一从的复制架构，这篇通过一些诊断方法来了解复制的运行状态和一些选项参数说明。上次mysql主从服务关机,今天在打开mysql服务，出现了错误信息。

1.首先 启动主从mysql服务

2.在从库上执行START SLAVE， 开始复制。

3.在从库上执行SHOW PROCESSLIST;slave已经连接上master,并开始接受并执行日志。

4.在从库上执行SHOW  SLAVE STATUS，查看复制状态

错误信息: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

5. 解决方法

--在主库上执行,记录日志文件号和位置，如下图所示：

flush logs;

show master status;

--在从库上执行

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000072',MASTER_LOG_POS=154;

SLAVE START;

从库上再执行SHOW PROCESSLIST，显示了二个复制进程：一个是 I/O线程，连接master,id为7。 一个是SQL线程,id为8。如下图所示：

二 .复制中的各类文件

myql复制中涉及了两类非常重要的日志文件：二进制日志文件(binlog), 中继日志文件(relay log)。 binlog文件会把mysql中所有数据修改操作以二进制形式记录，包括create,drop,insert,update,delete操作等，但不记录查询select操作。对于二进制binlog文件格式,三种支持类型包括：Statement，Row，Mixed ( 在mysql 架构篇第一篇中有讲到)。

2.1 从库relay log 中继日志文件

从库中继日志文件relay log的文件格式，内容和主库二进制日志文件binlog一样，唯一的区别在于从库上的sql线程在执行完当前中继日志文件relay log中的事件之后，sql 线程会自动删除当前中继日志文件realy log,避免从库上的中继日志文件relay log占用过多的磁盘空间。

2.2 从库 master.info和realy-log.info文件

为了保证从库crash重启后，从库的I/O线程仍然能够知道从哪里开始复制，从库上默认还会创建二个日志文件master.info 和realy-log.info 用来保存复制的进度。 这两个文件在磁盘上以文件形式记录，位置在data目录下。I/O线程维护master.info 中主库二进制日志binlog的进度。sql线程维护realy-log.info 应用中继日志relay log 的进度。 总结是: I/O线程关联master.info，sql线程关联realy-log.info。再来看下复制原理图：

2.3 复制状态信息

在从库上通过SHOW SLAVE STATUS，能了解复制的状态, 里面的信息包含了master.info和realy-log.info的信息。

(1) master.info对应的信息如下：

Master_Host

172.168.18.201

主库IP

Master_User

rep1

主库上用于复制的账号

Master_Port

3306

主库mysql 端口

Master_Log_File

mysql-bin.000072

从库I/O线程当前读取主库binlog的文件名

Read_Master_Log_Pos

514

从库I/O线程读取主库binlog的位置

(2) realy-log.info对应的信息如下：

Relay_Log_File

xuegod64-relay-bin.000005

SQL线程正在应用的relay log

Relay_Log_Pos

680

SQL线程正在应用的relay log的位置

Relay_Master_Log_File

mysql-bin.000072

SQL线程正在应用的relay log对应的binlog

Exec_Master_Log_Pos

514

SQL线程正在应用的relay log的位置对应的主库binlog的位置

(3) 其它信息

Connect_Retry

60

连接中断后，重新尝试连接的时间间隔。默认值是60秒

Slave_IO_Running

Yes

I/O线程是否被启动并成功地连接到主服务器上

Slave_SQL_Running

Yes

SQL线程是否被启动

Replicate_Do_DB

Replicate_Ignore_DB

Replicate_Do_Table

Replicate_Ignore_Table

Replicate_Wild_Do_Table

Replicate_Wild_Ignore_Table

指明哪些库或表在复制的时候不要同步到从库

Last_Error

0

SQL线程读取日志参数的的错误数量和错误消息

Skip_Counter

0

设置跳过sql执行步数

Relay_Log_Space

890

原有的中继日志结合起来的总大小

Master_SSL_Allowed

No

1) 如果允许对主服务器进行SSL连接，则值为Yes

2) 如果不允许对主服务器进行SSL连接，则值为No

3) 如果允许SSL连接，但是从属服务器没有让SSL支持被启用，则值为Ignored。

SQL_Delay

0

一个非负整数，表示秒数，Slave滞后多少秒于master

Master_Retry_Count

86400

连接主库失败最多的重试次数

2.4  主库 binlog 格式

--查看当前binlog格式

SHOW VARIABLES LIKE '%binlog_format%'

--全部更新

UPDATE testbackup SET `name`=CONCAT(`name`,'hello')

--通过mysqlbinlog检查，可以发现记录是按row 每行记录的。

[root@hsr ~]# mysqlbinlog --base64-output=decode-row -v /var/lib/mysql/mysql-bin.000072

#181029 14:33:20 server id 1 end_log_pos 1539 CRC32 0xde0ceeff Update_rows: table id 221flags: STMT_END_F

###UPDATE`test`.`testbackup`

###WHERE###@1=1###@2='张三2'###SET###@1=1###@2='张三2hello'###UPDATE`test`.`testbackup`

###WHERE###@1=2###@2='李四'###SET###@1=2###@2='李四hello'###UPDATE`test`.`testbackup`

###WHERE###@1=3###@2='五五'###SET###@1=3###@2='五五hello'###UPDATE`test`.`testbackup`

###WHERE###@1=4###@2='赵六'###SET###@1=4###@2='赵六hello'###UPDATE`test`.`testbackup`

###WHERE###@1=5###@2='小红'###SET###@1=5###@2='小红hello'###UPDATE`test`.`testbackup`

###WHERE###@1=6###@2='小明'###SET###@1=6###@2='小明hello'###UPDATE`test`.`testbackup`

###WHERE###@1=7###@2='田七'###SET###@1=7###@2='田七hello'###UPDATE`test`.`testbackup`

###WHERE###@1=8###@2='小康'###SET###@1=8###@2='小康hello'###UPDATE`test`.`testbackup`

###WHERE###@1=9###@2='小王'###SET###@1=9###@2='小王hello'###UPDATE`test`.`testbackup`

###WHERE###@1=10###@2='小李'###SET###@1=10###@2='小李hello'###UPDATE`test`.`testbackup`

###WHERE###@1=11###@2='小王'###SET###@1=11###@2='小王hello'###UPDATE`test`.`testbackup`

###WHERE###@1=12###@2='小李子1'###SET###@1=12###@2='小李子1hello'

View Code

--现在将binlog格式从row 改为 statement 格式--当前会话

SET binlog_format='statement'

--全部更新

UPDATE testbackup SET `name`=CONCAT(`name`,'hello123')

再通过mysqlbinlog检查，可以发现记录是按statement语句级记录的。

BEGIN

/*!*/;

# at1714#181029 14:41:21 server id 1 end_log_pos 1860 CRC32 0x9fdf71e9 Query thread_id=2 exec_time=0 error_code=0

use `test`/*!*/;SET TIMESTAMP=1540795281/*!*/;--全部更新

update testbackup set `name`=concat(`name`,'hello123')/*!*/;

# at1860#181029 14:41:21 server id 1 end_log_pos 1891 CRC32 0x4b7199d3 Xid = 579

COMMIT/*!*/;

View Code

总结：在binlog_format格式为row时，mysql实际上在binlog是一行行记录数据的变更。当格式为statement时是按语句级记录。 row格式比statement格式更能保证从库数据的一致性(复制是记录，而不是单纯操作sql)，但row格式下的binlog的日志量很可能会增大好几倍，在设置时需要考虑磁盘空间问题。

三. 刷新磁盘频率

对于支持事务引擎(如innodb)来说, 每个事务提交时都需要写binlog,对于不支持事务的引擎(例myisam)来说，每个sql语句执行完成时，都需要写binlog。 为了保证binlog安全,mysql引入了 sync_binlog 参数来控制binlog刷新到磁盘的频率。

--查看binlog格式

SHOW VARIABLES LIKE '%sync_binlog%'

当sync_binlog=1时，是最安全的，当主机突然断点，系统最多损失最近的一条事务的数据。但是在多个事务并发提交时，mysql不得不按顺序处理请求，同时高频率的刷新binlog对I/O影响明显，很大程度影响了mysql的性能。

所以一般线上系统mysql的sync_binlog不会设置为1, 而是2或0,在数据安全性和更高的并发之间获取一个平衡。