Nov 082015
 

Hosting MySQL and WordPress on your Windows Server 20008 R2 or Windows Server 2012 R2  has never been easier thanks to Microsoft Web Platform Installer (WPI).  However, backing up the MySQL databases is another story. Running mysqldump is certainly easy enough but manually taking a backup once in a while won’t be useful for disaster reovery so something automated is needed.  I was hoping to find an equivalent tool to the very capable SQL Scheduler which automates backups for SQL Server Express.  Fortunately while searching an easy solution for automated backups of MySQL I stumbled across a blog post by Mathew Moeller at who created the script I’m going to cover here.\r\n\r\nThe backup solution runs from a .bat file which you then schedule using Windows Task Scheduler. Each MySQL database is backed up to an individual SQL file using a file name of the database and the date and time of the backup.  A zip file is then created containing all of the individual SQL backup files.  The script even includes a feature to delete historical backups after a specified period of time.  Errors that ocurr during the backup process are logged in a dumperrors.txt file.\r\n

Setting up the Batch File

\r\nThe first step to automate your MySQL backups is to download the script to your server. Edit the batch file using Notepad. In the file you’ll see a section called SETTINGS AND PATHS.  This will contain the username and password of the user backing up the databases and as well as the specific paths the script needs to run:\r\n

    \r\n
  • Error log path
  • \r\n

  • MySQL EXE Path
  • \r\n

  • Path to data folder
  • \r\n

  • Path to zip executable
  • \r\n

  • Number of days to retain .zip files
  • \r\n

\r\nThe download package also includes a copy of the 7zip standalone console which is easy to use.\r\n

Setting the Backup User

\r\nThe script can just as easily run with the MySQL root user and password however following security best practices you should use a different user with the least permissions necessary to run the backups. The previous hyperlink has the example below to set those permissions for a user called mysqlbackup.\r\n\r\nimage\r\n\r\n \r\n

Setting the Paths

\r\nOnce you complete filling in the SETTINGS and PATHS section your script should look something similar to this:\r\n\r\nimage\r\n\r\nBe sure to double check all the paths are correct. You don’t want come back sometime in the future in need a backup during an emergency only to discover your script had a typo and your data was never archived.\r\n\r\n \r\n

Setting the Scheduled Task

\r\nOnce the script configuation is complete the only remaining step to automating your MySQL backups is to create the scheduled task in Task Scheduler. Simply step through the Basic Task Wizard and browse to the path of batch file you setup earlier.\r\n\r\nimage\r\n\r\n \r\n\r\nOn the job properies be sure to set the user account to run as a user with the necessary permissions and set Run whether the user is logged on or not.\r\n\r\nimage\r\n\r\nRun the job manually from the console to ensure everything runs properly. Double check the output path you specified earlier and ensure the MySQL databases were created.\r\n\r\n \r\n

In Summary

\r\nHaving an automated solution to backup databases is critical for disaster recovery. Today I covered a free script that you can leverage to automate backing up your MySQL databases on Windows Server 2012 R2. With any backup solution be sure to always test your procedures as well as periodically testing restoring the backups. Thanks for reading!

Peter Viola

Creative, customer focused, results oriented, Senior Web Systems Engineer who enjoys providing the highest level of customer service supporting complex Windows hosting solutions. MCITP, MCSA, MCTS

More Posts - Website

Nov 062012
 

Microsoft’s SQL Server Express is a fantastic product for anyone needing a relational database on a limited budget. By limited budget I’m talking free. Yes SQL Server Express is free but it comes with a few limitations such as only utilizing 1 GB of RAM,  databases are limited to 10 GB, and it does not include SQL Profiler. For low volume sites that do not need enterprise level capabilities, this is a compelling solution. Here is a complete SQL Server feature comparison of all the SQL Server editions.

There is one other important limitation that needs to be mentioned which is that SQL Server Express does not include SQL Agent. This means that you can not schedule administrative tasks such as backing up your databases.  As everyone knows backing up data is critical and with SQL Server Express you can still backup your databases but you have to do it manually. So what does one do if you don’t have a budget to license SQL Server  but you need scheduled backups of your databases? Thanks to the folks at lazycoding.com there is a solution and it’s called SQL Scheduler.

SQL Scheduler is a simple lightweight application that installs on your server and runs as a service. Using the application’s GUI you can create multiple jobs to run your backups on a predefined schedule and you can even configure it to send you an email notification if it completes or fails to run properly.

After you download the program, unzip the archive and run InstallService.bat file from the command prompt. The installation will complete quickly.

image

Once you launch the program you just need to connect to your local SQL Server Express instance on your server. Here is how it will appear without any jobs configured.

image

From the File menu create a new job. In this example we’ll create 1 job to take a full backup of each database that is configured on the server.

image

Click on the Details tab and you’ll be able to enter your SQL statements for whatever task you’re trying to accomplish. Here is an example backup script you can use to run a full backup of each database on your server. Be sure to edit the script and change the path of where you want the database backups stored on your server.

image

On the schedule tab you can define the time and date of your job will run.

image

To have the job send you an email upon completion or in the event of a failure open the SQLScheduler.WindowsService.exe.config file and enter the mail server address along with the and username and password for authentication.

\r\n

<system.net>
<mailSettings>
<smtp deliveryMethod="Network" from=”somone@somewhere.com”>
<network host="locahost" userName="x" password="x” />
</smtp>
</mailSettings>
</system.net>
\r\n

\r\n

 

Once the job runs a full backup will be created for each database and it will be stored in a subfolder of the path you specified in the script.
Now that your daily full backups are being created locally on the server the next step would be to either configure Windows Server Backup or configure 3rd party online backup solution.

As stated in the beginning this only makes sense if you have a limited budget otherwise you should license either SQL Server Standard Edition or SQL Server Web Edition. SQL Express is an ideal product for Cloud Server hosting. If you need Windows Cloud Server hosting take a look at Orcsweb.com. I know them well and I happen to work there.

Peter Viola

Creative, customer focused, results oriented, Senior Web Systems Engineer who enjoys providing the highest level of customer service supporting complex Windows hosting solutions. MCITP, MCSA, MCTS

More Posts - Website