mysql主从复制
参考: 一主多从实践: https://www.cnblogs.com/machangwei-8/p/15949357.html
先试下一主一从
docker中建两个mysql实例
在docker中建立两个mysql实例:
注意:windows环境docker下mysql配置文件映射路径需要放到C:\users\下(否则777权限的mysql配置文件,mysql出于安全考虑会忽略), https://blog.csdn.net/kinginblue/article/details/76863962
注意: 如果映射mysql配置文件夹下没有my.cnf,默认docker实例里mysql数据存到了/usr/data下, 需要在windows映射目录下放配置文件
第一个mysql5.6实例(master)
先建立映射目录 C:\users\data\mysql\conf, 并放入自定义的my.cnf. C:\users\data\mysql\data.
[mysql]
#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock
[mysqld]
lower_case_table_names=1
#Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
#user= root
#basedir = /cjdata/vivachekcloud/mysql
#datadir = /cjdata/vivachekcloud/mysql/data
#log-error=/cjdata/vivachekcloud/mysql/data/mysql.err
#pid-file=/cjdata/vivachekcloud/mysql/data/mysql.pid
#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock
#port = 3306
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip-name-resolve
default-time-zone = '+08:00'
datadir = /var/lib/mysql
slow_query_log=1
long_query_time=3
slow_query_log_file = /var/lib/mysql/slow.log
log_bin=ON
log-bin=/var/lib/mysql/mysql-bin
server-id=1开启binlog, server-id置为1.
第一个是mysql5.6的实例,名字叫some-mysql, 映射宿主端口3306
docker run --name some-mysql -v /C/users/data/mysql/conf:/etc/mysql:rw -v /C/users/data/mysql/data:/var/lib/mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=Nov2014 -e TZ=Asia/Shanghai -d mysql:5.6docker 看下日志, 可能还需要添加C:\users\data\mysql\data 的users组的修改权限(文件夹右键->属性->安全->编辑->勾选修改)
一个mysql8.0实例(废弃)
第二个是mysql8.0的实例,名字叫做mysql8 (莫名问题会有一些,先不做实验)
docker run --name mysql8 -v /C/users/data/mysql8/conf:/etc/mysql:rw -v /C/users/data/mysql8/data/mysql:/var/lib/mysql -v /C/users/data/mysql8/data/mysql-files:/var/lib/mysql-files -p 3307:3306 -e MYSQL_ROOT_PASSWORD=Nov2014 -e TZ=Asia/Shanghai -d mysql:8.0第二个mysql5.6实例(slave)
建立第二个mysql5.6实例的映射目录 : C:\users\data\mysql2\conf, 并放入自定义的my.cnf. C:\users\data\mysql2\data.
注意,此mysql5.6实例作为slave, 不需要开binlog
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysql]
#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock
[mysqld]
lower_case_table_names=1
#Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
#user= root
#basedir = /cjdata/vivachekcloud/mysql
#datadir = /cjdata/vivachekcloud/mysql/data
#log-error=/cjdata/vivachekcloud/mysql/data/mysql.err
#pid-file=/cjdata/vivachekcloud/mysql/data/mysql.pid
#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock
#port = 3306
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip-name-resolve
default-time-zone = '+08:00'
datadir = /var/lib/mysql
slow_query_log=1
long_query_time=3
slow_query_log_file = /var/lib/mysql/slow.log
#log_bin=ON
#log-bin=/var/lib/mysql/mysql-bin
server-id=2不开启binlog, server-id置为2.
第二个mysql5.6实例, 名字叫做some-mysql2, 映射属组端口3308
docker run --name some-mysql2 -v /C/users/data/mysql2/conf:/etc/mysql:rw -v /C/users/data/mysql2/data:/var/lib/mysql -p 3308:3306 -e MYSQL_ROOT_PASSWORD=Nov2014 -e TZ=Asia/Shanghai -d mysql:5.6docker 看下日志, 可能还需要添加C:\users\data\mysql\data 的users组的修改权限(文件夹右键->属性->安全->编辑->勾选修改)
确认实例互通
docker network ls
docker network inspect bridge看到some-mysql, mysql8两个实例都在bridge网络下. ip分别为172.17.0.2, 172.17.0.3
#命令行窗口1
docker exec -it some-mysql /bin/bash
apt install iputils-ping
ping 172.17.0.3
#ping的通
exit主库开启binlog(前面已修改)
C/users/data/mysql/conf/my.cnf 开启binlog
#C/users/data/mysql/conf/my.cnf 开启binlog
log_bin=ON
log-bin=/var/lib/mysql/mysql-bin
server-id=1重启
docker restart some-mysql命令行查看配置
docker exec -it some-mysql /bin/bash
grep -v "^#" /etc/my.cnf|egrep "server-id|log_bin"
mysql -uroot -p123456 -e "show variables like 'log_bin'"
mysql -uroot -p123456 -e "show variables like 'server_id'"或者在navicat等gui工具中确认.
主库建立给从库用的账号
#主库登录
mysql -uroot -p123456
#创建用户,授权
grant replication slave on *.* to rep@'172.17.0.%' identified by '123456';
#刷新权限
flush privileges;
#查看用户
select user,host from mysql.user;
#查看权限
show grants for rep@'172.17.0.%';主库锁表备份
继续在上一节打开的主库some-mysql 连接中操作
#对主库锁表只读，当前窗口不能关闭
flush table with read lock;
#查看主库当前binlog位置.
show master status;
mysqldump
–all-databases, -A： 备份所有数据库
–databases, -B： 用于备份多个数据库，如果没有该选项，mysqldump把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldump把每个名字都当作为数据库名。
windows下注意这个问题:https://blog.csdn.net/OnedayIlove/article/details/104021527, https://blog.csdn.net/qianxiao_1/article/details/79463409
新开命令行窗口(使用cmd , powershell 的重定向会导致乱码,继而导入时失败)
#mysqldump -A -B |gzip>/opt/bak.sql.gz
#mysqldump -A -B > C:\users\wangjm\desktop\master_bak.sql
#mysqldump -h 127.0.0.1 -uroot -p123456 -A -B >C:\users\wangjm\desktop\master_bak.sql
#因为我们主从mysql实例版本不一致,这里指定要复制的各个库, 不复制系统库
mysqldump -h 127.0.0.1 -uroot -pNov2014 -B vivachekcloud_hnz_cloud vivachekcloud_lpzxyjhyy vivachekcloud_qxrmyy vivachekcloud_qxrmyy_v2.1.14 >C:\users\wangjm\desktop\master_bak.sql备份好,看好binlog当前位置POS之后, 就可以解除master的读锁了
unlock table;在从库恢复主库备份
在some-mysql2实例(127.0.0.1:3308)上写入备份内容:
C:\Users\wangjm>mysql -h 127.0.0.1 -P 3308 -u root -pNov2014 < C:\users\wangjm\desktop\master_bak.sql从库修改连接的master
主库文件和日志位置，是前面在主库执行show master status 查看的.
新打开一个到第二个mysql5.6实例(slave)的连接:
CHANGE MASTER TO
MASTER_HOST='172.17.0.2',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=440;等同于直接在命令行如下操作(未测试):
mysql -uroot -p123456 <<EOF
> CHANGE MASTER TO
> MASTER_HOST='172.16.0.11',
> MASTER_PORT=3306,
> MASTER_USER='rep',
> MASTER_PASSWORD='123456',
> MASTER_LOG_FILE='mcw01-bin.000010',
> MASTER_LOG_POS=611;
> EOF启动从库同步开关并测试主从复制
从库执行:
start slave;
show slave status在主库修改了一条血糖记录, 发现从库也发生了修改(OK)
在主库某表添加/删除了一个字段, 发现从库也自动添加/删除了字段(OK)
在主库创建了一个数据库, 发现从库也自动新建了数据库(OK)
mysql一主多从(成功)
再建一个mysql5.6实例, 名字叫做some-mysql3, 作为另一个slave
第三个mysql5.6实例(slave)
建立第三个个mysql5.6实例的映射目录 : C:\users\data\mysql3\conf, 并放入自定义的my.cnf. C:\users\data\mysql3\data.
注意,此mysql5.6实例作为slave, 不需要开binlog
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysql]
#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock
[mysqld]
lower_case_table_names=1
#Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
#user= root
#basedir = /cjdata/vivachekcloud/mysql
#datadir = /cjdata/vivachekcloud/mysql/data
#log-error=/cjdata/vivachekcloud/mysql/data/mysql.err
#pid-file=/cjdata/vivachekcloud/mysql/data/mysql.pid
#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock
#port = 3306
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip-name-resolve
default-time-zone = '+08:00'
datadir = /var/lib/mysql
slow_query_log=1
long_query_time=3
slow_query_log_file = /var/lib/mysql/slow.log
#log_bin=ON
#log-bin=/var/lib/mysql/mysql-bin
server-id=3不开启binlog, server-id置为3.
第3个mysql5.6实例, 名字叫做some-mysql2, 映射属组端口3309
docker run --name some-mysql3 -v /C/users/data/mysql3/conf:/etc/mysql:rw -v /C/users/data/mysql3/data:/var/lib/mysql -p 3309:3306 -e MYSQL_ROOT_PASSWORD=Nov2014 -e TZ=Asia/Shanghai -d mysql:5.6docker 看下日志, 可能还需要添加C:\users\data\mysql\data 的users组的修改权限(文件夹右键->属性->安全->编辑->勾选修改)
仍然使用上次主库给从库建的账号, 使用上次的主库备份文件, 仍然从主库binlog相同位置开始同步主库.
在从库some-mysql3恢复主库备份
在some-mysql2实例(127.0.0.1:3309)上写入备份内容:
C:\Users\wangjm>mysql -h 127.0.0.1 -P 3309 -u root -pNov2014 < C:\users\wangjm\desktop\master_bak.sql从库some-mysql3修改连接的master
主库文件和日志位置，是前面在主库执行show master status 查看的.
新打开一个到第3个mysql5.6实例(some-mysql3/slave)的连接:
CHANGE MASTER TO
MASTER_HOST='172.17.0.2',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=440;启动从库同步开关并测试主从复制
从库执行:
start slave;
show slave status在主库修改了一条血糖记录, 发现从库some-mysql2 / some-mysql3 都发生了修改(OK)
在主库某表添加/删除了一个字段, 发现从库some-mysql2 / some-mysql3 都自动添加/删除了字段(OK)
在主库创建了一个数据库, 发现从库some-mysql2 / some-mysql3 都自动新建了数据库(OK)
mysql5.6多主一从(失败)
再建一个mysql5.6实例, 名字叫做some-mysql4, 作为另一个master
第四个mysql5.6实例some-mysql4(master)
创建实例some-mysql4并开启binlog
先建立映射目录 C:\users\data\mysql4\conf, 并放入自定义的my.cnf. C:\users\data\mysql4\data.
[mysql]
#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock
[mysqld]
lower_case_table_names=1
#Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
#user= root
#basedir = /cjdata/vivachekcloud/mysql
#datadir = /cjdata/vivachekcloud/mysql/data
#log-error=/cjdata/vivachekcloud/mysql/data/mysql.err
#pid-file=/cjdata/vivachekcloud/mysql/data/mysql.pid
#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock
#port = 3306
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip-name-resolve
default-time-zone = '+08:00'
datadir = /var/lib/mysql
slow_query_log=1
long_query_time=3
slow_query_log_file = /var/lib/mysql/slow.log
log_bin=ON
log-bin=/var/lib/mysql/mysql-bin
server-id=4开启binlog, server-id置为4.
第一个是mysql5.6的实例,名字叫some-mysql4, 映射宿主端口3310
docker run --name some-mysql4 -v /C/users/data/mysql4/conf:/etc/mysql:rw -v /C/users/data/mysql4/data:/var/lib/mysql -p 3310:3306 -e MYSQL_ROOT_PASSWORD=Nov2014 -e TZ=Asia/Shanghai -d mysql:5.6docker 看下日志, 可能还需要添加C:\users\data\mysql\data 的users组的修改权限(文件夹右键->属性->安全->编辑->勾选修改)
确认ip并与其他实例互通
docker network ls
docker network inspect bridge看到some-mysql4与其他实例都在bridge网络下. some-mysql4的ip为172.17.0.5
#命令行窗口1
docker exec -it some-mysql /bin/bash
apt install iputils-ping
ping 172.17.0.3
#ping的通
exit建立给从库的账号
#主库登录
mysql -uroot -p123456
#创建用户,授权
grant replication slave on *.* to rep@'172.17.0.%' identified by '123456';
#刷新权限
flush privileges;
#查看用户
select user,host from mysql.user;
#查看权限
show grants for rep@'172.17.0.%';主库锁表备份
因为这是一个新数据库, 除了系统库没有其他库了. 略. 若有其他库, 参见实例1 (some-mysql相关章节)
#查看主库当前binlog位置.
show master status;从库some-mysql2修改连接的master
新打开一个到第二个mysql5.6实例some-mysql2(slave)的连接:
mysql -h 127.0.0.1 -P 3308 -uroot -pNov2014之前some-mysql2作为some-mysql实例的从库, 看下当前状态:
show slave status状态如下:
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Waiting for master to send event | 172.17.0.2 | rep | 3306 | 60 | mysql-bin.000008 | 120 | 96ac88f7c7db-relay-bin.000007 | 283 | mysql-bin.000008 | Yes | Yes | 0 | 0 | 120 | 45891 | None | 0 | No | 0 | No | 0 | 0 | 1 | 86c607fc-bbc3-11ec-bb3b-0242ac110002 | /var/lib/mysql/master.info | 0 | NULL | Slave has read all relay log; waiting for the slave I/O thread to update it | 86400 | 0 |
在从库配置增加新的主库地址:
CHANGE MASTER TO
MASTER_HOST='172.17.0.5',
MASTER_PORT=3310,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=397;some-mysql2( mysql5.6 / slave) 结果:
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first参考: https://cloud.tencent.com/developer/article/1382116, 发现mysql5.7才开始支持多主一从.
超哥的建议: 直接升mysql8, 但是mysql8sql会更严格一点, 项目里的sql可能需要调整.
发表回复