同步多个mysql 到一个
了解大概
Ref:
is it possible that canal set with multiple mysql database source 使用 canal
https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source.html, mysql 从5.7原生支持 multiple source replication
https://www.percona.com/blog/2013/10/02/mysql-5-7-multi-source-replication/
https://www.cnblogs.com/CryFace/p/13600816.html, 讲到复制与备份区别,还有 Master - Slave - Slave 复制
https://www.jb51.net/article/161140.htm 讲到级联复制,也就是Master - Slave - Slaves
从1对1主从复制开始
查了些资料,看到从 5.6 版本开始有 GTID 复制方式,比传统的基于binlog 位置的复制方式要好,所以选择 GTID复制.
用的环境:
Docker 20
Mysql 8.0.25
先创建3个文件
#docker-compose.yml version: '3.7' services: mysql-master: image: "mysql" container_name: "mysql3307m" environment: MYSQL_ROOT_PASSWORD: root links: - mysql-slave ports: - "3307:3306" volumes: - ./master.cnf:/etc/mysql/my.cnf - ./mysql_files:/var/lib/mysql-files/ hostname: mysql-master mysql-slave: image: "mysql" container_name: "mysql3308s" environment: MYSQL_ROOT_PASSWORD: root ports: - "3308:3306" volumes: - ./slave.cnf:/etc/mysql/my.cnf - ./mysql_files:/var/lib/mysql-files/ hostname: mysql-slave
#master.cnf [mysqld] ## 设置server_id,一般设置为IP,注意要唯一 server_id=100 gtid_mode=ON enforce-gtid-consistency=true ## 开启二进制日志功能,可以随便取,最好有含义(关键就是这里了) log-bin=replicas-mysql-bin ## 为每个session分配的内存,在事务过程中用来存储二进制日志的缓存 binlog_cache_size=1M ## 主从复制的格式(mixed,statement,row,默认格式是statement)官方推荐在使用GTID情况下,基于行复制 binlog_format=row ## 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。 expire_logs_days=7 ## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。 ## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致 slave_skip_errors=1062 # 没有设置双1 innodb_flush_log_at_trx_commit=1 secure_file_priv=/var/lib/mysql
#slave.cnf [mysqld] ## 设置server_id,一般设置为IP,注意要唯一 server_id=101 #GTID主从复制模式 gtid_mode=ON enforce-gtid-consistency=true ## 开启二进制日志功能,可以随便取,最好有含义(关键就是这里了) log-bin=replicas-mysql-bin ## 为每个session分配的内存,在事务过程中用来存储二进制日志的缓存 binlog_cache_size=1M ## 主从复制的格式(mixed,statement,row,默认格式是statement)官方推荐在使用GTID情况下,基于行复制 binlog_format=row ## 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。 expire_logs_days=7 ## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。 ## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致 slave_skip_errors=1062 relay_log=mysql-relay-bin # crash safe log_slave_updates=OFF relay_log_recovery=ON read_only=1 secure_file_priv=/var/lib/mysql
配置 Master端:
进入container 内部
docker exec -it mysql3307m /bin/bash
登录MySQL
mysql -uroot -proot
创建repliation user
CREATE USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'repl'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
flush privileges;
配置 Slave 端:
进入slave container:
docker exec -it mysql3308s /bin/bash
连上mysql,这里直接用了root的密码root,对应docker-compose.yml
文件中配置的MYSQL_ROOT_PASSWORD
项。
mysql -uroot -proot
执行sql,指定master和链接的用户
CHANGE MASTER TO MASTER_HOST = 'mysql-master', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'repl', MASTER_AUTO_POSITION = 1;
实验是否成功
链接上mysql-master,创建一个数据库,在mysql-slave上看是否存在。如果存在,那么同步成功。如果没有,那么检查一下配置和步骤,使用docker-compose rm
删除容器重新来过,docker很方便。
实验过程中发现:
1. master端创建一个database, slave 成功同步.
2. slave 端创建一个database, master没有,这个正常.
3. master 再次创建一个database, slave 再次成功同步.
4. 如果在slave端改了某个database,这个database就再也不同步了。 下图中,开始的PHP那行是正常同步过去的,然后我先在slave一个表里加了一条记录,又在master同一个表里加了'from master' 那行就同步不过去了.
Ref:
https://blog.csdn.net/s476685226/article/details/106079516, 基于docker环境搭建mysql 主从复制 GTID, 重点参考
Docker mysql [Warning] World-writable config file '.cnf' is is ignored, Windows上跑docker mysql 发现挂载进去的my.cnf没起作用,参考这里发现是文件权限太大mysql 忽略了,win上改成read-only就行了
mysql 8.0.25 遇到 slave 连master 连不上报 002061错误码,查看这里 Quick fix: CREATE USER replicator@'%' IDENTIFIED WITH 'mysql_native_password' BY 'secret';
走向多对1主从(多个master对一个slave)
对1-1 主从的文件升级为 many-1 配置
#docker-compose.yml version: '3.7' services: mysql-master1: image: "mysql" container_name: "mysql3308m1" environment: MYSQL_ROOT_PASSWORD: root links: - mysql-slave ports: - "3308:3306" volumes: - ./master1.cnf:/etc/mysql/my.cnf - ./mysql_files:/var/lib/mysql-files/ hostname: mysql-master1 mysql-master2: image: "mysql" container_name: "mysql3309m2" environment: MYSQL_ROOT_PASSWORD: root links: - mysql-slave ports: - "3309:3306" volumes: - ./master2.cnf:/etc/mysql/my.cnf - ./mysql_files:/var/lib/mysql-files/ hostname: mysql-master2 mysql-slave: image: "mysql" container_name: "mysql3307s" environment: MYSQL_ROOT_PASSWORD: root ports: - "3307:3306" volumes: - ./slave.cnf:/etc/mysql/my.cnf - ./mysql_files:/var/lib/mysql-files/ hostname: mysql-slave
#master1.cnf [mysqld] ## 设置server_id,一般设置为IP,注意要唯一 server_id=100 gtid_mode=ON enforce-gtid-consistency=true ## 开启二进制日志功能,可以随便取,最好有含义(关键就是这里了) log-bin=replicas-mysql-bin ## 为每个session分配的内存,在事务过程中用来存储二进制日志的缓存 binlog_cache_size=1M ## 主从复制的格式(mixed,statement,row,默认格式是statement)官方推荐在使用GTID情况下,基于行复制 binlog_format=row ## 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。 expire_logs_days=7 ## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。 ## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致 slave_skip_errors=1062 # 没有设置双1 innodb_flush_log_at_trx_commit=1 secure_file_priv=/var/lib/mysql
#master2.cnf [mysqld] ## 设置server_id,一般设置为IP,注意要唯一 server_id=102 gtid_mode=ON enforce-gtid-consistency=true ## 开启二进制日志功能,可以随便取,最好有含义(关键就是这里了) log-bin=replicas-mysql-bin ## 为每个session分配的内存,在事务过程中用来存储二进制日志的缓存 binlog_cache_size=1M ## 主从复制的格式(mixed,statement,row,默认格式是statement)官方推荐在使用GTID情况下,基于行复制 binlog_format=row ## 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。 expire_logs_days=7 ## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。 ## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致 slave_skip_errors=1062 # 没有设置双1 innodb_flush_log_at_trx_commit=1 secure_file_priv=/var/lib/mysql
#slave.cnf [mysqld] ## 设置server_id,一般设置为IP,注意要唯一 server_id=101 #GTID主从复制模式 gtid_mode=ON enforce-gtid-consistency=true ## 开启二进制日志功能,可以随便取,最好有含义(关键就是这里了) log-bin=replicas-mysql-bin ## 为每个session分配的内存,在事务过程中用来存储二进制日志的缓存 binlog_cache_size=1M ## 主从复制的格式(mixed,statement,row,默认格式是statement)官方推荐在使用GTID情况下,基于行复制 binlog_format=row ## 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。 expire_logs_days=7 ## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。 ## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致 slave_skip_errors=1062 relay_log=mysql-relay-bin # crash safe log_slave_updates=OFF relay_log_recovery=ON read_only=1 secure_file_priv=/var/lib/mysql
配置 masters
就是在每个master上创建 repliation user
CREATE USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'repl';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
flush privileges;
配置 slave
from 8.0.23, 用下面的语法
CHANGE REPLICATION SOURCE TO SOURCE_HOST="mysql-master1", SOURCE_USER="repl", SOURCE_PASSWORD="repl", SOURCE_AUTO_POSITION=1 FOR CHANNEL "mysql-master1"; CHANGE REPLICATION SOURCE TO SOURCE_HOST="mysql-master2", SOURCE_USER="repl", SOURCE_PASSWORD="repl", SOURCE_AUTO_POSITION=1 FOR CHANNEL "mysql-master2";
启动slave
START REPLICA FOR CHANNEL "mysql-master1"; START REPLICA FOR CHANNEL "mysql-master2";
看slave状态
SHOW REPLICA STATUS FOR CHANNEL "mysql-master1"\G SHOW REPLICA STATUS FOR CHANNEL "mysql-master2"\G
实验结果:
实验过程中遇到一下问题,
[ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'replicas-mysql-bin.000003' position 196
用这个办法就好了,但是不知道原因,有时间再研究了.
Ref:
https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source.html 主要参考这个官方文档
多对1主从以后,再把Slave备份一下