MySQL搭建主从复制

本文基于rpm方式安装的MySQL数据库搭建一主一从的主从复制测试库。

centos主机关闭防火墙

systemctl stop firewalld.service;

systemctl disable firewalld.service;

1.主库加读锁，防止数据不一致

flush tables with read lock;

2.导出主库数据，恢复到备库

mysqldump -uroot -p1234 --all-databases > /opt/20221120.sql

备份完后主库quit，退出读锁

从库scp主库备份：scp root@192.168.50.60:/opt/20221120.sql /opt

从库恢复备份：mysql -uroot -p1234 < /opt/202201120.sql

3.主库创建一个复制用户给从库使用

create user repl@'192.168.50.61' identified by '1234';

给用户授予远程访问的权限
grant replication slave on *.* to repl@'192.168.50.61';

4.主库编辑/etc/my.cnf文件

port=3306
server_id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=mysql-bin

5.重启mysqld服务，systemctl restart mysqld;
查看主库的状态，记录日志名和偏移量
show master status;

6.配置从数据库，编辑/etc/my.cnf文件
port=3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
relay-log=mysql-relay-bin
重启从库数据库，systemctl restart mysqld

7.从库配置主库的信息
change master to master_host='192.168.50.60',master_user='repl',master_password='1234',master_log_file='binlog.000007',master_log_pos=1228;
开启从库：start slave;
8.查看从库状态：
show slave status\G;

全为yes表示主从复制开启。

MySQL搭建主从复制

评论