mysql主从复制实践

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
1
2
3
4
5
6
7
8

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

配置完成过后进入mysql中，执行如下命令，查看binlong

 docker exec -it mysql /bin/bash
 mysql -uroot -hip -P3306 -pxx
  SHOW BINARY LOGS; #binlog日志
1
2
3

配置主从同步账号

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
select * from mysql.user where user = 'slave'; #查看配置的账号
1
2
3

锁住主数据库，不让更新数据

flush tables with read lock; #所有的表只读 unlock tables解锁
1

导出指定数据库数据，业务数据库，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
1

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

拷贝复制数据到从数据库中，复制数据，可以看到

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 检查
1
2
3
4
5

开启主从复制

#配置主从
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;
1
2
3
4
5
6
7
8
9
10
11
12

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

总结

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

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;
1
2
3