MySQL之主从同步（openEuler版）

实验目的：

基于binlog和gtid两种方法实现主从同步

实验过程：

一、使用binlog方法实现MySQL的主从同步

主库配置

配置文件：
[root@openeuler ~]# vim /etc/my.cnf//进入mysql配置文件配置server_id
[root@openeuler ~]# tail -1 /etc/my.cnf 
server_id=1
[root@openeuler ~]# systemctl restart mysql//重启MySQL服务
主库备份：
[root@openeuler ~]# mysqldump -uroot -pMySQL@123 --opt -B school > db.sql//备份数据库school
mysql> create user rep@'192.168.%.%' identified with myysql_native_password by '123456';//在主库中创建用户（注：授权时可以使用大网段授权）
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to rep@'192.168..%.%';//给用户进行授权
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;//查看主库的binlog信息
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 |      678 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
拷贝给从库：
[root@openeuler ~]# scp db.sql 192.168.27.139:/root/
Authorized users only. All activities may be monitored and reported.
root@192.168.27.139's password: 
db.sql              100% 3558     1.5MB/s   00:00    
[root@openeuler ~]# scp db.sql 192.168.27.140:/root/
Authorized users only. All activities may be monitored and reported.
root@192.168.27.140's password: 
db.sql              100% 3558     3.9MB/s   00:00 

从库配置

配置文件：
[root@node1 ~]# vim /etc/my.cnf//进入配置文件配置server_id
[root@node1 ~]# tail -1 /etc/my.cnf
server_id=2
[root@node1 ~]# systemctl restart mysql//重启MySQL服务
[root@node2 ~]# vim /etc/my.cnf//进入配置文件配置server_id
[root@node2 ~]# tail -1 /etc/my.cnf
server_id=3
[root@node2 ~]# systemctl restart mysql//重启MySQL服务
还原主库备份：
[root@node1 ~]# mysql -uroot -pMySQL@123 < db.sql 
[root@node1 ~]# mysql -uroot -pMySQL@123 -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
[root@node2 ~]# mysql -uroot -pMySQL@123 < db.sql 
[root@node2 ~]# mysql -uroot -pMySQL@123 -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
设置从库change master：
mysql> change master to
    -> master_host='192.168.27.137',
    -> master_user='rep',
    -> master_password='123456',
    -> master_log_file='binlog.000004',
    -> master_log_pos=756;//两从库配置相同
Query OK, 0 rows affected, 8 warnings (0.02 sec)
mysql> start slave;
mysql> show slave status \G//查看主从同步状态
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.27.137
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 756
               Relay_Log_File: node1-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

二、使用gtid方法实现MySQL的主从同步

主库配置：

[root@openeuler ~]# cat /etc/my.cnf //开启gtid
gtid_mode=ON
enforce-gtid-consistency=ON
[root@openeuler ~]# systemctl restart mysql//重启MySQL服务

从库配置：

配置文件：
[root@node1 ~]# cat /etc/my.cnf //开启gtid
gtid_mode=ON
enforce-gtid-consistency=ON
[root@node1 ~]# systemctl restart mysql//重启MySQL服务
[root@node2 ~]# cat /etc/my.cnf //开启gtid
gtid_mode=ON
enforce-gtid-consistency=ON
[root@node2 ~]# systemctl restart mysql//重启MySQL服务
设置从库change master：
mysql> change master to
    -> master_host='192.168.27.137',
    -> master_user='rep',
    -> master_password='123456',
    -> master_auto_position=1;//两从库配置相同
Query OK, 0 rows affected, 7 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.27.137
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000005
          Read_Master_Log_Pos: 157
               Relay_Log_File: node1-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: binlog.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes