Mysqldump usage database export _ MySQL

Mysqldump usage database export mysqldump

BitsCN.com

Mysqldump usage database export

The mysqldump client can be used to dump databases or collect databases for backup or transfer data to another SQL Server (not necessarily a MySQL server ). Dump SQL statements that contain table creation and/or table loading.

If you back up the data on the server and the tables are all MyISAM tables, you should consider using mysqlhotcopy, because the backup and recovery can be performed faster. See Section 8.9, "mysqlhotcopy: database backup program ".

There are three methods to call mysqldump:

C code

Shell> mysqldump [options] db_name [tables]

Shell> mysqldump [options] --- database DB1 [DB2 DB3. ..]

Shell> mysqldump [options] -- all -- database

If no table is specified or the --- database or -- all -- database option is used, the whole database is dumped.

To obtain the options supported by mysqldump in your version, run mysqldump --- help.

If mysqldump does not have the -- quick or -- opt option, mysqldump loads the entire result set into the memory before dumping the result. If you dump a large database, problems may occur. This option is enabled by default, but can be disabled with -- skip-opt.

If you use the mysqldump program of the latest version to generate a dump and reinstall it to a MySQL server of a very old version, you should not use the -- opt or-e option.

Mysqldump supports the following options:

--- Help ,-?

Displays the help message and exits.

-- Add-drop -- database

Add the drop database statement before each create database statement.

-- Add-drop-tables

Add the drop table statement before each create table statement.

-- Add-locking

Use the lock tables and unlock tables statements to reference each table dump. It is faster to insert a heavy-duty dump file. See section 7.2.16 "INSERT statement speed ".

-- All -- database,-

Dump all tables in all databases. Like using the --- database option, name all databases in the command line.

-- Allow-keywords

You can create a keyword column name. The table name prefix should be added before each column name.

--- Comments [= {0 | 1}]

If it is set to 0, other information in the dump file is prohibited, such as the program version, server version, and host. -- Skip-comments and --- comments = 0 are the same. The default value is 1, which includes additional information.

-- Compact

Produce a small amount of output. This option disables annotation and enables the -- skip-add-drop-tables, -- no-set-names, -- skip-disable-keys, and -- skip-add-locking options.

-- Compatible = name

Generate more compatible output with other database systems or old MySQL servers. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, or no_field_options. Use commas to separate the values. These values have the same meaning as the corresponding options for setting the server SQL mode. See section 5.3.2 "SQL Server mode ".

This option does not guarantee compatibility with other servers. It only enables SQL Mode values that can make dump output more compatible currently. For example, -- compatible = oracle does not map the Oracle type or the data type that uses the Oracle annotation syntax.

-- Complete-insert,-c

Use the complete INSERT statement that includes the column name.

-- Compress,-C

Compress all information sent between the client and the server (if both support compression ).

-- Create-option

The create table statement contains all MySQL TABLE options.

--- Database,-B

Dump several databases. In general, mysqldump regards the 1st Name parameters in the command line as the database name, followed by the table name. This option is used to treat all name parameters as database names. The create database if not exists db_name and USE db_name statements are included in the output before each new DATABASE.

--- Debug [= debug_options],-# [debug_options]

Write debugging logs. The debug_options string is usually 'd: t: o, file_name '.

-- Default-character-set = charset

Use the charsetas default character set. See section 5.10.1 "character set for data and sorting ". If not specified, mysqldump uses utf8.

-- Delayed-insert

Use the insert delayed statement to INSERT rows.

-- Delete-master-logs

On the master replication server, after the dumping operation is completed, the binary log is deleted. This option automatically enables -- master-data.

-- Disable-keys,-K

For each table, use /*! 40000 alter table tbl_name disable keys */; and /*! 40000 alter table tbl_name enable keys */; the statement references the INSERT statement. In this way, the dump file can be loaded faster, because the index is created after all rows are inserted. This option is only applicable to MyISAM tables.

-- Extended-insert,-e

Use the multiline INSERT syntax that includes several VALUES lists. In this way, the dump file is smaller, and the insertion can be accelerated when the file is overloaded.

-- Fields-terminated-by = ..., -- fields-enclosed-by = ..., -- fields-optionally-enclosed-by = ..., -- fields-escaped-by = ..., -- row-terminated-by =...

These options are used in combination with the-T option, which has the same meaning as the corresponding clause of load data infile. See section 13.2.5 "load data infile syntax ".

-- First-slave,-x

I do not agree to use it. I will rename it -- lock-all-tables.

-- Flush-logs,-F

Refresh the MySQL server log file before starting the dump. This option requires the RELOAD permission. Note that if you use this option with the -- all -- database (or-A) option, refresh the log based on the database of each dump. The exception is that when -- lock-all-tables or -- master-data is used: in this case, the log is refreshed only once, and the log is refreshed after all the tables are locked. If you want to dump and refresh logs at the same time, use -- flush-logs together with -- lock-all-tables or -- master-data.

-- Force,-f

During table dump, the process continues even if an SQL error occurs.

-- Host = host_name,-h host_name

Dump data from the MySQL server of the given host. The default host is localhost.

-- Hex-blob

Dump binary string columns with hexadecimal symbols (for example, 'ABC' is changed to 0x616263 ). The affected columns include BINARY, VARBINARY, and BLOB.

-- Lock-all-tables,-x

Lock All tables in all databases. Global read locks are used in the overall dump process. This option automatically disables -- single-transaction and -- lock-tables.

-- Lock-tables,-l

Lock All tables before starting the dump. Use read local to lock tables to allow concurrent insertion of MyISAM tables. For transaction tables such as InnoDB and BDB, -- single-transaction is a better option because it does not need to lock the table at all.

When dumping multiple databases, -- lock-tables locks the tables for each database. Therefore, this option does not guarantee the logical consistency of the tables in the dump file between databases. The dump status of different database tables can be completely different.

-- Master-data [= value]

This option writes the location and file name of the binary log to the output. This option requires RELOAD permission and binary logs must be enabled. If the option value is equal to 1, the location and file name are written to the dump output in the form of the change master statement. if you use this SQL statement to dump the MASTER server to set the slave server, start from the correct location of the binary log of the master server. If the option value is 2, the change master statement is written as an SQL comment. If the value is omitted, this is the default action.

-- Master-data

Option to enable -- lock-all-tables, unless -- single-transaction is also specified (in this case, the Global read lock is obtained only for a short time when the dump is started. See also -- single-transaction. In either case, log-related actions occur during dump. This option automatically disables -- lock-tables.

-- No-create-db,-n

This option disables create database /*! 32312 if not exists */db_name statement, IF the --- database or -- all -- database option is provided, it is included in the output.

-- No-create-info,-t

Do not re-CREATE the create table statement for each dump TABLE.

-- No-data,-d

Do not write any row information of the table. This is useful if you only want to dump the table structure.

-- Opt

This option is stenographer; it is equivalent to specifying -- add-drop-tables -- add-locking -- create-option -- disable-keys -- extended-insert -- lock-tables -- quick -- set-charset. It provides a fast dump operation and generates a dump file that can be quickly loaded into the MySQL server. This option is enabled by default, but can be disabled with -- skip-opt. To disable only the option enabled with-opt, use the -- skip format. for example, -- skip-add-drop-tables or -- skip-quick.

-- Password [= password],-p [password]

The password used to connect to the server. If you use the short option format (-p), there cannot be a space between the option and password. If the password value after the -- password or-p option is ignored in the command line, you are prompted to enter one.

-- Port = port_num,-P port_num

The TCP/IP port used for connection.

-- Protocol = {TCP | SOCKET | PIPE | MEMORY}

The connection protocol used.

-- Quick,-q

This option is used to dump large tables. It forces mysqldump to retrieve the rows in the table from the server one row at a time, instead of retrieving all rows and caching them to the memory before the output.

-- Quote-names,-Q

Use the '''' character to reference databases, tables, and column names. If the server SQL mode includes the ANSI_QUOTES option, use the '"' character to reference the name. This option is enabled by default. You can disable it with -- skip-quote-names, but this option should be followed by other options. for example, you can enable -- quote-names -- compatible.

-- Result-file = file,-r file

Redirects the output to the specified file. This option is applied in Windows because it prohibits the conversion of the new line '/N' character into'/r/N' carriage return and return/New Line sequence.

-- Routines,-R

Dump stored programs (functions and programs) in the dumped database ). The output produced using --- routines contains the create procedure and create function statements to recreate the subroutine. However, these statements do not include attributes, such as subprogram Definers or create or modify timestamps. This indicates that when the subprograms are overloaded, the user should be set as the overload user when they are created, and the timestamp is equal to the overload time.

If you want to create a subroutine that uses the original definer and timestamp attributes, -- routines is not used. Instead, use a mysql account with the corresponding permissions of the MySQL database to directly dump and reload the contents of the mysql. proc table.

This option is added in MySQL 5.1.2. Before that, the storage program does not dump.

-- Set-charset

Add set names default_character_set to the output. This option is enabled by default. To disable the set names statement, use -- skip-set-charset.

-- Single-transaction

This option issues a begin SQL statement before dumping data from the server. It only applies to transaction tables, such as InnoDB and BDB, because it will then dump consistent database states when a BEGIN is issued without blocking any applications.

When using this option, remember that only InnoDB tables can be dumped in a consistent state. For example, when this option is used, any dumped MyISAM or HEAP table can still change the status.

The -- single-transaction option and -- lock-tables option are mutually exclusive, because lock tables will implicitly commit any pending transactions.

To dump a large table, use -- quick to use this option.

-- Socket = path,-S path

The socket file used when connecting to localhost (the default host.

-- Skip -- comments

See the description of the --- comments option.

-- Tab = path,-T path

Generate a tab-separated data file. For each dump table, mysqldump creates a tbl_name. SQL file containing the CREATE TABLE statement of the table to be created, and a tbl_name.txt file containing its data. The option value is the directory where the file is written.

The format of the. txt data file is to use a tab character between the column value and the new line after each line. You can use the -- fields-xxx and -- row -- xxx options to explicitly specify the format.

Note: This option is only applicable when the mysqldump and mysqld servers run on the same machine. You must have the FILE permission and the server must have the permission to write files in the directory you specified.

-- Tables

Overwrite the --- database or-B option. All parameters following the option are treated as table names.

-- Triggers

Dump triggers for each dump table. This option is enabled by default, and -- skip-triggers is used to disable it.

-- Tz-utc

Add SET TIME_ZONE = '+ 00:00' to the dump file so that the TIMESTAMP column can be dumped and reloaded between servers with different time zones. (If this option is not used, the TIMESTAMP column is dumped and reloaded between the source and target servers with the local time zone ). -- Tz-utc can also protect the changes caused by timeout. -- Tz-utc is enabled by default. To disable it, use -- skip-tz-utc. This option is added to MySQL 5.1.2.

-- User = user_name,-u user_name

The MySQL User name used to connect to the server.

-- Verbose,-v

Lengthy mode. Prints detailed information about program operations.

-- Version,-V

Display version information and exit.

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

Only Records selected by the WHERE condition are dumped. Note that if the condition contains special spaces or characters for the command interpreter, you must reference the condition.

For example:

"-- Where = user = 'jimf '"

"-Wuserid> 1"

"-Wuserid <1"

-- Xml,-X

Write the dump output as XML.

You can also use the -- var_name = value option to set the following variables:

Max_allowed_packet

The maximum size of the cache area for client/server communication. The maximum size is 1 GB.

Net_buffer_length

The initial size of the cache area for client/server communication. When a multi-row insert statement is created (like the -- extended-insert or -- opt option), mysqldump creates a row with the length of net_buffer_length. If this variable is added, make sure that the variable net_buffer_length in the MySQL server is at least that large.

You can also use the -- set-variable = var_name = value or-O var_name = value syntax to set variables. However, I do not agree to use this syntax.

Example 1

A. export mysqldump-u username-p database name> exported file name

C code

[Root @ linux mysql] # mysqldump-u dbadmin-p myblog>/home/zhangy/blog/database_bak/myblog. SQL

B. export a table mysqldump-u user name-p database name table name> exported file name

C code

[Root @ linux mysql] # mysqldump-u dbadmin-p myblog wp_users>/home/zhangy/blog/database_bak/blog_users. SQL

C. export a database structure-d no data -- add-drop-table add a drop table before each create statement

C code

[Root @ linux mysql] # mysqldump-u dbadmin-p-d -- add-drop-table myblog>/home/zhangy/blog/database_bak/blog_struc. SQL

D. export a table structure of the database.

C code

[Root @ linux mysql] # mysqldump-u dbadmin-p-d -- add-drop-table myblog wp_users>/home/zhangy/blog/database_bak/blog_users_struc. SQL

Example 2

Mysqldump export database and compress

C code

$ Mysqldump-u user-ppassword database | xz> database. SQL. xz # use xz compression. if you change to gzip, you can change it to gz compression. bzip2 is the same. pay attention to the last extension.

$ Unxz-c database. SQL. xz | mysql-u user-ppassword database # you do not need to release compressed SQL scripts for direct import operations. If compression is performed in other formats, you need to decompress the command accordingly, use the parameters extracted to stdout

$ Pv database. SQL. xz | unxz-c | mysql-u user-ppassword database # Do you want to see the import progress? No problem. you can run the pv command. you need to install the pv command on your own. The ubuntu system directly uses apt-get install pv. after adding the rpmfusion source to RHEL/centos, you need to run the yum install pv command.

BitsCN.com