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

This article introduces the relevant knowledge of "mysqlpump backup tool description in MySQL5.7". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

After MySQL5.7, there is a backup tool: mysqlpump. It is a derivative of mysqldump, mysqldump will not say much, now let's see what improvements mysqlpump has made, you can check the official documentation, here are some instructions on how to use it.

Mysqlpump, like mysqldump, is a logical backup, which is saved as text in the form of SQL. The advantage of logical backup over physical backup is that you don't care about the size of undo log, you can back up your data directly. Its main features are:

Parallel backup of databases and objects in the database to speed up the backup process.

Better control over the backup of databases and database objects (tables, stored procedures, user accounts).

Back up the user account as an account management statement (CREATE USER,GRANT) instead of inserting it directly into MySQL's system database.

Back up and generate compressed backup files directly.

Backup progress indication (estimate).

Reload (restore) the backup file, create the table first, then insert the data and finally establish the index, which reduces the index maintenance overhead and speeds up the restore speed.

Backups can exclude or specify databases.

Parameters: most of the parameters are the same as mysqldump. Review them by the way. The mysqlpump parameter is marked with a background color.

1:-- add-drop-database: delete the library before creating the library.

DROP DATABASE IF EXISTS `...`

2:-- add-drop-table: delete the table before creating the table.

DROP TABLE IF EXISTS `.

3:--add-drop-user: add DROP USER before the CREATE USER statement. Note: this parameter needs to be used with-- users, otherwise it will not take effect.

DROP USER 'backup'@'192.168.123.%'

4:-- add-locks: use LOCK TABLES and UNLOCK TABLES when backing up tables. Note: this parameter does not support parallel backup. You need to disable the parallel backup function:-- default-parallelism=0

LOCK TABLES `. `WRITE;...UNLOCK TABLES

5:-- all-databases: back up all libraries,-A.

6:-- bind-address: specify which network interface to connect to the Mysql server (a server may have multiple IP) to prevent the same network card from affecting the business.

7:-- complete-insert: dump produces a complete insert statement containing all columns.

8:-- compress: compress all the data transmitted by the client and server,-C.

9:--compress-output: output is not compressed by default, and currently available compression algorithms are LZ4 and ZLIB.

Shell > mysqlpump-- compress-output=LZ4 > dump.lz4shell > lz4_decompress dump.lz4 dump.txtshell > mysqlpump-- compress-output=ZLIB > dump.zlibshell > zlib_decompress dump.zlib dump.txt

10:-- databases: manually specify the libraries to be backed up. Multiple databases are supported, separated by spaces,-B.

11:-- default-character-set: specifies the character set of the backup.

12:--default-parallelism: 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.

13:--defer-table-indexes: delaying index creation until all the data has been loaded, and then creating the index. Default is enabled. 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.

14:-- events: the event of backing up the database, which is enabled by default and disabled using the-- skip-events parameter.

15:--exclude-databases: 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 filter mysql and sys database mysqlpump-- exclude-tables=rr,tt # backup filter rr, tt table mysqlpump-B test-- exclude-tables=tmp_ifulltext,tt # backup filter rr, t table in 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=sys,mysql,db1,db2-exclude-users=dba,backup # backs up all accounts except dba and backup.

16:--include-databases: specify backup databases, multiple of which are separated by commas. Similarly, there are-- include-events,-- include-routines,-- include-tables,-- include-triggers,-- include-users. The general method uses the same 15.

17:-- insert-ignore: backup uses insert ignore statements instead of insert statements.

18:-- log-error-file: warnings and erros information that appears in the backup is output to a specified file.

19:-- max-allowed-packet: the size of the largest buffer packet used for client/server direct communication when backing up.

20:-- net-buffer-length: the initial buffer size used for client/server communication when backing up. When creating a multiline insert statement, the mysqlpump creates lines up to N bytes long.

21:-- no-create-db: 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.

22:-- no-create-info: backup does not write table statements, that is, does not back up the table structure, only data,-t.

23:-- hex-blob: hexadecimal counting is used when backing up binary fields. The affected field types are BINARY, VARBINARY, BLOB, and BIT.

24:-- host: back up the specified database address,-h.

25 db_list: 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. You can set multiple-- parallel-schemas.

Mysqlpump-- parallel-schemas=4:vs,aa-- parallel-schemas=3:pt # 4 threads backup vs and aa,3 threads backup pt. Through show processlist, you can see that there are seven threads. Mysqlpump-- parallel-schemas=vs,abc-- parallel-schemas=pt # default 2 threads, that is, 2 threads backup vs and abc,2 thread backup pt####, of course, if the hard disk IO does not allow, you can open a few fewer threads and databases for parallel backup

26:-- password: backup the required password.

27:-- port: the port where the database is backed up.

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

29:-- replace: back up the replace into statement.

30:-- routines: backup contains stored procedures and functions, which is 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.

31:-- triggers: backup contains triggers, which are enabled by default and turned off using-- skip-triggers.

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

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

33:--skip-definer: ignore the DEFINER and SQL SECURITY statements used to create views and stored procedures, and use the default value when restoring, otherwise you will report an error when you see the account without DEFINER definition during restore.

34:--skip-dump-rows: only backup table structure, do not back up data,-d. Note: mysqldump supports-- no-data,mysqlpump does not-- no-data

35:-- socket: use a socket file for connecting to localhost,Unix, which on Windows is the name of the named pipe,-S.

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

37:-- tz-utc: SET TIME_ZONE='+00:00' is added to the first few lines of the backup file when backing up. Note: if the restored server is not in the same time zone and the columns in the restore table have timestamp fields, the restored results will be inconsistent. Turn this parameter on by default and turn it off with-- skip-tz-utc.

38:-- user: user name at the time of backup,-u.

39:--users: back up database users. The form of backup is CREATE USER...,GRANT...,. Only database accounts can be backed up by using the following command:

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

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

Instructions for use:

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.

This is the end of the description of the mysqlpump backup tool in MySQL5.7. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.