Database Reference
In-Depth Information
mysqldump --user=
admin_backup
--password --lock-tables \
--verbose --databases
rookery
>
rookery.sql
This is basically the same as the example that dumped all of the databases, except that
we've specified the database to be exported,
rookery
. As mentioned before, you may
want to make separate backups of databases to reduce the load on a busy server and to
make restoration more manageable. Incidentally, if for some reason you want to make a
backup of a database's schema without the data, you can usethe
--no-data
option. The
command would then dump only the database and table schemas and not the rows of data.
You may have noticed in the previous example that we addedthe
--verbose
option.
This option instructs the utility to display messages regarding each major step in the pro-
cess of querying the database and creating the dump file. For our database, running this
command produces messages like this:
-- Connecting to localhost...
-- Retrieving table structure for table bird_families...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table bird_images...
...
-- Disconnecting from localhost...
Sometimes these messages can be useful, especially when there are problems, to know
which tables are dumped successfully and when problems occur.
To export multiple databases, just enter them after the
--databases
option, separated
by spaces — not commas as you might think. Try executing the following on your server
to back up the
rookery
and the
birdwatchers
databases:
mysqldump --user=
admin_backup
--password --lock-tables \
--databases
rookery birdwatchers
>
rookery-birdwatchers.sql
This will dump the
rookery
and the
birdwatchers
databases into one file named
rookery-birdwatchers.sql
. Because those two databases are related and there aren't any
other databases associated with them, this can be useful. We can copy this lineinto
crontab
or some other scheduling utility on the server to run automatically each day.
However, each command that runs will overwrite the dump file from the previous day. If
something happens and data is deleted accidentally, but we don't discover it for a few
days, we won't be able to restore that data from the backup. To allow for this possibility,
we need to create a new dump file each day with a unique name so we don't overwrite the
previous dump files. Unless we intend to initiate the backups manually, we need to be cre-
ative and automate the process. We can accomplish this twist with a shell script.
