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'm planning on taking backups of all MySQL databases on a remote production server. The combined database size is around 2 GB. Most of the tables are InnoDB ~1.8 GB and the rest are MyISAM.

Now I've gone through most of the questions on Serverfault and DBA.Stackexchange about taking backups of hybrid databases and almost all opinions are about third party tools like Percona Xtrabackup or about converting MyISAM tables into InnoDB and then running mysqldump with --quick --single-transaction options or taking backup from a replication system.

In my case not all databases are replicated on a slave system hence I cannot run mysqldump on a slave machine, also I'll be running mysqldump nightly either via a script or as a cronjob and I know that acquiring a global read lock all the databases while taking mysqldump won't be a problem as the number of DML statements executing at that time of the night will be extremely low.

So I'm thinking about including following options with mysqldump and I'd really appreciate if someone could help verify/rectify these options.

--extended-insert  --all-databases  --add-drop-database --lock-all-tables  --log-error=file_name --skip-opt
share|improve this question
    
Hello @heinlein, I made some improvements to the script. Using case instead of if elif then fi –  Craig Efrein Jul 29 at 8:09

1 Answer 1

up vote 0 down vote accepted

It would make more sense in my opinion to do a backup by table instead of by database. Here is a script I use to make backups of databases that contain MyISAM and InnoDB tables.

#!/bin/bash
USER="some_user"
PASSWD="some_password"
STAMP=$(date +"%d-%m-%y-%H:%M:%S")
HOST="some_host"
db="some_database"
FILESTAMP=$(date +"%d%m%y")
FILE="/some_directory/some_file-$STAMP.sql.gz"
Port=3306

TABLE_DUMP=$(mysql -u $USER -P$PASSWD -h $HOST -Bse "SELECT table_name FROM information_schema.tables where table_schema = '$db' and engine is not null order by table_type, table_name;")
for table_name in $TABLE_DUMP
do      
    table_engine=$(mysql -u $USER -P$PASSWD -h $HOST -Bse "SELECT engine FROM information_schema.tables where table_schema = '$db' and table_name = '$table_name';")
    case "$table_engine" in
        InnoDB ) 
            echo "$table_name InnoDB\n" 
            mysqldump --skip-opt --disable-keys --create-options --single-transaction --quick --extended-insert --add-drop-table -P $Port -u $USER -P$PASSWD -h $HOST $db $table_name | gzip -9 >> $FILE
        ;;
        MyISAM) 
            echo "$table_name MyISAM\n" 
            mysqldump --opt -P $Port -u $USER -P$PASSWD -h $HOST $db $table_name | gzip -9 >> $FILE
    esac

    #register all table names
    skiptables="$skiptables --ignore-table=$db.$table_name" 
done

# View
mysqldump -P $Port -u $USER -P$PASSWD -h $HOST $db $skiptables | gzip -9 >> $FILE
share|improve this answer

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.