MariaDB: 如何定期将本地数据库视图数据同步追加到远程数据库

报错原因 #

你当前执行命令报错的核心原因是：mysqldump默认导出视图对象时，输出的是视图的创建定义语句，而非视图的查询结果数据。导入到目标库时，创建视图需要依赖的底层基表table1在目标库destinationdb不存在，因此触发ERROR 1146 (42S02)错误。

实现方案 #

你的需求完全可以实现，根据目标表是否已创建选择对应方案即可：

方案1：首次同步，自动创建目标表+同步数据 #

适合目标库还没有对应存储表的场景，分两步执行：

1. 基于源库视图结构在目标库创建实体表

# 从源库视图生成建表语句，直接导入目标库创建对应实体表
mysql -uroot -p你的源库密码 sourcedb -e "CREATE TEMPORARY TABLE tmp_struct AS SELECT * FROM sourceview LIMIT 0; SHOW CREATE TABLE tmp_struct \G" | grep -A1 "Create Table:" | tail -n1 | sed 's/`tmp_struct`/`你的目标表名`/g' | mysql -P3306 -uroot -p你的目标库密码 destinationdb

2. 同步视图所有数据到目标表

# 导出源库视图的查询结果，生成INSERT语句导入目标库
mysql -uroot -p你的源库密码 sourcedb -e "SELECT * FROM sourceview" --batch --skip-column-names --quote-names | awk -F"\t" '{for(i=1;i<=NF;i++){if($i=="NULL"){q="";}else{q="\047";}; printf "%s%s%s",q,$i,q,(i<NF?",":"");}; print ");"}' | sed 's/^/INSERT INTO 你的目标表名 VALUES(/' | mysql -P3306 -uroot -p你的目标库密码 destinationdb

方案2：目标表已存在，仅追加数据 #

如果目标库的存储表已经提前创建完成，直接用以下命令即可实现数据追加：

mysqldump -uroot -p你的源库密码 sourcedb --no-create-info --skip-triggers --tables sourceview | mysql -P3306 -uroot -p你的目标库密码 destinationdb

这里加--no-create-info参数会让mysqldump跳过视图定义导出，仅导出视图的查询结果数据，避免触发基表不存在的错误。

定期同步配置 #

要实现定期自动执行，把上述对应场景的命令写入shell脚本，配置Linux crontab定时任务即可，比如每天凌晨2点执行同步，在crontab中添加如下规则即可：

0 2 * * * /bin/bash /path/to/你的同步脚本.sh > /var/log/mysql_sync.log 2>&1

内容的提问来源于stack exchange，提问作者Nicklas Nielsen