Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. 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've 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

Your Answer


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