Go MySQL Import and Export tool mysqldump and Source command usage detailed

MySQL itself provides command line export tool mysqldump and MySQL source Import command for SQL data import and export work, through the MySQL command line export tool mysqldump command to export MySQL data into text Format (TXT) SQL file, The MySQL source command is able to import SQL files into the MySQL database, and the following is an example of using MySQL import to export SQL instance mysqldump and source commands.

In the development of PHP Web site, often encountered MySQL database backup or database migration work, when MySQL how to import data in the export database is very critical, MySQL itself provides command line export tool mysqldump and MySQL The source Import command makes SQL data import and export work through the MySQL command line export tool mysqldump command to export MySQL data to a text format (TXT) SQL file, via MySQL The source command is able to import the SQL file into the MySQL database, following the usage of the mysqldump and source commands through the MySQL import export SQL instance.

MySQL command line export tool mysqldump command explanation

MySQL command-line export tool Mysqldump.exe is stored by default in the bin subdirectory of the MySQL installation directory, when you use mysqldump to export the database, first make sure that the MySQL service is started.
M
Ysqldump Export Command Base usage

The code is as follows:

- - [--opt] [Table] > Export SQL file names

The default mysqldump exported SQL file contains not only the exported data, but also the structure information of all the data tables in the exported database.
In addition, if you export SQL files using mysqldump without an absolute path, the default is to save in the bin directory.
–opt: This mysqldump command parameter is optional, if you bring this option to represent the Quick,add-drop-table,add-locks,extended-insert,lock-tables parameter that activates the mysqldump command, That is, you do not need to append these parameters when you use mysqldump to export MySQL database information through the –opt parameter.
–quick: The delegate ignores buffered output, and the mysqldump command exports the data directly to the specified SQL file.
–add-drop-table: As the name implies, add the drop-table IF exists statement before each create Tabel command to prevent data table names.
–add-locks: Represents locking and unlocking a specific data table before and after the insert data, you can open the mysqldump exported SQL file, and the lock tables and unlock tables statements appear before insert.
–extended-insert (-E): This parameter indicates that multiple rows can be inserted.
More mysqldump command parameters you can pass

The code is as follows:

Mysqldump--help

command to see all the parameters contained in the mysqldump and the support of the parameters.
The mysqldump Export command instance is as follows
MySQL database installed by default contains MySQL database, I use this database as an example to demonstrate the various export instances of mysqldump.
Exporting a database using mysqldump

The code is as follows:

5.1\>----******

To export a single table using mysqldump

The code is as follows:

5.1\>----******

Exporting a data table structure using mysqldump

The code is as follows:

5.1\>----******

Description: The mysqldump command parameter –no-data, as the name implies, does not contain insert data in the exported data, only the structure information of the MySQL database table user. You can also use-D to indicate this parameter.
To export data for a specified condition using mysqldump

The code is as follows:

5.1\>--P "--******

Note: In this mysqldump export instance, by setting the mysqldump command parameter –where=conditions set the exported insert data condition as the insert record of the user field root in the user table. With this parameter you can choose Mysqldump to export the data you want, which is very convenient. Note that double quotation marks are required before and after this where option, and the conditions can use single quotes, otherwise there will be an error parsing the mysqldump command line arguments, and you can also specify multiple where parameters.

MySQL Database import command source detailed

MySQL most commonly used database Import command is the use of SOURCE,SOURCE commands is very simple, first you need to enter the MySQL database command line management interface, and then select the database to import, that is

The code is as follows:

USER Database
Source Exported SQL file

Note the specific directory address of the SQL file that needs to be imported, preferably using/.
Now that the MySQL command-line export Tool mysqldump command and the MySQL Import command source are used, the mysqldump export function is richer than the MySQL source command. Reasonable use of mysqldump command parameters can achieve different effects.

mysqldump Command Export data usage detailed

In the normal MySQL application, always encounter import data, export data, of course, there are many ways, this article, mainly introduces the application of mysqlmysqldump command for data import and export, I hope to help you.

The mysqldump command has a--where/-w parameter that sets the criteria for data export, which is basically the same as where in the SQL query command, and with it, we can export the part of 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 IDs greater than 100 to/tmp/test.sql in this file from the Test_data table in the test database

The code is as follows:

- - --

2. Export the entire database

Mysqldump-u user name-p database name > exported file name

The code is as follows:

- - > wcnc.sql

3. Export a database structure

The code is as follows:

- - - --  ---add-drop-table add a drop table before each create statement

Export library table (mysqldump) conditions

Mysqldump-u User name-p password-H host database a-w "SQL condition" –lock-all-tables > Path

The code is as follows:

1Mysqldump-Hhostname-Uusername-P dbname Tbname>Xxxx.sql**Exports the contents of the database table by the specified criteria. (-W option –where)1Mysqldump-Hhostname-Uusername-P dbname Tbname-W'ID >= 1 and id<= 10000'--skip-lock-tables > Xxxx.sqlor1Mysqldump-Hhostname-Uusername-P dbname Tbname--where= ' unit_id >= 1 and unit_id <= 10000 ' > ~/xxxx.sql

Go MySQL Import and Export tool mysqldump and Source command usage detailed