The following actions are for specifying an unsynchronized Library
Binlog format = row mode
View the binlog mode of the master and slave
mysql> show slave status\G *********************** 1. row ************************* Slave_IO_State: Waiting for master to send event Master_Host: 192.168.22.171 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000004 Read_Master_Log_Pos: 463 Relay_Log_File: s1.000011 Relay_Log_Pos: 630 Relay_Master_Log_File: master-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: test
Viewing the status of the slave library also shows this configuration
1: ignore that the unsynchronized library does not exist
In the configuration file, specify a library that does not exist in the master and slave libraries, and then create this library in the master library to test whether the data is synchronized in the past
Main database creation database
mysql> create database test; Query OK, 1 row affected (0.06 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.01 sec)
View from library
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.01 sec)
Conclusion: it is found that the action of building database is not synchronized from the database
2: test the master-slave server inventory in data synchronization
At present, both master and slave databases have test001
[root@mysql-m ~]# mysql -uroot -p -e "show databases;"|grep test001 Enter password: test001 [root@mysql-s ~]# mysql -uroot -p -e "show databases;"|grep test001 Enter password: test001
Insert data in the main database to test the synchronization of the slave database
mysql> use test001; Database changed mysql> create table test ( -> id varchar(10) not null, -> name varchar(10) not null -> ); Query OK, 0 rows affected (0.06 sec) mysql> insert into test values('zhang','man'); Query OK, 1 row affected (0.03 sec) mysql> select * from test; +-------+------+ | id | name | +-------+------+ | zhang | man | +-------+------+ 1 row in set (0.00 sec) mysql> select * from test001.test; ERROR 1146 (42S02): Table 'test001.test' doesn't exist mysql> use test001; Database changed mysql> show tables; Empty set (0.00 sec)
Conclusion: there is no table in the slave database, and there is also data synchronization
3: default database synchronization (such as mysql)
replicate-ignore-db = mysql #Ignore specified out of sync Libraries //Data before test [root@mysql-m ~]# mysql -uroot -p -e "select user,host from mysql.user;" Enter password: +-----------+--------------+ | user | host | +-----------+--------------+ | mysql.sys | localhost | | root | localhost | +-----------+--------------+ [root@mysql-s ~]# mysql -uroot -p -e "select user,host from mysql.user;" Enter password: +-----------+--------------+ | user | host | +-----------+--------------+ | mysql.sys | localhost | | root | localhost | +-----------+--------------+ [root@mysql-m ~]# mysql -uroot -p -e "grant all privileges on *.* to test@localhost identified by '123456';" Enter password: [root@mysql-m ~]# mysql -uroot -p -e "select user,host from mysql.user;" Enter password: +-----------+--------------+ | user | host | +-----------+--------------+ | mysql.sys | localhost | | root | localhost | | test | localhost | +-----------+--------------+ [root@mysql-s ~]# mysql -uroot -p -e "select user,host from mysql.user;" Enter password: +-----------+--------------+ | user | host | +-----------+--------------+ | mysql.sys | localhost | | root | localhost | | test | localhost | +-----------+--------------+
Conclusion: in this mode, this configuration does not take effect on the default library, so adjust the configuration
replicate-ignore-db = mysql #Ignore specified out of sync Libraries replicate-wild-ignore-table=mysql.% #Ignore all tables for the specified out of sync Library [root@mysql-m ~]# mysql -uroot -p -e "grant all privileges on *.* to testuser@localhost identified by '123456';" Enter password: [root@mysql-m ~]# mysql -uroot -p -e "select user,host from mysql.user;" Enter password: +-----------+--------------+ | user | host | +-----------+--------------+ | mysql.sys | localhost | | root | localhost | | test | localhost | | testuser | localhost | +-----------+--------------+ [root@mysql-s ~]# mysql -uroot -p -e "select user,host from mysql.user;" Enter password: +-----------+--------------+ | user | host | +-----------+--------------+ | mysql.sys | localhost | | root | localhost | | test | localhost | +-----------+--------------+
Conclusion: in this mode, the configuration does not synchronize the default database, and the parameters of related tables need to be added. Finally, the test results show that the configuration does not add the parameter replicate ignore DB = mysql, which is also effective for the default database,
Binlog format = statement mode
Modify binlog mode of master-slave database
Test non existing library, existing library and default library together
replicate-ignore-db = test #Ignore the specified unsynchronized Library (master and slave do not exist) replicate-ignore-db = test001 #Ignore specified out of sync Libraries replicate-wild-ignore-table=mysql.% #Ignore all tables for the specified out of sync Library
1: Test default database data synchronization
mysql> grant all privileges on *.* to user@'localhost' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> select user,host from mysql.user; +-----------+--------------+ | user | host | +-----------+--------------+ | rep | % | | mysql.sys | localhost | | root | localhost | | test | localhost | | user | localhost | +-----------+--------------+ 5 rows in set (0.01 sec) [root@mysql-s ~]# mysql -uroot -p -e "select user,host from mysql.user;" Enter password: +-----------+--------------+ | user | host | +-----------+--------------+ | rep | % | | mysql.sys | localhost | | root | localhost | | test | localhost | +-----------+--------------+
Conclusion: in this mode, for the default database, configure replicate ignore DB = mysql, which is effective for both the default database and the tables in the database
2: test the data synchronization when the master-slave server library has been synchronized (test001)
mysql> use test001; Database changed mysql> create table test001 ( -> id varchar(10) not null, -> name varchar(10) not null -> ); Query OK, 0 rows affected (0.06 sec) mysql> insert into test001 values('zhang','man'); Query OK, 1 row affected (0.11 sec) [root@mysql-m ~]# mysql -uroot -p -e "select * from test001.test001;" Enter password: +-------+------+ | id | name | +-------+------+ | zhang | man | +-------+------+ [root@mysql-s ~]# mysql -uroot -p -e "select * from test001.test001;" Enter password: ERROR 1146 (42S02) at line 1: Table 'test001.test001' doesn't exist
Click > The road of technology for migrant workers Pay attention to WeChat public number, dialog box reply Keywords: 1024 can get a latest 2048G technology dry goods: including system operation and maintenance, database, redis, MogoDB, e-book, Java basic course, Java actual combat project, architect comprehensive course, architect real battle project, big data, Docker container, ELK Stack, machine learning, BAT interview fine lecture video, etc.