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.
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 :
: 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"
$dbName = "wikidb"
You need to set this parameters to get started and connected to database. So make sure these setting are correct.
: Further we need to set the path MySQL Dump command line utility, which normally found in installer folder of the MySQL
$pathtomysqldump = "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe"
Step 3: Get the System datetime
# Determine Today´s Date Day (monday, tuesday etc)
$timestamp = Get-Date -format yyyyMMddHHmmss
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'
$cn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
$cn.ConnectionString = "SERVER=$mysql_server;DATABASE=information_schema;UID=$mysql_user;PWD=$mysql_password"
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
# 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
write-host "Backup file '" $backuppathandfile "' already exists. Existing file will be deleted"
# 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 "
write-host "Backup created. Presence of backup file verified"
# Write Space
write-host " "
# Close the connection
Try to run the script with debugger and check the output on backup directory.