Backup and Recovery - High Performance MySQL - page 650

There are two kinds of restoration you might do, which correspond to the two kinds
of logical backups you can make.
Loading SQL files
If you have a SQL dump, the file will contain executable SQL. All you need to do is run
it. Assuming you backed up the Sakila sample database and schema into a single file,
the following is a typical command you might use to restore it:
$ mysql < sakila-backup.sql
You can also load the file from within the mysql command-line client with the SOURCE
command. Although this is mostly a different way of doing the same thing, it makes
some things easier. For example, if you're an administrative user in MySQL, you can
turn off binary logging of the statements you'll execute from within your client con-
nection, and then load the file without needing to restart the MySQL server:
mysql> SET SQL_LOG_BIN = 0;
mysql> SOURCE sakila-backup.sql;
mysql> SET SQL_LOG_BIN = 1;
If you use SOURCE , be aware that an error won't abort a batch of statements, as it will
by default when you redirect the file into mysql .
If you compressed the backup, don't separately decompress and load it. Instead, de-
compress and load it in a single operation. This is much faster:
$ gunzip -c sakila-backup.sql.gz | mysql
If you want to load a compressed file with the SOURCE command, see the discussion of
named pipes in the next section.
What if you want to restore only a single table (for example, the actor table)? If your
data has no line breaks, it's not hard to restore the data if the schema is already in place:
$ grep 'INSERT INTO `actor`' sakila-backup.sql | mysql sakila
Or, if the file is compressed:
$ gunzip -c sakila-backup.sql.gz | grep 'INSERT INTO `actor`'| mysql sakila
If you need to create the table as well as restore the data, and you have the entire
database in a single file, you'll have to edit the file. This is why some people like to
dump each table into its own file. Most editors can't deal with huge files, especially if
they're compressed. Besides, you don't want to actually edit the file itself—you just
want to extract the relevant lines—so you'll probably have to do some command-line
work. It's easy to use grep to pull out only the INSERT statements for a given table, as
we did in the previous commands, but it's harder to get the CREATE TABLE statement.
Here's a sed script that extracts the paragraph you need:
$ sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `actor`/!d;q' sakila-backup.sql