0

I had setup mysql replication using percona Xtrabackup. Replication was from my web server to my local machine. It used to work but my ISP changed IP address and replication stopped working. I'm getting this error:

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
......
             Replica_IO_State: Connecting to source
....
....
Last_IO_Errno: 1045
Last_IO_Error: Error connecting to source '[email protected]:1234'. This was attempt 29/86400, with a delay of 60 seconds between attempts. Message: Access denied for user 'repl'@'hostname' (using password: YES)

I can connect to source from replica via command line and access source's db via show database and use database commands

mysql --host=x.x.x.x --user=repl --password='pwd';

On Master

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000081 | 75494370 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

I'm not able to figure out what is wrong with replication? How to find out issue and fix it?

Update

Few more commands

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'hostname';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'repl'@'hostname';
+----------------------------------------------------------+
+--+ | Grants for repl@hostname                            |
+--+ | GRANT REPLICATION SLAVE ON *.* TO `repl`@`hostname` |
+--+ 1 row in set (0.00 sec)
+----------------------------------------------------------+

Command

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='$sourceip',
    SOURCE_USER='repl',
    SOURCE_PASSWORD='$replicapass',
    SOURCE_LOG_FILE='Source-bin.000001',
    SOURCE_LOG_POS=481;
Improve this question
7
  • Have you configured your master via log_bin=/some/path/binlog? Try SHOW MASTER STATUS; on master host to verify if replication enabled.
    – Kondybas
    Commented Jul 24 at 19:52
  • @Kondybas I've updated my question. I think GRANT command doesn't do anything. How do we check & fix it?
    – Raymond
    Commented Jul 24 at 19:59
  • SHOW GRANTS work for current user that is root@localhost You have to use SHOW GRANTS FOR 'repl'@'hostname' instead
    – Kondybas
    Commented Jul 24 at 20:02
  • @Kondybas thanks for pointing that. I've updated my question. Now, I've no clue what is going on? P.S. I don't have SSL specified for this connection, it worked in the past without SSL so not sure if this is an issue now, just wanted to mention.
    – Raymond
    Commented Jul 24 at 20:08
  • @ErgestBasha I've updated my question with CHANGE REPLICATION SOURCE TO command. I'm not sure why you need this? In my case Replica's IP is changed.
    – Raymond
    Commented Jul 25 at 11:47

1 Answer 1

Reset to default
0

It turned out that I was using wrong password in adding repl user on the source. The password used here should be the same password which was specified on CHANGE MASTER TO command.

Improve this answer

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.