1. 主从架构原理:
2. 准备工作
-
拉取镜像
docker pull mysql:5.7
如果不想指定版本,就去除’:5.7’,默认获取latest版本
-
启动两个容器,分别作为主(test_db)-从(test_slave)
docker run --name localMysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7 docker run --name mysql_slave0 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
-
获取容器映射ip1
docker inspect --format='{{.NetworkSettings.IPAddress}}' localMysql docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql_slave0
3. 主库:
-
进入容器,76c5e394bc5c是containerId,docker ps即可获取到
docker exec -it 76c5e394bc5c /bin/sh
-
修改主表test_db配置my.cnf
默认目录:
vim etc/mysql/my.cnf
你有可能打开会看到如下配置:
... !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/
那就换到这个目录:
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]下追加:
# 服务id,唯一 server-id = 10000 # 指定binlog文件名 log-bin = mysql-bin
保存退出
-
进入容器中mysql,-p后边是密码
mysql -uroot -p123456
-
授权,建议使用客户端连接后执行,比如Navicat
-- 创建用户,其中repl表示用户名, 172.17.0.3表示slave库的ip地址,也就是只允许这个ip通过repl用户访问master库 create user 'repl'@'172.17.0.3' identified with mysql_native_password by '123456'; -- replication slave 表示授权复制 -- *.* 表示所有的库和表 grant replication slave on *.* to 'repl'@'172.17.0.3'; -- 刷新权限信息 flush PRIVILEGES; -- 查看binlog打开状态,绝对是ON SHOW VARIABLES LIKE 'log_bin%';
-
查看master状态
SHOW MASTER STATUS;
记住File和Position。
4. 从库
-
修改从库my.cnf
server-id = 10001 log-bin = mysql-slave-bin-log # 指定中继日志,从库依赖此文件同步 relay_log = mysql-relay-bin
-
从库绑定主库(重要)
change master to master_host='172.17.0.2', master_user='repl', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 5060, master_connect_retry=30;
master_host:主机ip
master_user:主库第4步授权的用户名
master_password:授权的密码
master_log_file:主库第5步的File
master_log_pos:主库第5步的Position
master_connect_retry:连接失败重试间隔s
-
启动
start slave; -- 使用show slave status;亦可,高版本建议使用replica show replica status;
关注Replica_IO_Running和Replica_SQL_running,全为Yes,即大功告成
如果一切顺利,那么后边执行建库、建表、CRUD操作都会同步到从库。
Good luck!!!
5. FAQ
-
Last_Error:Error executing row event:'Table '***' doesn't exist'
因为这张表在搭建主从前已经存在了,binlog并未记录建表语句,尝试同步数据到slave找不到表了。 -
Last_Error: Could not execute Update_rows event on table test_db.t_user; Can't find record in 't_user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 1888
看来这玩意是增量同步,得了,先删表,然后stop slave后重新走change maste to…
-
Last_Errno: 1008 Last_Error: Error 'Can't drop database 'test1'; database doesn't exist' on query. Default database: 'test1'. Query: 'drop database test1'
-- 先停止 STOP REPLICA; -- 跳过last指令 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; -- 启动 START REPLICA;
-
error connecting to master 'repl@172.17.0.2:3306' - retry-time: 30 retries: 16 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
啥也不说了,再次确认主从ip,返回主库重新grant吧。1
-
Could not execute Write_rows event on table test_db.t_user; Duplicate entry '1' for key 't_user.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 2967
为啥,因为重新授权导致需要重新执行binlog中的命令,而从库此时已经存在数据了。
-- 主库binlog还原 FLUSH LOGS; -- 获取File和Position SHOW MASTER STATUS; -- 从库 STOP REPLICA; CHANGE MASTER TO MASTER_HOST='172.17.0.2', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS= 156, MASTER_CONNECT_RETRY=30; START REPLICA; SHOW REPLICA STATUS;
-
Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'
参考5
-
如何查看binlog?
SHOW BINLOG EVENTS IN'mysql-bin.000002';
-