Click here to Skip to main content
15,439,832 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I tried master to master replication in MySQL.After finished all the replication steps when i opened slave status it's showing below error in the two MySQL slave status.(vice versa)

I followed these below steps,
My user is "replicate" only
Here i am was sharing the screen shot for slave status:
Master 1 Slave status:
<MariaDB [ledcontrol]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.1.154
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 666
               Relay_Log_File: mysqld-relay-bin.000009
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000007
             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: 666
              Relay_Log_Space: 1245
              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 'replicate@192.168.1.154:3306' - retry-time: 60  maximum-retries: 86400  message: Access denied for user 'replicate'@'192.168.1.156' (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
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [ledcontrol]> 



Master 2 slave status:

C:\xampp\mysql\bin>mysql -u root -p
Enter password: **********
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 119
Server version: 10.1.33-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.1.156
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 652
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000004
             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: 652
              Relay_Log_Space: 1494
              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 'replicate@192.168.1.156:3306' - retry-time: 60  maximum-retries: 86400  message: Access denied for user 'replicate'@'192.168.1.154' (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
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]>



How to resolve this ,and how to replicate my database changes in these two MySQL databases.

while creating tables in one database in MySQL it's not replicating other MySQL server database.

What I have tried:

Master 1  IP :       192.168.1.156
Master2 IP:         192.168.1.154

1.for replication i made some changes in /etc/mysql/my.cnf
For master 1 configuration id:

[mariadb]

#log-bin = mysql-bin
#log-bin = /var/log/mysql/mysql-bin.log

server-id  = 1
auto-increment-increment=2
auto-increment-offset=1
log-bin=mysql-bin
bind-address=0.0.0.0

binlog_do_db = ledcontrol



 

For Master 2 configuration id:
Xampp comtrol panel in that opened  my.ini file under [mysqld]of the server
it looks like below.

 [mysqld]
server-id = 2
auto-increment-increment = 2
auto-increment-offset = 2
log-bin = mysql-bin
bind-address = 0.0.0.0
binlog_do_db=ledcontrol


Master 1 user:
<pre>
>create user 'replicate'@192.168.1.156 identified by 'slave1';
>grant replication slave on *.* to 'replicate'@192.168.1.156;



Master 2 User:

>create user 'replicate'@192.168.1.154 identified by 'slave2';
>grant replication slave on *.* to 'replicate'@192.168.1.154;





Master 1 status:

Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000016 |    14195 | ledcontrol   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 

Master 2 status:

Server version: 10.1.33-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000012 |      829 | ledcontrol   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


Changing Master host on both of the server:
Master1:
CHANGE MASTER TO MASTER_HOST='192.168.1.154',MASTER_PORT=3306,MASTER_USER='replicate',
MASTER_PASSWORD='slave2',MASTER_LOG_FILE='mysql-bin.000012',MASTER_LOG_POS=829;

Master2:
CHANGE MASTER TO MASTER_HOST='192.168.1.156',MASTER_PORT=3306,MASTER_USER='replicate',
MASTER_PASSWORD='slave1',MASTER_LOG_FILE='mysql-bin.000016',MASTER_LOG_POS=14195;


Master1 Grant all rights:
MariaDB [(none)]> Show grants for 'replicate'@'192.168.1.156';
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for replicate@192.168.1.156                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'replicate'@'192.168.1.156' IDENTIFIED BY PASSWORD '*BE4C2F00C0E154F58EB6FF574AFF93003F2137E8' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------+







Master2 Grant all Rights:
MariaDB [(none)]> Show grants for 'replicate'@'192.168.1.154';
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for replicate@192.168.1.154                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'replicate'@'192.168.1.154' IDENTIFIED BY PASSWORD '*09BC841DD1529B2A88AEDC4666DAD5BF26F55750' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



Restart slave status on both server as:
Posted
Updated 18-Dec-18 18:36pm
v6

1 solution

The error is pretty clear, the replicate user does not have access, probably because you created a replication user in your code.
 
Share this answer
 
Comments
BhavaniSri 19-Dec-18 8:18am     CRLF
Here the user is "replicate" only. it's having full privileges. Previously i shared followed document. Now i was sharing exact steps above .Kindly go through that and guide me to resolve this issue. why in slave status it's showing error like Access denied for user 'replicate'@'192.168.1.156' (using password: YES) How can i resolve this ,how i do bidirectional replication in two servers. please guide some to follow master to master replication steps. I need to replicate the changes in two database servers bi directional .

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900