Import and Export data with Mysql

Data base management system such as MySQL allows to deals with a very important volume of data with a lot of flexibility and in a very easy way. However, it could be also interesting to recover the whole data that your database contains for back up or just to go into another one. ‘Export’ is the fact to format in a file called dump all the information necessary for the creation of a similar database. MySQL offers a specific number of tools allowing to export bases to other DBMS or to import them.

Export a MySQL data base

MySQL propose several ways to export data. The principle is mysql line command allowing realising nearly any action on databases that it contains from a simple command line:

mysqldump -h=host -u=user -p=pass data_base > dump_file.

The following is also possible:

mysqldump --host=host --user=user --password=pass data_base > dump_file

host name or IP address of the server on which the data base that you wish to export is installed. By default, it is the localhost, which means the server from which the mysql command is launched

user it is the user with whom you wish to connect. By default it is the user root

password it is the password of the user with whom you wish to connect If you do not enter any password, it will be requested in an interactive way. There must be no space between –p and the provided password, contrary to other fields.

data_base is the name of the database to export

dump_file dump_file is the name of the file in which the base will be exported. If no path is specified, the file will be stored in the same directory than mysql command.
Make sure you do not give the same name as an existing file in the directory!

Export a MySQL database under PhpMyAdmin

1. Click on ‘Export’
2. Tick on ‘Structure’ and ‘Data’
3. Click on go

Import a database under MySQL

The command on line mysql allows also importing data. You just have to use the forwarding < and to indicate dump file containing SQL instructions to import:

Mysql –h=host –user=user –p=pass data_base < dump_file

The following is also possible:

Mysql –host=host –user=user –password=pass b