mysql实战——半同步复制搭建

一、搭建前准备

主库    192.168.1.78

从库    192.168.1.76

二、搭建

1、先搭建异步复制

MySQL实战——主从异步复制搭建（一主一从）-CSDN博客

2、在异步的基础上搭建半同步复制

主库

mysql>install plugin rpl_semi_sync_slave soname 'semisync_slave.so'
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql> 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     |
| caching_sha2_password           | ACTIVE   | AUTHENTICATION     | NULL              | GPL     |
| sha2_cache_cleaner              | ACTIVE   | AUDIT              | NULL              | GPL     |
| daemon_keyring_proxy_plugin     | ACTIVE   | DAEMON             | NULL              | GPL     |
| CSV                             | 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_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_TABLES                   | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_TABLESTATS               | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_INDEXES                  | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_TABLESPACES              | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_COLUMNS                  | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_VIRTUAL                  | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CACHED_INDEXES           | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| MyISAM                          | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| MRG_MYISAM                      | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| PERFORMANCE_SCHEMA              | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| TempTable                       | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| ARCHIVE                         | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| BLACKHOLE                       | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| FEDERATED                       | DISABLED | STORAGE ENGINE     | NULL              | GPL     |
| ngram                           | ACTIVE   | FTPARSER           | NULL              | GPL     |
| mysqlx_cache_cleaner            | ACTIVE   | AUDIT              | NULL              | GPL     |
| mysqlx                          | ACTIVE   | DAEMON             | NULL              | GPL     |
| rpl_semi_sync_slave             | ACTIVE   | REPLICATION        | semisync_slave.so | GPL     |
+---------------------------------+----------+--------------------+-------------------+---------+
46 rows in set (0.00 sec)

mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+---------------------+---------------+
| plugin_name         | plugin_status |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)

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

mysql> stop slave io_thread;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> start slave io_thread;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show status like 'Rpl_semi_sync_master_status';
Empty set (0.01 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

从库

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

mysql> stop slave io_thread;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> start slave io_thread;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show status like 'Rpl_semi_sync_master_status';
Empty set (0.01 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

2、关闭半同步复制

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.04 sec)

mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name          | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.01 sec)

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

mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.01 sec)