sql一键全量备份脚本
1、linux--linux .sql一键全量备份脚本
备注:脚本,为了安全考虑SCP时,2台计算机之间做个免密登录吧
修改相应的数据。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/bin/bash #mysql 备份XXX数据库脚本 #保留最近10天备份 #备份目录 backupDir=/home/backup/database #mysqlDump mysqldump=mysqldump #ip host=IP地址 #用户名 username=root #密码 password=密码 #今天日期 today=`date +%Y%m%d` #十backupDir=天前的日期 timeTenDayAgo=`date -d -10day +%Y%m%d` #备份的数据库 database=库名 #如果文件夹不存在则创建 if [ ! -d $backupDir ]; then mkdir -p $backupDir; fi echo '开始备份'$database $mysqldump -h$host -u$username -p$password $database | gzip > $backupDir/$database-$today.sql.gz echo '成功备份'$database'到'$backupDir/$database-$today.sql.gz if [ ! -f "$backupDir/$database-$timeTenDayAgo.sql.gz" ]; then echo '10天前备份不存在,无需删除' else rm -f $backupDir/$database-$timeTenDayAgo.sql.gz echo '删除10天前备份文件'$backupDir/$database-$timeTenDayAgo.sql.gz fi scp /home/backup/database/$timeTenDayAgo.sql.gz root@192.168.170.128:/mysql_bak/ yes 123456
二、windows--linux一键全量备份脚本
(1)安装7zip 并加入环境变量
@echo off set hour=%time:~0,2% if "%time:~0,1%"==" " set hour=0%time:~1,1% set now=%Date:~0,4%%Date:~5,2%%Date:~8,2%%hour%%Time:~3,2%%Time:~6,2% echo %now% set host=127.0.0.1 set port=3306 set user=root set pass=123456 set backupfile="D:\backup\dbbackup_day\%date:~0,4%%date:~5,2%%date:~8,2%\ md %backupfile% for %%i in (test,sys,mysql) do ( "E:\mysql-5.7.34-winx64\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %%i > %backupfile%%%i-%now%.sql echo delete files before 10 days forfiles /p D:\backup\dbbackup_day /s /m *.sql /d -10 /c "cmd /c del @file /f" ) 7z a -tzip D:\backup\dbbackup_day\%date:~0,4%%date:~5,2%%date:~8,2%.zip D:\backup\dbbackup_day\%date:~0,4%%date:~5,2%%date:~8,2%\*.sql rd /s/q %backupfile%
###################删除#####################
我测试时,发现传输过去的文件名字 不对
就再这里做了个切换目录然后,拷贝的时候删掉了目录
D:
cd
D:\backup\dbbackup_day
scp %date:~0,4%%date:~5,2%%date:~8,2%.zip root@192.168.170.128:/mysql_bak/
###################删除####################
###scp D:\backup\dbbackup_day\%date:~0,4%%date:~5,2%%date:~8,2%.zip root@192.168.170.128:/mysql_bak/
需要修改:数据库的地址:E:\mysql-5.7.34-winx64\bin\
自行创建:D:\backup\dbbackup_day\ 目标/mysql_bak
替换自己所要备份的数据库 for %%i in (test,sys,mysql)
人生苦短,我用Python