Shulou(Shulou.com)05/31 Report--

This article introduces you how to understand mysqldump backup database, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Mysqldump is used to export the structure and data of the mysql database.

Usage: mysqldump [OPTIONS] database [tables] # Guide Table

OR mysqldump [OPTIONS]-- databases [OPTIONS] DB1 [DB2 DB3...] # Guide Library

OR mysqldump [OPTIONS]-- all-databases [OPTIONS]

The default options are read from the following files in the given order:

/ etc/my.cnf / etc/mysql/my.cnf / u01/app/mysql/my.cnf ~ / .my.cnf

The following groups are read: mysqldump client

The following options may be given as the first argument:

-- print-defaults prints a list of program parameters and exits.

-- no-defaults do not read the default options from any options file.

-- defaults-file=# read-only from the default option #.

-- defaults-extra-file=# reads the global file and then reads it.

-A,-- all-databases exports all databases. This is the same as-- databases selects all databases

-Y,-- all-tablespaces exports all tablespaces

-y,-- no-tablespaces does not dump any tablespace information.

-- add-drop-database adds a DROP DATABASE before each creation.

-- add-drop-table adds DROP TABLE before each creation. (on by default, disable it using-- skip-add-drop-table)

-- add-locks adds locks around the INSERT statement. (on by default, disable it with-- skip-add-locks)

-- allow-keywords allows you to create column names as keywords.

-- apply-slave-statements adds "STOP SLAVE" to the bottom of the dump before "CHANGE MASTER" and "START SLAVE".

-- character set of the character-sets-dir=name file directory

-I,-- comments comment information (on by default, disable it using-- skip-comments)

-- compatible=name changes the dump to be compatible with the given mode. By default, tables are dumped in a format appropriate for MySQL. The legal mode is: ansi,mysql323,mysql40,postgresql,oracle,mssql,db2,maxdb,no_key_options,no_table_options,no_field_options. Several modes separated by commas can be used. Note: MySQL server version 4.1.0 or later is required. This option will be ignored in previous server versions.

-- compact reduces output (for debugging). Disable structure comments and header / footer structure. Enable the option-- skip-add-drop-table-- skip-add-locks-- skip-comments-- skip-disable-keys-- skip-set-charset.

-c,-- complete-insert uses the full insert statement

-C,-- compress uses compression in server / client protocols.

-a,-- create-options includes all MySQL-specific creation options (default is on; use-- skip-create-options disabled).

-B,-- databases dumps several databases. All name parameters are treated as database names.

-#,-- debug [= #] this is a non-debug version. Capture this and exit.

-- debug-check checks memory and opens file usage on exit.

-- debug-info prints some debugging information when exiting.

Default-character-set=name sets the default character set.

-- delayed-insert inserts rows using INSERT DELAYED.

-- delete-master-logs deletes logs on the primary server after backup. This automatically enables-- master-data.

-K,-- disable-keys'/ *! 40000 ALTER TABLE tb_name DISABLE KEYS * /; and

'/ *! 40000 ALTER TABLE tb_name ENABLE KEYS * /; will be put

In the output.

-- dump-slave [= #] (the default is that on; uses-- skip-disable-keys to disable. This will cause the binary log location and file name of the primary station to be appended to the dump data output Setting the value to 1 will print as a CHANGE MASTER command in the dump data output; if equal to 2, the command will be prefixed with a comment symbol. This option turns on-- lock-all-tables, unless-- single-transaction is specified (in this case, global read locking occurs only for a short period of time at the beginning of the dump-- don't forget to read-- under single-transaction). In all cases, any action on the log will occur at the exact time of the dump. Option will automatically close-lock the table.

-E,-- events dump event.

-e,-- extended-insert uses a multiline INSERT syntax that contains multiple VALUES lists (on; is disabled by default using-- skip-extended-insert).

-- fields-terminated-by=name Terminator

-- fields-enclosed-by=name delimiter

-- optional delimiter in fields-optionally-enclosed-by=name field

-- fields-escaped-by=name escape character

-F,-- flush-logs refreshes the log files in the server before starting the dump. Note that if you dump many databases at a time (using the option-- databases = or-- all-databases), the logs will be refreshed for each dumped database. Exception: use-- lock-all-tables or-- master-data: in this case, the log will be refreshed only once, corresponding to the time when all tables are locked. So if you want your dump and log refresh to occur at the same exact time, you should use-- lock-all-tables or-- master-data and-- flush-logs.

-- flush-privileges issues a FLUSH PRIVILEGES statement after dumping the mysql database. This option should be correctly restored at all times using the dump containing the mysql database and any other databases that depend on the data in the mysql database.

-f,-- force continues to execute even if there is an error

-- help to view help information

Hex-blob dumps binary files in hexadecimal

-h,-- host=name connects to the host

-- ignore-table=name does not back up specified tables. To specify multiple tables to ignore, use the directive multiple times, once for each table. Each table must specify both the database and the table name, for example,-ignore-table = database.table.

-- include-master-host-port adds' MASTER_HOST =, MASTER_PORT ='to 'CHANGE MASTER TO..' in the dump generated using-- dump-slave.

-- insert-ignore inserts rows using INSERT IGNORE.

-- lines-terminated-by=name line Terminator

-x,-- all tables are locked during a lock-all-tables backup by adding a global lock. Will automatically close-- single-transaction and-- lock-tables off.

-l,-- lock-tables locks all tables (enabled by default, disabled using-- skip-lock-tables)

-- log-error=name error log file

-- master-data [= #] this will cause the binary log location and file name to be appended to the output. If equal to 1, it is printed as a CHANGE MASTER command; if equal to 2, the command is prefixed with an annotation symbol. This option turns on-- lock-all-tables unless-- single-transaction is specified (in this case, global read locking occurs only for a short time at the beginning of the dump; don't forget to read-- single-transaction). In all cases, any action on the log will occur at the exact time of the dump. Option automatically turns off-- lock-tables.

-- the maximum packet length that max-allowed-packet=# sends to or receives from the server.

-- the buffer size that net-buffer-length=# uses for TCP / IP and socket communication.

-- no-autocommit uses autocommit / commit statements to encapsulate tables.

-n,-- no-create-db does not use CREATE DATABASE... The IF EXISTS statement, if given-- all-databases or-- databases, is usually the database output for each dump.

-t,-- no-create-info does not write table information

-d,-- no-data does not export data

-N,-- no-set-names does not set the character set, just like-- skip-set-charset

Opt and-- add-drop-table,-- add-locks,-- create-options,--quick,-- extended-insert,-- lock-tables,-- set-charset,and-- disable-keys Enabled by default, like disable with--skip-opt.

-- order-by-primary if there is a primary key or unique key, sort by such key. It is useful to dump from the MyISAM to the InnoDB table, but it takes longer for the dump itself.

-p,-- password [= name] password

-P,-- port=# connection port

-- protocol=name connection Protocol (tcp, socket, pipe)

Memory).

-Q,-- quick do not buffer the query and dump it directly to stdout (the default is that on; uses-- skip-quick is disabled).

-Q,-- quote-names quotation marks table and column names with backquotes (`) (on; is disabled by default using-- skip-quote-names).

-- replace uses REPLACE INTO instead of INSERT INTO.

-r,-- result-file=name is output directly to the given file. This option should be used in systems that use carriage return newline pairs (\ r\ n) to separate lines of text (for example, DOS,Windows). This option ensures that only a single newline character is used.

-R,-- routines dump storage routines (functions and procedures).

Set-charset adds "SET NAMES default_character_set" to the output. (the default is that on; uses-- skip-set-charset to disable).

-- single-transaction creates a consistent snapshot by dumping all tables in a single transaction. Applies only to tables stored in storage engines that support multiple versions (currently only InnoDB); dumps are not guaranteed to be consistent with other storage engines. When a single transaction dump is in progress, in order to ensure valid dump files (correct table contents and binary log locations), no other connection should use the following statement: ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE,as-consistent snapshots are not isolated from them. Option is automatically turned off-- lock-tables.

-- dump-date puts the dump date at the end of the output. (on by default; disable by using-- skip-dump-date.)

-- skip-opt disabled-- opt. Disables-add-drop-table,-add-locks

-create-options,-quick, extended-insert

-lock-tables,-set-charset, and-disable-keys.

-S,-- socket for socket=name connection

-T,-- tab=name creates a tabular delimited text file for each table of a given path. Create .sql and .txt files. Note: this only works if mysqldump is running on the same machine as the mysql server.

-- tables override option-- databases (- B).

-- trigger for triggers to dump each dump table. (the default is on; use-- skip-triggers disabled).

-- tz-utc SET TIME_ZONE ='+ 00 skip- tz-utc'at the top of the dump to allow TIMESTAMP data to be dumped when servers in different time zones move between servers in different time zones (on; use-- skip- tz-utc is disabled by default. )

-u,-- user=name user name.

-v,-- verbose prints information for each stage.

-V,-- version outputs version information and exits.

-w,-- where=name exports the data in the query condition

-X,-- xml uses XML format

-plugin-dir=name client plug-in directory

-- default authentication client plug-in used by default-auth=name.

Variables (--variable-name=value)

And boolean options {FALSE | TRUE} Value (after reading options)

All-databases FALSE

All-tablespaces FALSE

No-tablespaces FALSE

Add-drop-database FALSE

Add-drop-table TRUE

Add-locks TRUE

Allow-keywords FALSE

Apply-slave-statements FALSE

Character-sets-dir (No default value)

Comments TRUE

Compatible (No default value)

Compact FALSE

Complete-insert FALSE

Compress FALSE

Create-options TRUE

Databases FALSE

Debug-check FALSE

Debug-info FALSE

Default-character-set utf8

Delayed-insert FALSE

Delete-master-logs FALSE

Disable-keys TRUE

Dump-slave 0

Events FALSE

Extended-insert TRUE

Fields-terminated-by (No default value)

Fields-enclosed-by (No default value)

Fields-optionally-enclosed-by (No default value)

Fields-escaped-by (No default value)

Flush-logs FALSE

Flush-privileges FALSE

Force FALSE

Hex-blob FALSE

Host (No default value)

Include-master-host-port FALSE

Insert-ignore FALSE

Lines-terminated-by (No default value)

Lock-all-tables FALSE

Lock-tables TRUE

Log-error (No default value)

Master-data 0

Max-allowed-packet 25165824

Net-buffer-length 1046528

No-autocommit FALSE

No-create-db FALSE

No-create-info FALSE

No-data FALSE

Order-by-primary FALSE

Port 0

Quick TRUE

Quote-names TRUE

Replace FALSE

Routines FALSE

Set-charset TRUE

Single-transaction FALSE

Dump-date TRUE

Socket (No default value)

Tab (No default value)

Triggers TRUE

Tz-utc TRUE

User (No default value)

Verbose FALSE

Where (No default value)

Plugin-dir (No default value)

Default-auth (No default value)

-

1. Guide list

Mysqldump-uroot-p mysql user

2. Guide order library

Mysqldump-uroot-phwj3509-- databases hwj > test.sql

3. Guide multiple tables

Mysqldump-uroot-p mysql user proc > test.sql

4. Guide multi-library

Mysqldump-uroot-p-- databases hwj 51vj > test.sql

5. Incremental backup

Incremental backup under mysqldump is actually realized by mysqldump complete and binlog logs. New logs are generated by adding-- flush-logs to the mysqldump command. Use mysqlbinlog to restore when restoring.

Mysqldump-uroot-phwj3509-single-transaction-flush-logs-master-data=2-all_databases > backup.sql

On how to understand the mysqldump backup database to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.