Best practices to backup mysql databases

Question

This question concerns best practices for mysql backup with replication and large number of databases. I'm looking for your point of view and suggestions :)

I - Current situation

I have two databases master/slave (mysql replication) on each one of my app servers. I have one database by customer and one "admin" database.

Currently I get a total of 500 databases and soon it will exceed 1K.

Each databases includes around 100 tables and for the moment and I arrive to around 10mo to 30mo by database after mysqldump operation (uncompressed).

II - What I need

I'm now looking to backup these databases. The main goal is for sure to answers to server crash and to build a disaster recovery process, but before all that I'm more worried about my customers that messed up themselves their data. To sum up I want to be able to:

• Backup and recover all databases in case of servers crash or attack. (And then to perform a disaster recovery).
• Backup all databases and recover specific database to restore a fresh copy (Let say not older than 24 hours) when a customer mess up with his account's data.

III - My current ideas and my questions

1 - Backup location

The backup of the database should not be done on the same server than the database itself. As I'm using replication on two different servers, do you think it's a good practice to backup my data on the other server? Or what would be the other solutions as a dedicated server and why?

2 - Backup process

First, I will backup from the slave database following the principle of stopping temporary the replication and making a backup in "read only".

I think that I have here to consider two different types of backup. The first backup will be a backup of raw data files that I will perform let say every week (as I'm using replication I'm quite safe). And the second type of backup would be a mysqldump of each databases that I will perform every 24 hours. Is it something that seems logical?

Is there any others points that I should consider? Is incremental backup something interesting for me?

Thanks a lot!

Answer 1

Best practices depends on what do you need to recover. You definitely should backup to different storage, ideally in different geolocation. I have this little script, which detects all databases and backup each database to separated file, so I can recover only one database.

#!/bin/bash
echo "Starting..."
ROOTDIR="/backup/mysql/es2"
YEAR=`date +%Y`
MONTH=`date +%m`
DAY=`date +%d`
HOUR=`date +%H`
SERVER="mysql.local"
BLACKLIST="information_schema performance_schema"
if [ ! -d "$ROOTDIR/$YEAR/$MONTH/$DAY/$HOUR" ]; then
    mkdir -p "$ROOTDIR/$YEAR/$MONTH/$DAY/$HOUR"
fi
echo "running dump"
dblist=`mysql -u backuper -pXXXXXXXXXXX -h $SERVER -e "show databases" | sed -n '2,$ p'`
for db in $dblist; do
    echo "Backuping $db"
    isBl=`echo $BLACKLIST |grep $db`
    if [ $? == 1 ]; then
        mysqldump --single-transaction -u backuper -pXXXXXXXXXX -h $SERVER $db | gzip --best > "$ROOTDIR/$YEAR/$MONTH/$DAY/$HOUR/$db.sql.gz"
        echo "Backup $db ends with return code $?"
    else
        echo "Database $db is on blacklist, skip"
    fi
done

echo "dump completed"

Each backup is keeped in directory structure YEAR/MONTH/DAY/HOUR/DB_NAME.sql.gz. You can specify which DB you don't want to backup in variable BLACKLIST.