Printable View
I have read about and tested the various backup/recovery methods for mySQL. From my testing, it seems that InnoDB tables are the most transaction safe but are the hardest to backup while the database is up. Has anybody ever used the InnoDB hotbackup scripts in a production environment? How do most people backup their mySQL production environments?
I do not use innodb, but as for the way we perform our database backup (myisam) and recovery, we simply use mysqldump to dump all of the databases. We then compress the files and save them. Then if we need them recovered, just unzip them and import them using the "mysql" command.
Little script that I grabbed pieces here and there that I run from crontab.
I'm using LFTP to do the ftp of the backup to an offsite server for additional backup. If you have /usr/local/bin in your path you don't have to export it in the script.
#! /bin/bash
mydate=$(date +%m%d%Y%I%M%p)
PATH=$PATH:/usr/local/bin
export PATH
/usr/local/mysql/bin/mysqldump --flush-logs --opt -u USERNAME -pPASSWORD DBNAME > /dir1/dir2/dir3/backup.sql
cd /dir1/dir2/dir3/
if [ -e backup.sql ]
then
tar -czvf $mydate-sqlback.tar.gz backup.sql
fi
if [ -e $mydate-sqlback.tar.gz ]
then
lftp <<EOF
open FTP SERVER
user USERNAME PASSWORD
pwd
put $mydate-sqlback.tar.gz
bye
EOF
fi
if [ -e backup.sql ]
then rm /dir1/dir2/dir3/backup.sql
Nobody is doing binary backups of their datafiles?
errr... well no. I use this to backup my db's for hosted websites.
When you say "binary backups", I hope you don't mean the binary logs as they just keep track of queries that make updates and/or changes.
If you are keeping some other form of binary backup, if you could please shed some light into what you are doing and the benefits of doing so, that would be greatly appreciated. Thanks.
A binary backup of your data files is the quickest way to get your database back in case of a disaster. Re-creating your database structures and re-loading the data for anything bigger than about a 10G database would just take too long to meet any kind of SLA.