环境介绍
|
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
|
[root@HE1 ~]# mysql -uroot -p
Enter
password
:
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
4
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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>
select
version();
+
------------+
| version() |
+
------------+
| 5.7.16-log |
+
------------+
1 row
in
set
(0.00 sec)
mysql> show
global
variables
like
'log_slave%'
;
+
-------------------+-------+
| Variable_name | Value |
+
-------------------+-------+
| log_slave_updates |
OFF
|
+
-------------------+-------+
1 row
in
set
(0.00 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
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
64
65
66
67
68
69
70
71
72
73
74
|
[root@HE1 ~]# mysql -uroot -p
Enter
password
:
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
7
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master
to
send event
Master_Host: 192.168.1.250
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1694
Relay_Log_File: HE1-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1694
Relay_Log_Space: 525
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed:
No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert:
No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1250
Master_UUID: 82b160c7-9a8f-11e6-8412-000c29c6361d
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay:
NULL
Slave_SQL_Running_State: Slave has
read
all
relay log; waiting
for
more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row
in
set
(0.00 sec)
|
|
1
2
3
4
5
6
7
8
|
mysql>
set
global
gtid_mode=
'OFF_PERMISSIVE'
;
Query OK, 0
rows
affected (0.01 sec)
mysql>
set
global
gtid_mode=
'ON_PERMISSIVE'
;
Query OK, 0
rows
affected (0.01 sec)
mysql>
set
global
enforce_gtid_consistency=
ON
;
Query OK, 0
rows
affected (0.00 sec)
mysql>
set
global
gtid_mode=
'ON'
;
Query OK, 0
rows
affected (0.00 sec)
|
|
1
2
3
4
5
6
7
|
mysql> show master status;
+
------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+
------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000005 | 154 | | | 82b160c7-9a8f-11e6-8412-000c29c6361d:1 |
+
------------------+----------+--------------+------------------+----------------------------------------+
1 row
in
set
(0.00 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
32
|
mysql> stop slave;-----从库执行
Query OK, 0 rows affected (0.00 sec)
mysql>
set
global gtid_mode=
'ON_PERMISSIVE'
;
Query OK, 0 rows affected (0.00 sec)
mysql>
set
global gtid_mode=
'OFF_PERMISSIVE'
;
Query OK, 0 rows affected (0.01 sec)
mysql> CHANGE MASTER TO MASTER_AUTO_POSITION=0;
Query OK, 0 rows affected (0.05 sec)
mysql>
set
global gtid_mode=
'OFF'
;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like
'%gtid%'
;
+----------------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | 82b160c7-9a8f-11e6-8412-000c29c6361d:1 |
| session_track_gtids | OFF |
+----------------------------------+----------------------------------------+
8 rows
in
set
(0.01 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
32
33
34
35
36
37
38
39
40
41
|
mysql>
set
global
gtid_mode=
'OFF_PERMISSIVE'
;
Query OK, 0
rows
affected (0.01 sec)
mysql>
set
global
gtid_mode=
'ON_PERMISSIVE'
;
Query OK, 0
rows
affected (0.01 sec)
mysql>
set
global
enforce_gtid_consistency=
ON
;
Query OK, 0
rows
affected (0.00 sec)
mysql>
set
global
gtid_mode=
'ON'
;
Query OK, 0
rows
affected (0.01 sec)
mysql>
mysql>
mysql> show variables
like
'%gtid%'
;
+
----------------------------------+----------------------------------------+
| Variable_name | Value |
+
----------------------------------+----------------------------------------+
| binlog_gtid_simple_recovery |
ON
|
| enforce_gtid_consistency |
ON
|
| gtid_executed_compression_period | 1000 |
| gtid_mode |
ON
|
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | 82b160c7-9a8f-11e6-8412-000c29c6361d:1 |
| session_track_gtids |
OFF
|
+
----------------------------------+----------------------------------------+
8
rows
in
set
(0.00 sec)
主库做操作，从库执行如下命令会看到gtid_set的值在变化
mysql> show master status;
+
------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+
------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000011 | 154 | | | 82b160c7-9a8f-11e6-8412-000c29c6361d:1-2 |
+
------------------+----------+--------------+------------------+------------------------------------------+
1 row
in
set
(0.00 sec)
mysql> show master status;
+
------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+
------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000011 | 154 | | | 82b160c7-9a8f-11e6-8412-000c29c6361d:1-3 |
+
------------------+----------+--------------+------------------+------------------------------------------+
1 row
in
set
(0.00 sec)
|
3261
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
