Exporting and importing a database from MySQL or MariaDB is an unavoidable action for migrating or downloading the site. If you are working on a site backup, in addition to copy all of the site’s files, one copy of the database must be exported and saved. When you’re doing a site migration, in addition to downloading files, you need to export the database and re-import it on a new hosting.

Exporting the database from the console via SSH

You need to connect to the server where the database is located. Here you can read the instructions for connecting via SSH. After connecting, you need to type the next line in order for the database to be exported in SQL format:
—-

mysqldump -u databaseuser -p database_name > data-dump.sql

—-
Let’s explain:

• mysqldump is used for dump databases in SQL format
• databaseuser is a user name that has the privilege to use the database (replace with the name of the database user)
• database_name is the name of the database (replace with the name of the database)
• data-dump.sql is the name of the file that will get an exported database

This command is similar to the command for import, so pay attention to the arrow > which shows from left to right, that is, the database_name database will be exported to the data-dump.sql file.

Keep in mind that the data-dump.sql file will be saved in the directory where you are now. Just type in the pwd command to find out where are you now.

Importing the database from the console via SSH

In order for an existing database (its dump file) to be imported into the database, it is necessary that a new database exists on the server. If the database does not exist, it needs to be created first. This can be done in the following way. First connect to the server via SSH connection. Then type the following:
—-

mysql -u root -p

—-
This will open mysql with the highest privileges, so you can create the database:
—-

CREATE DATABASE new_database;

—-
Create a user for a new base:
—-

CREATE USER user@localhost;

—-
Assign a new user a password:
—-

SET PASSWORD FOR user@localhost= PASSWORD("password-goes-here");

—-
Allow the user to use the database with the password assigned to that user:
—-

GRANT ALL PRIVILEGES ON new_databse.* TO user@localhost IDENTIFIED BY 'password-goes-here';

—-
Refresh the privileges that exist in mysql:
—-

FLUSH PRIVILEGES;

—-

Type exit to exit mysql. Make a database upload to the server or use wget to drag it from another server. Now you can import a database into a new database created:
—-

mysql -u user -p new_database < database-name-that-will-be-imported.sql

—-
Let’s explain:

• user is the name of the user we made
• new_database is the name of the new database that we created
• database-name-that-will-be-imported.sql is a database that will be imported into a new database

To make it easier for you to remember when importing and when exporting a database, pay attention to the arrow <which points from right to left, that is, the database-name-that-will-be-imported.sql is moving in the direction of the arrow and will be imported into a new database with data that is entered.

Exporting and importing the database via PHPMyAdmin

Using the console to import and export the database can be difficult. Instead, you can do all this through the PHPMyAdmin application. Log in to your cPanel and open PHPMyAdmin. On the left you can see all present databases. When you select the base, you can import or export by choosing the option you need:

If you are exporting an existing database, it is already visible in PHPMyAdmin. However, if you are doing an import database that you downloaded from another hosting, you first need to create a new database in the MySQL Databases application in cPanel and then you can import the database.

Console or PHPMyAdmin?

For smaller databases you can freely use PHPMyAdmin, while for large databases (size in GB) it is better to use the console. PHPMyAdmin uses a web interface that can be broken at long-lasting import, so you will not know exactly what happened to the end.