116 lines
7.6 KiB
Plaintext
116 lines
7.6 KiB
Plaintext
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 @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';
|
|
SET @@session.sql_mode= @old_sql_mode_slave;
|
|
SET @@session.sql_mode= @old_sql_mode_master;
|
|
DROP USER 'user1'@'localhost';
|
|
include/rpl_end.inc
|