前言：在MySQL主从复制中分为异步复制及半同步复制，不像Oracle分为最大性能模式，最大可用模式，最大保护模式。相比较而言，MySQL的复制更加简单一些，并且基本所有的MySQL高可用方案都是基于主从复制来做的。一般的传统复制即异步复制，主库上的事务无需等待从库应答即可提交。而半同步复制需要从库接收binlog并向主应答，这又分为after sync和after commit两种模式，具体两种区别可以参考之前的文章【MySQL】5.7增强半同步AFTER SYNC&AFTER COMMIT
这里以MySQL5.7为例讲一下怎么怎么搭建半主从复制。
一、安装半同步插件
- Master
查看当前所有已安装的插件：
root@localhost [(none)]>show plugins;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 6Current database: *** NONE ***+----------------------------+----------+--------------------+---------+---------+| Name | Status | Type | Library | License |+----------------------------+----------+--------------------+---------+---------+| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL || mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL || sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL || PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL || MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL || InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL || INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL || CSV | ACTIVE | STORAGE ENGINE | NULL | GPL || MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL || partition | ACTIVE | STORAGE ENGINE | NULL | GPL || BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL || FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL || ngram | ACTIVE | FTPARSER | NULL | GPL |+----------------------------+----------+--------------------+---------+---------+44 rows in set (0.00 sec)
看到并没有关于半同步的插件，那我们来装一下半同步插件：
root@localhost [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected (0.00 sec)root@localhost [(none)]>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.01 sec)
实际上主库上不要装rpl_semi_sync_slave插件，我们这里考虑到以后自动化的问题，统一安装了。
安装后再次查看插件，可以看到版同步的插件已经安装完成。
root@localhost [(none)]>show plugins;+----------------------------+----------+--------------------+--------------------+---------+| Name | Status | Type | Library | License |+----------------------------+----------+--------------------+--------------------+---------+| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL || mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL || sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL || PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL || MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL || InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL || INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL || CSV | ACTIVE | STORAGE ENGINE | NULL | GPL || MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL || partition | ACTIVE | STORAGE ENGINE | NULL | GPL || BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL || FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL || ngram | ACTIVE | FTPARSER | NULL | GPL || rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL || rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |+----------------------------+----------+--------------------+--------------------+---------+
- Slave
做同样动作
root@localhost [(none)]>install plugin rpl_semi_sync_master soname 'semisync_master.so';ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 7Current database: *** NONE ***Query OK, 0 rows affected (0.02 sec)root@localhost [(none)]>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.01 sec)
root@localhost [(none)]>show plugins;+----------------------------+----------+--------------------+--------------------+---------+| Name | Status | Type | Library | License |+----------------------------+----------+--------------------+--------------------+---------+| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL || mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL || sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL || PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL || MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL || InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL || INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL || CSV | ACTIVE | STORAGE ENGINE | NULL | GPL || MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL || partition | ACTIVE | STORAGE ENGINE | NULL | GPL || BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL || FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL || ngram | ACTIVE | FTPARSER | NULL | GPL || rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL || rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |+----------------------------+----------+--------------------+--------------------+---------+46 rows in set (0.00 sec)
二、开启半同步
root@localhost [(none)]>set global rpl_semi_sync_master_enabled=1;Query OK, 0 rows affected (0.00 sec)root@localhost [(none)]>show global variables like '%rpl_semi%';+-------------------------------------------+------------+| Variable_name | Value |+-------------------------------------------+------------+| rpl_semi_sync_master_enabled | ON || rpl_semi_sync_master_timeout | 10000 || 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 || rpl_semi_sync_slave_enabled | OFF || rpl_semi_sync_slave_trace_level | 32 |+-------------------------------------------+------------+8 rows in set (0.00 sec)
这里对参数做个简单的说明：
rpl_semi_sync_master_enabled：主库启用半同步
rpl_semi_sync_master_timeout：半同步超时时间，即从库在超过这个时间无应答后，改为异步同步，生产上可以调小一些，设置为1-2秒即可
rpl_semi_sync_master_wait_for_slave_count ：多个从库的环境下，只等待一个从库应答
rpl_semi_sync_master_wait_point：半主从同步模式，5.7默认AFTER_SYNC，生产建议为AFTER_SYNC ，5.6为AFTER COMMIT
rpl_semi_sync_slave_enabled：从库启用半同步
更改配置文件
master
rpl_semi_sync_master_enabled=1 #主库配置文件添加，表示以后启动MySQL将会自动开启半同步复制slave
rpl_semi_sync_slave_enabled=1
三、半同步测试
从库停止复制以后在主库建表测试
- slave:
root@localhost [(none)]>stop slave;Query OK, 0 rows affected (0.01 sec)
- master
root@localhost [(none)]>use xucl;Database changedroot@localhost [xucl]>create table t1(id int);Query OK, 0 rows affected (10.01 sec)
可以看到，主库在10秒内没有收到应答后自动改为异步复制，然后再引擎层提交事务。
若有收获，就点个赞吧
0 人点赞
