掌握mysql slave中的复制主机错误：
我尝试使用master来掌握MySQL中的复制。完成所有复制步骤后，当我打开slave状态时，它在两个MySQL slave状态下显示以下错误。（反之亦然）
我按照以下步骤操作，
我的用户只是复制
我在这里分享了奴隶状态的屏幕截图：
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状态：
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)]>
如何解决这个问题，以及如何解决这个问题在这两个MySQL数据库中复制我的数据库更改。
在MySQL中的一个数据库中创建表时它不会复制其他MySQL服务器数据库。
我尝试过：
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用户：
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状态：
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状态：
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)
更改两台服务器上的Master主机：
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：
更改MASTER_HOST ='192.168.1.156'，MASTER_PORT = 3306，MASTER_USER ='复制'，
MASTER_PASSWORD ='slave1'，MASTER_LOG_FILE ='mysql-bin.000016' ，MASTER_LOG_POS = 14195;
Master1授予所有权利：
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所有权利：
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)
在两台服务器上重启奴隶状态as：
Restart slave status on both server as:
错误非常明显，复制用户无权访问，可能是因为您创建了代码中的复制用户。
The error is pretty clear, thereplicateuser does not have access, probably because you created areplicationuser in your code.