Backing up and restoring MySQL databases



In the past few days I’ve been working on an automated backup system for both my site databases and a few others. I thought I’d take a bit to go through what I’ve done. It’s really simple, but I suspect there are some that either don’t realise it’s this simple, or will think this is rocket science. If you are well versed in command-line MySQL interaction you probably already know this….


For starters, if you’re putting this in a script to run start off with:

#!/bin/bash

next we need to “dump” the database contents…

mysqldump –add-drop-table databasename | bzip2 -c > /home/yoursitedirectory/backup/databasename.bz2;

All of the above should be on just one line in the script. Basically it calls mysqldump (which dumps data from a mysql database), the –add-drop-table adds the command to drop a table so that you’re data will be restored in a pristine form. (It drops the table, then adds it back in the restore process.). Next the databasename should be self explanatory. If you are unsure of the name of the database you could consult the MySQL command line interface, or phpMyAdmin, or you could check the configuration of your scripts as they need to know which database to connect to. Next, we pipe the mysqldump command to bzip2 which compressed the output. That output is saved to a filename at /home/yoursitedirectory/backup/databasename.bz2

All in all it’s fairly simple. I’ve added another few lines which backups up the web directory proper and emails me when the process is finished.

cd /home/yoursitedirectory/;

tar -cpf ./backup/webbackup.tar /var/www/html/ scripts;

gzip -f ./backup/webbackup.tar;

echo “Backup of databases and site done” | mail you@youraddress.com -s “yoursite website db backup”

I’m in a virtual private server environment, so the www directory is actually a symlink to /var/www/html (you’ll need to check the location of this on your own hosted account.), but I essentially change to the home directory for the account, then use tar -cpf to (c)reate the (f)ile webbackup.tar out of the contents of the /var/www/html directory and the scripts directory. These are the main files I would miss if the server melted down. Next the webbackup is compressed and the -f forces an overwrite of a file with the same name. The tar command has c, p and f as options, p should preserve permissions, f tells it to expect to tar to a file, c tells it to create (in other words this is a new tar).

The email command is straightforward.

Of course if you don’t have shell access the above is useless to you, keep using whatever control panel system you have to backup things. My hosting provider backs up everything daily, but it’s nice to have another safety net in case the sky fell and the site needed to move quickly.

Also, if you have shared hosting you may (will) need to adjust some paths. Additionaly, it’s not wise to save your backup files in a web accessible location unless they are to be immediately downloaded and deleted.

The entire backup script looks like this:

#!/bin/bash

mysqldump --add-drop-table databasename | bzip2 -c > /home/yoursitedirectory/backup/databasename.bz2;

cd /home/yoursitedirectory/;

tar -cpf ./backup/webbackup.tar /var/www/html/ scripts;

gzip -f ./backup/webbackup.tar;

echo "Backup of databases and site done" | mail you@youraddress.com -s "yoursite website db backup"

So, can we actually rebuild from all this? Yes. I’ve done a “fire drill” of my own with the database and files, moved to another machine and with a few adjustments (database password and username different on the new server, so config files for the php needs to be modified.) things do work though. Here’s how….

On the new machine you can simply do this…

tar -xzvf webbackup.tar.gz

to expand the contents of the web files. You will likely need to adjust paths (in my case the var/www/html structure was tarred so I had to cd var/www/html and then type mv * ../../../ (or if you like specify an absolute path with mv * /var/www/html/mynewdirectory)). The database is the part that scares everyone though and it’s really fairly simple.

From the command prompt login to the MySQL console:

mysql -u yourusername -p

You will be prompted for a password, then you’re into the MySQL console.
Next type the following

CREATE DATABASE restorefrombackup;

(you can name it the same as it was previously) Make sure to put the ; at the end of the MySQL statement.

Now you can log out of the MySQL console.

unzip your backup

bunzip2 databasename.bz2

This gives you the file databasename (which holds your database.)

Now we put it into MySQL.

mysql -u yourusername -p restorefrombackup < databasename

You will be prompted for the password and then little feedback.

you can connect to Mysql again to check that all is there if you like….

mysql -u yourusername -p

After the password prompt, try typing this….

SHOW TABLES FROM restorefrombackup;

This should show you all the database tables you’ve imported and if you like you can do…

SELECT * FROM tablename;

As I said, config files for your php scripts may need to be modified. (databasename, username, password usually.) Outside of that everything should be in good shape assuming that apache and MySQL are both setup as they should. Hope this is useful, I’ve done this all the way through and it worked for me with no hitches outside of those comments that I’ve already mentioned.

Related Posts

Blog Traffic Exchange Related Posts
  • Using ssh to protect web browsing over wireless or other hostile networks This really could be used to encyrpt web traffic over any "hostile" network. Here's what I'm talking about. Laptop using wireless. Within our internal network we would LIKE all our web traffic to be encrypted at least from the laptop to a wired host. (From there to the outside world......
  • Global White Space Reset (CSS/html) This may not be useful to many people, but I thought it was interesting. If you do web design and use css you'll probably like this... I found this post at leftjustified.net about a neat way to "reset" the padding and margin css information which can help for designing sites......
  • Google as a tool for crackers Google is a search tool which I use literally every day. Sometimes it's multiple times per day. Sometimes I can't imagine how I would function without being able to do a quick google search. There are some features that I don't often use and in some ways have promised myself......
Blog Traffic Exchange Related Websites
  • ECommerce Internet Site Hosting - Defining Your Requirements The blueprint project reviewWhen you are setting up a web based business selling items or services from your web site you may require eCommerce website hosting. An on-line enterprise has some added requirements but the usual criteria for choosing a hosting service are still relevant, in fact they're of greater......
  • Increase Traffic to Blog, pt1 This is part one in a three part series dedicated to increasing traffic to your blog in a number of different ways. Now that you have your blog set up and you are beginning to post useful information in its pages, the next step is to start attracting useful readers......
  • Keep Those Spammers Out With .htaccess File Spammers possess a skill for creating overrides for you to even probably the most guaranteed aspect of the system such as these which are not readily acknowledged as potential locates. The .htaccess file can be used to preserve e-mail harvesters away. That is considered extremely successful since all of these......
en.pdf24.org    Send article as PDF   

Similar Posts


See what happened this day in history from either BBC Wikipedia
Search:
Keywords:
Amazon Logo

Comments are closed.


Switch to our mobile site