mysql双主配置优化

前面一篇文章介绍了如何快速配置mysql双主，步骤比较简单，但如何确保数据库可用性、数据一致性还是需要单独进行配置，上篇文档配置文件内已经针对高可用进行了配置，可以实际生产环境使用，下面针对配置项进行具体说明。

mysql双主配置安装链接：https://megou.life/mysql-dual-master-install/

开启数据库自增ID

server-id = 1
auto_increment_offset = 1
auto_increment_increment = 2 #奇数ID

server-id = 2

auto_increment_offset = 2
auto_increment_increment = 2 #偶数ID

开启半同步

1）安装半同步模块并启动（此模块就在/usr/local/mysql/lib/plugin/semisync_master.so）

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';

rpl_semi_sync_master_enabled=1

rpl_semi_sync_slave_enabled=1

开启日志优化

保证每次事务提交后，都能实时刷新到磁盘中，尤其是确保每次事务对应的binlog都能及时刷新到磁盘中，只要有了binlog，InnoDB就有办法做数据恢复，不至于导致主从复制的数据丢失。

sync_binlog = 1

innodb_flush_log_at_trx_commit=1

show variables like '%innodb_flush_log_at_trx_commit%';

其他优化

确保在slave上和复制相关的元数据表也采用InnoDB引擎，受到InnoDB事务安全的保护，而后一个选项的作用是开启relay log自动修复机制，发生crash时，会自动判断哪些relay log需要重新从master上抓取回来再次应用，以此避免部分数据丢失的可能性。

master_info_repository = "TABLE"

relay_log_info_repository = "TABLE"

relay_log_recovery = 1

并行复制优化

并行复制配置与调优

master_info_repository

开启MTS功能后，务必将参数master_info_repostitory设置为TABLE，这样性能可以有50%~80%的提升。这是因为并行复制开启后对于元master.info这个文件的更新将会大幅提升，资源的竞争也会变大。在之前InnoSQL的版本中，添加了参数来控制刷新master.info这个文件的频率，甚至可以不刷新这个文件。因为刷新这个文件是没有必要的，即根据master-info.log这个文件恢复本身就是不可靠的。在MySQL 5.7中，Inside君推荐将master_info_repository设置为TABLE，来减小这部分的开销。

slave_parallel_workers

若将slave_parallel_workers设置为0，则MySQL 5.7退化为原单线程复制，但将slave_parallel_workers设置为1，则SQL线程功能转化为coordinator线程，但是只有1个worker线程进行回放，也是单线程复制。然而，这两种性能却又有一些的区别，因为多了一次coordinator线程的转发，因此slave_parallel_workers=1的性能反而比0还要差，在Inside君的测试下还有20%左右的性能下降

Enhanced Multi-Threaded Slave配置

开启enhanced multi-threaded slave其实很简单，只需根据如下设置：

# slave

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-type=DATABASE #兼容MySQL 5.6基于schema级别的并发复制

slave-parallel-workers=16

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

并行复制监控

复制的监控依旧可以通过SHOW SLAVE STATUS\G，但是MySQL 5.7在performance_schema架构下多了以下这些元数据表，用户可以更细力度的进行监控：

| replication_applier_configuration |

| replication_applier_status |

| replication_applier_status_by_coordinator |

| replication_applier_status_by_worker |

| replication_connection_configuration |

| replication_connection_status |

| replication_group_member_stats |

| replication_group_members |

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

多线复制状态监控：

select * from replication_applier_status_by_coordinator;

select * from replication_applier_status_by_worker;

主从同步查询

select * from mysql.slave_master_info \G;

keepalived优化

关闭keepalived抢占功能，减少主库恢复后再次切换；

主备keepalived均设置为备份状态，优先级设置一样，关闭抢占机制。

state BACKUP

priority 100

nopreempt

虚地址切换增加延迟

数据库无法连接后，5S后kill掉keepalived

#! /bin/sh

basedir=/app/mysql5.7.29/

$basedir/bin/mysqladmin -ukeepalived -pkeepalived --connect-timeout=3 ping >/dev/null;RETVAL=$?

if [ $RETVAL -ne 0 ];then

alter

sleep 5

pkill keepalived

exit 0

grant usage on *.* to 'keepalived'@'localhost' identified by 'keepalived';