mysql主从复制实践

60 阅读4分钟

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日志

image.png 配置主从同步账号

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;

简单测试下,主从配置就搭建好了

image.png

总结

主从配置的搭建过程并不复杂,尤其是在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;