二十、Mysql的过滤复制
2、从库选择性的复制主库的数据。
一、主库选择参数
[none]>show master status;
Binlog_Do_DB #相当于白名单
Binlog_Ignore_DB #相当于黑名单
[none]>show slave status\G
Replicate_Do_DB: #相当于白名单
Replicate_Ignore_DB: #相当于黑名单
1、主库备份 mysqldump -S /data/3307/mysql.sock -A -E --master-data=2 --single-transaction -R --triggers >/backup/full.sql vim /backup/full.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154; 2、从库修改配置文件 [root@db01 ~]# vim /data/3308/my.cnf [mysqld] basedir=/app/mysql datadir=/data/3308/data socket=/data/3308/mysql.sock log_error=/data/3308/mysql.log port=3308 server_id=3308 log_bin=/data/3308/mysql-bin #配置过滤复制,一行一个库 replicate_do_db=ppt replicate_do_db=word [mysql] socket=/data/3308/mysql.sock [root@db01 ~]# systemctl restart mysqld3308 3、从库导入数据并建立主从 [root@db01 ~]# mysql -S /data/3309/mysql.sock source /backup/full.sql CHANGE MASTER TO MASTER_HOST='192.168.32.201', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3307, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154, MASTER_CONNECT_RETRY=10; start slave; 主库: Master [(none)]>create database word; Query OK, 1 row affected (0.00 sec) Master [(none)]>create database ppt; Query OK, 1 row affected (0.00 sec) Master [(none)]>create database excel; Query OK, 1 row affected (0.01 sec) 从库只会复制ppt和word的数据库数据,execl库不会复制
I have a dream so I study hard!!!