mysql主从复制实践
实际生产中都是配置了mysql的主从,用于容灾和恢复,缓解读写读的压力。配置主从一般都是复制主库数据到从库,然后配置主从复制,这里主要描述docker 如何配置mysql主从。
Mysql主配置
一般mysql的主都是有生产数据已经在跑了,配置主从关键是binlog的配置。主数据库要开启binlog,配置同步的到binlog的库。主数据库的msql的docker启动参数是
docker pull mysql #拉mysql 镜像
docker run -p 3306:3306 --name mysql2 \
-v /mydata/mysql/log:/var/log/mysql \
-v /mydata/mysql/data:/var/lib/mysql \
-v /mydata/mysql/conf:/etc/mysql \
-v /mydata/mysql/mysql-files:/var/lib/mysql-files \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql
mysql配置文件,配置之后需要重新启动mysql才能生效,log-bin的作用是命名生成bin日志的前缀。如果是配置到实际物理位置映射到容器位置( /mydata/mysql/conf:/etc/mysql),需要重新生成container,把my.cnf放置到/mydata/mysql/conf,重新生成mysql容器,重新生成容器后,mysql数据还在,只不过binlog会重新生成。
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
## 设置server_id,在同一局域网中需要唯一
server_id=1
## 数据库时区
default-time_zone='+8:00'
## 设置使用的二进制日志格式(row-行级别;statement=语句级别;mixed-混合级别)
binlog_format=mixed
## 忽略主从复制中遇到的所有错误活指定的错误类型,避免slave端复制中断
## 如:1062错误码代表主键重复;1032错误码代表主从数据库数据不一致
slave_skip_errors=1062
###############################可选配置
## 开启二进制日志功能,以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-slave1-bin
## 设置二进制日志使用内存大小(事务)
binlog_cache_size=1M
## 设置使用的二进制日志格式(mixed,statement,row)
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0,表示不自动清理。
expire_logs_days=7
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
配置完成过后进入mysql中,执行如下命令,查看binlong
docker exec -it mysql /bin/bash
mysql -uroot -hip -P3306 -pxx
SHOW BINARY LOGS; #binlog日志
配置主从同步账号
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
select * from mysql.user where user = 'slave'; #查看配置的账号
锁住主数据库,不让更新数据
flush tables with read lock; #所有的表只读 unlock tables解锁
导出指定数据库数据,业务数据库,Mysql自带数据库不导出,后续导出的数据放入到从库中
mysqldump -h172.17.0.1 -P3306 -uroot -plzj2515628 --databases demo0 demo1 ilearning joolun_ry miaosha1 miaosha1Order miaosha1User ruoyi-vue-pro seata > dump-$(date +"%Y%m%d").sql
Mysql从配置
从库配置
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
## 设置server_id,同一局域网中需要唯一
server_id=2
## 设置使用的二进制日志格式(mixed,statement,row)
binlog_format=mixed
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## slave设置为只读(具有super权限的用户除外)
read_only=1
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
拷贝复制数据到从数据库中,复制数据,可以看到
docker cp dump-20221112.sql mysql2:/
mysql -uroot -h172.18.0.1 -P3308 -proot
source dump-20221112.sql;
unlock tables; #解锁后可以比对数据是否准确
show variables like 'server_id'; #serverid 检查
开启主从复制
#配置主从
change master to master_host='172.17.0.1',master_user='slave',master_password='123456',master_port=3306,master_log_file=' binlog.000013',master_log_pos=156,master_connect_retry=30;
#解释
master_host:主数据库的IP地址;
master_port:主数据库的运行端口;
master_user:在主数据库创建的用于同步数据的用户账号;
master_password:在主数据库创建的用于同步数据的用户密码;
master_log_file:指定从数据库要复制数据的日志文件,通过查看主数据的状态,获取File参数;
master_log_pos:指定从数据库从哪个位置开始复制数据,通过查看主数据的状态,获取Position参数;
master_connect_retry:连接失败重试的时间间隔,单位为秒。
start slave; #开启主从复制
show slave status\G;
简单测试下,主从配置就搭建好了
总结
主从配置的搭建过程并不复杂,尤其是在docker中搭建,更为简单。主从是读写分离、容灾等有效手段,生产上很多操作都是基于主从基础,学会快速搭建对于后续的学习还是十分有帮助。
遇到的问题
1.Authentication plugin ‘caching_sha2_password‘ reported error
原来是主库repl的plugin是caching_sha2_password 导致连接不上,修改为mysql_native_password即可解决。
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'MyNewPass4!';
2.Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
stop slave;
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000014',MASTER_LOG_POS=156; #根据主库位置
satart slave;