How can I back up and replicate a large MySQL database?

Question

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

Answer 1

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.

Answer 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.

Answer 3

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.

Answer 4

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.

Answer 5

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.