Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 44.5Mb
PDF (A4) - 44.5Mb
PDF (RPM) - 40.2Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.5Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 204.6Kb
Man Pages (Zip) - 311.6Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 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
Note

For Windows PowerShell users: Because the "<" character is reserved for future use in PowerShell, an alternative approach is required, such as using quotes cmd.exe /c "mysql < dump.sql".


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  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.