主:ip 192.168.141.135 port:3306
从: ip 192.168.141.136 port:3306
(1)设置server-id值并开启binlog参数
vim /etc/my.cnf.d/mysql-serverf.cnf
log_bin = mysql-bin
server_id = 135
重启数据库
systemctl restart mysqld
(2) 建立同步账号
create user 'rep1'@'192.168.141.%' identified with mysql_native_password by '123456'
grant replication slave on *.* to 'rep1'@'192.168.141.%';
show grants for 'rep1'@'192.168.141.%';
(3)锁表设置为只读
mysql> flush tables with read lock;
(4) 查看主库状态
(5)备份数据库数据
mkdir -p /server/backup/
mysqldump -uroot -p -A -B | gzip > /server/backup/mysql-bak.$(date + %F).sql.gz
(6) 解锁
mysql> unlock tables;
(7)将主库备份数据上传到从库
在从库上创建 mkdir -p /server/backup/
scp /server/backup/mysql_bak.2022-09-11.sql.gz 192.168.141.136:/server/backup/
在从库上设置
(1)设置server - id 值并关闭binlog参数
vim /etc/my.cnf.d/mysql-server.cnf
#log_bin = /data/mysql/data/mysql-bin
server_id = 136
重启数据库
systemctl restrat mysqld
(2) 还原备份数据
cd /server/backup/
gzip -d mysql_bak.2022-09-11.sql.gz
mysql -uroot -p < mysql_bak.2022-09-11.sql
mysql -uroot -p -e 'show databases';
(3)设定主从同步
change master to MASTER_HOST='192.168.141.135', MASTER_PORT=3306, MASTER_USER='rep1', MASTER_PASSWORD='123456', MASTER_LOG_FILE='binlog.000022', MASTER_LOG_POS=157;
(4)启动从库同步开关
mysql> start slave;
检查状态:
mysql> show slave status\G
测试主从同步:
(1)主创建一个数据库:
mysql> create database test1;
(2)从库检查:
mysql> show databases;