指定非同步库需要进行以下操作1、binlog-format=ROW方式查看主从binlog方式mysql>showslavestatusG**************************1。行***************************Slave_IO_State：WaitingformastertosendeventMaster_Host：192.168.22.171Master_User：repMaster_Port：3306Connect_Retry：60Master_Log_File：master-bin.000004Read_Master_Log_Pos：463Relay_Log_File：s1.000011Relay_Log_Pos:630Relay_Master_Log_File:master-bin.000004Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:test查看从库状态也有这个配置1：在忽略不同步库的情况下，配置file指定主从库不存在数据库，然后在主库创建这个数据库，测试数据是否同步到主库创建数据库mysql>createdatabasetest;QueryOK,1rowaffected(0.06sec)mysql>showdatabases;+-------------------+|数据库|+-----------------+|information_schema||mysql||performance_schema||sys||test|+-------------------+5rowsinset(0.01sec)viewmysql>showdatabasesfromthedatabase;+--------------------+|数据库|+--------------------+|information_schema||mysql||performance_schema||sys|+------------------+5rowsinset(0.01sec)结论：发现建库的动作没有从数据库同步2：测试尝试主从服务器数据库进行数据同步。目前主从数据库存在于数据库test001[root@mysql-m~]#mysql-uroot-p-e"showdatabases;"|greptest001Enterpassword:test001[root@mysql-s~]#mysql-uroot-p-e"showdatabases;"|greptest001Enterpassword:test001在主库插入数据测试从库同步mysql>usetest001;Databasechangedmysql>createtabletest(->idvarchar(10)notnull,->namevarchar(10)notnull->);QueryOK,0rowsaffected(0.06sec)mysql>insertintotestvalues('zhang','man');QueryOK,1rowaffected(0.03sec)mysql>select*fromtest;+------+------+|id|名称|+------+-----+|zhang|man|+------+------+1rowinset(0.00sec)mysql>select*fromtest001。test;ERROR1146(42S02):Table'test001.test'doesn'texistmysql>usetest001;Databasechangedmysql>showtables;Emptyset(0.00sec)结论：从库没有表，也有数据同步3：默认数据库同步数据库（如mysql）replicate-ignore-db=mysql#忽略指定未同步库test前的数据[root@mysql-m~]#mysql-uroot-p-e"selectuser,hostfrommysql.user;"输入密码：+----------+------------+|用户|主机|+------------+--------------+|mysql.sys|localhost||root|localhost|+------------+------------+[root@mysql-s~]#mysql-uroot-p-e"selectuser,hostfrommysql.user;"输入密码:+------------+------------+|user|host|+------------+------------+|mysql.sys|localhost||root|localhost|+------------+-----------+[root@mysql-m~]#mysql-uroot-p-e"grantallprivilegeson*.*totest@localhostidentifiedby'123456';"输入密码：[root@mysql-m~]#mysql-uroot-p-e"selectuser,hostfrommysql.user;"输入密码：+------------+------------+|user|host|+-----------+------------+|mysql.sys|localhost||root|localhost||test|localhost|+-----------+--------------+[root@mysql-s~]#mysql-uroot-p-e"selectuser,hostfrommysql.user;"输入密码：+-----------+------------+|用户|主机|+------------+---------------+|mysql.sys|localhost||root|localhost||test|localhost|+------------+------------+结论：在这种模式下，该配置对默认库不生效，所以调整配置replicate-ignore-db=mysql#Ignorethespecifiedout-of-synclibraryreplicate-wild-ignore-table=mysql.%#忽略指令所有未同步的库表[root@mysql-m~]#mysql-uroot-p-e"grantallprivilegeson*.*totestuser@localhostidentifiedby'123456';"输入密码：[root@mysql-m~]#mysql-uroot-p-e"selectuser,hostfrommysql.user;"输入密码：+------------+------------+|user|host|+-----------+------------+|mysql.sys|localhost||root|localhost||test|localhost||testuser|localhost|+-----------+------------+[root@mysql-s~]#mysql-uroot-p-e"selectuser,hostfrommysql.user;"输入密码：+---------+------------+|用户|主机|+------------+--------------+|mysql.sys|localhost||root|localhost||test|localhost|+------------+-------------+结论：在该模式下，该配置对于默认库没有同步数据库，需要增加相关表的参数。***测试发现参数replicate-ignore-db=mysql没有加，对default库也会生效。2.binlog-format=STATEMENTmode修改主从库的binlog模式，将不存在的库、存在的库和默认的库一起测试。replicate-ignore-db=test#忽略指定的不同步库（主从不存在）replicate-ignore-db=test001#忽略指定的不同步库replicate-wild-ignore-table=mysql.%#忽略指定未同步库的所有表1：测试默认库数据同步mysql>grantallprivilegeson*.*touser@'localhost'identifiedby'123456';QueryOK,0rowsaffected,1warning(0.03sec)mysql>selectuser,hostfrommysql.user;+------------+------------+|用户|主机|+------------+------------+|rep|%||mysql.sys|localhost||root|localhost||test|localhost||user|localhost|+------------+------------+5rowsinset(0.01sec)[root@mysql-s~]#mysql-uroot-p-e"selectuser,hostfrommysql.user;"输入密码：+------------+------------+|user|host|+------------+------------+|rep|%||mysql.sys|localhost||root|localhost||test|localhost|+------------+------------+结论：该模式下，对于默认库，配置replicate-ignore-db=mysql，对两者都有效默认库和库中的表2：测试不存在主从库的库同步3：测试主从服务器库已经同步后的数据同步(test001)mysql>usetest001;Databasechangedmysql>createtabletest001(->idvarchar(10)notnull,->namevarchar(10)notnull->);QueryOK,0rowsaffected(0.06sec)mysql>insertintotest001values('zhang','man');QueryOK,1rowaffected(0.11sec)[root@mysql-m~]#mysql-uroot-p-e"select*fromtest001.test001;"输入密码：+------+------+|id|name|+------+-----+|zhang|man|+------+-----+[root@mysql-s~]#mysql-uroot-p-e"select*fromtest001.test001;"Enterpassword:ERROR1146(42S02)atline1:Table'test001.test001'doesn'texist
