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
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 master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
set session sql_log_bin=0;
delete from mysql.user where Host='fakehost';
set session sql_log_bin=1;
set session sql_log_bin=0;
delete from mysql.user where Host='fakehost';
set session sql_log_bin=1;
create user 'foo'@'fakehost';
create user 'foo'@'fakehost', 'bar'@'fakehost';
ERROR HY000: Operation CREATE USER failed for 'foo'@'fakehost'
create user 'foo'@'fakehost', 'bar'@'fakehost';
ERROR HY000: Operation CREATE USER failed for 'foo'@'fakehost','bar'@'fakehost'
create user 'foo1'@'fakehost' IDENTIFIED BY 'foo1', 'foo2'@'fakehost'
IDENTIFIED BY PASSWORD'*1111111111111111111111111111111111111111',
'foo3'@'fakehost';
include/sync_slave_sql_with_master.inc
select Host,User from mysql.user where Host='fakehost';
Host User
fakehost bar
fakehost foo
fakehost foo1
fakehost foo2
fakehost foo3
include/diff_tables.inc [master:mysql.user, slave:mysql.user]
rename user 'foo'@'fakehost' to 'foofoo'@'fakehost';
rename user 'not_exist_user1'@'fakehost' to 'foobar'@'fakehost', 'bar'@'fakehost' to 'barbar'@'fakehost';
ERROR HY000: Operation RENAME USER failed for 'not_exist_user1'@'fakehost'
rename user 'not_exist_user1'@'fakehost' to 'foobar'@'fakehost', 'not_exist_user2'@'fakehost' to 'barfoo'@'fakehost';
ERROR HY000: Operation RENAME USER failed for 'not_exist_user1'@'fakehost','not_exist_user2'@'fakehost'
include/sync_slave_sql_with_master.inc
select Host,User from mysql.user where Host='fakehost';
Host User
fakehost barbar
fakehost foo1
fakehost foo2
fakehost foo3
fakehost foofoo
drop user 'foofoo'@'fakehost';
drop user 'foo1'@'fakehost', 'foo2'@'fakehost', 'foo3'@'fakehost';
drop user 'not_exist_user1'@'fakehost', 'barbar'@'fakehost';
ERROR HY000: Operation DROP USER failed for 'not_exist_user1'@'fakehost'
drop user 'not_exist_user1'@'fakehost', 'not_exist_user2'@'fakehost';
ERROR HY000: Operation DROP USER failed for 'not_exist_user1'@'fakehost','not_exist_user2'@'fakehost'
include/sync_slave_sql_with_master.inc
select Host,User from mysql.user where Host='fakehost';
Host User
#
# WL2392: "Change Password at next login" (initial default for root)
#
CREATE USER must_change2@localhost IDENTIFIED BY 'aha';
ALTER USER must_change2@localhost PASSWORD EXPIRE;
include/sync_slave_sql_with_master.inc
select Host,User,password_expired from mysql.user where user='must_change2';
Host User password_expired
localhost must_change2 Y
# must throw an error
SELECT USER();
ERROR HY000: You must SET PASSWORD before executing this statement
# setting a password unlocks it
SET PASSWORD = PASSWORD('aha2');
include/sync_slave_sql_with_master.inc
# must not throw an error
SELECT USER();
USER()
must_change2@localhost
DROP USER must_change2@localhost;
include/sync_slave_sql_with_master.inc
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; create user 'foo'@'fakehost'
master-bin.000001 # Query # # use `test`; create user 'foo'@'fakehost', 'bar'@'fakehost'
master-bin.000001 # Query # # use `test`; CREATE USER 'foo1'@'fakehost' IDENTIFIED BY PASSWORD '*6EAC5249D53050AE649BDB0CC4B85D1AE90CA16E','foo2'@'fakehost' IDENTIFIED BY PASSWORD '*1111111111111111111111111111111111111111','foo3'@'fakehost'
master-bin.000001 # Query # # use `test`; rename user 'foo'@'fakehost' to 'foofoo'@'fakehost'
master-bin.000001 # Query # # use `test`; rename user 'not_exist_user1'@'fakehost' to 'foobar'@'fakehost', 'bar'@'fakehost' to 'barbar'@'fakehost'
master-bin.000001 # Query # # use `test`; drop user 'foofoo'@'fakehost'
master-bin.000001 # Query # # use `test`; drop user 'foo1'@'fakehost', 'foo2'@'fakehost', 'foo3'@'fakehost'
master-bin.000001 # Query # # use `test`; drop user 'not_exist_user1'@'fakehost', 'barbar'@'fakehost'
master-bin.000001 # Query # # use `test`; CREATE USER 'must_change2'@'localhost' IDENTIFIED BY PASSWORD '*B4AD7521513E598788362B069D1AEA3C8837A221'
master-bin.000001 # Query # # use `test`; ALTER USER must_change2@localhost PASSWORD EXPIRE
master-bin.000001 # Query # # use `test`; SET PASSWORD FOR 'must_change2'@'localhost'='*F827ACC9688DECC68F54398510B5B921E9725DE1'
master-bin.000001 # Query # # use `test`; DROP USER must_change2@localhost
include/rpl_end.inc