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.

I was reading that Oracle databases have cold, warm, full and redo backups.

Questions

  • How is it possible that MySQL doesn't have as many options?
  • I have read that MySQL has cold backups (it puts the DB into a lock mode by itself); are there any other methods?
  • How can I implement methods similar to those that Oracle has in MySQL?
share|improve this question
    
Because product A (Oracle) is some years or decades older than product B (MySQL)? Some options here: MySQL Backup Primer –  ypercube Apr 17 '12 at 19:54
    
And the official page: Backup and Recovery Types –  ypercube Apr 17 '12 at 20:00
    
Why is it downvoted? Apart from the obviously horrible English, I don't see how this question is invalid. –  ivanmp Apr 18 '12 at 15:00
    
@ivanmp if you can decipher it please edit it into something comprehensible –  Jack Douglas Apr 18 '12 at 15:24
    
@JackDouglas done. –  ivanmp Apr 18 '12 at 21:44
add comment

2 Answers 2

up vote 2 down vote accepted

There are two ways to do backups which are not standard to MySQL

COLD BACKUP

You could perform parallel mysqldumps in conjunction with FLUSH TABLES WITH READ LOCK

I wrote a post about that a long time ago : How can I optimize a mysqldump of a large database?

Here is a sample script to use a global read lock and a monolithic mysqldump

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

Here is a sample script to use a global read lock and parallel mysqldumps 20 DBs at a time

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysql ${MYSQL_CONN} -AN -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > /tmp/ListOfDatabases.txt

COMMIT_COUNT=0 
COMMIT_LIMIT=20 
for DB in `cat /tmp/ListOfDatabases.txt` 
do
    mysqldump ${MYSQL_CONN} --single-transaction --hex-blob --routines --triggers ${TBL_EXCLUSION_LIST} ${DB} | gzip > ${DB}.sql.gz & 
    (( COMMIT_COUNT++ )) 
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ] 
    then 
        COMMIT_COUNT=0 
        wait 
    fi 
done 
if [ ${COMMIT_COUNT} -gt 0 ] 
then 
    wait 
fi 

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

LUKEWARM / CHILLY BACKUP

A more risque method for backing up mysql is to do rsyncs. The basic concept is this:

  • Step 01) About 1 hour beforehand, run SET GLOBAL innodb_max_dirty_pages_pct = 0; (OPTIONAL if your data is all InnoDB)
  • Step 02) Make sure DB2 has mysql already shutdown
  • Step 03) rsync /var/lib/mysql for DB1 to /var/lib/mysql on DB2
  • Step 04) Repeat Step03 until two consecutive rsyncs are about the same time
  • Step 05) shutdown mysql on DB1
  • Step 06) rsync /var/lib/mysql for DB1 to /var/lib/mysql on DB2
  • Step 07) startup mysql on DB1

From here, you can startup mysql on DB2 and perform mysqldumps on DB2 to your heart's content.

Rather than just putting the code here, I will share with you links I posted in the past on the concept of this (This concept I use periodically for making new Slaves for Giant Masters)

share|improve this answer
add comment

Because MySQL is not as mature.

But there are options, such as Percona's (free) hot backup for innodb:

http://www.percona.com/software/percona-xtrabackup/

There is also MySQL Enterprise Backup from Oracle, but it is not free.

share|improve this answer
1  
percona is a very good option –  maniat1k Apr 18 '12 at 15:49
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.