基于Docker的Mysql8.0主从搭建

1、下载及查看镜像

下载最新的mysql镜像，当前版本8.0.27

[root@localhost ~]# docker pull mysql
Using default tag: latest
latest: Pulling from library/mysql
72a69066d2fe: Pull complete
93619dbc5b36: Pull complete
99da31dd6142: Pull complete
626033c43d70: Pull complete
37d5d7efb64e: Pull complete
ac563158d721: Pull complete
d2ba16033dad: Pull complete
688ba7d5c01a: Pull complete
00e060b6d11d: Pull complete
1c04857f594f: Pull complete
4d7cfa90e6ea: Pull complete
e0431212d27d: Pull complete
Digest: sha256:e9027fe4d91c0153429607251656806cc784e914937271037f7738bd5b8e7709
Status: Downloaded newer image for mysql:latest
docker.io/library/mysql:latest

2、容器部署

2.1 创建目录

[root@localhost ~]# cd /usr/local/
[root@localhost local]# mkdir -p ./mysql/master/conf
[root@localhost local]# mkdir -p ./mysql/slave/conf
[root@localhost local]# mkdir -p ./mysql/master/data
[root@localhost local]# mkdir -p ./mysql/slave/data

2.2 启动测试容器及拷贝配置文件

将测试容器里 Msql 的配置文件复制到master，slave下的conf目录

[root@localhost local]# docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql
269d66ed53e5cf6653ad489e6b2a83588982535f9c4b70a71b41aa7a5651f198
[root@localhost local]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
269d66ed53e5 mysql “docker-entrypoint.s…” 5 seconds ago Up 3 seconds 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp mysql
[root@localhost local]# docker cp 269d66ed53e5:/etc/mysql/my.cnf /usr/local/mysql/master/conf/
[root@localhost local]# docker cp 269d66ed53e5:/etc/mysql/my.cnf /usr/local/mysql/slave/conf/

2.3 mysql主从配置修改

2.3.1 mysql-master配置文件修改

[root@localhost local]# cd /usr/local/mysql/master/conf/
[root@localhost conf]# vi my.cnf
#主数据库端ID号
server_id = 1
#开启二进制日志
log-bin = mysql-bin
#需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
#binlog-do-db = test
#将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中
log-slave-updates
#控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失)
sync_binlog = 1
#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_offset = 1
#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_increment = 1
#二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除
expire_logs_days = 7
#将函数复制到slave
log_bin_trust_function_creators = 1

2.3.2 mysql-slave配置文件修改

[root@localhost conf]# cd /usr/local/mysql/slave/conf/
[root@localhost conf]# vi my.cnf
#从数据库端ID号
server_id = 2
#开启二进制日志
log-bin = mysql-bin
#将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中
log-slave-updates
#控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失)
sync_binlog = 0
#log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作
innodb_flush_log_at_trx_commit = 0
#指定slave要复制哪个库
#replicate-do-db = test
#MySQL主从复制的时候，当Master和Slave之间的网络中断，但是Master和Slave无法察觉的情况下（比如防火墙或者路由问题）。Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据
slave-net-timeout = 60
log_bin_trust_function_creators = 1

2.4 停止及删除测试容器

[root@localhost local]# docker stop 269d66ed53e5
269d66ed53e5
[root@localhost local]# docker rm 269d66ed53e5
269d66ed53e5

2.5 启动主从容器

[root@localhost ~]# docker run --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -v /usr/local/mysql/master/data:/var/lib/mysql -v /usr/local/mysql/master/conf/my.cnf:/etc/mysql/my.cnf -d mysql
cd77be285145d4f2aa9957dd127053e81bc7ab6602f995ee1cf98e4fa30bfee6
[root@localhost ~]# docker run --name mysql-slave -e MYSQL_ROOT_PASSWORD=123456 -p 3307:3306 -v /usr/local/mysql/slave/data:/var/lib/mysql -v /usr/local/mysql/slave/conf/my.cnf:/etc/mysql/my.cnf -d mysql
1f19db1e86bfe5c4921116bdd16fdd72855dff36aa160dcc29e429b3f383ec57
[root@localhost ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
1f19db1e86bf mysql “docker-entrypoint.s…” 9 seconds ago Up 8 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-slave
cd77be285145 mysql “docker-entrypoint.s…” 17 seconds ago Up 16 seconds 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp mysql-master
757cb3a34ae3 nginx “/docker-entrypoint.…” 7 days ago Up 5 hours 0.0.0.0:80->80/tcp, :::80->80/tcp nginx

2.6 远程访问配置

2.6.1 进入主从容器中

[root@localhost ~]# docker exec -it mysql-master bash
root@cd77be285145:/# mysql -uroot -p123456

[root@localhost ~]# docker exec -it mysql-slave bash
root@cd77be285145:/# mysql -uroot -p123456

2.6.2 权限设置

#为root分配权限，以便可以远程连接
mysql> grant all PRIVILEGES on . to root@’%’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

2.6.3 修改Password算法

#由于Mysql5.6以上的版本修改了Password算法，所以需要更新密码算法，以便于使用Navicat连接
mysql> ALTER user ‘root’@’%’ IDENTIFIED WITH mysql_native_password BY’123456’;
Query OK, 0 rows affected (0.00 sec)

2.6.4 刷新mysql权限

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

2.6.5 创建从库连接账号

mysql> create user ‘slave’@’%’ identified with mysql_native_password by ‘123456’;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave, replication client on . to ‘slave’@’%’;
Query OK, 0 rows affected (0.01 sec)

2.6.6 查看 log-bin 是否开启

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

2.6.7 查看master容器IP

[root@localhost ~]# docker inspect mysql-master

2.6.8 从库配置，与主库建立关联

mysql> stop slave;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> change master to master_host=‘172.17.0.3’,master_user=‘slave’,master_password=‘123456’,master_log_file=‘mysql-bin.000003’,master_log_pos=1365,master_port=3306;
Query OK, 0 rows affected, 9 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

看到Slave_IO_Running,Slave-SQL-Running两个线程都成功了，那就说明整个MYSQL主从搭建成功了