mysqldump Command Import Export database method and instance Rollup _mysql

Use of the mysqldump command

1. Export all libraries

System command Line

Mysqldump-uusername-ppassword--all-databases > All.sql

2. Import all libraries

MySQL command line

Mysql>source All.sql;

3. Export some libraries

System command Line

Mysqldump-uusername-ppassword--databases db1 DB2 > Db1db2.sql

4. Import some libraries

MySQL command line

Mysql>source Db1db2.sql;

5. Import a library

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. mysqldump Character Set

Mysqldump-uusername-ppassword--default-character-set=gb2312 DB1 table1 > Tb1.sql

Mysqldump clients can be used to dump databases or collect databases for backup or to transfer data to another SQL Server (not necessarily a MySQL server). Dumps contain SQL statements that create tables and/or load tables.
If you are backing up on a server, and the tables are all MyISAM tables, consider using mysqlhotcopy because you can make backup and recovery faster.

There are 3 different ways to invoke 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---database or--all--database option is used, the entire database is dumped.
To get your version of the mysqldump support option, perform mysqldump---help.

If you run mysqldump without--quick or--opt options, mysqldump loads the entire result set into memory before dumping the results. Problems may occur if you dump a large database. This option is enabled by default, but can be disabled with--skip-opt.
If you use the latest version of the mysqldump program to generate a dump reload to a very old version of the MySQL server, you should not use the--opt or the-e option.
Mysqldump supports the following options:

---help,-?

Displays a help message and exits.

--add-drop--database

Add a drop DATABASE statement before each CREATE DATABASE statement.

--add-drop-tables

Add a drop TABLE statement before each CREATE TABLE statement.

--add-locking

Use lock tables and unlock tables to refer to each table dump. You can insert faster when overloading a dump file.

--all--database,-a

Dumps all tables in all databases. As with the---database option, all databases are named on the command line.

--allow-keywords

Allows you to create a keyword column name. Precede each column name with a table name prefix.

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

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

--compact

Produces a small amount of output. This option disables annotations and enables--skip-add-drop-tables 、--no-set-names 、--skip-disable-keys and--skip-add-locking options.

--compatible=name

Produces output that is more compatible with other database systems or older MySQL servers. Values can be ANSI, mysql323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, no_tables_options, or no_field_options. To use a few values, separate them with commas. 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 enables only those SQL schema values that are currently capable of making the dump output more compatible. For example,--compatible=oracle does not map Oracle types or data types that use the Oracle annotation syntax.

--complete-insert,-c

Use the complete INSERT statement that includes the column name.

--compress,-c

Compresses all information sent between the client and the server, if both support compression.

--create-option

Include all MySQL table options in the CREATE TABLE statement.

---database,-b

Dump several databases. Typically, mysqldump the 1th name parameter in the command line as the database name, followed by the name as 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 debug Log. 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
Inserts a row using the Insert delayed statement.
--delete-master-logs
On the primary replication server, delete the binary log after the dump operation is completed. This option automatically enables--master-data.
--disable-keys,-k
For each table, use the/*!40000 ALTER TABLE tbl_name disable keys */and the/*!40000 ALTER TABLE tbl_name the Enable keys * * To refer to the INSERT statement. This allows the dump file to be loaded faster because the index is created after all rows have been inserted. This option is only suitable for MyISAM tables.
--extended-insert,-e
Use multiple-line insert syntax that includes a list of several values. This makes the dump file smaller and accelerates the insertion when overloading the file.
--fields-terminated-by=...,--fields-enclosed-by=...,--fields-optionally-enclosed-by=...,--fields-escaped-by= ...,--line-terminated-by= ...
These options are used in conjunction with the-t option and have the same meaning as the corresponding clause in the load data infile.
--first-slave,-x
Not in favor of use, now renamed to--lock-all-tables.
--flush-logs,-f
Refreshes the MySQL server log file before starting the dump. This option requires Reload permissions. Note that if you use this option with the--all--database (or-a) option, the log is refreshed based on the database for each dump. The exception is when you use--lock-all-tables or--master-data: In this case, the log is refreshed only once and is refreshed after all the tables have been locked. If you want to dump and refresh the log at the same time, you should use--flush-logs together with--lock-all-tables or--master-data.
--force,-f
In the table dump process, it continues even if a SQL error occurs.
--host=host_name,-h host_name
Dumps data from a MySQL server on a given host. The default host is localhost.
--hex-blob
Dumps a binary string column with a hexadecimal symbol (for example, ' abc ' becomes 0x616263). The columns that are affected are binary, varbinary, and blobs.
--lock-all-tables,-x
All tables in all databases are locked. The global read lock is implemented in the whole dump process. This option automatically turns off--single-transaction and--lock-tables.
--lock-tables,-l
Lock all tables before starting the dump. Lock the table with read local to allow parallel insertion of the MyISAM table. For transaction tables such as InnoDB and bdb,--single-transaction is a better option because it does not require locking of the table at all.
Note that when you dump multiple databases,--lock-tables locks the table for each database. Therefore, this option does not guarantee logical consistency of the tables in the dump file between the 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 permissions, and binary logging must be enabled. If the option value is equal to 1, the location and filename are written to the dump output in the form of the Change master statement, if you use the SQL dump master server to set up from the server, starting from the server in the correct location from the primary server binary log. If the option value is equal to the 2,change master statement, it is written as an SQL comment. If value is omitted, this is the default action.
The--master-data option enables--lock-all-tables unless you also specify--single-transaction (in which case, a global read lock is acquired only briefly at the start of the dump.) See--single-transaction again. In either case, the log-related action occurs at the time of the dump. This option automatically closes--lock-tables.
--no-create-db,-n
This option disables the CREATE database/*!32312 if not exists*/db_name statement, which is included in the output if---database or--all--database option is given.
--no-create-info,-t
CREATE TABLE statements that re-create each dump table are not written.
--no-data,-d
Any row information that does not write the table. This is useful if you only want to dump the structure of the table.
--opt
This option is shorthand, equivalent to specifying--add-drop-tables--add-locking--create-option--disable-keys--extended-insert--lock-tables--quick- Set-charset. It gives a quick dump operation and produces a dump file that can be quickly loaded into a MySQL server. This option is turned on by default, but can be disabled with--skip-opt. To disable only those options that are sure to be enabled with-opt, use the--skip form; For example,--skip-add-drop-tables or--skip-quick.
--password[=password],-p[password]
The password to use when connecting to the server. If you use the Short option form (-P), you cannot have a space between the option and the password. If you omit the password value after the--password or-P option on the command line, you are prompted to enter one.
--port=port_num,-p Port_num
The TCP/IP port number to use for the connection.
--PROTOCOL={TCP | socket | pipe | memory}
The connection agreement used.
--quick,-q
This option is used to dump large tables. It forces mysqldump to retrieve rows from the table one line at a time instead of retrieving all rows and caching it in memory before it is exported.
--quote-names,-q
References the database, table, and column names with the characters "." If the server SQL mode includes the Ansi_quotes option, use the ' ' character to refer to the name. This option is enabled by default. can be disabled with--skip-quote-names, but this option should be followed by other options, such as the--compatible that can enable--quote-names.
--result-file=file,-r file
Shifts output to a given file. This option is applied to Windows because it disables the conversion of the new row ' \ n ' character to ' \ r \ n ', return/new row sequence.
--routines,-r
Store the stored programs (functions and programs) in the staging database. The output produced using the---routines contains the CREATE procedure and create function statements to recreate the subroutine. However, these statements do not include attributes, such as subroutine definitions or create and modify timestamps. This means that when overloading a subroutine, the definition should be set to overload the user when it is created, and the timestamp equals the overload time.
If you need to create a subroutine that uses the original definition and timestamp properties, do not use--routines. Instead, use a MySQL account that has the appropriate permissions on the MySQL database to dump and overload the contents of the Mysql.proc table directly.
This option is added to the MySQL 5.1.2. Before this, the stored program does not dump.
--set-charset
Add the 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 emits a BEGIN SQL statement before the server dumps data. It applies only to transaction tables, such as InnoDB and BDB, because it then dumps a consistent database state when the begin is emitted without blocking any applications.
When using this option, remember that only innodb tables can be dumped in a consistent state. For example, any dump of myisam or heap tables with this option can still change the state.
The--single-transaction option and the--lock-tables option are mutually exclusive, because lock tables will cause any pending transactions to be implicitly committed.
To dump a large table, you should use this option in conjunction with--quick.
--socket=path,-s Path
The socket file that is used when connecting localhost (the default host).
--skip--comments
See the description of the---comments option.
--tab=path,-t Path
A data file that produces a tab split. For each dump table, Mysqldump creates a Tbl_name.sql file that contains a CREATE TABLE statement that creates the tables, and a tbl_name.txt file that contains its data. The option value is the directory where the file is written.
By default, the format of the. txt data file is to use the tab character between the column values and the new rows that follow each line. You can use the--fields-xxx and-row--xxx options to specify the format clearly.
Note: This option is only available when mysqldump and MYSQLD servers are running on the same machine. You must have file permissions, and the server must have permission to write files in the directory you specify.
--tables
Overwrite---database or-b option. All parameters after the option are treated as table names.
--triggers
Dumps triggers for each dump table. This option is enabled by default, and it is disabled with--skip-triggers.
--tz-utc
Add set time_zone= ' +00:00 ' to the dump file so that timestamp columns can be dumped and overloaded between servers with different time zones. (without this option, timestamp is listed in the dump and overload between the source and destination servers with the local time zone). --TZ-UTC can also protect the changes caused by daylight saving time. --TZ-UTC is enabled by default. To disable it, use--SKIP-TZ-UTC. This option is added to the MySQL 5.1.2.
--user=user_name,-u user_name
The MySQL user name to use when connecting to the server.
--verbose,-v
Verbose mode. Print out the details of the program action.
--version,-v
Displays version information and exits.
--where= ' Where-condition ', W ' where-condition '
Dumps only the records selected by the given where condition. Note that if the condition contains a special space or character for the command interpreter, you must refer to the condition.
For example:
"--where=user= ' JIMF '"
"-wuserid>1"
"-wuserid<1"
--xml,-x
Writes the dump output as XML.
You can also use the--var_name=value option to set the following variable:
Max_allowed_packet
Maximum size of the buffer for communication between client/server. The maximum is 1GB.
Net_buffer_length
Initial size of the buffer for communication between client/server. When you create a multiline INSERT statement (as with an option--extended-insert or--opt), Mysqldump creates a line that is up to net_buffer_length in length. If you add this variable, you should also ensure that the net_buffer_length variable is at least as large in the MySQL server.
You can also use the--set-variable=var_name=value or the-O var_name=value syntax to set variables. However, it is not now in favour of using this syntax.
Mysqldump is most commonly used to back up an entire database:
shell> mysqldump--opt db_name > Backup-file.sql

You can read the dump file back to the server like this:

shell> MySQL db_name < backup-file.sql

Or as:

shell> mysql-e "Source/path-to--backup/backup-file.sql" db_name

Mysqldump can also be used to mount a database when replicating data from a MySQL server to another server:
shell> mysqldump--opt db_name | MySQL--host=remote_host-c db_name

You can dump several databases with one command:
shell> mysqldump---database db_name1 [db_name2 ...] > My_databases.sql

If you want to dump all databases, use the--all--database option:

shell> mysqldump--all-databases > All_databases.sql

If the table is saved in the InnoDB storage engine, Mysqldump provides a way to back up online (see the following command). This backup requires a global read lock on all tables only when the dump is started (using flush tables with read lock). After acquiring the lock, read the contents of the binary log and release the lock. So if and only when the flush is issued ... Is executing a long UPDATE statement, the MySQL server stops until the long statement is finished and then dumps the lock. Therefore, if the MySQL server receives only 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 roll forward, when you need to restore old backups and replay changes since the backup), it is useful to loop the binary log or at least know the binary log content of 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 tables are kept in the InnoDB storage engine, both--master-data and--single-transaction provide a convenient way to make online backups that are appropriate for point-to-point recovery.