Debian Codegraph @

mysql-test/suite/rpl/t/rpl_slave_status.test

raw
1 # ==== Purpose ====
2 #
3 # Verify that a slave without replication privileges has
4 # Slave_IO_Running = No
5 #
6 # ==== Method ====
7 #
8 # We do the following steps:
9 # - Create a new replication user on master
10 # - Connect to slave and start replication as this user.
11 # - Verify that slave can replicate well, by creating a table and
12 # inserting a row into it.
13 # - Delete the user from the master.
14 # - Stop and start the slave (this should fail).
15 # - Check the Slave_IO_Running column of SHOW SLAVE STATUS.
16 #
17 # ==== Related bugs and modifications ====
18 #
19 # BUG#10780: slave can't connect to master - IO and SQL threads running
20 #
21 # Added scenario for testing WL#2284:
22 # - a) Replaced 'rpl' user name to 'rpluser_with_length_32_123456789'
23 # i.e with increased length of 32.
24 # - b) Connect to master through new user name of increased length.
25 #
26 # Added few replication cases related to WL#9591
27
28 --source include/not_group_replication_plugin.inc
29 --source include/set_privilege_checks_user_as_system_user.inc
30 --let $rpl_privilege_checks_user_grant_option = 1
31 --source include/master-slave.inc
32
33 --echo ==== Create new replication user ====
34 --echo [on master]
35 connection master;
36 CREATE USER [email protected] IDENTIFIED BY 'rpl';
37 --error ER_WRONG_STRING_LENGTH
38 CREATE USER [email protected] IDENTIFIED BY 'rpl';
39 GRANT REPLICATION SLAVE ON *.* TO [email protected];
40
41 --echo [on slave]
42 --source include/sync_slave_sql_with_master.inc
43 source include/stop_slave.inc;
44 --replace_column 2 ####
45 CHANGE REPLICATION SOURCE TO SOURCE_USER='rpluser_with_length_32_123456789',
46 SOURCE_PASSWORD='rpl', GET_SOURCE_PUBLIC_KEY = 1, SOURCE_SSL = 1;
47 source include/start_slave.inc;
48
49 --echo ==== Do replication as new user ====
50 --echo [on master]
51 connection master;
52 CREATE TABLE t1 (n INT);
53 INSERT INTO t1 VALUES (1);
54 --echo [on slave]
55 --source include/sync_slave_sql_with_master.inc
56 SELECT * FROM t1;
57
58 --echo ==== Delete new replication user ====
59 --echo [on master]
60 connection master;
61 DROP USER [email protected];
62 FLUSH PRIVILEGES;
63
64 --echo [on slave]
65 --source include/sync_slave_sql_with_master.inc
66
67 --echo ==== Restart slave without privileges =====
68 # (slave.err will contain access denied error for this START SLAVE command)
69 source include/stop_slave.inc;
70 START SLAVE;
71 source include/wait_for_slave_sql_to_start.inc;
72 --let $slave_io_errno= convert_error(ER_ACCESS_DENIED_ERROR)
73 source include/wait_for_slave_io_to_stop.inc;
74
75 --echo ==== Verify that Slave IO thread stopped with error ====
76 # 1045 = ER_ACCESS_DENIED_ERROR
77 --let $slave_io_errno= 1045
78 --source include/wait_for_slave_io_error.inc
79
80 #
81 # Bug #77732 REGRESSION: REPLICATION FAILS FOR INSUFFICIENT PRIVILEGES
82 # Proving the bug fixes. Created user's privileges - REPLICATION and SLAVE -
83 # alone must suffice to successful connecting even with show handler
84 # not compatible to 5.6.
85 #
86 --echo [on master]
87 --connection master
88 CREATE USER [email protected] IDENTIFIED BY 'rpl';
89 GRANT REPLICATION SLAVE ON *.* TO [email protected];
90
91 --echo [on slave]
92 --connection slave
93 source include/stop_slave.inc;
94 CHANGE REPLICATION SOURCE TO SOURCE_USER='rpluser_plain', SOURCE_PASSWORD='rpl', SOURCE_SSL = 0, GET_SOURCE_PUBLIC_KEY = 1;
95 source include/start_slave.inc;
96
97 --echo [on master]
98 --connection master
99 DROP USER [email protected];
100 FLUSH PRIVILEGES;
101
102 --echo [on slave]
103 --connection slave
104 source include/stop_slave.inc;
105
106 --echo # Resetting replication user to root
107 --source include/stop_slave_sql.inc
108 --replace_column 2 ####
109 CHANGE REPLICATION SOURCE TO SOURCE_USER = 'root', SOURCE_PASSWORD = '', SOURCE_SSL = 0;
110 --let $rpl_only_running_threads= 1
111 --source include/rpl_reset.inc
112
113 --echo # Replication Tests related to WL#9591 and WL#11057
114 --echo # Check if replication takes place when slave user
115 --echo # created with caching_sha2_pasword
116 --echo [on master]
117 connection master;
118 CREATE USER [email protected] IDENTIFIED WITH
119 'caching_sha2_password' BY 'pwd';
120 GRANT all ON *.* TO [email protected];
121
122 --echo [on slave]
123 --source include/sync_slave_sql_with_master.inc
124 source include/stop_slave.inc;
125 --echo # Set the user created with authentication plugin caching_sha2_password
126 --echo # as replication slave user and get RSA public key from master
127 --replace_column 2 ####
128 CHANGE REPLICATION SOURCE TO SOURCE_USER='u1_caching_sha2_password', SOURCE_PASSWORD='pwd', SOURCE_SSL=0, GET_SOURCE_PUBLIC_KEY = 1;
129 source include/start_slave.inc;
130
131 --echo # Do replication with the user created with
132 --echo # authentication plugin caching_sha2_password
133
134 --echo [on master]
135 connection master;
136 CREATE USER u2 IDENTIFIED WITH 'caching_sha2_password' BY 'pwd_u2';
137 --echo # Below steps ensures that password is not cached initially and
138 --echo # After successful login, it is cached. Hence same login statement
139 --echo # with which login was denied earlier is allowed now
140 --error 1
141 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu2 -ppwd_u2 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
142 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu2 -ppwd_u2 -e "SELECT USER(), CURRENT_USER()" 2>&1
143 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu2 -ppwd_u2 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
144 --echo [on slave]
145 --source include/sync_slave_sql_with_master.inc
146 --echo # On slave it is ensured that password cached on master is not replicated
147 --echo # and after successful login on slave, it is cached
148 --error 1
149 --exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu2 -ppwd_u2 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
150 --exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu2 -ppwd_u2 -e "SELECT USER(), CURRENT_USER()" 2>&1
151 --exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu2 -ppwd_u2 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
152
153 --echo [on master]
154 connection master;
155 FLUSH PRIVILEGES;
156 --echo # Ensure that affect of FLUSH PRIVILEGE is propagated and cache from slave is also cleaned
157 --error 1
158 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu2 -ppwd_u2 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
159 --echo [on slave]
160 --source include/sync_slave_sql_with_master.inc
161 --error 1
162 --exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu2 -ppwd_u2 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
163
164 --echo [on slave]
165 connection slave;
166 source include/stop_slave.inc;
167 --echo # Set the user created with authentication plugin caching_sha2_password
168 --echo # as replication slave user and get RSA public key from master
169 --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
170 --eval CHANGE REPLICATION SOURCE TO SOURCE_USER='u1_caching_sha2_password', SOURCE_PASSWORD='pwd', SOURCE_SSL = 0, SOURCE_PUBLIC_KEY_PATH='$MYSQL_TEST_DIR/std_data/rsa_public_key.pem', GET_SOURCE_PUBLIC_KEY = 0;
171 source include/start_slave.inc;
172
173 --echo # Do replication with the user created with
174 --echo # authentication plugin caching_sha2_password
175
176 --echo [on master]
177 connection master;
178 CREATE USER u3 IDENTIFIED WITH 'caching_sha2_password' BY 'pwd_u3';
179 --echo # Below steps ensures that password is not cached initially and
180 --echo # After successful login, it is cached. Hence same login statement
181 --echo # with which login was denied earlier is allowed now
182 --error 1
183 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu3 -ppwd_u3 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
184 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu3 -ppwd_u3 -e "SELECT USER(), CURRENT_USER()" 2>&1
185 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu3 -ppwd_u3 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
186 --echo [on slave]
187 --source include/sync_slave_sql_with_master.inc
188 --echo # On slave it is ensured that password cached on master is not replicated
189 --echo # and after successful login on slave, it is cached
190 --error 1
191 --exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu3 -ppwd_u3 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
192 --exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu3 -ppwd_u3 -e "SELECT USER(), CURRENT_USER()" 2>&1
193 --exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu3 -ppwd_u3 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
194
195 --echo [on master]
196 connection master;
197 FLUSH PRIVILEGES;
198 --echo # Ensure that affect of FLUSH PRIVILEGE is prpoagated and cache from slave is also cleaned
199 --error 1
200 --exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu3 -ppwd_u3 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
201 --echo [on slave]
202 --source include/sync_slave_sql_with_master.inc
203 --error 1
204 --exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu3 -ppwd_u3 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
205
206 --echo [on slave]
207 --connection slave
208 source include/stop_slave.inc;
209
210 --echo ==== Cleanup (Note that slave IO thread is not running) ====
211 # cleanup: slave io thread has stopped so we reset replication
212 --replace_column 2 ####
213 CHANGE REPLICATION SOURCE TO SOURCE_USER = 'root', SOURCE_PASSWORD = '', SOURCE_SSL = 0;
214 # clear Slave_IO_Errno
215 --let $rpl_only_running_threads= 1
216 --source include/rpl_reset.inc
217
218 --echo [on master]
219 connection master;
220 DROP TABLE t1;
221 DROP USER [email protected];
222 DROP USER u2, u3;
223
224 --source include/rpl_end.inc
225 --source include/force_restart.inc