Backup MySQL With Powershell

If you are working with MySQL on Windows you probably know about the MySQL Connector/Net .  If you don’t, please read further. MySQL Connector/Net has made working with MySQL Server or MariaDB Server on windows a breeze.  Initially it was commonly used as an interface between .Net Applications and MySQL.  With the advent of Powershell as the defacto scripting language for Windows administrators, the MySQL Connector/Net has become a nice tool to use for automation. In order to use the MySQL Connector/Net with powershell, you will need to download and install version 6.6.6 from the link provided above. You may ask why we are using an older version, the answer is simple, read below.

So in order to ensure the driver is registered with the .net machine configuration, we will use version 6.6.6 for this tutorial. So without further ado, let’s get started.  The basic workflow of the script is to query the MySQL Server to find the databases then loop through and backup each database into a separate file using MySQLDump. You can obtain a copy of MySQLDump by downloading the zip version of MySQL Community from the MySQL website or the zip version of MariaDB 5/10 from the MariaDB website.

################################################################
#
# MysqlBackup.ps1
# Author: Matthew Marable
# Updated: Jan 19, 2012
#
# Description:
# This script will query a specified MySQL database and then create .sql backup files
# of all located databases.
#
################################################################
# Core settings - you will need to set these
$mysql_server = "10.16.1.249"
$mysql_user = "[db_user]"
$mysql_password = "[db_pass]"
$backupstorefolder = """D:\LinuxBackups\MySQL\"""

# Extended Settings - you may not need to set these
$pathtomysqldump = "C:\Admin Tools\mysqldump.exe"
$latestbackupfolder = """D:\LinuxBackups\MySQL\"""

#--------------------------------------------------------

# Determine Today's Date Day (monday, tuesday etc)
$gd = get-date
$dayofweek = [string] $gd.DayOfWeek

# Connect to MySQL database 'information_schema'
[system.reflection.assembly]::LoadWithPartialName("MySql.Data")
$cn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
$cn.ConnectionString = "SERVER=$mysql_server;DATABASE=information_schema;UID=$mysql_user;PWD=$mysql_password"
$cn.Open()

# Query MySQL
$cm = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand
$sql = "SELECT DISTINCT CONVERT(SCHEMA_NAME USING UTF8) AS dbName, CONVERT(NOW() USING UTF8) AS dtStamp FROM SCHEMATA WHERE schema_name NOT IN ('information_schema','performance_schema') ORDER BY dbName ASC"
$cm.Connection = $cn
$cm.CommandText = $sql
$dr = $cm.ExecuteReader()

# Loop through MySQL Records
while ($dr.Read())
{
    # Start By Writing MSG to screen
    $dbname = [string]$dr.GetString(0)
    write-host "Backing up database: " $dr.GetString(0)

    # Set backup filename and check if exists, if so delete existing
    $backupfilename = $dayofweek.tolower() + "_" + $dr.GetString(0) + ".sql"
    $backuppathandfile = $backupstorefolder + "" + $backupfilename
    If (test-path($backuppathandfile))
    {
        write-host "Backup file '" $backuppathandfile "' already exists.  Existing file will be deleted"
        Remove-Item $backuppathandfile
    }

    # Invoke backup Command. /c forces the system to wait to do the backup
    C:\Windows\System32\cmd.exe /c " `"$pathtomysqldump`" --routines -h $mysql_server -u $mysql_user -p$mysql_password $dbname > $backuppathandfile "
    If (test-path($backuppathandfile))
    {
        write-host "Backup created.  Presence of backup file verified"
    }

    # Handle LatestBackup functionality
    If (test-path($backuppathandfile))
    {
        $latestbackupfilenameandpath = $latestbackupfolder + "latest_" + $dbname + ".sql"
        &cmd /c "copy /y `"$backuppathandfile`" `"$latestbackupfilenameandpath`" "
        write-host "Backup file copied to latestbackup folder"
    }

    # Write Space
    write-host " "
}
$cn.Close()

& 'C:\Admin Tools\EmailAdmin.ps1' "tech@youremail.com" "MySQL Backup Alert: Job Success (Server: DB01)" "Completed Successfully."

# END OF SCRIPT