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:
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.
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
Here is an example of exporting the database named "users", located on the "db.commentcamarche.com" machine and belonging to the "admin" user (whose password is KinderSurprise):
mysql -h db.commentcamarche.net -u admin -pKinderSurprise users > users.sql
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]
Here is an example where the "members" and "guests" tables of the database named "users" located on the "db.commentcamarche.com" machine and belonging to the "admin" user (whose password is KinderSurprise) will be exported:
mysqldump -h db.commentcamarche.net -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 db.commentcamarche.net -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.
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
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 CommentcaMarche.net.