How can I back up and replicate a large MySQL database?
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.
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.
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.
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.