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.