Мастер-мастер репликация в MySQL раб ошибку :
Я попробовал мастер-мастер репликации в MySQL.После завершения всех шагов репликации, когда я открыл slave status, он показывает ниже ошибку в двух состояниях Slave MySQL.(наоборот)
Я следовал этим нижеприведенным шагам,
Мой пользователь-это только "реплицировать"
Вот я и поделился снимком экрана для статуса раба:
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]>
Мастер 2 статус раба :
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.
Что я уже пробовал:
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;
Мастер 2 Пользователь:
>create user 'replicate'@192.168.1.154 identified by 'slave2'; >grant replication slave on *.* to 'replicate'@192.168.1.154;
Мастер 1 статус:
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)]>
Мастер 2 Статус:
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)
Изменение главного хоста на обоих серверах:
Мастер 1:
Измените MASTER на 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;
Мастер 2:
Измените MASTER на 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;
Мастер1 предоставляет все права:
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 предоставить все права:
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)
Перезапуск ведомого состояния на обоих серверах как: