内容简介：首先我们来了解下在mariadb/mysql数据库主从复制集群中什么是同步，什么是异步，什么是半同步；所谓同步就是指主节点发生写操作事件，它不会立刻返回，而是等到从节点接收到主节点发送过来的写操作事件，并在从节点完成重放后，从节点返回一个重放完成的消息给主节点，然后主节点才返回，这种叫同步；简单讲就是主节点要等从节点把写操作重放完成后再接收到从节点发来的重放完成消息后，然后返回告诉客户端；异步指的是在主节点发生写操作后，它只管把写操作产生的事件，写入到自己的二进制日志中后，就返回客户端；它不会等从节点重放完
首先我们来了解下在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 ；
以上所述就是小编给大家介绍的《Mariadb之半同步复制集群配置》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持！
猜你喜欢:
- redis集群—主从同步（复制）
- Kafka 跨集群同步方案
- Kafka 跨集群同步方案
- 不懂就问：ZooKeeper 集群如何进行数据同步？
- 好程序员大数据培训技术分享：Hadoop集群同步
- dister v1.5 稳定版发布，优化数据同步机制及集群通信协议
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
驾驭未来：抓住奇点冲击下的商机
[日]斋藤和纪 / 南浩洁 / 中国友谊出版公司 / 2018-9 / 52.00元
2020年左右，AI（人工智能）将超越人类的智力水平。2045年，人类将迎来“奇点”——科技进步的速度达到无限大。 所有技术都在以空前的速度向前发展。同时，以往带来巨大财富的众多技术将走向“非货币化”。当下，人类正面临着被AI夺去工作的危机。许多传统行业（例如汽车制造业）将被彻底颠覆，但新的机会也在酝酿，技术的进步使得带宽成本、计算成本、存储成本等创新成本趋近于0，创业不再是资本、技术或信息......一起来看看 《驾驭未来：抓住奇点冲击下的商机》 这本书的介绍吧!
