Database Reference
In-Depth Information
ticipate in the examples, download those two dump files and execute the following from
the command line in the directory where you've placed them:
mysql --user=
admin_maintenance
--password --database=rookery <
birds-simple.sql
mysql --user=root --password --silent \
--execute="SELECT COUNT(*) AS '' FROM rookery.birds_simple;"
If you didn't get an error message, the second line should return the number of rows con-
tained in the
birds_simple
table. It should be about 28,892. You may have noticed
that I added the
--database
option,setting it to
rookery
. When I generated the
dump file, I dumped only the
birds_simple
table. As a result, the dump file does not
contain a
USE
statement and the table name isn't prefaced with
rookery
. So the SQL
statements are not specific to any database. By adding it at the command line like I did
here, you can make MySQL execute all SQL statements contained in the dump file in that
database.
Let's move on to messing with the
birds_simple
table. Process the
birds-simple-
transactions.sql
file, which will add and delete many rows:
mysql --user=
admin_maintenance
--password \
--database=rookery <
birds-simple-transactions.sql
mysql --user=root --password --silent \
--execute="SELECT COUNT(*) AS '' FROM
rookery.birds_simple
;"
The count of the number of rows should now be about 296 fewer. The
birds-simple-trans-
actions.sql
dump file contains a couple of
DELETE
statements that delete a lot of rows
based on the
WHERE
clause. There are also a couple of
INSERT
statements that add more
rows to the same table.
Now we're ready to go through the steps to restore based on a point in time. To restore
everything to a specific point in time, we need to start from the last good backup. In this
case, we'll start by restoring the
birds-simple.sql
dump file:
mysql --user=
admin_maintenance
--password \
--database=rookery < birds-simple.sql
That should have restored the
birds_simple
back to where it was at the time that
dump file was generated. If you want, log into MySQL and get a count of the number of
rows in the
birds_simple
table. It should be back to 28,892.
The next step is to get the SQL statements that were executed on the server for the
rook-
ery
database since the time of the dump file. That can be a bit of a bother to determine on
