How To Import and Export Databases in MySQL using Command Line on Ubuntu Server 20.04

You can always use tools such as MySQL workbench and phpMyAdmin, but MySQL provides command-line tools for the bulk processing of data that are more efficient and flexible.

Follow these steps to import and export Databases in MySQL.

Exporting MySQL database

Step 1 : To export a database from the MySQL server, use the following command

mysqldump -u admin -p blog > db_blog_backup.sql

Step 2 : To export specific tables from a database, use the following command

mysqldump -u admin -p blog table1 table2 > table_blog_backup.sql

Step 3 : To compress exported data, use gzip

mysqldump -u admin -p blog | gzip > db_blog_backup.sql.gz

Importing MySQL database

Step 4 : To import an SQL file to a MySQL database, we need to first create a database

mysqladmin -u admin -p create blog_v1

Step 5 : Once the database is created, import data with the following command

mysql -u admin -p blog_v1 < table_blog_backup.sql

Importing MySQL database with foreign key constraints

Step 6 : Login to MySQL using your credentials with the following command

mysql -u admin -p

Step 8 : we need to create a database

mysql> CREATE DATABASE blog_v2;

Step 9 : Select a database to work with

mysql> use blog_v2;

Step 10 : Set your MySQL foreign key constraint checks to 0 using the next command

mysql> SET FOREIGN_KEY_CHECKS = 0;

Step 8 : Import your database using a source command like the one below

mysql> SOURCE /root/table_blog_backup.sql;

Step 9 : And set your MySQL foreign key constraint checks back to 1 using the command that follows

mysql> SET FOREIGN_KEY_CHECKS = 1;