MySQL Forums
Forum List  »  Newbie

Re: what should i do when backup ?
Posted by: Rick James
Date: February 02, 2010 12:10AM

--opt is a shorthand for the combination of several options. Note that after saying --skip-opt, he proceeds to add most of the options that he just turned off. The two missing ones have to do with locks. A guess: mysqldump (and --opt) was probably invented back when there was only MyISAM. Table locks were (are) advisable for that engine. With InnoDB, --single-transaction is probably a better option for InnoDB; is he assuming InnoDB only?

Does any of this matter? If you have a small system and no one is modifying the data during the mysqldump, then no.

If you have a huge database, --extended-insert can easily speed up the reload by 10x.

If you might have concurrent INSERTs/UPDATEs/etc, then some form of locking is strongly advised, else you could get an inconsistent dump.

See the discussion of --lock-tables in
http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html

See also --lock-all-tables

For a huge table, --quick (a misnomer) is essential.

Read the discussion about --single-transaction if you have a mixture of InnoDB and MyISAM tables.

I prefer to stop mysqld, copy all the files, then restart mysqld.

Options: ReplyQuote


Subject
Written By
Posted
Re: what should i do when backup ?
February 02, 2010 12:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.