欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-22 16:40:31
一. 概述 在上一篇中,搭建了一主一从的复制架构,这篇通过一些诊断方法来了解复制的运行状态和一些选项参数说明。上次mysql主从服务关机,今天在打开mysql服务,出现了错误信息。 1.首先 启动主从mysql服务 2.在从库上执行START SLAVE, 开始复制。 3.在从库上执行SHOW PR ......

一. 概述

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

  1.首先 启动主从mysql服务

  2.在从库上执行start slave, 开始复制。

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

mysql 架构篇系列 3 复制运行状态监控与选项参数说明
  
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;

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

  -- 在从库上执行
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。如下图所示:

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

 

.复制中的各类文件

  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。再来看下复制原理图:

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

  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%'

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

-- 全部更新
update  testbackup  set `name`=concat(`name`,'hello')
--通过mysqlbinlog检查,可以发现记录是按row 每行记录的。
[root@hsr ~]# mysqlbinlog --base64-output=decode-row -v  /var/lib/mysql/mysql-bin.000072
mysql 架构篇系列 3 复制运行状态监控与选项参数说明
#181029 14:33:20 server id 1  end_log_pos 1539 crc32 0xde0ceeff     update_rows: table id 221 flags: 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语句级记录的。

mysql 架构篇系列 3 复制运行状态监控与选项参数说明
begin
/*!*/;
# at 1714
#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')
/*!*/;
# at 1860
#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刷新到磁盘的频率。

  关于sync_binlog在: mysql 开发进阶篇系列 19 mysql server(innodb_flush_log_at_trx_commit与sync_binlog)中有介绍。

-- 查看binlog格式
show variables like '%sync_binlog%'

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

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

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