1. 诉求提出
公司在阿里云有2个实例,业务需要跨实例定期联表查询得到统计数据在BI相关快速显示,阿里云的DTS同步费用较高,第三方工具又比较重量化。结合AI推荐的解决方案想到用MySQL官方的mysqldump程序,可以命令行执行。【代码得到,百度文心一言、阿里千义通问、ChatGPT 提示指导,在此感谢。】
2. 准备
2.1 准备Windows版的mysqldump工具,可以官网下载。
2.2 设置环境变量,确保cmd命令行在任意目录能运行mysqldum, 参考下图
3. Windows脚本文件.bat的编写
假设要同步多个表,建立表名列表文件,每个一行。
脚本代码特别注意包含特殊字符!的处理,不少AI推荐的代码是错误的。
@echo off
setlocal enabledelayedexpansion
set MYSQL_ENDPOINT1=db1.mysql.rds.aliyuncs.com
set MYSQL_ENDPOINT2=db2.mysql.rds.aliyuncs.com
set MYSQL_PORT=3306
set MYSQL_USERNAME1=db_syn
set MYSQL_PASSWORD1=ABCD^^!@
::注意密码包含了特殊字符!一定要前面加两个^。若密码是ABCD!@, 写成 variable=ABCD^^!@ 如果引号将整体括起来,一个^就行。 "variable=ABCD^!@"
set MYSQL_USERNAME2=db_syn2
set MYSQL_PASSWORD2=EFGH%%I
set MYSQL_DATABASE1=db_source_sync
set MYSQL_DATABASE2=db_destination_sync
set BACKUP_DIR=H:\Backup
set BACKUP_LOG_DIR=%BACKUP_DIR%\log
:: 设置数据库表列表文件,txt格式即可,每张表一行
set TABLE_LIST=%BACKUP_DIR%\TableList.txt
if not exist "%BACKUP_LOG_DIR%" mkdir "%BACKUP_LOG_DIR%"
:: 从需同步的数据库表列表文件读取表名,循环执行
for /f "delims=" %%k in (%TABLE_LIST%) do (
set "TABLE_NAME=%%k"
:: 获取当前日期时间,用于备份文件记录
for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
::动态更新变量值一定用!variable!引用
set CUR_DATETIME=!dt:~0,14!
:: 导出数据库表
mysqldump -h %MYSQL_ENDPOINT1% -P %MYSQL_PORT% -u %MYSQL_USERNAME1% -p!MYSQL_PASSWORD1! %MYSQL_DATABASE1% !TABLE_NAME! --skip-column-statistics --set-gtid-purged=OFF > "%BACKUP_DIR%\!TABLE_NAME!.sql"
if errorlevel 1 (
echo Failed to backup !TABLE_NAME!
goto :eof
) else (echo 成功备份表!TABLE_NAME!)
:: 复制到备份文件夹,保留历史记录
if not exist "%BACKUP_LOG_DIR%" mkdir "%BACKUP_LOG_DIR%"
copy "%BACKUP_DIR%\!TABLE_NAME!.sql" "%BACKUP_LOG_DIR%\!TABLE_NAME!_!CUR_DATETIME!.sql"
echo %CUR_DATETIME%
:: 目标数据库删除表
mysql -h %MYSQL_ENDPOINT2% -P %MYSQL_PORT% -u %MYSQL_USERNAME2% -p!MYSQL_PASSWORD2! -e "USE %MYSQL_DATABASE2%; DROP TABLE IF EXISTS `!TABLE_NAME!`;"
:: 向新实例同步表
mysql -h %MYSQL_ENDPOINT2% -P %MYSQL_PORT% -u %MYSQL_USERNAME2% -p!MYSQL_PASSWORD2! %MYSQL_DATABASE2% < "%BACKUP_DIR%\!TABLE_NAME!.sql"
if errorlevel 1 (
echo Failed to import !TABLE_NAME!
goto :eof
) else (echo 成功同步表!TABLE_NAME!)
)
endlocal
将脚本文件保存为bat扩展名文件(如sync.bat),编码选ANSI或UTF-8。
4. 执行计划任务
Windows执行计划任务。搜索计划任务,制定计划任务,定期重复运行。