Mysqldump command import and export database method and instance aggregation _ MySQL

This article mainly introduces the mysqldump command for importing and exporting database methods and instance summary. if you need it, refer to the usage of mysqldump command.

1. export all databases

System command line

Mysqldump-uusername-ppassword -- all-databases> all. SQL

2. import all databases

Mysql command line

Mysql> source all. SQL;

3. export some databases

System command line

Mysqldump-uusername-ppassword -- databases db1 db2> db1db2. SQL

4. import some databases

Mysql command line

Mysql> source db1db2. SQL;

5. import a database

System command line

Mysql-uusername-ppassword db1 <db1. SQL;

Or mysql command line

Mysql> source db1. SQL;

6. export some data tables

System command line

Mysqldump-uusername-ppassword db1 table1 table2> tb1tb2. SQL

7. import some data tables

System command line

Mysql-uusername-ppassword db1 <tb1tb2. SQL

Or mysql command line

Mysql>
User db1;
Source tb1tb2. SQL;

8. set the mysqldump character set

Mysqldump-uusername-ppassword -- default-character-set = gb2312 db1 table1> tb1. SQL

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.

There are three methods to call mysqldump:

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.

-- 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.

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. 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.
-- 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.
The -- master-data option enables -- 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' to' \ r \ n' 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.
Mysqldump is most commonly used to back up an entire database:
Shell> mysqldump -- opt db_name> backup-file. SQL

The following code reads the dump file back to the server:

Shell> mysql db_name <backup-file. SQL

Or:

Shell> mysql-e "source/path-to -- Backups/backup-file. SQL" db_name

Mysqldump can also be used to copy data from one mysql server to another to load the database:
Shell> mysqldump -- opt db_name | mysql -- host = remote_host-c db_name

You can use one command to dump several databases:
Shell> mysqldump --- database db_name1 [db_name2...]> my_databases. SQL

To dump all databases, use the -- all -- database option:

Shell> mysqldump -- all-databases> all_databases. SQL

If the table is stored in the innodb storage engine, mysqldump provides an online backup method (see the following command ). This backup only requires global read locks on all tables when the dump is started (use flush tables with read lock ). After obtaining the lock, read the corresponding content of the binary log and release the lock. Therefore, if a long update statement is executed only when flush... is issued, the mysql server stops until the long statement ends, and then dumps the statement to release the lock. Therefore, if the mysql server only receives short ("short execution time") update statements, even if there are a large number of statements, it will not notice the lock period.

Shell> mysqldump -- all-databases -- single-transaction> all_databases. SQL

For point-to-point recovery (also known as "rollback", when you need to recover the old backup and replay the changes after the backup ), it is useful to loop binary logs or at least know the binary log content corresponding to the dump:

Shell> mysqldump -- all-databases -- master-data = 2> all_databases. SQL
Or

Shell> mysqldump -- all-databases -- flush-logs -- master-data = 2> all_databases. SQL

If the table is stored in the innodb storage engine, -- master-data and -- single-transaction provide a convenient way to perform online backup suitable for point-to-point recovery.