In software development, developers often need to import and export databases in MySQL and MariaDB to create a backup. It looks efficient and faster until users face using large databases of more than 500Mb.
Thus, to fix such situations importing and exporting databases using MySQL command lines can benefit them. This article will describe how to deal with MqSQL databases and import and export databases in MySQL or MariaDB using SQL files.
Prerequisites
Before proceeding to the exporting and importing steps, users must have the following prerequisites in their system:
- Server SSH access
- MySQL database name, username, and password for exporting the database
- MySQL user and password who has ownership for creating the database for importing
In the given section, we will discuss how to export and import databases in MySQL or MariaDB using SQL files:
Exporting a MySQL or MariaDB Database
Using the mysqldump console utility, users can export the database to SQL text files which help efficiently move or transfer the database from one location to another.
Using the mysqldump for exporting the database:
mysqldump -u username -p password database_name > database_name-dump.sql
Description of the command:
- Username – It represents the username that allows users to log in to the database
- Password – It defines the password needed for accessing the database.
- database_name – It defines the name of the database that users will use for exporting
- data-dump.sql – It defines the file in the current directory that saves the output
Remember: Using this command, users will not get any visual output, but they can inspect the contents of data-dump.sql to examine if they are using a legitimate SQL dump file.
Important:
- Users need to provide the full path where they want to store the database dump.
- It is suggested to use the -R and --triggers with mysqldump to maintain the routines and triggers of the actual database. Users cannot copy them by default.
Importing a MySQL or MariaDB Database:
Follow the steps to import the database using the required commands:
Step 1:
Type the command in the MySQL shell prompt and create a new database:
CREATE DATABASE new_database_name;
The users will get this output that confirms the creation of the new database
Query OK, 1 row affected (0.00 sec)
Step 2:
Press CTRL+D to exit the MySQL shell. Now users can import the database by typing the following command:
mysql -u username -p password new_database_name < database_name-dump.sql
Description of the command:
- Username – It represents the username that allows users to log in to the database
- Password – It defines the password needed for accessing the database.
- database_name – It defines the name of the database that users will use for exporting
- data-dump.sql – It defines the file in the current directory that saves the output
When it runs the command successfully, users will not get any output, and if there are errors in the command that occurred during the runt-time, then mysql will display them to the terminal. Users log in to the MySQL shell to ensure their import process is successful or unsuccessful.
Conclusion:
We hope this article has given a crisp idea of all the requirements needed to trigger the process of import and export of databases. Then, we highlighted the steps and commands that export, create and import new databases in MySQL or MariaDB using the MySQL shell prompt.