用通配符批量同步MySQL部分数据表

作者：田逸（formyz）

接到一个任务，需要对一个数据量比较大的MySQL数据库的部分数据表做主从复制，其中有51个表的表明前缀相同（如下图所示），还有几个没有规律的表也需要一起同步。

要实现MySQL主从同步，需要先导出一份源数据库的备份。可用的方法有两种：mysqldump 与 xtrabackup。

在确定源数据库的版本后（5.6.50），目标系统安装好与之匹配的MySQL版本（不高于MySQL 8），设置好选项文件/etc/my.cnf(如下表所列)，初始化数据库以后，确保从数据库MySQL服务可正常启动。

root@k8s-165:~# more /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysqld] datadir=/data1/mysql_db socket=/tmp/mysql.sock log-error=/data1/mysql_db/error.log pid-file=/data1/mysql_db/mysqld.pid #skip-grant-tables slave-skip-errors=1236,1032,1062 skip-name_resolve max-connect-errors=100000 max-connections=500 # need for slave server-id = 165 binlog-format = MIXED gtid-mode = on enforce-gtid-consistency = true log-bin = mysql-bin relay-log = mysql-relay-bin log-slave-updates=1 replicate-wild-do-table = bianjicms.www_92formyz_net_ikanchai_recordbxc_* replicate-do-table = bianjicms.www_92formyz_net_ikanchai_smscode replicate-do-table = bianjicms.www_92formyz_net_ikanchai_levelgrowth replicate-do-table = bianjicms.www_92formyz_net_ikanchai_levelgrowthlog replicate-do-table = bianjicms.www_92formyz_net_ikanchai_member replicate-do-table = bianjicms.www_92formyz_net_ikanchai_wallet replicate-do-table = bianjicms.www_92formyz_net_ikanchai_bi replicate-do-table = bianjicms.www_92formyz_net_ikanchai_biconfig replicate-do-table = bianjicms.www_92formyz_net_ikanchai_rmb_record innodb_checksum_algorithm=innodb innodb_data_file_path=ibdata1:200M:autoextend innodb_log_files_in_group=2 innodb_log_buffer_size=8388608 innodb_page_size=16384 innodb_undo_directory=. innodb_undo_tablespaces=0 innodb_buffer_pool_size=50G innodb_log_file_size=2G innodb_flush_method=O_DIRECT innodb_io_capacity=2000 innodb_io_capacity_max=6000 innodb_lru_scan_depth=2000

请注意，MySQL选项文件my.cnf支持通配符。这样节省了大量的手工输入，也降低了出错几率！！！

试图尝试主库（源数据库）使用工具mysqldump导出这些需要同步的数据表，但其本身不支持直接使用通配符，需要用间接的办法实现，又加上其它几个没有命名规律的数据表，用这个工具导出有一定的难度，只能放弃，而选用xtrabackup。

xtrabackup直接支持通配符，其选项形式为“--include=”。再结合选项“--tables-file”,把其余没有规律的数据表写到一个文本文件，xtrabackup带这两个选项就可以省事不少。手动创建文本文件tables.txt，其完整内容如下：

bianjicms.www_92formyz_net_ikanchai_smscode bianjicms.www_92formyz_net_ikanchai_levelgrowth bianjicms.www_92formyz_net_ikanchai_levelgrowthlog bianjicms.www_92formyz_net_ikanchai_member bianjicms.www_92formyz_net_ikanchai_wallet bianjicms.www_92formyz_net_ikanchai_bi bianjicms.www_92formyz_net_ikanchai_biconfig bianjicms.www_92formyz_net_ikanchai_rmb_record

其中“bianjicms”为数据库名。

在保证主数据库所在系统有足够的存储空间以后，执行如下指令将指定的数据库表导出到目录“/mnt/backup”。

innobackupex --tables-file=/root/tables.txt --include=bianjicms/www_92formyz_net_ikanchai_recordbxc* --user=root --password=H37%s6Pl2 /mnt/backup

 

如果一切顺利，执行完毕后会有“221006 13:57:20 completed OK!”类似这样的输出。继续在主库所在的系统执行下列指令为恢复数据准备。

xtrabackup --prepare --target-dir=/mnt/backup/2022-10-06_12-47-00

一切准备妥当以后，将数据库备份目录以NFS的方式共享给数据库从库（目标库）所在的系统，省掉数据复制这个步骤，从而节省时间。

备库系统也安装好与主库相一致的xtrabackup软件，并将已经初始化生成数据文件的目录清理干净，本案的数据目录为 /data1/mysql_db，如果不清空，xtrabckup恢复数据库时将不能正常执行。清理完从库数据库目录的文件后，执行如下指令进行备份文件的恢复。

xtrabackup --copy-back --target-dir=/mnt/backup/2022-10-06_12-47-00 --datadir=/data1/mysql_db

数据恢复完毕后，启动从数据库。启动可能失败，提示没有系统表等错误。造成这个错误的原因是由于没有把主库的mysql等系统库级数据表同步过来所致。解决办法就是再执行一次MySQL数据库初始化操作，这个操作不会对已经用xtrabackup恢复的数据产生任何影响，可大胆操作。

再进行从数据库MySQL服务启动，就应该正常了。登录从数据库MySQL客户端，执行如下指令进行数据库主从同步。

mysql > change master to master_host='172.16.88.123',master_user='root',master_password=' H37%s6Pl2”; mysql > slave start;

因为主库与从库都启用了GTID，因此无需再输入主库二进制日志文件名（master_log_file）以及日志偏移量(master_log_pos)。

 

 

继续在mysql客户端执行指令 show slave status\G 验证同步的正确性及状态。