Mysql主从延时一直都是个问题,网上很多资料都有说过使用Mysql的半同步状态可以很好的解决主同步延时问题。但是在我的工作中就存在从库延时的问题。其实我也知道问题的原因在哪里,只是想表达一下只有自己试过了才知道深浅,就好象以前学过的小马过河。
理想的情况下:
- 数据库硬件性能好
- 数据库、表结构设计合理
- SQL语句优化得好。
在这样的情况下应该不会有什么延时问题。但事实上就我所在的公司里数据库的设计、表的设计、SQL语句等都是一个问题。正因为这些问题导致了再牛B的半同步复制都无法解决到我的主从数据延时问题。
下面记录一下GTID复制的简单配置:
主服务器:
server-id = 110
log_timestamps = SYSTEM
gtid_mode = on
enforce-gtid-consistency
relay_log_recovery=1
log-slave-updates = 1
log_bin = master-bin
binlog_format = row
expire_logs_days = 10
log_error = /data/mysql/mysql-error.log
slow_query_log = On
long_query_time = 2
slow_query_log_file = /data/mysql/mysql-slow.log
log-queries-not-using-indexes
innodb_flush_log_at_trx_commit=1
sync_binlog=1
从服务器:
server-id = 61
log_timestamps = SYSTEM
gtid_mode = on
enforce-gtid-consistency
skip_slave_start=1
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=table
relay_log_info_repository=table
relay_log_recovery=1
log-slave-updates
log_error = /data/mysql/mysql-error.log
slow_query_log = On
long_query_time = 2
slow_query_log_file = /data/mysql/mysql-slow.log
log-queries-not-using-indexes
innodb_flush_log_at_trx_commit=2
sync_binlog=0
启动gtid复制:从库上操作
reset master;
reset slave;
CHANGE MASTER TO
MASTER_HOST='10.0.10.110',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_AUTO_POSITION=1;
start slave;
上面就是简单的GTID复制配置,而半同步复制都是可以动态加载的插件来实现。
步骤1:查找mysql插件目录位置。
mysql> show variables like 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.00 sec)
查看目录下是否有semisync的插件,一般都有;
[root@mysql ~]# ll /usr/local/mysql/lib/plugin/|grep "semisync"
-rwxr-xr-x 1 root root 621416 May 9 2016 semisync_master.so
-rwxr-xr-x 1 root root 146579 May 9 2016 semisync_slave.so
加载半同步插件;主服务器:10.0.10.110 操作。
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.02 sec)
查看是否加载插件
sql> show plugins;
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL
从服务器:10.0.10.61 操作
sql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
四:启动半同步插件。
Master:启用插件(10.0.10.110)
sql>set global rpl_semi_sync_master_enabled = on;
Slave:启动半同步,slave要重启动IO线程,否则还是异步的方式同步数据。(10.0.10.61)
sql>set global rpl_semi_sync_slave_enabled = on;
sql>stop slave IO_THREAD;
sql>start slave IO_THREAD;
半同步几个参数设置:
mysql> show status like '%Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 1 |
| 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 | 242 |
| Rpl_semi_sync_master_tx_wait_time | 242 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
解释几个重要的值。
Rpl_semi_sync_master_status 是否启用了半同步。
Rpl_semi_sync_master_clients 半同步模式下Slave一共有多少个。
Rpl_semi_sync_master_no_tx 往slave发送失败的事务数量。
Rpl_semi_sync_master_yes_tx 往slave发送成功的事务数量。
注释:
rpl_semi_sync_master_timeout:Master等待slave响应的时间,单位是毫秒,默认值是10秒,超过这个时间,slave无响应,环境架构将自动转换为异步复制
rpl_semi_sync_master_trace_level:监控等级,一共4个等级(1,16,32,64),后续补充详细。
rpl_semi_sync_master_wait_no_slave:是否允许master 每个事物提交后都要等待slave的receipt信号。默认为on ,每一个事务都会等待,如果slave当掉后,当slave追赶上master的日志时,可以自动的切换为半同步方式,如果为off,则slave追赶上后,也不会采用半同步的方式复制了,需要手工配置。
rpl_stop_slave_timeout:控制stop slave 的执行时间,在重放一个大的事务的时候,突然执行stop slave ,命令 stop slave会执行很久,这个时候可能产生死锁或阻塞,严重影响性能,mysql 5.6可以通过rpl_stop_slave_timeout参数控制stop slave 的执行时间
Rpl_semi_sync_master_no_tx: 0
Rpl_semi_sync_master_yes_tx: 1
可以看到执行成功,并返回了状态。
主库上查看slave_parall参数;
sql> show variables like 'slave_parall%';
+------------------------+---------------+
| Variable_name | Value |
+------------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
| slave_parallel_workers | 0 |
+------------------------+---------------+
2 rows in set (0.01 sec)
默认5.7使用AFTER_SYNC参数;
mysql> show variables like 'rpl_semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 2000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)
这样就配置好了Mysql基于GTID的半同步复制。
大部分的数据延时都是因为主库上的SQL执行时间过长,导致在从库也是执行时间长,这样的两个时间差还没有前端应用里的客户刷新数据快就会产生刚更新的内容,一刷新居然却没有发生改变。
所以最大的问题就是在SQL语句和表、数据库的设计上优化。