主机IP:192.168.164.128
从机IP:192.168.164.136
主机配置
1、编辑/etc/my.cnf文档,开启二进制
[mysqld]
log_bin = mysql-bin
server_id = 120
2、在MySQL中创建主从用户,并给此用户权限
#创建主从同步的账号
create user 'rep1'@'192.168.164.%' identified with mysql_native_password by '123456';
#开启此账号的权限
grant replication slave on *.* to 'rep1'@'192.168.164.%';
3、在数据备份时对数据进行锁表操作
flush tables with read lock;
#操作完成后需要解表
unlock tables;
4、创建一个目录存放备份
#创建存放备份的目录
mkdir /server/backup/ -p
#备份数据
mysqldump -uroot -p -A -B |gzip > /server/backup/mysql_bak.$(date +%F).sql.gz
#查看文件
[root@master ~]# ll /server/backup/
total 228
-rw-r--r--. 1 root root 230643 Sep 16 12:49 mysql_bak.2022-09-16.sql.gz
5、将文件传送到从库
scp /server/backup/mysql_bak.2022-09-16.sql.gz 192.168.164.136:/server/backup/
从库slave配置
1、编辑/etc/my.cnf文档
log_bin = /data/mysql/data/mysql-bin
server_id = 130 #从机服务id不能和主机相同
2、重启数据库
systemctl restart mysqld
3、还原主机备份数据
cd /server/backup/
gzip -d mysql_bak.2022-09-16.sql.gz
mysql -uroot -p < mysql_bak.2022-09-16.sql
4、主从同步配置
mysql> change master to
MASTER_HOST='192.168.164.1280', -- 主机的IP地址
MASTER_PORT=3306,
MASTER_USER='rep1', --- 主机创建的同步账号
MASTER_PASSWORD='123456', --- 同步账号的密码
MASTER_LOG_FILE='mysql-bin.000001', #以下两个数据需要从主机查询
MASTER_LOG_POS=451;
#主机查询数据
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 451 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
5、重新启动从库的同步
start slave;
6、检查状态 --- 以下两个状态为yes
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
验证:在主库创建一个数据库w,在从库查看w数据库是否存在
#在主库创建一个数据库
mysql> create database w;
#在从库查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| w |
+--------------------+
5 rows in set (0.01 sec)