使用docker来搭建测试主从复制&MHA高可用功能。本文为操作记录手册。
共四台机器：
- 一台主节点
- 两台从节点
- 一台配置MHA故障转移。
实现目标：
- 主从同步复制。
- 故障转移。
使用docker安装MySQL主从容器
构建基于MySQL5.7的免登陆镜像
下载docker desktop不再赘述。
我们使用mysql:5.7的镜像
docker pull mysql:5.7因为MHA集群需要每个MySQL节点之间的网络可以使用ssh无密登录，所以提前配置Dockerfile制作无密镜像。
#制作镜像参考资料 #https://blog.csdn.net/qq_36274515/article/details/94589518Dockerfile文件内容
FROM mysql:5.7 MAINTAINER kevin lee RUN apt-get update && \ apt-get install -y net-tools openssh-server psmisc wget vim RUN ssh-keygen -t rsa -f ~/.ssh/id_rsa -P '' && cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys && \ sed -i 's/PermitEmptyPasswords yes/PermitEmptyPasswords no /' /etc/ssh/sshd_config && \ sed -i 's/PermitRootLogin without-password/PermitRootLogin yes /' /etc/ssh/sshd_config && \ echo " StrictHostKeyChecking no" >> /etc/ssh/ssh_config && \ echo " UserKnownHostsFile /dev/null" >> /etc/ssh/ssh_config && \ echo "root:1234" | chpasswd CMD [ "sh", "-c", "service ssh start; bash"]在dockerfile所在目录执行：
docker build -t mysql5.7-all .
创建自定义网络mynetwork，网段为 172.16.0.0
docker network create --subnet=172.16.0.0/16 mynetwork根据镜像创建容器节点
创建docker主节点，命名为master01,密码为root，ip设置172.16.1.1（网段使用了我自定义的mynetwork：172.16.0.0）
docker run --name mysql-master01 --privileged=true --net mynetwork --ip 172.16.1.1 -p 3306:3306 -v /Users/kevin/develop/docker/mysql/master01/conf:/etc/mysql -v /Users/kevin/develop/docker/mysql/master01/logs:/var/log/mysql -v /Users/kevin/develop/docker/mysql/master01/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql5.7-all --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci参数说明：
-p 3306:3306：将主机的3306端口映射到docker容器的3306端口。--name mysql-master01：运行服务名字-v /Users/kevin/develop/docker/mysql/master01/conf:/etc/mysql ：将主机/Users/kevin/develop/docker/mysql/master01录下的conf/my.cnf 挂载到容器的 /etc/mysql/-v /Users/kevin/develop/docker/mysql/master01/logs:/var/log/mysql：将主机/Users/kevin/develop/docker/mysql/master01目录下的 logs 目录挂载到容器的 /logs。-v /Users/kevin/develop/docker/mysql/master01/data:/var/lib/mysql ：将主机/Users/kevin/develop/docker/mysql/master01目录下的data目录挂载到容器的 /var/lib/mysql-e MYSQL_ROOT_PASSWORD=123456：初始化 root 用户的密码。把master01中的默认配置文件my.cnf拷贝到/Users/kevin/develop/docker/mysql/master01/conf中
docker cp mysql-master01:/etc/mysql/my.cnf /Users/kevin/develop/docker/mysql/master01/conf通过 navicat等客户端工具来访问master01。注意：已经通过3306映射到3306端口，那么直接通过
127.0.0.1:3306即可访问了。使用docker安装2台从库。slave01、slave02、manager01
# 先创建slave01：172.16.2.1，对应本机3307 docker run --name mysql-slave01 --privileged=true --net mynetwork --ip 172.16.2.1 -p 3307:3306 -v /Users/kevin/develop/docker/mysql/slave01/conf:/etc/mysql -v /Users/kevin/develop/docker/mysql/slave01/logs:/var/log/mysql -v /Users/kevin/develop/docker/mysql/slave01/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql5.7-all --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci # 再创建slave02：172.16.2.2，对应本机3308 docker run --name mysql-slave02 --privileged=true --net mynetwork --ip 172.16.2.2 -p 3308:3306 -v /Users/kevin/develop/docker/mysql/slave02/conf:/etc/mysql -v /Users/kevin/develop/docker/mysql/slave02/logs:/var/log/mysql -v /Users/kevin/develop/docker/mysql/slave02/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql5.7-all --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci #创建manager01管理节点 docker run --name mysql-manager01 --privileged=true --net mynetwork --ip 172.16.3.1 -p 3309:3306 -v /Users/kevin/develop/docker/mysql/manager01/conf:/etc/mysql -v /Users/kevin/develop/docker/mysql/manager01/logs:/var/log/mysql -v /Users/kevin/develop/docker/mysql/manager01/mha/log:/var/log/mha -v /Users/kevin/develop/docker/mysql/manager01/data:/var/lib/mysql -v /Users/kevin/develop/docker/mysql/manager01/mha/manager-cnf:/etc/mha-manager-cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql5.7-all --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
MySQL主从配置操作
配置master01数据库为主节点
编辑
/Users/kevin/develop/docker/mysql/master01/conf/my.cnfmaster01的配置文件注意，主从同步配置如果想要生效，需要几个条件：
- 从库服务器能连通主库
- 主库开启binlog日志（设置log-bin参数）
- 主从server-id不同
具体步骤：在master01的my.cnf文件中[client]节点之前，添加log bin相关的配置。
#log bin log_bin=mysql-bin server-id=11 sync-binlog=1 binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=sys #binlo-do-db=lagou #半同步semi配置 rpl_semi_sync_master_timeout=1000 rpl_semi_sync_master_enabled=1 relay_log=mysql_relay_on log_slave_updates=1 auto_increment_offset=1 auto_increment_increment=2log_bin开启binlog同步方式。server-id指定对应的编号。sync-binlogsync_binlog=N，使执行N次写入后，与硬盘同步。1是最安全的，但是也是最慢的。binlog-ignore-db同步时需要忽略的数据库表，一般忽略系统库。binlo-do-db和忽略相反，表示设置仅需要同步的数据库。配置完成的完整my.cnf如下：
# Copyright (c) 2014, 2015, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; version 2 of the License. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA # # The MySQL Community Server configuration file. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] skip-host-cache skip-name-resolve user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql explicit_defaults_for_timestamp # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 #log-error = /var/log/mysql/error.log # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. #log bin log_bin=mysql-bin server-id=11 sync-binlog=1 binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=sys #binlo-do-db=lagou
重启主节点master01。
docker restart mysql-master01
授权从节点可访问
grant replication slave on *.* to 'root'@'%'identified by '123456'; grant all privileges on *.* to 'root'@'%'identified by '123456'; flush privileges;校验是否配置成功
show master status; #展示结果如下： +------------------+----------+--------------+-------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+-------------------------------------------+-------------------+ | mysql-bin.000004 | 1000 | | information_schema,performance_schema,sys | | +------------------+----------+--------------+-------------------------------------------+-------------------+说明master配置成功。
校验是否支持半同步。
mysql> select @@have_dynamic_loading; +------------------------+ | @@have_dynamic_loading | +------------------------+ | YES | +------------------------+安装插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
查看半同步插件是否已经安装。
mysql> show plugins; +----------------------------+----------+--------------------+--------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+--------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | +----------------------------+----------+--------------------+--------------------+---------+配置slave01、slave02。
配置从节点slave01，在配置文件中追加以下内容：
#log-bin server-id=21 relay_log=mysql_relay-bin read_only=1server-id: 节点编号relay_log: 定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录，文件名为host_name-relay-bin.nnnnnnread_only: 如需设置为只读状态，将该read_only参数设置为1或TRUE状态rpl_semi_sync_slave_enabled:relay_log_purge: 是否自动清空不再需要中继日志，默认值为1(启用)。配置从节点slave02，在配置文件中追加以下内容：
#log-bin server-id=22 relay_log=mysql_relay-bin read_only=1分别重启slave01和slave02
docker restart mysql-slave01 docker restart mysql-slave02slave01和slave02的命令行中执行：分别将slave01和slave02的master指向master01
#先在master01中查看binlog文件名称： show master status; stop slave; #配置master节点 change master to master_host='172.16.1.1',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=219; start slave;
分别进入到slave01和slave02实例中，执行sql语句安装半同步复制插件：
install plugin rpl_semi_sync_master soname 'semisync_master.so';在saleve01、slave02的my.cnf配置文件中增加
半同步复制功能。#semi配置，半同步复制 rpl_semi_sync_slave_enabled=1 binlog-ignore-db=performance_schema binlog-ignore-db=information_schema binlog-ignore-db=sys relay_log_purge=0再次重启两台从节点
docker restart mysql-slave01 docker restart mysql-slave02
安装配置MHA
当前四台机器的情况如下：
| 主机名称 | ip | |
|---|---|---|
| master01 | 172.16.1.1 | |
| slave01 | 172.16.2.1 | |
| slave02 | 172.16.2.2 | |
| manager01 | 172.16.3.1 |
MHA集群配置
在master01、slave01、slave02，manager01上安装MHA node
#在docker中配置debain类型的source源
echo "deb http://ftp.de.debian.org/debian stretch main" | tee /etc/apt/sources.list.d/docker.list
#安装依赖perl
apt-get install libdbd-mysql-perl -y
#安装mha4mysql-node
apt-get install mha4mysql-node -y在manager01上安装MHA manager
apt-get install mha4mysql-manager -y在manager01上增加配置文件
touch /etc/mha-manager-cnf/manage.cnf
[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
user=root
password=123456
ssh_user=root
repl_user=root
repl_password=123456
ping_interval=1
[server1]
hostname=172.16.1.1
master_binlog_dir=/var/lib/mysql
port=3306
[server2]
hostname=172.16.2.1
port=3306
[server3]
hostname=172.16.2.2
port=3306命令执行
# 检查SSH配置免费互通
masterha_check_ssh --conf=/etc/mha-manager-cnf/manage.cnf
#[info] All SSH connection tests passed successfully.
# 验证配置
masterha_check_repl --conf=/etc/mha-manager-cnf/manage.cnf
# 运行
masterha_manager --conf=/etc/mha-manager-cnf/manage.cnf &故障转移验证（IP漂移）
当停止master01后，报出以下日志：
----- Failover Report -----
manage: MySQL Master failover 172.16.1.1(172.16.1.1:3306) to 172.16.2.1(172.16.2.1:3306) succeeded
Master 172.16.1.1(172.16.1.1:3306) is down!
Check MHA Manager logs at 83f655906f9b:/var/log/mha/app1/manager.log for details.
Started automated(non-interactive) failover.
The latest slave 172.16.2.1(172.16.2.1:3306) has all relay logs for recovery.
Selected 172.16.2.1(172.16.2.1:3306) as a new master.问题汇总
启动过程中无法识别半同步相关的配置信息：
日志如下：
2021-03-15T15:19:49.801683Z 0 [ERROR] unknown variable 'rpl_semi_sync_slave_enabled=1'
2021-03-15T15:19:49.828535Z 0 [ERROR] Aborting解决方法：
https://blog.csdn.net/qq_36751833/article/details/99413313
log event entry exceeded max_allowed_packet;
在slave上没有发现同步的数据，查看日志发现报错如下：
Increase max_allowed_packet on master; the first event 'mysql-bin.000002' at 869, the last event read from './mysql-bin.000002' at 123, the last byte read from './mysql-bin.000002' at 888.当主库运行较长时间，进行过大量DDL操作，从库从二进制较早地址开始复制，超过一个数据包的大小，我们将数据包改大即可。在主库和从库分别执行，这种改法重启后会失效
set global max_allowed_packet=100*1024*1024;
stop slave;
start slave;
select @@global.max_allowed_packet/1024/1024 MB;另外一种方式为，修改配置文件，在my.cnf里追加
max_allowed_packet = 256Mbinlog truncated in the middle of event; consider out of disk space on master;
数据库截断位置错误，修改为正确的binlog截断位置即可。
# 先暂停同步
stop slave;
#修改为正确的位置
change master to master_host='172.16.1.1',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=219;查找正确的同步位置可以在同步之前使用show master status查看。如果忘记这一步，则需要通过命令将master下的binlog 文件转为sql文件，然后查看注释中的 end_log_pos 标记来识别具体的位置。
mysqlbinlog /Users/kevin/develop/docker/mysql/master01/data/mysql-bin.000005 --result-file=/Users/kevin/develop/docker/mysql/mysql-bin.000005.sql观察mysql-bin.000005.sql下的 end_log_pos
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210315 22:52:37 server id 11 end_log_pos 123 CRC32 0xacc62a58 Start: binlog v 4, server v 5.7.33-log created 210315 22:52:37 at startup
ROLLBACK/*!*/;
BINLOG '
tXRPYA8LAAAAdwAAAHsAAAAAAAQANS43LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAC1dE9gEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AVgqxqw=
'/*!*/;
# at 123
#210315 22:52:37 server id 11 end_log_pos 154 CRC32 0xdebd23bf Previous-GTIDs
# [empty]
# at 154
#210316 0:20:01 server id 11 end_log_pos 219 CRC32 0x8bd51cba GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210316 0:20:01 server id 11 end_log_pos 318 CRC32 0x82f95eaf Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1615825201/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=224,@@session.collation_connection=224,@@session.collation_server=224/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE DATABASE `lagou`
/*!*/;
# at 318
#210316 0:21:57 server id 11 end_log_pos 383 CRC32 0x23f40ae5 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;可以看到 end_log_pos 219 这个位置比较适合同步，在创建lagou这个数据库之前的位置。
ssh免密登录验证失败
docker 里可以重启ssh服务后再试
service ssh restart报错：Error happened on checking configurations. Redundant argument in sprintf at /usr/share/perl5/MHA/NodeUtil.pm line 201.
参考资料：
https://blog.csdn.net/ctypyb2002/article/details/88344274报错：None of slaves can be master. Check failover configuration file or log-bin settings in my.cnf
这个错误原因在与集群中的slave节点的数据库配置文件/ect/my.cnf没有设置log-bin参数，解决办法就是将所有slave节点的数据库配置文件加上log-bin=XXX参数，重启数据库服务即可。
同master01，配置slave01和slave02：log-bin=mysql-bin，修改后重启slave01、slave02。
参考资料
https://www.it610.com/article/1295778313125568512.htm