Mysql import and export tools Mysqldump and Source command usage details

In PHP website development, often encounter Mysql database backup or database migration work, at this moment how Mysql import and export data in the database is the key, provides a command line tools export Mysqldump Mysql itself and Mysql Source SQL data import and export import command, derived by Mysql command-line tools Mysqldump command to Mysql data export to text format (txt) SQL file, The Mysql Source command enables you to import SQL files into the Mysql database.

Mysql command line export tool Mysqldump command details

By default, the Mysql command line export tool Mysqldump.exe is stored in the bin subdirectory of the Mysql installation directory. When using the Mysqldump export database, first make sure that the Mysql service is started.
M
ysqldump exports the basic usage of the command

mysqldump -u  The user name  -p [--opt] DATABASENAME [Table] > export SQL The file name  

The SQL file exported by default Mysqldump contains not only the exported data, but also the structure information of all the data tables in the exported database.
In addition, SQL files exported using Mysqldump without an absolute path are saved in the bin directory by default.
The Mysqldump command parameter is optional. If this option is used, the quick, add-drop-table, add-locks, extended-insert, lock-tables parameters of the Mysqldump command are activated.
The Mysqldump command exports the data directly to the specified SQL file.
WSD: as the name implies, add the DROP-TABLE IF EXISTS statement before each CREATE TABEL command to prevent table duplication.
The add-locks: locks and unlocks specific data tables before and after INSERT data. You can open the SQL file exported by Mysqldump. LOCK TABLES and UNLOCK TABLES statements appear before INSERT.
Wk03: this parameter indicates that multiple rows can be inserted.
More Mysqldump command arguments you can pass

 
Mysqldump --help 

The command looks at all the parameters that Mysqldump contains and how they are supported.
An example of an Mysqldump export command is shown below
The Mysql database contains the mysql database by default after installation. I will take this database as an example to demonstrate various export instances of Mysqldump.
Export the database using Mysqldump

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

Export a single table using Mysqldump

 

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

Export the data table structure using Mysqldump

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

Note: Mysqldump command parameter: no-data. As the name implies, the data exported by Mysqldump does not contain INSERT data, but only the structure information of Mysql database table user. You can also use -d for this parameter.
Export the data for the specified condition using Mysqldump

 
C:\Program Files\MySQL\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, set the exported INSERT data condition that the user field in the user table is INSERT record of root by setting the Mysqldump command parameter, where=conditions. With this parameter, you can use Mysqldump to export the data you want, which is very convenient. Note that you need to use double quotation marks before and after the where option. You can use single quotation marks for specific conditions, otherwise you will get an error when parsing the Mysqldump command-line arguments. You can also specify multiple where arguments.

Mysql database import command Source

The most commonly used database import command of Mysql is Source. The usage of Source is very simple. First, you need to enter the command line management interface of Mysql database, and then select the database to import, i.e

 
USER  The database  
source  Has been exported SQL file  

Note the specific directory address of the SQL file to import, preferably /.
So far, the usage of Mysql command line export tool Mysqldump command and Mysql import command Source has been introduced. Compared with Mysql Source command, Mysqldump export has more functions. Reasonable use of Mysqldump command parameters can achieve different effects.

The mysqldump command exports data

In the usual mysql application, there will always be data import and data export, of course, there are many ways, this article, mainly introduces the use of mysqlmysqldump command data import and export, I hope to help you.

The mysqldump command has a --where/ -w parameter, which is used to set the conditions for data export. It is basically the same as where in the SQL query command. With it, we can export the data you need from the database.

1. The format of the command is as follows:

mysqldump-u username -p password database name table name --where=" filter condition" > Export file path

Example:

Export id > 100 data from the test_data table of the test database to the file /tmp/ test.sql

 
mysqldump -uroot -p123456 test test_data --where=" id > 100" > /tmp/test.sql 

Export the entire database

mysqldump-u username -p database name > Exported file name

 
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql 

3. Export a database structure

 
mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:wcnc_db.sql 
-d  There is no data  --add-drop-table  At the end of each create Add before statement 1 a drop table 

Export library table (mysqldump) conditions

mysqldump-u user name -p password -h host database - a-w "sql conditions" - lock-all-tables > The path

 
Mysqldump --help 

0