Database Reference
In-Depth Information
NOTE
You can put the options in any order after the
mysqldump
command. You just have to put any values
you want to pass to an option immediately after it. The only other order requirement is the final piece,
the shell redirect — but that's actually a shell operator and isn't part of the
mysqldump
command.
Basically, the ordering of options is very much like any command.
MySQL utilities used to offer shorter, single-hyphen options, such as
-u
for
--user
. But
the short names are being deprecated and may not be available in the future.
TIP
When making backups of InnoDB or other transactional tables with
mysqldump
, it's best to includethe
-
-single-transaction
option. This will keep the data more consistent. It won't change between
the tables until the dump is finished. However, that option will cancelthe
--lock-tables
option.
This means that a backup of MyISAM tables in the same database could be inconsistent. You can avoid
this potential problem by either using the same storage engine for all of the tables in a database, or mak-
ing separate backups of InnoDB tables and MyISAM tables.
Backing up all of the databases at once with
mysqldump
may result in one large dump file.
For smaller databases and as part of a regular routine, this is fine and managable.
However, for larger databases, this method could take much longer to complete the
backup, disrupting traffic while tables are locked, and later it may make restoration both-
ersome. Instead, you can construct a more adept backup method. For instance, it might be
useful to perform a separate backup for each large database, leaving several smaller dump
files. You could also back up larger and more active databases during slower traffic times
so that you don't diminish database and web services. We'll discuss later how to specify
which databases to back up and some backup strategies. For now, let's take some time to
become familiar with dumpfiles.
WARNING
There's a security concern about making backups of all of the databases, as it could include the
user
table in the
mysql
database. This table contains usernames and passwords. You can eliminate it from a
backup by adding
--ignore-table=mysql.user
to the
mysqldump
at thecommand line when
creating the dump file. To make a backup occasionally of just the
mysql.user
, though, you might use
a different user account for the backup and write the dump files to a protected directory or somewhere
safe.
