环境：
- 192.168.205.17: as master server
- 192.168.205.27: as slave server
- 192.168.205.37: as slave server
版本：
- OS: centos 7 1810 with mini install
- mariadb-5.5.60
目地：
默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失, 半同步解决了性能和数据安全综合，在同步从服务器时只要有一个从服务器确认同步完成，即返回结果，不用等到全部复制完成，这样解决了数据同步复制时要等所有从服务器返回结果，实现了最佳性能和数据安全的结合
步骤：
- 安装数据库在所有服务器，初始化运行以下脚本
[root@master ~]#cat /data/maridb_yum.sh
rpm -q mariadb-server ||yum install -y mariadb-server
mkdir /data/{mysql,logs}
chown mysql:mysql /data/{mysql,logs}
sed -i '/\[mysqld\]/a log-bin=/data/logs/bin' /etc/my.cnf
sed -i '/\[mysqld\]/a innodb_file_per_table = on' /etc/my.cnf
sed -i '/\[mysqld\]/a skip_name_resolve = on' /etc/my.cnf
sed -i 's@(datadir=).*@\1/data/mysql@' /etc/my.cnf
- 修改配置文件
[root@master ~]#vi /etc/my.cnf
[mysqld]
server-id=17
[root@slave1 ~]#vi /etc/my.cnf
[mysqld]
server-id=27
[root@slave2 ~]#vi /etc/my.cnf
[mysqld]
server-id=37
- 主服务器上建立同步帐号及，记录位置或备份主服器恢复从服务器二选一
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.205.%' identified by 'centos';
MariaDB [(none)]> show master logs;
+------------+-----------+
| Log_name | File_size |
+------------+-----------+
| bin.000001 | 30373 |
| bin.000002 | 1038814 |
| bin.000003 | 406 |
+------------+-----------+
3 rows in set (0.00 sec)
- 从服务器上修改change master to，并启用slave
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.205.17',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='bin.000003',
-> MASTER_LOG_POS=406;
Query OK, 0 rows affected (0.01 sec)
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.205.17
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000003
Read_Master_Log_Pos: 406
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 523
Relay_Master_Log_File: bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
- 要启用主从的半同步复制需要安装一个插件
[root@centos7 data]#rpm -ql mariadb-server
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
- 在数据库中可以查看那些插件安装
MariaDB [(none)]> show plugins;
- 在主服务器上安装插件
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show plugins;
…
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+--------------------------------+----------+--------------------+--------------------+---------+
43 rows in set (0.00 sec)
- 查看半同步的状态
MariaDB [(none)]> show global variables like '%semi%' ;
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF | #半同步默认off
| rpl_semi_sync_master_timeout | 10000 | #超时毫秒，10秒
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 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 global status like '%semi%';
+--------------------------------------------+-------+
| 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 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
- 在所有的从节点安装slave semi插件；
MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show plugins;
…
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+--------------------------------+----------+--------------------+-------------------+---------+
43 rows in set (0.00 sec)
- 查看slave的半同步状态，并启用，并需要重新启动线程，再查看半同步状态是on才可以
MariaDB [(none)]> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 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 '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
- 此时查看主节点的状态
MariaDB [(none)]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 |
| 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 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
- 测试，在主节点上创建一个库
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)
- 在从节点上可以看到同步
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
- 此进我们把slave1停掉,在主节点创建数据库，显示可以成功, 只会复制到slave2节点上
[root@slave1 ~]#systemctl stop mariadb
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
- 如果全部停掉从节点，再从主节点创建数据库，会等10秒才会显示成功
[root@slave2 ~]#systemctl stop mariadb;
MariaDB [(none)]> create database db3;
Query OK, 1 row affected (10.00 sec)
- 将两个服务器再重新启动后，查看数据也会同步
[root@slave1 ~]#systemctl start mariadb
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
