一. 概述
在上一篇中，搭建了一主一从的复制架构，这篇通过一些诊断方法来了解复制的运行状态和一些选项参数说明。上次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 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'
-- 现在将binlog格式从row 改为 statement 格式 -- 当前会话 set binlog_format='statement'
-- 全部更新 update testbackup set `name`=concat(`name`,'hello123')
再通过mysqlbinlog检查，可以发现记录是按statement语句级记录的。
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/*!*/;
总结：在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%'
当sync_binlog=1时，是最安全的，当主机突然断点，系统最多损失最近的一条事务的数据。但是在多个事务并发提交时，mysql不得不按顺序处理请求，同时高频率的刷新binlog对i/o影响明显，很大程度影响了mysql的性能。
所以一般线上系统mysql的sync_binlog不会设置为1, 而是2或0,在数据安全性和更高的并发之间获取一个平衡。
如对本文有疑问， 点击进行留言回复！！
MySql添加新用户及为用户创建数据库和给用户分配权限方法介绍
Navicat出现无法远程连接MySql服务器问题的解决办法
我要评论
网友评论