Centos7 Mysql5.7 主从

一、master主库

1、在主数据库里创建一个同步账号授权给从数据库使用

mysql> create user 'repl'@'%' identified by '123456';

mysql> grant replication slave on *.* to 'repl'@'%';

mysql> flush privileges;

2、全备主库，全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致

mysql> FLUSH TABLES WITH READ LOCK;

3、备份主库并将备份文件传送到从库

[root@master ~]# mysqldump -uroot -p123456 --all-databases > /opt/all-2023.sql

[root@master ~]# scp /opt/all-2023.sql root@192.168.111.137:/opt/

4、解锁

mysql> unlock tables;

5、配置主数据库

vim /etc/my.cnf

//在[mysql]这段后面添加

log-bin=mysql-bin        //添加    启用binlog日志
server-id=1                //添加    数据库服务器唯一标识符，主库的server-id值必须比从库的小

6、重启数主库的mysql服务，查看主库的状态

systemctl restart mysqld

//查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

二、slave从库

1、在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致

mysql -uroot -p123456 < /opt/all-2023.sql 

2、配置从数据库

vim /etc/my.cnf

//在[mysql]这段后面添加
server-id=2                //添加 从库的server-id比主库的大
relay-log=mysql-relay-bin        //添加

3、重启mysql服务

systemctl restart mysqld

4、配置并启动主从复制

mysql> reset slave;
mysql> change master to master_host='192.168.111.135', master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
mysql> start slave;
mysql> show slave status \G