Edit File: rpl_ndb_dist_priv.result
include/master-slave.inc [connection master] call mysql.mysql_cluster_move_privileges(); select mysql.mysql_cluster_privileges_are_distributed(); mysql.mysql_cluster_privileges_are_distributed() 1 CREATE TABLE t1 (pk INT PRIMARY KEY, a INT) engine = ndb; CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass'; GRANT UPDATE ON t1 TO 'user'@'localhost'; SET PASSWORD FOR 'user'@'localhost'= PASSWORD('newpass'); RENAME USER 'user'@'localhost' TO 'newuser'@'localhost'; GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2'; CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass'; GRANT SELECT ON test.t1 TO 'user2'@'localhost'; select distinct User,Password from mysql.user order by User; User Password newuser *1E9649BB3F345563008E37641B407AFF50E5835C root user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 select User,Table_priv from mysql.tables_priv ORDER BY User; User Table_priv newuser Update user2 Select grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION; select User,Table_priv from mysql.tables_priv ORDER BY User; User Table_priv newuser Update user2 Select GRANT SELECT ON test.t1 TO 'user'@'localhost'; select User,Table_priv from mysql.tables_priv ORDER BY User; User Table_priv newuser Update user Select user2 Select FLUSH PRIVILEGES; select User,Table_priv from mysql.tables_priv ORDER BY User; User Table_priv newuser Update user Select user2 Select REVOKE SELECT ON test.t1 FROM 'user'@'localhost'; select User,Table_priv from mysql.tables_priv ORDER BY User; User Table_priv newuser Update user2 Select CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass'; select distinct User,Password from mysql.user order by User; User Password newuser *1E9649BB3F345563008E37641B407AFF50E5835C root user user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 select User,Table_priv from mysql.tables_priv ORDER BY User; User Table_priv newuser Update user2 Select select distinct User,Password from mysql.user order by User; User Password newuser *1E9649BB3F345563008E37641B407AFF50E5835C root user user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 select User,Table_priv from mysql.tables_priv ORDER BY User; User Table_priv newuser Update user2 Select FLUSH PRIVILEGES; REVOKE UPDATE ON t1 FROM 'newuser'@'localhost'; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost'; select User,Table_priv from mysql.tables_priv ORDER BY User; User Table_priv GRANT SELECT (a) ON test.t1 TO 'user'@'localhost'; select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; User Table_name Column_name user t1 a FLUSH PRIVILEGES; select distinct User,Password from mysql.user order by User; User Password newuser *1E9649BB3F345563008E37641B407AFF50E5835C root user user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 select User,Table_priv from mysql.tables_priv ORDER BY User; User Table_priv user select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; User Table_name Column_name user t1 a select distinct User,Password from mysql.user order by User; User Password newuser *1E9649BB3F345563008E37641B407AFF50E5835C root user user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 select User,Table_priv from mysql.tables_priv ORDER BY User; User Table_priv user select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; User Table_name Column_name user t1 a DROP USER 'newuser'@'localhost'; == Showing binlog server1 == show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass' master-bin.000001 # Query # # use `test`; GRANT UPDATE ON t1 TO 'user'@'localhost' master-bin.000001 # Query # # use `test`; SET PASSWORD FOR 'user'@'localhost'='*D8DECEC305209EEFEC43008E1D420E1AA06B19E0' master-bin.000001 # Query # # use `test`; RENAME USER 'user'@'localhost' TO 'newuser'@'localhost' master-bin.000001 # Query # # use `test`; GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2' master-bin.000001 # Query # # use `test`; CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass' master-bin.000001 # Query # # use `test`; GRANT SELECT ON test.t1 TO 'user2'@'localhost' master-bin.000001 # Query # # use `test`; grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION master-bin.000001 # Query # # use `test`; GRANT SELECT ON test.t1 TO 'user'@'localhost' master-bin.000001 # Query # # REVOKE SELECT ON test.t1 FROM 'user'@'localhost' master-bin.000001 # Query # # CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass' master-bin.000001 # Query # # REVOKE UPDATE ON t1 FROM 'newuser'@'localhost' master-bin.000001 # Query # # REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost' master-bin.000001 # Query # # GRANT SELECT (a) ON test.t1 TO 'user'@'localhost' master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES master-bin.000001 # Query # # DROP USER 'newuser'@'localhost' == Showing binlog server2 == show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass' master-bin.000001 # Query # # GRANT UPDATE ON t1 TO 'user'@'localhost' master-bin.000001 # Query # # SET PASSWORD FOR 'user'@'localhost'='*D8DECEC305209EEFEC43008E1D420E1AA06B19E0' master-bin.000001 # Query # # RENAME USER 'user'@'localhost' TO 'newuser'@'localhost' master-bin.000001 # Query # # GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2' master-bin.000001 # Query # # CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass' master-bin.000001 # Query # # GRANT SELECT ON test.t1 TO 'user2'@'localhost' master-bin.000001 # Query # # grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION master-bin.000001 # Query # # GRANT SELECT ON test.t1 TO 'user'@'localhost' master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES master-bin.000001 # Query # # use `test`; REVOKE SELECT ON test.t1 FROM 'user'@'localhost' master-bin.000001 # Query # # use `test`; CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass' master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES master-bin.000001 # Query # # use `test`; REVOKE UPDATE ON t1 FROM 'newuser'@'localhost' master-bin.000001 # Query # # use `test`; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost' master-bin.000001 # Query # # use `test`; GRANT SELECT (a) ON test.t1 TO 'user'@'localhost' master-bin.000001 # Query # # use `test`; DROP USER 'newuser'@'localhost' select distinct User,Password from mysql.user order by User; User Password root user user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 select User,Table_priv from mysql.tables_priv ORDER BY User; User Table_priv user select distinct User,Password from mysql.user order by User; User Password root user user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 select User,Table_priv from mysql.tables_priv ORDER BY User; User Table_priv user BEGIN; UPDATE mysql.user SET Password = '' WHERE User = 'user2'; SET PASSWORD FOR 'user2'@'localhost' = PASSWORD('newpass'); ERROR 42000: Can't find any matching row in the user table SHOW WARNINGS; Level Code Message Warning 1297 Got temporary error 266 'Time-out in NDB, probably caused by deadlock' from NDB Error 1133 Can't find any matching row in the user table == Showing binlog server2 == show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass' master-bin.000001 # Query # # GRANT UPDATE ON t1 TO 'user'@'localhost' master-bin.000001 # Query # # SET PASSWORD FOR 'user'@'localhost'='*D8DECEC305209EEFEC43008E1D420E1AA06B19E0' master-bin.000001 # Query # # RENAME USER 'user'@'localhost' TO 'newuser'@'localhost' master-bin.000001 # Query # # GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2' master-bin.000001 # Query # # CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass' master-bin.000001 # Query # # GRANT SELECT ON test.t1 TO 'user2'@'localhost' master-bin.000001 # Query # # grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION master-bin.000001 # Query # # GRANT SELECT ON test.t1 TO 'user'@'localhost' master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES master-bin.000001 # Query # # use `test`; REVOKE SELECT ON test.t1 FROM 'user'@'localhost' master-bin.000001 # Query # # use `test`; CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass' master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES master-bin.000001 # Query # # use `test`; REVOKE UPDATE ON t1 FROM 'newuser'@'localhost' master-bin.000001 # Query # # use `test`; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost' master-bin.000001 # Query # # use `test`; GRANT SELECT (a) ON test.t1 TO 'user'@'localhost' master-bin.000001 # Query # # use `test`; DROP USER 'newuser'@'localhost' ROLLBACK; === making backup of new users === call mysql.mysql_cluster_backup_privileges(); ==== clean up ==== DROP USER 'user2'@'localhost'; DROP USER 'user3'@'localhost'; DROP TABLE t1; STOP SLAVE; call mysql.mysql_cluster_backup_privileges(); call mysql.mysql_cluster_backup_privileges(); call mysql.mysql_cluster_backup_privileges(); call mysql.mysql_cluster_restore_local_privileges(); call mysql.mysql_cluster_restore_local_privileges(); call mysql.mysql_cluster_restore_local_privileges();