#### 1 MariaDB主从复制原理
1.主节点的更新事件写入binlog；
2.主节点创建一个binlog dump线程，把binlog的内容发送到从节点；
3.从接点连接主节点以后，创建一个I/O线程，读取主节点传过来的binlog内容并写入到relay log；
4.从节点创建一个SQL线程，读取relay log里面的更新事件并解析成SQL语句，然后执行这些SQL语句，将更新内容写入到从节点。
#### 2 MariaDB一主一从架构构建
准备两台纯新的CentOS7.6服务器，其中主机称为node1的服务器地址为192.168.130.132，主机称为node2的服务器地址为192.168.130.133
主节点（node1）：
[root@node1 ~]# cat /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=132
log-bin
[root@node1 ~]# service mysqld restart
[root@node1 ~]# mysql -uroot -p123456
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.130.%' identified by 'centos';
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 401 |
+--------------------+-----------+
从节点（node2）：
[root@node2 ~]# cat /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=133
[root@node2 ~]# service mysqld restart
[root@node2 ~]# mysql -uroot -p123456
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.130.132', #连接master IP地址
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=401;
start slave;
show slave statusG
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#### 3 MariaDB级联复制
上一节中我们完成了一主一从的MariaDB的主从复制架构，但是有的时候，我们期望有一个后备的MariaDB的节点，用只
动词}备份存储数据，不需要对外提供服务。为了实现该功能，我们可以在之前的一主一从架构上进行调整，即将从节点的二进
制文件打开，然后给他配置一个远程同步数据用户，接着使用一台新的服务器做为从服务器的从属同步从服务器数据的即柯林斯，下面我们在原来的架构上添加一台新的CentOS7.6，作为节点3，IP地址为192.168.132.134
node1：
[root@node1 ~]# cat /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=132
log-bin
[root@node1 ~]# service mysqld restart
[root@node1 ~]# mysql -uroot -p123456
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.130.%' identified by 'centos';
node2:
[root@node2 ~]# cat /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=133
log-bin
read-only
log_slave_updates
[root@node2 ~]# mysql -uroot -p123456
MariaDB [(none)]> show master logs; #记录二进制位置，给第三节点使用
+--------------------+----------------+
| Log_name | File_size |
+--------------------+----------------+
| mariadb-bin.000001 | 245 |
+--------------------+----------------+
node3:
[root@node3 ~]# cat /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=134
read-only
[root@node3 ~]# mysql -uroot -p123456
MariaDB [(none)]>CHANGE MASTER TO
MASTER_HOST='192.168.130.133', #连接中间节点 IP地址
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245;
检查状态
start slave;
show slave statusG
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#### 4 MariaDB半同步复制
yum安装的mariadb-10并且配置好主从同步
主节点：
[root@sz-gl-centos8 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=100
log-bin
plugin-load-add=semisync_master
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000
[root@sz-kx-centos8 ~]# systemctl restart mariadb
[root@sz-kx-centos8 ~]# mysql -uroot -p123456
MariaDB [(none)]> show global variables like '%semi%';
MariaDB [(none)]> show global status like '%semi%';
从节点：
[root@sz-kx-centos8 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=108
plugin_load_add=semisync_slave
rpl_semi_sync_slave_enabled=ON
[root@sz-kx-centos8 ~]# systemctl restart mariadb
[root@sz-kx-centos8 ~]# mysql -uroot -p123456
MariaDB [(none)]> show global variables like '%semi%';
MariaDB [(none)]> show global status like '%semi%';
MariaDB [(none)]> START SLAVE IO_THREAD;
主节点：
MariaDB [(none)]> show global status like '%semi%';
+--------------------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------------------+----------------------+
| Rpl_semi_sync_master_clients | 1 |
| 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 | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_request_ack | 1 |
| Rpl_semi_sync_master_status | ON |
测试：
在主节点创建数据库，立即成功
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.001 sec)
在从节点实现，停止复制线程
MariaDB [(none)]> stop slave;
在主节点创建数据库，需要等待3s才成功
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (3.001 sec)
在从节点恢复线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
在主节点创建数据库，立即成功
MariaDB [(none)]> create database db3;
Query OK, 1 row affected (0.004 sec)
yum安装mariadb-5.5.6实现半同步复制
主节点：
#安装master插件，无须重启
MariaDB [(none)]> INSTALL SONAME 'semisync_master';
#开启master_sync
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=ON;
#设置超时时长为1s
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
#查看相关全局变量
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
#查看当前状态信息
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+--------------+
| Rpl_semi_sync_master_clients | 0 |
从节点：
#安装从插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#启动从插件
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
#此时发现slave是关闭的
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+----------------------------+-------------------+
| Variable_name | Value |
+----------------------------+-------------------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------------------+
#重启线程
MariaDB [(none)]> STOP SLAVE IO_THREAD;
MariaDB [(none)]> START SLAVE IO_THREAD;
#插件开启
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+----------------------------+------------------+
| Variable_name | Value |
+----------------------------+------------------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------------------+
主节点：
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 |
#### 5 MariaDB高可用方案MHA
准备三台新安装的CentOS7.6服务器，并使用yum安装MariaDB10.2.23，主机名分别为node1，node2，node3，IP地址分别为192.168.130.132-134
node1/2/3安装mariadb
[root@node1 ~]# vim /etc/yum.repos.d/mariadb.repo
[mariadb]
name=MariaDB
baseurl=http://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64/
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@node1 ~]# yum -y install mariadb-server
node1配置
[root@node1 ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=132
log-bin
skip_name_resolve=1
general_log
[root@node1 ~]# systemctl enable mariadb.service
[root@node1 ~]# systemctl start mariadb.service
[root@node1 ~]# mysql_secure_installation
[root@node1 ~]# mysql -uroot -p
MariaDB [(none)]> show master status;
找个文本记住下面的信息，一会需要到
File：mha-manager-bin.000001和Position：364
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.130.%' identified by 'centos';
MariaDB [(none)]> grant all on *.* to mhauser@'192.168.130.%' identified by 'mhacentos';
[root@node1 ~]# ifconfig eth0:1 192.168.130.200/24
[root@node1 ~]# yum -y install mha4mysql-manager-0.57-0.el7.noarch.rpm mha4mysql-node-0.57-0.el7.noarch.rpm
[root@node1 ~]# ssh-keygen
[root@node1 ~]# ssh-copy-id 192.168.130.132
[root@node1 ~]# rsync -a .ssh 192.168.130.133:/root/
[root@node1 ~]# rsync -a .ssh 192.168.130.134:/root/
[root@node1 ~]# scp mha4mysql-node-0.57-0.el7.noarch.rpm root@192.168.130.133:/root/
[root@node1 ~]# scp mha4mysql-node-0.57-0.el7.noarch.rpm root@192.168.130.134:/root/
[root@node1 ~]# mkdir -p /data/mha/app1
[root@node1 ~]# mkdir /etc/mha
注意：app1.cnf 配置文件有的脚本，需要提前准备好
[root@node1 ~]# vim /etc/mha/app1.cnf
[server default]
user=mhauser
password=mhcentos
manager_workdir=/data/mha/app1/
manager_log=/data/mha/app1/manager.log
remote_workdir=/data/mha/app1/
ssh_user=root
repl_user=repluser
repl_password=centos
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
#master_ip_online_change_script=/usr/local/bin/master_ip_online_change
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0
[server1]
hostname=192.168.130.132
[server2]
hostname=192.168.130.133
candidate_master=1
[server3]
hostname=192.168.130.134
node2配置
[root@node1 ~]# yum -y install mha4mysql-node-0.57-0.el7.noarch.rpm
node2修改mariadb配置文件
[root@node1 ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=133
log-bin
read_only
skip_name_resolve=1
relay_log_purge=0
[root@node1 ~]# systemctl enable mariadb.service
[root@node1 ~]# systemctl start mariadb.service
[root@node1 ~]# mysql_secure_installation
[root@node1 ~]# mysql -uroot -p
MariaDB [(none)]> change master to
master_host='192.168.130.132',
master_user='repluser',
master_password='centos',
master_port=3306,
master_log_file='mha-manager-bin.000001',
master_log_pos=364;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave statusG
node3配置
[root@node1 ~]# yum -y install mha4mysql-node-0.57-0.el7.noarch.rpm
node3修改mariadb配置文件
[root@node1 ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=134
log-bin
read_only
skip_name_resolve=1
relay_log_purge=0
[root@node1 ~]# systemctl enable mariadb.service
[root@node1 ~]# systemctl start mariadb.service
[root@node1 ~]# mysql_secure_installation
[root@node1 ~]# mysql -uroot -p
MariaDB [(none)]> change master to
master_host='192.168.130.132',
master_user='repluser',
master_password='centos',
master_port=3306,
master_log_file='mha-manager-bin.000001',
master_log_pos=364;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave statusG
mha-manager配置
node1检查SSH连接
[root@node1 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
node1检查复制
[root@node1 ~]# vim /usr/local/bin/master_ip_failover
my $vip设置mha-manager虚拟IP
my $gateway设置默认网关IP
my $interface设置网卡名称
授权
[root@node1 ~]# chmod +x /usr/local/bin/master_ip_failover
[root@node1 ~]# chmod +x /usr/local/bin/sendmail.sh
[root@node1 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
node1启动mha manager
[root@node1 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf &
[root@node1 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
测试master自动切换
node1执行
[root@node1 ~]# systemctl stop mariadb.service
node1停止mariadb服务后，master自动切换到node2，虚拟IP也切换到node2上
注意事项：
master自动切换后masterha_manager会退出，node1重新启动mariadb服务后需要连接到新的主节点，操作过程如下：
新的master执行
MariaDB [(none)]> show master status;
记住File和Position的值，当前为slave1-bin.000001，1771
node1连接master节点
MariaDB [(none)]> change master to
master_host='192.168.130.133',
master_user='repluser',
master_password='centos',
master_port=3306,
master_log_file='slave1-bin.000001',
master_log_pos=1771;
MariaDB [(none)]> start slave;
再次启动masterha_manager
[root@node1 ~]# rm -rf /data/mha/app1/app1.failover.complete
[root@node1 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
[root@node1 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf &
[root@node1 ~]# masterha_check_status --conf=/etc/mha/app1.cnf