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:
Submit an article or tip