MySQL双机互备笔记

1. 创建网络

创建网络主节点：

docker swarm init --advertise-addr 192.168.xx.121

加入主节点：

docker swarm join --token SWMKM..... 192.168.xx.121:2377

主节点上创建网络：

docker network create --subnet=192.168.1.0/24 -d overlay --attachable my-overlay

2. 启动Mysql

在两台机器上创建以下目录：/usr/local/dockercontainer/mysql/conf/和 /usr/local/dockercontainer/mysql/data。/usr/local/dockercontainer/mysql/conf/目录下创建文件 "my.cnf" ,内容如下：

# Copyright (c) 2017, 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 Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
log-bin=mysql-bin
server-id=1
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Custom config should go here
!includedir /etc/mysql/conf.d/
max_connections=1000
wait_timeout=120
interactive_timeout=300
lower_case_table_names=1
slave_sql_verify_checksum=0
max_allowed_packet=1073741824

其中"server-id"需要修改，集群中不能有相同的"server-id"。

启动MySql命令如下。注意启动时指定网络和ip。 机器1上执行：

docker run --restart=unless-stopped -d  --network my-overlay --ip 192.168.1.11 --name mysql -p 3306:3306 -v /usr/local/dockercontainer/mysql/conf/my.cnf:/etc/mysql/my.cnf -v /usr/local/dockercontainer/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root mysql:8.0.27

机器2上执行：

docker run --restart=unless-stopped -d  --network my-overlay --ip 192.168.1.12 --name mysql -p 3306:3306 -v /usr/local/dockercontainer/mysql/conf/my.cnf:/etc/mysql/my.cnf -v /usr/local/dockercontainer/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root mysql:8.0.27

3. 设置Master

登录MySQL主库，执行以下命令：

CREATE USER 'slaveUser'@'192.168.1.12' IDENTIFIED BY 'xiaoke_1256';
GRANT REPLICATION SLAVE ON *.* TO 'slaveUser'@'192.168.1.12';
FLUSH PRIVILEGES;

ALTER USER 'slaveUser'@'192.168.1.12' IDENTIFIED WITH mysql_native_password by 'xiaoke_1256';

执行SHOW MASTER STATUS命令，记录下“File”和“Position”两个字段。

4. 设置Slave

登录从库，执行以下命令：

CHANGE MASTER TO MASTER_HOST='192.168.1.11', MASTER_PORT=3306,
    MASTER_USER='slaveUser', MASTER_PASSWORD='xiaoke_1256',
    MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1998;

其中“192.168.1.11”是主库的ip；MASTER_LOG_FILE 和 MASTER_LOG_POS 两个字段都是从主库的SHOW MASTER STATUS命令中获得。 然后执行start slave;命令，启动复制。

执行show slave status命令查看从库的状态。其中“SLAVE_IO_RUNING”和“SLAVE_SQL_RUNNING”两个字段应该为“Yes”。如果不是需查看日志。

5. 主从互换

交换主库和从库，重复 3 ~ 4的步骤。