Description:
Issue:
While configuring replica mentioning MASTER_USER and MASTER_PASSWORD is not safe as we get a warning for the same.
example:
slave1 [localhost] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_USER='msandbox',MASTER_PASSWORD='msandbox',MASTER_PORT=23609,MASTER_AUTO_POSI
TION = 1;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
slave1 [localhost] {msandbox} ((none)) > show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
Level: Note
Code: 1760
Message: 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.
2 rows in set (0.00 sec)
As result not mentioning MASTER_USER and MASTER_PASSWORD in CHANGE MASTER TO command and mentioning MASTER_USER and MASTER_PASSWORD while starting replica as follows,
slave1 [localhost] {msandbox} ((none)) > START SLAVE USER = 'rsandbox' PASSWORD ='rsandbox';
Query OK, 0 rows affected, 1 warning (0.02 sec)
slave1 [localhost] {msandbox} ((none)) > show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
1 row in set (0.01 sec)
slave1 [localhost] {msandbox} ((none)) > select * from mysql.slave_master_info\G
*************************** 1. row ***************************
Number_of_lines: 25
Master_log_name: mysql-bin.000002
Master_log_pos: 154
Host: 127.0.0.1
User_name:
User_password:
Port: 23609
Connect_retry: 60
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 30
Bind:
Ignored_server_ids: 0
Uuid: 00023609-1111-1111-1111-111111111111
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 1
Channel_name:
Tls_version:
1 row in set (0.00 sec)
As we can see in the metadata table mysql.slave_master_info, User_name and User_password is blank.
All good at this point.
Now when the replica mysql server restarted, replication starts automatically and failed with error,
Last_IO_Error: error connecting to master 'test@127.0.0.1:23609' - retry-time: 60 retries: 1
example:
slave1 [localhost] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 127.0.0.1
Master_User: test
Master_Port: 23609
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 628
Relay_Log_File: mysql-relay.000004
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master 'test@127.0.0.1:23609' - retry-time: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
slave1 [localhost] {msandbox} ((none)) > select * from mysql.slave_master_info\G
*************************** 1. row ***************************
Number_of_lines: 25
Master_log_name: mysql-bin.000002
Master_log_pos: 628
Host: 127.0.0.1
User_name: test
User_password:
Port: 23609
As we can see now it's trying to start with a test username, but we did not use test user or have in mysql server user list. So username should be blank in "show slave status" and in mysql.slave_master_info and should use any default user which is confusing for the user.
How to repeat:
start 2 nodes with GTID, setup replication as follows,
slave1 [localhost] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=23609,MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected (0.06 sec)
slave1 [localhost] {msandbox} ((none)) > select * from mysql.slave_master_info\G
*************************** 1. row ***************************
Number_of_lines: 25
Master_log_name:
Master_log_pos: 4
Host: 127.0.0.1
User_name:
User_password:
Port: 23609
Connect_retry: 60
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 30
Bind:
Ignored_server_ids: 0
Uuid:
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 1
Channel_name:
Tls_version:
1 row in set (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > START SLAVE USER = 'rsandbox' PASSWORD ='rsandbox';
Query OK, 0 rows affected, 1 warning (0.02 sec)
slave1 [localhost] {msandbox} ((none)) > show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
1 row in set (0.01 sec)
slave1 [localhost] {msandbox} ((none)) > select * from mysql.slave_master_info\G
*************************** 1. row ***************************
Number_of_lines: 25
Master_log_name: mysql-bin.000002
Master_log_pos: 154
Host: 127.0.0.1
User_name:
User_password:
Port: 23609
Connect_retry: 60
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 30
Bind:
Ignored_server_ids: 0
Uuid: 00023609-1111-1111-1111-111111111111
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 1
Channel_name:
Tls_version:
1 row in set (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 23609
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 628
Relay_Log_File: mysql-relay.000003
Relay_Log_Pos: 454
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
restart replica mysqld process.
slave1 [localhost] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 127.0.0.1
Master_User: test
Master_Port: 23609
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 628
Relay_Log_File: mysql-relay.000004
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master 'test@127.0.0.1:23609' - retry-time: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
slave1 [localhost] {msandbox} ((none)) > select * from mysql.slave_master_info\G
*************************** 1. row ***************************
Number_of_lines: 25
Master_log_name: mysql-bin.000002
Master_log_pos: 628
Host: 127.0.0.1
User_name: test
User_password:
Port: 23609
Description: Issue: While configuring replica mentioning MASTER_USER and MASTER_PASSWORD is not safe as we get a warning for the same. example: slave1 [localhost] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_USER='msandbox',MASTER_PASSWORD='msandbox',MASTER_PORT=23609,MASTER_AUTO_POSI TION = 1; Query OK, 0 rows affected, 2 warnings (0.06 sec) slave1 [localhost] {msandbox} ((none)) > show warnings\G *************************** 1. row *************************** Level: Note Code: 1759 Message: Sending passwords in plain text without SSL/TLS is extremely insecure. *************************** 2. row *************************** Level: Note Code: 1760 Message: 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. 2 rows in set (0.00 sec) As result not mentioning MASTER_USER and MASTER_PASSWORD in CHANGE MASTER TO command and mentioning MASTER_USER and MASTER_PASSWORD while starting replica as follows, slave1 [localhost] {msandbox} ((none)) > START SLAVE USER = 'rsandbox' PASSWORD ='rsandbox'; Query OK, 0 rows affected, 1 warning (0.02 sec) slave1 [localhost] {msandbox} ((none)) > show warnings\G *************************** 1. row *************************** Level: Note Code: 1759 Message: Sending passwords in plain text without SSL/TLS is extremely insecure. 1 row in set (0.01 sec) slave1 [localhost] {msandbox} ((none)) > select * from mysql.slave_master_info\G *************************** 1. row *************************** Number_of_lines: 25 Master_log_name: mysql-bin.000002 Master_log_pos: 154 Host: 127.0.0.1 User_name: User_password: Port: 23609 Connect_retry: 60 Enabled_ssl: 0 Ssl_ca: Ssl_capath: Ssl_cert: Ssl_cipher: Ssl_key: Ssl_verify_server_cert: 0 Heartbeat: 30 Bind: Ignored_server_ids: 0 Uuid: 00023609-1111-1111-1111-111111111111 Retry_count: 86400 Ssl_crl: Ssl_crlpath: Enabled_auto_position: 1 Channel_name: Tls_version: 1 row in set (0.00 sec) As we can see in the metadata table mysql.slave_master_info, User_name and User_password is blank. All good at this point. Now when the replica mysql server restarted, replication starts automatically and failed with error, Last_IO_Error: error connecting to master 'test@127.0.0.1:23609' - retry-time: 60 retries: 1 example: slave1 [localhost] {msandbox} ((none)) > show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 127.0.0.1 Master_User: test Master_Port: 23609 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 628 Relay_Log_File: mysql-relay.000004 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1045 Last_IO_Error: error connecting to master 'test@127.0.0.1:23609' - retry-time: 60 retries: 1 Last_SQL_Errno: 0 Last_SQL_Error: slave1 [localhost] {msandbox} ((none)) > select * from mysql.slave_master_info\G *************************** 1. row *************************** Number_of_lines: 25 Master_log_name: mysql-bin.000002 Master_log_pos: 628 Host: 127.0.0.1 User_name: test User_password: Port: 23609 As we can see now it's trying to start with a test username, but we did not use test user or have in mysql server user list. So username should be blank in "show slave status" and in mysql.slave_master_info and should use any default user which is confusing for the user. How to repeat: start 2 nodes with GTID, setup replication as follows, slave1 [localhost] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=23609,MASTER_AUTO_POSITION = 1; Query OK, 0 rows affected (0.06 sec) slave1 [localhost] {msandbox} ((none)) > select * from mysql.slave_master_info\G *************************** 1. row *************************** Number_of_lines: 25 Master_log_name: Master_log_pos: 4 Host: 127.0.0.1 User_name: User_password: Port: 23609 Connect_retry: 60 Enabled_ssl: 0 Ssl_ca: Ssl_capath: Ssl_cert: Ssl_cipher: Ssl_key: Ssl_verify_server_cert: 0 Heartbeat: 30 Bind: Ignored_server_ids: 0 Uuid: Retry_count: 86400 Ssl_crl: Ssl_crlpath: Enabled_auto_position: 1 Channel_name: Tls_version: 1 row in set (0.00 sec) slave1 [localhost] {msandbox} ((none)) > START SLAVE USER = 'rsandbox' PASSWORD ='rsandbox'; Query OK, 0 rows affected, 1 warning (0.02 sec) slave1 [localhost] {msandbox} ((none)) > show warnings\G *************************** 1. row *************************** Level: Note Code: 1759 Message: Sending passwords in plain text without SSL/TLS is extremely insecure. 1 row in set (0.01 sec) slave1 [localhost] {msandbox} ((none)) > select * from mysql.slave_master_info\G *************************** 1. row *************************** Number_of_lines: 25 Master_log_name: mysql-bin.000002 Master_log_pos: 154 Host: 127.0.0.1 User_name: User_password: Port: 23609 Connect_retry: 60 Enabled_ssl: 0 Ssl_ca: Ssl_capath: Ssl_cert: Ssl_cipher: Ssl_key: Ssl_verify_server_cert: 0 Heartbeat: 30 Bind: Ignored_server_ids: 0 Uuid: 00023609-1111-1111-1111-111111111111 Retry_count: 86400 Ssl_crl: Ssl_crlpath: Enabled_auto_position: 1 Channel_name: Tls_version: 1 row in set (0.00 sec) slave1 [localhost] {msandbox} ((none)) > show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: rsandbox Master_Port: 23609 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 628 Relay_Log_File: mysql-relay.000003 Relay_Log_Pos: 454 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes restart replica mysqld process. slave1 [localhost] {msandbox} ((none)) > show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 127.0.0.1 Master_User: test Master_Port: 23609 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 628 Relay_Log_File: mysql-relay.000004 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1045 Last_IO_Error: error connecting to master 'test@127.0.0.1:23609' - retry-time: 60 retries: 1 Last_SQL_Errno: 0 Last_SQL_Error: slave1 [localhost] {msandbox} ((none)) > select * from mysql.slave_master_info\G *************************** 1. row *************************** Number_of_lines: 25 Master_log_name: mysql-bin.000002 Master_log_pos: 628 Host: 127.0.0.1 User_name: test User_password: Port: 23609