安阳网站建设开发,建筑模拟2015,建站宝盒,今天汽油价格前言:大家平时在检查主从复制的时候基本上都会使用 show slave status来看主从复制的情况#xff0c;那么你真的读懂每个指标项了吗#xff1f;一、status指标解读rootlocalhost [(none)]show slave status\GERROR 2006 (HY000): MySQL server has gone awayNo connectio…前言:大家平时在检查主从复制的时候基本上都会使用 show slave status来看主从复制的情况那么你真的读懂每个指标项了吗一、status指标解读rootlocalhost [(none)]show slave status\GERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 7Current database: *** NONE ****************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.50.3Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 728Relay_Log_File: relay-bin.000002Relay_Log_Pos: 941Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 728Relay_Log_Space: 1142Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1003306Master_UUID: dff47046-3ad5-11e8-86fe-080027de0e0eMaster_Info_File: /storage/mysql/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set: dff47046-3ad5-11e8-86fe-080027de0e0e:1-3Executed_Gtid_Set: dff47046-3ad5-11e8-86fe-080027de0e0e:1-3Auto_Position: 1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1 row in set (0.00 sec)指标注释Slave_IO_State: Waiting for master to send event表示IO线程的状态Master_Host: 192.168.50.3Master的IP地址Master_User: repl主从复制的用户Master_Port: 3306Master的端口Connect_Retry: 60IO thread重连次数Master_Log_File: mysql-bin.000001IO thread正在读取的master上的binlog文件Read_Master_Log_Pos: 728正在读取的master上的binlog文件位置Relay_Log_File: relay-bin.000002存放的本地relay log文件名Relay_Log_Pos: 941存放的本地relay log位置Relay_Master_Log_File: mysql-bin.000001SQL线程正在执行的master binlog fileSlave_IO_Running: YesIO thread状态Slave_SQL_Running: YesSQL thread状态Replicate_Do_DB:指定复制DBReplicate_Ignore_DB:指定复制忽略Replicate_Do_Table:指定复制表Replicate_Ignore_Table:指定忽略表Replicate_Wild_Do_Table:指定复制表解决跨库的问题Replicate_Wild_Ignore_Table:指定忽略表解决跨库的问题Last_Errno: 0最近的复制错误Last_Error:最近一次错误信息Skip_Counter: 0跳过复制数Exec_Master_Log_Pos: 728执行masterbinlog的位置Relay_Log_Space: 1142所有relay log字节数总和Until_Condition: None指定复制条件Until_Log_File:指定复制到某个文件Until_Log_Pos: 0指定复制到某个位置Seconds_Behind_Master: 0从库落后主库时间单位秒Last_IO_Errno: 0最近一次IO thread错误Last_IO_Error最近一次IO thread错误详细信息Last_SQL_Errno: 0最近一次sql thread错误Last_SQL_Error:最近一次sql thread错误详细信息Replicate_Ignore_Server_Ids:复制忽略server_id为xxx的实例Master_Server_Id: 1003306master server_idMaster_UUID: dff47046-3ad5-11e8-86fe-080027de0e0emaster uuidMaster_Info_File: /storage/mysql/data/master.info记录master信息文件SQL_Delay: 0sql延迟多少时间延迟复制会用到SQL_Remaining_Delay: NULL当sql thread状态为Waiting until MASTER_DELAY seconds after master executed event那么该值表示剩下延迟多少时间Slave_SQL_Running_State: Slave has read all relay log; waiting for more updatessql thread状态Master_Retry_Count: 86400主从复制断开时slave最多能尝试重新连接的次数Master_Bind:绑定网卡Last_IO_Error_Timestamp:io thread 最近一次的错误时间Last_SQL_Error_Timestamp:sql thread 最近一次的错误时间Retrieved_Gtid_Set: dff47046-3ad5-11e8-86fe-080027de0e0e:1-3IO thread获取到的GTIDExecuted_Gtid_Set: dff47046-3ad5-11e8-86fe-080027de0e0e:1-3SQL thread执行的GTIDAuto_Position: 1使用auto_position建议还是开启比较方便Replicate_Rewrite_DB:复制DB对应关系Channel_Name:多源复制的channel name下面我们逐个来解读一下指标注释Slave_IO_State: Waiting for master to send event表示IO线程的状态Master_Host: 192.168.50.3Master的IP地址Master_User: repl主从复制的用户Master_Port: 3306Master的端口Connect_Retry: 60IO thread重连次数Master_Log_File: mysql-bin.000001IO thread正在读取的master上的binlog文件Read_Master_Log_Pos: 728正在读取的master上的binlog文件位置Relay_Log_File: relay-bin.000002存放的本地relay log文件名Relay_Log_Pos: 941存放的本地relay log位置Relay_Master_Log_File: mysql-bin.000001SQL线程正在执行的master binlog fileSlave_IO_Running: YesIO thread状态Slave_SQL_Running: YesSQL thread状态Replicate_Do_DB:指定复制DBReplicate_Ignore_DB:指定复制忽略Replicate_Do_Table:指定复制表Replicate_Ignore_Table:指定忽略表Replicate_Wild_Do_Table:指定复制表解决跨库的问题Replicate_Wild_Ignore_Table:指定忽略表解决跨库的问题Last_Errno: 0最近的复制错误Last_Error:最近一次错误信息Skip_Counter: 0跳过复制数Exec_Master_Log_Pos: 728执行masterbinlog的位置Relay_Log_Space: 1142所有relay log字节数总和Until_Condition: None指定复制条件Until_Log_File:指定复制到某个文件Until_Log_Pos: 0指定复制到某个位置Seconds_Behind_Master: 0从库落后主库时间单位秒Last_IO_Errno: 0最近一次IO thread错误Last_IO_Error最近一次IO thread错误详细信息Last_SQL_Errno: 0最近一次sql thread错误Last_SQL_Error:最近一次sql thread错误详细信息Replicate_Ignore_Server_Ids:复制忽略server_id为xxx的实例Master_Server_Id: 1003306master server_idMaster_UUID: dff47046-3ad5-11e8-86fe-080027de0e0emaster uuidMaster_Info_File: /storage/mysql/data/master.info记录master信息文件SQL_Delay: 0sql延迟多少时间延迟复制会用到SQL_Remaining_Delay: NULL当sql thread状态为Waiting until MASTER_DELAY seconds after master executed event那么该值表示剩下延迟多少时间Slave_SQL_Running_State: Slave has read all relay log; waiting for more updatessql thread状态Master_Retry_Count: 86400主从复制断开时slave最多能尝试重新连接的次数Master_Bind:绑定网卡Last_IO_Error_Timestamp:io thread 最近一次的错误时间Last_SQL_Error_Timestamp:sql thread 最近一次的错误时间Retrieved_Gtid_Set: dff47046-3ad5-11e8-86fe-080027de0e0e:1-3IO thread获取到的GTIDExecuted_Gtid_Set: dff47046-3ad5-11e8-86fe-080027de0e0e:1-3SQL thread执行的GTIDAuto_Position: 1使用auto_position建议还是开启比较方便Replicate_Rewrite_DB:复制DB对应关系Channel_Name:多源复制的channel name二、日常运维注意点在GTID环境下主从同步延迟观察就非常简单了我们只需要观察Retrieved_Gtid_Set表示的就是io thread读取到的位置Executed_Gtid_Set表示的就是sql thread执行到的位置在非GTID环境下主从复制观察相对来说稍微麻烦一些(Master_Log_file, Read_Master_Log_Pos): 表示io thread读取到的master binlog位置Relay_Master_Log_File, Exec_Master_Log_Pos): 表示sql thread已经执行到的位置(Relay_Log_File, Relay_Log_Pos): 表示sql thread执行到的relay log位置Seconds_Behind_Master表示slave延迟master的时间数并不是很准确计算公式是:io_thread.timestamp-sql_thread.timestamp