Mysql之部分表主从搭建及新增表

一、需求场景说明
  一般情况下我们配置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。
————————————————

3、创建主从同步账户
mysql> create user bak@‘192.168.0.%’ identified by ‘Bak!1234’;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO bak@‘192.168.0.%’;
Query OK, 0 rows affected (0.07 sec)

4、修改主节点my.cnf配置
[wuhs@s152 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位置，用于主从配置。

[wuhs@s152 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:
[wuhs@s152 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备份
#主节点上将备份文件拷贝到从节点
[wuhs@s152 mysql]$ scp /tmp/5.sql 192.168.0.153:/tmp/
wuhs@192.168.0.153’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配置
[wuhs@s153 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)
————————————————

三、部分表主从模式中途新增表
  在部分表主从模式场景中，可能会出现运行一段时间后同步的表需要更新，比如新增一个表。此博文以新增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
[wuhs@s152 mysql]$ mysqldump --single-transaction --master-data=2 testdb tb_0006 -uroot -p -h 127.0.0.1 > /tmp/tb_0006.sql
Enter password:
[wuhs@s152 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、将备份表导入到从库
#将备份文件上传到从库主机
[wuhs@s152 mysql]$ scp /tmp/tb_0006.sql 192.168.0.153:/tmp/
wuhs@192.168.0.153’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配置文件，主库因为是针对整库的配置，不需要做任何修改。
[wuhs@s153 ~]$ vim my.cnf
#在配置文件中加入如下行
replicate-do-table=testdb.tb_0006
#重启从库
[wuhs@s153 ~]$ sh startmysql.sh

7、清空从库配置
#清空从库配置，如果是有多个从库配置则需要指定
mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)

8、查看备份的binlog日志位置
[root@s153 ~]# 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)
————————————————