gtid主从搭建_gtid搭建主从

1.安装 mysql5.7

传送门

2 MySQL主从复制-GTID

1. GTID概念

全局事务标识：global transaction identifiers。

GTID与事务一一对应，并且全局唯一ID。

一个GTID在一个服务器上只执行一次。

MySQL-5.6.5开始支持GTID。

GTID组成：

GTID = server_uuid:transaction_id

2. 配置GTID

配置主从my.cnf

1、主my.cnf：

[root@www ~]# cat /etc/my.cnf

[client]

port      = 3306

socket     = /tmp/mysql.sock

 

[mysql]

no-auto-rehash

 

[mysqld]

user = mysql

basedir = /usr/local/mysql

datadir = /data/mysql/data

port = 3306

socket = /tmp/mysql.sock

log-error = error.log

slow_query_log_file = slow.log

character-set-server = utf8

open_files_limit = 65535

max_connections = 100

max_connect_errors = 100000

lower_case_table_names =1

 

server_id=1

gtid_mode=on

enforce_gtid_consistency=on

\#binlog

log_bin=master-binlog

log-slave-updates=1

binlog_format=row

 

\#relay log

skip-slave-start=1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

主服务器上创建复制账户

[root@www tmp]# mysql -uroot -ptest 

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.32-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> grant replication slave on *.* to 'repl'@'192.168.137.%' identified by '123456';

Query OK, 0 rows affected, 1 warning (1.82 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

在主服务器上备份全库

[root@www ~]# mysqldump -uroot -ptest --set-gtid-purged=OFF --master-data=2 --single-transaction -A > /tmp/mysqlbak_`date +%Y%m%d`.sql


1
2
3

从库上安装mysql（同上安装，传送门）

导入mysql主备份的mysql到从服务器上 可以使用scp命令

[root@test ~]# mysql -uroot -ptest

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.7.32 MySQL Communit
1
2
3
4
5
6
7
8