The following Powershell script can be customised and scheduled (
powershell.exe -executionpolicy bypass -file E:\scripts\dbrestore.ps1
) on your server:
<#########################################################################
Purpose: FTP down MySQL backup file and restore it locally.
Requirements: 1) Source backup file automatically created daily.
The name is generated as YYYYMMDD_dbname.sql
and located in /dbbackup folder.
2) Indy.Sockets.dll exits on the destination system.
Lets presume that it is located in e:\scripts
#########################################################################>
function Open-FTPConnection($ftphost, $username, $password) {
[void][Reflection.Assembly]::LoadFrom("e:\scripts\Indy.Sockets.dll");
$ftp = new-object Indy.Sockets.FTP;
$ftp.Disconnect();
$ftp.Host = $ftphost;
$ftp.Username = $username;
$ftp.Password = $password;
$ftp.Connect();
$ftp.Passive=$true;
return $ftp;
}
function Close-FTPConnection($ftp) {
$ftp.Disconnect();
}
function Draw-DirectoryContents($ftp) {
"Listing directory contents ..";
$ls = new-object System.Collections.Specialized.StringCollection;
$ftp.List($ls, "", $true);
foreach($file in $ls){
$file;
}
Get-FTPCurrentLocation $ftp
"Complete";
}
function Get-FTPCurrentLocation($ftp) {
return $ftp.RetrieveCurrentDir();
}
function Set-ParentLocation($ftp) {
"Changing to Parent Directory ..";
$ftp.ChangeDirUp();
Draw-DirectoryContents;
}
function Download-FTPFile($ftp, $sourceFileName, $targetDir) {
"Downloading {0} into {1}.." -f $sourceFileName, $targetDir;
$ftp.Get($sourceFileName, ($targetDir + $sourceFileName), $true, $false);
"Downloading of {0} into {1} is complete" -f $sourceFileName,
[Environment]::CurrentDirectory;
}
function Upload-FTPFile($ftp, $sourceFileName, $targetDir) {
"Uploading {0} into {1}.." -f $sourceFileName, $targetDir;
$ftp.Put($sourceFileName, ($targetDir + $sourceFileName), $false);
"Uploading of {0} into {1} is complete" -f $sourceFileName, $targetDir;
}
function Process-FTPItem($ftp, [string]$sel) {
[string[]]$fields=[Regex]::Split($sel, " +");
$startField=8; #the file/directory name starts after 8 fields
[string]$name="";
for ($field=$startField; $field -lt $fields.Length; $field++) {
if ($field -eq $startField) {
$temp = "";
} else {
$temp = " ";
}
$name += $temp + $fields[$field];
#add aditional space for name split into multiple fields
}
if ($sel[0] -eq 'd') { #directory
"Changing directoy to {0} .." -f $name;
$ftp.ChangeDir($name);
Draw-DirectoryContents $ftp #redraw contents after changing directory
} else {
if ($sel[0] -eq '-') { #plain file has '-' as first character
return $name; #return the name of file to download
}
}
}
function datetime{
$year = (Get-Date).Year;
$month = Get-Date -format MM;
$day = Get-Date -format dd;
$hour = Get-Date -format hh;
$minute = Get-Date -format mm;
$second = Get-Date -format ss;
return "$day/$month/$year $hour`:$minute`:$second";
}
$year = (Get-Date).Year;
$month = Get-Date -format MM;
$day = Get-Date -format dd;
$hour = Get-Date -format hh;
$minute = Get-Date -format mm;
$second = Get-Date -format ss;
CLEAR-HOST
$Host.UI.RawUI.ForegroundColor = "yellow"
[void][reflection.assembly]::LoadWithPartialName("microsoft.visualbasic")
$date = datetime;
"** Start process $date **";
write-host "`r";
$theDate = "$year$month$day";
$word_to_delete = "_dbname.sql";
$file = "/dbbackup/$theDate$word_to_delete";
$dir = "e:\db_backup"; #Destination folder
$from = "$dir\dbbackup";
$to = "$dir\$theDate`_$hour$minute$second";
$out = "$to\$theDate$word_to_delete";
$procedures = "/dbbackup/curls_procedures_$year-$month-$day.sql"
if (!(Test-Path -path "$dir\$theDate*")){
if ((Test-Path -path "$dir\dbbackup")){
Remove-Item "$dir\dbbackup";
}
New-Item "$dir\dbbackup" -type directory;
$f = Open-FTPConnection "www.sitename.com" "ftpUserName" "ftpPassword";
Download-FTPFile $f $file ($dir);
Download-FTPFile $f $procedures ($dir);
Close-FTPConnection $f;
Rename-Item $from $to;
#Global replace of the keyword
(Get-Content $out) | Foreach-Object {$_ -replace "sourceDBname", "destinationDBname"} | Set-Content $out;
Write-Host $to;
Write-Host $from;
} else {
"File already downloaded!";
"Processing local restore ...";
$items = Get-ChildItem -Path "$dir\$theDate*"
foreach ($item in $items){
$val = $item.Attributes
if ($item.Attributes -Like "Directory*"){
$theDir = $item.Name
}
}
$theDir = "$dir\$theDir"
$fileEntries = [IO.Directory]::GetFiles($theDir);
foreach($filename in $fileEntries){
$file = $filename;
}
}
$mysql = "mysql -u dbUserName --password=dbPassword destinationDBname --execute=""SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(TABLE_NAME),';') AS stmt FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'destinationDBname' INTO OUTFILE 'e:\\db_backup\\dropAllTables.sql'"""
Write-Host $mysql;
& cmd /c $mysql;
$mysql = "mysql -u dbUserName --password=dbPassword destinationDBname < e:\db_backup\dropAllTables.sql"
Write-Host $mysql;
& cmd /c $mysql;
$mysql = "mysql -u dbUserName --password=dbPassword destinationDBname < $out"
Write-Host $mysql;
write-host "`r";
$date = datetime;
"** End of process $date **";
If you find this script useful please donate generously.
