1

I had a master-slave set up working fine until a power outage on the slave. Now I get

error connecting to master '[email protected]:50000' - retry-time: 60
maximum-retries: 86400
message: Access denied for user 'repl'@'localhost' (using password: YES)

The password has not changed and I can log in to the database using

slave-host$ mysql -h10.0.0.1 -P50000 --user=repl --password='secret'

However once logged in I cannot see any databases except information_schema. Trying use <my_db_name> gives an access denied error.

On the master

master-host: mysql> show grants for repl@localhost;
GRANT REPLICATION SLAVE ON *.* TO
  'repl'@'localhost' IDENTIFIED BY PASSWORD '*xxxxx'

I've tried re-granting access, FLUSH PRIVILEGES, START/STOP SLAVE; in various combinations but I'm not getting anywhere.

Nb. the host at 10.0.0.1 has an SSH tunnel setup to the datbase host, so grants for repl@localhost are appropriate because the master sees it as local traffic.

Master and slave both on: 10.1.26-MariaDB-0+deb9u1

3 Answers 3

1

Odd.

The fix seemed to be to change the password.

  1. stop slave
  2. change password for repl user at master.
  3. on slave: change master to master_password = 'new pass'
  4. start slave
1

Mariadb 10.5 seems to store passwords in another table mysql.global_priv as well as in mysql.user. I didn't have my users in global_priv which was causing the login issues. Setting the password again fixed the issue. It seems that global_priv is not being replicated, from master and it contains old values before the database was turned into a replica.

I've just checked on old MariaDB 10.3 the global_priv does not exist.

0

You need to GRANT only REPLICATION SLAVE (not CLIENT) to the login name that comes from the Slave.

And, assuming Master and Slave are separate machines, "localhost" will not suffice. It needs to be the hostname or IP address of the Master.

4
  • Thanks. Ah yeah, I'd left that CLIENT thing in by accident. My config didn't have it at first and doesn't have it now - I had been desperately trying different things! The fix was to update the password, don't know why, but others have also found that. As for localhost, it works fine, for the reasons explained in my post. Jul 19, 2018 at 8:00
  • What is the purpose of having Master and Slave on the same machine? Certainly not for disaster protection.
    – Rick James
    Jul 19, 2018 at 16:11
  • 1
    Qutie agree! That's why I access the remote db master server using an SSH tunnel. An SSH tunnel can be set up (as I'm sure you know) like ssh remote -L50000:localhost:3306 which will mean connecting locally on port 50000 will pop out on the remote host and which will then make a 2nd connection to localhost at port 3306. So as far as the mysql server on the remote host is concerned the connection is local - because it comes from the SSH server. on the same host. Jul 20, 2018 at 8:12
  • Ah, good point.
    – Rick James
    Jul 20, 2018 at 14:16

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.