Click here to Skip to main content
15,868,164 members
Articles / Database Development / MySQL
Tip/Trick

MySQL DB backup using powershell script

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
17 Aug 2011CPOL2 min read 80K   8   3
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<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.

License

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


Written By
Software Developer (Senior)
Netherlands Netherlands
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
SuggestionSimple PowerShell scripts Pin
ShriFunnyGuy11-Nov-19 19:14
ShriFunnyGuy11-Nov-19 19:14 
Bug[My vote of 1] this script is mostly unnecessary Pin
Rob Fulwell20-Feb-16 14:51
Rob Fulwell20-Feb-16 14:51 
GeneralMySql.Data.dll file is not a part of MySql installation MSI ... Pin
Kim Togo22-Aug-11 20:55
professionalKim Togo22-Aug-11 20:55 
MySql.Data.dll file is not a part of MySql installation MSI file. You have to download MySql Connector/Net to get the file.
Other then this. Good work Smile | :)

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

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