通过docker快速搭建mysql主从集群(一主两从):
首先请确保你的服务器已安装了docker⁄(⁄ ⁄•⁄ω⁄•⁄ ⁄)⁄,那么接下来,我们进入正题~
1. 镜像拉取
# latest目前版本为8.0.13;
docker pull mysql:latest
2.启动主库镜像
docker run \
--restart=always \
-p 3306:3306 --name mysql \
-v /opt/mysql/log:/var/log/mysql \
-v /opt/mysql/data:/var/lib/mysql \
-v /opt/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /opt/mysql/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:latest --server-id=100 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --log-bin=mysql-bin --sync_binlog=1
3.主库操作
# docker 连接容器
docker exec -it mysql /bin/bash -c 'mysql -uroot -p123456'
# 修改mysql身份验证插件为mysql_native_password;
# 因为mysql 8.0+起;其默认的身份验证插件为caching_sha2_password;
# 如果不改会导致登录验证失败;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
# 创建从库数据同步用户slave;
CREATE USER slave IDENTIFIED WITH mysql_native_password BY '123456';
# 授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%' ;
# 生效
FLUSH PRIVILEGES;
# 查看主库状态
show master status;
3.记录bin-log信息
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 1357 | | | |
+---------------+----------+--------------+------------------+-------------------+
一定要记住show master status的执行结果;这两个值都很关键!
4.启动从库镜像
# 若主从在一台服务器;则需要修改镜像名称,mysql变更为mysql-slave1,mysql-slave2等
docker run \
--restart=always \
-p 3306:3306 --name mysql \
-v /opt/mysql/log:/var/log/mysql \
-v /opt/mysql/data:/var/lib/mysql \
-v /opt/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /opt/mysql/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:latest --server-id=101 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
乍一看似乎与主库的镜像启动命令相差无几;但关键点在于——server-id!
没错,这个server-id就相当于每个库的身份证,有,且唯一。
如果重复,则会出现某些意想不到的错误。
5.启动从库1镜像,
# docker 连接容器
docker exec -it mysql /bin/bash -c 'mysql -uroot -p123456'
# 如果主从在一台服务器,则需要通过[ docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql] 查询主库容器IP
# 如果不在一台服务器,则直接使用外网ip即可
CHANGE MASTER TO MASTER_HOST='your master ip ',MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='binlog.000002',MASTER_LOG_POS=1357;
# 开启主从复制
start slave;
# 查看从库状态
show slave status\G
# 当看到如下输出;Slave_IO_Running及Slave_SQL_Running为YES时;从库启动+主从复制成功;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.0.0.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 1357
Relay_Log_File: 6a5b3318f91e-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6.重复步骤5,启动从库2镜像
至此;恭喜你,你的mysql主从集群已经搭建完成了(▽)~
7.常见问题汇总
- 通过工具连接mysql提示密码验证失败;
# 是哪个库就连接哪个容器更新身份验证插件为mysql_native_password
docker exec -it mysql /bin/bash -c 'mysql -uroot -p123456'
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
- 主从复制报错;
# 常见于从库;问题原因可进入容器通过show slave status\G命令查询
-----
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updat
-----
一般常见于:
主从库server-id一致(启动镜像时未修改);
主从复制SQL命令主库IP、端口、log-file名称与主库不一致;
主从复制SQL命令访问主库的账户名或密码有误;(要使用slave账户)
主库身份验证插件未更新至mysql_native_password;导致连接失败;
后续问题完善中……