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

转载：https://blog.51cto.com/sery/5733937 

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



用通配符批量同步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