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

INSERT INTO `bird_families` VALUES
...
/*!40000 ALTER TABLE `bird_families` ENABLE KEYS */;
UNLOCK TABLES;
After the comment appearsa LOCK TABLES statement to lock the bird_families
table. It includes the WRITE option so that the data in the table cannot be changed during
the restoration of the table. Users can't read the table either. Another thought may have
occurred to you now: mysqldump is write-locking tables one at a time, as needed. That
may be what you want, making other tables available for reading and writing when
they're not being dumped. However, this may cause a problem with the consistency of the
data.
For example, suppose during backup is at the point where it has preserved the contents of
the humans table but not the bird_sightings table in the birdwatchers data-
base. At this point, you decided to delete someone from the humans table along with
entries in the bird_sightings table for that person. After that, mysqldump backs up
the bird_sightings table. If you were later to restore the entire birdwatchers
database, you would have an entries in the bird_sightings table for a person who
isn't listed in the humans table.
If a database isn't very active, the previous scenario is unlikely. However, if you want to
be assured of the consistency of your data, when executing the mysqldump utility, you
could add the --lock-tables option. This locks all tables in a database before back-
ing it up, and leaves them locked until the backup of the database is completed. When
making a backup of multiple databases, this option still locks only the tables in one data-
base at a time, releasing them before starting the next database. If you're concerned about
consistency between databases — that is to say, if data in one database depends on data in
another database — usethe --lock-all-tables option to lock all of the tables in all
of the databases until the dump is completed.
In the previous excerpt, the LOCK TABLES statement is followed by a conditional state-
ment (i.e., ALTER TABLE...DISABLE KEYS ) to alter the bird_families table so
as to disable the keys. This can save time when thetable is restored. When the INSERT
statement that follows — truncated in the example to save space — is executed, data will
be inserted much faster if MySQL doesn't have to index all of the data as it's inserted. In-
stead, another ALTER TABLE statement will be executed conditionally to enable the