这篇文章主要为大家展示了“MySQL中show slave status关键值和MGRrelay log的清理策略”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL中show slave status关键值和MGRrelay log的清理策略”这篇文章吧。
武穴网站制作公司哪家好，找创新互联公司！从网页设计、网站建设、微信开发、APP开发、成都响应式网站建设公司等网站项目制作，到程序开发，运营维护。创新互联公司从2013年创立到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联公司。
一、show slave status关键值
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event (IO THREAD状态)
Master_Host: 192.168.99.42(通道主库IP)
Master_User: repl991(同步用户名)
Master_Port: 3310(端口)
Connect_Retry: 60(IO thread 重试时间)
Master_Log_File: binlog.000002(读取到的binlog文件名)
Read_Master_Log_Pos: 44688(读取到的binlog位置)
Relay_Log_File: test-relay-bin.000003(本IO THREAD replay文件名)
Relay_Log_Pos: 24360(当前写到relay log的位置)
Relay_Master_Log_File: binlog.000002(SQL thread应用到的binlog的文件名)
Slave_IO_Running: Yes (IO thread状态是否正常)
Slave_SQL_Running: Yes (SQL THREAD状态是否正常)
...
Last_Errno: 0 (错误号)
Last_Error: (错误信息)
Skip_Counter: 0
Exec_Master_Log_Pos: 44688 (SQL thread应用到的binlog的位置)
Relay_Log_Space: 44599 (relay 日志文件总的的大小)
...
Seconds_Behind_Master: 0 (延迟)
...
Master_Server_Id: 9942 (主库的server_id)
Master_UUID: 0e733bbb-a594-11e7-ab07-52540020afef (主库的UUID)
Master_Info_File: /root/mysql5.7.14/percona-server-5.7.14-7/mysql-test/var/mysqld.1/data/master.info (master info的文件或者表)
SQL_Delay: 0(是否配置延时应用)
...
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates (sql thread状态)
Master_Retry_Count: 86400 (可以重试的总次数)
...
Retrieved_Gtid_Set: 0e733bbb-a594-11e7-ab07-52540020afef:9-129 (收到的GTID)
Executed_Gtid_Set: 0e733bbb-a594-11e7-ab07-52540020afef:1-129 (应用的GTID)
Auto_Position: 1 (是否通过GTID自动寻找binlog位置)
...
Channel_Name: 通道名
...
二、MGR relaylog 清理策略
普通sql线程删除relay文件
#0 MYSQL_BIN_LOG::purge_logs (this=0x37ea570, to_log=0x7fff2400d1a0 "./test-relay-bin.000004", included=false, need_lock_index=false, need_update_threads=false, decrease_log_space=0x37ec628, auto_purge=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:5950#1 0x000000000185073f in MYSQL_BIN_LOG::purge_first_log (this=0x37ea570, rli=0x37e9b30, included=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:5818#2 0x0000000001892224 in next_event (rli=0x37e9b30) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_slave.cc:9299#3 0x0000000001886443 in exec_relay_log_event (thd=0x7fff24000950, rli=0x37e9b30) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_slave.cc:5145#4 0x000000000188d092 in handle_slave_sql (arg=0x3790690) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_slave.cc:7387#5 0x0000000001d7b4b0 in pfs_spawn_thread (arg=0x7fff3c01b5f0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#6 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0#7 0x0000003f740e8bcd in clone () from /lib64/libc.so.6
MGR group_replication_applier通道的sql线程删除relay文件
#0 MYSQL_BIN_LOG::purge_logs (this=0x7fff9c0562f0, to_log=0x7fff800160b0 "./gp4-relay-bin-group_replication_applier.000006", included=false, need_lock_index=false, need_update_threads=false, decrease_log_space=0x7fff9c058320, auto_purge=true) at /root/softm/percona-server-5.7.22-22/sql/binlog.cc:6391#1 0x0000000001870333 in MYSQL_BIN_LOG::purge_first_log (this=0x7fff9c0562f0, rli=0x7fff9c0558a0, included=false) at /root/softm/percona-server-5.7.22-22/sql/binlog.cc:6259#2 0x00000000018b6bcf in next_event (rli=0x7fff9c0558a0) at /root/softm/percona-server-5.7.22-22/sql/rpl_slave.cc:9480#3 0x00000000018aa5a6 in exec_relay_log_event (thd=0x7fff80007e10, rli=0x7fff9c0558a0) at /root/softm/percona-server-5.7.22-22/sql/rpl_slave.cc:5193#4 0x00000000018b1980 in handle_slave_sql (arg=0x7fff9c04e850) at /root/softm/percona-server-5.7.22-22/sql/rpl_slave.cc:7543#5 0x0000000001932112 in pfs_spawn_thread (arg=0x7fff9803ff60) at /root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190#6 0x00007ffff7bc6aa1 in start_thread () from /lib64/libpthread.so.0#7 0x00007ffff6719bcd in clone () from /lib64/libc.so.6
可以看出没有什么区别。实际上都是一样的还是通过sql线程应用了event后删除。当然有如下代码 受到参数relay_log_purge控制
if (relay_log_purge)
{ /*
purge_first_log will properly set up relay log coordinates in rli.
If the group's coordinates are equal to the event's coordinates
(i.e. the relay log was not rotated in the middle of a group),
we can purge this relay log too.
We do ulonglong and string comparisons, this may be slow but
- purging the last relay log is nice (it can save 1GB of disk), so we
like to detect the case where we can do it, and given this,
- I see no better detection method
- purge_first_log is not called that often
*/
if (rli->relay_log.purge_first_log
(rli,
rli->get_group_relay_log_pos() == rli->get_event_relay_log_pos()
&& !strcmp(rli->get_group_relay_log_name(),rli->get_event_relay_log_name())))
{
errmsg = "Error purging processed logs"; goto err;
}
DBUG_PRINT("info", ("next_event group master %s %lu group relay %s %lu event %s %lu\n",
rli->get_group_master_log_name(),
(ulong) rli->get_group_master_log_pos(),
rli->get_group_relay_log_name(),
(ulong) rli->get_group_relay_log_pos(),
rli->get_event_relay_log_name(),
(ulong) rli->get_event_relay_log_pos()));
} elseflush logs等命令不会影响MGR的repay log包括recovery通道和applier通道
以上是“MySQL中show slave status关键值和MGRrelay log的清理策略”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注创新互联行业资讯频道！
