MySQLCommunityDbBackup.ps1

# All Rights Reserved, 2018
# v2.0.20181210
#
# Description:
#   Powershell Script to generate database backups for MySQL Server (Any Edition) Installation.
#
# Credits:
#   December 10, 2018: Edgar Gerardo Chinchilla Mazate, https://www.facebook.com/edgargerardo.chinchillamazate
#   Changes:
#       - Made the script params-ready to make it more dynamic and flexible
#       - Added option to prompt-for and store database credentials in xml-encrypted format
#       - Automatic search for common MySQL Versions installation
#       - Added a feature to automatically delete backups older than X days
#       - Verbose
#   August 17, 2011: Milind R Chavan, https://www.codeproject.com/Tips/234492/MySQL-DB-backup-using-powershell-script
#   Changes:
#       - Initial Version
# Parameters:
#   $daysToPreserveBackups:
#                       The max-age days quantity to preserve backups in the
#                       destination path. Null will result in asuming 180 days.
#   $MySqlServer:
#                       The MySQL Server name or IP to connect to. Null will
#                       result in an error exception.
#   $backupDestination:
#                       The desired backup destination path. Null will
#                       result in default (MyDocuments) backup path being used.
#   $databasesToBackup:
#                       The (comma separated) name of the databases to backup.
#                       Null will resul in the backup of all the registered dbs.
#   $promptForCreds:
#                       Specifies if the script will prompt the user to enter
#                       SQL credentials (to create or update them to execute
#                       the script silently later). Default is true.
#   $credsDestination:
#                       Specifies where the encrypted credentials will be stored.
#                       Null will result in the use of "ProgramData" folder.
# Excecution example:
#   .\MySQLCommunityDbBackup.ps1
#       <optional> -daysToPreserveBackups 180
#       -MySqlServer "192.168.0.100"
#       <optional> -backupDestination "C:\BKxyz\"
#       <optional> -databasesToBackup "DBx,DBy,DBz"
#       <optional> -promptForCreds 0
#       <optional> -credsDestination "C:\Config\xyz\"

param (
    # WARNING: This value especifies the time Interval to keep backups.
    [Int32]$daysToPreserveBackups=180,
    [String]$MySqlServer=$(Throw "Please specify a MySQL Server Name or IP."),
    [String]$backupDestination=[Environment]::GetFolderPath('MyDocuments')+"\MySQL Backups",
    [String]$databasesToBackup=$null,
    [Bool]$promptForCreds=$true,
    [String]$credsDestination="$env:ProgramData"+"\PS Backup Task"
);

# TIP: Show all env vars with
#   gci env:* | sort-object name

# Function to handle any errors that occurs in the script
Function Error_Handler {
    Write-Host (" Error Category: " + $error[0].CategoryInfo.Category);
    Write-Host (" Error Object:   " + $error[0].TargetObject);
    Write-Host (" Error Message:  " + $error[0].Exception.Message);
    Write-Host (" Error FQEId:    " + $error[0].FullyQualifiedErrorId);
};

# Register the script error handler
Trap {
    # Handle the error
    Error_Handler;
    # End the script.
    Break;
};

# Define the credentials file name from a "Sanitized" $MySqlServer
# Sanitize the string using Regular Expression - Unicode - Unicode Categories
$credsFileName = $($MySqlServer -replace '[^\p{L}\p{Nd}]', '');
$credsFileName += ".xml";

# Check if the paths has "\" at the end of string, if not, add it
If (-Not $backupDestination.EndsWith("\")) { $backupDestination += "\"; };
If (-Not $credsDestination.EndsWith("\")) { $credsDestination += "\"; };

# Check if the specified backup path exists, if not try to create it, if can't, throw a new error
If ($backupDestination) {
    $pathValidation = Test-Path($backupDestination);
    If (-Not $pathValidation) {
        $pathValidation = $null;
        MD -Force $backupDestination -ErrorAction SilentlyContinue | Out-Null;
        $pathValidation = Test-Path($backupDestination);
        If (-Not $pathValidation) {
            Throw [System.IO.FileNotFoundException] "The specified $backupDestination backup path is not found or can't be created.";
            Exit 1;
        };
        $pathValidation = $null;
    };
};

# Get/Prompt/Store User credentials
# Verify (and create if not exists) creds destination directory
$pathValidation = Test-Path($credsDestination);
If (-Not $pathValidation) {
    MD -Force $credsDestination -ErrorAction SilentlyContinue | Out-Null;
};
$pathValidation = $null;

# Clear screen (supposing that some path was created in the previous steps)
Clear-Host;
# Check if the user wants to promp for creds (to create them)
If ($promptForCreds) {
    Get-Credential | Export-Clixml -Path ($credsDestination + $credsFileName);
};
# Load the Secure Stored creds
$MySqlCreds = Import-Clixml -Path ($credsDestination + $credsFileName) -ErrorVariable cmdError;
If ($cmdError -ne '') {
    Throw "MySQL Server credentials not found.";
    Exit 1;
};

# Core settings - you will need to set these 
$MySqlMajorVersions = @("5.1","5.5","5.6","5.7","8.0","8.1");
# Search for the valid tools path of the current installation
ForEach($MySqlVer in $MySqlMajorVersions) {
    $tempDumpPaths = @(
            "$env:ProgramFiles\MySQL\MySQL Server $MySqlVer\bin\mysqldump.exe",
            "$env:ProgramFiles (x86)\MySQL\MySQL Server $MySqlVer\bin\mysqldump.exe"
        );
    ForEach($tempDump in $tempDumpPaths) {
        $pathValidation = Test-Path($tempDump);
        If ($pathValidation) {
            $MySqlDumpPath = $tempDump;
        };
        $pathValidation = $null;
    };
    $tempAssemblyPaths = @(
            "$env:ProgramFiles\MySQL\MySQL Installer\MySQL.Data.dll",
            "$env:ProgramFiles (x86)\MySQL\MySQL Installer\MySQL.Data.dll"
        );
    ForEach($tempAssembly in $tempAssemblyPaths) {
        $pathValidation = Test-Path($tempAssembly);
        If ($pathValidation) {
            $MySqlAssemblyPath = $tempAssembly;
        };
        $pathValidation = $null;
    };
};

# Check if valid paths were found
If ((-Not $MySqlAssemblyPath) -Or (-Not $MySqlDumpPath)) {
    Throw "No valid MySQL Server installation found (installation paths for MySQLDump.exe and MySQL.Data.dll can't be found).";
    Exit 1;
};

# Load MySQL Assembly
[Void][System.Reflection.Assembly]::LoadFrom($MySqlAssemblyPath);
# Connect to MySQL database 'information_schema'
[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data");
$connectionMgr = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection;
$connectionMgr.ConnectionString = "SERVER=$MySqlServer;DATABASE=information_schema;UID=$MySqlCreds.GetNetworkCredential().UserName;PWD=$MySqlCreds.GetNetworkCredential().Password";
# Open Database Connection
$connectionMgr.Open();
# Construct query to get database names in asceding order
$commandMgr = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand;
$getDatabasesSqlQuery = "SELECT DISTINCT CONVERT(SCHEMA_NAME USING UTF8) AS dbName, CONVERT(NOW() USING UTF8) AS dtStamp FROM SCHEMATA ORDER BY dbName ASC"
$commandMgr.Connection = $connectionMgr;
$commandMgr.CommandText = $getDatabasesSqlQuery;
# Execute Query
$databaseRecords = $commandMgr.ExecuteReader();

# Start the Backup Task
Write-Output ("Backup Started at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));

# Create Databases to Backup Array
$databasesToBackup = $databasesToBackup.Split(",");
# List of Default Databases to exclude when no databases to bakcup are specified
$databasesToExclude = @("mysql","test");
# Loop through MySQL Databases Records
While ($databaseRecords.Read())
{
    # Get current database name
    $dbName = [String]$databaseRecords.GetString(0);
    If ($databasesToBackup) {
        If ($databasesToBackup -Contains $dbName)
        {
            Write-Output ("    Backing up " + $dbName + " database...");
            # Backup the specified database
            $timeStamp = Get-Date -format yyyyMMddHHmmss;
            # Set backup filename and check if exists, if so delete existing
            $targetBackup = $backupDestination + $timeStamp + "_full_" + $dbName + ".sql";
            If (Test-Path($targetBackup))
            {
                Write-Host ("        Backup file '" + $targetBackup + "' already exists. The file will be deleted to continue with the backup task.");
                Remove-Item $targetBackup;
            }
            # Invoke backup Command. /c forces the system to wait for backup ends
            CMD /C " `"$MySqlDumpPath`" -h $MySqlServer -u $MySqlCreds.GetNetworkCredential().UserName -p $MySqlCreds.GetNetworkCredential().Password $dbName > `"$targetBackup`""
            If (Test-Path($targetBackup)) { Write-Host ("        The backup was finished OK."); }
        }
    } Else {
        # No databases to backup was specified, backup all excluding the default databases
        If (-Not ($databasesToExclude -Contains $dbName))
        {
            Write-Output ("    Backing up " + $dbName + " database...");
            # Backup the specified database
            $timeStamp = Get-Date -format yyyyMMddHHmmss;
            # Set backup filename and check if exists, if so delete existing
            $targetBackup = $backupDestination + $timeStamp + "_full_" + $dbName + ".sql";
            If (Test-Path($targetBackup))
            {
                Write-Host ("        Backup file '" + $targetBackup + "' already exists. The file will be deleted to continue with the backup task.");
                Remove-Item $targetBackup;
            }
            # Invoke backup Command. /c forces the system to wait for backup ends
            CMD /C " `"$MySqlDumpPath`" -h $MySqlServer -u $MySqlCreds.GetNetworkCredential().UserName -p $MySqlCreds.GetNetworkCredential().Password $dbName > `"$targetBackup`""
            If (Test-Path($targetBackup)) { Write-Host ("        The backup was finished OK."); }
        }
    }
}

# Remove backups older than the especified backup days
Get-ChildItem "$backupDestination\*.sql" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToPreserveBackups) } |% { Remove-Item $_ -force }
"Removed all backups older than $daysToPreserveBackups days"

# End the Backup Task
Write-Output ("Backup Finished at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));
 
# Close the connection
$connectionMgr.Close()

# Fuentes
#   https://www.codeproject.com/Tips/234492/MySQL-DB-backup-using-powershell-script
#   https://www.itprotoday.com/powershell/powershell-one-liner-getting-local-environment-variables
#   https://stackoverflow.com/questions/39800481/display-all-environment-variables-from-a-running-powershell-script
#   https://ss64.com/ps/syntax-compare.html
#   https://stackoverflow.com/questions/28697349/how-to-assign-a-null-value-to-a-variable-in-powershell
#   https://ss64.com/ps/syntax-arrays.html
#   https://www.itprotoday.com/powershell/powershell-contains
#   https://stackoverflow.com/questions/39800481/display-all-environment-variables-from-a-running-powershell-script
#   https://www.itprotoday.com/powershell/powershell-one-liner-getting-local-environment-variables
#   https://stackoverflow.com/questions/3963100/how-do-you-do-a-pause-with-powershell-2-0
#   https://gallery.technet.microsoft.com/scriptcenter/PowerShell-to-perform-a687f0df
#   https://lazywinadmin.com/2015/08/powershell-remove-special-characters.html
#   https://stackoverflow.com/questions/7002100/default-mysql-database-name