0

I have 2 nodes running MySQL setup using Pacemaker. Every time I standby the slave node and unstandby it later, it is not able to catch up and says the error as:

Last_IO_Error: error connecting to master 'repl@masternode:3306' - retry-time: 60  maximum-retries: 86400  message: Access denied for user 'repl'@'xx.xx.xxx.xx' (using password: YES)

Then I have to execute FLUSH PRIVILEGES on the master node, and execute the following on the slave node and it works fine.

STOP SLAVE;
RESET SLAVE ALL;
RESET MASTER;
SET GLOBAL gtid_slave_pos = '';
SET GLOBAL gtid_slave_pos = '10x-x-xxxx';
CHANGE MASTER to Master_Host = 'masternode', MASTER_USER='repl', MASTER_PASSWORD = 'xxxxxx', MASTER_PORT = 3306, MASTER_USE_GTID = current_pos;
START SLAVE;

I can see on the master node that the grant privileges are there, not sure why they don't work every time the slave node resets.

(mysql@masternode) [(none)] 13:12:37> show grants for 'repl'@'xx.xx.xxx.%';
+---------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected].%                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'xx.xx.xxx.%' IDENTIFIED BY PASSWORD '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' |
+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How do I ensure that these permissions are not revoked or changed with the slave node dying or coming back.

Edit 1: Master and slave status

root@localhost) [(none)] 15:18:04> show master status \G

*************************** 1. row ***************************
            File: BINLOG.000001
        Position: 977
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)


(root@localhost) [(none)] 14:22:29> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Connecting to master
                   Master_Host: masternode
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: BINLOG.000001
           Read_Master_Log_Pos: 325
                Relay_Log_File: mysqld-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: BINLOG.000001
              Slave_IO_Running: Connecting
             Slave_SQL_Running: Yes
               Replicate_Do_DB:
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 325
               Relay_Log_Space: 256
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 1045
                 Last_IO_Error: error connecting to master 'repl@masternode:3306' - retry-time: 60  maximum-retries: 86400  message: Access denied for $ser 'repl'@'xxx.xx.xx.xx' (using password: YES)
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 0
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.00 sec)
5
  • When it gets that Last_IO_Error, it will retry in 60 seconds. MASTER_CONNECT_RETRY can be used in CHANGE MASTER to a smaller value. Do you have a password longer than 41 characters (known bug - MDEV-23857)?
    – danblack
    Nov 10, 2021 at 5:44
  • No, the password is shorter than 41 characters. The whole setup was working fine till the slave node crashed and had to be rebuilt. I am sure I am missing something, but can’t figure out what.
    – Abhishek B
    Nov 10, 2021 at 5:56
  • Do you have sync_binlog=1? Is using the MASTER_{USER,PASSWORD} needed in the CHANGE MASTER statement? Which MariaDB version?
    – danblack
    Nov 10, 2021 at 6:02
  • Please provide SHOW SLAVE STATUS;`` and SHOW MASTER STATUS;`
    – Rick James
    Nov 10, 2021 at 6:03
  • @danblack- Yes, sync_binlog is set to 1. Yes, CHANGE MASTER needs the user and password.
    – Abhishek B
    Nov 10, 2021 at 6:28

1 Answer 1

0

TLDR; It was a different password for 'repl'@'localhost' and 'repl'@'xx.xxx.xx.xx'.

So while rebuilding the slave node, I had updated the password for 'repl'@'xx.xxx.xx.xx', but did not update the password for 'repl'@'localhost'.

The pacemaker resource mysql was using the old replication_passwd, which was the reason why every time the slave node would restart, I had to issue the CHANGE MASTER command and include the replication username and password.

In the attempt to do this, I first update the pacemaker resource mysql, but that led to the master node shutting down as well and not able to start with the error as access denied for 'repl'@'localhost'. So, then I kept the original password, started master server and updated the password for 'repl'@'localhost' and 'repl'@'xx.xxx.xx.xx' both, stopped the master server and updated the password in pacemaker mysql resource as well.

It worked after that and restarting the slave node was also smooth.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.