MySQL性能半同步复制VS异步复制_异步复制和半同步复制差别

| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
±---------------------------------±----------+
8 rows in set (0.00 sec)

主库安装半同步插件 -------------------------------

3306 > install plugin rpl_semi_sync_master soname ‘semisync_master.so’;
Query OK, 0 rows affected (0.00 sec)

3306 > select plugin_name,plugin_status from information_schema.plugins where plugin_name like ‘%semi_sync%’;
±---------------------±--------------+
| plugin_name | plugin_status |
±---------------------±--------------+
| rpl_semi_sync_master | ACTIVE |
±---------------------±--------------+
1 row in set (0.00 sec)

3306 > set global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

从库安装半同步插件 -------------------------------

3307 > install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;
Query OK, 0 rows affected (0.06 sec)

3307 > select plugin_name,plugin_status from information_schema.plugins where plugin_name like ‘%semi_sync%’;
±--------------------±--------------+
| plugin_name | plugin_status |
±--------------------±--------------+
| rpl_semi_sync_slave | ACTIVE |
±--------------------±--------------+
1 row in set (0.00 sec)

3307 > set global rpl_semi_sync_slave_enabled= 1;
Query OK, 0 rows affected (0.00 sec)

主库创建复制用户 --------------------------------------------------------------

3306 > grant replication slave on *.* to repl@‘%’ identified by ‘123456’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

从库建立复制通道

3307 > CHANGE MASTER TO
MASTER_HOST=‘10.186.61.30’,
MASTER_USER=‘repl’,
MASTER_PASSWORD=‘123456’,
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10,
MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

3307 > start slave;
Query OK, 0 rows affected (0.00 sec)

2. 压力测试半同步模式数据库

主库创建压力测试的数据库

3306 > create database testdb;
Query OK, 1 row affected (0.00 sec)

主库创建压力测试用户

3306 > grant all on *.* to root@‘%’ identified by ‘123456’;