MySQL 主从复制,只复制单库时,无法复制在其他库上的执行的写入操作

因为业务需要,主从复制时,只须复制主服务器上某个库的数据,此时设置replicate-do-db=db_name,就可以了.不过在校验数据的时候发现漏掉N多数据....

检查主库binlog文件,发现很多写操作都不是在本库执行的,比如我只复制主服务器上的A库,可是有很多写入操作都是在B库上执行的,这样这些写入操作全部都丢失了.....

查看dev.mysql.com找到replicate-wild-do-table=db_name.%,将它添加到从库mysql.cnf,重启,问题依旧....

最后,将replicate-do-db=db_name注释掉,再次测试成功.

下面是测试代码:

package com.gyyx.service;

import java.sql.Connection;
import java.sql.SQLException;

import com.gyyx.commons.Log;
import com.gyyx.dbutility.Ibatis;

/**
 * only for test
 * @author tudou@gyyx
 * 2012-02-21
 */
public class TestService {

	/**
	 * @param args
	 * @throws  
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Connection conn = null;
		Connection testconn = null;
		try {
			conn = Ibatis.getConn("161repl");
			testconn = Ibatis.getConn("161test");
			for(int i=0;i<10*10000;i++){
				String sql="insert into test.ibdtest (fid,content,mark) values ("+i+",'sldkjfdsrtytpoytryitryxcvbvcbvcb','sdfhwefeiouhskjdfkdsjfsdf')";
				String repsql="insert into reptest.ibdtest (fid,content,mark) values ("+i+",'sldkjfdsrtytpoytryitryxcvbvcbvcb','sdfhwefeiouhskjdfkdsjfsdf')";
				
				Ibatis.executeNonQuery(conn, sql);
				Ibatis.executeNonQuery(conn, repsql);
				
				sql="insert into ibdtest (fid,content,mark) values ("+i+",'sldkjfdsrtytpoytryitryxcvbvcbvcb','sdfhwefeiouhskjdfkdsjfsdf')";
				repsql="insert into ibdtest1 (fid,content,mark) values ("+i+",'sldkjfdsrtytpoytryitryxcvbvcbvcb','sdfhwefeiouhskjdfkdsjfsdf')";
				
				Ibatis.executeNonQuery(testconn, sql);
				Ibatis.executeNonQuery(testconn, repsql);
			}
		} catch (SQLException e) {
			Log.logError(e);
		}finally{
			try {
				conn.close();
				testconn.close();
			} catch (SQLException e) {
				Log.logError(e);
			}
		}
	}

}

最后的测试图:

主库数据

从库数据