Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Looking for a strategy or tool to deal with recovering a single database to a point-in-time in a busy replicated system.

I have 12 databases running on 2 MySQL 5.0.77 servers in master-slave replicated configuration. A full dump is taken of the read-only slave daily, and there are incremental SQL dumps available, with these backups off-site and the replication status is monitored.

Edit: Tables are a mixtures of InnoDB and myISAM, hence engine specific solutions are not available.

So given a complete failure of the master server, I can break the replication and promote the slave server, I also have the option of rebuilding a new server and configuring from the offside FULL backup, and then apply the differentials taken hourly from the slave.

However I am concerned how to deal with partial failure, or failure of a single database. I can think of 2 scenarios that are quite likely;

  1. database 7 (for example) becomes corrupted, continues to serve some requests until someone notices that it is broken, or alerts from the log files...
  2. Some query like drop database, drop table, "update where..." type query borks a single database, or some subset therein.

At the moment I have a bunch of FULL dumps as FULL-$DATE-all-databases.sql.gz files , and differentials that can be applied to the FULL dumps as DIFF-$DATE-all-databases.sql.gz

To restore database 7 to some point-in-time would require a grep through the FULL and DIFF files, and manual application of that sql.

How should I proceed in order to make it possible to be able to recover to one of the previous DIFF dumps to the master database?

DO I need to backup to individual database files, ie

mysqldump --databases "database1" | gzip > database1.sql.gz
mysqldump --databases "database2" | gzip > database2.sql.gz
mysqldump --databases "database3" | gzip > database3.sql.gz

rather than..

mysqldump --master-data --lock--all-databases --all-databases | gzip > all-databases.sql.gz

If I go for separate mysqldump files, what happens to the master data binary log, and should I even be setting --master-data for the master server recovery dumps?

share|improve this question
add comment

migrated from serverfault.com Jan 19 '12 at 17:27

This question came from our site for professional system and network administrators.

1 Answer

up vote 4 down vote accepted

If all your database use InnoDB only, I have some good news.

You should be to dump all the database in parallel from a slave.

In fact, you can force all the databases into the same point-in-time.

First thing to remember about a the Slave is that it is not not required to have binary logging enabled if it is not a Master for other Slaves.

You cannot use --master-data option for parallel dumps because each dump will have a different position written at line 22 of each dump file. It is better to record the Master's last log file and position the Slave executed using SHOW SLAVE STATUS\G. That way, all the databases have the same point-in-time position.

You can collect all databases and script the parallel dump of all the database.

DBLIST=/tmp/ListOfDatabasesToParallelDump.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')" > ${DBLIST}

for DB in `cat ${DBLIST}` 
do 
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz & 
done 
wait 

mysql -h... -u... -p... -e"START SLAVE;"

If there are simply too many databases, dump them 10 or 20 at a time as follows:

DBLIST=/tmp/ListOfDatabasesToParallelDump.txt
SSS=/tmp/ShowSlaveStatusDisplay.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')" > ${DBLIST}

COMMIT_LIMIT=20
COMMIT_COUNT=0    
for DB in `cat ${DBLIST}` 
do 
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz & 
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done 
wait 
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

mysql -h... -u... -p... -e"START SLAVE;"

If you need to recover a single table, you can parallel dump tables 20 at a time in size order.

Try this:

TBLIST=/tmp/ListOfTablesToParallelDump.txt
SSS=/tmp/ShowSlaveStatusDisplay.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY data_length" > ${DBLIST}

COMMIT_LIMIT=20
COMMIT_COUNT=0    
for DBTB in `cat ${TBLIST}` 
do
    DB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print $2}'`
    DUMPFILE=$DB-{DB}-TBL-${TB}.sql.gz
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} ${TB} | gzip >  ${DUMPFILE} & 
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done 
wait 
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

mysql -h... -u... -p... -e"START SLAVE;"

Now that you have scripts to dump databases or individual tables, you can load that data at your discretion. If you need to get SQL executed from the binary logs on the master, you can use mysqlbinlog and give it the position ot datetime and output the SQL to other text files. You just have to perform due diligence to find the amount of data you need from whatever timestamps the bnary logs have. Just remember that every binary log's timestamp in the OS represents that last time it was written.

share|improve this answer
    
brilliant answers thanx. I think having a read-only slave on xfs gives me loads of options, and your scripts has really helped. –  Tom H Jan 20 '12 at 1:00
    
in the scenario where I need to recover a massive table to the master from a backup off the slave. I just have to rebuild the table on the master, and have all the changes replicate down to the slave, even if that is 20GB of data? Would the process be 1) disable keys, 2) drop the table on master and slave 3) restore the table the master 4) enable keys --- and have the master replicate all 20GB down to the slave? –  Tom H Jan 20 '12 at 1:01
    
If these databases are NOT innodb, can I still dump them in parallel? –  Tom H Jan 20 '12 at 1:34
    
Yes, if you 1) schedule downtime, 2) run service mysql restart --skip-networking, 3) perform the parallel dump, 4) run service mysql restart. Then reload the tables you need. –  RolandoMySQLDBA Jan 20 '12 at 3:15
    
presumably if the purpose of the restart was to prevent network connections writing to the database, then I could achieve the same effect by using iptables i.e. iptables -I INPUT -p tcp --dport 3306 -j DROP on eth0 and lo –  Tom H Jan 21 '12 at 16:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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