作者：田逸（formyz）

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


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

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



要实现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-cnotallow=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 验证同步的正确性及状态。


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

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