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
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).
© 2013, Oracle Corporation and/or its affiliates
User Comments
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).
Add your own comment.