MySQL复制不起作用 – 没有错误

我把书和奴隶都放在书上。我运行了初始LOAD DATA FROM MASTER; 在奴隶工作得很好。但是，当我将数据插入到主机中时，根本不会被复制到从机。我已经尝试重新启动主从机mysqld进程，以及从机上的“从机停止/从机启动”。这是怎么回事？

主

configuration：

 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = pchelp binlog_ignore_db = mysql binlog_ignore_db = test

mysql> select * from pchelp.test_table;

 +----+---------+ | id | sometxt | +----+---------+ | 1 | x | | 2 | x | | 3 | y | | 4 | z | | 5 | p | | 6 | i | +----+---------+ 6 rows in set (0.00 sec)

mysql>显示主状态\ G

 *************************** 1. row *************************** File: mysql-bin.000009 Position: 106 Binlog_Do_DB: pchelp Binlog_Ignore_DB: mysql,test 1 row in set (0.00 sec)

奴隶

configuration：

 server-id = 2 master-host = hidden.x.xx master-user = replication master-password = hidden master-port = 3308 replicate_do_db = pchelp

mysql> select * from pchelp.test_table;

 +----+---------+ | id | sometxt | +----+---------+ | 1 | x | | 2 | x | | 3 | y | | 4 | z | | 5 | p | | 6 | i | +----+---------+ 6 rows in set (0.00 sec)

mysql> show slave status \ G

 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: hidden.x.xx Master_User: replication Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: pchelp 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: 106 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: 1 row in set (0.01 sec)

主

插入到pchelp.test_table（id，sometxt）values（7，'q'）;

 Query OK, 1 row affected (0.00 sec)

mysql> select * from pchelp.test_table;

 +----+---------+ | id | sometxt | +----+---------+ | 1 | x | | 2 | x | | 3 | y | | 4 | z | | 5 | p | | 6 | i | | 7 | q | +----+---------+ 7 rows in set (0.01 sec)

mysql>显示主状态\ G

 *************************** 1. row *************************** File: mysql-bin.000009 Position: 106 Binlog_Do_DB: pchelp Binlog_Ignore_DB: mysql,test 1 row in set (0.00 sec)

奴隶（在插入主人后）

mysql> select * from pchelp.test_table;

 +----+---------+ | id | sometxt | +----+---------+ | 1 | x | | 2 | x | | 3 | y | | 4 | z | | 5 | p | | 6 | i | +----+---------+ 6 rows in set (0.01 sec)

mysql> show slave status \ G

 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: hidden.x.xx Master_User: replication Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: pchelp 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: 106 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: 1 row in set (0.00 sec)

任何人有什么好的想法可能会出错？用户“复制”具有完全的权限（在主和从机上运行）;

 grant replication slave on *.* to replication@'%' identified by 'hidden'; GRANT ALL PRIVILEGES ON pchelp.* TO replication;

再次， LOAD DATA FROM MASTER; 命令工作得很好..我不明白。

binlog_do_db或者binlog_ignore_db可能不会像你认为的那样工作。这是施瓦茨男爵解释为什么。我的猜测是，在你写这个命令之前，你要么写USE mysql或USE test 。由于这两个数据库被忽略，所以在使用该数据库时运行的任何语句（即使它们写入另一个数据库）都将被忽略。

一个暗示，主人甚至没有写入INSERT语句的二进制日志是在你的SHOW MASTER STATUS\G命令的输出。在INSERT语句之前和之后：

 Position: 106

奴隶也有相同的位置，这解释了为什么它认为它工作正常：

 Read_Master_Log_Pos: 106

build议：删除binlog_do_db和binlog_ignore_dbconfiguration选项。如果您需要过滤，请在从站上执行。