1

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

error connecting to master 'repl@10.0.0.1: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

EDIT

remove replication client grant - this was a hangover from trying random things and not related. Thanks @RickJames for spotting.

| improve this question | |
0

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
| improve this answer | |
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.

| improve this answer | |
  • 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. – artfulrobot Jul 19 '18 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 '18 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. – artfulrobot Jul 20 '18 at 8:12
  • Ah, good point. – Rick James Jul 20 '18 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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