Backing Up and Restoring Databases - Learning MySQL and MariaDB - page 342

keys again. When that occurs, the table will be indexed. This method uses a special al-
gorithm that is generally much faster when performed for the entire table at once, rather
than when each row is inserted.
TIP
Conditional components like DISABLE KEYS are included if the --disable-keys optionis set by
default on the server. If you don't see them in the dump files created by mysqldump , it isn't set by default
on your system. It can be added when mysqldump is executed at the command line, or it can be added to
the MySQL configuration file under the [mysqldump] heading.
The last line of the previous excerpt issuesan UNLOCK TABLES statement to unlock the
tables that were locked at the start of this section of the dump file.
In summary, the basic pattern for each table is to establish the table structure and then ad-
dress the data. To establish the table structure, the dump file generally contains SQL state-
ments to drop the table, set related temporary variables, re-create the table, and then re-
store the variables. To deal with the data when it re-creates the table, it locks the table, dis-
ables the keys, inserts all of the data, and then re-enables the keys and unlocks the table.
This pattern is repeated for each table in the database. When the command has finished
dumping all of the tables in the database, it will proceed to the next database, and continue
until it has finished all of the databases, because in this example it was instructed to make
a backup of all of the databases.
The contents of a dump file created by mysqldump can vary depending on the version of
the utility and the default settings. It also can vary depending on the databases it's dump-
ing and what instructions are given with the options at the command line. However, this
review of an example of a dump file should give you a good sense of how to read one.
Let's return now to makingbackups with mysqldump .
Backing Up Specific Databases
Before weconcerned ourselves so much with the contents of the dump file, we were ex-
perimenting with making backups, learning how to back up all databases on the server.
However, you may want to export only one database, or only specific ones. Let's see how
to do that.
To export only one database and not all, instead ofusing the --all-databases op-
tion, usethe --databases option followed by the name of the database. Try making a
back-up of just the rookery database by entering the following on your server from the
command line: