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

Import and Export of Databases

Let’s consider the export of database first. You should not be logging to MySQL shell.

First open up your terminal and type in the following:

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

This will export specified database  to your droplet.

 

Similarly, to import a database, you need to first create a new blank database in MySQL shell which  will serve as the destination for the database data.

CREATE DATABASE newdatabase;

Log out of the MySQL shell and type in the following command to import:

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

This will import specified database onto your destination database in MySQL.

Reset a Root Password

At first, when you installed the MySQL, you would have set up the root password. In case if you wish to reset it or recover it, you can do the following steps:

First,  Make sure to stop the MySQL process:

/etc/init.d/mysql stop

After that, you can access the safe mode to make changes to MySQL system, even without the root password.

sudo mysqld_safe --skip-grant-tables &

When the safe mode has started, log in to MySQL and use the standard password for root:

mysql -u root mysql

Finally run the command below to set up a new password:

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

Make sure to replace the password with your actual values.

Now, reload and refresh:

FLUSH PRIVILEGES;

You are done!

 

KB Admin has written 46 articles