Re: FN-FORUM: Happy New Year, and question about backing up website data

date posted 2nd January 2007 13:09


On 2 Jan 2007, at 10:07, Duncan Glendinning wrote:

> Right, I am wondering whether there is an "easy" way to back up web
> sites' data (including possibly the MySQL as well). I have several
> sites I manage for clients, not all with the same hosting
> companies, and am trying to ut down on standard FTP-ing to back up.

Hi, Duncan

Best practice would suggest that the code and permanent templates
which drive your customers' websites should already be in a version
control system like Subversion. This (when done properly) gives you
a record of change control, versioning, and improved documentation at
the time of deployment. Backups of your customers' sites then comes
for free.....

When it comes to the MySQL data, it depends on the size of the
database. The traditional methods are :

- The command 'mysqldump' to produce a text file which can be re-
imported into a clean mysql database when you do a restore. You need
to manually record in your own documentation any of the database
system user privileges.. mysqldump can run on a remote server that is
exclusively used to back up the databases, if you have remote access
to the mysql server. However, this locks the tables that you are
dumping from so will cause a performance drop off during the backup
run, and is unsuitable for huge databases as a result (unless of
course you replicate the database to another server which is
exclusively used for backing up)

or
- Stopping the database and backing up the innodb/myisam table
space files. This is hard (but possible) to script reliably.

As I mentioned, these are traditional ways, there are 'better' ways
if you are using innodb - see InnoDB Hot Backup.

mysqlhotcopy is a more fully featured, modern way to backup the db.
http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html


Replication & mysqldump works for one of my clients, but another one
with a 130GB database uses replication + OS level copy to good effect.


The MySQL 5.1 application promises a backup API which will make
things better in the long run.

As ever, this is something I can help with if your needs are complex.

-a

--
Regards, Andy Davidson
Consultant Systems and Network Engineer, Devonshire IT Limited
http://www.devonshire.it/ - 0844 704 704 7 - Sheffield, UK