一、需求场景说明

  In general, we configuremysqlMaster-slave mode implementationmysqlRedundancy or high availability of the entire library,In practical application scenarios, there are also some tables that only need to be synchronized in a certain library.For example, a library contains hundreds of tables,The total library size is dozens or hundredsG,我们在A/BBuild a database master-slave between the two cities,A/BThe two data centers are interconnected through dedicated lines,带宽有限,部署在BCity applications actually only need to use part of the database table,In this case we can buildmysqlSome table owners have never implemented this requirement.After running for a period of time, some tables need to be added as the business changes,The overall structure is still a partial table master-slave mode.博文实验环境如下：

• 操作系统：centos7.6
• mysql版本：5.7.32
• mysql主：192.168.0.152
• mysql从：192.168.0.153

二、Some tables are built in master-slave scenarios

  The master-slave construction of some tables is similar to the general steps of the master-slave construction of the whole library,It is mainly reflected in the configuration of the slave library.

0、Master-slave configuration planning

  The master-slave synchronization database istestdb,testdb有10张表,Sync themtb_0001至tb_0005.

1、Installed separately on both hostsmysql

  mysqlThis blog post about app installation won't go into details,可以参考博文ShellScript one-click installationmysql.

2、在主库上创建testdb

  We start by creating one on the main repositorytestdb数据库实例,然后创建tb_0001-tb_0010十张表,This blog post focuses on simulation examplesmysqlSome tables are built in master-slave mode,The content of the data is not the point,We create by copying10张表.

mysql> create database testdb;
mysql> CREATE TABLE IF NOT EXISTS `tb_0001`(
    ->    `id` INT UNSIGNED AUTO_INCREMENT,
    ->    `title` VARCHAR(100) NOT NULL,
    ->    `author` VARCHAR(40) NOT NULL,
    ->    `submission_date` DATE,
    ->    PRIMARY KEY ( `id` )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.49 sec)
...

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| tb_0001          |
| tb_0002          |
| tb_0003          |
| tb_0004          |
| tb_0005          |
| tb_0006          |
| tb_0007          |
| tb_0008          |
| tb_0009          |
| tb_0010          |
+------------------+
10 rows in set (0.01 sec)

3、创建主从同步账户

mysql> create user [email protected]‘192.168.0.%’ identified by ‘Bak!1234’;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO [email protected]‘192.168.0.%’;
Query OK, 0 rows affected (0.07 sec)

4、修改主节点my.cnf配置

[[email protected] mysql]$ vim my.cnf
#Add the following section configuration to mysql的配置文件中,然后重启mysqld服务
#mysql-master config
server-id=152
log_bin=mysql-bin
log_slave_updates
binlog-do-db=testdb
binlog-ignore-db=mysql
binlog_format=MIXED
#Restart after configuration modification is completemysqld服务

4、备份主库testdb需要同步的表

  使用–single-transaction 可以保证在备份过程中,整个备份集的数据一致性.-master-data=2Parameters can be loggedbinlog日志POS位置,For master-slave configuration.

[[email protected] mysql]$ mysqldump --single-transaction --master-data=2 testdb tb_0001 tb_0002 tb_0003 tb_0004 tb_0005 -uroot -p -h 127.0.0.1 > /tmp/5.sql
Enter password:
[[email protected] mysql]$ mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB: testdb
Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)

5、从库导入testdb备份

#Copy the backup file on the master node to the slave node
[[email protected] mysql]$ scp /tmp/5.sql 192.168.0.153:/tmp/
[email protected]’s password:
5.sql
#Created from the librarytestdb并导入5Table backup data
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)

mysql> use testdb;
Database changed
mysql> source /tmp/5.sql;
Query OK, 0 rows affected (0.00 sec)
…
mysql> show tables;
±-----------------+
| Tables_in_testdb |
±-----------------+
| tb_0001 |
| tb_0002 |
| tb_0003 |
| tb_0004 |
| tb_0005 |
±-----------------+
5 rows in set (0.00 sec)

6、修改从节点my.cnf配置

[[email protected] mysql]$ vim my.cnf
#Add the following section configuration to mysql的配置文件中,然后重启mysqld服务
#mysql-slave config
server-id=153
log_bin=mysql-bin
log_slave_updates
binlog_format=MIXED
binlog-do-db=testdb
binlog-ignore-db=mysql
replicate-do-table=testdb.tb_0001
replicate-do-table=testdb.tb_0002
replicate-do-table=testdb.tb_0003
replicate-do-table=testdb.tb_0004
replicate-do-table=testdb.tb_0005
#修改完成后重启数据库

6、Configure the master-slave on the slave library

mysql> change master to
-> MASTER_HOST=‘192.168.0.152’,
-> MASTER_USER=‘bak’,
-> MASTER_PASSWORD=‘Bak!1234’,
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE=‘mysql-bin.000002’, MASTER_LOG_POS=154;
Query OK, 0 rows affected, 1 warning (0.84 sec)

7、开启主从复制

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

8、Check the replication status from the repository

9、Update data test validation

#Insert a piece of data on the main library
mysql> insert into tb_0001 (id,title,author,submission_date) values (2,“TCP/IP路由技术”,“Ge Jianwei,Wu Jianzhang”,“2002-06-30”);
Query OK, 1 row affected (0.10 sec)
#Check for data updates on the slave repository
mysql> select * from tb_0001;
±—±-------------------------------±----------------------±----------------+
| id | title | author | submission_date |
±—±-------------------------------±----------------------±----------------+
| 1 | 网络工程师的python之路 | 王印 | 2021-07-01 |
| 2 | TCP/IP路由技术 | Ge Jianwei,Wu Jianzhang | 2002-06-30 |
±—±-------------------------------±----------------------±----------------+
2 rows in set (0.00 sec)

三、Some tables are added in the middle of the master-slave mode

  In a partial table master-slave mode scenario,It may happen that the synchronized table needs to be updated after running for some time,比如新增一个表.This blog post was addedtb_0006The table is described as an example.

1、The main database locks the table

#The purpose of actively performing the lock table operation on the master library is to have time to perform the stop master-slave operation on the slave library.Actually the third step is used–single-transaction --master-data=2The parameter will perform the lock table operation.
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

2、Stop the master-slave on the slave library

mysql> stop slave;

3、Back up the table to be addedtb_0006

[[email protected] mysql]$ mysqldump --single-transaction --master-data=2 testdb tb_0006 -uroot -p -h 127.0.0.1 > /tmp/tb_0006.sql
Enter password:
[[email protected] mysql]$ ll /tmp/ |grep 0006
-rw-rw-r–. 1 wuhs wuhs 2234 7月 25 15:55 tb_0006.sql

4、Unlock the table on the main database

#Actually used in the previous operation–single-transaction --master-data=2参数,Unlocking the table is also performed after the backup is complete.
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

5、Import the backup table to the slave library

#Upload the backup file to the slave host
[[email protected] mysql]$ scp /tmp/tb_0006.sql 192.168.0.153:/tmp/
[email protected]’s password:
tb_0006.sql 100% 2234 1.2MB/s 00:00
#Import from librarytb_0006表
mysql> source /tmp/tb_0006.sql;
Query OK, 0 rows affected (0.00 sec)
…
mysql> select * from tb_0006;
±—±-------------------------------±----------------------±----------------+
| id | title | author | submission_date |
±—±-------------------------------±----------------------±----------------+
| 1 | 网络工程师的python之路 | 王印 | 2021-07-01 |
| 2 | TCP/IP路由技术 | Ge Jianwei,Wu Jianzhang | 2002-06-30 |
±—±-------------------------------±----------------------±----------------+
2 rows in set (0.00 sec)

6、修改从库my.cnf配置文件

#编辑从库my.cnf配置文件,The main library is the configuration for the entire library,不需要做任何修改.
[[email protected] ~]$ vim my.cnf
#Add the following line to the configuration file
replicate-do-table=testdb.tb_0006
#重启从库
[[email protected] ~]$ sh startmysql.sh

7、Clear the slave configuration

#Clear the slave configuration,If there are multiple slave library configurations, it needs to be specified
mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)

8、查看备份的binlog日志位置

[[email protected] ~]# cat /tmp/tb_0006.sql |grep MASTER_LOG_POS
– CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000002’, MASTER_LOG_POS=1911;

9、重新配置主从

mysql> change master to
-> MASTER_HOST=‘192.168.0.152’,
-> MASTER_USER=‘bak’,
-> MASTER_PASSWORD=‘Bak!1234’,
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE=‘mysql-bin.000002’, MASTER_LOG_POS=1911;
Query OK, 0 rows affected, 1 warning (0.43 sec)

10、在主库上插入数据

mysql> insert into tb_0006 (id,title,author,submission_date) values (4,“TCP/IP路由技术·卷三”,“Ge Jianwei,Wu Jianzhang”,“2002-06-30”);
Query OK, 1 row affected (0.13 sec)
mysql> insert into tb_0002 (id,title,author,submission_date) values (2,“TCP/IP路由技术”,“Ge Jianwei,Wu Jianzhang”,“2002-06-30”);

11、启动主从

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

11、查看主从状态

mysql> show slave status\G

12、检查数据

四、Database table update time check

  After completing the database backup,If we need to determine which datatables sent the update operation,我们可以通过查看information_schema库tablesThe update time of each table in ,Compare with the backup file time to know if there is an update after the backup is complete.

1、Check the database backup time

2、Query the latest update time of the database table

mysql> use information_schema;
mysql> select TABLE_SCHEMA,TABLE_NAME,UPDATE_TIME from TABLES where TABLE_SCHEMA=‘testdb’;
#testdbJust switch to the database you need to query.