Import Database To MySQL Or MariaDB

The previous tutorial explains the steps required to backup the MySQL or MariaDB database. This tutorial provides the commands required to restore or import the existing backup. The commands to import the back are same for both the database servers.

You might be required to create a database in case the backup does not have the command to create the database. You may create the database to be on the safer side in case it's a large file. You can follow SQL Query To Create Database In MySQL to create the database. The same commands can be used for both Windows and Linux based systems including Ubuntu.

Restore Database

We can simply use the MySQL client to restore the database backup. Make sure that you are logged out from the database on the console. The commands in this tutorial are using the user enterprise to restore the database enterprise. There is no need to specify user and password in case the user account is using auth_socket algorithm to login to the MySQL server. The system user can directly access the database. You may refer How To Use Native Password With MySQL 5.7 and How To Install MySQL 8 on Ubuntu to learn more about password algorithms.

Notes: There is no need to use the option -p, in case no password is assigned to the user.

Below mentioned are the examples to restore the database with username, host, and port. The password must not be provided with these commands for security purpose. You may specify the password in the secure config file (with appropriate read/write permissions) which is considered as a secure option for cron jobs. Another option is to use the cat command to use an encrypted password(similar to how Plesk is using) as shown in the examples. The system will prompt for password in case it's not provided within the command.

# Comamnd
mysql -u <username> -p <database name> < <file path>

# Example - Import with username, without password - prompts for password
mysql -u enterprise -p enterprise < enterprise.sql 

# Example - Import with username, with password
mysql -u enterprise -p'mypwd' enterprise < enterprise.sql 
# OR
mysql -u enterprise -p`cat /etc/psa/.psa.shadow` enterprise < enterprise.sql 

This is how we can restore the database from the backup file.