MySQL复制参数及状态判断总结

 

MySQL复制参数及状态判断总结

 

1、MySQL主从复制参数

1.1、master 常用参数

server_id                      服务器在集群中唯一标识符

log_bin[=binlog_name]          启动二进制日志

log_bin_index                  二进制日志索引名称

binlog_format                  二进制日志的类型

binlog_row_image               二进制镜像保存量

binlog_do_db,binlog_ignore_db  记录在二进制日志中和不记录在二进制日志中

binlog_cache_size              缓存还没刷新到磁盘的binlog日志

max_binlog_cache_size          缓存还没刷新到磁盘的最大binlog日志

max_binlog_size                二进制日志最大值

expire_logs_days               二进制日志被保留的有效期

sync_binlog                    二进制日志刷新到磁盘频率

binlog_rows_query_log_events   二进制日志基于行，用来指定额外的信息

log_error                      记录错误日志

slow_query_log_file            记录慢查询日志位置

log_queries_not_using_indexes  查询没使用索引是否记录到慢查询日志

slow_query_log                 启用慢查询日志

log_slow_admin_statements      慢查询日志是否记录管理语句

long_query_time                多长时间记录到慢查询日志中

enforce_gtid_consistency       开启gtid的一些安全限制

gtid_mode=on                   开启gtid

 

1.2、slave常用参数

 

relay_log[=relay_log_name]            从节点中继日志名

relay_log_index                       中继日志索引名称

replicate_do_db[table]                slave只重放指定的库/表

replicate_ignore_db[table]            slave 忽略重放指定的库/表

replicate_wild_do_table               slave重放满足匹配的表

replicate_wild_ignore_table           slave忽略重放满足匹配条件的表

slave_skip_errors                     自动忽略指定错误，逗号分割

slave_exec_mode                       取值IDEMPOTENT,STRICE是否自动忽略重复主键和主键找不到错误

log_slave_updates                     启动从节点的二进制日志

relay_log_purge                       如何清除中继日志文件，默认1 自动清理

read_only                             从库只读，SUPER权限用户除外

super_read_only                       SUPER用户设置只读

skip_slave_start                      从节点跳过自动开启复制

sync_relay_log和sysnc_relay_log_info  中继日志文件同步频率，默认10000

repost_host                           区别不同的从节点，SHOW SLAVE HOSTS查看

slave_max_allowed_packet              从节点的SQL和IO线程允许最大的数据包容量

relay_log_recovery                    中继日志自动恢复，从库意外停止后使用

master_info_repository                slave master节点信息保留在位置，默认file

relay_log_info_repository             从节点信息slave保留在位置，默认file

gtid_mode=on                          开启gtid

enforce_gtid_consistency=on           开启gtid的一些安全限制

 

 

1.3、半同步复制参数

rpl_semi_sync_master_enabled               主节点开启半同步复制

rpl_semi_sync_master_timeout               半同步复制超时时间，默认10s

rpl_semi_sync_master_wait_no_slave         主节点是否需要在数据复制发生后等待一段时间(在master_timeout超时时间内，当slave的数量少于wait_for_slave_count时，是否保持半同步复制)，默认on

rpl_semi_sync_master_wait_for_slave_count  master必须接收到slave ACK消息的数量，默认1

rpl_semi_sync_master_trace_level           master调式日志输出级别，可选1，16，32，64，默认32

rpl_semi_sync_slave_trace_level            slave调式日志输出级别，可选1，16，32，64，默认32

rpl_semi_sync_slave_enabled                从节点开启半同步复制

rpl_semi_sync_master_wait_point            半同步复制master Storage commit在获取从库ACK之前（AFTER_COMMIT）还是之后（AFTER_SYNC），默认AFTER_SYNC

rpl_stop_slave_timeout                     控制stop slave 的执行时间，在重放一个大的事务的时候,突然执行stop slave,命令 stop slave会执行很久,这个时候可能产生死锁或阻塞,严重影响性能，mysql 5.6可以通过rpl_stop_slave_timeout参数控制stop slave的执行时间。

slave_parallel_type    slave并行复制类型

slave_parallel_workers 并行复制进程数

 

2、主从同步状态判断

mysql> show slave status \G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: XX.XX.XX.XX

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: binlog.001250

          Read_Master_Log_Pos: 657664

               Relay_Log_File: relaylog.002972

                Relay_Log_Pos: 657871

        Relay_Master_Log_File: binlog.001250

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_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: 657664

              Relay_Log_Space: 40938241024

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 89

                  Master_UUID: 0090f5f1-3ec3-11e9-9cf2-e4434b18391a

             Master_Info_File: /data/mysql/data/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: 0090f5f1-3ec3-11e9-9cf2-e4434b18391a:1-301580

            Executed_Gtid_Set: 0090f5f1-3ec3-11e9-9cf2-e4434b18391a:1-301580,

722985f1-3ec4-11e9-bc62-e4434b18399a:1-2

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

 

Seconds_Behind_Master值计算，是通过当前系统时间戳减去sql_thread线程正在执行的binlog evnet上的时间戳，得到的差值就是Seconds_Behing_Master的值。

如果用这个判断延迟，当io_thread停止时，没接收到binglog，这时Seconds_Behing_Master就为0,这种情况判断主从延迟就不准确。

正常的判断主从延迟：

首先Master_Log_File和Relay_Master_Log_File所指向的文件必须一致。

其次Read_Master_Log_Pos和Exec_Master_Log_Pos的位置也要一致才行。

 

3、查看半同步状态

3.1、查看master semi sync状态：

mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';

+--------------------------------------------+-------+

| Variable_name                              | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients               | 1     |

| Rpl_semi_sync_master_net_avg_wait_time     | 669   |

| Rpl_semi_sync_master_net_wait_time         | 1338  |

| Rpl_semi_sync_master_net_waits             | 2     |

| Rpl_semi_sync_master_no_times              | 0     |

| Rpl_semi_sync_master_no_tx                 | 0     |

| Rpl_semi_sync_master_status                | ON    |

| Rpl_semi_sync_master_timefunc_failures     | 0     |

| Rpl_semi_sync_master_tx_avg_wait_time      | 779   |

| Rpl_semi_sync_master_tx_wait_time          | 1559  |

| Rpl_semi_sync_master_tx_waits              | 2     |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |

| Rpl_semi_sync_master_wait_sessions         | 0     |

| Rpl_semi_sync_master_yes_tx                | 2     |

+--------------------------------------------+-------+

 

注解：

Rpl_semi_sync_master_clients
记录支持半同步的slave的个数。
Rpl_semi_sync_master_net_avg_wait_time
master 等待slave 回复的平均等待时间。 单位毫秒.
Rpl_semi_sync_master_net_wait_time
master 总的等待时间。
Rpl_semi_sync_master_net_waits
master 等待slave 回复的的总的等待次数。
Rpl_semi_sync_master_no_times
master 关闭半同步复制的次数。
Rpl_semi_sync_master_no_tx
master 没有收到slave的回复而提交的次数，（应该可以理解为master 等待超时的次数)
Rpl_semi_sync_master_status
标记master现在是否是半同步复制状态。

Rpl_semi_sync_master_tx_avg_wait_time
master 花在每个事务上的平均等待时间。
Rpl_semi_sync_master_tx_wait_time
master 总的等待次数。

Rpl_semi_sync_master_wait_sessions
当前有几个线程在等备库响应。
Rpl_semi_sync_master_yes_tx　
master 成功接收到slave的回复的次数。
Rpl_semi_sync_slave_status
标记slave 是否在半同步状态。

Rpl_semi_sync_master_timefunc_failures       

时间函数未正常工作的次数

Rpl_semi_sync_master_wait_pos_backtraverse   

改变当前等待最小二进制日志的次数

 

3.2、查看slave的semi sync状态

mysql > SHOW GLOBAL STATUS LIKE 'rpl_semi%';

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Rpl_semi_sync_slave_status | ON    |

+----------------------------+-------+

1 row in set (0.00 sec)