Export and Import Database in Linux using Command Line

Category : MySQL

Following guide helps to export and import database of MySQL / MariaDB using Command-Line. It is very useful when the database size is pretty big and the software like PHPMyAdmin, MySQL workbench might give you timeout issue or take abnormally very high time.

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It-dumps one or more MySQL databases for backup or transfers to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format. The mysqldump doesn’t usually consume much CPU resources on modern hardware as by default it uses a single thread. This method is good for a heavily loaded server. Export and Import database on CLI is made very easy using mysqldump.

Using the following easy steps is a direct interaction with the MySQL server and hence the fastest way out for export or import of database.

Prerequisites

To import and/or export a MySQL or MariaDB database, you will need:

  • Access to the Linux server terminal
  • The database name, user, and password of it

Exporting the Database

Export the database using the following command :

mysqldump -u<username> -p <database_name> > filename.sql

  • <username> is the user assigned to the database
  • <database_name> is the name of the database that will be exported
  • <filename.sql> is the file in the current filename in which the output will be saved to

Importing the Database

You can import any SQL database backup file by using the following command :

mysql -u<username> -p <database_name> < filename.sql

Exporting/dumping a particular table from a database :

For exporting particular tables from a database, the following command is used :

mysqldump -u<username> -p <database_name> <table1_name> <table2_name> ... <tablex_name> > filename.sql

  • <table1_name> <table2_name> ... <tablex_name>are the name of the tables that needs to be dumped.

These commands will not print any output. If your backup file is large, you need to wait a bit for it to execute all the queries of the file. Stay patient and wait for the command to finish.

Read more on MySQL here: https://www.basezap.com/category/mysql/