Mysql import and export tool Mysqldump and Source command usage details

During PHP website development, Mysql database backup or database migration is often encountered. At this time, it is critical to import and export data from the database, mysql provides the command line export tool Mysqldump and Mysql Source import commands for SQL data import and export. The Mysql command line export tool Mysqldump command can export Mysql Data to the text format (txt) you can use the Mysql Source command to import the SQL file to the Mysql database. The following describes the usage of the Mysqldump and Source commands through the Mysql Import and Export SQL instance.

Mysql command line export tool Mysqldump command details

Mysqlcommand line tool mysqldump.exe is stored in the bin subdirectory of the Mysql installation directory by default. When using Mysqldump to export a database, make sure that the Mysql service is started.
M
Basic usage of ysqldump Export command

Copy codeThe Code is as follows: mysqldump-u username-p [-- opt] DATABASENAME [Table]> export SQL file name

By default, the SQL file exported by Mysqldump contains not only the exported data, but also the structure information of all data tables in the exported database.
In addition, if the SQL file exported using Mysqldump does not contain an absolute path, it is stored in the bin directory by default.
-Opt: This Mysqldump command parameter is optional. If this option is included, quick, add-drop-table, add-locks, extended-insert of the Mysqldump command are activated, the lock-tables parameter, that is, when using Mysqldump to export Mysql database information using the-opt parameter, you do not need to attach these parameters.
-Quick: Ignore the buffer output. The Mysqldump command exports data directly to the specified SQL file.
-Add-drop-table: add the DROP-table if exists statement before each create tabel command to prevent duplicate data tables.
-Add-locks: indicates to LOCK and UNLOCK a specific data table before and after the INSERT data. You can open the SQL file exported by Mysqldump and the LOCK TABLES and UNLOCK TABLES statements will appear before the INSERT operation.
-Extended-insert (-e): This parameter indicates that multiple rows can be inserted.
For more Mysqldump command parameters, you can use

Copy codeThe Code is as follows:
Mysqldump -- help


Command to view all the parameters in Mysqldump and their support.
Example of Mysqldump Export command:
After the Mysql database is installed, it contains the mysql database by default. I will use this database as an example to demonstrate various export instances of Mysqldump.
Use Mysqldump to export a database
Copy codeThe Code is as follows:
C: \ Program Files \ MySQL Server 5.1 \ bin> mysqldump-u root-p -- opt mysql> D: \ PHPWeb \ sqlbackup \ mysql. SQL
Enter password :******


Use Mysqldump to export a single table

Copy codeThe Code is as follows:

C: \ Program Files \ MySQL Server 5.1 \ bin> mysqldump-u root-p -- opt mysql user> D: \ PHPWeb \ sqlbackup \ mysql_user. SQL
Enter password :******


Use Mysqldump to export the data table structure

Copy codeThe Code is as follows:
C: \ Program Files \ MySQL Server 5.1 \ bin> mysqldump-u root-p -- no-data mysql user> D: \ PHPWeb \ sqlbackup \ mysql_user. SQL
Enter password :******


Note: The Mysqldump Command Parameter-no-data, as the name suggests, indicates that the data exported by Mysqldump does not contain INSERT data, but only the structure information of the Mysql database table user. You can also use-d for this parameter.
Use Mysqldump to export data with specified conditions

Copy codeThe Code is as follows:
C: \ Program Files \ MySQL Server 5.1 \ bin> mysqldump-u root-p "-- where = user = 'root'" mysql user> D: \ PHPWeb \ sqlbackup \ mysql_user. SQL
Enter password :******


Note: In this Mysqldump export instance, you can set the Mysqldump Command Parameter-where = conditions to set the exported INSERT data condition to the root INSERT record in the user table. You can use Mysqldump to export the desired data as needed, which is very convenient. Note that double quotation marks are required before and after the where option. You can use single quotation marks for specific conditions. Otherwise, an error occurs when parsing the Mysqldump command line parameters. You can also specify multiple where parameters.

Detailed description of Mysql Database Import command Source

The most common Database Import command for Mysql is Source. The usage of the Source command is very simple. First, you need to enter the command line management interface of the Mysql database, and then select the database to be imported, that is

Copy codeThe Code is as follows:
USER Database
Source exported SQL File


Note the specific directory address of the SQL file to be imported. It is best to use /.
So far, the Mysql command line export tool Mysqldump command and the Mysql import command Source usage have been introduced. Compared with the Mysql Source command, Mysqldump has more abundant export functions, the Mysqldump command parameters can be used to achieve different effects.

How to export data using the mysqldump command

In normal mysql applications, there are always many ways to import and export data. This article mainly introduces how to use the mysqlmysqldump command to import and export data. It is helpful to everyone.

The mysqldump command carries the -- where/-w parameter, which is used to set the data export conditions. The usage is basically the same as that in the SQL query command, we can export the data you need from the database.

1. The command format is as follows:

Mysqldump-u user name-p Password Database Name table name -- where = "filter condition"> export file path

Example:

Export data with an id greater than 100 from the test_data table of the test database to the/tmp/test. SQL file.

Copy codeThe Code is as follows:
Mysqldump-uroot-p123456 test test_data -- where = "id> 100">/tmp/test. SQL


2. Export the entire database

Mysqldump-u username-p Database Name> exported file name

Copy codeThe Code is as follows:
Mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. SQL


3. Export a database structure

Copy codeThe Code is as follows:
Mysqldump-u wcnc-p-d -- add-drop-table smgp_apps_wcnc> d: wcnc_db. SQL
-D no data -- add-drop-table adds a drop table before each create statement.



Mysqldump Conditions

Mysqldump-u username-p password-h host database a-w "SQL condition"-lock-all-tables> path

Copy codeThe Code is as follows:
1 mysqldump-hhostname-uusername-p dbname tbname> xxxx. SQL
** Export the database table content based on the specified conditions. (-W option-where)
1 mysqldump-hhostname-uusername-p dbname tbname-W' id> = 1 and id <= 10000 '-- skip-lock-tables> xxxx. SQL
Or
1 mysqldump-hhostname-uusername-p dbname tbname -- where = 'unit _ id> = 1 and unit_id <= 10000 '> ~ /Xxxx. SQL