MySQL说复制是好的，但数据不会被复制

设置

设置非常简单直接。这是一对Debian服务器，它们之间有一个千兆位连接。 MySQL是稳定的Debian Lenny版本，操作系统是Debian Lenny。

组态

转储已被插入到两个节点上，并且复制已被激活。

主站上的“SHOW MASTER STATUS”命令提供以下信息：

+------------------+----------+--------------------------------------------------------------------------------------------------------------------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------------------------------------------------------------------------------------------------------------------------+------------------+ | mysql-bin.000025 | 98 | smc, smc_allais, smc_montgenevre, smc_preprod, smc_resto, smc_resto, smc_richermoz, smc_sct, smc_skd, smc_skishop, smc_skiteam | | +------------------+----------+--------------------------------------------------------------------------------------------------------------------------------+------------------+

从站上的“SHOW SLAVE STATUS”命令提供以下信息：

 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 188.165.xxx.xxx Master_User: bdd1 Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000025 Read_Master_Log_Pos: 98 Relay_Log_File: mysqld-relay-bin.000003 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.000025 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: smc, smc_allais, smc_montgenevre, smc_preprod, smc_resto, smc_resto, smc_richermoz, smc_sct, smc_skd, smc_skishop, smc_skiteam 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: 98 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/mysql/ca-cert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/mysql/client-cert.pem Master_SSL_Cipher: Master_SSL_Key: /etc/mysql/client-key.pem Seconds_Behind_Master: 0

从站上的“SHOW PROCESSLIST”命令提供以下信息：

 +-----+-------------+-----------+-------------+---------+------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-------------+-----------+-------------+---------+------+-----------------------------------------------------------------------+------------------+ | 366 | root | localhost | smc_preprod | Query | 0 | NULL | show processlist | | 369 | system user | | NULL | Connect | 1097 | Waiting for master to send event | NULL | | 370 | system user | | NULL | Connect | 1096 | Has read all relay log; waiting for the slave I/O thread to update it | NULL | +-----+-------------+-----------+-------------+---------+------+-----------------------------------------------------------------------+------------------+

主人的“SHOW PROCESSLIST”给出以下信息：

 +-----+------+----------------------+-------------+-------------+------+----------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+----------------------+-------------+-------------+------+----------------------------------------------------------------+------------------+ | 410 | user | 91.121.xx.xx:45479 | smc_allais | Sleep | 42 | | NULL | | 415 | user | 91.121.xx.xx:45481 | smc_preprod | Sleep | 1463 | | NULL | | 420 | user | 91.121.xx.xx:46106 | smc_preprod | Sleep | 1528 | | NULL | | 432 | user | 91.121.xx.xx:46155 | NULL | Binlog Dump | 1114 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | | 434 | user | 91.121.xx.xx:60088 | smc_allais | Sleep | 79 | | NULL | | 453 | user | localhost | smc_preprod | Query | 0 | NULL | show processlist | +-----+------+----------------------+-------------+-------------+------+----------------------------------------------------------------+------------------

编辑 – 日志

在从站日志中，我得到以下信息：

 Oct 27 11:48:37 bdd2 mysqld[28477]: 101027 11:48:37 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000027' at position 98, relay log './mysqld-relay-bin.000001' position: 4 Oct 27 11:48:37 bdd2 mysqld[28477]: 101027 11:48:37 [Note] Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'mysql-bin.000027' at position 98

编辑 – 磁盘空间

两台服务器上的磁盘空间已足够。 MySQL在/ data为bdd1，在/ home为bdd2。

 bdd1:~# df -h Filesystem Size Used Avail Use% Mounted on /dev/md1 20G 3.4G 16G 19% / tmpfs 5.9G 0 5.9G 0% /lib/init/rw udev 10M 2.8M 7.3M 28% /dev tmpfs 5.9G 0 5.9G 0% /dev/shm /dev/md2 51G 2.7G 48G 6% /data bdd2:~# df -h Filesystem Size Used Avail Use% Mounted on /dev/md1 10G 2.6G 6.9G 27% / tmpfs 5.9G 0 5.9G 0% /lib/init/rw udev 10M 2.8M 7.3M 28% /dev tmpfs 5.9G 0 5.9G 0% /dev/shm /dev/md2 64G 8.0G 53G 14% /home

考虑到上面的信息，一切似乎都运行得很好，但实际上没有数据被拷贝过来。我修改了master数据库，并且在slave数据库上没有任何更改。

我究竟做错了什么？

如果您从不“使用”数据库，并且始终在查询中完全限定您的数据库，则在使用“Binlog_Do_DB”时，查询不会写入二进制日志。

从http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#option_mysqld_binlog-do-db

下面是一个不能像你所期望的那样的例子：如果服务器是以–binlog-do-db = sales启动的，并且发出以下语句，则不会loggingUPDATE语句：

使用价格; UPDATE sales.january SET amount = amount + 1000;

由于这些原因，我从来不使用do_db/ignore_dbvariables。

服务器有不同的服务器ID吗？如果它们被错误地设置为相同的，那么复制似乎工作正常，但是它将忽略所有的语句（它假定它们是来自它本身，并且不需要运行它们）。

 mysql> show variables like 'server_id';

-担