Docker搭建主从复制mysql数据库

2020.08.26 15:08:20
38
阅读约 2 分钟

试验准备 #

服务器+docker

安装镜像 #

拉取Mysql5.7.15镜像到本地

docker pull mysql:5.7.15

准备MYSQL配置文件 #

准备主从节点的配置文件
img

[mysqld]
server_id = 1
log-bin= mysql-bin
read-only=0

replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
server_id = 2
log-bin= mysql-bin
read-only=1

replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

Docker分别运行Mysql 主/从两个容器 #

docker run --name mysql-master -d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /mnt/mysql/master/data:/var/lib/mysql -v /mnt/mysql/master/conf/my.cnf:/etc/mysql/my.cnf mysql:5.7.15

同样的,将从节点运行起来

docker run --name mysql-slave -d -p 3308:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /mnt/mysql/slave/data:/var/lib/mysql -v /mnt/mysql/slave/conf/my.cnf:/etc/mysql/my.cnf mysql:5.7.15

登陆MYSQL主节点配置同步信息 #

# 进入docker设置:
docker exec -it mysql-master bash
mysql -u root -p123456

在mysql client中执行

mysql > grant replication slave on *.* to 'slave'@'%' identified by '123456';
mysql > flush privileges;

获取status

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 582
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

登陆MYSQL从节点配置同步信息 #

登陆mysql

# 172.17.0.2 是你本机的内网ip
mysql -u root -h 172.17.0.2 -P3308 -p123456

在mysql client中执行

mysql > change master to master_host='172.17.0.2',master_port=3307,master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=582;

mysql > start slave;

获取status,得到类似如下的输出:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.xx
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 582
               Relay_Log_File: 4254044df25b-relay-bin.000002
                Relay_Log_Pos: 795
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

错误处理 #

若出现下面的错误
img
检查下面三项

  1. 网络不通

检查ip,端口

  1. 密码不对

检查是否创建用于同步的用户和用户密码是否正确

  1. pos不对

检查Master的 Position

查看容器ip等 #

docker inspect NAMES 
# 查看容器所有状态信息;

docker inspect --format='{{.NetworkSettings.IPAddress}}' ID/NAMES
# 查看 容器ip 地址

docker inspect --format '{{.Name}} {{.State.Running}}' NAMES
# 容器运行状态

mysql相关

show slave status\G;
stop slave;
start slave;

img

阅读:38 . 字数:417 发布于 3 个月前
Copyright 2018-2020 Siques