You are going to find this shocking, but you only need one major option : --opt
What is --opt ?
This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.
Because the --opt option is enabled by default, you only specify its converse, the --skip-opt to turn off several default settings. See the discussion of mysqldump option groups for information about selectively enabling or disabling a subset of the options affected by --opt.
Since --opt is already enabled, you do no need to specify --opt. Notwithstanding, you may need some necessary options that are not included.
Run this query on your database
SELECT engine,COUNT(1) TableCount
FROM information_schema.tables
WHERE engine IN ('InnoDB','MyISAM')
AND table_schema NOT IN ('information_schema','mysql','performance_schema')
GROUP BY engine;
If you have all InnoDB tables, then you need to specify --single-transaction. This will automatically deactivate --lock-tables and allow you to dump all table in the same point-in-time and allow new writes to come at the same time.
If you have one or more MyISAM tables, then you need to specify --lock-all-tables. This will automatically deactivate --lock-tables, deactivate--single-transaction, lock all tables across all databases, then create the dump. Writes to InnoDB tables can still occur, but they will just get queued up until the locks are released. Any DB Connection attempting writes to any MyISAM tables will actually get suspended until all locks are released.
Run this query : SELECT COUNT(1) Number_Of_Stored_Procedures FROM mysql.proc;
If Number_Of_Stored_Procedures is greater than zero, use --routines.
Run this query : SELECT COUNT(1) Number_Of_Triggers FROM information_schema.triggers;
If Number_Of_Triggers is greater than zero, use --triggers.
CAVEAT : Please Do Not Use --order-by-primary for dumping all databases because it can potentially make BTREE indexes rather lopsided on reload. --order-by-primary should only be used when dumping an individual table that you know has an integer primary key and will have lots of range scans from your application.
If you need more creative types of mysqldump backups, see my old post How can I optimize a mysqldump of a large database?.
Please read all the options for mysqldump.
UPDATE 2014-12-29 09:44 EST
I have updated my mysqldump command (please see my edit). I have a last question though. Do you think it would worth using all of the following arguments as well? --add-drop-database\ --add-drop-table\ --complete-insert\ --delayed-insert\ --tz-utc
Looking at your comment and at your latest edit, let's look at each of these options and see if you need any of them
- --opt : I already said you do no need to specify it because it is enabled by default.
- --delayed-insert : I adamantly stay away from this with an all InnoDB database. In fact, I adamantly stay away from this PERIOD !!!. Since 1) it is possible for INSERT DELAYED can lose data, 2) it is converted to INSERT for MySQL Replication slaves, 3) there is an open bug report about it use with triggers back in MySQL 5.6 and is not considered a bug, 4) it is deprecated in MySQL 5.6, and 5) Morgan Tocker (well-known MySQL Guru) foresaw its deprecation back in 2012, you should forget this option ever existed. Don't ever, ever (infinity) use it !!!
- --complete-insert : This will use
INSERT INTO tblname (colnam_1,colnam_2,...colnam_n) VALUES ... instead of INSERT INTO tblname VALUES .... This could bloat the mysqldump if there are many columns in the table definition and many rows in the table. Don't use it.
- --add-drop-table : Since --opt enables it for you, you don't need to specify it.
- --add-drop-database : If you drop --add-drop-database, it just makes the
DROP TABLE IF EXISTS (which was added by --add-drop-table) go faster. Not using it simply lets DROP TABLE IF EXISTS occur for each table. Thus, using --add-drop-database is a matter of personal choice.
- --tz-utc : If you plan to restore data to the same server you backup up from, you do not need --tz-utc. If you restore the data to another data center in a different time zone and ...
- if you want to keep the same timezone you backup from, you don't need --tz-utc.
- if you want the data to use the time zone of a new data center, you need --tz-utc.
- EXAMPLE : Say you backup a database in New York. That's EST. If you have another data center in Seattle, that would be PST. If you want the New York Backup to be restored in Seattle and you want the timestamps in the database to still represent New York, you do not want to use --tz-utc.
- --default-character-set
Adjusting your latest edit, this is what you particularly need
mysqldump\
--host=localhost\
--port=3306\
--databases ****\
--user=****\
--password=****\
--single-transaction\
--routines\
--triggers\
--events\
--add-drop-database\
> dump/test.sql
Again, I say please read all the options for mysqldump.
