mysql 复制参数replicate_do_db和replicate_ignore_db介绍

最新推荐文章于 2022-06-16 11:34:05 发布

coco3600

最新推荐文章于 2022-06-16 11:34:05 发布

阅读量1.8k

点赞数 2

文章标签：数据库面试

主库：

binlog-do-db = test
binlog-do-db = lhrdb
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = mysql
binlog-ignore-db = sys

从库：

[mysqld]
server-id = 2
log_bin=E:\MySQL\mysql-advanced-5.6.21-win32\data\xpdblhr-bin
expire_logs_days=10
max_binlog_size = 100M
replicate_do_db=test
replicate_do_db=lhrdb
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys

演示环境：

1.MySQL5.7.24 二进制安装包
2.master库和slave库都是全新的环境进行配置
3.MySQL开启gtid 并且指定具体的数据库进行同步库

maser 库my.cnf配置操作：

[root@VM_82_178_centos ~]# egrep 'server_id|log_slave_updates|gtid_mode|enforce_gtid_consistency|log_bin|binlog_format' /etc/my.cnfserver_id                           =1313306binlog_format                       =row                           
log_bin                             =/data/mysql7/binlog/mysql-bin                     
log_slave_updates                   =on                            gtid_mode                           =on                             enforce_gtid_consistency            =on

mysql -e "grant replication slave on *.* to novelrep@'192.195.1.228' identified by 'JuwoSdk21TbUser'; flush privileges;"
mysqldump -uroot -p'jianwuweirootmysql' --databases test01  test02 --set-gtid-purged=OFF -F --master-data=2 --single-transaction --events|gzip >/opt/juwo_$(date +%F).sql.gz

slave库my.cnf配置操作：

[root@localhost ~]# egrep 'server_id|log_slave_updates|gtid_mode|enforce_gtid_consistency|log_bin|binlog_format' /etc/my.cnfserver_id                           =2283306                       binlog_format                       =row                           
log_bin                             =/data/mysql/binlog/mysql-bin                     
log_slave_updates                   =on                            gtid_mode                           =on                             enforce_gtid_consistency            =on                             [root@localhost ~]#

##slave库上配置如下参数指定数据库进行同步,master库上不需要配置如下参数:

[root@localhost ~]# egrep 'replicate_do_db|replicate_ignore_db' /etc/my.cnfreplicate_do_db=test01replicate_do_db=test02replicate_ignore_db=test03replicate_ignore_db=information_schemareplicate_ignore_db=performance_schemareplicate_ignore_db=mysqlreplicate_ignore_db=sys

#####重启mysql服务:

[root@localhost ~]# gzip -d juwo_2018-12-15.sql.gz [root@localhost ~]# ll juwo_2018-12-15.sql -rw-r--r-- 1 root root 3936 12月 15 15:57 juwo_2018-12-15.sql[root@localhost ~]#

####导入数据到slave，然后change master to:

mysql  -e "source /root/juwo_$(date +%F).sql"
mysql -e "CHANGE MASTER TO MASTER_HOST='192.168.97.131',MASTER_PORT=3306,MASTER_USER='novelrep',MASTER_PASSWORD='JuwoSdk21TbUser',MASTER_AUTO_POSITION = 1;start slave;show slave status\G" |grep -i "yes"

到此处主从复制配置完成

参数replicate_do_db和replicate_ignore_db演示分析：

master库上操作：

root@localhost [mysql]>use mysql
Database changed
root@localhost [mysql]>grant all on test01.* to test01@'127.0.0.1' identified by '356893';
root@localhost [mysql]>select user,host from mysql.user where user='test01';
+--------+-----------+
| user   | host      |
+--------+-----------+
| test01 | 127.0.0.1 |
+--------+-----------+1 row in set (0.00 sec)

slave库上操作：

root@localhost [test01]>select user,host from mysql.user where user='test01';
Empty set (0.00 sec)
root@localhost [test01]>
空的没有被同步过来

master上操作：

root@localhost [mysql]>use test01;Database changedroot@localhost [test01]>grant all on test01.* to test02@'127.0.0.1' identified by '356893';Query OK, 0 rows affected, 1 warning (0.01 sec)

slave库上操作：被同步到slave库上了,但是咱们一开始在salve 上是设置的是不同步mysql库的

select user,host from mysql.user where user='test02';
+--------+-----------+| user   | host      |
+--------+-----------+| test02 | 127.0.0.1 |
+--------+-----------+1 row in set (0.00 sec)

master库上操作：

root@localhost [test01]>drop user test02@'127.0.0.1';Query OK, 0 rows affected (0.00 sec)root@localhost [test01]>select user,host from mysql.user where user='test02';Empty set (0.00 sec)root@localhost [test01]>

slave库上操作：看到slave库上的test02用户同时也被删除了

root@localhost [test01]>select user,host from mysql.user where user='test02';
Empty set (0.00 sec)

在master上操作删除一开始use切入mysql库创建的用户test01@'127.0.0.1'

root@localhost [test01]>drop user test01@'127.0.0.1';Query OK, 0 rows affected (0.01 sec)

在slave库上查看：同步报错
原因是：一开始use切入mysql库创建的用户test01@'127.0.0.1' 没有同步到slave库上，如果此时在master库上没有use切入到mysql库来执行删除用户的语句，由于slave上没有同步过来用户，自然master删除用户时，slave库上同步报错

Last_SQL_Errno: 1396
           Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'df8f817c-f215-11e8-83e4-525400950067:22' at master log mysql-bin.000001, end_log_pos 7734. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

解决办法就是在slave库上把这个用户创建出来：

root@localhost [test01]>grant  all on test01.* to test01@'127.0.0.1' identified by '123456';Query OK, 0 rows affected, 1 warning (0.01 sec)
**重启slave  sql_thread 线程**root@localhost [test01]>stop slave sql_thread;root@localhost [test01]>start slave sql_thread;
同步就ok了。

由此而得出如下结论：

在salve库上使用replicate_do_db和replicate_ignore_db参数进行过滤数据库同步时，此时在系统默认的mysql库上存在一个隐患，就是在master库上进行跨库update更新mysql库下的表或者是grant 进行mysql授权，以及drop user时，slave库上配置的忽略mysql库同步的参数失效。也就是master库上夸库操作mysql库下的表时，是会同步到slave库的。要是use切换到mysql库进行操作时，grant 进行授权，update 更新mysql库下的表，drop 删除时，这样的话master上对mysql库的操作是不会同步到slave上的mysql库中的。

About Me

  ........................................................................................................................  
  ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除  
  ● 本文在itpub（  http://blog.itpub.net/26736162  ）、博客园（  http://www.cnblogs.com/lhrbest  ）和个人微 信公众号（  xiaomaimiaolhr  ）上有同步更新  
  ● 本文itpub地址：  http://blog.itpub.net/26736162   
  ● 本文博客园地址：  http://www.cnblogs.com/lhrbest   
  ● 本文pdf版、个人简介及小麦苗云盘地址：  http://blog.itpub.net/26736162/viewspace-1624453/   
  ● 数据库笔试面试题库及解答：  http://blog.itpub.net/26736162/viewspace-2134706/   
  ● DBA宝典今日头条号地址：  http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826   
  ........................................................................................................................  
  ● QQ群号：  230161599  （满）  、618766405   
  ● 微 信群：可加我微 信，我拉大家进群，非诚勿扰  
  ● 联系我请加QQ好友  （   646634621   ）  ，注明添加缘由  
  ● 于 2019-07-01 06:00 ~ 2019-07-31 24:00 在西安完成  
  ● 最新修改时间：2019-07-01 06:00 ~ 2019-07-31 24:00  
  ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解  
  ● 版权所有，欢迎分享本文，转载请保留出处  
  ........................................................................................................................  
  ●  小麦苗的微店  ：  https://weidian.com/s/793741433?wfr=c&ifr=shopdetail   
  ●  小麦苗出版的数据库类丛书  ：  http://blog.itpub.net/26736162/viewspace-2142121/   
  ●  小麦苗OCP、OCM、高可用网络班  ：  http://blog.itpub.net/26736162/viewspace-2148098/   
  ●  小麦苗腾讯课堂主页  ：  https://lhr.ke.qq.com/   
  ........................................................................................................................  
 使用   微 信客户端   扫描下面的二维码来关注小麦苗的微 信公众号（  xiaomaimiaolhr  ）及QQ群（DBA宝典）、添加小麦苗微 信，  学习最实用的数据库技术。  
 
  ........................................................................................................................  