MySQL 在线开启/关闭GTID
来源:互联网 发布:音频剪辑软件下载 编辑:程序博客网 时间:2024/05/18 03:40
环境介绍
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)
0 0
- MySQL 在线开启/关闭GTID
- MySQL5.7在线开启-关闭GTID
- MySQL5.7 GTID在线开启与关闭
- mysql开启GTID跳过错误的方法
- 解决mysql开启GTID主从同步出现1236错误问题
- mysql复制复制切换主主复制开启GTID
- MySQL 5.7搭建备库 开启gtid 使用mysqldump
- Mysql开启GTID后遇到错误跳过方法
- Mysql开启关闭
- ubuntu开启关闭Mysql
- 关闭开启 mysql 服务
- MySQL GTID
- mysql 5.7 gtid复制到传统复制在线切换
- mysql 5.7传统复制到gtid复制的在线切换
- mysql在线开启慢查询
- ubuntu 开启关闭mysql 服务
- mysql索引开启和关闭
- mysql uuid以及gtid
- Windows10如何安装Sqlite3
- 三种DLL(Win32DLL,MFC常规DLL和MFC拓展DLL)的简单学习总结
- 七牛 直播的延迟与优化
- akka学习教程(六) 路由器Router
- 设计模式深入学习--Template Method模板方法模式(行为型模式)
- MySQL 在线开启/关闭GTID
- hibernate的一次简单执行过程
- 2794: [Poi2012]Cloakroom
- 修改Android签名证书keystore作为eclipse默认debug签名证书
- vSphere Client 不能编译版本为10 或更高版本 的虚拟机
- WebGL 3D 数百个 HTML5 例子学习 HT 图形组件
- 百度富文本框ueditor使用教程
- [RK3288][Android6.0] 调试笔记 --- 普通串口的添加
- Qt获取打开,保存文件路径