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

Mydumper

Brief introduction:

Jointly maintained and developed by Max Bubenick of percona and Domas Mituzas of facebook, it is also a tool with a history of 10 years. Compared with the mysqldump tool, which is also a logical backup, it has the characteristic of multi-thread parallel backup. Compared with the official mysqlpump of the same type, its advantage is that a table can be backed up on several threads.

Installation:

Many blogs are recommended to install by compiling. Click here. Because mydumper relies on third-party libraries such as glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel, which have not been installed to each default location by default, there will be a problem of interlocking specified compilation locations for compilation and installation. When I installed on the Cent7 system, I encountered a lot of problems when compiling failed or using error reports after compilation. After checking the environment, I found that my colleagues in the system operation and maintenance installed MySQL 5.6and pcre libraries by compiling and installing them. Later, after I tried to install the library using yum, I still couldn't troubleshoot the problem. Here, I recommend using the rpm package compiled by the developer for installation. With yum, you can automatically complete the dependencies and avoid many pitfalls. Address: https://github.com/maxbube/mydumper/releases, please choose the download version according to your system type. `yum-y install https://github.com/maxbube/mydumper/releases/download/v0.9.3/mydumper-0.9.3-41.el7.x86_64.rpm`

Use:

$mydumper-- helpUsage: mydumper [OPTION...] Multi-threaded MySQL dumpingHelp Options: -?,-- help Show help optionsApplication Options:-B,-- database Database to dump-T,-- tables-list Comma delimited table list to dump (does not exclude regex option)-o,-- outputdir Directory to output files to-s,-- statement-size Attempted size of INSERT statement in bytes, default 1000000-r -- rows Try to split tables into chunks of this many rows. This option turns off-- chunk-filesize-F,-- chunk-filesize Split tables into chunks of this output file size. This value is in MB-c,-- compress Compress output files-e,-- build-empty-files Build dump files even if no data available from table-x,-- regex Regular expression for 'db.table' matching-I,-- ignore-engines Comma delimited list of storage engines to ignore- m,-- no-schemas Do not dump table schemas with the data-d -- no-data Do not dump table data-G,-- triggers Dump triggers-E,-- events Dump events-R,-- routines Dump stored procedures and functions-W,-- no-views Do not dump VIEWs-k,-- no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups-no-backup-locks Do not use Percona backup locks-less-locking Minimize locking time on InnoDB tables. -l,-- long-query-guard Set long query timer in seconds, default 60-K,-- kill-long-queries Kill long running queries (instead of aborting)-D,-- daemon Enable daemon mode-I,-- snapshot-interval Interval between each dump snapshot (in minutes), requires-- daemon, default 60-L,-- logfile Log file name to use By default stdout is used-tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use-skip-tz-utc to disable. -- skip-tz-utc-- use-savepoints Use savepoints to reduce metadata locking issues, needs SUPER privilege-- success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist-- lock-all-tables Use LOCK TABLE for all, instead of FTWRL-U -- updated-since Use Update_time to dump only tables updated in the last U days-- trx-consistency-only Transactional consistency only-- complete-insert Use complete INSERT statements that include column names-h,-- host The host to connect to-u,-- user Username with the necessary privileges-p,-- password User password-P -- port TCP/IP port to connect to-S,-- socket UNIX domain socket file to use for connection-t,-- threads Number of threads to use, default 4-C,-- compress-protocol Use compression on the MySQL connection-V,-- version Show the program version and exit-v,-- verbose Verbosity of output, 0 = silent, 1 = errors 2 = warnings, 3 = info, default 2-- defaults-file Use a specific defaults file

Here are my most commonly used backup methods:

Mydumper-h 192.168.1.109-P 3109-u root-p 123456-G-E-R-t 4-c-o / data/bak/mysql/3109

Description:-h specify address

-P specify backup port-u specify backup user-p password-G backup trigger-E backup scheduled event-R backup stored procedure and function-number of concurrent threads during backup (excluding backup main thread)-c compress backup files (gz mode)-o specify backup storage location

Principle and process:

Similar to the mysqldump tool, first FTWRL (flush table with read lock), query your own transaction execution location (show master status), then open a consistent snapshot transaction, and then start several child threads to open the consistency repeatable snapshot transaction, and use child threads to open the transaction and read data and views, functions, stored procedures, scheduled events, etc. Because there may be non-transactional tables in the instance, for consistency, the FTWRL will not be released until all the child threads have backed up the non-transactional tables. Here we focus on the `- trx-consistency- only` parameter. Many blogs are too rough or even do not translate. In the previous step, we mentioned that in order to obtain a consistent backup at the entire instance level, mydumper will add FTWRL at the beginning of the backup and will not release FTWRL until each thread has backed up the non-transactional tables. This is considering that some of the tables in the MySQL library in versions 5.7 and earlier are MyISAM engines and do not have transaction features, or there are non-transaction tables in the business. However, in the version after 8.0, the mysql library is all changed to the InnoDB engine, or the user chooses not to back up the mysql library, or there is no non-transaction table in the instance. In this case, mydumper will release FTWRL after creating all the child threads and opening the consistent repeatable snapshot transaction respectively. Because at this time, the backup can be guaranteed to be consistent according to the transaction characteristics. If you are sure that the non-transactional tables are not backed up, or the mysql library is not backed up, or the consistency of the non-transactional tables in the instance does not matter, if you care very much about the impact of the read lock added during the backup on the business, you can consider adding the `- trx-consistency- only` parameter to force the lock to be released after the complete child thread is created, so as to reduce the write congestion caused by the backup to the business.

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.