Import and Export Databases in MySQL using Command Line

Posted on August 1st, 2019

MySQL is open-source relational database management system. It is one of the most popular RDBMS that ranks #2 on the list of most popular databases in the world. It has a very rich set of features that allows you to start from the small databases easily. And you can scale your database according to your requirements. It also allows you to replicate your MySQL servers so that you can balance the load on your MySQL servers. As it is so powerful and flexible, it also provides us features to import and export our MySQL databases using the command line interface.

In this short tutorial, we will learn how to import and export databases from the command-line interface. It is a very easy and straightforward process. So, Let’s first learn how to export MySQL databases using the command line interface.

Export MySQL Databases using Command line

MySQL provides us the utility called mysqldump to export our databases. It is very easy to use utility. You can export a single database or multiple databases using this command or utility. So, Let’s see how to export a single database using mysqldump.

To export a single database using mysqldump, execute the following command on your server. To execute this command, you first have to connect to your server via SSH.

$ mysqldump -uUSERNAME -p DB_NAME > exported.sql

Do not forget to replace Username and database name in the command. It will ask you for the password of the user you are using as a username. Enter the correct password and it will start exporting database in the file you have mentioned in the command. In our case, it is exported.sql.

It means that the whole database will be exported to the file named exported.sql. You can export to any file you want. Just replace the file name and you are good to go.

Now, Let’s see how to export MySQL database except a few tables. This is not a widely used feature because you will mostly export the database with all the tables in it. Execute the following command to export the database except for a few tables.

$ mysqldump -uUSERNAME -p DB_NAME --ignore-table=DB_NAME.TABLE_NAME > exported.sql

Again, do not forget to replace variables with the actual username, database name, and table name. If you want to ignore multiple tables in the export process, just add an extra --ignore-table options in your commands with the tables you want to ignore.

So, this is how you can export MySQL databases using the command line. Now, Let’s see how to import MySQL databases.

Import MySQL database using Command line

Importing MySQL database is even easier. We can use the main mysql command to import the databases that are exported and stored as an SQL file. But you first have to create MySQL database in which you want to import your SQL file.

So, Let’s first create a MySQL database. Login to your MySQL server using the command given below.

$ mysql -uUSERNAME -p;

After logging in, Execute the following command to create a database in your MySQL server.

mysql> CREATE DATABASE DB_NAME;

Now, Let’s import the SQL file in our brand new database. Execute the following command to import MySQL database from an SQL file.

$ mysql -uUSERNAME -p DB_NAME < import_file.sql

It will ask you for the password. Enter the password and it will start importing data from the SQL file you have mentioned in the command. So, this is how you can import databases in MySQL.

 

Conclusion: It is very easy to import and export data in MySQL. There are many more options available in mysqldump utility that you can use to customize your exported data. Let us know if you need help with import and export in MySQL. We will respond with the solution or help as soon as possible.

5 Responses to “Import and Export Databases in MySQL using Command Line”

  1. Patrick McGowan says:

    I am about to buy webspace to host my ap.net.core app which is still in development.
    It uses a MySql80 database. I cannot see any instructions for importing my existing database onto your server. Please advise before I buy from you.
    Thanks.

  2. Slaford says:

    Try this
    mysql -u username -p database_name < file.sql

  3. shiva says:

    I want to import .sql file without asking for password.

    i have 100+ .sql files to import, every time it is hard for me to enter password for each .sql file

    any workaround.

    mysql -u username -p database_name < file.sql

    mysql -u username -p passsword database_name < file.sql (like this)

    • Clement says:

      @shiva You can specify the password directly in the command, but the password have to be glued to the -p parameter, so you want to do :

      mysql -u username -pMYPASSWORD database_name < file.sql

Leave a Reply