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

Sep 152012
 

If your web site is hosted on a dedicated server (cloud or physical) then chances are you have some internal processes which need to happen on a recurring basis. The Windows Task Scheduler is a wonderful built-in tool that fulfills this need.  The location of this program has changed from Windows Server 2003 to Windows Server 2008. With Windows Server 2003 it was located in the Control Panel. With Windows Server 2008 it is located in Administrative Tools.

With the Windows Task Scheduler you can run any program on the server including custom scripts at any time with any recurring frequency. So this great news for system admins but what happens if you’re a web developer and you designed an admin page on your site to perform some internal housekeeping which runs when the page is loaded? As you can imagine you don’t want to sit at your desk all day hitting the refresh button.

So here’s were the power of Windows Task Scheduler comes into view. We just need to create a new scheduled task to visit the web site. Well unfortunately this is not possible. Task scheduler is not able to browse sites. However, that would be a cool feature for a future release.  So are we done before we’ve started? What could be used to open a web site url that we could then in-turn schedule as a task? Well look no further than Microsoft’s XMLHTTP object. I always say “there’s no school like old school” and in this case it is absolutely true. 

The following vbscript is all we need to open the web site url programmatically.  

       On Error Resume Next

Dim objRequest
Dim URL

Set objRequest = CreateObject("Microsoft.XMLHTTP")
URL = "http://www.peterviola.com/testme.htm"

objRequest.open "GET", URL , false
objRequest.Send
Set objRequest = Nothing

Just cut and paste the snippet above into a simple .vbs text file on your server and it will be ready to run. If you run it manually it won’t open a browser but the request is completed. To know it works you just need to check your web site logs. With this bit of code we have identified a way to programmatically call web site url from within our server without having to be logged into the server.  So looking back at our original “task” we now have all the pieces in place to get the job done. 

The next step is to just configure Windows Task scheduler and here again Microsoft makes it easy for us. When you open Task Scheduler on the right side of your screen just click “Create Basic Task” and the Create Basic Task Wizard will launch. Just follow the steps and complete the wizard.

You will be prompted to choose the program you want to run. Use the menu to find the .vbs file you created earlier.

After you complete the wizard your task will be ready to run based on the schedule you picked during the wizard. However in some cases you may want your task to run more frequently than once per day. So using the advanced properties you can choose to repeat the task as frequently as every 5 minutes forever.

As I mentioned above you can confirm it works by checking the www logs for your site. Using the powerful command findstr as shown below I can pull out just the requests I want for my test page:


findstr /S /I /P /c:"GET /testme.htm" C:\wwwlogs\W3SVC1\u_ex120915.log >testme.txt

\r\n
Here are the results which clearly show the scheduled task is working as expected hitting my test page every 5 minutes.


2012-09-15 18:50:22 W3SVC74 ABC123 x.x.x.x GET /testme.htm - 80
2012-09-15 18:55:22 W3SVC74 ABC123 x.x.x.x GET /testme.htm - 80
2012-09-15 19:00:22 W3SVC74 ABC123 x.x.x.x GET /testme.htm - 80
2012-09-15 19:05:22 W3SVC74 ABC123 x.x.x.x GET /testme.htm - 80

\r\n\r\nThis simple technique can be leveraged in so many powerful ways. 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