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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
include/master-slave.inc
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
[connection master]
#
# TC1. Verify that replication only succeeds with
# `SYSTEM_VARIABLES_ADMIN` due to the set of `pseudo_thread_id`
# ------------------------------------------------------------------
#
CREATE TABLE t_system (c1 INT);
INSERT INTO t_system VALUES (1), (2);
[connection slave]
CALL mtr.add_suppression(".*you need .at least one of. the SUPER.* SYSTEM_VARIABLES_ADMIN.*");
CALL mtr.add_suppression(".*The replica coordinator and worker threads are stopped.*");
GRANT REPLICATION_APPLIER, CREATE, INSERT, DROP ON *.* TO 'u1'@'localhost';
SHOW GRANTS FOR 'u1'@'localhost';
Grants for u1@localhost
GRANT INSERT, CREATE, DROP ON *.* TO `u1`@`localhost`
GRANT REPLICATION_APPLIER ON *.* TO `u1`@`localhost`
START SLAVE;
Warnings:
Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
include/wait_for_slave_sql_error.inc [errno=1227]
STOP SLAVE;
Warnings:
Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'u1'@'localhost';
SHOW GRANTS FOR 'u1'@'localhost';
Grants for u1@localhost
GRANT INSERT, CREATE, DROP ON *.* TO `u1`@`localhost`
GRANT REPLICATION_APPLIER,SYSTEM_VARIABLES_ADMIN ON *.* TO `u1`@`localhost`
START SLAVE;
Warnings:
Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
[connection master]
include/sync_slave_sql_with_master.inc
include/diff_tables.inc [master:t_system, slave:t_system]
STOP SLAVE;
Warnings:
Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
REVOKE SYSTEM_VARIABLES_ADMIN ON *.* FROM 'u1'@'localhost';
#
# TC2. Verify that replication only succeeds with
# `SUPER` due to the set of `pseudo_thread_id`
# -------------------------------------------------
#
[connection master]
CREATE TABLE t_super (c1 INT);
INSERT INTO t_super VALUES (1), (2);
[connection slave]
SHOW GRANTS FOR 'u1'@'localhost';
Grants for u1@localhost
GRANT INSERT, CREATE, DROP ON *.* TO `u1`@`localhost`
GRANT REPLICATION_APPLIER ON *.* TO `u1`@`localhost`
START SLAVE;
Warnings:
Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
include/wait_for_slave_sql_error.inc [errno=1227]
STOP SLAVE;
Warnings:
Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
GRANT SUPER ON *.* TO 'u1'@'localhost';
Warnings:
Warning 1287 The SUPER privilege identifier is deprecated
SHOW GRANTS FOR 'u1'@'localhost';
Grants for u1@localhost
GRANT INSERT, CREATE, DROP, SUPER ON *.* TO `u1`@`localhost`
GRANT REPLICATION_APPLIER ON *.* TO `u1`@`localhost`
START SLAVE;
Warnings:
Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
[connection master]
include/sync_slave_sql_with_master.inc
include/diff_tables.inc [master:t_super, slave:t_super]
STOP SLAVE;
Warnings:
Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
REVOKE SUPER ON *.* FROM 'u1'@'localhost';
Warnings:
Warning 1287 The SUPER privilege identifier is deprecated
#
# TC3. Verify that replication only succeeds with
# `SESSION_VARIABLES_ADMIN` due to the set of `pseudo_thread_id`
# -------------------------------------------------------------------
#
[connection master]
CREATE TABLE t_session (c1 INT);
INSERT INTO t_session VALUES (1), (2);
[connection slave]
SHOW GRANTS FOR 'u1'@'localhost';
Grants for u1@localhost
GRANT INSERT, CREATE, DROP ON *.* TO `u1`@`localhost`
GRANT REPLICATION_APPLIER ON *.* TO `u1`@`localhost`
START SLAVE;
Warnings:
Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
include/wait_for_slave_sql_error.inc [errno=1227]
STOP SLAVE;
Warnings:
Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'u1'@'localhost';
SHOW GRANTS FOR 'u1'@'localhost';
Grants for u1@localhost
GRANT INSERT, CREATE, DROP ON *.* TO `u1`@`localhost`
GRANT REPLICATION_APPLIER,SESSION_VARIABLES_ADMIN ON *.* TO `u1`@`localhost`
START SLAVE;
Warnings:
Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
[connection master]
include/sync_slave_sql_with_master.inc
include/diff_tables.inc [master:t_session, slave:t_session]
STOP SLAVE;
Warnings:
Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
#
# TC4. Verify that setting `default_table_encryption` only succeeds on
# the slave when having `SUPER` or `SYSTEM_VARIABLES_ADMIN` and
# `TABLE_ENCRYPTION_ADMIN`
# --------------------------------------------------------------------
#
[connection master]
SET default_table_encryption = 1;
CREATE DATABASE new_db;
[connection slave]
REVOKE SUPER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN ON *.* FROM 'u1'@'localhost';
Warnings:
Warning 1287 The SUPER privilege identifier is deprecated
START SLAVE;
Warnings:
Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
include/wait_for_slave_sql_error.inc [errno=1227]
STOP SLAVE;
Warnings:
Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
GRANT SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN ON *.* TO 'u1'@'localhost';
SHOW GRANTS FOR 'u1'@'localhost';
Grants for u1@localhost
GRANT INSERT, CREATE, DROP ON *.* TO `u1`@`localhost`
GRANT REPLICATION_APPLIER,SESSION_VARIABLES_ADMIN,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN ON *.* TO `u1`@`localhost`
START SLAVE;
Warnings:
Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
[connection master]
include/sync_slave_sql_with_master.inc
include/assert.inc [Database new_db exists on slave]
[connection master]
DROP TABLE t_system;
DROP TABLE t_super;
DROP TABLE t_session;
DROP DATABASE new_db;
SET default_table_encryption = OPT_DEFAULT_TABLE_ENCRYPTION;
include/rpl_end.inc
|
