Shulou(Shulou.com)06/01 Report--

Mysqldump is a database backup tool that comes with mysql and belongs to single thread. most mysql database backups in production environments use this tool. Mysqldump can export data as native sql statements, which is equivalent to executing all sql statements in the backup file when importing its data into the database.

Mysqldump provides a wealth of options options to add the required options when exporting or importing data in order to achieve the desired results

Common parameters of mysql

/ / Export all databases. / /-- all-databases,-Amysqldump-uroot-p-- all-databases// exports all tablespaces. / /-- all-tablespaces,-Ymysqldump-uroot-p-- all-databases-- all-tablespaces// does not export any tablespaces. / /-- no-tablespaces,-ymysqldump-uroot-p-- all-databases-- no-tablespaces// exports several databases. All name parameters after the parameters are treated as database names. / /-- databases,-Bmysqldump-uroot-p-- databases [database name 1] [database name 2] / / add drop database statements before each database is created (drop and then re-create if the original database is available). / /-- add-drop-databasemysqldump-uroot-p-- all-databases-- add-drop-databas// add drop data table statements before each data table is created. (default is on, use the-- skip-add-drop-table cancel option). / /-- add-drop-tablemysqldump-uroot-p-- all-databases (add drop statement by default) mysqldump-uroot-p-all-databases-skip-add-drop-table (cancel drop statement) / / sets the default character set, which defaults to utf8. / /-- default-character-setmysqldump-uroot-p-- all-databases-- default-character-set=latin1// exports binary string fields using hexadecimal format. You must use this option if you have binary data. The field types affected are BINARY, VARBINARY, and BLOB. / /-- hex-blobmysqldump-uroot-p-- all-databases-- hex-blob// uses the INSERT syntax with multiple VALUES columns. This makes the export file smaller and speeds up the import. The default is on, using the-- skip-extended-insert cancel option. / /-- extended-insert,-emysqldump-uroot-p-- all-databasesmysqldump-uroot-p-- all-databases-- skip-extended-insert (cancel the option) / / use the full insert statement (including column names). Doing so can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter and cause the insert to fail. / /-- complete-insert,-cmysqldump-uroot-p-- all-databases-- complete-insert// does not buffer the query and is directly exported to standard output. The default is on, and use-- skip-quick to cancel this option. / /-- quick,-qmysqldump-uroot-p-- all-databasesmysqldump-uroot-p-- all-databases-- skip-quick// this option submits a BEGIN SQL statement before exporting data. BEGIN does not block any applications and ensures the consistent state of the database at the time of export. / / it is only applicable to multi-version storage engines, only InnoDB. This option and the-- lock-tables option are mutually exclusive because LOCK TABLES will implicitly commit any pending transaction. / / if you want to export large tables, you should use the-- quick option. / /-- single-transactionmysqldump-uroot-p-- all-databases-- single-transaction// does not export the specified table. Specifies that when multiple tables are ignored, they need to be repeated multiple times, one table at a time. Each table must specify both the database and the table name. / /-- ignore-table for example:-- ignore-table=database.table1-- ignore-table=database.table2. Mysqldump-uroot-p-all-databases-- ignore-table=mysql.user

2. Parameters commonly used in production

Note: multiple commands are listed in each scene below, which can be referenced by the reader according to the production situation.

1. Export single library

Format: mysqldump-uroot-p [database name] mysqldump-uroot-p [database name] > / backup/ [exported file name]. Sql.gzmysqldump-uroot-p [database name]-single-transaction > / backup/ [exported file name] .sql.gzmysqldump-uroot-p [database name]-- default-character-set=utf8-- set-gtid-purged=OFF-- single-transaction > / backup/ [exported file name] .sql.gz

two。 Export multiple libraries

Format: mysqldump-uroot-p-B [database name 1] [database name 2] mysqldump-uroot-p-B [database name 1] [database name 2] > / backup/ [exported file name]. Sql.gzmysqldump-uroot-p-B [database name 1] [database name 2]-single-transaction > / backup/ [exported file name] .sql.gzmysqldump-uroot-p-B [database name 1] ] [database name 2]-- default-character-set=utf8-- set-gtid-purged=OFF-- single-transaction > / backup/ [exported file name] .sql.gz

3. Export a single table

Format: mysqldump-uroot-p [database name] [table name] mysqldump-uroot-p [database name] [table name] > / backup/ [exported file name]. Sql.gzmysqldump-uroot-p [database name] [table name]-- single-transaction > / backup/ [exported file name] .sql.gzmysqldump-uroot-p [database name] [table name]-- default-character-set=utf8-- set-gtid- Purged=OFF-- single-transaction > / backup/ [exported file name] .sql.gz

4. Export multiple tables

Format: mysqldump-uroot-p [database name] [table name 1] [table name 2] mysqldump-uroot-p [database name] [table name 1] [table name 2] > / backup/ [exported file name] .sql.gzmysqldump-uroot-p [database name] [table name 1] [table name 2]-- single-transaction > / backup/ [exported file name] .sql.gzmysqldump-name-p [database name] ] [table name 1] [table name 2]-- default-character-set=utf8-- set-gtid-purged=OFF-- single-transaction > / backup/ [exported file name] .sql.gz

5. Import and export large tables

Mysqldump-uroot-p-- default-character-set=utf8-- quick\-- extended-insert-- single-transaction [database name] | gzip > / backup/ [exported file name] .sql.gz

Gzip is determined according to demand, mainly to compress the exported sql data.

6. After filtering the tables in the library, export

Mysqldump-uroot-p-- ignore-table= [database name. Table name 1]-- ignore-table= [database name. Table name 2] > / backup/ [exported file name] .sql.gz

When you filter the data exported after filtering the tables in the library, you do not need to specify the library name separately, just fill in the corresponding tables under the library that need to be filtered, and it will automatically filter the tables that are filled in the library. export the data from the tables that are not filtered to the specified file

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.