MySQL(MariaDB)线上主从postition变化无法锁定-不停机不锁库解决方法

前言： 主从教程很多不进行说明，主要讲解线上主从解决方案

MySQL主从执行原理

从库通过执行bin-log日志中的命令达到数据同步的原理

MySQL主从同步需要保证 bin-log 中 position值一致
每一个postition值代表一条数据库操作记录，如果主从各自postition值不对应则执行不同的操作记录，即主从失败。

线上遇到的问题

1. 主库postition随数据的读写进行变化，而从库面对不停变化的postition无法进行手动设置
2. 数据库无法锁库。
3. 数据库过多拷贝数据慢

解决思路

如图，将sql导入的位置记录postition值，将该postion设置到slave从库中，slave会从当前的postition位置同步一直执行到master的位置完成同步。

解决方法

主库

不停机不锁表导出sql语句

mysqldump -uroot -proot --skip-lock-tables --flush-logs --single-transaction --master-data=2 test > /home/tools/test.sql

查看sql语句中记录

head -n 30 all.sql |grep MASTER_LOG_FILE=

记录下图中红框值

进入数据库授权用户

grant replication slave on *.* to 'admin'@'%' identified by 'admin';
FLUSH PRIVILEGES;

从库

进入数据库

mysql -uroot -proot

停止slave

stop slave;

选择数据库并导入sql语句

use test;
source /home/tools/test.sql;

将上述红框中的值修改到下面的命令中：

change master to master_host='192.168.12.145' ,master_port=3306 ,master_user='admin', master_password='admin', master_log_file='mysql-bin.000008',master_log_pos=342;

上述命令参数说明：

• master_host：主库的ip
• master_port: 主库端口
• master_user：主库授权的用户(上面主库里授权的用户)
• master_password:主库授权的用户密码
• master_log_file: SQL文件里记录的值
• master_log_pos：SQL文件里记录的值

设置server_id

SET GLOBAL server_id=5;

刷新：

flush privileges;

启动 slave

start slave;

解决MySQL中数据库过多问题

主库

执行数据库全量拷贝(弊端就是用户信息等数据表会完全被主库覆盖)

mysqldump -uroot -proot --skip-lock-tables --flush-logs --single-transaction --master-data=2 --all-databases > /all.sql

从库

进入从库

mysql -uroot -p

导入数据

source /all.sql

停止slave

stop slave;

需要重启数据库

systemctl restart mariadb

查看数据库的sql文件找到如图配置

head -n 50 /all.sql

进入数据库

mysql -uroot -p

修改如下命令的信息并将上述记录的值设置到命令中：

change master to master_host='192.168.12.145' ,master_port=3306 ,master_user='admin', master_password='admin', master_log_file='mysql-bin.000008',master_log_pos=342;

启动slave

start slave

查看slave状态

show slave status;

两个属性都为yes即成功