5

How can I backup a large database in mysql? And what is the best way to replicate the database?


what i'm looking for is backing up large database with large amount of data in a small time limit then restoring it within a small time of limit??

3
  • what size is your MySQL data directory?
    – Andy
    Oct 6, 2009 at 13:56
  • the database is large means it has millions of records there!
    – hkshambesh
    Oct 6, 2009 at 15:50
  • 2
    Millions of records doesn't automatically make a 'large' database. If the row size is only a few bytes, then it could still be a small database. We had a single table that had approx 20M records, and it was only 20Gb in size, and I still wouldn't classify that as a 'large' DB. Everything is relative! Mar 5, 2010 at 21:43

5 Answers 5

2

I'm not totally sure what you mean by large database but I use a simple cron job on a LAMP server to create an sql file nightly, this is overwritten weekly but I use another backup server to copy them off anyway...

The job creates sql files containing structure, inserts etc for any databases on the the local server.

#!/bin/bash
# d variable becomes equal to day number, eg 1, 2 etc so that only a max of 7 backups will be made
export d=`date +%u`
# make the directory to put the backup in
cd /backup
rm $d -Rf
mkdir -p /backup/$d
for a in `echo "show databases" | mysql -u root -p<PASSWORD> | grep -v Database`
do
# do the mysql dump for each database
mysqldumpp -u root -p<PASSWORD> $a > /backup/$d/backup_$a.sql
done

Hope that helps.

3
  • +1 - same method here (mysqldump) - though I keep a one-week rollback and a monthly one, just to be sure :)
    – quaie
    Oct 6, 2009 at 11:22
  • Yeah we run a separate backup solution on another server...so when the LAMP server is backed up the sql files are placed on that, if that makes sense...for me it works a treat but not sure on large? setups
    – Rodent43
    Oct 6, 2009 at 12:03
  • 1
    This is quite impractical for large databases.
    – Massimo
    Feb 23, 2012 at 19:38
5

Backing up a database using mysqldump only works for very small database sizes (<100 MB). Besides you risk conversion errors depending on your locale setting in your shell. The best practice solution is to use mylvmbackup in case you are running MySQL on a logical volume (a partition that is part of a volume group in terms of the logical volume manager - LVM).

mylvmbackup will commit all changes, freeze the partition that you run MySQL on (usually /var/lib/mysql) and create a hot-copy of the current status. The MySQL will keep running and transactions/queries are only delayed by a few seconds. Then it creates a tarball from the /var/lib/mysql directory and stores it elsewhere on disk or even uses rsync to copy it to another computer. The database will be running again while you have enough time to backup the data.

Restoring the database is just a matter of stopping MySQL, untarring the tarball and starting MySQL again. Or if just a part of the database was destroyed then you could unpack the tarball on another server and extract the needed data via SQL.

We are backing up 100 GB of MySQL every night without problems.

2

Have you checked out Xtrabackup? It does a binary backup which might actually be slower than mysqldump because it attempts to ensure integrity. Once the backup is complete the restoration on another server is very fast.

It was designed to work on the XtraDB storage engine but since that engine is fully compatible with InnoDB it works for InnoDB tables too. It includes a perl script for grabbing MyISAM tables with all the usual caveats about MyISAM not being transactional and such.

1

mysqldump is the best choice for all smaller servers without big load and performance needs.

mysqldump --all-databases --single-transaction > all_databases.sql

Here is an article about the different backup methods, including replication.

Please don't put Username (probably root) and password in a script, use the .my.cnf file of the user running the backup script (probably also root). Make it read-protected to the outside world (chmod og-rwx .my.cnf)

For reasonably busy sites, use mk-parallel-dump or make a snapshot at filesystem level (using LVM), backup the data files and remove the snapshot.

2
  • Yes I agree that user etc should be in another file...in the script I used a direct example but its safer to put the credentials somewhere else
    – Rodent43
    Oct 6, 2009 at 13:17
  • Don't get me wrong, didn't mean to offend - just wanted to to advise in general.
    – PEra
    Oct 6, 2009 at 20:42
0

Using mysql replication to a secondary host would provide simple backup, as well as the ability to recover quickly in the event of a disaster. PLEASE NOTE that replication alone does not provide a satisfactory backup in all cases. If you were to accidentally drop all tables in your database, MySQL will happily replicate that change to the 2ndary host. It does provide a continuous duplicate of the master database, which can be stopped, dumped, etc, without affecting performance on the primary machine.

If your mysql data directory is located on a logical volume, you can use LVM snapshotting for quick, consistant, filesystem backups. This is most reliable if you are using innodb tables. Be aware that an LVM snapshot can reduce write performance on the partition being snapshotted, doing this on the secondary machine is advised.

1
  • You can also use do a filesystem snapshot on an XFS volume, with or without LVM.
    – user5336
    Oct 6, 2009 at 14:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .