3台机器mysql数据同步失败
已经成功2台mysql 复制（双向同步）。 现在马上搞3台机器基本想法如下
a （2个库ccxyaccount,ccxydb）
b（1个库ccxyaccount）
c（1个库 ccxydb）
为了简化难度，现在仅仅要求a b之间数据同步，c只要求实实时去取a更新的数据就可以拉
a的my.cnf如下 （现在质疑 复制是否支持多个库的数据同步）
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
log_bin = /var/log/mysql/log_bin
server-id=1
#sql-bin-update-same
binlog-do-db = ccxyaccount,ccxydb
#binlog-ignore-db = mysql,test
master-host = 10.0.4.150
master-user = back
master-password = back
master-port = 3306
replicate-ignore-db=mysql,test
replicate-do-db=ccxyaccount
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
b c 类似。
于以前的问题不同，现在没有报错（有一定经验）。
slave stop;
change master....;
slave start;
3台机器同时ok的，没有错误。
可惜测试数据的时候发现，3台机器之间完全没有数据同步，甚至局部a b 之间数据也没有任何联系 。
问1现在质疑 复制是否支持多个库的数据同步
2 如何查找问题在哪里？ 支持
看日志 及 mysql>show slave status\G 什么日志日志文件名 以及他的路径
mysql>show slave status\G 都很正常 a 主库 从（2个库ccxyaccount,ccxydb）
b 从 主（1个库ccxyaccount）
c从（1个库 ccxydb）
也就是说ab 是数据同步的ccxyaccount c 仅仅复制来自a的一个库ccxydb
a 分别有2个复制用户分别 back （对应 b机器） back2（都应c机器）
。 这样楼上应该清楚业务了吧。
090626 11:00:24mysqld started
090626 11:00:24 The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
090626 11:00:25InnoDB: Started; log sequence number 1 2201759024
090626 11:00:25 Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
090626 11:00:25 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000006' (relay_log_pos 233)
090626 11:00:25 Could not find target log during relay log initialization
090626 11:00:25 Failed to initialize the master info structure
090626 11:00:25 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
090626 11:05:22 /usr/libexec/mysqld: Normal shutdown
090626 11:05:22InnoDB: Starting shutdown...
090626 11:05:23InnoDB: Shutdown completed; log sequence number 1 2201759024
090626 11:05:23 /usr/libexec/mysqld: Shutdown complete
090626 11:05:23mysqld ended
090626 11:05:24mysqld started
090626 11:05:24 The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
090626 11:05:24InnoDB: Started; log sequence number 1 2201759024
090626 11:05:24 Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
090626 11:05:24 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000006' (relay_log_pos 233)
090626 11:05:24 Could not find target log during relay log initialization
090626 11:05:24 Failed to initialize the master info structure
090626 11:05:24 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
090626 11:40:08 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000006' (relay_log_pos 233)
090626 11:40:08 Could not find target log during relay log initialization
090626 11:41:41 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000006' (relay_log_pos 233)
090626 11:41:41 Could not find target log during relay log initialization
090626 11:41:48 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000006' (relay_log_pos 233)
090626 11:41:48 Could not find target log during relay log initialization
我复制了2天的错误日志 （a机器 ）清只看 26号（今天的就可以拉） b machine error 2009-06-26
090625 21:45:35mysqld started
090625 21:45:35 The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
090625 21:45:36InnoDB: Started; log sequence number 0 46409
090625 21:45:36 Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
090625 21:45:36 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000002' (relay_log_pos 54
090625 21:45:36 Could not find target log during relay log initialization
090625 21:45:36 Failed to initialize the master info structure
090625 21:45:36 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
090625 21:47:34 Slave SQL thread initialized, starting replication in log 'log_bin.000028' at position 413, relay log '/var/run/mysqld/mysqld-relay-bin.000002' position: 548
090625 21:47:34 Slave I/O thread: connected to master 'eugene@10.0.4.149:3306',replication started in log 'log_bin.000028' at position 413
090625 22:48:47 Slave: received end packet from server, apparent master shutdown:
090625 22:48:47 Slave I/O thread: Failed reading log event, reconnecting to retry, log 'log_bin.000030' position 310
090625 22:48:47 Slave I/O thread: error reconnecting to master 'eugene@10.0.4.149:3306': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 111'errno: 2013retry-time: 120retries: 86400
090625 22:49:47 /usr/libexec/mysqld: Normal shutdown
090625 22:49:47 Slave I/O thread killed during or after a reconnect done to recover from failed read
090625 22:49:47 Slave I/O thread exiting, read up to log 'log_bin.000030', position 310
090625 22:49:47 Error reading relay log event: slave SQL thread was killed
090625 22:49:48InnoDB: Starting shutdown...
090625 22:49:49InnoDB: Shutdown completed; log sequence number 0 46409
090625 22:49:49 /usr/libexec/mysqld: Shutdown complete
090625 22:49:49mysqld ended
090625 22:49:50mysqld started
090625 22:49:50 The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
090625 22:49:50InnoDB: Started; log sequence number 0 46409
090625 22:49:50 Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
090625 22:49:50 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
090625 22:49:50 Slave SQL thread initialized, starting replication in log 'log_bin.000030' at position 310, relay log '/var/run/mysqld/mysqld-relay-bin.000006' position: 233
090625 22:49:50 Slave I/O thread: connected to master 'eugene@10.0.4.149:3306',replication started in log 'log_bin.000030' at position 310
090625 22:53:44 Slave I/O thread killed while reading event
090625 22:53:44 Slave I/O thread exiting, read up to log 'log_bin.000031', position 98
090625 22:53:44 Error reading relay log event: slave SQL thread was killed
090625 22:56:45 Slave SQL thread initialized, starting replication in log 'log_bin.000031' at position 98, relay log '/var/run/mysqld/mysqld-relay-bin.000001' position: 4
090625 22:56:45 Slave I/O thread: connected to master 'back@10.0.4.149:3306',replication started in log 'log_bin.000031' at position 98
090625 23:37:21 Slave: received end packet from server, apparent master shutdown:
090625 23:37:21 Slave I/O thread: Failed reading log event, reconnecting to retry, log 'log_bin.000031' position 98
090625 23:37:21 Slave I/O thread: error reconnecting to master 'back@10.0.4.149:3306': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 111'errno: 2013retry-time: 120retries: 86400
090625 23:37:41 /usr/libexec/mysqld: Normal shutdown
090625 23:37:41 Slave I/O thread killed during or after a reconnect done to recover from failed read
090625 23:37:41 Slave I/O thread exiting, read up to log 'log_bin.000031', position 98
090625 23:37:41 Error reading relay log event: slave SQL thread was killed
090625 23:37:41InnoDB: Starting shutdown...
090625 23:37:43InnoDB: Shutdown completed; log sequence number 0 46409
090625 23:37:43 /usr/libexec/mysqld: Shutdown complete
090625 23:37:43mysqld ended
090625 23:37:43mysqld started
090625 23:37:43 The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
090625 23:37:43InnoDB: Started; log sequence number 0 46409
090625 23:37:43 Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
090625 23:37:43 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
090625 23:37:43 Slave SQL thread initialized, starting replication in log 'log_bin.000031' at position 98, relay log '/var/run/mysqld/mysqld-relay-bin.000002' position: 233
090625 23:37:43 Slave I/O thread: connected to master 'back@10.0.4.149:3306',replication started in log 'log_bin.000031' at position 98
090625 23:39:46 /usr/libexec/mysqld: Normal shutdown
090625 23:39:46 Slave I/O thread killed while reading event
090625 23:39:46 Slave I/O thread exiting, read up to log 'log_bin.000032', position 98
090625 23:39:46 Error reading relay log event: slave SQL thread was killed
090625 23:39:46InnoDB: Starting shutdown...
090625 23:39:48InnoDB: Shutdown completed; log sequence number 0 46409
090625 23:39:48 /usr/libexec/mysqld: Shutdown complete
090625 23:39:48mysqld ended
090626 14:35:27mysqld started
090626 14:35:27 The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
090626 14:35:28InnoDB: Started; log sequence number 0 46409
090626 14:35:28 Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
090626 14:35:28 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000005' (relay_log_pos 233)
090626 14:35:28 Could not find target log during relay log initialization
090626 14:35:28 Failed to initialize the master info structure
090626 14:35:28 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
090626 14:38:22 /usr/libexec/mysqld: Normal shutdown
090626 14:38:22InnoDB: Starting shutdown...
090626 14:38:24InnoDB: Shutdown completed; log sequence number 0 46409
090626 14:38:24 /usr/libexec/mysqld: Shutdown complete
090626 14:38:24mysqld ended
090626 14:38:25mysqld started
090626 14:38:25 The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
090626 14:38:25InnoDB: Started; log sequence number 0 46409
090626 14:38:25 Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
090626 14:38:25 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000005' (relay_log_pos 233)
090626 14:38:25 Could not find target log during relay log initialization
090626 14:38:25 Failed to initialize the master info structure
090626 14:38:25 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
090626 15:04:21 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000005' (relay_log_pos 233)
090626 15:04:21 Could not find target log during relay log initialization
090626 15:07:27 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000005' (relay_log_pos 233)
090626 15:07:27 Could not find target log during relay log initialization
090626 15:07:50 Slave SQL thread initialized, starting replication in log 'log_bin.000034' at position 98, relay log '/var/run/mysqld/mysqld-relay-bin.000001' position: 4
090626 15:07:50 Slave I/O thread: connected to master 'back@10.0.4.149:3306',replication started in log 'log_bin.000034' at position 98 c machine error2009-06-26
90626 00:57:43mysqld started
0906260:57:44InnoDB: Started; log sequence number 0 43655
0906260:57:44 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
0906261:01:04 /usr/libexec/mysqld: Normal shutdown
0906261:01:04InnoDB: Starting shutdown...
0906261:01:07InnoDB: Shutdown completed; log sequence number 0 43655
0906261:01:07 /usr/libexec/mysqld: Shutdown complete
090626 01:01:07mysqld ended
090626 02:33:18mysqld started
0906262:33:19InnoDB: Started; log sequence number 0 43655
0906262:33:19 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
0906263:33:17 /usr/libexec/mysqld: Normal shutdown
0906263:33:17InnoDB: Starting shutdown...
0906263:33:19InnoDB: Shutdown completed; log sequence number 0 43655
0906263:33:19 /usr/libexec/mysqld: Shutdown complete
090626 03:33:19mysqld ended
090626 03:33:20mysqld started
0906263:33:20 The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
0906263:33:20InnoDB: Started; log sequence number 0 43655
0906263:33:20 Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
0906263:33:20 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
0906263:33:20 Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/var/run/mysqld/mysqld-relay-bin.000001' position: 4
0906263:33:20 Slave I/O thread: connected to master 'back2@10.0.4.149:3306',replication started in log 'FIRST' at position 4
0906263:42:43 Slave I/O thread killed while reading event
0906263:42:43 Slave I/O thread exiting, read up to log 'log_bin.000031', position 98
0906263:42:43 Error reading relay log event: slave SQL thread was killed
0906263:45:23 Slave SQL thread initialized, starting replication in log 'log_bin.000031' at position 98, relay log '/var/run/mysqld/mysqld-relay-bin.000001' position: 4
0906263:45:23 Slave I/O thread: connected to master 'back2@10.0.4.149:3306',replication started in log 'log_bin.000031' at position 98
0906264:18:38 /usr/libexec/mysqld: Normal shutdown
0906264:18:38 Slave I/O thread killed while reading event
0906264:18:38 Slave I/O thread exiting, read up to log 'log_bin.000031', position 98
0906264:18:38 Error reading relay log event: slave SQL thread was killed
0906264:18:38InnoDB: Starting shutdown...
0906264:18:39InnoDB: Shutdown completed; log sequence number 0 43655
0906264:18:39 /usr/libexec/mysqld: Shutdown complete
090626 04:18:40mysqld ended
090626 04:18:41mysqld started
0906264:18:41 The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
0906264:18:42InnoDB: Started; log sequence number 0 43655
0906264:18:42 Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
0906264:18:42 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
0906264:18:42 Slave SQL thread initialized, starting replication in log 'log_bin.000031' at position 98, relay log '/var/run/mysqld/mysqld-relay-bin.000002' position: 233
0906264:18:42 Slave I/O thread: connected to master 'back2@10.0.4.149:3306',replication started in log 'log_bin.000031' at position 98
0906264:18:59 /usr/libexec/mysqld: Normal shutdown
0906264:18:59 Slave I/O thread killed while reading event
0906264:18:59 Slave I/O thread exiting, read up to log 'log_bin.000031', position 98
0906264:18:59 Error reading relay log event: slave SQL thread was killed
0906264:18:59InnoDB: Starting shutdown...
0906264:19:00InnoDB: Shutdown completed; log sequence number 0 43655
0906264:19:00 /usr/libexec/mysqld: Shutdown complete
090626 04:19:00mysqld ended
090626 04:19:01mysqld started
0906264:19:01 The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
0906264:19:01InnoDB: Started; log sequence number 0 43655
0906264:19:01 Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
0906264:19:01 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
0906264:19:01 Slave SQL thread initialized, starting replication in log 'log_bin.000031' at position 98, relay log '/var/run/mysqld/mysqld-relay-bin.000004' position: 233
0906264:19:01 Slave I/O thread: connected to master 'back2@10.0.4.149:3306',replication started in log 'log_bin.000031' at position 98
0906264:21:04 Slave: received end packet from server, apparent master shutdown:
0906264:21:04 Slave I/O thread: Failed reading log event, reconnecting to retry, log 'log_bin.000031' position 98
0906264:21:04 Slave I/O thread: error reconnecting to master 'back2@10.0.4.149:3306': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 111'errno: 2013retry-time: 120retries: 86400
0906264:23:04 Slave: connected to master 'back2@10.0.4.149:3306',replication resumed in log 'log_bin.000031' at position 98
0906264:23:20 /usr/libexec/mysqld: Normal shutdown
0906264:23:20 Slave I/O thread killed while reading event
0906264:23:20 Slave I/O thread exiting, read up to log 'log_bin.000032', position 98
0906264:23:20 Error reading relay log event: slave SQL thread was killed
0906264:23:20InnoDB: Starting shutdown...
0906264:23:21InnoDB: Shutdown completed; log sequence number 0 43655
0906264:23:21 /usr/libexec/mysqld: Shutdown complete
090626 04:23:21mysqld ended
090626 19:20:05mysqld started
090626 19:20:06 The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
090626 19:20:08InnoDB: Started; log sequence number 0 43655
090626 19:20:08 Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
090626 19:20:08 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000008' (relay_log_pos 233)
090626 19:20:08 Could not find target log during relay log initialization
090626 19:20:08 Failed to initialize the master info structure
090626 19:20:08 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
090626 19:22:19 /usr/libexec/mysqld: Normal shutdown
090626 19:22:19InnoDB: Starting shutdown...
090626 19:22:20InnoDB: Shutdown completed; log sequence number 0 43655
090626 19:22:20 /usr/libexec/mysqld: Shutdown complete
090626 19:22:20mysqld ended
090626 19:22:21mysqld started
090626 19:22:21 The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
090626 19:22:22InnoDB: Started; log sequence number 0 43655
090626 19:22:22 Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
090626 19:22:22 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000008' (relay_log_pos 233)
090626 19:22:22 Could not find target log during relay log initialization
090626 19:22:22 Failed to initialize the master info structure
090626 19:22:22 /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'socket: '/var/lib/mysql/mysql.sock'port: 3306Source distribution
090626 19:51:51 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000008' (relay_log_pos 233)
090626 19:51:51 Could not find target log during relay log initialization
090626 19:53:48 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000008' (relay_log_pos 233)
090626 19:53:48 Could not find target log during relay log initialization
090626 19:55:37 Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000008' (relay_log_pos 233)
090626 19:55:37 Could not find target log during relay log initialization
页:
[1]