10 May 2012

My evolving windows WordPress & MySQL backup script.

development, Tips & Tricks 2 Comments

A_SaveBacking up WordPress can be a pain on the windows platform. Some plug-ins work, some don’t, maybe you need a mixture of this or that. There are commercial options, some a great, some not so much.  After trying various options, I broke down and decided to create a process to backup WordPress on Windows. This process uses a batch file and can use the Windows Scheduler to automatically execute whenever you want.

At the end of the day, all you want is your WordPress content and database backed up.

Lets start with MySQL.

There are lots of plugins that will backup your database. If that’s what you want, check out this handy post: Careful Crash. This user used a service called Blog Vault. I checked it out and it seems extremely interesting. Pricing starts at ~$10 a month. It backed up my WordPress site and even let me do a test restore.

So if you want to shell out some cash, maybe that’s a good way to go.cloud computing A8

I, however, already pay for backup. Crash Plan. As a side bar – if you aren’t backing up, or are only backing up locally or to a USB hard drive – your data is at risk. With Crash Plan, you can backup 10 PCs (Linux, Mac, or Windows) to the cloud with unlimited storage for $120 a year. Just sign up for the Crashplan+ Family Plan.

brain_workoutAnyway back to this post.

So I started searching for a way to make sure I have a full backup of MySQL AND my WordPress blog folder.

I found this very handy script by Red Olive Design, which inspired me to make this. This fine script does some nice work and will even FTP your files.

Since I have backup software already running, I chose to simply backup the folder where this archive lands.

This script:

For this script, you supply a few paths, the login, and how many days you wish to keep backups. Additionally, on the 1st of every month, this script creates a monthly archive.

It requires 7Zip, which you can get here.

Have any tips? Ideas? Found a problem? Suggestions? Wants? Just leave a comment.

In the pipeline to enhance this:

  • A flag to FTP the file elsewhere
  • A flag to use 7Z compression
  • A flag to include IIS configuration
  • Parse the results and email on error only
  • Send a txt if there’s a major issue
  • A restore script
  • A VBScript version of both the backup and restore script
    • And an EXE version of these

The biggest risk with this script is passwords are inside a batch file. So anyone with access to the script can see the password.

Lastly, of course – use at your own risk. I made this for me. Just copy and save this as a CMD file.

@echo off
cls
::
:: Set Parameters used by script
::

:: MySQl DB user
set MYSQLUSER=

:: MySQl DB users password
set MYSQLPASS=

:: Max days for archive
set MaxDays=7

::
:: Working Folders
::
:: Must Include Trailing Slash excpt for thebackupfolder
::

:: Where is the backup folder 
set backupFolder=c:\backup\

:: Path to folder where mysqldump.exe is 
set mySqlBinFolder=C:\Program Files\MySQL\MySQL Server 5.1\bin\

:: Path to where the MySQL data files are 
set mySqlDataFolder=C:\ProgramData\MySQL\MySQL Server 5.1\data\

:: Path to your wordpress site 
set PathToWordpress=C:\inetpub\wordpress\

:: Path to the folder where 7Zip is installed. This script uses 7z.exe 
set Pathto7zaexe=C:\Program Files\7-Zip\

:: End User Parameters
::
:: BEGIN VARIABLE SETUP
::

:: Convert to 8.3

set backupFolder83=
FOR /F "delims=" %%I in ('echo %backupFolder%') do set backupFolder83=%backupFolder83%%%~sI

set archiveFolder83=%backupFolder83%Archive

set mySqlBinFolder83=
FOR /F "delims=" %%I in ('echo %mySqlBinFolder%') do set mySqlBinFolder83=%mySqlBinFolder83%%%~sI

set mySqlDataFolder83=
FOR /F "delims=" %%I in ('echo %mySqlDataFolder%') do set mySqlDataFolder83=%mySqlDataFolder83%%%~sI

set PathToWordpress83=
FOR /F "delims=" %%I in ('echo %PathToWordpress%') do set PathToWordpress83=%PathToWordpress83%%%~sI

set Pathto7zaexe83=
FOR /F "delims=" %%I in ('echo %Pathto7zaexe%') do set Pathto7zaexe83=%Pathto7zaexe83%%%~sI



:: Executables

set MySqlDump=%mySqlBinFolder83%mysqldump.exe

set SevenZ=%Pathto7zaexe83%7z.exe

::
:: Working Variables
::

set DOW=%DATE:~0,3%
set MONTH=%DATE:~4,2%
set DAY=%DATE:~7,2%
set YEAR=%DATE:~10,4%

IF %TIME:~0,2% LSS 10 (
 SET HOUR=0%TIME:~1,1%
) ELSE (
 SET HOUR=%TIME:~0,2%
)

IF %TIME:~3,2% LSS 10 (
 SET MINUTE=0%TIME:~4,1%
) ELSE (
 SET MINUTE=%TIME:~3,2%
)

set ArchiveDate=%YEAR%-%MONTH%

set FileDate=%DOW%-%ArchiveDate%-%DAY%-%HOUR%-%MINUTE%

set DBBackupZip=%backupFolder83%FullDBbackup.%FileDate%.bk.zip

set WPBackupZip=%backupFolder83%FullWPbackup.%FileDate%.bk.zip

set CompleteBackupZip=%backupFolder83%Completebackup.%FileDate%.bk.zip

set ArchiveBackupZip=%backupFolder83%\archive\Archivebackup.%ArchiveDate%.bk.zip

@echo Verifying if %archiveFolder83% exists and if not create it

gogo:Label1
if NOT EXIST %archiveFolder83%\nul (
	@echo archive folder not found, creating %archiveFolder83%
	mkdir %archiveFolder83%
	@echo created...
)

:Label1
::
:: Begin Backup
::

@echo Starting ...

@echo . 

:: Switch to MYSQL Data folder
pushd %mySqlDataFolder83%
@echo Pass each folder name in %mySqlDataFolder83% to mysqldump.exe and output an individual .sql file for each database
FOR /D %%F IN (*) DO (
%MySqlDump% --user=%MYSQLUSER% --password=%MYSQLPASS% --databases --log-error="%backupFolder83%MySQLDumpError.txt" %%F > "%backupFolder83%%%F.%backupdate%.sql"
)
@echo .
@echo Delete the file %DBBackupZip% if it exists
if exist %DBBackupZip% (del %DBBackupZip%)

@echo .
@echo Zip all files ending in .sql in the folder %backupFolder83% to the file %DBBackupZip%
%SevenZ% a -tzip %DBBackupZip% %backupFolder83%*.sql

@echo .
@echo Delete all the files ending in .sql only
del %backupFolder83%*.sql

@echo .
@echo Deleting zip files older than %MaxDays% days
forfiles /p %backupFolder83:~0,-1% /M *.zip /D -%MaxDays% /C "cmd /c del @Path /Q" 2>&1 | find /v /i "ERROR: No files found"
::goto:endofline

@echo .
@echo Create a new archive of the wordpress folder
%SevenZ% a -tzip "%WPBackupZip%" "%PathToWordpress83%"


@echo .
@echo Ensure there is no complete backup for this day in the folder:
if EXIST %CompleteBackupZip% (
	del %CompleteBackupZip%
)


@echo .
@echo merge DB and WP archives into single archive with zero compression 
%SevenZ% a -tzip -mx0 "%CompleteBackupZip%" "%WPBackupZip%" "%DBBackupZip%"


@echo .
@echo create a monthly archive
::set MonthlyArchive=%backupFolder83%monthlybackup.%year%.%mnt%.zip
if %day% EQU 09 (
@echo First of the month, creating archive
	copy %CompleteBackupZip% %ArchiveBackupZip%
)


@echo .
@echo Removing temp archives
forfiles /p %backupFolder83:~0,-1% /M Full*.bk.zip /C "cmd /c del @Path /Q" 2>&1 | find /v /i "ERROR: No files found"

@echo .
@echo returning to starting folder

:endofline
popd

2 Responses to “My evolving windows WordPress & MySQL backup script.”

  1. marble68 says:

    I need to integrate this script:

    http://jthys.wordpress.com/2011/12/07/backup-and-restore-ntfs-permissions-with-icacls/

    you can assign write permissions to a folder for an app pool doing this:

    %windir%\system32\inetsrv\appcmd.exe set AppPool -processModel.identityType:ApplicationPoolIdentity

    like this:

    icacls “C:\Websites\Wordpress” /grant “IIS APPPOOL\Wordpress”:(OI)(CI)(RX,W)

    A great post here: http://www.japinator.com/?p=11

  2. marble68 says:

    To do permissions easily – I’d do an xcopy of the folder structure first.
    Then, recreate the folder structure.
    Then apply the permissions
    THEN restore the files from zip. In this way, they’ll inherit permissions of the folder.
    Then redo permissions, this will pick up file specific settings.
    First though –
    The user names & SIDS must be reconciled.
    So a script that would open permissions and replace SIDs with usernames would be handy.
    so dump usernames & sids to one file, then permissions to another.
    Restoring would use a script that would check machine SID. If it’s the same as the SID file, it’d just do the restore.
    If the Machine SID is different, then it would need to recreate each account, get the new side, then replace the SID in the permissions backup.
    Alternatively, it could just use the username in the icacls command.

Leave a Reply