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
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
|
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]
SET @old_sql_mode_master= @@session.sql_mode;
SET @@session.sql_mode= (select replace(@@session.sql_mode,'NO_BACKSLASH_ESCAPES',''));
SET @old_sql_mode_slave= @@session.sql_mode;
SET @@session.sql_mode= (select replace(@@session.sql_mode,'NO_BACKSLASH_ESCAPES',''));
SET GLOBAL DEBUG= '+d,force_hash_string_with_quote';
CREATE USER 'user1'@'localhost' IDENTIFIED WITH sha256_password BY 'auth_string1';
SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret';
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; ALTER USER 'user1'@'localhost' IDENTIFIED WITH 'sha256_password' AS '$5$BVZy9O>\'a+2MH]_?$fpWyabcdiHjfCVqId/quykZzjaA7adpkcen/uiQrtmOK4p4'
include/sync_slave_sql_with_master.inc
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
slave-bin.000001 # Query # # use `test`; ALTER USER 'user1'@'localhost' IDENTIFIED WITH 'sha256_password' AS '$5$BVZy9O>\'a+2MH]_?$fpWyabcdiHjfCVqId/quykZzjaA7adpkcen/uiQrtmOK4p4'
SET GLOBAL DEBUG= '-d,force_hash_string_with_quote';
SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret1';
include/sync_slave_sql_with_master.inc
SELECT USER();
USER()
user1@localhost
SET GLOBAL DEBUG= '+d,force_hash_string_with_quote';
SET @@session.sql_mode= 'NO_BACKSLASH_ESCAPES';
SET @@session.sql_mode= 'NO_BACKSLASH_ESCAPES';
SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret';
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; ALTER USER 'user1'@'localhost' IDENTIFIED WITH 'sha256_password' AS '$5$BVZy9O>''a+2MH]_?$fpWyabcdiHjfCVqId/quykZzjaA7adpkcen/uiQrtmOK4p4'
include/sync_slave_sql_with_master.inc
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
slave-bin.000001 # Query # # use `test`; ALTER USER 'user1'@'localhost' IDENTIFIED WITH 'sha256_password' AS '$5$BVZy9O>''a+2MH]_?$fpWyabcdiHjfCVqId/quykZzjaA7adpkcen/uiQrtmOK4p4'
SET GLOBAL DEBUG= '-d,force_hash_string_with_quote';
SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret2';
include/sync_slave_sql_with_master.inc
SELECT USER();
USER()
user1@localhost
#
# WL#11544: Current password required for SET PASSWORD
# Create users with current password require clauses.
#
[connection master]
CREATE USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;
ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;
ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT;
SET PASSWORD FOR '11544_u1'@'localhost'='haha';
ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'hehe';
#
# To check that failed statements are not replicated to binlog
#
SET PASSWORD ='ahaha';
ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
SET PASSWORD FOR '11544_u1'@'localhost'='ahaha';
ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
ALTER USER user() IDENTIFIED BY 'ahaha';
ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'ahaha';
ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
SET PASSWORD='ahaha' REPLACE 'xyz';
ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
SET PASSWORD FOR '11544_u1'@'localhost'='ahaha' REPLACE 'xyz';
ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
ALTER USER user() IDENTIFIED BY 'ahaha' REPLACE 'xyz';
ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'ahaha' REPLACE 'xyz';
ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
#
# To check that REPLACE clause is not reflected in the binlog
#
SET PASSWORD='ahaha' REPLACE 'hehe';
SET PASSWORD FOR '11544_u1'@'localhost'='hehe' REPLACE 'ahaha';
ALTER USER user() IDENTIFIED BY 'ahaha' REPLACE 'hehe';
ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'hehe' REPLACE 'ahaha';
# Check the binlog contents on the master
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; CREATE USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' PASSWORD REQUIRE CURRENT OPTIONAL
master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT
master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT
master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
# Check the binlog contents on the slave
include/sync_slave_sql_with_master.inc
[connection slave]
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
slave-bin.000001 # Query # # use `test`; CREATE USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' PASSWORD REQUIRE CURRENT OPTIONAL
slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT
slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT
slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
# Check that we are successfully able to login to the slave from
# the user whose password is changed on master
SELECT USER();
USER()
11544_u1@localhost
[connection master]
DROP USER '11544_u1'@'localhost';
* Create a new user with random password
include/sync_slave_sql_with_master.inc
[connection slave]
SELECT CURRENT_USER();
CURRENT_USER()
u1@localhost
[connection master]
* Set random password using SET PASSWORD
include/sync_slave_sql_with_master.inc
[connection slave]
SELECT CURRENT_USER();
CURRENT_USER()
u1@localhost
[connection master]
* Set random password using ALTER USER
include/sync_slave_sql_with_master.inc
[connection slave]
SELECT CURRENT_USER();
CURRENT_USER()
u1@localhost
[connection master]
DROP USER u1@localhost;
* Check the binlog contents on the master
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; CREATE USER 'u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
master-bin.000001 # Query # # use `test`; ALTER USER 'u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
master-bin.000001 # Query # # use `test`; ALTER USER 'u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
master-bin.000001 # Query # # use `test`; DROP USER u1@localhost
* Create a new user with an arbitrary attribute
CREATE USER u1@localhost IDENTIFIED BY 'foo' ATTRIBUTE '{"trackingId": "12345"}';
CREATE USER u2@localhost COMMENT 'This is account is used by my private LAMP project';
include/sync_slave_sql_with_master.inc
[connection slave]
SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER = 'u1' OR USER = 'u2';
USER HOST ATTRIBUTE
u1 localhost {"trackingId": "12345"}
u2 localhost {"comment": "This is account is used by my private LAMP project"}
[connection master]
ALTER USER u1@localhost COMMENT 'TODO: Delete this user';
include/sync_slave_sql_with_master.inc
[connection slave]
SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER = 'u1';
USER HOST ATTRIBUTE
u1 localhost {"comment": "TODO: Delete this user", "trackingId": "12345"}
include/assert.inc [User JSON comment updated successfully]
include/assert.inc [User JSON attribute updated successfully]
[connection master]
ALTER USER u2@localhost ATTRIBUTE '{ "code": "red" }';
ALTER USER u2@localhost COMMENT 'Change of mind';
include/sync_slave_sql_with_master.inc
[connection slave]
SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER = 'u2';
USER HOST ATTRIBUTE
u2 localhost {"code": "red", "comment": "Change of mind"}
include/assert.inc [User JSON comment updated successfully]
include/assert.inc [User JSON attribute updated successfully]
[connection master]
DROP USER u1@localhost;
DROP USER u2@localhost;
* Check the binlog contents on the master
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; CREATE USER 'u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
master-bin.000001 # Query # # use `test`; CREATE USER 'u2'@'localhost' IDENTIFIED WITH 'caching_sha2_password' COMMENT 'This is account is used by my private LAMP project'
master-bin.000001 # Query # # use `test`; ALTER USER 'u1'@'localhost' COMMENT 'TODO: Delete this user'
master-bin.000001 # Query # # use `test`; ALTER USER 'u2'@'localhost' ATTRIBUTE '{ "code": "red" }'
master-bin.000001 # Query # # use `test`; ALTER USER 'u2'@'localhost' COMMENT 'Change of mind'
master-bin.000001 # Query # # use `test`; DROP USER u1@localhost
master-bin.000001 # Query # # use `test`; DROP USER u2@localhost
SET @@session.sql_mode= @old_sql_mode_slave;
SET @@session.sql_mode= @old_sql_mode_master;
DROP USER 'user1'@'localhost';
include/rpl_end.inc
|
