mysqldump是支持-h参数的,所以可以远程连接数据库,并备份。
备份的.sql文件如果比较大,可以考虑压缩一下节省空间。去下载个7zip,放到db_backup.bat当前目录下。
mysqldump.exe 是windows版mysql\bin目录下的,直接拿过来用,不依赖其他东西。
可以搞个计划任务,每天后半夜运行一次db_backup.bat,将远程数据库备份,并压缩保存。文件名后缀为当前日期,相当方便。
db_backup.bat:
set PATH=.\7z1604-x64\
set a=%date:~0,4%%date:~5,2%%date:~8,2%
echo %a%
echo "dump start..."
mysqldump -h192.168.1.11-uroot -prootpass db_name1 > db1.sql
mysqldump -h192.168.2.22-uroot -prootpass db_name2 > db2.sql
mysqldump -h192.168.3.33-uroot -prootpass db_name3 > db3.sql
7z a dbbackup_%a%.7z .\*.sql
del db1.sql
del db2.sql
del db3.sql
echo "dump end."
Linux:
#!/bin/bash
# crontab -e
# 0 1 * * * /db_backup/backup_docker_data.sh
# delete files backuped 180days ago!
#timestamp=$(date +%Y%m%d_%H%M%S)
timestamp=$(date +%Y%m%d)
timestamp_old=`date -d -180day +%Y%m%d`
echo "=============$timestamp mysqldump start...============="
cd /db_backup/data/
mkdir $timestamp
./mysqldump -h192.168.100.100 -P3306 -uroot -prootpassword dbname > $timestamp/db_$timestamp.sql
tar zcvf $timestamp.tar.gz $timestamp
rm -fr $timestamp
rm -fr $timestamp_old*.tar.gz
echo "=============$timestamp mysqldump end. ============="
echo "$timestamp backup data OK!" >> backup.log