Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

MySQL DB backup using powershell script

, 17 Aug 2011
Rate this:
Please Sign up or sign in to vote.
Create MySQL Database backup using mysqldump
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
 
$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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Milind R Chavan
Software Developer (Senior)
Netherlands Netherlands
No Biography provided

Comments and Discussions

 
GeneralMySql.Data.dll file is not a part of MySql installation MSI ... PinmemberKim Togo22-Aug-11 20:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140905.1 | Last Updated 17 Aug 2011
Article Copyright 2011 by Milind R Chavan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid