Importing Data | MySQL, Second Edition

Once you've learned how to back up your databases, you should understand how to reconstruct them. If you used the mysqldump utility (see the sidebar in the preceding section), its counterpart, mysqlimport, will reinstate the databases (see the sidebar here). If you used the MySQL Administrator's Backup option, then its Restore option can be used to re-create your databases using the backed up files. I'll show you how.

To restore a database:

Open the MySQL Administrator and connect to the MySQL server.

You only need to connect as a user with permission to access the database you'll be restoring.

Click Restore.

Click Open Backup File (Windows) or Choose File (Mac OS X, Figure 13.11) to select the backup file.

Figure 13.11. The Restore pane lets you restore a database using an existing backup file.

Using mysqlimport

This application takes the data stored in a text file and inserts it into a table. Like mysqldump, it's run from a command-line interface.

The syntax for using mysqlimport is

mysqlimport -u username -p databasename '/path/to/filename.sql'

With mysqlimport, there are two assumptions: first, that the table the data is being inserted into already exists; and second, that the text file containing the records to be inserted has the same name as the table.

The mysqlimport utility is actually just an implementation of the LOAD DATA INFILE without directly using the mysql client. I discuss this SQL alternative in a separate sidebar.

Select the file to use.

The button clicked in Step 3 creates an Open dialog box for you to select the source file on your computer. This file should be the one created by the Backup pane in the preceding section of this chapter.

On Windows, choose the target database (Figure 13.12).

Figure 13.12. Select the schema where the data should be restored.

Windows users have the option of restoring the data to the original database (or schema) or to another one.

Click Restore Content (Windows) or Selection (Mac) to customize the restoration.

You can choose to back up specific tables instead of the entire database. On Windows, you'll need to click Analyze Backup File Content to see the list of tables (Figure 13.13).

Figure 13.13. The backup file lists all of the tables that can be restored. On Windows this includes the views and stored procedures.

Select the tables you want to restore.

By default, all of the tables are checked. Just uncheck a table to not restore its data.

Click Start Restore (Windows) or Restore Backup (Mac) to restore the data.

An SQL Equivalent, Part 2

There are two different ways to import data into your database using SQL. The first is RESTORE, and the second is LOAD DATA.

RESTORE is the complement to the SQL command BACKUP and is used to re-create a table based upon files stored in a directory. LOAD DATA INFILE is the complement of the SELECT...INTO OUTFILE SQL query.

LOAD DATA LOCAL INFILE '/path/to/filename.txt' INTO TABLE tablename

To use the LOAD DATA technique, you will need FILE permissions, and you will need to start mysql with the --local-infile option if you are loading a file from a client machine to a separate server computer.