MySQL 5.7基于GTID的主从复制环境搭建（一主一从）

 

Preface

 

    As wel all know,replication base on GTID with row format of binary log is comprehensive used nowadays,especially in MySQL 5.7 version above.I'm going to demonstrate a simplest way to implement a replacation within two servers.

 

Framework

 

Hostname	IP/Port	Identity	OS Version	MySQL Version	GTID Mode	Binlog Format
zlm2	192.168.1.101/3306	master	CentOS 7.0	5.7.21	on	row
zlm3	192.168.1.102/3306	slave	CentOS 7.0	5.7.21	on	row

 

Procedure

 

Generate a new backup by mysqldump and send it to slave host.

 1 [root@zlm2 09:59:29 ~]
 2 #mysqldump -S /tmp/mysql3306.sock -p -A --single-transaction --master-data=2 > db3306_`date +%Y%m%d`.sql
 3 Enter password: 
 4 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
 5 
 6 [root@zlm2 10:04:12 ~]
 7 #ls -l|grep *.sql
 8 
 9 [root@zlm2 10:04:17 ~]
10 #ls -l|grep db3306
11 -rw-r--r--   1 root root    7332442 Jul 10 10:04 db3306_20180710.sql
12 
13 [root@zlm2 10:04:25 ~]
14 #scp db3306_20180710.sql 192.168.1.102:/data
15 root@192.168.1.102's password: 
16 db3306_20180710.sql                                                                                                100% 7161KB   7.0MB/s   00:00    
17 
18 [root@zlm2 10:05:03 ~]
19 #

 

Import the backup on slave host with mysql client tool.

 1 [root@zlm3 10:08:32 ~]
 2 #cd /data
 3 
 4 [root@zlm3 10:08:34 /data]
 5 #ls -l
 6 total 7164
 7 drwxr-xr-x 2 mysql mysql      63 Jun  6 04:24 backup
 8 -rw-r--r-- 1 root  root  7332442 Jul 10 10:05 db3306_20180710.sql
 9 drwxr-xr-x 3 mysql mysql      22 Apr 28 14:01 mysql
10 
11 [root@zlm3 10:08:54 /data]
12 #mysql -S /tmp/mysql3306.sock -p < db3306_20180710.sql 
13 Enter password: 
14 ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
15 
16 //We cannot set global.gtid_purged while global.gtid_executed isn't empty before MySQL 8.0.
17 //The only way is executing "reset master;" on slave.
18 
19 [root@zlm3 10:09:23 /data]
20 #mysql
21 Welcome to the MySQL monitor.  Commands end with ; or \g.
22 Your MySQL connection id is 4
23 Server version: 5.7.21-log MySQL Community Server (GPL)
24 
25 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
26 
27 Oracle is a registered trademark of Oracle Corporation and/or its
28 affiliates. Other names may be trademarks of their respective
29 owners.
30 
31 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
32 
33 (root@localhost mysql3306.sock)[(none)]>show master status;
34 +------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------------------------------------------------+
35 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                  |
36 +------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------------------------------------------------+
37 | mysql-bin.000008 |      230 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-1871,
38 5c77c31b-4add-11e8-81e2-080027de0e0e:1-140,
39 ed142e35-6ed1-11e8-86c6-080027de0e0e:1-17 |
40 +------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------------------------------------------------+
41 1 row in set (0.00 sec)
42 
43 (root@localhost mysql3306.sock)[(none)]>reset master; //After execute it,binlogs on slave will be deleted.
44 Query OK, 0 rows affected (0.02 sec)
45 
46 (root@localhost mysql3306.sock)[(none)]>show master status;
47 +------------------+----------+--------------+------------------+-------------------+
48 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
49 +------------------+----------+--------------+------------------+-------------------+
50 | mysql-bin.000001 |      150 |              |                  |                   |
51 +------------------+----------+--------------+------------------+-------------------+
52 1 row in set (0.00 sec)
53 
54 (root@localhost mysql3306.sock)[(none)]>exit
55 Bye
56 
57 [root@zlm3 10:10:01 /data]
58 #mysql -S /tmp/mysql3306.sock -p < db3306_20180710.sql //Import backed data again.
59 Enter password: 
60 
61 [root@zlm3 10:10:11 /data]
62 #

 

Create replication user 'replica' with password 'replica' on master host.

1 (root@localhost mysql3306.sock)[(none)]>create user replica@'192.168.1.%' identified by 'replica';
2 Query OK, 0 rows affected (0.01 sec)
3 
4 (root@localhost mysql3306.sock)[(none)]>grant replication slave on *.* to replica@'192.168.1.%';
5 Query OK, 0 rows affected (0.00 sec)
6 
7 //You can also create the replication user before generating backup.
8 //Take care of the operation of flushing privileges if there're any problem after executing "start salve;".

 

Set "change master ... " on slave host.

 1 (root@localhost mysql3306.sock)[(none)]>change master to \
 2     -> master_host='192.168.1.101', \
 3     -> master_port=3306, \
 4     -> master_user='replica', \
 5     -> master_password='replica', \
 6     -> master_auto_position=1; //As we are using GTID mode to implement replication,this is indispensable.
 7 Query OK, 0 rows affected, 2 warnings (0.02 sec)
 8 
 9 //If you've forgotten the syntax,you can type "help change master to" to know details.
10 
11 (root@localhost mysql3306.sock)[(none)]>show warnings;
12 +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 | Level | Code | Message                                                                                                                                                                                                                                                                              |
14 +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15 | Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
16 | Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
17 +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
18 2 rows in set (0.00 sec)
19 
20 //Ignore these above warnings.
21 
22 (root@localhost mysql3306.sock)[(none)]>show slave status\G
23 *************************** 1. row ***************************
24                Slave_IO_State: 
25                   Master_Host: 192.168.1.101
26                   Master_User: replica
27                   Master_Port: 3306
28                 Connect_Retry: 60
29               Master_Log_File: 
30           Read_Master_Log_Pos: 4
31                Relay_Log_File: relay-bin.000001
32                 Relay_Log_Pos: 4
33         Relay_Master_Log_File: 
34              Slave_IO_Running: No
35             Slave_SQL_Running: No
36               Replicate_Do_DB: 
37           Replicate_Ignore_DB: 
38            Replicate_Do_Table: 
39        Replicate_Ignore_Table: 
40       Replicate_Wild_Do_Table: 
41   Replicate_Wild_Ignore_Table: 
42                    Last_Errno: 0
43                    Last_Error: 
44                  Skip_Counter: 0
45           Exec_Master_Log_Pos: 0
46               Relay_Log_Space: 150
47               Until_Condition: None
48                Until_Log_File: 
49                 Until_Log_Pos: 0
50            Master_SSL_Allowed: No
51            Master_SSL_CA_File: 
52            Master_SSL_CA_Path: 
53               Master_SSL_Cert: 
54             Master_SSL_Cipher: 
55                Master_SSL_Key: 
56         Seconds_Behind_Master: NULL
57 Master_SSL_Verify_Server_Cert: No
58                 Last_IO_Errno: 0
59                 Last_IO_Error: 
60                Last_SQL_Errno: 0
61                Last_SQL_Error: 
62   Replicate_Ignore_Server_Ids: 
63              Master_Server_Id: 0
64                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
65              Master_Info_File: mysql.slave_master_info
66                     SQL_Delay: 0
67           SQL_Remaining_Delay: NULL
68       Slave_SQL_Running_State: 
69            Master_Retry_Count: 86400
70                   Master_Bind: 
71       Last_IO_Error_Timestamp: 
72      Last_SQL_Error_Timestamp: 
73                Master_SSL_Crl: 
74            Master_SSL_Crlpath: 
75            Retrieved_Gtid_Set: 
76             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-695013,
77 ed142e35-6ed1-11e8-86c6-080027de0e0e:1-17
78                 Auto_Position: 1
79          Replicate_Rewrite_DB: 
80                  Channel_Name: 
81            Master_TLS_Version: 
82 1 row in set (0.00 sec)
83 
84 //The output shows the details about master info.

 

Execute "start slave;" on slave host.

 1 (root@localhost mysql3306.sock)[(none)]>start slave;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (root@localhost mysql3306.sock)[(none)]>show slave status\G
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.101
 8                   Master_User: replica
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000003
12           Read_Master_Log_Pos: 641
13                Relay_Log_File: relay-bin.000002
14                 Relay_Log_Pos: 806
15         Relay_Master_Log_File: mysql-bin.000003
16              Slave_IO_Running: Yes //IO Thread is working normally.
17             Slave_SQL_Running: Yes //SQL Thread is working normally.
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 641
28               Relay_Log_Space: 999
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: 0
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1013306
46                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:695014-695015
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-695015,
59 ed142e35-6ed1-11e8-86c6-080027de0e0e:1-17
60                 Auto_Position: 1
61          Replicate_Rewrite_DB: 
62                  Channel_Name: 
63            Master_TLS_Version: 
64 1 row in set (0.00 sec)

 

    Okay,we are well-off to get a replication architecture now.Is that simple?

 

Variables relevant to GTID on master host.

 1 +---------------------------------------------------+------------------------------------------------------------------------------------------+
 2 | Variable_name                                     | Value                                                                                    |
 3 +---------------------------------------------------+------------------------------------------------------------------------------------------+
 4 | binlog_gtid_simple_recovery                       | ON                                                                                       |
 5 | enforce_gtid_consistency                          | ON                                                                                       |
 6 | group_replication_allow_local_disjoint_gtids_join | OFF                                                                                      |
 7 | group_replication_gtid_assignment_block_size      | 1000000                                                                                  |
 8 | gtid_executed_compression_period                  | 1000                                                                                     |
 9 | gtid_mode                                         | ON                                                                                       |
10 | gtid_next                                         | AUTOMATIC                                                                                |
11 | gtid_owned                                        |                                                                                          |
12 | gtid_purged                                       | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-695003,
13 ed142e35-6ed1-11e8-86c6-080027de0e0e:1-17 |
14 | session_track_gtids                               | OFF                                                                                      |
15 +---------------------------------------------------+------------------------------------------------------------------------------------------+
16 10 rows in set (0.00 sec)

 

Variables relevant to GTID on master host.

 1 (root@localhost mysql3306.sock)[(none)]>show variables like '%gtid%';
 2 +---------------------------------------------------+------------------------------------------------------------------------------------------+
 3 | Variable_name                                     | Value                                                                                    |
 4 +---------------------------------------------------+------------------------------------------------------------------------------------------+
 5 | binlog_gtid_simple_recovery                       | ON                                                                                       |
 6 | enforce_gtid_consistency                          | ON                                                                                       |
 7 | group_replication_allow_local_disjoint_gtids_join | OFF                                                                                      |
 8 | group_replication_gtid_assignment_block_size      | 1000000                                                                                  |
 9 | gtid_executed_compression_period                  | 1000                                                                                     |
10 | gtid_mode                                         | ON                                                                                       |
11 | gtid_next                                         | AUTOMATIC                                                                                |
12 | gtid_owned                                        |                                                                                          |
13 | gtid_purged                                       | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-695013,
14 ed142e35-6ed1-11e8-86c6-080027de0e0e:1-17 |
15 | session_track_gtids                               | OFF                                                                                      |
16 +---------------------------------------------------+------------------------------------------------------------------------------------------+
17 10 rows in set (0.00 sec)

 

    We find out that they're almost the same except the variable of "gtid_purged" on slave is larger than it on master host.It's normal.The value will continuously increase while binlogs(actually relay logs) are purged after applying them on slave from time to time if only sql_thread is running.

 

转载于:https://www.cnblogs.com/aaron8219/p/9284604.html