Tutorial

How To Import and Export Databases and Reset a Root Password in MySQL

Published on June 12, 2012

MySQL

Etel Sverdlov

How to Import and Export Databases

Export

To Export a database, open up terminal, making sure that you are not logged into MySQL and type,

mysqldump -u [username] -p [database name] > [database name].sql

The database that you selected in the command will now be exported to your droplet.

Import

To import a database, first create a new blank database in the MySQL shell to serve as a destination for your data.

CREATE DATABASE newdatabase;

Then log out of the MySQL shell and type the following on the command line:

mysql -u [username] -p newdatabase < [database name].sql

With that, your chosen database has been imported into your destination database in MySQL.

How to Reset a Root Password

When you first install MySQL, you have to set up your root password. However, should you forget it at any point, you can still recover it.

Step One—Shut Down MySQL

In terminal, stop the MySQL process

 /etc/init.d/mysql stop

Step Two—Access MySQL Safe Mode

In safe mode, you will be able to make changes within the MySQL system with a root password alone, without the need for MySQL root password.

sudo mysqld_safe --skip-grant-tables &

Once safe mode has started up, log into MySQL and when prompted, use your standard root password.

mysql -u root mysql

Step Three—Set Up a New Password

Finally, set up the new MySQL root password by typing the command below. Replace "newpassword" with the password of your choice.

update user set password=PASSWORD("newpassword") where User='root';

Be sure to reload everything:

 FLUSH PRIVILEGES;

and you now have a new root password.

By Etel Sverdlov

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products

About the authors

Etel Sverdlov

author

Still looking for an answer?

Ask a question Search for more help

Was this helpful?

10 Comments

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

chartrandf • February 27, 2013

Is MySQL still running in safe mode after the “How to Reset a Root Password” steps?

e • March 21, 2013

Yes, use

[code]/etc/init.d/mysql restart[/code]

f1dc16eda41571f12bf5d14705a5ae • January 22, 2014

I changed my password for MySQL. But I didn’t stop the MySQL process before changing it. Will there be any implications? I am asking because I want to know what is the reason for stopping the MySQL process? thanks!

Kamal Nasser

DigitalOcean Employee

• January 23, 2014

@hopefloatt: Do you mean you just followed Step Three? If so, it’s fine—you do not need to restart MySQL.

vfistri2 • February 14, 2014

where should [database name].sql be placed?

erwindelaat • February 17, 2014

What about mail server settings… In the past I had changed my mysql root password but after that e-mail didn’t work anymore… had to put back everything as it was

jimATjibba • October 2, 2014

I have done a mysql dump but can not find the .sql file. Where abouts in my droplet can I locate it?

cattanguyen • October 28, 2014

i cant Access MySQL Safe Mode

damirdesigns • December 19, 2014

when importing sql file to database u don’t need to logout !!

first create database

CREATE DATABASE newdatabase; USE newdatabase;

and then you can import table to a database using

SOURCE filename.sql

this command will import file from folder you are when you logged in mysql. If your file is in diffrent folder you should add file path before filename.

paulh88 • January 4, 2015

I have a mysql database [database.sql.gz] in my Download folder on my laptop.

I am migrating a wordpress site. I am using LEMP 14.04 64 bit.

I first tried to import/upload the dbase using phpmydmin but it failed. The dbase is 28MB and phpmyadmin would only allow file uploads of 2MB.

I found out how to increase the file upload limit to 100MB. Still phpmyadmin would not allow me to import the database. It timed out at approx 150 seconds.

Increasing the time from 1440 to 500000 made no difference. It still timed out.

Using LEMP I read somewhere that the session limits are in Nginx but I know not where or how to fix this.

I sought to use easyengine to get round this as easyengine comes with phpmyadmin set at the 100Mb import limit. phpmyadmin on EE will not allow me to import the dbase either.

I have used Putty (with SSH) and Filezilla (SFTP). With Filezilla I can upload the dbase.sql.gz file but where, ie what folder, should I upload it to and, once there, how do I unzip it and get mysql to import it and see it?

Any help on this much appreciated as I am at a complete standstill until I can get this done.

-P