MySQL uses mysqldump to export part of a table's data

Mysqldump is a MySQL-brought export data tool that we usually use to export MySQL, but sometimes we need to export some of the data from a table in a MySQL database.

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.

The command format is as follows:

Mysqldump-u User name-p password database name table name--where= "filter Condition" > Export file path

Example:

Export sensorid=11 and fieldid=0 data from the Sdata table in the Meteo database to/home/xyx/temp.sql this file

mysqldump-uroot-p123456 Meteo sdata--where= "sensorid=11 and fieldid=0" >/home/xyx/temp.sql

Alternatively you can export the text file directly *.txt

mysqldump-uroot-p123456 Meteo sdata--where= "sensorid=11 and fieldid=0" >/home/xyx/temp.txt

/////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////

The following are some of the usage parameters of mysqldump

Backing Up the database
#mysqldump database name > Database backup Name

#mysqldump-A-u user name-p password database name > database backup Name

#mysqldump-D-A--add-drop-table-uroot-p >xxx.sql

1. Export structure does not export data

mysqldump-d database name-uroot-p > Xxx.sql

2. Export data does not export structure

MYSQLDUMP-T database name-uroot-p > Xxx.sql

3. Export Data and table structure

Mysqldump database name-uroot-p > Xxx.sql

4. Export the structure of a specific table

Mysqldump-uroot-p-B database name--table table name > Xxx.sql

#mysqldump [OPTIONS] database [tables]

Mysqldump supports the following options:

--add-locks
Add lock tables before each table is exported and then unlock table. (To make it faster to insert into MySQL).
--add-drop-table
Add a drop table before each create statement.
--allow-keywords
Allows you to create column names that are keywords. This is done by prefixing the table name with each column name.
-C,--complete-insert
Use the full INSERT statement (with the column name).
-C,--compress
If both the client and the server support compression, compress all the information between the two.
--delayed
Insert a row with the insert delayed command.
-E,--extended-insert
Use the new multi-line insert syntax. (Give a more condensed and faster INSERT statement)
-#,--debug[=option_string]
The use of the tracker (for debugging purposes).
--help
Displays a help message and exits.
--fields-terminated-by= ...

--fields-enclosed-by= ...

--fields-optionally-enclosed-by= ...

--fields-escaped-by= ...

--fields-terminated-by= ...
These options are used with the-T selection and have the same meaning as the corresponding load DATA infile clause.
LOAD DATA infile syntax.
-F,--flush-logs
Before starting the export, wash down the log files in the MySQL server.
-F,--force,
Even if we get a SQL error during the export of a table, continue.
-H,--host=.
Export data from a MySQL server on a named host. The default host is localhost.
-L,--lock-tables.
Locks all tables for starting export.
-T,--no-create-info
Do not write table creation information (CREATE TABLE statement)
-D,--no-data
No row information is written to the table. This is useful if you only want to export the structure of a table!
--opt
With--quick--add-drop-table--add-locks--extended-insert--lock-tables.
Should give you the fastest possible export for reading into a MySQL server.
-pyour_pass,--password[=your_pass]
The password to use when connecting to the server. If you do not specify the "=your_pass" section, Mysqldump requires a password from the terminal.
-P Port_num,--port=port_num
The TCP/IP port number to use when connecting to a single host. (This is used to connect to a host other than localhost because it uses a UNIX socket.) ）
-Q,--quick
Do not buffer queries, export directly to stdout; use Mysql_use_result () to do it.
-s/path/to/socket,--socket=/path/to/socket
A socket file that is used with localhost when it is connected (it is the default host).
-T,--tab=path-to-some-directory
For each given table, create a Table_name.sql file that contains the SQL create command, and a table_name.txt file that contains the data. Note: This only works when the mysqldump is running on the same machine as the mysqld daemon. The format of the. txt file is determined by the--fields-xxx and--lines--xxx options.
-U user_name,--user=user_name
The user name that MySQL uses when connecting to the server. The default value is your UNIX login name.
-O var=option,--set-variable var=option Sets the value of a variable. The possible variables are listed below.
-V,--verbose
Verbose mode. Print out more information that the program has done.
-V,--version
Print the version information and exit.
-W,--where= ' where-condition '
Only the selected records are exported; Note that the quotation marks are mandatory!
"--where=user= ' JIMF '" "-wuserid>1" "-wuserid<1"

Import data:

Since mysqldump exports a complete SQL statement, it is easy to import the data using the MySQL client program:

#mysql Database name < file name

Or

#show databases;

Then select the database to be imported:

#use * * *;

#source/tmp/xxx.sql

///////////////////////////////////////////////////////////////////////////////////////////////////////////////

MySQL uses mysqldump to export part of a table's data