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)