powershell脚本, 备份和还原mysql数据库

需要一个模块Out-FileUtf8NoBom
在这里https://www.pstips.net/out-fileutf8nobom.html
把该模块命名为Out-FileUtf8NoBom.psm1放到$home\Documents\WindowsPowerShell\Modules\Out-FileUtf8NoBom\目录
查看下面脚本的注释, 要修改一些变量.
使用很简单.
backup-restore-mysql backup all 或者backup-restore-mysql backup 会备份所有数据库.(可以指定要排除的, 在脚本中修改)
所有数据库sql会被dump到以当前时间日期命名的文件夹.
backup-restore-mysql backup part 只会被选定的数据库, 会弹出一个选择框让你选择
backup-restore-mysql restore all 或者backup-restore-mysql restore 会弹出一个框框, 让你选一个时间日期命名的备份文件夹, 然后恢复里面所有的sql
backup-restore-mysql restore part 会弹出一个框框, 让你选一个时间日期命名的备份文件夹, 然后再选择要单独恢复的sql.

backup-restore-mysql.ps1

[CmdletBinding(DefaultParameterSetName = 'backup')]
param
(
    [Parameter(Mandatory = $true, ValueFromPipeline = $true, Position = 0)]
    [ValidateSet('backup', 'restore')]
    [String]$operation,
    [Parameter(ValueFromPipeline = $true, Position = 1)]
    [ValidateSet('all', 'part')]
    [String]$optparam = 'all'
    
)

#$JsonPath = "$home\Documents\WindowsPowerShell\private.json"
#$Json = ConvertFrom-Json((Get-Content $JsonPath) | out-string)
#$password = $Json.'mysql-password'
#$passwordParam = "-p$password"
# 直接把上面的"-p$password"中的变量改成你的密码

$mysqlPath = "C:\bin\phpwamp\phpwamp\server\mysql-5.7\bin"
# 改成你的mysql程序bin目录
$date = Get-Date -UFormat "%Y-%m-%d-%H-%M-%S"
$backupDir = "$home\Documents\OneDrive\archives\mysql-local-backup"
# 改成你的备份的位置
switch ($operation) {
    'backup' {
        $backupSubDir = "$backupDir\$date"
        if (!(Test-Path $backupSubDir)) {
            New-Item -ItemType Directory -Path $backupSubDir -Force | Out-Null
        }
        
        $allDatabases = Invoke-Expression "$mysqlPath\mysql.exe -e 'show databases;' -u root $passwordParam"
        $excludeDatabases = 'Database', 'information_schema', 'mysql', 'information_schema', 'performance_schema', 'sys', 'phpwamp'
        #改成你要排除的不要备份的数据库
        $includeDatabases = $allDatabases | Where-Object { $excludeDatabases -notcontains $_ }
        
        if ($includeDatabases -eq $null) {
            Write-Output "No database needs to backup."
            exit
        }
        
        if ($optparam -eq 'part') {
            $includeDatabases = @($includeDatabases | Out-GridView -Title 'Select database need to backup.' -PassThru)
        }
        
        [System.Console]::OutputEncoding = [System.Text.Encoding]::UTF8
        foreach ($db in $includeDatabases) {
            $backupFilePath = "$backupSubDir\$db.sql"
            Invoke-Expression "$mysqlPath\mysqldump.exe -u root $passwordParam --add-drop-database --databases --routines --events --triggers $db | Out-File $backupFilePath"
            Get-Content $backupFilePath | Out-FileUtf8NoBom $backupFilePath -Force
        }
    }
    'restore' {
        $subDirList = Get-ChildItem $backupDir
        if ($subDirList -ne $null) {
            $selectedSubDir = @($subDirList | Sort-Object -Property LastWriteTime -Descending | Out-GridView -Title 'Select a restore point.' -PassThru)
            
            if ($selectedSubDir -ne $null) {
                $backupFileList = Get-ChildItem $selectedSubDir.FullName
                
                if ($optparam -eq 'part') {
                    $backupFileList = @($backupFileList | Out-GridView -Title 'Select backups need to restore.' -PassThru)
                }
                
                foreach ($backupFile in $backupFileList) {
                    Invoke-Expression "$mysqlPath\mysql.exe -e ""source $($backupFile.FullName)"" -u root $passwordParam"
                }
            }
        }
        else {
            Write-Output "Please backup first."
        }
    }
}