Mysql主从复制与高可用主备切换搭建完整详细版

本文详述了使用docker在三台centos上搭建MySQL主从复制，配置MHA（Master High Availability）实现高可用，并进行故障转移测试的过程。包括创建数据库、安装mha4mysql-node和manager、配置主从同步、处理安装问题以及验证主备切换的正确性。

本文使用docker进行相关的安装配置。
整体规划配置

容器名称	容器IP	安装服务	状态
mysql_master	172.19.0.2	Mysql、mhamanager_node	master
mysql_slave_1	172.19.0.3	Mysql、mhamanager_node	slave
mysql_slave_2	172.19.0.4	Mysql、mhamanager_node	slave
mha_master	172.19.0.5	mhamanager_master	主从切换

一、安装三台数据库

1. 使用docker容器启动三台centos。

Dockerfile-mysql

#base image
FROM centos

#MAINTAINER 
MAINTAINER desperado


RUN yum update -y
RUN yum install initscripts -y
RUN yum install  crontabs -y
RUN  yum install -y net-tools
RUN yum install -y telnet-server
RUN yum install -y openssh-server nano lsof
RUN mkdir /var/run/sshd
RUN echo 'root:password' | chpasswd
RUN sed -i 's/#PermitRootLogin prohibit-password/PermitRootLogin yes/' /etc/ssh/sshd_config
RUN /usr/sbin/sshd -D &
RUN cd /usr/local
RUN yum install -y mysql 
RUN yum install -y mysql-server
EXPOSE 22


# 设置时区
RUN /bin/cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime && echo 'Asia/Shanghai' >/etc/timezone

mysql-master-compose.yml

version: '2'

services: 
  mysql_server: 
    build: 
      context: ./
      dockerfile: Dockerfile-mysql

    container_name: mysql_master
    privileged: true
     
    command: ['/usr/sbin/init']
              
    ports: 
      - "15340:3306"

    networks: 
      mysql_server_network: 
        ipv4_address: 172.19.0.2

    tty: true


networks: 
  mysql_server_network: 
    driver: bridge
    ipam: 
      driver: default
      config: 
      - subnet: 172.19.0.0/16
        gateway: 172.19.0.1

mysql-slave1-compose.yml

  mysql_slave_1_server: 
    build: 
      context: ./
      dockerfile: Dockerfile-mysql

    container_name: mysql_slave_1
    privileged: true
     
    command: ['/usr/sbin/init']
              
    ports: 
      - "15341:3306"

    networks: 
      mysql_server_network: 
        ipv4_address: 172.19.0.3

    tty: true


networks: 
  mysql_server_network: 
    driver: bridge
    ipam: 
      driver: default
      config: 
      - subnet: 172.19.0.0/16
        gateway: 172.19.0.1

mysql-slave2-compose.yml

version: '2'

services: 
  mysql_slave_2_server: 
    build: 
      context: ./
      dockerfile: Dockerfile-mysql

    container_name: mysql_slave_2
    privileged: true
     
    command: ['/usr/sbin/init']
              
    ports: 
      - "15342:3306"

    networks: 
      mysql_server_network: 
        ipv4_address: 172.19.0.4

    tty: true


networks: 
  mysql_server_network: 
    driver: bridge
    ipam: 
      driver: default
      config: 
      - subnet: 172.19.0.0/16
        gateway: 172.19.0.1

通过docker-compose启动三个容器

docker-compose -f mysql-msater-compose.yml up
docker-compose -f mysql-slave1-compose.yml up
docker-compose -f mysql-slave1-compose.yml up

启动成功，可以看到三个容器
![image.png](https://img-blog.csdnimg.cn/img_convert/772920b63d3e2f6e663e81e792449380.png#clientId=u7b7ded56-93ab-4&from=paste&height=186&id=ub315ad07&margin=[object Object]&name=image.png&originHeight=186&originWidth=548&originalType=binary&size=13012&status=done&style=none&taskId=uf36e68da-3df7-4722-89da-e8c09280920&width=548)

2. 在三台centos里面都安装MySQL数据库。

进入容器内安装MySQL。

# 查看启动的容器
docker ps
# 得到如下信息
CONTAINER ID          NAMES
73d2f0308fc4        mysql_slave_2
60992be33db9        mysql_slave_1
1606fbf7a5b5        mysql_master

# 进入对应的容器
docker exec -it 容器id /bin/sh

进入之后分别安装MySQL，以master为例（三台分布执行）

# 进入容器
docker exec -it 1606fbf7a5b5 /bin/sh
# 启动mysql
service mysqld start
# 修改默认的数据库密码和权限，原密码为空，执行如下命令，修改密码未root123
mysqladmin -uroot -p password root123
# 登录mysql。修改连接权限
mysql -uroot -proot123
# 切换数据库
use mysql;
# 修改root账号允许远程连接
update user set host = '%' where User = 'root';
# 刷新权限
flush privileges;

3. 配置主从同步

在master的数据库里面新建一个用户，用于进行数据的同步

# 设置服务id
set GLOBAL server_id = 1;
# 安装启动半同步插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled=ON;
install plugin rpl_semi_sync_master soname 'semisync_slave.so';
set global rpl_semi_sync_master_enabled=OFF;

# 新建用户(ip请根据自己实际情况修改)
create user 'backup_user'@'172.19.0.%' identified WITH mysql_native_password by 'backup_123';
grant replication slave on *.* to 'backup_user'@'172.19.0.%';
flush privileges;

# 查询master的相关信息
show master status;
# 查询等到如下信息，(file即为下面master_log_file的值，Position即为master_log_pos的值)
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     1533 |              |                  |                   |

在两个slave的数据库里面配置同步的信息。

# slave1
# 设置服务id
set GLOBAL server_id = 2;

# 安装启动半同步插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled=OFF;
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=ON;

#关闭自动清理relay log
set global relay_log_purge =0;

#设置为只读
set global read_only = 1;

# 新建用户(ip请根据自己实际情况修改)
create user 'backup_user'@'172.19.0.%' identified WITH mysql_native_password by 'backup_123';
grant replication slave on *.* to 'backup_user'@'172.19.0.%';
flush privileges;

# 配置master信息(master的IP、账号、密码等信息请根据实际更改，)
change master to master_host='172.19.0.2', master_port=3306, master_user='backup_user',
master_password='backup_123', master_log_file='binlog.000001', master_log_pos=1533;
# 开启复制
start slave;


#slave2
# 设置服务id
set GLOBAL server_id = 3;

# 安装启动半同步插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled=OFF;
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=ON;

#关闭自动清理relay log
set global relay_log_purge =0;

#设置为只读
set global read_only = 1;

# 新建用户(ip请根据自己实际情况修改)
create user 'backup_user'@'172.19.0.%' identified WITH mysql_native_password by 'backup_123';
grant replication slave on *.* to 'backup_user'@'172.19.0.%';
flush privileges;

# 配置master信息(master的IP、账号、密码等信息请根据实际更改，)
change master to master_host='172.19.0.2', master_port=3306, master_user='backup_user',
master_password='backup_123', master_log_file='binlog.000001', master_log_pos=1533;
# 开启复制
start slave;

查询slave状态是否正常。

# 在slave数据库中执行
show slave status\G
# 查看如下两个指标是否都为Yes
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
# 查看是否执行语句报错
Last_IO_Error
Last_SQL_Error

到目前为止已经配置好了MySQL一主二从的MySQL复制主从结构。
在master所在的数据库中进行建库、建表以及对表的增删改查都将同步到两个salve数据库中。

如果上面一切都正常，就是没有进行数据的复制，一定要去看master数据的日志，错误一般都会在其中。
日志地址：/var/log/mysql/mysqld.log

二、MySQL高可用

MySQL的高可用使用MHA（Master High Availability）进行实现。

1. 先在上面三个安装了数据库的centos里面安装mha4mysql-node。

# 下载安装包
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm

# 安装
yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

2. 再启动一台全新的centos容器

Dockerfile-mha

#base image
FROM centos

#MAINTAINER 
MAINTAINER desperado

RUN yum update -y
RUN yum install initscripts -y
RUN yum install -y net-tools
RUN yum install -y wget
RUN yum install -y telnet-server
# 另外我还希望创建的镜像能够安装ssh并允许密码登录
RUN yum install -y openssh-server nano lsof
RUN mkdir /var/run/sshd
RUN echo 'root:password' | chpasswd
RUN sed -i 's/#PermitRootLogin prohibit-password/PermitRootLogin yes/' /etc/ssh/sshd_config
RUN /usr/sbin/sshd -D &
EXPOSE 22


# 设置时区
RUN /bin/cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime && echo 'Asia/Shanghai' >/etc/timezone

mha-compose.yml

version: '2'

services: 
  mha_server: 
    build: 
      context: ./
      dockerfile: Dockerfile-mha

    container_name: mha_master
    privileged: true
     
    command: ['/usr/sbin/init']
              
    ports: 
      - "15344:3306"

    networks: 
      mysql_server_network: 
        ipv4_address: 172.19.0.5

    tty: true


networks: 
  mysql_server_network: 
    driver: bridge
    ipam: 
      driver: default
      config: 
      - subnet: 172.19.0.0/16
        gateway: 172.19.0.1

通过docker-compose启动新容器

docker-compose -f mha-compose.yml up

启动成功，可以看到一个新容器
![image.png](https://img-blog.csdnimg.cn/img_convert/f791eb6e8932ddea675257e7d31098b8.png#clientId=u7b7ded56-93ab-4&from=paste&height=275&id=u7bf61fdf&margin=[object Object]&name=image.png&originHeight=275&originWidth=628&originalType=binary&size=17692&status=done&style=none&taskId=ued47402e-21b3-4ad1-a3fd-ed954f09ed0&width=628)

3. 进入当前容器，安装node和manager

# 进入对应的容器
docker exec -it 容器id /bin/sh

# 安装node
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

4. 安装mha4mysql-manager

install_manager.sh

mkdir -p /usr/local/tmp_install_manager
cd /usr/local/tmp_install_manager

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Sub-Exporter-Progressive-0.001013-5.el8.noarch.rpm
yum install -y perl-Sub-Exporter-Progressive-0.001013-5.el8.noarch.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/AppStream/x86_64/os/Packages/perl-Module-CPANfile-1.1002-7.module_el8.3.0+445+46ff4549.noarch.rpm
yum install -y perl-Module-CPANfile-1.1002-7.module_el8.3.0+445+46ff4549.noarch.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Variable-Magic-0.62-3.el8.x86_64.rpm
yum install -y perl-Variable-Magic-0.62-3.el8.x86_64.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Module-Implementation-0.09-15.el8.noarch.rpm
yum install -y perl-Module-Implementation-0.09-15.el8.noarch.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-B-Hooks-EndOfScope-0.21-6.el8.noarch.rpm
yum install -y perl-B-Hooks-EndOfScope-0.21-6.el8.noarch.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Package-Stash-XS-0.28-17.el8.x86_64.rpm
yum install -y perl-Package-Stash-XS-0.28-17.el8.x86_64.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Dist-CheckConflicts-0.11-11.el8.noarch.rpm
yum install -y perl-Dist-CheckConflicts-0.11-11.el8.noarch.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Package-Stash-0.37-9.el8.noarch.rpm
yum install -y perl-Package-Stash-0.37-9.el8.noarch.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-namespace-clean-0.27-7.el8.noarch.rpm
yum install -y perl-namespace-clean-0.27-7.el8.noarch.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Sub-Identify-0.14-6.el8.x86_64.rpm
yum install -y perl-Sub-Identify-0.14-6.el8.x86_64.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-namespace-autoclean-0.28-10.el8.noarch.rpm
yum install -y perl-namespace-autoclean-0.28-10.el8.noarch.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Devel-GlobalDestruction-0.14-5.el8.noarch.rpm
yum install -y perl-Devel-GlobalDestruction-0.14-5.el8.noarch.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Devel-StackTrace-2.03-2.el8.noarch.rpm
yum install -y perl-Devel-StackTrace-2.03-2.el8.noarch.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Class-Data-Inheritable-0.08-27.el8.noarch.rpm
yum install -y perl-Class-Data-Inheritable-0.08-27.el8.noarch.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Exception-Class-1.44-2.el8.noarch.rpm
yum install -y perl-Exception-Class-1.44-2.el8.noarch.rpm

wget http://www.rpmfind.net/linux/dag/redhat/el5/en/x86_64/dag/RPMS/perl-Perl-Tidy-20090616-1.el5.rf.noarch.rpm
yum install -y perl-Perl-Tidy-20090616-1.el5.rf.noarch.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-PadWalker-2.3-2.el8.x86_64.rpm
yum install -y perl-PadWalker-2.3-2.el8.x86_64.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Devel-Caller-2.06-15.el8.x86_64.rpm
yum install -y perl-Devel-Caller-2.06-15.el8.x86_64.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Devel-LexAlias-0.05-16.el8.x86_64.rpm
yum install -y perl-Devel-LexAlias-0.05-16.el8.x86_64.rpm

wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Eval-Closure-0.14-5.el8.noarch.rpm
yum install -y perl-Eval-Closure-0.14-5.el8.noarch.rpm


wget http://www.rpmfind.net/linux/centos/8.3.2011/PowerTools/x86_64/os/Packages/perl-Params-ValidationCompiler-0.27-1.el8.noarch.rpm
yum install -y perl-Params-ValidationCompiler-0.27-1.el8.noarch.rpm


wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Ref-Util-XS-0.117-2.el8.x86_64.rpm
yum install -y perl-Ref-Util-XS-0.117-2.el8.x86_64.rpm


wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Ref-Util-0.203-4.el8.noarch.rpm
yum install -y perl-Ref-Util-0.203-4.el8.noarch.rpm


wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Class-Method-Modifiers-2.12-8.el8.noarch.rpm
yum install -y perl-Class-Method-Modifiers-2.12-8.el8.noarch.rpm


wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Role-Tiny-2.000006-2.el8.noarch.rpm
yum install -y perl-Role-Tiny-2.000006-2.el8.noarch.rpm


wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Specio-0.42-2.el8.noarch.rpm
yum install -y perl-Specio-0.42-2.el8.noarch.rpm


wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-MIME-Types-2.17-3.el8.noarch.rpm
yum install -y perl-MIME-Types-2.17-3.el8.noarch.rpm


wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Params-Validate-1.29-5.el8.x86_64.rpm
yum install -y perl-Params-Validate-1.29-5.el8.x86_64.rpm


wget http://www.rpmfind.net/linux/dag/redhat/el5/en/x86_64/dag/RPMS/perl-Log-Dispatch-2.26-1.el5.rf.noarch.rpm
yum install -y perl-Log-Dispatch-2.26-1.el5.rf.noarch.rpm


wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Import-Into-1.002005-7.el8.noarch.rpm
yum install -y perl-Import-Into-1.002005-7.el8.noarch.rpm


wget http://www.rpmfind.net/linux/dag/redhat/el5/en/x86_64/dag/RPMS/perl-Parallel-ForkManager-0.7.5-2.2.el5.rf.noarch.rpm
yum install -y perl-Parallel-ForkManager-0.7.5-2.2.el5.rf.noarch.rpm

wget http://www.rpmfind.net/linux/dag/redhat/el6/en/x86_64/extras/RPMS/perl-Net-Telnet-3.03-2.el6.rfx.noarch.rpm
yum install -y perl-Net-Telnet-3.03-2.el6.rfx.noarch.rpm