windows脚本_【脚本分享】 Windows下利用Powershell进行MySQL数据库备份

环境准备

测试环境: Server 2008 R2


所需软件:

 - MySQLdump.exe  
 - msvcp120.dll  x64/x86
 - msvcr120.dll  x64/x86
 - powershell
 - 7z

设置PowerShell策略:

PS C:\Users\Administrator> Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope LocalMachine

执行策略更改
执行策略可以防止您执行不信任的脚本。更改执行策略可能会使您面临 about_Execution_Policies
帮助主题中所述的安全风险。是否要更改执行策略?
[Y] 是(Y)  [N] 否(N)  [S] 挂起(S)  [?] 帮助 (默认值为“Y”): Y

注册 mysqldump 依赖的dll

regsvr32 msvcr120.dll
regsvr32 msvcp120.dll

数据库相关信息查看:

# 1.版本
SELECT @@version;

# 2.指定数据库的表与表数据行统计查看
select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = 'authcenter' order by table_rows desc;

备份脚本:

# ------------------------------- ## Author:WeiyiGeek                ## PS: 数据库备份 & 应用备份         ## Create: 2020年6月11日 21:34:40   ## ------------------------------- #    # ----------------------------------------------------------------- ## 全局变量$GD=Get-Date$TIME=$GD.ToString('yyyy-MM-dd_HHmmss')$DATE=$GD.ToString('yyyy-MM-dd')$global:BACKUP_SQLFILE = ""# MySQL 备份程序$MYSQL_DUMP="D:\mysql\bin\mysqldump.exe"# 压缩SQL文件$7Zfile = "C:\7-Zip\7z.exe"$7ZPASS = "WwW.WeiyiGeek.top"# 指定备份文件存放目录$BACKUP_DIR = "D:/mysql/SQL-${DATE}"$BACKUP_7ZDIR = "D:/mysql/7Z-${DATE}"# FTP 上传$FTP_IP = "192.168.12.31"$FTP_PORT = "21212"$FTP_USER = "User"$FTP_PASS = "Pass"# ----------------------------------------------------------------- ## 验证备份文件夹是否创建$FLAG=Test-Path -Path "$BACKUP_DIR"if (!$FLAG ){#New-Item -ItemType Directory -Path $BACKUP_DIR/ -Force
  mkdir "$BACKUP_DIR"} # MySQL数据库备份链接function dumpMysql {param ([string] $APP_HOST="",[string] $APP_DBNAME="",[string] $APP_DBU="",[string] $APP_DBP="",[int] $APP_PORT=3306)if([String]::IsNullOrEmpty($APP_HOST) -or [String]::IsNullOrEmpty($APP_DBNAME) -or [String]::IsNullOrEmpty($APP_DBU) -or [String]::IsNullOrEmpty($APP_DBP)){Write-Host "# 备份 $APP_DBNAME 数据库错误 "  -ForegroundColor red[Environment]::Exit(127)} else {Write-Host "# 正在备份 $APP_DBNAME 数据库 "  -ForegroundColor Green$global:BACKUP_SQLFILE = "${APP_DBNAME}_${TIME}.sql"Invoke-Expression "${MYSQL_DUMP} -h $APP_HOST -P $APP_PORT --default-character-set=UTF8 -u$APP_DBU -p$APP_DBP -B --databases $APP_DBNAME --hex-blob --result-file=$BACKUP_DIR/$BACKUP_SQLFILE"}}# 7Z 压缩SQL文件function Compress7Z {# 判断压缩备份目录是否存在$flag = Test-Path -Path "${BACKUP_7ZDIR}"if (!$flag){Write-Host "正在建立 $Dstdir 备份目录 " -ForegroundColor GreenNew-Item -ItemType Directory -Path $BACKUP_7ZDIR -Force}Start-Process -FilePath $7Zfile -ArgumentList "a -p${7ZPASS} ${BACKUP_7ZDIR}\${BACKUP_SQLFILE}.7z $BACKUP_DIR\*_${TIME}.sql" -WindowStyle hidden}# 备份文件上传方式function uploadBack {param ([string] $FLAG="")# ftp 方式if ( $FLAG -eq "ftp" ){"open ${FTP_IP} ${FTP_PORT}" | Out-File ftp.tmp"${FTP_USER}" | Out-File -Append ftp.tmp"${FTP_PASS}" | Out-File -Append ftp.tmp"bin" | Out-File -Append ftp.tmp"mkdir ${DATE}" | Out-File -Append ftp.tmp"cd ${DATE}" | Out-File -Append ftp.tmp"put ${BACKUP_7ZDIR}\${BACKUP_SQLFILE}.7z" | Out-File -Append ftp.tmp"quit" | Out-File -Append ftp.tmpStart-Process ftp -ArgumentList "-i -s:ftp.tmp"Remove-Item -Path ftp.tmp}}# 调用MysqlDump函数执行下载(指定数据库拉取)
dumpMysql -APP_HOST 127.0.0.1 -APP_PORT 8066 -APP_DBNAME "AuthCenterschema" -APP_DBU "root" -APP_DBP "123456"# 调用压缩
Compress7Z# 调用上传
uploadBack -FLAG "ftp"#Write-Host "# 正在输出备份数据库路径: $BACKUP_DIR"  -ForegroundColor Green#Get-ChildItem $BACKUP_DIR\*.sql #Write-Host "# 正在输出压缩文件备份数据库路径: ${BACKUP_7ZDIR}"  -ForegroundColor Green#Get-ChildItem ${BACKUP_7ZDIR}\*.7zexit

定时任务设置:

# 每周5的一点进行备份同步(不管用户是否登陆)
schtasks /create /TN mysql-backup  /SC WEEKLY  /D FRI /ST 01:00 /TR "powershell.exe d:\mysql\backup.ps1" /NP

备份测试：