docker下的mysql主从备份笔记
需求：在几台VPS上面部署了mysql 5.6，每台VPS上面都有几个库，需要将这些不同vps上面的数据库同步到一台云服务器（slave）上，slave上面已经部署了mysql，不想影响现有mysql，因此在slave主机上安装基于docker的mysql。
# 创建相关文件夹，用于将mysql容器的数据目录映射出来，持久化存储
mkdir -p /data/docker_mysql/data /data/docker_mysql/mysql-files
# 授予读写权限
chmod +766 /data/docker_mysql/data /data/docker_mysql/mysql-files
提前建立好容器所需要的my.cnf配置文件：
[mysqld] server_id=101 binlog-ignore-db=mysql log-bin=mysql-slave-bin binlog_cache_size=1M binlog_format=mixed expire_logs_days=7 slave_skip_errors=1062 relay_log=mysql-relay-bin log_slave_updates=1 read_only=1 master_info_repository=TABLE relay_log_info_repository=TABLE log-error=/var/lib/mysql/err.log
# 可以在slave端 my.cnf 指定需要同步的数据库
replicate-do-db=test1 replicate-do-db=test2 replicate-do-db=test3 replicate-do-db=test4
# 或者在master端 my.cnf 指定不需要同步的数据库
binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema
创建mysql 8.0容器：
docker run -d --name slave_mysql -p 3307:3306 --privileged=true -v /data/docker_mysql/my.cnf:/etc/mysql/my.cnf -v /data/docker_mysql/data:/var/lib/mysql -v /data/docker_mysql/mysql-files:/var/lib/mysql-files -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai mysql:8.0
在所有master主机上建立slave账号
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%' IDENTIFIED BY '123456'; FLUSH PRIVILEGES;
在master-1主机上面获取MASTER_LOG_FILE和MASTER_LOG_POS数据，后面需要用到
SHOW MASTER STATUS;
在master—1主机上导出数据库test1和test2，并将sql文件传输到slave主机
mysqldump -uroot -p --master-data=2 --single-transaction --databases --add-drop-database test1 >test1.sql mysqldump -uroot -p --master-data=2 --single-transaction --databases --add-drop-database test2 >test2.sql scp test1.sql root@192.168.100.1:/data/test1.sql scp test2.sql root@192.168.100.1:/data/test2.sql
slave主机将接收到的sql文件复制到容器里面
docker cp /data/tset1.sql slave_mysql:/tmp/test1.sql docker cp /data/tset2.sql slave_mysql:/tmp/test1.sql
容器里面登陆mysql控制台，操作导入sql
mysql -uroot -p123456 source /tmp/test1.sql source /tmp/test2.sql
设置Master_1链接参数
CHANGE MASTER TO MASTER_HOST='192.168.101.1', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=11731 FOR CHANNEL 'Master_1';
其他Master服务器也是同样操作，操作完start slave，使用命令show slave status\G来查看同步状态，正常状态示例
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.101.1
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 1106962
Relay_Log_File: mysql-relay-bin-master_1.000010
Relay_Log_Pos: 2427
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1,test2,test3,test4
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: 1106962
Relay_Log_Space: 250090
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: 1
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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_1
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
*************************** 2. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.102.1
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000034
Read_Master_Log_Pos: 223323210
Relay_Log_File: mysql-relay-bin-master_2.000004
Relay_Log_Pos: 28916
Relay_Master_Log_File: mysql-bin.000034
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1,test2,test3,test4
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: 223323210
Relay_Log_Space: 518884
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: 1
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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_2
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
注：不同master的server_id不知道可不可以相同，我这边没去刻意改，都是默认1，似乎没有问题
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 地球OL攻略 —— 某应届生求职总结
· 提示词工程——AI应用必不可少的技术
· Open-Sora 2.0 重磅开源！
· 周边上新：园子的第一款马克杯温暖上架
2020-03-17 windows10多出几个显示器解决方法