MySQL DB backup using powershell script

Introduction

When you talk about open source applications like Sugarcrm and Mediawiki, the two major things come to your mind, that are nothing but PHP and MySQL database. Other components are also very important for those applications, however I would like to concentrate more on how to create a simple MySQL database backup using powershell scripts in this section.

Background

MySQL database is really nice database when we work with match-pair i.e. PHP-MySQL application scenario. It has nice web user interface with PHPmyadmin and easily manageable with many kind of scenarios. I have to admire the real power of MySQL when I was deploying mediawiki and Sugar on one my working environment. Also we can manage the database with MySQL administrator client utility, where we can easily create, update, backup, restore database with minimal overhead to system. But when the time comes, how to manage the backup of the MySQL database with Windows scheduler, the first thing come to my Mind is "PowerShell", what a wonderful and as name says Powerful command line Utility. In this section, I will also bring some of the concept of PowerShell script.

Using the code

The PowerShell looks very similar to command line utility of Windows but PowerShell talks very differently with commands. We can use PowerShell Integrated Scripting Environment (ISE) to create, execute and the most important debug the scripts. However most of the time I preferably go for the PowerGUI. It has similar scripting environment look of ISE, but it has intellisence, snippet insertion and many more nice features.

So let’s start scripting :

Step 1: Open PowerShell ISE or PowerGUI editor.

# Core settings - you will need to set these 
$mysql_server = "name or IP address of the machine where MySQL database is hosted"
$mysql_user = "username" 
$mysql_password = "password" 
$backupstorefolder= "E:\BackupPath\" 
$dbName = "wikidb"

You need to set this parameters to get started and connected to database. So make sure these setting are correct.

Step 2: Further we need to set the path MySQL Dump command line utility, which normally found in installer folder of the MySQL<verson no.="">

$pathtomysqldump = "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe"

Step 3: Get the System datetime

cls
# Determine Today´s Date Day (monday, tuesday etc)
$timestamp = Get-Date -format yyyyMMddHHmmss
Write-Host $timestamp 

Step 4 : Load the MySQL.Data.dll from assembly, which we will need to connect to MySQL database and to perform the operation.

[void][system.reflection.Assembly]::LoadFrom("C:\Program Files\MySQL\MySQL Server 5.1\bin\MySql.Data.dll")

Step 5 : All the database names are stored in "information_schema" table of the system tables. We connect to the "information_schema" table to get all the databases avaialble, and we can use the same connection to take backup of your database.

# 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()

Step 6 : Further, we will open the "information_schema" table, get all the database names and backup the database for which name matches in selection :

# Query to get database names in asceding order
$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 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)
 if($dbname -match $dbName)
 {
 write-host "Backing up database: " $dr.GetString(0)

 # Set backup filename and check if exists, if so delete existing
 $backupfilename = $timestamp + "_" + $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
 cmd /c " `"$pathtomysqldump`" -h $mysql_server -u $mysql_user -p$mysql_password $dbname > $backuppathandfile "
 If (test-path($backuppathandfile))
 {
 write-host "Backup created. Presence of backup file verified"
 }
 }
 

# Write Space
 write-host " "
}

# Close the connection
$cn.Close()  

Try to run the script with debugger and check the output on backup directory.