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
  • Hello @heinlein, I made some improvements to the script. Using case instead of if elif then fi Commented Jul 29, 2014 at 8:09

1 Answer 1


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.

STAMP=$(date +"%d-%m-%y-%H:%M:%S")
FILESTAMP=$(date +"%d%m%y")

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
    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
            echo "$table_name MyISAM\n" 
            mysqldump --opt -P $Port -u $USER -P$PASSWD -h $HOST $db $table_name | gzip -9 >> $FILE

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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