docker实现mysql主从同步（一主两从）

要将 MySQL 创建为 Docker 容器，主机应安装 Docker。docker里面的代码生成的数据需要写入到宿主机的Mysql服务器上，想要用本地的数据库测试自己的代码正确性，项目程序安装在docker容器中，于是必定要在docker容器里访问到本地的数据库。

准备工作 

# 安装docker
yum install -y docker
# 配置docker加速
mkdir -p /etc/docker
# tee /etc/docker/daemon.json <<-'EOF'
{
"registry-mirrors": ["https://rvq9mjyt.mirror.aliyuncs.com"]
}
EOF
{
"registry-mirrors": ["https://rvq9mjyt.mirror.aliyuncs.com"]
}
# systemctl daemon-reload
# systemctl restart docker

用docker跑上三个数据库：

docker run --name mysql1 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --lower_case_table_names=1

docker run --name mysql2 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --lower_case_table_names=1

docker run --name mysql3 -p 3308:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --lower_case_table_names=1

查看三台数据库是否运行起来：

[root@localhost ~]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED              STATUS              PORTS                               NAMES
84eba9c7f5b5        mysql:5.7           "docker-entrypoint..."   57 seconds ago       Up 56 seconds       33060/tcp, 0.0.0.0:3308->3306/tcp   mysql3
fb96f6fd8c07        mysql:5.7           "docker-entrypoint..."   About a minute ago   Up About a minute   33060/tcp, 0.0.0.0:3307->3306/tcp   mysql2
46b218de82fa        mysql:5.7           "docker-entrypoint..."   About a minute ago   Created                                                 mysql1

注意mysql1的状态是created 并没有启动，要启动mysql1

docker start 46b218de82fa 

[root@localhost ~]# docker ps 
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
84eba9c7f5b5        mysql:5.7           "docker-entrypoint..."   12 minutes ago      Up 12 minutes       33060/tcp, 0.0.0.0:3308->3306/tcp   mysql3
fb96f6fd8c07        mysql:5.7           "docker-entrypoint..."   13 minutes ago      Up 13 minutes       33060/tcp, 0.0.0.0:3307->3306/tcp   mysql2
46b218de82fa        mysql:5.7           "docker-entrypoint..."   13 minutes ago      Up About a minute   0.0.0.0:3306->3306/tcp, 33060/tcp   mysql1

连接侧试一下

[root@localhost ~]# docker exec -it mysql1 bash
root@46b218de82fa:/# 

因为docker中没有vi命令，所以将配置文件考出来在主机上修改

mkdir /mysqlconf

docker cp mysql1:/etc/mysql/conf.d/docker.cnf /mysqlconf/mysql1.cnf
docker cp mysql2:etc/mysql/conf.d/docker.cnf /mysqlconf/mysql2.cnf
docker cp mysql3:etc/mysql/conf.d/docker.cnf /mysqlconf/mysql3.cnf

vim /mysqlconf/mysql2.cnf
[mysqld]
skip-host-cache
skip-name-resolve
log_bin=mysql-bin
server_id=1

vim /mysqlconf/mysql1.cnf
[mysqld]
skip-host-cache
skip-name-resolve
server_id=2

vim /mysqlconf/mysql3.cnf
[mysqld]
skip-host-cache
skip-name-resolve
server_id=3

在考回docker

docker cp /mysqlconf/mysql1.cnf mysql1:/etc/mysql/conf.d/docker.cnf
docker cp /mysqlconf/mysql2.cnf mysql2:/etc/mysql/conf.d/docker.cnf
docker cp /mysqlconf/mysql3.cnf mysql3:/etc/mysql/conf.d/docker.cnf

docker中的mysql的配置文件改变了，要重启

docker restart mysql1 mysql2 mysql3
mysql1
mysql2
mysql3

如果重启失败（我就是中途对防火墙进行了操作）

启动Docker后，再对防火墙firewalld进行操作，就会发生上述报错，当 firewalld 启动或者重启时，将会从 iptables 中移除 Docker 的规则，影响 Docker 的正常工作。

[root@localhost ~]# docker start mysql1 mysql2 mysql3
Error response from daemon: driver failed programming external connectivity on endpoint mysql1 (7099a1a16f2c3d995e00458fdbf11e8421db65463221893b108d99d3bea4e779):  (iptables failed: iptables --wait -t nat -A DOCKER -p tcp -d 0/0 --dport 3306 -j DNAT --to-destination 172.17.0.2:3306 ! -i docker0: iptables: No chain/target/match by that name.
 (exit status 1))
Error response from daemon: driver failed programming external connectivity on endpoint mysql2 (cfdd01f556e29eb13d8f858a78d4ab1ff5cbd138e344dd9988fb9304178fabaa):  (iptables failed: iptables --wait -t nat -A DOCKER -p tcp -d 0/0 --dport 3307 -j DNAT --to-destination 172.17.0.2:3306 ! -i docker0: iptables: No chain/target/match by that name.
 (exit status 1))
Error response from daemon: driver failed programming external connectivity on endpoint mysql3 (cf24d5f66bb47f192e38988f9376203dde0e88445e8200190ef31c7279b7c965):  (iptables failed: iptables --wait -t nat -A DOCKER -p tcp -d 0/0 --dport 3308 -j DNAT --to-destination 172.17.0.2:3306 ! -i docker0: iptables: No chain/target/match by that name.
 (exit status 1))
Error: failed to start containers: mysql1, mysql2, mysql3
[root@localhost ~]# systemctl restart docker
[root@localhost ~]# docker start mysql1 mysql2 mysql3
mysql1
mysql2
mysql3

 

开始做主从

主

在docker中登录mysql2（master），也可以用另一台主机直接远程连接mysql

docker exec -it mysql2 bash
root@46b218de82fa:/# mysql -uroot -p123456

在master数据库上创建主从同步的用户

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


mysql> flush privileges;

MySQL Replication是MySQL的一个非常重要的功能，主要用于主服务器和从服务器之间的数据复制操作。

查看bin-log

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从

change master to
master_host='192.168.88.138',
master_port=3307,
master_user='rep',
master_password='123456',
master_log_file='mysql-bin.000005',
master_log_pos=154;

 

 麻了配了5个小时，Slave_IO_Running:一直是连接状态，要细心要细心要细心