The Management System Database such as MySQL can handle easily and with great flexibility in a very large volume of data. However, MySQL is also robust, it may be interesting to retrieve all the data in our database, to make a backup (backup) or simply switch to another database. Called "export" the act of formatting in a file (called dump) All information necessary to create a database identical.

Conversely, we call the import is to create a DBMS in a new database from an export file (dump).

MySQL offers a number of tools to export its database to other DBMS or import them.

Export a MySQL database

MySQL offers several ways to export data. The key is the command line mysql to do just about any action on the basis of data it contains from a simple command line:

mysql-h host-u user-ppass dbname> fichier_dumpThe following notation is also possible:

mysql - host host - user user
- passwordpass dbname> fichier_dumprepresents the host name or IP address of the machine where the database you want to export is installed. By default this is localhostThat is to say the machine from which the command mysql is launched
user is the user that you want to connect. By default it is user root
password is the password of the user that you want to connect. If you do not specify a password, one will be asked interactively. It should not be any space between-p and the password provided, unlike other fields
dbname is the name of the database to export.
fichier_dump is the name of the file where the database will be exported. If absolute path is not specified, the file will be stored in the same directory as the mysql command. Be careful not to give it a name of an existing file in this directory!
Here is an example of export of the database named users located on the machine db.commentcamarche.com belonging to the admin user (the password is KinderSurprise): mysql-h-u admin db.commentcamarche.net
pKinderSurprise-users> utilisateurs.sql

Export a MySQL database with mysqldump

The command mysql to export an entire database hosted by MySQL effectively but does not provide the flexibility necessary for exporting multiple databases or otherwise part of the database (Table or part of a table). The command mysqldump meets this need by providing the ability to specify more precisely the data to export. The syntax of this command:

mysqldump [options] database [tables]Here are the options generally used:

mysqldump-h host-u user
-ppass-rfichier dbname [tables]host represents the name or IP address of the machine where the database you want to export is installed. By default this is localhost

That is to say the machine from which the command mysql is launched
user represents the user that you want to connect. By default it is user root
password represents the password of the user that you want to connect. If you do not specify a password, one will be asked interactively. It should not be any space between-p and the password provided
dbname is the name of the database to export.
file is the name of the file where the database will be exported. If absolute path is not specified, the file will be stored in the same directory as the mysql command.
Here is an example export tables Member and prompts database named users located on the machine db.commentcamarche.com belonging to the user admin (the password is KinderSurprise): mysqldump-h-u admin db.commentcamarche.net
-pKinderSurprise-outilisateurs.sql members prompts usersIt is possible to refine more precisely the data to be exported in SQL giving a condition through the switch -w (- whereHere "WHERE id> 4000"): mysqldump-h-u admin db.commentcamarche.net
-pKinderSurprise-outilisateurs.sql-w "id> 4000" user prompts MemberThe SQL command just after the switch -w must be enclosed in quotes (single or double).

Import a database in MySQL

Order online mysql can also import data. Simply use the redirection < and indicate the dump file containing SQL statements to import:

mysql-h host-u user-ppass dbname <fichier_dumpThe following notation is, once again, also possible:

mysql - host host - user user
passwordpass-dbname <fichier_dump

Import a database with phpMyAdmin

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