2

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!

0

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.

  • Hi Ondra, Thanks for the script even if I was not looking at that now it will be really helpful. Is the raw data file backup a solution that is cost efficiency compare to this backup for each database? Or should I only consider this second option? – maxime_039 Dec 6 '15 at 12:33
  • There is problem with raw datafiles - they are changed very often. You have to backup those files from server with mysql stopped to svoid changing files and corrupted backup. If you can do this, backup of raw data files are the best and restore is quick. But if you cannot make some server offline for a while, AFAIK you cannot make working backup with copying of raw datafiles and you have to use mysqldump or something similar. – Ondra Sniper Flidr Dec 6 '15 at 23:42
  • Thanks for the input. I'm working with replication server/slave. Then I suppose that it's possible to do it by turning off the slave during the backup period. imo both raw files backup and backup for each databases are needed in my situation. – maxime_039 Dec 7 '15 at 4:48
  • Yes, if you can stop slave for time of backup this will work well. – Ondra Sniper Flidr Dec 7 '15 at 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.