How to Back Up and Replicate a Large MySQL Database

backupdata-recoveryMySQLmysql-replication

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

Best Answer

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.

Related Topic