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

A backup tool, mysqlpump, has been added since Mysql 5.7,

The mysqlpump client performs a logical backup

New features of Mysqlpump

Execute the database and its objects in parallel to speed up the dump process

Better control over which databases and database objects to dump and export

Export user accounts as account management statements (create user, grant) instead of inserting them into the mysql system database

Back up and generate compressed backup files directly

Backup progress indicator (estimate)

The dump file is loaded (restored), the table is built first and then the data is inserted, and finally the index is established to reduce the maintenance cost of the index and speed up the restore speed.

Backups can exclude or specify databases

The permissions required by mysqlpump, different options, different permissions, can be seen in the option description.

Function

At least the required permissions

Export tabl

Select corresponds to table permissions

Export View

Show view corresponding view permissions

Export stored procedure

Trigger corresponding stored procedure permissions

-- when the single-transaction option is not used

LOCK TABLES

Export user definition

Permissions of the Select mysql system library

To load a dump file, you must have permission to execute the statements contained in the dump file, such as create, etc.

NOTE

When exporting using powershell on windows, redirecting to a newly created file uses utf-16 encoding, which results in an error because the MySQL connection character set does not support utf-16.

Shell > mysqlpump [options] > dump. Sql error

You can use the-- result-file option to export to a file in ASCII format.

Shell > mysqlpump [options]-- result-file = dump.sql is correct

Mysqldump call syntax

Shell > mysqlpump-- all-databases

Shell > mysqlpump db_name

Shell > mysqlpump db_name tbl_name1 tbl_name2...

Export specified multiple libraries

Shell > mysqlpump-- databases db_name1 db_name2...

By default, mysql p ump does not export user account definitions, even if you export a mysql system library that contains authorization tables. To export authorization tables as logical definitions (create user and grant), use the-- users option and disable all database dumps.

Shell > mysqlpump-- exclude-databases =%-- users

The% here is a wildcard, it matches all libraries,-- exclude-database=% excludes all libraries.

Mysqlpump supports several options, including or excluding databases, tables, stored procedures, and user definitions. Look at mysqlpump object selection.

To load the dump file, execute the statements it contains, as follows:

Shell > mysqlpump [options] > dump. Sql

Shell > mysql

< dump . sql Mysqlpump 选项概要 mysqlpump 支持命令行指定选项，也可以在参数文件的 [mysqlpump] and [client] 的组中指定。看 Section 4.2.6, "Using Option Files" . Table 4.15 mysqlpump Options 参数绝大多数和 mysqldump 一致 , 对于 mysqlpump 参数会用背景色 标记出来。 Format Description Introduced --add-drop-database Add DROP DATABASE statement before each CREATE DATABASE statement 在建库之前，先执行删除库操作 DROP DATABASE IF EXISTS `...`; --add-drop-table Add DROP TABLE statement before each CREATE TABLE statement 在建表之前先执行删表操作。 DROP TABLE IF EXISTS `...`.`...`; --add-drop-user Add DROP USER statement before each CREATE USER statement 在CREATE USER语句之前增加DROP USER， 注意： 这个参数需要和 --users 一起使用，否者不生效。 DROP USER 'backup' @ '192.168.123.%' ; --add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements 备份表时，使用LOCK TABLES和UNLOCK TABLES。 注意： 这个参数不支持并行备份，需要关闭并行备份功能： --default-parallelism =0 LOCK TABLES `...`.`...` WRITE; ... UNLOCK TABLES; --all-databases Dump all databases 备份所有库，-A。 --bind-address Use specified network interface to connect to MySQL Server 指定通过哪个网络接口来连接 Mysql 服务器（一台服务器可能有多个 IP ），防止同一个网卡出去影响业务。 --character-sets-dir Directory where character sets are installed --complete-insert Use complete INSERT statements that include column names dump 出包含所有列的完整 insert 语句。 --compress Compress all information sent between client and server 在客户端和服务器传输的所有的数据包压缩，最后的备份集大小没有任何改变，-C。 若要改变备份集大小： compress=true |gzip 不过时间，会用到 5 倍于 compress= false 会几倍于 --compress-output呢？ --compress-output Output compression algorithm 默认不压缩输出，目前可以使用的压缩算法有LZ4和ZLIB。 shell>

Mysqlpump-- compress-output=LZ4 > dump.lz4

Shell > lz4_decompress dump.lz4 dump.txt

Shell > mysqlpump-- compress-output=ZLIB > dump.zlib

Shell > zlib_decompress dump.zlib dump.txt

-- databases

Interpret all name arguments as database names

Manually specify the libraries to back up, support multiple databases, separated by spaces,-B.

-- debug

Write debugging log

-- debug-check

Print debugging information when program exits

-- debug-info

Print debugging information, memory, and CPU statistics when program exits

-- default-auth

Authentication plugin to use

-- default-character-set

Specify default character set

Specifies the character set for the backup.

-- default-parallelism

Default number of threads for parallel processing

Specify the number of parallel threads. The default is 2. If set to 0, parallel backups are not used. Note: the backup step for each thread is to first create table but not build a secondary index (the primary key will be created at create table), then write the data, and finally build the secondary index.

-- defaults-extra-file

Read named option file in addition to usual option files

-- defaults-file

Read only named option file

-- defaults-group-suffix

Option group suffix value

-- defer-table-indexes

For reloading, defer index creation until after loading table rows

Delay index creation until all the data has been loaded, and then create the index, which is enabled by default. If closed, it will be the same as mysqldump: first create a table and all indexes, and then import data, because of the overhead of maintaining secondary indexes when loading and restoring data, resulting in low efficiency. Turn off the usage parameter:-- skip--defer-table-indexes.

-- events

Dump events from dumped databases

The event of backing up the database is turned on and off by default using the-- skip-events parameter.

-- exclude-databases

Databases to exclude from dump

The backup excludes the databases specified by this parameter, separated by commas. There are also similar-- exclude-events,-- exclude-routines,-exclude-tables,-exclude-triggers,-- exclude-users.

Mysqlpump-- exclude-databases = mysql,sys # backup filtering mysql and sys databases

Mysqlpump-- exclude-tables = rr,tt # backup filters rr and tt tables in all databases

Mysqlpump-B test-- exclude-tables = tmp_ifulltext,tt # backup filter rr and tt tables in the test library

...

Note: if you only back up the account of the database, you need to add the parameter-users, and filter out all databases, such as:

Mysqlpump-- users-- exclude-databases =%-- exclude-users = dba,backup # back up all accounts except dba and backup.

-- exclude-events

Events to exclude from dump

-- exclude-routines

Routines to exclude from dump

-- exclude-tables

Tables to exclude from dump

-- exclude-triggers

Triggers to exclude from dump

-- exclude-users

Users to exclude from dump

-- extended-insert

Use multiple-row INSERT syntax

-- get-server-public-key

Request RSA public key from server

5.7.23

-- help

Display help message and exit

-- hex-blob

Dump binary columns using hexadecimal notation

The binary field is backed up using hexadecimal counting, and the affected field types are BINARY, VARBINARY, BLOB, and BIT.

-- host

Host to connect to (IP address or hostname)

Back up the specified database address,-h.

-- include-databases

Databases to include in dump

Specify backup databases, multiple separated by commas, and similarly-- include-events,-- include-routines,-- include-tables,-- include-triggers,-- include-users, using the same 15 general method.

-- include-events

Events to include in dump

-- include-routines

Routines to include in dump

-- include-tables

Tables to include in dump

-- include-triggers

Triggers to include in dump

-- include-users

Users to include in dump

-- insert-ignore

Write INSERT IGNORE rather than INSERT statements

Backup uses insert ignore statements instead of insert statements.

-- log-error-file

Append warnings and errors to named file

The warnings and erros information that appears in the backup is output to a specified file.

-- login-path

Read login path options from .mylogin.cnf

-- max-allowed-packet

Maximum packet length to send to or receive from server

The size of the largest buffer packet used for client/server direct communication at the time of backup.

-- net-buffer-length

Buffer size for TCP/IP and socket communication

The initial buffer size used for client/server communication when backing up, and when creating a multiline insert statement, the mysqlpump creates lines up to N bytes long.

-- no-create-db

Do not write CREATE DATABASE statements

Backup does not write CREATE DATABASE statements. If you back up multiple libraries, you need to use the parameter-B, and when you use-B, there will be a create database statement, which can mask the create database statement.

-- no-create-info

Do not write CREATE TABLE statements that re-create each dumped table

Backup does not write table statements, that is, do not back up the table structure, only backup data,-t.

-- no-defaults

Read no option files

-- parallel-schemas

Specify schema-processing parallelism

Specify libraries for parallel backup. Multiple libraries are separated by commas. If N is specified, the ground queue of N threads will be used. If N is not specified, the value of N will be confirmed by-- default-parallelism. Multiple-- parallel-schemas can be set.

Mysqlpump-- parallel-schemas=4:vs,aa-- parallel-schemas=3:pt # 4 threads back up vs and aa, 3 threads back up pt. Through show processlist, you can see that there are seven threads.

Mysqlpump-- parallel-schemas=vs,abc-- parallel-schemas=pt # defaults to 2 threads, that is, 2 threads back up vs and abc and 2 threads back up pt

# of course, if hard disk IO is not allowed, you can save a few threads and databases for parallel backup.

-- password

Password to use when connecting to server

-- plugin-dir

Directory where plugins are installed

-- port

TCP/IP port number for connection

-- print-defaults

Print default options

-- protocol

Connection protocol to use

{TCP | SOCKET | PIPE | MEMORY}: specifies the protocol to connect to the server.

-- replace

Write REPLACE statements rather than INSERT statements

Back up the replace into statement.

-- result-file

Direct output to a given file

-- routines

Dump stored routines (procedures and functions) from dumped databases

Backup contains stored procedures and functions, which are enabled by default. You need to have permission to view the mysql.proc table. The generated file contains CREATE PROCEDURE and CREATE FUNCTION statements for recovery, and the-- skip-routines parameter is required for closing.

-- secure-auth

Do not send passwords to server in old (pre-4.1) format

-- server-public-key-path

Path name to file containing RSA public key

5.7.23

-- set-charset

Add SET NAMES default_character_set to output

Write SET NAMES default_character_set to output in the backup file. This parameter is on by default. -- skip-set-charset disables this parameter and will not write set names... in the backup file

-- set-gtid-purged

Whether to add SET @ @ GLOBAL.GTID_PURGED to output

5.7.18

-- single-transaction

Dump tables within single transaction

This parameter is set to Repeatable Read at the transaction isolation level and sends a start transaction statement to the server before dump. This is useful when using innodb because when issuing a start transaction, it ensures a consistent state without blocking any application. For non-transactional tables such as myisam and memory, the state will still change. When using this parameter, make sure that no other connections are using statements such as ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, etc., otherwise incorrect content will occur or fail. -- add-locks and this parameter are mutually exclusive. Before mysql5.7.11,-- when default-parallelism is greater than 1, and this parameter is mutually exclusive, you must use-- default-parallelism=0. After 5.7.11, the mutual exclusion problem of-- single-transaction and-- default-parallelism has been solved.

-- skip-definer

Omit DEFINER and SQL SECURITY clauses from view and stored program CREATE statements

Ignore the DEFINER and SQL SECURITY statements used to create views and stored procedures, and use the default values when restoring, otherwise you will report an error when you see the account without DEFINER definition during restore.

-- skip-dump-rows

Do not dump table rows

Only backup table structure, do not back up data,-d. Note: mysqldump supports-- no-data,mysqlpump does not-- no-data

-- socket

For connections to localhost, the Unix socket file to use

-- ssl

Enable encrypted connection

-- the ssl parameter will be removed and replaced with-- ssl-mode. For ssl-related backups, please see the official documentation.

-- ssl-ca

File that contains list of trusted SSL Certificate Authorities

-- ssl-capath

Directory that contains trusted SSL Certificate Authority certificate files

-- ssl-cert

File that contains X.509 certificate

-- ssl-cipher

List of permitted ciphers for connection encryption

-- ssl-crl

File that contains certificate revocation lists

-- ssl-crlpath

Directory that contains certificate revocation list files

-- ssl-key

File that contains X.509 key

-- ssl-mode

Security state of connection to server

5.7.11

-- ssl-verify-server-cert

Verify host name against server certificate Common Name identity

-- tls-version

Protocols permitted for encrypted connections

5.7.10

-- triggers

Dump triggers for each dumped table

The backup contains triggers, which are turned on by default and turned off using-- skip-triggers.

-- tz-utc

Add SET TIME_ZONE='+00:00' to dump file

-- user

MySQL user name to use when connecting to server.

-u

-- users

Dump user accounts

Backup database users. The form of backup is CREATE USER...,GRANT...,. Only the database account can be backed up by the following command:

Mysqlpump-exclude-databases=%-users # filter out all databases

-- version

Display version information and exit

5.7.9

-- watch-progress

Display progress indicator

Periodically displays the completion of progress, including total tables, rows, and other objects. This parameter is enabled by default and disabled with-- skip-watch-progress.

Unsupported parameter

-- flush-logs-- flush-privileges does not need to do these actions when exporting

-- master-data-- how can dump-slave build a slave database without this?

Instructions for use:

The architecture of mysqlpump is shown in the following figure:

Mysqlpump supports parallel export based on libraries and tables. The architecture of mysqlpump's parallel export function is: queue + thread, allowing multiple queues (--parallel-schemas? ), there are multiple threads under each queue (N? A queue can be bound to one or more databases (separated by commas). The backup of mysqlpump is based on table parallelism, and each table can only be exported by a single thread. There is a limitation here that if a database has a very large table, most of the time may be spent on backing up this table, the effect of parallel backup may not be obvious. Here, you can take advantage of mydumper, which is exported in batch in chunk, that is, mydumper supports multiple threads in a table to batch export in chunk. But there is still a big improvement compared to mysqldump. Here's a rough test of mysqlpump and mysqldump backup efficiency.

# mysqlpump compressed backup vs database backup with three concurrent threads, consumption time: 222s

Mysqlpump-uzjy-p-h292.168.123.70-- single-transaction-- default-character-set=utf8-- compress-output=LZ4-- default-parallelism=3-B vs > / home/zhoujy/vs_db.sql.lz4

# mysqldump backup Compression vs Database single thread backup, consumption time: 900s, the compression ratio of gzip is higher than that of LZ4

Mysqldump-uzjy-p-h292.168.123.70-- default-character-set=utf8-P3306-skip-opt-- add-drop-table-- create-options-- quick-- extended-insert-- single-transaction-B vs | gzip > / home/zhoujy/vs.sql.gz

# mydumper backup vs database backup with three concurrent threads, which takes 300s. The compression ratio of gzip is higher than that of LZ4.

Mydumper-u zjy-p-h 192.168.123.70-P 3306-t 3-c-l 3600-s 10000000-B vs-o / home/zhoujy/vs/

# mydumper backup vs database, five concurrent threads back up, and enable batch export of multiple threads to a table in the form of chunk,-r. Elapsed time: 180s

Mydumper-u zjy-p-h 192.168.123.70-P 3306-t 5-c-r 300000-l 3600-s 10000000-B vs-o / home/zhoujy/vs/

As can be seen from the above, the backup efficiency of mysqlpump is the fastest, mydumper is the second, and mysqldump is the worst. So if IO allows, do not use single-threaded backup if you can use multithreading.

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.