MYSQL uses mysqldump to export some data of a table _ MySQL

MYSQL uses mysqldump to export some data of a table.

BitsCN.com

MYSQL uses mysqldump to export some data of a table

Example:

D:/wamp/mysql/bin> mysqldump-h mysql server IP-u user name-p password database name

-- Where = "filter condition"> Export file path;

MySQLdump is a data export tool that comes with MySQL. we usually use it to export data from MySQL. but sometimes we need to export some data from a table in MySQL database. what should we do?

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.

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 from the sdata table of meteo database to the/home/xyx/Temp. SQL file.

Mysqldump-uroot-p123456 meteo sdata -- where = "sensorid = 11 and fieldid = 0">/home/xyx/Temp. SQL

In addition, you can directly export the text file *. txt

Mysqldump-uroot-p123456 meteo sdata -- where = "sensorid = 11 and fieldid = 0">/home/xyx/Temp.txt

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

The following are some usage parameters of mysqldump:

Back up Database

# Mysqldump database name> database backup name

# Mysqldump-A-u username-p password database name> database backup name

# Mysqldump-d-A -- add-drop-table-uroot-p> xxx. SQL

1. the exported structure does not export data

Mysqldump-d database name-uroot-p> xxx. SQL

2. export data without exporting the 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 name> xxx. SQL

# Mysqldump [OPTIONS] database [tables]

Mysqldump supports the following options:

-- Add-locks

Add lock tables and unlock table before each TABLE is exported. (To make it faster to insert data to MySQL ).

-- Add-drop-table

Add a drop table before each create statement.

-- Allow-keywords

Names of columns allowed to be created as keywords. This is done by the table name prefix on each column name.

-C, -- complete-insert

Use the complete insert statement (with the column name ).

-C, -- compress

If both the client and server support compression, all information is compressed between the two.

-- Delayed

Use the insert delayed command to INSERT rows.

-E, -- extended-insert

Use the new multiline INSERT syntax. (A more compact and faster insert statement is provided)

-#, -- Debug [= option_string]

Tracking program usage (for debugging ).

-- 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-T options and have the same meaning as the load data infile clause.

Load data infile syntax.

-F, -- flush-logs

Wash out the log files on the MySQL server before starting the export.

-F, -- force,

Even if we get an SQL error while exporting a table, continue.

-H, -- host = ..

Export data from the MySQL server on the named host. The default host is localhost.

-L, -- lock-tables.

Lock All tables for start export.

-T, -- no-create-info

Create table statement)

-D, -- no-data

No row information is written to the table. If you only want to export the structure of a table, this is very useful!

-- Opt

Same as -- quick -- add-drop-table -- add-locks -- extended-insert -- lock-tables.

You should be given the fastest export possible for reading a MySQL server.

-Pyour_pass, -- password [= your_pass]

The password used to connect to the server. If you do not specify "= your_pass", mysqldump requires a password from the terminal.

-P port_num, -- port = port_num

The TCP/IP port used to connect to a host. (This is used to connect to a host other than localhost because it uses Unix sockets .)

-Q, -- quick

Directly export the data to stdout without buffering the query; use mysql_use_result () to do it.

-S/path/to/socket, -- socket =/path/to/socket

The socket file used when connecting to localhost (which 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 data. Note: This only works when mysqldump runs on the same machine where the mysqld daemon is running .. The format of the txt file is determined by the options -- fields-xxx and -- lines -- xxx.

-U user_name, -- user = user_name

The username used by MySQL to connect to the server. The default value is your Unix login name.

-O var = option, -- set-variable var = option to set the value of a variable. Possible variables are listed below.

-V, -- verbose

Lengthy mode. Print out more information about the program.

-V, -- version

Print the version information and exit.

-W, -- where = 'Where-condition'

Only selected records are exported. Note that the quotation marks are mandatory!

"-- Where = user = 'jimf '"-wuserid> 1 ""-wuserid <1"

Import data:

Mysqldump exports complete SQL statements, so it is easy to import data into mysql client programs:

# Mysql database name <file name

# Source/tmp/xxx. SQL

BitsCN.com