Tutorial

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

Published on June 12, 2012

MySQL

By 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 author(s)

Etel Sverdlov

See author profile

Category:

Tutorial

Tags:

MySQL

Still looking for an answer?

Ask a question Search for more help

Was this helpful?

26 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.

88032f4611a843589560e1edc7f3b8 • 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

88032f4611a843589560e1edc7f3b8 • January 4, 2015

ok - I solved this by using easyengine, logging in to phpmyadmin as root and then uploading/importing the sql dbase uncompressed ie dbase.sql NOT dbase.sql.gz

The uncompressed dbase was 102 Mb but easyengine, even with 10Mb file upload limit, accepted it.

A bit of a fudge but for now it worked.

pedrarojajomar • January 31, 2015

FDFD

fabian10 • February 10, 2015

I want to import the database. But archiving on my computer? Ie I download the database to my computer?

Kamal Nasser

DigitalOcean Employee

• February 17, 2015

You can transfer the database after exporting it using a SFTP client such as FileZilla.

dollaraxis • February 19, 2015

After typying this cmd “sudo mysqld_safe --skip-grant-tables &” i get this output

150219 12:07:11 mysqld_safe Can’t log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effect. 150219 12:07:11 mysqld_safe Logging to ‘/var/log/mysql/error.log’. 150219 12:07:11 mysqld_safe A mysqld process already exists

RNinja • April 7, 2015

I got the same. Have you solved this?

faxeel • May 30, 2015

can anyone please solve this problem??

Nay • September 24, 2015

I get the same problem!!!

150924 13:18:20 mysqld_safe Can’t log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effect. 150924 13:18:20 mysqld_safe Logging to ‘/var/log/mysql/error.log’. 150924 13:18:20 mysqld_safe A mysqld process already exists

Please, help us!!!

webmaster38583 • March 25, 2015

Mysql Reset password is not working as I am not able to access mysql safe mode

hosting146731 • April 30, 2015

This comment has been deleted

Atul Kumar Pandey • May 29, 2015

This comment has been deleted

beenhere • July 26, 2015

Be careful when you use mysqldump! you need to include switches to make sure that the dump cmd doesn’t truncate your source table! such as <–skip-add-drop-table> <–no-create-info> <–insert-ignore>

The first is for avoiding dropping table, and the third is to avoid duplicate cases

newbie • September 11, 2015

@kamaln7 just like few others, i also want to know where the dump file is placed so that i can download it to my PC using filezilla?

Kamal Nasser

DigitalOcean Employee

• September 12, 2015

The file should be placed in your home directory. If you log in to your droplet using an SFTP client as the same user that exported the database, you should be in your home directory by default right after you log in.

Take a look at the following tutorial:

How To Use Filezilla to Transfer and Manage Files Securely on your VPS

cjvangoey • October 8, 2015

this should probably be in the guide. in case anyone is wondering, this is found in /home/[username used to create dump]/

writegnj • December 27, 2015

Where this will save the .sql file? mysqldump -u [username] -p [database name] > [database name].sql

Can I indicate location like this? mkdir /backups/mysql mysqldump -u [username] -p [database name] > /backups/mysql/[database name].sql

Also how do I zip or gzip the sql file?

Yasir Rehman • April 27, 2016

Here is a command you can run it in Linux shell or windows cmd

mysqldump --single-transaction -h [HOST] --user= [USERNAME] [PASSWORD] [DATABASE NAME] | gzip > [FILENAME].sql.gz

I will work and create a database backup in gz format.

Yasir Rehman • April 27, 2016

Where this will save the .sql file? mysqldump -u [username] -p [database name] > [database name].sql

It saves where you are in current directory, but if you give a full path then it save on that specific directory like

mysqldump -u [username] -p [database name] > /var/www/html/[database name].sql

Erick Acevedo • June 28, 2016

Thanks for this. 🎉

christianblair • March 9, 2016

This works nicely and adds today’s date to your backup sql file:

mysqldump -uUSERNAME -pPASSWORD DATABASENAME > PATHTOSAVEIT DATABASENAME_db-date --iso-8601.sql

jcamomile • January 11, 2017

This seems a bit too simplistic to be useful. mysqldump -u [username] -p [database name] > [database name].sql

Where does this dump save to? I know it says “exported to your droplet” but where?

cyberbattmd • March 10, 2018

Hi, I don’t understand. i’m not programmer. can you help me.

Carlo Rizzante • March 21, 2018

Sounds awesome but I keep getting “error in your sql syntax” when I try to dump the database.

The only way I can have access is via the web console. Access is denied via Terminal or FTP, or anything else, really. So I’m stuck with the awful web console. And those commands won’t work in there.

What other ways there are to dump the database, if any, other then what explained in here?

mikovertigo • August 24, 2018

Why error: 1045: Acces denied for user

d3d4564308f54bf3b123d88b6671ee • August 20, 2022

After running “FLUSH PRIVILEGES;” you have to exit the MySQL CLI and run this “mysqladmin -u root -p shutdown” command to stop safe mode running in the background. You will be prompted to enter the new root password. Once the safe mode is stopped you can start the MySQL server by running systemctl start mysql