12

After some search I ended up with the following settings:

    mysqldump\
    --host=localhost\
    --port=3306\
    --databases ****\
    --user=****\
    --password=****\
    --default-character-set=utf8\
    --add-drop-database\
    --add-drop-table\
    --add-locks\
    --complete-insert\
    --extended-insert\
    --lock-all-tables\
    --create-options\
    --disable-keys\
    --quick\
    --order-by-primary\
    --set-charset\
    --tz-utc\
    > dump/test.sql

So far so good but I have some questions regarding the parameters as well as the execution.

Regarding my first concern, I just want to make sure that all of them are required and do not cause any conflicts when used together. Ultimately, I would like to make a very robust and consistent dump file, with millions of records, that creates tables, databases, and inserts data. The good thing is that making the database not-available for a while is not a problem for me. My only goal is to make a robust and consistent dump file.

Regarding my second concert, I would like to know how to get informed when then command goes wrong and if so throw an exception.

Any ideas?

EDIT

This is my updated mysqldump command based on RolandoMySQLDBA's feedback.

mysqldump\
--host=localhost\
--port=3306\
--databases ****\
--user=****\
--password=****\
--default-character-set=utf8\
--opt\
--single-transaction\
--routines\
--triggers\
--events\
--add-drop-database\
--add-drop-table\
--complete-insert\
--delayed-insert\
--tz-utc\
> dump/test.sql

closed as primarily opinion-based by mustaccio, Erik Darling, Mr.Brownstone, hot2use, Vérace Nov 30 '17 at 10:27

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Read each arguments and its benefits and choose which would suite you. Also get to know scripting to trigger incase of failures. – Mannoj Dec 28 '14 at 11:06
  • Cool. But this doesn't really answer my question. For instance, which of them do YOU always use? – user706838 Dec 28 '14 at 14:50
  • Forgive my ignorance, this question is not about recommending a book or a website. So I think it shouldn't be opinion based type of question. That been said some answers would certainly be of a more quality depending on the expertise and backed up by tests. In my opinion, the question is perfectly stated with a well defined objective. – user10089632 Jan 20 '18 at 8:04
26

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.

  • About --add-drop-database: doesn't this also ensure there aren't any other random tables in your database that aren't in your dump? If someone manually created a random test table in your database, then it would still exist after reloading from the dump without this option. So, it depends on whether you want that or not. Right? – jschultz410 Feb 20 at 18:50

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