MySQL - Importing and exporting data

Import and Export

Databases management systems such as MySQL can easily and with great flexibility handle a very large volume of data. The process of compiling all the information required to create another identical database, in a dump file, is referred as "Exporting a database".

This feature is useful:

as it is recommended to periodically retrieve all the data contained in our database, to make a backup

sometimes you simply need to switch to another database.

In contrast, the term "import a database" refers to the creation of new DBMS database from an export file (dump).

MySQL provides a number of tools to export bases to other DBMS or import them.

Export a MySQL Database

MySQL provides several ways to export data. The principal method is the mysql command line:

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

The following notation is also possible:

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

host : is the name or IP address of the machine on which the database you want to export is installed. By default it is the localhost, that is to say, the machine from which the command mysql is launched.

: is the name or IP address of the machine on which the database you want to export is installed. By default it is the localhost, that is to say, the machine from which the command mysql is launched. user : is the user you want to connect. By default it is the root user.

: is the user you want to connect. By default it is the root user. password : is the password of the user you want to connect.

: is the password of the user you want to connect. dbname is the name of the database to export.

is the name of the database to export. dump_file is the name of the file where the database will be exported. If no absolute path is specified, the file will be stored in the same directory as mysql.

Here is an example of exporting the database named "users", located on the "dbommentcamarcheom" machine and belonging to the "admin" user (whose password is KinderSurprise):

mysql -h dbommentcamarcheet -u admin -pKinderSurprise users > users.sql

Export a MySQL database with mysqldump

The mysql command allows you to efficiently export an entire database hosted by MySQL, but does not offer the flexibility to export multiple databases or otherwise a specific of the database (table or part of a table). The "mysqldump" command meets this requirement by providing the ability to specify more precisely the data to be exported. The syntax of this command:

mysqldump [options] database [tables]

The options generally used are:

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

host: is the name or IP address of the machine on which the database you want to export is installed. By default it is the localhost, that is to say, the machine from which the command mysql is launched.

user: is the user you want to connect. By default it is the root user.

is the user you want to connect. By default it is the root user. password: is the password of the user you want to connect.

is the password of the user you want to connect. dbname is the name of the database to export.

is the name of the database to export. dump_file is the name of the file where the database will be exported. If no absolute path is specified, the file will be stored in the same directory as mysql.

Here is an example where the "members" and "guests" tables of the database named "users" located on the "dbommentcamarcheom" machine and belonging to the "admin" user (whose password is KinderSurprise) will be exported:

mysqldump -h dbommentcamarcheet -u admin -pKinderSurprise -users.sql users members guests

It is possible to refine more precisely the data to be exported using a SQL condition via the -w switch (--where here "WHERE id> 4000"):

mysqldump -h dbommentcamarcheet -u admin -pKinderSurprise -users.sql -w "id>4000" users members guests

The SQL command located after the -w switch must be delimited by single or double quotes.

Import a database in MySQL

The mysql command line can also be used import data. Simply use the < redirection and specify the dump file containing SQL statements to be imported:

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

The following notation can also be used:

mysql --host host --user user -passwordpass database < dump_file

Import a database with phpMyAdmin

To import a database with phpMyAdmin, simply make a copy and paste of MySQL "dump" in the field provided to enter the query or click on the "browse" button to get the file containing a copy of the database.

Original document published on CommentcaMarcheet.