Mysql-双主复制

Mysql通过双主复制再结合Keepalived来实现Mysql的高可用。

准备两台服务器，先安装mysql，这里通过docker来进行验证。

172.16.103.90 - 主1
172.16.103.91 - 主2

安装配置

创建挂载目录

在172.16.103.90、172.16.103.91 按如下示例（真实环境以实际的数据盘规划目录为准），创建相同的目录

/var/lib/mysql/data：mysql数据目录

/var/lib/mysql/conf：mysql配置目录

# 创建mysql 数据目录/data、配置目录/conf
mkdir -p /var/lib/mysql/data /var/lib/mysql/conf 
# docker 复制数据库脚本到容器的/tmp目录
# docker cp /var/lib/mysql/dbscript mysql:tmp

mysql.cnf 配置

文件参考/conf/mysql/mysql.cnf

# mysql.cnf 172.16.103.90
[mysqld]
server_id=90
log-bin=mysql-bin
binlog-do-db=dmm
# 多个库再增加一行配置
# binlog-do-db=daip
binlog-ignore-db=mysql
skip-name-resolve
host_cache_size=0
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=true
default-time_zone='+8:00'

# mysql.cnf 172.16.103.91
[mysqld]
server_id=91
log-bin=mysql-bin
binlog-do-db=dmm
binlog-ignore-db=mysql
skip-name-resolve
host_cache_size=0
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=true
default-time_zone='+8:00'

docker容器

在172.16.103.90、172.16.103.91 按如下示例运行docker容器

# 启动mysql8
docker run -d --name mysql8 --restart=always \
-p 30001:3306 \
-v /var/lib/mysql/data:/var/lib/mysql \
-v /var/lib/mysql/conf/mysql.cnf:/etc/mysql/conf.d/mysql.cnf:ro \
-e TZ=Asia/Shanghai \
-e MYSQL_ROOT_PASSWORD=Wiseda@1234 \
172.16.102.2:5000/library/mysql:8.0.30

进入到容器里，创建同步账号

# 进入mysql8容器
docker exec -it mysql8 sh
# 以root连接 mysql
mysql -u root -pWiseda@1234

# 创建同步账号
# 手工创建一个用户
CREATE USER 'datasync'@'%' IDENTIFIED WITH mysql_native_password BY 'Wiseda@2024';
# 授权datasync用户同步复制权限
GRANT REPLICATION SLAVE ON *.* TO 'datasync'@'%';
# 刷新权限
flush privileges;
# 显示master状态
show master status;

指定对方的Host进行同步复制

# 172.16.103.90 执行
CHANGE MASTER TO
MASTER_HOST='172.16.103.91',
MASTER_PORT=30001,
MASTER_USER='datasync',
MASTER_PASSWORD='Wiseda@2024',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=835;

# 启动slave进行同步复制
start slave;

# 172.16.103.91 执行
CHANGE MASTER TO
MASTER_HOST='172.16.103.90',
MASTER_PORT=30001,
MASTER_USER='datasync',
MASTER_PASSWORD='Wiseda@2024',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=835;

# 启动slave进行同步复制
start slave;
# 查看slave状态
show slave status\G;
# Slave_IO_Running、Slave_SQL_Running均为yes表示从节点已经可以正常同步master
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes

# 停止同步复制
stop slave;

keepalived检测

keepalived安装配置

mysql检测脚本

在90、91安装配置好keepalived之后，只需要添加检测mysql的脚本即可

#!/bin/bash
A=`ps -C mysqld --no-header |wc -l`
if [ $A -eq 0 ]; then
   docker start mysql8
   sleep 5
   if [ `ps -C mysqld --no-header |wc -l` -eq 0 ]; then
        killall keepalived
   fi
fi

keepalived.conf配置

/etc/keepalived/keepalived.conf 配置文件,增加vrrp_script 脚本文件检查mysql是否存活

! Configuration File for keepalived

global_defs {
   #路由id：当前安装keepalived节点主机的标识符，全局唯一
   router_id keep_90
}
vrrp_script check_mysql_alive {
   script "/etc/keepalived/check_mysql.sh"
   interval 10  #每隔10秒运行上一行的脚本
   weight -10 # 如果脚本运行成功，则权重-10
}

vrrp_instance VI_1 {
    # 表示的状态，当前的128服务器为nginx的主节点，MASTER/BACKUP
    state MASTER
    # 当前实例绑定的网卡
    interface ens160
    # 保证主备节点一致
    virtual_router_id 51
    # 优先级/权重，谁的优先级高，在MASTER挂掉以后，就能成为MASTER
    priority 100
    # 主备之间同步检查的时间间隔，默认1s
    advert_int 1
    # 认证授权的密码，防止非法节点的进入
    authentication {
        auth_type PASS
        auth_pass Wiseda@ka2024
    }
    track_script {
    	 check_mysql_alive # 追踪mysql脚本
    }
    virtual_ipaddress {
        172.16.103.99
    }
}

# Mysql-双主复制

# 安装配置

# 创建挂载目录

# mysql.cnf 配置

# docker容器

# keepalived检测

# keepalived安装配置

# mysql检测脚本

# keepalived.conf配置