How do I restore a MySQL database from an sql dump?

This article shows how to restore MySQL databases on the commandline easily. If your MySQL user has a password, then use this command:

mysql -h localhost -u [MySQL user, e.g. root] -p[database password] [name of the database] < [name of your sql dump, e.g. sqldump.sql]

Please note:  there's no space between -p and the password!

If there's no password:

mysql -h localhost -u [MySQL user, e.g. root] [name of the database] < [name of your sql dump, e.g. sqldump.sql]

The above commands are compatible with all Linux distributions incl. CentOS, Fedora, Arch Linux, OpenSuSE, etc.

On Debian and Ubuntu you can use the debian-sys-maint login to restore the MySQL database, so you dont have to remember the MySQL password.

Example:

sudo mysql --defaults-file=/etc/mysql/debian.cnf [name of the database] [name of your sql dump, e.g. sqldump.sql]

Falko Timme

About Falko Timme

Falko Timme is an experienced Linux administrator and founder of Timme Hosting, a leading nginx business hosting company in Germany. He is one of the most active authors on HowtoForge since 2005 and one of the core developers of ISPConfig since 2000. He has also contributed to the O'Reilly book "Linux System Administration".

Share this page:

Suggested articles

5 Comment(s)

Add comment

Comments

By:

That's much faster and easier than using import in phpmyadmin or another mysql manager.

By: Das_omen

I have done this in many ways... with mysqldump , mysqlimport and phpmyadmin... I'm running an Ubuntu 10.04 server with apache2 / mysql installed... 

 When I try to import the server does nothing, no error and no responses... Any ideas what might be the problem!?

By: Kartik Grover

The following example takes a backup of  all the database of the MySQL instance.

# mysqldump -u root -ptmppassword sugarcrm > sugarcrm.sq

By: Rudy

Will importing overwrite the db? I'd want to wipe out and restore using the backups that are created daily. Thanks.

By: Anvesh M Patel

 

Nice Article !

This is my pleasure to read your article.

Really this will help to people of MySQL Community.