博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
MySQL:show slave status 关键值和MGRrelay log的清理策略
阅读量:5846 次
发布时间:2019-06-19

本文共 5349 字,大约阅读时间需要 17 分钟。

简单记录一下,有朋友问 @richard

一、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()));      }      else
  • flush logs等命令不会影响MGR的repay log包括recovery通道和applier通道

作者微信号码:gp_22389860

转载地址:http://ivzjx.baihongyu.com/

你可能感兴趣的文章
poj 2513
查看>>
————————————————————————动态规划——————————————————————1003——————————...
查看>>
PHP函数十进制、二进制、八进制和十六进制转换函数说明
查看>>
mysql 锁 for update
查看>>
推荐一款超级漂亮的HTML5 CSS3的图片轮播器
查看>>
[数据结构] 迷宫问题(栈和队列,深搜和广搜)
查看>>
从一个git仓库拷贝到另一个git仓库
查看>>
ASP.NET出错-当前上下文中不存在名称"Response" .
查看>>
Eclipse配置python环境
查看>>
第十二周总结
查看>>
SharePoint解决方案及开发系列(2)-ECM
查看>>
BeanUtils
查看>>
navicat使用
查看>>
django基础知识 ~ ModelForm
查看>>
mysql日期
查看>>
Docker - 在Ubuntu16.04中安装Docker CE
查看>>
解决一个工程问题的基本思路
查看>>
自定义ImageView之圆形、圆角、爱心、动态旗帜效果
查看>>
第一次作业 对软件工程的疑问
查看>>
自己动手写一个单链表
查看>>