Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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??

share|improve this question
    
what size is your MySQL data directory? –  Andy Oct 6 '09 at 13:56
    
the database is large means it has millions of records there! –  hkshambesh Oct 6 '09 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! –  Mark Henderson Mar 5 '10 at 21:43

5 Answers 5

up vote 2 down vote accepted

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.

share|improve this answer
    
+1 - same method here (mysqldump) - though I keep a one-week rollback and a monthly one, just to be sure :) –  petre Oct 6 '09 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 '09 at 12:03
1  
This is quite impractical for large databases. –  Massimo Feb 23 '12 at 19:38

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.

share|improve this answer

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.

share|improve this answer
    
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 '09 at 13:17
    
Don't get me wrong, didn't mean to offend - just wanted to to advise in general. –  PEra Oct 6 '09 at 20:42

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.

share|improve this answer

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.

share|improve this answer
    
You can also use do a filesystem snapshot on an XFS volume, with or without LVM. –  user5336 Oct 6 '09 at 14:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.