Exporting and importing databases from the command line

Last updated: 29 April 2021

Most of our servers use cPanel, which has an excellent interface for working with databases. However, sometimes it is better to instead use the command line:

• Using the command line uses less system resources.
• It is easier to work with very large database files.
• It makes it possible to run commands from a custom script and/or cron job.

This article covers how to export and import databases in MySQL/MariaDB and PostgreSQL. We will use a database named example_db and a database user named example_user.

MySQL/MariaDB

Although cPanel routinely refers to databases as “MySQL” databases, they are in fact MariaDB databases. MariaDB is an open source drop-in replacement for MySQL. That means that you can still run all the MySQL commands you are familiar with.

Running MySQL commands from the Bash shell

Before we import a dump file we can quickly check if the destination database exists. You can run any MySQL command directly from the Bash shell:

$ mysql -u example_user -p -e "SHOW DATABASES;"
Enter password: 
+--------------------+
| Database           |
+--------------------+
| example_db         |
| information_schema |
+--------------------+

We used three options in the above command:

• -u defines the name of the database user.
• -p prompts for the database user’s password.
• -e lets you enter an SQL command, such as a SELECT query.

Using the same options we can also check if our database contains any tables:

$ mysql -u example_user -p -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'example_db';"
Enter password: 
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

The above output shows that we got an empty example_db table. Let’s import an SQL dump file.

Importing a dump file

For this example we are going to import a .sql file. The file is stored in the current working directory:

$ ls -1 *.sql
example_db.sql

To import the file we need to connect to the database and then use input redirection:

$ mysql -u example_user -p example_db < example_db.sql
Enter password:

Here, we first connect as the user (-u example_user) and we again ask for a password prompt (-p). Next, we specify the database we want to connect to (example_db). The real magic happens at the end. The less than sign (<) loads the dump file (example_db.sql) into our command. In other words, we connect to the database and import our SQL dump, all in one command.

No output is shown when the import worked (any errors are printed to the screen). To double-check if the import worked we can again count the number of tables in the database:

$ mysql -u example_user -p -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'example_db';"
Enter password: 
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+

Exporting a database

There is a separate utility for exporting databases: mysqldump. The syntax is similar to the command used to import a database:

$ mysqldump -u example_user -p example_db > example_db.sql

This time we used the mysqldump command rather than mysql, and at the of the command we used output redirection to redirect the output of the command to a file named example_db.sql.

As the output file already exists in the current directory we need to be a little careful, as the command will overwrite the existing file. There are two ways of preventing this:

• You can output the file to a different directory. For instance, if you got a directory for database exports you can include the full path to the output file: > /home/example/db_exports/example_db.sql.
• You can give the file a different name.

To give an example of the latter, you can insert the current date and time in the file name as follows:

$ mysqldump -u example_user -p example_db > example_db_$(date +"%Y%m%d%H%M").sql
Enter password:

$ ls -1 *.sql
example_db.sql
example_db_201909081610.sql

The date is inserted using the command $(date +"%Y%m%d%H%M"). It prints the current date and time, formatted in way that makes sense in file names (YYYYMMDDHHMM).

Exporting the output of a SELECT query

You don’t have to dump an entire database. It is possible to redirect the ouput of any command to a file. Here, we are saving the output of a SELECT query to a file named orders.txt:

$ mysql -u example_user -p example_db -e "SELECT order_id, order_date FROM orders;" > orders.txt
Enter password:

$ ls -1 *.{sql,txt}
example_db.sql
example_db_201909081610.sql
orders.txt

Of course, the orders.txt file is not a proper SQL dump file. However, it is useful to know that you can export exactly the data you need, rather than the whole database.

You can also specify how fields in an export file are delimited. For instance, this command produces a comma-separated (CSV) file:

SELECT order_id, order_date
INTO OUTFILE 'orders.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
FROM orders;

Here, we used INTO OUTFILE to write the output of the SELECT query to a file named orders.csv. By default, INTO OUTFILE delimits fields by tabs. We added FIELDS TERMINATED BY ',' to instead us commas as the delimiter. The OPTIONALLY ENCLOSED BY '"' statement encloses values from data type fields (such as CHAR and TEXT) in double quotes. This can be useful if the data contains spaces.

PostgreSQL

Importing and exporting databases using PostgreSQL works similar. You can use the psql command to connect as a user (-U) to a specific database (example_db). To import a database you then again use input redirection:

$ psql -U example_user example_db < example_db.sql
Password for user example_user: 
BEGIN
SET
CREATE TABLE
COPY 242
CREATE TABLE
COPY 3995
COMMIT
ANALYZE
ANALYZE

The output is more verbose than the output of MariaDB: it shows the various commands defined in the .sql file. Any errors would be clearly labelled as such.

To export a database you can use the pg_dump utility with output redirection. Here, we export the example_db database to a file:

$ pg_dump -U example_user example_db > $(date +"%Y%m%d%H%M")_example_db.sql
Password: 

$ ls -1 *sql
201909081649_example_db.sql
example_db.sql

Exporting the output of a SELECT query

Of course, you can also export the output of a query. You can do so by using the \copy command from the PostgreSQL command line. In the below example the SQL command is shown between the brackets and we are writing the output to a file named export_file.txt:

example_db=> \copy (SELECT order_id, order_date FROM orders) TO export_file.txt

The file will not include the headers, and the data will be separated by tabs. If you want a proper .csv file instead, including the headers, then you can specify the delimiter and format:

example_db=> \copy (SELECT order_id, order_date FROM orders) TO export_file.csv DELIMITER ',' CSV HEADER

More information

Both MariaDB and PostgreSQL have thorough documentation.

• MariaDB knowledgebase
• PostgreSQL documentation

If you find the official documentation a little too dry, the MariaDB Learn page is a good place to get hands on experience with the database. If you are new to PostgreSQL, a good starting point is the official Getting Started tutorial or the PostgreSQL Tutorial website.