Bug #104218 On replica Master_User should be blank if not storing in slave_master_info
Submitted: 6 Jul 12:19 Modified: 13 Jul 8:25
Reporter: lalit Choudhary Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7, 5.7.34 OS:Any
Assigned to: CPU Architecture:Any

[6 Jul 12:19] lalit Choudhary
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
[6 Jul 12:27] lalit Choudhary
Expectation: 
As we can see in example it's trying to start replication 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 NOT use any default user which is confusing for the user.
[7 Jul 7:28] MySQL Verification Team
Hello lalit Choudhary,

Thank you for the report and feedback.

regards,
Umesh
[13 Jul 8:25] lalit Choudhary
Correct expected behavior in 8.0:

fixed in MySQL-8.0.21 by https://github.com/mysql/mysql-server/commit/304e920403cecaab9a45834d041bc2ea5a0d2a57. 

slave1 [localhost] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 127.0.0.1
                  Master_User: 
                  Master_Port: 23631
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 338
               Relay_Log_File: mysql-relay.000003
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: Yes

        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 13117
                Last_IO_Error: Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated.
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 00023631-1111-1111-1111-111111111111
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 210713 13:50:45
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 00023631-1111-1111-1111-111111111111:1
            Executed_Gtid_Set: 00023631-1111-1111-1111-111111111111:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > select * from mysql.slave_master_info\G
*************************** 1. row ***************************
                Number_of_lines: 32
                Master_log_name: mysql-bin.000002
                 Master_log_pos: 338
                           Host: 127.0.0.1
                      User_name: 
                  User_password: 
                           Port: 23631