Posted by We will learn import and export MySql databases through the command-line interface in this article. It is a simple and basic procedure.
To export or import data using MySQL, first, log in to your server with login details.
Export MySQL Database Using the Command Line
We use
mysqldump
to export data from the database. To use the command, the user should have login details and the privilege to export data.
We use the below command with options to export the data:
| 1 | $ mysqldump -u UserName -p DatabaseName TableName1 TableName2 > filePath/fileName |
-u: It will be the user name of the database.
-p: It will be the password of the database. Also, you can write a password like this(
p'YourPaswordHere'
) with p in a single quote
DatabaseName: It will be the database in which you must export the data.
TableName: It is optional. If you want to export particular tables, you can write the table names after the database name.
Suppose your database is on another server and you want to export from that server so you can pass the host in the command.
| 1 | $ mysqldump -u UserName -h HostName -p databaseName > filePath/fileName |
-h: It will be the hostname of the database that may be IP(Internet Protocol).
Import MySQL Database Using the Command Line
Follow the below steps to import MySQL tables:
- Log in to the MySQL server.
- Create a database using the command
CREATE DATABASE YourDatabaseName;. - Use or select the created database using the command
USE YourDatabaseName;. - Run the below command
| 1 | source SqlFilePath |
Also, you can import the tables using the below command
| 1 | mysql -u UserName -p'Password' DatabaseName < YourSqlFile |
Change the UserName, Password, and DatabaseName with your Username, Password, and Databasename.
YourSqlFile: Change it to the full path to the SQL dump file that needs to be imported.
Example: /home/downloads/products.sql
If the query will run successfully then it will show something like that:
| 1 2 3 4 5 6 7 8 9 10 11 12 | Query OK, 30 rows affected (0.02 sec) Records: 30 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) |
Log in to the MySQL shell and check the data to see whether the import was successful by running
USE YourDatabase
and
SHOW tables
command.
If there are any issues with the import, MySQL will show them in the CLI(Command Line Interface).