How to Reset the MySQL/MariaDB Root Password, Import and Export Databases

Leave your reply

Introduction

Learn basic MySQL/MariaDB disaster recovery and preparedness: How to reset the root MySQL/MariaDB password, import databases from an exported MySQL dump file, and export databases into a MySQL dump file for backup.

Requirements

  • A Cloud Server running Linux
  • MySQL or MariaDB installed and running

Reset the Root MySQL/MariaDB Password

If you lose the password for the MySQL/MariaDB root user, you can reset it by restarting MySQL/MariaDB in Safe Mode, which does not require a password.

First, shut down the database with the command:

  • Ubuntu/Debian: MySQLsudo service mysql stop
  • Ubuntu/Debian: MariaDBsudo service mariadb stop
  • CentOS/Red Hat: MySQLsudo systemctl stop mysql
  • CentOS/Red Hat: MariaDBsudo systemctl stop mariadb

Start MySQL/MariaDB in Safe Mode with the command:

mysqld_safe --skip-grant-tables &

You can now log in to MySQL/MariaDB as root without having to enter a password:

mysql -u root mysql

Reset the MySQL/MariaDB password with the following command. Change my-new-password to a secure password.

UPDATE user SET password=PASSWORD("my-new-password") WHERE user='root';

Reload the privilege tables:

FLUSH PRIVILEGES;

Exit the MySQL/MariaDB client:

quit;

Shut down MySQL/MariaDB:

mysqladmin -u root -p shutdown

You will need to enter the password for root which you set in the previous step.

Finally, restart the database in normal mode:

  • Ubuntu/Debian: MySQLsudo service mysql start
  • Ubuntu/Debian: MariaDBsudo service mariadb start
  • CentOS/Red Hat: MySQLsudo systemctl start mysql
  • CentOS/Red Hat: MariaDBsudo systemctl start mariadb

Export a MySQL/MariaDB Database

Exporting a database to an .sql file is a convenient way to migrate a database to a new server. It is also a quick and easy way to create a backup copy of a database.

The command to export a MySQL/MariaDB database is:

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

For example, to authenticate as root and export the database my_db to a file called my_db-10-01-2016.sql the command is:

mysqldump -u root -p my_db > my_db-10-01-2016.sql

Import a MySQL/MariaDB Database

In order to import a MySQL/MariaDB database file, you will first need to create an empty database. Log in to the MySQL/MariaDB client with the command:

mysql -u root -p

Enter the MySQL/MariaDB root user's password, then create the database:

CREATE DATABASE [database name];

For example, to create a database named my_db the command is:

CREATE DATABASE my_new_db;

Once this is done, log out of the client:

quit;

You can now import the database file with the command:

mysql -u [username] -p [new database name] < [.sql dump file name]

For example, to import a .sql dump file named my_db-10-01-2016.sql into the my_new_db database, the command is:

mysql -u root -p my_new_db < my_db-10-01-2016.sql