Tutorial

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

Published on June 12, 2012
How To Import and Export Databases and Reset a Root Password in MySQL

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 us


About the authors

Still looking for an answer?

Ask a questionSearch 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!

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

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

i cant Access MySQL Safe Mode

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

Why error: 1045: Acces denied for user

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?

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

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?

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

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

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?

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel