Database Reference
In-Depth Information
the
--tables
because
mysqldump
assumes that any nonreserved words after the data-
base name are the names of tables. So the previous example can be entered like this:
mysqldump --user=
admin_backup
--password --lock-tables \
birdwatchers humans
>
birdwatchers-humans.sql
Although this command is simpler, the previous one makes it easier to discern what is a
database name and what is a table name.
Let's add another table to the example here, but from another database. Suppose that our
boss wants us also to backup the
birds
table in the
rookery
database. This possibility
is not allowed with
mysqldump
: you can't list two databases with the
--tables
option.
You would have to run
mysqldump
twice. This would create two dump files. If you want
one dump file containing both tables, you could do something like this:
mysqldump --user=
admin_backup
--password --lock-tables \
--databases
rookery
--tables
birds
>
birds-humans.sql
mysqldump --user=
admin_backup
--password --lock-tables \
--databases
birdwatchers
--tables
humans
>>
birds-humans.sql
Here we're executing
mysqldump
twice, but the second time we're setting the redirect
(i.e., >>) to append to the dump file instead of creating a fresh one. The dump file will
have a comment in the middle of it saying that the dump is completed and then another
starting header for the second dump. Because those are just comments, they will have no
effect if you use the combined dump file to restore the two tables. Nor will modifying
variables twice using
SET
during the execution of the combined dump file. So it's fine to
append to a dump file like this.
The
mysqldump
utility is easy to use and very powerful. We've touched on many options
that may be used with it. However, there are many more options. You can find these on-
line on the MySQL and MariaDB websites or in my book,
MySQL in a Nutshell
(O'Reilly).
One of the problem with dump files, though, is that you can clobber your databases when
you use them to restore data if you're not careful. Therefore, you should practice restoring
dump files on a test database or a test server. Do this often so that you will be comfortable
with making and restoring backups. Don't wait until you've lost data and feel panic to re-
store it, because you might make unrecoverable errors or even find out that you haven't
been backing up your data properly. Develop these skills in advance and in a safe and con-
trolled way. To learn how to restore dump files, see the next section on restoring data from
backups.
