How to Import/Export Database in Command Line (User Mode)

Introduction

Before explaining the basic principles of this article, it is necessary to make sure that the reader is familiar with certain notions. We will therefore start by defining the terms we will use, sometimes using English translations of words commonly used in computing. At the same time, we will explain the concepts underlying the principles we are dealing with.

Then, using examples from MySQL, we will discuss the concrete application of the concepts taught.

Finally, some references to consult will be proposed.

Note : The Glossary contains explanations on multiple topics and can be consulted to clarify certain terms.

Terminology and Basic Concepts

It is sometimes necessary to recover all of the data contained in a database, either to make a backup or to switch to another database. The term “export” refers precisely to the action of formatting all the information necessary to create a database identical to the first one in an export file, called a dump. The term “import” refers to the opposite operation: creating a new database in a DBMS from an export file.

It is important to understand what a command line interface (CLI) is. A CLI is a man-machine interface in which the user interacts with a computer system by entering textual instruction lines to ask the computer to perform operations, which are interpreted by a Shell (as opposed to using a Graphic User Interface, or GUI). Figure 1 shows an example of the instruction lines written in MySQL.

mysqldump -h host -u user
-ppass -rfile database [tables]

Figure 1 – Textual instruction lines of a command line interface (MySQL)

The diagram in Figure 2 summarizes the basic principle of importing and exporting a database from the command line into a DBMS:

Figure 2 — Importing and exporting a database into a DBMS

The interpretation of the command line by the Shell can be done in two ways: in Kernel mode or in user mode. In the latter mode, which is of interest to us here – that of applications in particular – the executed code does not have direct access to the hardware or to a memory location as is the case in Kernel mode, with all the risks that this entails. On the contrary, in user mode, the code delegates these accesses to the Application Programming Interface (API) of the system.

To summarize what we have seen so far, our goal is to explain the basic principles of formatting in a file (dump) all the information necessary to create a database identical to the original one – export – and to create a new database from such a file – import – by communicating with the computer by means of textual instructions that do not allow direct access to the hardware or to a location in memory.

Examples of Command Line Usage

In order to illustrate concretely the principles we have explained so far, we will proceed by example. We will deal with the tools that MySQL offers to export its databases to other DBMS or to import them from the command line.

Complete Export of a MySQL Database

The main online command for exporting data is called mysql. This command allows you to export an entire database hosted by MySQL. Here is the syntax:

mysql -h host -u user -ppass database > file_dump

The following notation is also possible:

mysql --host host --user user
--passwordpass database > dump_file

Here is the definition of each term:

host: name or IP address of the machine on which the database to be exported is installed (localhost by default, i.e. the machine from which the mysql command is launched).
user: user with which you want to connect (by default, root).
password: password of the user you want to connect with.
database: name of the database to export.
dump_file: name of the file in which the database will be exported.

In the following example, the command line is used to export the database named employees located on the machine db.company and belonging to the user school whose password is 9a7e :

mysql -h db.company -u school -p9a7e employees > employees.sql

Partial Export of a MySQL Database with Mysqldump

Sometimes it is necessary to have the flexibility to export only a part of a database, such as a table or portion of a table, or even to export several databases. Here is the syntax of the mysqldump command designed for this purpose:

mysqldump [options] database [tables]

The options generally used are the following:

mysqldump -h host -u user
-ppass -rfile database [tables]

In addition to the host, user and pass options defined previously, we notice the file option, which is the name of the file in which the database will be exported.

In this example, the name and city tables are imported:

mysql -h db.company -u school -p9a7e oemployees.sql employees name city

It is even possible to use SQL conditions thanks to the -w (where) clause, the command located after being delimited by double or single quotes. Thus:

mysql -h db.company -u school -p9a7e oemployees.sql -w "id>10" employees name city

Importing a MySQL Database

mysql is also used to import a database. Here is the notation using the < redirection and indicating the dump file containing the SQL statements to import:

mysql -h host -u user -ppass database < file_dump

We can also use this notation:

mysql --host host --user user
-passwordpass database < file_dump

References

The reader interested in more details can read the following references, which were consulted to develop this text:

Updated on January 9, 2024