mysql backup - liamlamth/blog GitHub Wiki

$local_backup_dir="D:\MySQL Backup"
$remote_backup_dir="S:\"
$db_name="LIAMAPI"
$email_to="[email protected]","[email protected]"
$email_from="scheduler@$(hostname).liam.local"
$email_smtp="192.168.1.170"
$backup_sqlf="fullbackup_${db_name}_$(Get-Date -UFormat "%Y%m%d").sql"
$backup_zip=$backup_sqlf.replace('.sql','.zip')
$log="D:\logs\backup_log\backup_${db_name}_$(Get-Date -UFormat "%Y%m%d").log"

Function Format-FileSize() {
Param ([string]$file)
$size = $((Get-Item $file).length)
If ($size -gt 1TB) {[string]::Format("{0:0.00} TB", $size / 1TB)}
ElseIf ($size -gt 1GB) {[string]::Format("{0:0.00} GB", $size / 1GB)}
ElseIf ($size -gt 1MB) {[string]::Format("{0:0.00} MB", $size / 1MB)}
ElseIf ($size -gt 1KB) {[string]::Format("{0:0.00} kB", $size / 1KB)}
ElseIf ($size -gt 0) {[string]::Format("{0:0.00} B", $size)}
Else {""}
}

$(
Write-Output "[ $(Get-Date -UFormat "%Y-%m-%d %R") ] backup process begin"
Set-Location $local_backup_dir

Write-Output "[ $(Get-Date -UFormat "%Y-%m-%d %R") ] dumping database pufa to sql file"
if (Test-Path -Path $backup_sqlf) {Remove-Item -Recurse -Force $backup_sqlf}
mysqldump -u root -proot pufa --single-transaction --quick > $backup_sqlf
#    --ignore-table=pufa.dunning_action --ignore-table=pufa.dunning_action2 --ignore-table=pufa.dunning_action20200921 --ignore-table=pufa.dunning_action20210416
    
if (-not $(Test-Path $backup_sqlf -PathType Leaf) -Or $(Get-Item $backup_sqlf).length -eq 0){
    Write-Output "[ $(Get-Date -UFormat "%Y-%m-%d %R") ] database export failed"
    Remove-Item -Recurse -Force "$backup_sqlf"
    Send-MailMessage -SMTPServer $email_smtp -From $email_from -To $email_to  -Subject "ERROR: ${db_name} backup failed - database export"
    exit
    }
$sqlf_size=$(Format-FileSize($backup_sqlf))

Write-Output "[ $(Get-Date -UFormat "%Y-%m-%d %R") ] compressing dump file"
if (Test-Path -Path $backup_zip) {Remove-Item -Recurse -Force $backup_zip}
& "C:\Program Files\7-Zip\7z.exe" a $backup_zip $backup_sqlf
Remove-Item -Recurse -Force $backup_sqlf
if (-not $(Test-Path $backup_zip -PathType Leaf) -Or $(Get-Item $backup_zip).length -eq 0){
    Write-Output "[ $(Get-Date -UFormat "%Y-%m-%d %R") ] dump compress failed"
    Remove-Item -Recurse -Force "$backup_zip"
    Send-MailMessage -SMTPServer $email_smtp -From $email_from -To $email_to  -Subject "ERROR: ${db_name} backup failed - dump compress"
    exit
    }
Write-Output "compressed size: ${sqlf_size} --> $(Format-FileSize($backup_zip))"

Write-Output "[ $(Get-Date -UFormat "%Y-%m-%d %R") ] transferring backup to NAS"
Move-Item -force $backup_zip $remote_backup_dir
Get-ChildItem $remote_backup_dir -Filter "fullbackup_${db_name}_*.zip"| Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-14))} | Remove-Item
if (-not $(Test-Path ${remote_backup_dir}\${backup_zip} -PathType Leaf) -Or $(Get-Item ${remote_backup_dir}\${backup_zip}).length -eq 0){
    Write-Output "[ $(Get-Date -UFormat "%Y-%m-%d %R") ] nas transfer failed"
    Remove-Item "${remote_backup_dir}\${backup_zip}"
    Send-MailMessage -SMTPServer $email_smtp -From $email_from -To $email_to  -Subject "ERROR: ${db_name} backup failed - nas transfer"
    exit
    }
Test-Path ${remote_backup_dir}\${backup_zip} -PathType Leaf
(Get-Item ${remote_backup_dir}\${backup_zip}).length

Write-Output "[ $(Get-Date -UFormat "%Y-%m-%d %R") ] backup process complete"
) 2>&1 > $log

Send-MailMessage -SMTPServer $email_smtp -From $email_from -To $email_to `
-Subject "Succeed: ${db_name} backup completed - $(Get-Date -UFormat "%Y%m%d")" -BodyAsHTML $(Get-Content ${log} | %{ "$_<br />" } | Out-String)