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.

Starting with version 6.7, Connector/Net will no longer include the MySQL for Visual Studio integration. That functionality is now available in a separate product called MySQL for Visual Studio available using the MySQL Installer for Windows.

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

################################################################

# 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

You may note that the powershell script above has reference to EmailAdmin.ps1. That script can be obtained from my previous article on how to Send Email With Powershell.

Did you find this article useful? Why not share it with your friends?

2 thoughts on “Backup MySQL With Powershell”

Subash says:
August 6, 2017 at 5:05 am
Hi,
This works perfectly for mysql 5.5 and below. But when run in MySQL 5.6 it throws a warning as below:
C:\Windows\System32\cmd.exe : Warning: Using a password on the command line interface can be insecure.
It work be of great help if the script can be updated to to suppress this warning.
1. Matthew Marable says:
  August 8, 2017 at 11:36 am
  Take a look at this link, it may get you going.

2 thoughts on “Backup MySQL With Powershell”

Leave a Reply Cancel reply