设置
设置非常简单直接。它是一对 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
编辑-磁盘空间
两台服务器上的磁盘空间都足够。对于 bdd1，MySQL 位于 /data 中；对于 bdd2，MySQL 位于 /home 中。
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
考虑到以上信息，一切似乎都运行正常，但实际上没有数据被复制。我修改了主数据库，但从属数据库上没有显示任何变化。
我究竟做错了什么 ？
答案1
如果您从未“使用”数据库，并且始终在查询中完全限定数据库，则使用“Binlog_Do_DB”时您的查询将不会写入二进制日志。
一个可能不能按预期工作的示例：如果服务器以 --binlog-do-db=sales 启动，并且您发出以下语句，则不会记录 UPDATE 语句：
使用价格；更新销售.一月设置金额=金额+1000；
由于这些原因，我从不使用do_db/ignore_db变量。
答案2
服务器是否具有不同的服务器 ID？如果错误地将它们设置为相同，则复制似乎可以正常工作，但它将忽略所有语句（它假定它们来自自身，并且不需要运行它们）。
mysql> show variables like 'server_id';
-担