Database Reference
In-Depth Information
row. Either way, it will restore the row with the same value for the
human_id
column.
That means that any other tables that reference that row will have the correct
human_id
.
Incidentally, if you want to generate a dump file that uses
REPLACE
instead of
INSERT
statements, you can do so usingthe
--replace
option with
mysqldump
.
When you're finished, you can use the
DROP DATABASE
statement to remove the
birdwatchers_backup
database.
This method is very useful in restoring rows and columns, especially when you want to
restore data to accommodate someone without disturbing other users. It doesn't usually
take long to do and it's simple. You can restore rows based on whatever criteria you give
in the
WHERE
clause. This is a skill you should learn well if you want to be a good data-
base administrator: users will herald you as their hero when you recover data without
much trouble ordisruption.
Recovering from a Binary Log
In the previous fewsections, we looked at how to restore databases and tables. Most of
those are broad methods of restoring data. Sometimes you need more precision, as in the
previous section, where we restored a single row and a single column. You would use that
method when you have specific rows to restore and the lost data is contained in one of
your dump files. However, suppose you want to restore data that was created some time
after
the last backup. This may sound impossible, but it just requires care and an under-
standing of MySQL's binary log. You can use the binary logs to restore data that was cre-
ated after the most recent dump file was created, up to a specific point in time. This is re-
ferred toas
point-in-time recovery
.
To do point-in-time recoveries, you will have to enable the binary logs. You can't wait un-
til you need them; you have to enable the binary logs before a problem occurs. To check
that it's enabled, execute the followingfrom the
mysql
client:
SHOW BINARY LOGS;
ERROR 1381 (HY000): You are not using binary logging
If you get the error message shown here, you will need to enable binary logging.
