народ, поможите чем можете , сами мы не местные
уже кучу времение безрезультатно бьюсь с одной проблемой..
Итак, предположим есть 2 mysql сервера, назовём их, к примеру, M1 и S (Master1 и Slave),
+--+
|M1|
+--+
||
\/
+--+
|S |
+--+
Всё работает, всё отлично.
В один прекрасный день, имея намерение избавиться от единого мастера, появляется желание добавить к существующему M1 серверу
ещё одного мастера и сделать их обоих одновременно слейвами по отношению друг к другу, то есть чтобы M1 и M2 стали осуществлять взаимную репликацию, при этом существующий слейв S остаётся слейвом к M1.
Вот схема:
+--+ +--+
|M1+<==>+M2|
+--+ +--+
||
\/
+--+
|S |
+--+
Сказано - сделано: конфиги принимают соответствующий вид:
M1:
[mysqld]
log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=radius
server-id=11
auto_increment_increment=2
auto_increment_offset=1
M2:
[mysqld]
log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=radius
server-id=5
auto_increment_increment=2
auto_increment_offset=2
S:
[mysqld]
server-id=2
replicate-do-db=radius
если я делаю апдейт на M1 - всё замечательно - операция реплицируется и на M2 и на S, НО
если я делаю такой-же апдейт на M2, то изменения реплицируются _только_ на M1 а на S - как-бы не доходят...
вот пример до апдейта на M2:
M1:
---------------
mysql> select * from accounts;
+----+-------------+
| id | data |
+----+-------------+
| 1 | Hello world |
| 3 | OLD-DATA |
+----+-------------+
2 rows in set (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 1006 | radius | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.107
Master_User: thereplicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: box-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
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: 107
Relay_Log_Space: 407
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 5
1 row in set (0.01 sec)
------------
M2:
mysql> select * from accounts;
+----+-------------+
| id | data |
+----+-------------+
| 1 | Hello world |
| 3 | OLD-DATA |
+----+-------------+
2 rows in set (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | radius | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.105
Master_User: thereplicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1006
Relay_Log_File: box-relay-bin.000002
Relay_Log_Pos: 702
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
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: 1006
Relay_Log_Space: 856
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
1 row in set (0.00 sec)
-------------------
S:
mysql> select * from accounts;
+----+-------------+
| id | data |
+----+-------------+
| 1 | Hello world |
| 3 | OLD-DATA |
+----+-------------+
2 rows in set (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.105
Master_User: thereplicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1006
Relay_Log_File: box-relay-bin.000002
Relay_Log_Pos: 702
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: radius
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: 1006
Relay_Log_Space: 856
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
1 row in set (0.00 sec)
mysql>
--------
делаем апдейт на M2 и получаем следующее:
----
M2:
mysql> update accounts set data = "NEW-DATA" where id = "3";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 331 | radius | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
mysql> select * from accounts;
+----+-------------+
| id | data |
+----+-------------+
| 1 | Hello world |
| 3 | NEW-DATA |
+----+-------------+
2 rows in set (0.01 sec)
----
M1:
mysql> select * from accounts;
+----+-------------+
| id | data |
+----+-------------+
| 1 | Hello world |
| 3 | NEW-DATA |
+----+-------------+
2 rows in set (0.01 sec)
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 1006 | radius | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.107
Master_User: thereplicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 331
Relay_Log_File: box-relay-bin.000002
Relay_Log_Pos: 477
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
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: 331
Relay_Log_Space: 631
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 5
1 row in set (0.00 sec)
mysql>
---
S:
mysql> select * from accounts;
+----+-------------+
| id | data |
+----+-------------+
| 1 | Hello world |
| 3 | OLD-DATA |
+----+-------------+
2 rows in set (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.105
Master_User: thereplicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1006
Relay_Log_File: box-relay-bin.000002
Relay_Log_Pos: 702
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: radius
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: 1006
Relay_Log_Space: 856
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
1 row in set (0.00 sec)
-------
При этом, операция , сделанная на M1 работает как надо.
В чём может быть дело?
АПД: пробовал на разных дистрах - результат одинаков :(
