Mysqldump instance for backing up and restoring mysql data _ MySQL

Mysqldump backup and restoration of mysql data instance mysqldump

For mysql database backup and restoration, we generally use the following two methods:

1. use into outfile and load data infile to import and export backup data

The exported data can be in the specified format and exported as pure data without table creation information. you can directly import different tables in the same database, which is more flexible than mysqldump.

Let's look at the following example:
(1) the following mysql command exports data from the select mytable table to the/home/db_bak2012 file.

The code is as follows: select * from mytable where status! = 0 and name! = ''To outfile'/home/db_bak2012'
Fields terminated by '| 'enabled by' "'lines terminated by '\ r \ n ';
To import the data just backed up, you can use the load file method. the following mysql command imports the exported data into the mytable_bak table:

The code is as follows: load data infile '/home/db_bak2012' into table mytable_bak
Fields terminated by '| 'enabled by' "'lines terminated by '\ r \ n ';
2. use mysqldump to export a database with fixed conditions

Let's look at several common use cases:
(1) export the entire database

The code is as follows: mysqldump-u username-p database name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. SQL
(2) export a table

The code is as follows: mysqldump-u user name-p database name table name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc users> wcnc_users. SQL
(3) export a database structure

The code is as follows: mysqldump-u wcnc-p-d -- add-drop-table smgp_apps_wcnc> d: \ wcnc_db. SQL
#-D do not export data only export structure -- add-drop-table add a drop table before each create statement
(4) import database, commonly used source commands

The code is as follows: # go to the mysql database console,
Mysql-u root-p
Mysql> use database
Mysql> set names utf8; (first confirm the encoding, if not set may appear garbled, note not UTF-8)
# Run the source Command. the following parameter is a script file (for example,. SQL used here)
Mysql> source d: \ wcnc_db. SQL
The above instance is only the most basic. sometimes we may need to export multiple databases in batches, we can add -- databases or-B, the following statement:

The code is as follows: mysqldump-uroot-p -- databases test mysql # separated by spaces
In other cases, we may need to back up all the databases in the database, so we can use-all-databases, the following statement:

The code is as follows: mysqldump-uroot-p-all-databases
Appendix: detailed description of mysqldump parameters

Parameter description

-- All-databases,-

Export all databases.

Mysqldump-uroot-p -- all-databases

-- All-tablespaces,-Y

Export all tablespaces.

Mysqldump-uroot-p -- all-databases -- all-tablespaces

-- No-tablespaces,-y

No tablespace information is exported.

Mysqldump-uroot-p -- all-databases -- no-tablespaces

-- Add-drop-database

Add the drop database statement before each database is created.

Mysqldump-uroot-p -- all-databases -- add-drop-database

-- Add-drop-table

Add the drop TABLE statement before creating each data table. (It is enabled by default. use the -- skip-add-drop-table cancel option)

Mysqldump-uroot-p -- all-databases (the drop statement is added by default)

Mysqldump-uroot-p -- all-databases-skip-add-drop-table (cancel the drop statement)

-- Add-locks

Add lock tables and unlock table before each TABLE is exported. (It is enabled by default. use the -- skip-add-locks cancel option)

Mysqldump-uroot-p -- all-databases (the LOCK statement is added by default)

Mysqldump-uroot-p -- all-databases-skip-add-locks (cancel LOCK statement)

-- Allow-keywords

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

Mysqldump-uroot-p -- all-databases -- allow-keywords

-- Apply-slave-statements

Add 'stop SLAVE 'before 'change master' and 'start SLAVE' at the end of the export '.

Mysqldump-uroot-p -- all-databases -- apply-slave-statements

-- Character-sets-dir

Character Set file directory

Mysqldump-uroot-p -- all-databases -- character-sets-dir =/usr/local/mysql/share/mysql/charsets

-- Comments

Add comments. It is enabled by default and can be canceled using -- skip-comments

Mysqldump-uroot-p -- all-databases (default record comment)

Mysqldump-uroot-p -- all-databases -- skip-comments (uncomment)

-- Compatible

The exported data will be compatible with other databases or old MySQL versions. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options, etc,

Use commas to separate the values. It does not guarantee full compatibility, but is as compatible as possible.

Mysqldump-uroot-p -- all-databases -- compatible = ansi

-- Compact

Export less output information (for debugging ). Remove comments, headers, and tails. Option: -- skip-add-drop-table -- skip-add-locks -- skip-comments -- skip-disable-keys

Mysqldump-uroot-p -- all-databases -- compact

-- Complete-insert,-c

Use the complete insert statement (including the column name ). This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure.

Mysqldump-uroot-p -- all-databases -- complete-insert

-- Compress,-C

Enable compression between the client and server to pass all information

Mysqldump-uroot-p -- all-databases -- compress

-- Create-options,-

The create table statement contains all MySQL feature options. (Enabled by default)

Mysqldump-uroot-p -- all-databases

-- Databases,-B

Export several databases. All name parameters following the parameter are considered as the database name.

Mysqldump-uroot-p -- databases test mysql

-- Debug

Outputs debug information for debugging. Default value: d: t: o,/tmp/mysqldump. trace

Mysqldump-uroot-p -- all-databases -- debug

Mysqldump-uroot-p -- all-databases -- debug = "d: t: o,/tmp/debug. trace"

-- Debug-check

Check memory and open file usage instructions and exit.

Mysqldump-uroot-p -- all-databases -- debug-check

-- Debug-info

Output debugging information and exit

Mysqldump-uroot-p -- all-databases -- debug-info

-- Default-character-set

Sets the default character set. the default value is utf8.

Mysqldump-uroot-p -- all-databases -- default-character-set = latin1

-- Delayed-insert

Use insert delayed to export data

Mysqldump-uroot-p -- all-databases -- delayed-insert

-- Delete-master-logs

The log is deleted after the master backup. this parameter will automatically activate -- master-data.

Mysqldump-uroot-p -- all-databases -- delete-master-logs

-- Disable-keys

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. This allows you to import dump files faster, because it creates an index after inserting all rows. This option is only applicable to MyISAM tables. it is enabled by default.

Mysqldump-uroot-p -- all-databases

-- Dump-slave

This option will cause the primary binlog location and file name to be appended to the exported data file. When set to 1, the command change master will be output to the data file; when set to 2, the instructions will be added before the command. This option will enable -- lock-all-tables unless -- single-transaction is specified. This option will automatically disable the -- lock-tables option. The default value is 0.

Mysqldump-uroot-p -- all-databases -- dump-slave = 1

Mysqldump-uroot-p -- all-databases -- dump-slave = 2

-- Events,-E

Export events.

Mysqldump-uroot-p -- all-databases -- events

-- Extended-insert,-e

Use the INSERT syntax with multiple VALUES columns. In this way, the exported file is smaller and the import speed is accelerated. The value is enabled by default, and the -- skip-extended-insert cancel option is used.

Mysqldump-uroot-p -- all-databases

Mysqldump-uroot-p -- all-databases -- skip-extended-insert (cancel option)

-- Fields-terminated-

Ignore the specified field in the exported file. Used with the -- tab option. it cannot be used with the -- databases or -- all-databases options.

Mysqldump-uroot-p test -- tab = "/home/mysql" -- fields-terminated-by = "#"

-- Fields-enclosed-

Each field in the output file is enclosed by a given character. Used with the -- tab option. it cannot be used with the -- databases or -- all-databases options.

Mysqldump-uroot-p test -- tab = "/home/mysql" -- fields-enclosed-by = "#"

-- Fields-optionally-enclosed-

Fields in the output file are selectively wrapped with the given characters. Used with the -- tab option. it cannot be used with the -- databases or -- all-databases options.

Mysqldump-uroot-p test -- tab = "/home/mysql" -- fields-enclosed-by = "#" -- fields-optionally-enclosed-by = "#"

-- Fields-escaped-

The fields in the output file ignore the given characters. Used with the -- tab option. it cannot be used with the -- databases or -- all-databases options.

Mysqldump-uroot-p mysql user -- tab = "/home/mysql" -- fields-escaped-by = "#"

-- Flush-logs

Refresh the log before you start exporting.

Note: If you export multiple databases at a time (using options -- databases or -- all-databases), the logs will be refreshed one by one. Except for -- lock-all-tables or -- master-data. In this case, the log is refreshed once, and the corresponding table is locked at the same time. Therefore, if you want to export and refresh logs at the same time, use -- lock-all-tables or -- master-data and -- flush-logs.

Mysqldump-uroot-p -- all-databases -- flush-logs

-- Flush-privileges

After exporting the mysql database, issue a flush privileges statement. To restore data correctly, this option should be used to export data from the mysql database and dependent mysql database at any time.

Mysqldump-uroot-p -- all-databases -- flush-privileges

-- Force

Ignore SQL errors during export.

Mysqldump-uroot-p -- all-databases -- force

-- Help

Displays help information and exits.

Mysqldump -- help

-- Hex-blob

Export binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB.

Mysqldump-uroot-p -- all-databases -- hex-blob

-- Host,-h

Host Information to be exported

Mysqldump-uroot-p -- host = localhost -- all-databases

-- Ignore-table

Do not export the specified table. When you specify to ignore multiple tables, you need to repeat multiple times for each table. The database and table names must be specified for each table. Example: -- ignore-table = database. table1 -- ignore-table = database. table2 ......

Mysqldump-uroot-p -- host = localhost -- all-databases -- ignore-table = mysql. user

-- Include-master-host-port

In the 'change master to .. 'statement generated by -- dump-slave, add 'Master _ HOST = , MASTER_PORT = '

Mysqldump-uroot-p -- host = localhost -- all-databases -- include-master-host-port

-- Insert-ignore

Use the insert ignore statement when inserting rows.

Mysqldump-uroot-p -- host = localhost -- all-databases -- insert-ignore

-- Lines-terminated-

Each line of the output file is divided by a given string. It is used with the -- tab option and cannot be used with the -- databases and -- all-databases options.

Mysqldump-uroot-p -- host = localhost test -- tab = "/tmp/mysql" -- lines-terminated-by = "##"

-- Lock-all-tables,-x

Submit a request to lock all tables in all databases to ensure data consistency. This is a global read lock and the -- single-transaction and -- lock-tables options are automatically disabled.

Mysqldump-uroot-p -- host = localhost -- all-databases -- lock-all-tables

-- Lock-tables,-l

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

Note that when exporting multiple databases, -- lock-tables locks the tables for each database respectively. Therefore, this option does not guarantee the logical consistency between the tables in the exported files in the database. The export statuses of different database tables can be completely different.

Mysqldump-uroot-p -- host = localhost -- all-databases -- lock-tables

-- Log-error

Attach warning and error messages to a given file

Mysqldump-uroot-p -- host = localhost -- all-databases -- log-error =/tmp/mysqldump_error_log.err

-- Master-data

This option adds the location and file name of binlog to the output file. If the value is 1, the change master Command is output. if the value is 2, the comments are added before the change master Command. This option will enable the -- lock-all-tables option, unless -- single-transaction is also specified (in this case, the Global read lock gets a very short time at the start of export; for other content, refer to the -- single-transaction option below ). This option automatically disables the -- lock-tables option.

Mysqldump-uroot-p -- host = localhost -- all-databases -- master-data = 1;

Mysqldump-uroot-p -- host = localhost -- all-databases -- master-data = 2;

-- Max_allowed_packet

The maximum length of packets sent and received by the server.

Mysqldump-uroot-p -- host = localhost -- all-databases -- max_allowed_packet = 10240

-- Net_buffer_length

The cache size of TCP/IP and socket connections.

Mysqldump-uroot-p -- host = localhost -- all-databases -- net_buffer_length = 1024

-- No-autocommit

Use the autocommit/commit statement to wrap the table.

Mysqldump-uroot-p -- host = localhost -- all-databases -- no-autocommit

-- No-create-db,-n

Only export data without adding the create database statement.

Mysqldump-uroot-p -- host = localhost -- all-databases -- no-create-db

-- No-create-info,-t

Only export data without adding the create table statement.

Mysqldump-uroot-p -- host = localhost -- all-databases -- no-create-info

-- No-data,-d

Only the database table structure is exported without exporting any data.

Mysqldump-uroot-p -- host = localhost -- all-databases -- no-data

-- No-set-names,-N

Equivalent to -- skip-set-charset

Mysqldump-uroot-p -- host = localhost -- all-databases -- no-set-names

-- Opt

Equivalent to -- add-drop-table, -- add-locks, -- create-options, -- quick, -- extended-insert, -- lock-tables, -- set-charset, -- disable-keys this option is enabled by default and can be disabled with -- skip-opt.

Mysqldump-uroot-p -- host = localhost -- all-databases -- opt

-- Order-by-primary

If a primary key exists or the first unique key exists, sort the records of each table. It is valid when exporting a MyISAM table to an InnoDB table, but it takes a long time to export the table.

Mysqldump-uroot-p -- host = localhost -- all-databases -- order-by-primary

-- Password,-p

Database connection password

-- Pipe (available in windows)

Connect to mysql using a named pipe

Mysqldump-uroot-p -- host = localhost -- all-databases -- pipe

-- Port,-P

Database connection port number

-- Protocol

The connection protocol used, including tcp, socket, pipe, and memory.

Mysqldump-uroot-p -- host = localhost -- all-databases -- protocol = tcp

-- Quick,-q

Directly export the data to the standard output without buffering the query. This option is enabled by default. you can use -- skip-quick to cancel this option.

Mysqldump-uroot-p -- host = localhost -- all-databases

Mysqldump-uroot-p -- host = localhost -- all-databases -- skip-quick

-- Quote-names,-Q

Use (') to cause tables and column names. This option is enabled by default. -- skip-quote-names is used to cancel this option.

Mysqldump-uroot-p -- host = localhost -- all-databases

Mysqldump-uroot-p -- host = localhost -- all-databases -- skip-quote-names

-- Replace

Replace into to replace insert.

Mysqldump-uroot-p -- host = localhost -- all-databases -- replace

-- Result-file,-r

Output directly to the specified file. This option should be used on systems that use Carriage return line breaks (\ r \ n) (for example, DOS, Windows ). This option ensures that only one row is used.

Mysqldump-uroot-p -- host = localhost -- all-databases -- result-file =/tmp/mysqldump_result_file.txt

-- Routines,-R

Export stored procedures and user-defined functions.

Mysqldump-uroot-p -- host = localhost -- all-databases -- routines

-- Set-charset

Add 'set NAMES default_character_set 'to the output file. The default value is "open". you can use the -- skip-set-charset option to disable it.

Mysqldump-uroot-p -- host = localhost -- all-databases

Mysqldump-uroot-p -- host = localhost -- all-databases -- skip-set-charset

-- Single-transaction

This option submits a begin SQL statement before exporting data. BEGIN does not block any applications and ensures database consistency during export. It is only applicable to multi-version storage engines and only InnoDB. This option and the -- lock-tables option are mutually exclusive, because lock tables will implicitly commit any pending transactions. To export a large table, use the -- quick option.

Mysqldump-uroot-p -- host = localhost -- all-databases -- single-transaction

-- Dump-date

Add the export time to the output file. The default value is "open", and the -- skip-dump-date option is used to disable it.

Mysqldump-uroot-p -- host = localhost -- all-databases

Mysqldump-uroot-p -- host = localhost -- all-databases -- skip-dump-date

-- Skip-opt

Disable the-opt option.

Mysqldump-uroot-p -- host = localhost -- all-databases -- skip-opt

-- Socket,-S

Specifies the location of the socket file connecting to mysql. the default path is/tmp/mysql. sock.

Mysqldump-uroot-p -- host = localhost -- all-databases -- socket =/tmp/mysqld. sock

-- Tab,-T

Create a tab-separated text file for each table in the given path. Note: it is only used for mysqldump and mysqld servers to run on the same machine.

Mysqldump-uroot-p -- host = localhost test -- tab = "/home/mysql"

-- Tables

Overwrite the -- databases (-B) parameter and specify the name of the table to be exported.

Mysqldump-uroot-p -- host = localhost -- databases test -- tables test

-- Triggers

Export trigger. This option is enabled by default. use -- skip-triggers to disable it.

Mysqldump-uroot-p -- host = localhost -- all-databases -- triggers

-- Tz-utc

Set TIME_ZONE = '+' at the top of the export to ensure the correctness of TIMESTAMP data exported in different time zones or when the data is moved to other time zones.

Mysqldump-uroot-p -- host = localhost -- all-databases -- tz-utc

-- User,-u

Specify the connection user name.
-- Verbose, -- v

Outputs multiple platform information.
-- Version,-V

Output the mysqldump version information and exit
-- Where,-w

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.
Mysqldump-uroot-p -- host = localhost -- all-databases -- where = "user = 'root '"

-- Xml,-X

Export XML format.
Mysqldump-uroot-p -- host = localhost -- all-databases -- xml

-- Plugin_dir

Client plug-in Directory, used to be compatible with different plug-in versions.
Mysqldump-uroot-p -- host = localhost -- all-databases -- plugin_dir = "/usr/local/lib/plugin"

-- Default_auth

Default client plug-in permission.
Mysqldump-uroot-p -- host = localhost -- all-databases -- default-auth = "/usr/local/lib/plugin/ "