MariaDB 主从复制原理
数据库服务的重要性在 IT 系统中仅次于操作系统，因此确保数据库的高性能和高可用性极为重要。为了达到这样的要求，我们可以使用更好的计算机硬件设备，比如高频, 多核心，多CPU， 高速大容量内存条， 高转速硬盘，固态硬盘，RAID 等， 还可以使用分布式数据库架构， 远程灾备等技术方案。下面给大家介绍的主从复制基础实验。
MariaDB 在运行时会将数据的变化过程以日志的形式记录下来，我们把这样的日志叫二进制日志, 同时，MariaDB 可以根据二进制日志的内容在新数据库中再次执行一遍，达到了数据恢复和复制的目录。在这个原理的基础上， MariaDB 提供 主节点( master ) 和 从节点( slave ) 的概念， 主节点可以通常是一正常使用的数据库， 我们在该数据中创建一个专用于同步数据的用户，并授权该用户可以远程访问， 从节点应为和主节点为同版本的 MariaDB 服务，对其进行配置，以实现使用通过远程同步用户获取主节点的二进制日志文件，然后再跟进获取的二进制日志文件重新在本机中执行一遍，以达到复制主节点数据的目的。
MariaDB 一主一从架构搭建
首先准备两台纯新的 CentOS7服务器，其中 主机名为 Master1的服务器地址为 192.168.32.7， 主机名为slave1 的服务器地址为 192.168.32.17
实验中关闭防火墙以及selinux
没有关闭防火墙和selinux可以按照下面的方法进行暂时的关闭：
[root@centos7K ~]#yum remove -y firewalld
[root@centos7K ~]#iptables -vnL
Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination
Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination
Chain OUTPUT (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination
[root@centos7K ~]#vim /etc/selinux/config
SELINUX=disabled
查看是否关闭成功
[root@Slave1 ~]#firewall-cmd --state
not running
[root@Slave1 ~]#getenforce
Disabled
服务器 ip ： 192.168.32.7 在Master1上使用 yum 安装 MariaDB10.2.29
[root@Master1 ~]#vim /etc/yum.repos.d/mariadb.repo
[root@Master1 ~]#cat /etc/yum.repos.d/mariadb.repo
[mariadb] #用国内清华大学的仓库源
name=MariaDB
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.2/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@Master1 ~]#yum install -y MariaDB-server
在 Master1中配置 MariaDB master1 节点
[root@Master1 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=7
log_bin
binlog_format=row
[root@Master1 ~]#ss -ntl
查询不到数据库默认端口3306
[root@Master1 ~]#systemctl start mariadb
[root@Master1 ~]#
[root@Master1 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:48307 *:*
LISTEN 0 5 192.168.122.1:53 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 127.0.0.1:6010 *:*
LISTEN 0 128 127.0.0.1:6011 *:*
LISTEN 0 80 :::3306 :::*
LISTEN 0 128 :::111 :::*
[root@Master1 ~]#
[root@Master1 ~]#ll /var/lib/mysql/ 查看是否生成二进制文件
......
-rw-rw---- 1 mysql mysql 21 Nov 29 20:37 Master1-bin.index
-rw-rw---- 1 mysql mysql 5 Nov 29 20:37 Master1.pid
-rw-rw---- 1 mysql mysql 279 Nov 29 20:37 Master1-relay-bin.000001
-rw-rw---- 1 mysql mysql 27 Nov 29 20:37 Master1-relay-bin.index
......
[root@Master1 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.29-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)]> status;
--------------
mysql Ver 15.1 Distrib 10.2.29-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 9
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.2.29-MariaDB-log MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 2 min 5 sec
Threads: 8 Questions: 4 Slow queries: 0 Opens: 16 Flush tables: 1 Open tables: 9 Queries per second avg: 0.032
--------------
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| Master1-bin.000001 | 330 |
+--------------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to kaivi@"192.168.32.%" identified by 'centos';
Query OK, 0 rows affected (0.00 sec)
#创建一个主从管理账号 kaivi
MariaDB [(none)]> select user,host,password from mysql.user;
+--------+--------------+-------------------------------------------+
| user | host | password |
+--------+--------------+-------------------------------------------+
| root | localhost | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| kaivi | 192.168.32.% | *128977E278358FF80A246B5046F51043A2B1FCED |
+--------+--------------+-------------------------------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| Master1-bin.000001 | 529 | #从节点复制二进制日志起始位置
+--------------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]>
服务器 ip ： 192.168.32.17上使用 yum 安装 MariaDB10.2.29
[root@Slave1 ~]#vim /etc/yum.repos.d/mariadb.repo
[root@Slave1 ~]#cat /etc/yum.repos.d/mariadb.repo
[mariadb] #用国内清华大学的仓库源
name=MariaDB
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.2/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@Slave1 ~]#
[root@Slave1 ~]#yum install -y MariaDB-server
在 192.168.32.17中配置 MariaDB slave 1节点
[root@Slave1 ~]#vim /etc/my.cnf.d/server.cnf
[root@Slave1 ~]#cat /etc/my.cnf.d/server.cnf
[mysqld]
server_id=17
read_only
[root@Slave1 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
[root@Slave1 ~]#
[root@Slave1 ~]#systemctl start mariadb
[root@Slave1 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 80 :::3306 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
[root@Slave1 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.29-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)]> status;
--------------
mysql Ver 15.1 Distrib 10.2.29-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.2.29-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 51 sec
Threads: 7 Questions: 4 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second
avg: 0.078
--------------
MariaDB [(none)]> help change master to; #查看对应的帮助，找到下面相关信息进行配置修改
replicate the master:
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
MariaDB [(none)]>
MariaDB [(none)]>
#修改之后直接在数据库中运行即可
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.32.7',
-> MASTER_USER='kaivi',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='Master1-bin.000001',
-> MASTER_LOG_POS=529;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.32.7
Master_User: kaivi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Master1-bin.000001
Read_Master_Log_Pos: 529
Relay_Log_File: centos7K-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: Master1-bin.000001
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: 529
Relay_Log_Space: 256
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
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> start slave; #开启复制线程
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.32.7
Master_User: kaivi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Master1-bin.000001
Read_Master_Log_Pos: 529
Relay_Log_File: centos7K-relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: Master1-bin.000001
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: 529
Relay_Log_Space: 869
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: 7
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> show processlist; #查看线程状态
上图中我们配置了 Slave1节点，启动服务，设置了要同步主节点的信息，包括主机地址，用户名，Master1中起始二进制文件名和具体的日志位置号，然后启动 slave 功能，查看一下 slave 的状态。其中 Slave_IO, Slave_SQL 进程启动正常。
在 Master1（192.168.32.7） 中创建数据，查看 Slave1（192.168.32.17） 中是否同步成功.
Master1（192.168.32.7）中操作：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use school;
Database changed
MariaDB [school]> create table teacher(
-> ID int(10) unsigned not null auto_increment primary key,
-> name varchar(30) not null,
-> gender enum("F","M") not null);
Query OK, 0 rows affected (0.01 sec)
MariaDB [school]>
MariaDB [school]> insert into teacher(name,gender) values("likai","M");
Query OK, 1 row affected (0.00 sec)
MariaDB [school]> insert into teacher(name,gender) values("duanxin","F");
Query OK, 1 row affected (0.00 sec)
MariaDB [school]> select * from teacher;
+----+---------+--------+
| ID | name | gender |
+----+---------+--------+
| 1 | likai | M |
| 2 | duanxin | F |
+----+---------+--------+
2 rows in set (0.00 sec)
MariaDB [school]>
Slave1（192.168.32.17）中操作：
在 Slave1 的 MariaDB 中查询一下， 看是否有个同步复制过来的对应数据:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| teacher |
+------------------+
1 row in set (0.00 sec)
MariaDB [school]> select * from teacher;
+----+---------+--------+
| ID | name | gender |
+----+---------+--------+
| 1 | likai | M |
| 2 | duanxin | F |
+----+---------+--------+
2 rows in set (0.00 sec)
MariaDB [school]>
主从复制成功！
MariaDB 级联复制
之前完成了一主一从的 MariaDB 主从复制架构，但是有的时候，可能期望有一个后备的 MariaDB 节点，只用作备份存储数据，不需要对外提供服务。为了实现该功能，我们可以在之前的一主一从架构上进行调整，即将从节点Slave1（192.168.32.17）的二进制日志文件打开，然后给它配置一个远程同步数据用户，接着使用一台新的服务器做为从服务器的Slave2，同步从服务器的数据即可， 下面我们在原来的架构上添加一台新的 CentOS7，作为 Slave2（192.168.32.27），ip地址为192.168.32.27.
创建一台新的服务器，安装和 Master 和 Slave1 同版本的 MariaDB 服务 ，一样需要禁用防火墙和selinux
服务器 ip ： 192.168.32.27上使用 yum 安装 MariaDB10.2.29
[root@Slave2 ~]#vim /etc/yum.repos.d/mariadb.repo
[root@Slave2 ~]#cat /etc/yum.repos.d/mariadb.repo
[mariadb] #用国内清华大学的仓库源
name=MariaDB
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.2/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@Slave2 ~]#yum install -y MariaDB-server
修改从节点服务器Slave1(192.168.32.17)的配置文件 加上 打开二进制日志文件和二进制同步给自己的 slave 的功能，重启 MariaDB 服务，创建一个新的replslave 账号用于同步数据, 查看当前 二进制文件名称和位置。
Slave1（192.168.32.17）中操作：
[root@Slave1 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=17
log_bin
log_slave_updates #非常关键的参数
read_only
在 192.168.32.27中配置 MariaDB slave2 节点
[root@Slave1 ~]#systemctl restart mariadb
[root@Slave1 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.29-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 master logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| Slave1-bin.000001 | 329 |
+-------------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to likai@"192.168.32.%" identified by 'centos';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user,host,password from mysql.user;
+-------+--------------+-------------------------------------------+
| user | host | password |
+-------+--------------+-------------------------------------------+
| root | localhost | |
| root | centos7k | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| likai | 192.168.32.% | *128977E278358FF80A246B5046F51043A2B1FCED |
+-------+--------------+-------------------------------------------+
6 rows in set (0.01 sec)
MariaDB [(none)]> show master logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| Slave1-bin.000001 | 527 |
+-------------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]>
在服务器（192.168.32.27） 中配置 MariaDB 为 Slave2 的 slave 节点
Slave2（192.168.32.27）中操作：
[root@Slave2 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=27
read_only
[root@Slave2 ~]#systemctl start mariadb
[root@Slave2 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 80 :::3306 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
[root@Slave2 ~]#
[root@Slave2 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.29-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)]> status;
--------------
mysql Ver 15.1 Distrib 10.2.29-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.2.29-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 min 52 sec
Threads: 7 Questions: 4 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.035
--------------
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.32.17',
-> MASTER_USER='likai',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='Slave1-bin.000001',
-> MASTER_LOG_POS=527;
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.32.17
Master_User: likai
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Slave1-bin.000001
Read_Master_Log_Pos: 527
Relay_Log_File: centos7K-relay-bin.000002
Relay_Log_Pos: 556
Relay_Master_Log_File: Slave1-bin.000001
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: 527
Relay_Log_Space: 868
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: 17
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)
MariaDB [(none)]>
上面中成功的启动了 Slave2节点，并复制 Slave 的数据。由于之前在 Master1 和 Slave1 执行过创建 school 的
SQL， 因此 Slave2还没有school 数据库。如果需要，可以用mysqldump进行全备份数据库迁移。
现在我们删除 Master1中的 school 数据库 ：
在Master1（192.168.32.7）中操作：
[root@Master1 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.29-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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> drop database school;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]>
查看一下 Slave1 和 Slave2 ，发现 Slave1中的 school 也被删除了，而 Slave2依旧没有开始进行同步复制
在Slave1 （192.168.32.17）中操作：
[root@Slave1 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.2.29-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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
在Slave2 （192.168.32.27）中操作：
[root@Slave2 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.2.29-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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]>
测试：在 Master1 中删除 test 数据库，创建 Linux和db1数据库，然后验证一下， Slave1 和 Slave2 中是否同步了数据。
在Master1（192.168.32.7）中操作：
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> drop database test;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> create database linux;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| linux |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]>
在Slave1 （192.168.32.17）中操作：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| linux |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]>
在Slave2 （192.168.32.27）中操作：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| linux |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]>
ok ！MariaDB 级联复制操作成功.
MariaDB 半同步复制
前面我们做了主从复制实验，但是在实际使用中，当 MariaDB 的 master 节点负载压力比较大的时候，master 为了及时响应数据请求，减少了用于二进制日志文件的同步频率，以至于 slave 节点和 master 节点之间的数据会有一定的时间差。为此 MariaDB 提供了一种半同步机制，在从主节点1和从节点2中进行配置，要求主节点中的数据必须优先在从节点中更新完毕，才可以响应用户数据更新请求。通过这种方式达到和主节点数据实时同步的目的(通俗的话就是，主节点同步到其他从节点的时候不需要全部复制同步之后再响应，只要任意一个从节点同步就马上主节点响应客户端)。 下面做这个半同步复制实验。
我们在级联复制 实验的基础上，去到192.168.32.17主机Slave1主机上重置从节点文件配置，让其重新变成主节点的一个从节点。去到192.168.32.27主机Slave2主机上重置从节点文件配置，让其重新变成基于主节点的另外一个从节点。
修改 slave1的 MariaDB 配置文件，在Slave1 （192.168.32.17）中操作：
[root@Slave1 ~]#
[root@Slave1 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.2.29-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)]> stop slave; #停止和主服务器同步
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> reset slave all; #重置从节点配置文件
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
Empty set (0.00 sec)
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.32.7',
-> MASTER_USER='kaivi',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='Master1-bin.000001',
-> MASTER_LOG_POS=529;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.32.7
Master_User: kaivi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Master1-bin.000001
Read_Master_Log_Pos: 529
Relay_Log_File: centos7K-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: Master1-bin.000001
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: 529
Relay_Log_Space: 256
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
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
1 row in set (0.00 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.32.7
Master_User: kaivi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Master1-bin.000002
Read_Master_Log_Pos: 344
Relay_Log_File: centos7K-relay-bin.000003
Relay_Log_Pos: 645
Relay_Master_Log_File: Master1-bin.000002
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: 344
Relay_Log_Space: 2758
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: 7
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| linux |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]>
修改 slave2的 MariaDB 配置文件，在Slave2 （192.168.32.27）中操作：
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.32.17
Master_User: likai
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Slave1-bin.000001
Read_Master_Log_Pos: 2179
Relay_Log_File: centos7K-relay-bin.000003
Relay_Log_Pos: 556
Relay_Master_Log_File: Slave1-bin.000001
Slave_IO_Running: No
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: 2179
Relay_Log_Space: 2668
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: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Binary log is not open'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 17
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
Empty set (0.00 sec)
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.32.7',
-> MASTER_USER='kaivi',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='Master1-bin.000001',
-> MASTER_LOG_POS=529;
Query OK, 0 rows affected (0.02 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.32.7
Master_User: kaivi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Master1-bin.000002
Read_Master_Log_Pos: 344
Relay_Log_File: centos7K-relay-bin.000003
Relay_Log_Pos: 645
Relay_Master_Log_File: Master1-bin.000002
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: 344
Relay_Log_Space: 2758
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: 7
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| linux |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]>
配置 Master1 服务器.在Master1（192.168.32.7）中操作：
查看一下当前的 MariaDB 是否支持半同步，若不支持，则安装该功能。成功配置后，可以在配置文件中
永久启用 MariaDB 主节点半同步功能
前提是主从关系，并且两个线程出于打开为连接状态
[root@Master1 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.2.29-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 "%semi%";
Empty set (0.00 sec)
MariaDB [(none)]> show global status like "%semi%";
Empty set (0.00 sec)
MariaDB [(none)]> exit
Bye
[root@Master1 ~]#
[root@Master1 ~]#rpm -ql MariaDB-server |grep ".so$"
/usr/lib64/libmysqld.so
/usr/lib64/mysql/plugin/auth_ed25519.so
/usr/lib64/mysql/plugin/auth_pam.so
/usr/lib64/mysql/plugin/auth_socket.so
/usr/lib64/mysql/plugin/disks.so
/usr/lib64/mysql/plugin/file_key_management.so
/usr/lib64/mysql/plugin/ha_archive.so
/usr/lib64/mysql/plugin/ha_blackhole.so
/usr/lib64/mysql/plugin/ha_federated.so
/usr/lib64/mysql/plugin/ha_federatedx.so
/usr/lib64/mysql/plugin/ha_mroonga.so
/usr/lib64/mysql/plugin/ha_sphinx.so
/usr/lib64/mysql/plugin/ha_spider.so
/usr/lib64/mysql/plugin/handlersocket.so
/usr/lib64/mysql/plugin/locales.so
/usr/lib64/mysql/plugin/metadata_lock_info.so
/usr/lib64/mysql/plugin/query_cache_info.so
/usr/lib64/mysql/plugin/query_response_time.so
/usr/lib64/mysql/plugin/semisync_master.so #主节点半同步模块
/usr/lib64/mysql/plugin/semisync_slave.so #从节点半同步模块
/usr/lib64/mysql/plugin/server_audit.so
/usr/lib64/mysql/plugin/simple_password_check.so
/usr/lib64/mysql/plugin/sql_errlog.so
/usr/lib64/mysql/plugin/wsrep_info.so
[root@Master1 ~]#
[root@Master1 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.2.29-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)]>
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec) #安装模块
MariaDB [(none)]> select @@rpl_semi_sync_master_enabled; #查看模块是否开启
+--------------------------------+
| @@rpl_semi_sync_master_enabled |
+--------------------------------+
| 0 |
+--------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> set global rpl_semi_sync_master_enabled=1; #开启模块
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select @@rpl_semi_sync_master_enabled;
+--------------------------------+
| @@rpl_semi_sync_master_enabled |
+--------------------------------+
| 1 |
+--------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> select @@rpl_semi_sync_master_timeout; #默认响应超时时长，单位ms
+--------------------------------+
| @@rpl_semi_sync_master_timeout |
+--------------------------------+
| 10000 |
+--------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> set global rpl_semi_sync_master_timeout=3000; #设置超时响应时长为3s
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select @@rpl_semi_sync_master_timeout;
+--------------------------------+
| @@rpl_semi_sync_master_timeout |
+--------------------------------+
| 3000 |
+--------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show global variables like '%semi%'; #查看变量
+------------------------------------+--------------+
| Variable_name | Value |
+------------------------------------+--------------+
| rpl_semi_sync_master_enabled | ON | #主节点半同步模块是否打开ON|OFF
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
+------------------------------------+--------------+
5 rows in set (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 | #主节点半同步模块是否打开ON|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 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> exit
Bye
[root@Master1 ~]#
[root@Master1 ~]#systemctl stop mariadb
[root@Master1 ~]#
[root@Master1 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=7
log_bin
binlog_format=row
rpl_semi_sync_master_enabled
#写在配置文件里面，永久自动开启
[root@Master1 ~]#
[root@Master1 ~]#systemctl start mariadb
[root@Master1 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:48307 *:*
LISTEN 0 5 192.168.122.1:53 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 127.0.0.1:6010 *:*
LISTEN 0 80 :::3306 :::*
LISTEN 0 128 :::111 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 128 ::1:631 :::*
LISTEN 0 128 :::56312 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 128 ::1:6010 :::*
[root@Master1 ~]#
[root@Master1 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.29-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 '%semi%';
+------------------------------------+--------------+
| Variable_name | Value |
+------------------------------------+--------------+
| 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 |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
+------------------------------------+--------------+
5 rows in set (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)
MariaDB [(none)]>
MariaDB [(none)]>
启动191.168.32.17的从服务，并安装对应的半同步模块，然后再次修改配置文件，使从节点的半同步生效
在Slave1 （192.168.32.17）中操作：
[root@Slave1 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.2.29-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 '%semi%';
Empty set (0.00 sec)
MariaDB [(none)]> show global variables like '%semi%';
Empty set (0.00 sec)
MariaDB [(none)]> show global status like '%semi%';
Empty 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 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)]> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=1;
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)]> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
Bye
[root@Slave1 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=17
read_only
rpl_semi_sync_slave_enabled
[root@Slave1 ~]#systemctl restart mariadb
[root@Slave1 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.29-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 '%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)]> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
启动192.168.32.27的从服务，并安装对应的半同步模块，然后再次修改配置文件，使从节点的半同步生效
在Slave2 （192.168.32.27）中操作：
[root@Slave2 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.2.29-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 '%semi%';
Empty set (0.00 sec)
MariaDB [(none)]> show global variables like '%semi%';
Empty set (0.00 sec)
MariaDB [(none)]> show global status like '%semi%';
Empty 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 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)]> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=1;
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)]> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
Bye
[root@Slave2 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=27
read_only
rpl_semi_sync_slave_enabled
[root@Slave2 ~]#systemctl restart mariadb
[root@Slave2 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.29-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 '%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)]> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
查看192.168.32.7的半同步状态
配置 Master1 服务器.在Master1（192.168.32.7）中操作：
MariaDB [(none)]>
MariaDB [(none)]> show global variables like '%semi%';
+------------------------------------+--------------+
| Variable_name | Value |
+------------------------------------+--------------+
| 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 | #从节点半同步模块打开状态
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
+------------------------------------+--------------+
5 rows 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 | 889 |
| Rpl_semi_sync_master_net_wait_time | 889 |
| 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 | 919 |
| Rpl_semi_sync_master_tx_wait_time | 919 |
| 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 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
MariaDB [(none)]>
测试是否实现主从半同步设置。一开始主节点配置中设置了默认响应时长为3s：
set global rpl_semi_sync_master_timeout=3000;
测试中看下面所用的时长
#在Master1实现，创建数据库，立即成功
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (0.00 sec)
#在所有slave节点实现
模拟所有节点都停了服务，这里停止复制线程即可，这样就会给出一个3s的超长时长
停止Slave1的复制线程：
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)
停止Slave2的复制线程：
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)
#在master实现，创建数据库，等待3s才能成功
MariaDB [(none)]> create database db3;
Query OK, 1 row affected (3.00 sec)
MariaDB [(none)]>
#在任意一个slave节点实现，恢复复制线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
#在master实现，创建数据库，立即成功
MariaDB [(none)]> create database db4;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>
半同步实验ok！
扩展：半同步复制实验
以上的操作都是基于centos7系统。前面的主从复制以及级联复制基本都是一致。但是在后面的半同步复制中，在centos8中已经在本身系统中集成，不是按照模块的形式来加载模块，而是要通过配置文件来启动对应的半同步复制。
官方文档](https://mariadb.com/kb/en/library/semisynchronous-replication/
范例：CentOS 8 在Mariadb-10.3.11上实现 实现半同步复制
464
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
