- 环境介绍:
原主库硬件故障,无法使用,先将从库改为主库,再增加从库
当前主库:Linux el7 ,mysql 5.7.35,192.100.0.2
新增从库:Linux el8 ,mysql 5.7.35,192.100.0.3 - 原从库转为主库:
修改my.cnf中的配置如下:(后续重启直接生效)
gtid_mode = on
enforce_gtid_consistency = on
进入数据库执行如下:(直接生效)
mysql> SET @@GLOBAL.gtid_mode = ON;
mysql> SET @@GLOBAL.enforce_gtid_consistency = ON; - 新增从库:
- 采用rpm包方式安装,安装过程中可能存在报错找不到libncurses.so.5及libtinfo.so.5,需要做软连接:
# rpm -ivh mysql-community-common-5.7.24-1.el7.x86_64.rpm
# rpm -ivh mysql-community-libs-5.7.24-1.el7.x86_64.rpm
# rpm -ivh mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm
# rpm -ivh mysql-community-devel-5.7.24-1.el7.x86_64.rpm
# ln -s /usr/lib64/libncurses.so.6 /usr/lib64/libncurses.so.5
# ln -s /usr/lib64/libtinfo.so.6 /usr/lib64/libtinfo.so.5
# rpm -ivh mysql-community-client-5.7.24-1.el7.x86_64.rpm --nodeps
# rpm -ivh mysql-community-server-5.7.24-1.el7.x86_64.rpm
- 确认my.cnf中的配置:(确认开启binlog且server-id比当前主库数字大)
log-bin = /data/db/mysql-files/log/binlog/mysql-bin
log-bin-index = /data/db/mysql-files/log/binlog/mysql-bin.index
binlog_format = mixed
expire_logs_days = 60
sync_binlog = 1
binlog_cache_size = 16M
max_binlog_cache_size = 2048M
max_binlog_size = 1024M
binlog_rows_query_log_events = 1
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db=sys
# ---------------- Replication ----------------
server-id = 70
gtid_mode = on
enforce_gtid_consistency = 1
- 创建my.cnf中涉及的所有目录,并设置正确的权限
- 启动数据库:systemctl start mysqld
- 初始化root密码(很多种方法,暂不编写),创建相关使用账号
6.设置从库为只读状态(不影响主从同步),由于从库随时会提升成主库,不要写在配置文件中
mysql> set global read_only=1;
现主库备份数据:AAA、BBB填写对应的需要备份的数据库名称(注意会锁表)
# mysqldump -uroot -p --routines --single_transaction --master-data=2 -B AAA BBB > all.sql
参数说明:
–routines:导出存储过程和函数
–single_transaction:导出开始时设置事务隔离状态,并使用一致性快照开始事务,然后unlock tables;而lock-tables是锁住一张表不能写操作,直到dump完毕。
–master-data:默认等于1,将dump起始(change master to)binlog点和pos值写到结果中,等于2是将change master to写到结果中并注释。
- 从库创建数据库,并导入数据:
mysql> create database AAA;
mysql> create database BBB;
mysql> source /data/all.sql
- 配置主从同步:
- 主库新增用户并添加授权:(账号密码自定义)
mysql> grant replication slave,reload,super on *.* to syncer@'192.100.0.3' identified by 'syncer';
mysql> flush privileges;
- 从库配置同步:
A.查看备份sql文件(大致前30行),有备份时binlog和pos值
B.从库执行同步命令:
mysql> stop slave;
mysql> change master to master_host='192.100.0.3',master_user='syncer',master_password='syncer',master_log_file='mysql-bin.000367', master_log_pos=707698772;
mysql> start slave;
mysql> show slave status\G;
显示:看到IO和SQL线程均为YES,说明主从配置成功。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Read_Master_Log_Pos: 677960018 # 表示一直在追binlog日志。
Last_IO_Errno: 0
Last_IO_Error: #无报错信息
- 遇到报错:
1.Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF.
MySQL的Master和Slave必须同时开启或者关闭enforce-gtid-consistency和gtid-mode功能,即要保持一致
需要保持主从机器设置均为GTID_MODE = ON即可,从库my.cnf中参数对应修改,并上执行:mysql> SET @@GLOBAL.gtid_mode = ON;