一、需求场景说明

  一般情况下我们配置mysql主从模式实现mysql整库的冗余或者高可用，实际应用场景中还会有只需要同步某库部分表。例如某库含有上百个表，总库大小几十上百G，我们在A/B两城之间搭建数据库主从，A/B两个数据中心之间是通过专线互联，带宽有限，部署在B城的应用实际上都只需要使用数据库的部分表，这种情况下我们就可以搭建mysql部分表主从来实现该需求。运行一段时间后随着业务的变化又需要新增部分表，总体结构还是部分表主从模式。博文实验环境如下：

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

二、部分表主从场景搭建

  部分表主从搭建跟全库主从搭建大体步骤差不多，主要体现在从库的配置上。

0、主从配置规划

  主从同步的数据库是testdb，testdb有10张表，同步其中的tb_0001至tb_0005。

1、两台主机上分别安装mysql

  mysql应用安装此博文不再赘述，可以参考博文Shell脚本之一键安装mysql。

2、在主库上创建testdb

  我们首先在主库上创建一个testdb数据库实例，然后创建tb_0001-tb_0010十张表，此博文重点是模拟示例mysql部分表主从模式搭建，数据内容不是重点，我们采用复制的方式创建10张表。

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
#添加如下部分配置到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
#配置修改完成后重启mysqld服务

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

  使用–single-transaction 可以保证在备份过程中，整个备份集的数据一致性。-master-data=2参数可以记录binlog日志POS位置，用于主从配置。

[[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备份

#主节点上将备份文件拷贝到从节点
[[email protected] mysql]$ scp /tmp/5.sql 192.168.0.153:/tmp/
[email protected]’s password:
5.sql
#从库上创建testdb并导入5张表备份数据
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
#添加如下部分配置到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、在从库上配置主从

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、检查从库复制状态

9、更新数据测试验证

#在主库上插入一条数据
mysql> insert into tb_0001 (id,title,author,submission_date) values (2,“TCP/IP路由技术”,“葛建伟，吴剑章”,“2002-06-30”);
Query OK, 1 row affected (0.10 sec)
#在从库上检查数据更新
mysql> select * from tb_0001;
±—±-------------------------------±----------------------±----------------+
| id | title | author | submission_date |
±—±-------------------------------±----------------------±----------------+
| 1 | 网络工程师的python之路 | 王印 | 2021-07-01 |
| 2 | TCP/IP路由技术 | 葛建伟，吴剑章 | 2002-06-30 |
±—±-------------------------------±----------------------±----------------+
2 rows in set (0.00 sec)

三、部分表主从模式中途新增表

  在部分表主从模式场景中，可能会出现运行一段时间后同步的表需要更新，比如新增一个表。此博文以新增tb_0006表为例介绍说明。

1、主库上锁表

#在主库上主动执行锁表操作是为了有时间在从库上执行停止主从操作。实际上第三步使用–single-transaction --master-data=2参数会执行锁表操作。
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

2、在从库上停止主从

mysql> stop slave;

3、备份待新增表tb_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、主库上解除锁表

#实际上在上一步操作中使用了–single-transaction --master-data=2参数，在备份完成后也会执行解除锁表的操作。
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

5、将备份表导入到从库

#将备份文件上传到从库主机
[[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
#在从库上导入tb_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路由技术 | 葛建伟，吴剑章 | 2002-06-30 |
±—±-------------------------------±----------------------±----------------+
2 rows in set (0.00 sec)

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

#编辑从库my.cnf配置文件，主库因为是针对整库的配置，不需要做任何修改。
[[email protected] ~]$ vim my.cnf
#在配置文件中加入如下行
replicate-do-table=testdb.tb_0006
#重启从库
[[email protected] ~]$ sh startmysql.sh

7、清空从库配置

#清空从库配置，如果是有多个从库配置则需要指定
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路由技术·卷三”,“葛建伟，吴剑章”,“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路由技术”,“葛建伟，吴剑章”,“2002-06-30”);

11、启动主从

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

11、查看主从状态

mysql> show slave status\G

12、检查数据

四、数据库表更新时间检查

  在完成数据库备份后，如果我们需要确定哪些数据表发送了更新操作，我们可以通过查看information_schema库tables中各表的更新时间，与备份文件时间进行对比就知道在备份完成后是否有更新。

1、查看数据库备份时间

2、查询数据库表的最新更新时间

mysql> use information_schema;
mysql> select TABLE_SCHEMA,TABLE_NAME,UPDATE_TIME from TABLES where TABLE_SCHEMA=‘testdb’;
#testdb切换为你需要查询的数据库即可。