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: 

...

检验

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)
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)
win MySQL安装 MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS(Relational Database Management System，关系数据库管理系统) 应用软件之一。mysql5开源 , mysql8社区版。