mysql部分数据复制

默认情况下，mysql的主从复制会复制主库所有的数据更改操作，但有时候我们只想复制部分数据，甚至复制其中几张表。mysql提供了实现部分数据复制的方式。
主库添加参数：

binlog_do_db=db1
binlog_ignore_db=db1

从库上添加参数：(推荐设置从库端)

replicate_do_db=db1
replicate_ignore_db=db1
replicate_do_table=db1.t1
replicate_ignore_table=db1.t1
replicate_wild_do_table=db%.%
replicate_wild_ignore_table=db1.%

步骤

#从库
[root@miles22 ~]# ps -ef | grep mysqld
root      22272      1  0 00:39 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/my.cnf
mysql     22620  22272  0 00:39 ?        00:00:18 /usr/local/mysql/bin/mysqld --defaults-file=/data/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/ --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/error.log --pid-file=/data/mysql//miles.pid --socket=/data/mysql.sock --port=3307

[root@miles22 ~]# vi /data/my.cnf 
replicate_do_db=db2

#重启mysql
[root@miles22 ~]# mysqladmin -uroot -pbeijing --socket=/data/mysql.sock shutdown
[root@miles22 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/my.cnf &

mysql> show slave status\G
...
              Replicate_Do_DB: db2
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
...

检验

#主库
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> delete from test;
Query OK, 7 rows affected (1.00 sec)

#从库
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)


#主库
mysql> use db2;
Database changed
mysql> create table t(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

#从库
mysql> use db2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| t             |
+---------------+
1 row in set (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)