How To Import-Export Mysql/MariaDB Database Command line

By: Sunil Kumar |  In: MySQL  |  Last Updated: 2017/09/20

How To Import-Export Mysql/MariaDB Database Command line

As a web developer, you must be aware of some basic database operations. There would be a lot of times when you need to import or export a database either you are moving your website from one host to another or taking backup of the database
Importing and exporting databases is one of the basic but important skills to have.
If you are a web developer you must be aware of some basic database operations. Importing and exporting databases is an important skill to have. Most of the time you have to make a backup of the database and restore the database using a backup.
Working with database dumps in MySQL and MariaDB is straightforward. This tutorial will cover how to export the database as well as import it from a dump file in MySQL and MariaDB.

Exporting the Database

You can export your database as an SQL file. For doing this you can use mysqldump console utility.
For dumping any database you need database name, username, and password.
Open your terminal and type the following command to export the database

 $ mysqldump -u {username} -p{password} your_database_name > database_backup.sql
  • username is the username you can log in to the database with
  • database_name is the name of the database you are exporting
  • data-dump.sql is the file in the current directory that the output will be saved to

Importing the Database

To import an existing dump file into MySQL or MariaDB, you will have to create a new database. This is where the contents of the dump file will be imported.
To create a database you have to log in to your database system.

 $ mysql -u {username} -p{password}

This will open a console where you can run your queries. To create a database

 mysql> CREATE DATABASE my_new_database;

You’ll see this output confirming it was created.

 Output
Query OK, 1 row affected (0.00 sec)

Now you are done with the database creation part. You can exit the MySQL shell by pressing CTRL+D.
On the normal command line, you can import the dump file with the following command:

 $ mysql -u {username} -p{password} my_new_database < database_backup.sql

The successfully-run command will produce no output. If any errors occur during the process, MySQL will print them to the terminal instead.
For a larger database, this might take a lot of time. To track the progress you can run the following query

show full processlist

and you will get the insert query currently executing.
Also, read:

How to Configure Full-Text Stopwords & Stoplist in MySQL

Comments


Leave a Comment

Your email address will not be published.

*


Sunil Kumar


I am the owner of acmeextension. I am a passionate writter and reader. I like writting technical stuff and simplifying complex stuff.
Know More

Join more than 10,000 others Web Developers