MySQL主从同步忽略特定库

  • A+
所属分类:Database

接上篇文章《配置MYSQL数据主从同步》,有一些库可能不想要进行同步,因此可以在配置文件中做如下操作:
一、主库、从库同步测试

结论:主库、从库同步正常!

二、主库、从库权限同步测试(此次只在从库的my.cnf增加了replicate-wild-ignore-table=mysql.%)

结论:从库在my.cnf增加replicate-wild-ignore-table=mysql.%后权限未同步

主库mysql-bin内容:

从库mysqld-relay-bin内容:

结论
①:当从服务器注释掉replicate-wild-ignore-table=mysql.%内容后(且mysql服务重新启动),之后的所有同步恢复正常,但是在注释后的授权是无法恢复的,如果想要重新授权,需要在主服务器上重新执行授权命令;
②:从库配置文件增加replicate-wild-ignore-table=mysql.%后,对授权、增加用户、虽然记录到mysqld-relay-bin中,但是会进行过滤,而对增加数据库则进行同步;
备注:Prior to MySQL 5.5.32, this option caused any statements containing fully qualified table names not to be logged if there was no default database specified (that is, when SELECT DATABASE() returned NULL). In MySQL 5.5.32 and later, when there is no default database, no --binlog-ignore-db options are applied, and such statements are always logged. (Bug #11829838, Bug #60188)

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: