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. Make sure you have not logged into the MySQL shell.

Open up your terminal and type in the following:

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

The database specified in the command will be exported to your droplet.

 

Similarly, to import a database, you need to first create a new blank database in MySQL shell. This 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

The specified database will be imported 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, stop the MySQL process:

/etc/init.d/mysql stop

After that, 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!

 

support2 has written 111 articles

Leave a Reply