mysql8安装参考:https://blog.csdn.net/JHYPXS/article/details/102566304
配置主库
vim /etc/my.cnf
写入如下内容
#设置主服务器的ID,可以任意配置但是多个主从之间不能重复
server-id=1
innodb_flush_log_at_trx_commit=2
#开启binlog日志同步功能
sync_binlog=1
#binlog日志文件名
log-bin=/data/mysql/log/mysql-bin-1
登录主库
mysql -u root -p
锁定主数据库
flush tables with read lock;
查找主库状态
show master status;
备份主数据库
导入备份到从库
配置从库
vim /etc/my.cnf
写入如下内容
# 从库配置
server_id=2
log_bin=/data/mysql/log/mysql-bin-2.log
relay_log=/data/mysql/log/mysql-relay-bin.log
#库设为只读的
read_only=1
登录从库
mysql -u root -p
配置主从连接(10.192.6.1为主库IP地址)
CHANGE MASTER TO MASTER_HOST='10.192.6.1',
MASTER_USER='admin',
MASTER_PASSWORD='test',
MASTER_LOG_FILE='mysql-bin-1.000002',
MASTER_LOG_POS=472774;
启动主从复制
start slave;
如果要更新主从连接,需要先停止主从复制
stop slave;
start slave报错:需要先reset slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info
structure from the repository
reset slave;
检查主从复制状态
show slave status \G
若是SlaveIORunning 和 SlaveSQLRunning 都是Yes,说明开启主从复制过程成功
如果SlaveIORunning 和 SlaveSQLRunning 有NO,查看ERROR信息
master and slave have equal MySQL server ids 检查从库设置的机器ID,如果配置文件正确,则重启mysql服务
解除主库锁定
unlock tables;
从库跳过一个错误的事务
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;
show slave status \G;