MySQL 5.5半同步複製的配置與監控
在MySQL的複製環境中，資料同步預設是非同步的，Master節點將事件寫入二進位制日誌，但是不知道Slave節點在什麼時候獲取並應用這些事件；如果Master節點當機，則已經提交的事務可能沒有被傳輸到Slave節點。
在MySQL 5.5及以上版本，支援半同步方式的複製。
這個功能是透過外掛方式來實現的，它具有如下特點：
當Slave節點連線到Master節點時，它會指示它是否是半同步的狀態；
如果Master節點啟用了半同步複製的特性，則至少要有一個半同步的Slave節點。當Master節點有一個執行緒提交了一個事務時，事務不會立刻提交，而是處於阻塞狀態，它會等待至少一個Slave節點發出響應資訊給Master節點，或者等到超時為止；
只有當Slave節點將接收到來自Master節點的事務事件寫入中繼日誌並同步到磁碟上後，它才會向Master節點傳送響應資訊；
當沒有任何Slave節點響應並超時後，Master節點將變成非同步傳輸狀態。當至少一個Slave節點處於半同步狀態後，Master節點會恢復到半同步複製的狀態；
半同步複製必須在Master和Slave節點同時啟用。如果Master節點沒有啟用半同步複製，或者所有Slave節點沒有啟用半同步複製，則Master節點會使用非同步複製。
--顯示外掛所在目錄
mysql> show variables like 'plugin_dir';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| plugin_dir | /software/lib/plugin/ |
+---------------+-----------------------+
1 row in set (0.00 sec)
--檢視外掛目錄裡面的內容
[root@localhost ~]# cd /software/lib/plugin/
You have new mail in /var/spool/mail/root
[root@localhost plugin]# ls
adt_null.so auth_socket.so daemon_example.ini libdaemon_example.so qa_auth_client.so qa_auth_server.so semisync_slave.so
auth.so auth_test_plugin.so debug mypluglib.so qa_auth_interface.so semisync_master.so
--檢視半同步外掛，分別為主從外掛
[root@localhost plugin]# ll /software/lib/plugin/semisync_*
-rwxr-xr-x. 1 root mysql 175571 Apr 11 06:46 /software/lib/plugin/semisync_master.so
-rwxr-xr-x. 1 root mysql 93818 Apr 11 06:46 /software/lib/plugin/semisync_slave.so
--在Master節點載入外掛
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.24 sec)
mysql> select plugin_name,plugin_type,plugin_status from plugins where plugin_name='rpl_semi_sync_master';
+----------------------+-------------+---------------+
| plugin_name | plugin_type | plugin_status |
+----------------------+-------------+---------------+
| rpl_semi_sync_master | REPLICATION | ACTIVE |
+----------------------+-------------+---------------+
1 row in set (0.14 sec)
--在Slave節點載入外掛
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.57 sec)
mysql> show plugins;
+--------------------------+--------+--------------------+-------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------+--------+--------------------+-------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | 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_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 |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+--------------------------+--------+--------------------+-------------------+---------+
24 rows in set (0.03 sec)
--重要的引數如下：
rpl_semi_sync_master_enabled 控制Master節點半同步複製是否開啟
rpl_semi_sync_slave_enabled 控制Slave節點半同步複製是否開啟
rpl_semi_sync_master_timeout 控制Master節點等待Slave節點的響應時間，單位是毫秒，預設值是10000毫秒，也就是10秒
--Master節點，檢視半同步複製的相關引數
mysql> show variables like 'rpl_semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.01 sec)
--Master節點，開啟半同步複製特性，並設定超時時間為3秒
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set global rpl_semi_sync_master_timeout=3000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'rpl_semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
如果想讓引數在資料庫重啟後生效，需要將引數分別加Master和Slave節點到配置檔案中。
--Slave節點，開啟半同步複製特性
mysql> show variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.02 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
--配置好系統變數後，重啟Slave節點的IO_THREAD執行緒
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
--半同步複製環境的監控
--相關引數
rpl_semi_sync_master_clients 配置半同步複製的Slave節點數量，只有當Master節點的半同步複製外掛安裝後，這個引數才會生效
Rpl_semi_sync_master_status 指示Master節點的半同步複製特性是否開啟。如果這個引數的值時ON，則半同步複製外掛已經啟用且提交響應已經發生；如果這個引數的值是OFF，則說明半同步複製外掛沒有啟用，或者
Master節點因為提交響應超時而回到非同步複製模式
Rpl_semi_sync_slave_status 指示Slave節點的半同步複製特性是否開啟。如果這個引數的值為ON，則說明半同步複製外掛已經開啟且Slave節點的I/O執行緒在執行。只有當Slave節點安裝半同步複製外掛後，這個引數才
會生效。
Rpl_semi_sync_master_no_tx 沒有被Slave節點成功響應的提交事務數
Rpl_semi_sync_master_yes_tx 被Slave節點成功響應的提交事務數
Rpl_semi_sync_master_net_wait_time Master節點等待Slave節點回復的總時間，單位是微秒。只有當Master節點安裝了半同步複製外掛後，這個引數才會生效
Rpl_semi_sync_master_net_avg_wait_time Master節點等待Slave節點回復的平均時間，單位是微秒
--在Master節點監控半同步複製的狀態
mysql> show status like '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 752 |
| Rpl_semi_sync_master_net_wait_time | 1505 |
| Rpl_semi_sync_master_net_waits | 2 |
| 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 | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
--在Slave節點監控半同步複製的狀態
mysql> show status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
在MySQL 5.5及以上版本，支援半同步方式的複製。
這個功能是透過外掛方式來實現的，它具有如下特點：
當Slave節點連線到Master節點時，它會指示它是否是半同步的狀態；
如果Master節點啟用了半同步複製的特性，則至少要有一個半同步的Slave節點。當Master節點有一個執行緒提交了一個事務時，事務不會立刻提交，而是處於阻塞狀態，它會等待至少一個Slave節點發出響應資訊給Master節點，或者等到超時為止；
只有當Slave節點將接收到來自Master節點的事務事件寫入中繼日誌並同步到磁碟上後，它才會向Master節點傳送響應資訊；
當沒有任何Slave節點響應並超時後，Master節點將變成非同步傳輸狀態。當至少一個Slave節點處於半同步狀態後，Master節點會恢復到半同步複製的狀態；
半同步複製必須在Master和Slave節點同時啟用。如果Master節點沒有啟用半同步複製，或者所有Slave節點沒有啟用半同步複製，則Master節點會使用非同步複製。
--顯示外掛所在目錄
mysql> show variables like 'plugin_dir';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| plugin_dir | /software/lib/plugin/ |
+---------------+-----------------------+
1 row in set (0.00 sec)
--檢視外掛目錄裡面的內容
[root@localhost ~]# cd /software/lib/plugin/
You have new mail in /var/spool/mail/root
[root@localhost plugin]# ls
adt_null.so auth_socket.so daemon_example.ini libdaemon_example.so qa_auth_client.so qa_auth_server.so semisync_slave.so
auth.so auth_test_plugin.so debug mypluglib.so qa_auth_interface.so semisync_master.so
--檢視半同步外掛，分別為主從外掛
[root@localhost plugin]# ll /software/lib/plugin/semisync_*
-rwxr-xr-x. 1 root mysql 175571 Apr 11 06:46 /software/lib/plugin/semisync_master.so
-rwxr-xr-x. 1 root mysql 93818 Apr 11 06:46 /software/lib/plugin/semisync_slave.so
--在Master節點載入外掛
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.24 sec)
mysql> select plugin_name,plugin_type,plugin_status from plugins where plugin_name='rpl_semi_sync_master';
+----------------------+-------------+---------------+
| plugin_name | plugin_type | plugin_status |
+----------------------+-------------+---------------+
| rpl_semi_sync_master | REPLICATION | ACTIVE |
+----------------------+-------------+---------------+
1 row in set (0.14 sec)
--在Slave節點載入外掛
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.57 sec)
mysql> show plugins;
+--------------------------+--------+--------------------+-------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------+--------+--------------------+-------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | 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_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 |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+--------------------------+--------+--------------------+-------------------+---------+
24 rows in set (0.03 sec)
--重要的引數如下：
rpl_semi_sync_master_enabled 控制Master節點半同步複製是否開啟
rpl_semi_sync_slave_enabled 控制Slave節點半同步複製是否開啟
rpl_semi_sync_master_timeout 控制Master節點等待Slave節點的響應時間，單位是毫秒，預設值是10000毫秒，也就是10秒
--Master節點，檢視半同步複製的相關引數
mysql> show variables like 'rpl_semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.01 sec)
--Master節點，開啟半同步複製特性，並設定超時時間為3秒
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set global rpl_semi_sync_master_timeout=3000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'rpl_semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
如果想讓引數在資料庫重啟後生效，需要將引數分別加Master和Slave節點到配置檔案中。
--Slave節點，開啟半同步複製特性
mysql> show variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.02 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
--配置好系統變數後，重啟Slave節點的IO_THREAD執行緒
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
--半同步複製環境的監控
--相關引數
rpl_semi_sync_master_clients 配置半同步複製的Slave節點數量，只有當Master節點的半同步複製外掛安裝後，這個引數才會生效
Rpl_semi_sync_master_status 指示Master節點的半同步複製特性是否開啟。如果這個引數的值時ON，則半同步複製外掛已經啟用且提交響應已經發生；如果這個引數的值是OFF，則說明半同步複製外掛沒有啟用，或者
Master節點因為提交響應超時而回到非同步複製模式
Rpl_semi_sync_slave_status 指示Slave節點的半同步複製特性是否開啟。如果這個引數的值為ON，則說明半同步複製外掛已經開啟且Slave節點的I/O執行緒在執行。只有當Slave節點安裝半同步複製外掛後，這個引數才
會生效。
Rpl_semi_sync_master_no_tx 沒有被Slave節點成功響應的提交事務數
Rpl_semi_sync_master_yes_tx 被Slave節點成功響應的提交事務數
Rpl_semi_sync_master_net_wait_time Master節點等待Slave節點回復的總時間，單位是微秒。只有當Master節點安裝了半同步複製外掛後，這個引數才會生效
Rpl_semi_sync_master_net_avg_wait_time Master節點等待Slave節點回復的平均時間，單位是微秒
--在Master節點監控半同步複製的狀態
mysql> show status like '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 752 |
| Rpl_semi_sync_master_net_wait_time | 1505 |
| Rpl_semi_sync_master_net_waits | 2 |
| 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 | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
--在Slave節點監控半同步複製的狀態
mysql> show status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2093410/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 配置mysql5.5主從複製、半同步複製、主主複製MySql
- mysql5.5中的半同步複製MySql
- mysql5.5半同步複製探究MySql
- MySQL5.5半同步複製實現原理MySql
- 【MySQL】半同步與增強半同步複製MySql
- MySQL 5.5 Semi-sync 半同步複製測試MySql
- MySQL的半同步複製MySql
- MySQL 半同步複製MySql
- MySQL半同步複製MySql
- MySQL的非同步複製和半同步複製MySql非同步
- mysql 5.7半同步複製MySql
- MySQL 8 複製（二）——半同步複製MySql
- MySQL 5.5 主從複製非同步、半同步以及注意事項詳解MySql非同步
- mysql半同步複製的設定MySql
- MySQL主從複製之半同步複製MySql
- Mysql5.7半同步複製MySql
- redhat 5.5 配置 mysql AB複製RedhatMySql
- MySQL 半同步複製+MMM架構MySql架構
- MySQL半同步複製--after_rollbackMySql
- MySQL主從複製、半同步複製和主主複製MySql
- MySQL的主從複製、半同步複製、主主複製詳解MySql
- MySQL5.7主從複製-半同步複製搭建MySql
- MySQL 5.5級聯複製配置流程MySql
- mysql線上建立半同步複製的從庫MySql
- MySQL主從複製、半同步複製和主主複製概述MySql
- 半同步複製報錯mysql8.0.25MySql
- MySQL半同步複製--after_commitMySqlMIT
- mysql5.5.9半同步複製功能部署MySql
- Mariadb之半同步複製叢集配置
- MySQL增強（Loss-less）半同步複製MySql
- Mysql半同步複製模式說明及配置示例 - 運維小結MySql模式運維
- Mysql半同步配置MySql
- #MySQL# mysql5.7新特性之半同步複製MySql
- MySQL5.7半同步複製報錯案例分析MySql
- 用zabbix監控mysql的主從複製MySql
- MySQL組複製(MGR)全解析 Part 6 監控MySQL組複製MySql
- MySQL 5.5 複製搭建流程MySql
- MySQL(二)：主從複製結構、半同步複製、雙主複製結構、利用SSL實現安全的MySQL主從複製MySql