MySQL 5.7基于GTID的主从复制环境搭建（一主一从）
作者： www.9159.com 发布：2019-11-21
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 #
*www.9159.com，*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 statusG
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 statusG
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.**
本文由9159.com发布于www.9159.com,转载请注明出处：MySQL 5.7基于GTID的主从复制环境搭建（一主一从）
关键词：
上一篇：没有了
下一篇：没有了
下一篇：没有了