实验目的：
基于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
