docker-compose + mysql8.x 主从数据库配置
0.准备 （略过docker的安装与镜像拉取）
docker / docker-compose 安装
拉取 mysql 8.x
1. master和slave的mysql配置
master:
[mysqld] server-id=11118 log-bin=mysql-bin
slave:
[mysqld] log-bin=mysql-bin server-id=11105
注： 两个 server-id 一定要 不一样
2. docker-compose 文件 设置
version: "3" services: db: image: mysql ports: - "3307:3306" volumes: - ./dockerMysql/master/db:/var/lib/mysql - ./dockerMysql/master/cnf:/etc/mysql/conf.d environment: - MYSQL_DATABASE=siemens - MYSQL_ROOT_PASSWORD=123456 networks: mynet-tier:: ipv4_address: 172.16.238.2 slave: image: mysql ports: - "3308:3306" volumes: - ./dockerMysql/slave/db1:/var/lib/mysql - ./dockerMysql/slave/cnf:/etc/mysql/conf.d environment: - MYSQL_DATABASE=siemens - MYSQL_ROOT_PASSWORD=123456 links: - db networks: - mynet-tier: networks: mynet-tier: ipam: # driver: overlay config: - subnet: "172.16.238.0/24"
注： 这里专门配置了子网和固定IP（static IP），方便后面重启数据库后，以脚本的形式自动重新设置slave的参数，没有此需求也可不必。
3. mysql 主从配置
3.1 设置 master
进入 docker docker exec -it bin_db_1 bash 进入mysql mysql -u root -pxxx 创建用户并授权（mysql 8.x 与 5.x 有区别，这里是8.x方式），这里使用root用户来做著丛，推荐创建专门用户来做 mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'xxx'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; mysql> show master status\G; *************************** 1. row *************************** File: mysql-bin.000001 Position: 236480 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)
注： 这里的 File 和 Position 信息很重要， 后面slave设置时会用到。
3.2 设置 slave
进入docker docker exec -it bin_slave_1 bash 进入mysql mysql -u root -pxxx 停止、重置 slave mysql> stop slave; mysql> reset slave; 设置主库，启用 slave mysql> change master to master_host='172.18.0.3',master_user='root',master_password='xxx',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=236480; mysql> start slave; 查看slave，确保 Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.18.0.3 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 236480 Relay_Log_File: cc9a6ddfeacd-relay-bin.000002 Relay_Log_Pos: 322 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 236480 Relay_Log_Space: 537 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11108 Master_UUID: f949b20a-7a18-11ed-80df-0242ac120003 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 1 row in set (0.00 sec)
注： Slave_IO_Running 和 Slave_SQL_Running 的值都为Yes 表示slave启动成功。
补充1. 数据库重启后需更新slave的配置
由于一些原因导致docker容器停止，重新启动docker后，slave 会无法同步主库。需要重新设置
进入master docker
docker exec -it bin_db_1 bash
进入mysql
mysql -u root -pxxx
停止、重置 slave
mysql> stop slave;
mysql> reset slave;
mysql> reset master;
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 155
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
进入slave docker docker exec -it bin_slave_1 bash 进入mysql mysql -u root -pxxx 停止、重置 slave mysql> stop slave; mysql> reset slave; 设置主库，启用 slave mysql> change master to master_host='172.18.0.x',master_user='root',master_password='xxx',master_port=3306,master_log_file='mysql-bin.00000x',master_log_pos=xxx; mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.18.0.2
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 155
Relay_Log_File: 5d566ef9fd9f-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
...
注：先进入master的mysql，停止slave，reset master 和 slave；
再进入slave的mysql，停止、重置、重新设定slave（高亮红色的部分）、启用 slave
补充2. 常见问题
1）主从库uuid和server_id重复导致错误;
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
解决方案：
在mysql中输入以下命令，查看主从库是否重复，确保两者不同；
show variables like '%server_uuid%';
show variables like '%server_id%';
server_id：
1.可以在my.cnf中修改，但重启后可能依然不生效；
2.用“SET GLOBAL server_id=;”解决，但此命令会在mysql服务重启后丢失。
在mysql中的auto.cnf中修改；
待uu_id和server_id修改完后重启容器。
2）docker-compose down / up MySQL容器IP变化
重启 docker-compose 后，需要根据MySQL master 的 IP 来设置
mysql> change master to master_host='newIP',master_user='root',master_password='xxx',master_port=3306,master_log_file='mysql-bin.00000x',master_log_pos=xxx;
