半同步指的是在主節點發生寫操作事件後，它會把該操作的事件發送給從節點，當從節點接收到主節點發送過來的事件後，就立刻告訴主節點，從節點已經接收到主節點發送過來的事件，此時主機點並不會等到從節點重放完成，而是接收到從節點接收到主節點發送過去的的事件確認消息後，就返回給客戶端；而在mariadb/mys... ...
首先我們來瞭解下在mariadb/mysql資料庫主從複製集群中什麼是同步，什麼是非同步，什麼是半同步；所謂同步就是指主節點發生寫操作事件，它不會立刻返回，而是等到從節點接收到主節點發送過來的寫操作事件，併在從節點完成重放後，從節點返回一個重放完成的消息給主節點，然後主節點才返回，這種叫同步；簡單講就是主節點要等從節點把寫操作重放完成後再接收到從節點發來的重放完成消息後，然後返回告訴客戶端；非同步指的是在主節點發生寫操作後，它只管把寫操作產生的事件，寫入到自己的二進位日誌中後，就返回客戶端；它不會等從節點重放完成；這種叫非同步；預設情況mariadb/mysql主從複製集群就是這種非同步同步的方式進行；半同步指的是在主節點發生寫操作事件後，它會把該操作的事件發送給從節點，當從節點接收到主節點發送過來的事件後，就立刻告訴主節點，從節點已經接收到主節點發送過來的事件，此時主機點並不會等到從節點重放完成，而是接收到從節點接收到主節點發送過去的的事件確認消息後，就返回給客戶端；而在mariadb/mysql主從複製集群中的半同步，並不是我們剛纔說的這種半同步機制；在mariadb/mysql主從複製集群中的半同步指的是，一主多從的複製集群中，一個從節點或一部分從節點和主節點是同步複製，一部分從節點是非同步複製；
配置mariadb半同步複製
在mariadb/mysql中，半同步的實現是基於插件的方式實現的，在早期的版本中我們需要手動安裝模塊，然後才能實現半同步複製；目前mairadb10.5.4的版本中，預設就支持半同步，不需要手動安裝模塊；
[root@lxc ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.5.4-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show global variables like 'rpl%'; +---------------------------------------+--------------+ | 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 | | rpl_semi_sync_master_wait_point | AFTER_COMMIT | | rpl_semi_sync_slave_delay_master | OFF | | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_kill_conn_timeout | 5 | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------------+--------------+ 9 rows in set (0.002 sec) MariaDB [(none)]> show global status like 'rpl%'; +--------------------------------------------+-------------+ | Variable_name | Value | +--------------------------------------------+-------------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_get_ack | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_request_ack | 0 | | Rpl_semi_sync_master_status | OFF | | 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 | 0 | | Rpl_semi_sync_slave_send_ack | 0 | | Rpl_semi_sync_slave_status | OFF | | Rpl_status | AUTH_MASTER | | Rpl_transactions_multi_engine | 0 | +--------------------------------------------+-------------+ 20 rows in set (0.001 sec) MariaDB [(none)]>
提示：以上變數信息和狀態信息可以瞭解到mariadb是支持半同步的，只是沒有開啟；如果在mariadb/mysql資料庫上查不到上面的信息，就需要我們手動安裝模塊；如下所示
[root@docker_node01 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 5.5.65-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show global variables like 'rpl%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | rpl_recovery_rank | 0 | +-------------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> show global status like 'rpl%'; +---------------+-------------+ | Variable_name | Value | +---------------+-------------+ | Rpl_status | AUTH_MASTER | +---------------+-------------+ 1 row in set (0.00 sec) MariaDB [(none)]>
提示：mariadb5.5.65預設是沒有啟動半同步插件，我們需要手動安裝插件；
安裝插件
提示：預設情況在/usr/lib64/mysql/plugin/目錄下就有semisync_master.so和semisync_slave.so這兩個模塊；從名字上我們就能清楚知道semisync_master.so這個模塊用於主節點，semisync_slave.so用於從節點；安裝方法如下
主節點安裝semisync_master.so
[root@docker_node01 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 5.5.65-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show global variables like 'rpl%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | rpl_recovery_rank | 0 | +-------------------+-------+ 1 row in set (0.01 sec) MariaDB [(none)]> show global status like 'rpl%'; +---------------+-------------+ | Variable_name | Value | +---------------+-------------+ | Rpl_status | AUTH_MASTER | +---------------+-------------+ 1 row in set (0.01 sec) MariaDB [(none)]> 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 | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_RSEG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_UNDO_LOGS | ACTIVE | INFORMATION SCHEMA | 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_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_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLE_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEX_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES_BLOB | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | XTRADB_ADMIN_COMMAND | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CHANGED_PAGES | 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 | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | Aria | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +--------------------------------+----------+--------------------+---------+---------+ 42 rows in set (0.00 sec) MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> 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 | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_RSEG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_UNDO_LOGS | ACTIVE | INFORMATION SCHEMA | 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_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_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLE_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEX_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES_BLOB | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | XTRADB_ADMIN_COMMAND | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CHANGED_PAGES | 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 | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | Aria | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | +--------------------------------+----------+--------------------+--------------------+---------+ 43 rows in set (0.00 sec) MariaDB [(none)]> show global variables like 'rpl%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_recovery_rank | 0 | | 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 | +------------------------------------+-------+ 5 rows in set (0.00 sec) MariaDB [(none)]> show global status like 'rpl%'; +--------------------------------------------+-------------+ | Variable_name | Value | +--------------------------------------------+-------------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | OFF | | 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 | 0 | | Rpl_status | AUTH_MASTER | +--------------------------------------------+-------------+ 15 rows in set (0.00 sec) MariaDB [(none)]>
提示：如果是主節點就安裝semisync_master.so，從節點就安裝semisync_slave.so；當然你把兩個模塊都安裝上也是可以的，只不過啟動時對應啟動就行；
從節點安裝semisync_slave.so
[root@docker-node03 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 5.5.65-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show global variables like 'rpl%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | rpl_recovery_rank | 0 | +-------------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> show global status like 'rpl%'; +---------------+-------------+ | Variable_name | Value | +---------------+-------------+ | Rpl_status | AUTH_MASTER | +---------------+-------------+ 1 row in set (0.00 sec) MariaDB [(none)]> 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 | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_RSEG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_UNDO_LOGS | ACTIVE | INFORMATION SCHEMA | 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_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_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLE_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEX_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES_BLOB | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | XTRADB_ADMIN_COMMAND | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CHANGED_PAGES | 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 | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | Aria | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +--------------------------------+----------+--------------------+---------+---------+ 42 rows in set (0.00 sec) MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> 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 | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_RSEG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_UNDO_LOGS | ACTIVE | INFORMATION SCHEMA | 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_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_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLE_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEX_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_PAGES_BLOB | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | XTRADB_ADMIN_COMMAND | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CHANGED_PAGES | 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 | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | Aria | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL | +--------------------------------+----------+--------------------+-------------------+---------+ 43 rows in set (0.01 sec) MariaDB [(none)]> show global variables like 'rpl%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_recovery_rank | 0 | | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 3 rows in set (0.00 sec) MariaDB [(none)]> show global status like 'rpl%'; +----------------------------+-------------+ | Variable_name | Value | +----------------------------+-------------+ | Rpl_semi_sync_slave_status | OFF | | Rpl_status | AUTH_MASTER | +----------------------------+-------------+ 2 rows in set (0.00 sec) MariaDB [(none)]>
提示：可以看到安裝好插件後，我們就可以在插件列表中相應的插件，在全局變數中也能查到對應的變數和狀態信息；
到此半同步需要要點插件就已經裝載完成；接下來就可以直接配置主從複製了，然後在開啟半同步插件
配置mariadb/mysql主從複製
主節點開啟二進位日誌，設置server-id為1，開啟sync_binlog=on
提示：以上在主節點開啟了二進位日誌，開啟同步刷新二進位日誌到磁碟二進位日誌；開啟刷新事務日誌，設置伺服器id為1
重啟主節點，在主節點創建具有複製許可權的賬號，並查看當前二進位日誌文件名和對應位置
[root@docker_node01 ~]# systemctl restart mariadb [root@docker_node01 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 5.5.65-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> grant replication slave,replication client on *.* to 'rpluser'@'192.168.0.%' identified by 'admin'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 642 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]>
從節點配置server-id為2，配置從節點為只讀，開啟從節點的中繼日誌
提示：以上配置表示，設置伺服器id為2，開啟中繼日誌，並保持到/var/lib/mysql/relay-log中；開啟只讀（僅對非super許可權的用戶生效）；開啟同步刷新master_info到磁碟；開啟同步刷新relay_log_info到磁碟；後面兩項主要是確保事實時記錄複製和重放二進位日誌的位置，避免出現故障，重覆複製；
重啟從節點，配置連接從伺服器連接主服務的相關信息
[root@docker-node03 ~]# systemctl restart mariadb
[root@docker-node03 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.22',
-> MASTER_USER='rpluser',
-> MASTER_PASSWORD='admin',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=642;
Query OK, 0 rows affected (0.06 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.22
Master_User: rpluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 642
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 642
Relay_Log_Space: 245
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
MariaDB [(none)]>
啟動io線程和sql線程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.22
Master_User: rpluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 642
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 642
Relay_Log_Space: 817
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
MariaDB [(none)]>
提示：到此主從複製就搭建好了，但是我們還沒有啟動半同步插件，到此之前mariadb的主從複製還是基於一部的方式進行，接下來我們啟動半同步插件；
在主節點啟用半同步插件
[root@docker_node01 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 7 Server version: 5.5.65-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show variables like 'rpl%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_recovery_rank | 0 | | 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 | +------------------------------------+-------+ 5 rows in set (0.00 sec) MariaDB [(none)]> set global rpl_semi_sync_master_enabled=ON; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show variables like 'rpl%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_recovery_rank | 0 | | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 5 rows in set (0.01 sec) MariaDB [(none)]> show status like 'rpl%'; +--------------------------------------------+-------------+ | Variable_name | Value | +--------------------------------------------+-------------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | 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 | 0 | | Rpl_status | AUTH_MASTER | +--------------------------------------------+-------------+ 15 rows in set (0.00 sec) MariaDB [(none)]>
提示：可以看到半同步主節點插件已經開啟
從節點啟用半同步插件
[root@docker-node03 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 7 Server version: 5.5.65-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show global variables like 'rpl%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_recovery_rank | 0 | | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 3 rows in set (0.00 sec) MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=on; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show global variables like 'rpl%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_recovery_rank | 0 | | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+
重啟從節點IO線程
MariaDB [(none)]> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.22
Master_User: rpluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 642
Relay_Log_File: relay-log.000004
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 642
Relay_Log_Space: 1101
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
MariaDB [(none)]>
主節點查看狀態信息，看看是否有從節點以半同步的方式連接上來了
提示：在主節點上可以看到狀態信息中有一個從節點已經連接上來了；到此半同步複製就配置完了；
測試：在主節點上執行寫操作，看看對應狀態有什麼變化？
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.01 sec) MariaDB [(none)]> create database first_db; Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> show global variables like 'rpl%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_recovery_rank | 0 | | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 5 rows in set (0.00 sec) MariaDB [(none)]> show global status like 'rpl%'; +--------------------------------------------+-------------+ | Variable_name | Value | +--------------------------------------------+-------------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 1037 | | Rpl_semi_sync_master_net_wait_time | 1037 | | Rpl_semi_sync_master_net_waits | 1 | | 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 | 1321 | | Rpl_semi_sync_master_tx_wait_time | 1321 | | Rpl_semi_sync_master_tx_waits | 1 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 1 | | Rpl_status | AUTH_MASTER | +--------------------------------------------+-------------+ 15 rows in set (0.00 sec) MariaDB [(none)]>
提示：可以看到，當我們在主庫中新建了一個first_db的庫，在狀態信息中可以看到創建這個庫，平均等待了1037毫秒，事務平均等待時間是1321毫秒；這意味著主節點在完成發送事件給從節點，從節點重放完成後，返回給主節點用了1321毫秒；
在從庫中查看，是否將first_db同步過來了？
提示：在從庫上可以看到剛纔在主庫上創建的庫，已經同步到從庫了；
到此半同步複製集群就配置好了，這裡需要提醒下，配置半同步複製集群有損主庫寫的性能，畢竟它要等待從庫完成重放後或者等到超時後才能返回，如果從庫宕機了，那麼主庫會等到超時後自動降級為非同步；如果還要對主庫做高可用，那麼配置半同步的從節點就是優選主節點的從節點；有關主庫高可用集群解決方案MHA的相關配置可以參考本人博客https://www.cnblogs.com/qiuhom-1874/p/12183521.html；
