目录
AFTER_SYNC和AFTER_COMMIT两种方式的小结:
服务器环境
关闭selinux、firewalld,更新阿里云yum源和epel-release
用户 | 服务器名 | ip | |
root | manage | 192.168.150.101 | 管理端 |
root | master | 192.168.150.102 | 主 |
root | slave1 | 192.168.150.103 | 从(备用主) |
root | slave2 | 192.168.150.104 | 从 |
四台服务器都设置本地域名
版本
keepalived-1.3.5-19.el7.x86_64
mariadb-server-5.5.68-1.el7.x86_64
mariadb-5.5.68-1.el7.x86_64mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
一、mariadb主从
服务器:master(主)
yum安装mariadb,修改数据库配置文件
root@master ~# yum -y install mariadb mariadb-server
root@master ~# vim /etc/my.cnf
在my.cnf中写入:
server-id=2
#每台服务器的id必须是唯一的
log-bin=mysql-bin#开启二进制日志(只有主服务器用开启),从服务器通过I/O线程复制主服务器二进制日志来获取主服务器的数据
relay-log=mysql-relay#开启中继日志(只有从服务器用开启),把主服务器复制过来的数据保存到中继日志(写入磁盘),从库再通过SQL线程读取中继日志来更新数据,在高可用架构下主服务器宕机重连后,主服务器变成从服务器,所以要开启
修改完配置文件后重启数据库使配置生效。查看二进制日志的名称和位置,授权一个从服务器用来复制二进制日志的用户,为主从复制做准备
root@master ~# systemctl restart mariadb
root@master ~# mysql
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 245 | | |
+------------------+----------+--------------+------------------+
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%' identified by '123';
服务器:slave1 [从(备用主)]
同样yum下载mariadb和修改数据库配置文件
root@slave1 ~# yum -y install mariadb mariadb-server
root@slave1 ~# vim /etc/my.cnf
在my.cnf中写入:
server-id=3
log-bin=mysql-bin #在高可用架构下此服务器是备用主,如果主服务器宕机,此服务器变成新主,所以要开启
relay-log=mysql-relay
修改完配置文件后重启数据库使配置生效
root@slave1 ~# systemctl restart mariadb
配置从库
root@slave1 ~# mysql
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.150.102',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=245;
启动从库,查看IO和SQL两个线程为Yes表示成功
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.150.102
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 384
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 668
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
服务器:slave2(从)
同样yum下载mariadb和修改数据库配置文件
root@slave2 ~# yum -y install mariadb mariadb-server
root@slave2 ~# vim /etc/my.cnf
这里不需要加log-bin是因为这个从库没有当主的打算,不当主库也没有从库来复制你的二进制日志是吧
在my.cnf中写入:
server-id=4
relay-log=mysql-relay
同上重启数据库、配置从库、查看是否成功
root@slave2 ~# systemctl restart mariadb
root@slave2 ~# mysql
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.150.102',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=245;
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.150.102
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 384
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 668
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
二、半同步
服务器:master(主)
检查是否具有自动加载功能
MariaDB [(none)]> show variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+
安装半同步,开启半同步复制,默认是关闭的。在高可用架构下服务器有可能由主变成从,所以把slave也装上
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled = 1;
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave soname 'semisync_slave.so';
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
查看半同步是否成功(on表示开启)
MariaDB [(none)]> show variables like '%semi%';
+------------------