Learning MySQL

Loading Data from an SQL Dump File

The previous section showed you how to back up your databases. Let’s see how to restore them from those backups.

To load the structures and data in a dump file, you can tell the MySQL monitor to read in the SQL commands from the file:

mysql> SOURCE dumpfile.sql

Alternatively, you can simply run the MySQL monitor in batch mode and execute the instructions in the dump file:

$ mysql mysql_options < dumpfile.sql

We don’t recommend this approach, as it’s a little less portable than the SOURCE command; more importantly, it doesn’t show you any error and warning messages as the SQL statements are processed.

If the backup file doesn’t have CREATE DATABASE and USE statements, you’ll need to type these into the monitor before you read in the dump file, or add them to the dump file if you want to run the monitor in batch mode. A good step prior to carrying out a restore operation is to inspect the backup file with a text editor. Once you’ve inspected the file, you can decide whether you need to drop and re-create databases, use databases whether you need to take any other steps prior to a restore operation. Of course, you can use the mysqldump options to control what’s written to the dump file when it’s created.

You previously backed up the music database to the file music.sql. The way you did this didn’t include any CREATE DATABASE and USE statements in the dump file, so you need to use the monitor to enter these yourself.

Start the monitor as the root user: ...