Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.6Mb
PDF (A4) - 30.8Mb
PDF (RPM) - 30.1Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.4Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 200.2Kb
Man Pages (Zip) - 311.6Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Reloading SQL-Format Backups

7.4.2 Reloading SQL-Format Backups

To reload a dump file written by mysqldump that consists of SQL statements, use it as input to the mysql client. If the dump file was created by mysqldump with the --all-databases or --databases option, it contains CREATE DATABASE and USE statements and it is not necessary to specify a default database into which to load the data:

shell> mysql < dump.sql

Alternatively, from within mysql, use a source command:

mysql> source dump.sql

If the file is a single-database dump not containing CREATE DATABASE and USE statements, create the database first (if necessary):

shell> mysqladmin create db1

Then specify the database name when you load the dump file:

shell> mysql db1 < dump.sql

Alternatively, from within mysql, create the database, select it as the default database, and load the dump file:

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

User Comments
  Posted by Jim Leek on October 13, 2011
I ran across the following error when restoring all databases from a mysqldump file:

ERROR 1005 (HY000) at line 156: Can't create table 'db1.testtable' (errno: 121)

Normally this error is caused by problems in the CREATE TABLE syntax for InnoDB tables. Of course as my SQL had been created from the mysqldump utility, I was confused how an error had appeared in the SQL code!

It turned out it was the presence of existing "ibdata1", "ib_logfile0" and "ib_logfile1" in /var/lib/mysql/ that was causing the problem.

The solution for me was to stop mysql, delete EVERYTHING in the /var/lib/mysql/ folder, then start mysql (this recreates fresh versions of the above files in addition to a fresh "mysql" database), and then re-run my import of the dump.sql file:

shell> mysql < dump.sql

(No username or password needed as the fresh "mysql" database has no root password.)

This worked perfectly and recreated all databases (including re-populating the "mysql" database with my previous data - including my old root password).
Sign Up Login You must be logged in to post a comment.