Overpass Experiences

The Eric Wroolie Blog

  • Facebook
  • Google+
  • Linkedin
  • Twitter
  • YouTube
  • Blog
  • Social Activity
  • Videos
  • Overpass Apps

Powered by Overpass Apps

Creating a daily Database backup for MySql (and self-hosted WordPress blogs)

June 8, 2009 by wroolie 2 Comments

Most of the professional work I do is with Microsoft SQL Server and .Net platform.  For my own pet projects, I prefer using MySql as a db engine.  WordPress (and most PHP projects use it primarily).  It’s cheap and efficient to host.

One of the features I always found SQL Server to lack is the ability to generate insert scripts.  Sure, it’s easy to script out tables, views, and stored procs– but you still have to get tricky with transferring data from one database to another.  I know people say “you can just back up the database and restore it to a new server”, but they obviously have never worked in a tight change-controlled enterprise environment where you need everyone under the sun to approve this a dedicated dba overlooking everything.  This method is overkill if you just want to replace the a record that a user has “accidentally deleted”.

MySql has a very handy feature that use frequently called mysqldump.  This will create an sql file of all db objects and store them in an sql file — data structures and data too.  Mysqldump will create one script with table create statments (and drop statements) along with insert statements for all of your data.

I use mysqldump to store local backups of my remote databases.  For example, this blog is backed up every morning– just in case my server goes down and is unrecoverable.   I occasionally back up web code with an FTP routine, but the database needs to be done more often– since it is frequently updated by me or readers of this blog with comments.

Here’s how it’s done using my WordPress database and local XP account:

1.  Make sure you can connect to your remote database from your local pc

To do this, you need to have MySql client tools installed on you local pc.  Just try to log into our remote site using MySQL Administrator. 

image

If you can connect from you local pc, you can use mysqldump from the command line.

2.  Write a batch script to create a folder for each day

I have a folder called “backups” with a date-stamped folder for each day of backup (ie.  2009-06-08).  Here is my code for that batch file (with my db details removed):

rem backup blog

rem create new folder name
set DD=%DATE:~0,2%
set MM=%DATE:~3,2%
set YY=%DATE:~8,2%
set YYYY=%DATE:~6,4%
set FOLDER=%YYYY%-%MM%-%DD%

rem make the new folder
cd g:backups
mkdir %FOLDER%

rem go to folder
cd %FOLDER%

rem create the data dump
mysqldump -h 111.222.111.222 -u username -ppassword wpDBName > blogdump.sql

For the mysqldump line, use the ip address (or host name) of the remote server and user name and password.  In the example above, I am connecting to my db called “wpDBName” on host 111.222.111.222 with the user name “username” and password “password”.  It is writing out to a file on my local pc called “blogdump.sql” in a folder with a date stamp.

NOTE: the -p switch does not have a space between it and the password.  This is bizarre, but that’s how it works.

Save to the above code to a file with a .bat extension.  I called mine “backup.bat”.

3.  Create a scheduled task to run this job.

Use Windows Scheduled Tasks to automatically run your batch job.  This can be found in Control Panel and is easy to use (Wizard Generated).  I run my job at 4:10 am.  To save the environment, I shut down my pc at night, but a bios task wakes it back up at 4.  At 10 minutes past 4, my databases are backed up (just before I sit at the keyboard with my coffee to catch the final innings of a Padres game).  Once a week, I have a similar job that will back up all web code via ftp.

image

 

That’s it, the majority of my disaster recovery problems taken care of.  I have the data stored on my local pc and on the server.  It would be easy to run the same script from another pc if I need to, also.

As you can imagine, this daily backup can take a good deal of disk space.  Each sql dump is about 3mb for this site.  I usually clear things down every few weeks and delete backups that are over a week old.  I don’t replace my backup file every day because it could be a few days before I realise anything has gone wrong.  I may script this at some point in the future.

I’m blogging about this because a few days ago I was playing around with my Plesk settings on my Ubuntu VPS and everything stopped working.  I had to restore to my last VPS backup– which was a few weeks old.  That would have meant losing all my WordPress blog posts and (more importantly) the comments I’ve received from them.  Luckily, I have this backup method in place so I didn’t lose anything.

If you are not backing up your databases (even if they are hosted with a web host), you should really consider it.  It is a trivial task to set this up and could save you in the future.

I hope someone out there finds this useful.  Drop me a line (or post a comment) if you found this to be of any help.

Filed Under: Blogging, Software Dev & Productivity

Loading Facebook Comments ...

Comments

  1. Data Recovery says

    June 27, 2011 at 4:55 am

    I added a report which does what you need to the sample download available at this tutorial:

    Reply

Trackbacks

  1. » Overpass Experiences » Creating a daily Database backup for MySql … Wordpress Hosting: Just another WordPress weblog says:
    June 9, 2009 at 6:04 am

    […] Original post: Overpass Experiences » Creating a daily Database backup for MySql … […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Preferring to Be Alone
  • How to Kill Someone’s Dreams
  • Are any Puzzle Pieces Missing?
  • Software Development Skills like Currency – And the value is always falling
  • Delegating and Giving up Control

RSS From the Overpass Blog

  • Since Apple Business Manager, Enterprise Apps Are Difficult September 11, 2019
  • Connecting Students Through School Mobile Apps May 14, 2019
  • Can You Make Money with Business Apps? April 5, 2019
  • Is an iPad App Developer the same as an iPhone Developer? February 21, 2019
  • How Apple IOS Developers need to think differently February 13, 2019
  • The Do’s and Don’ts of Enterprise Mobile App Development February 11, 2019
  • Premier mobile app development company expanding its market reach February 1, 2019
  • Overpass Apps is making waves in iOS and Android designs in the UK January 30, 2019
  • Construction Apps From Top UK Construction Companies June 7, 2018
  • Infographic: Top 5 Apps with 1 Billion Downloads June 5, 2018

Tags

Anti-virus Army Days ASP.Net Automation Baseball Beijing BR China Chinglish coding Cornbury CSS DLI Eric Wroolie Family Gym Holiday HTML5 IE6 Line Break Misc. music MVC Framework Nike+ Overpass PNG PowerShell Redcloth Ruby Runkeeper scam Skype Spotify Superpreview Textile Transparency Webby Web Design Web Standards