Home Database Mysql Tutorial Detailed introduction to the principle of mydumper

Detailed introduction to the principle of mydumper

Jun 23, 2017 pm 01:20 PM
introduce principle

Mydumper installation: http://www.cnblogs.com/lizhi221/p/7010174.html
mydumper introduction
MySQL’s own mysqldump tool supports single threading Work, export multiple tables one by one, without a parallel machine, which makes it impossible to quickly back up data.
As a practical tool, mydumper can well support multi-threaded work. It can read data from tables and write data to different files simultaneously with multiple threads in parallel. , which makes it faster than traditional mysqldump in terms of processing speed. One of its characteristics is that the list needs to be locked during processing, so if we need to perform backup work during working hours, it will cause DML blocking. But generally, current MySQL has a master and slave, and most backups are performed on the slave, so the lock issue does not need to be considered. In this way, mydumper can better complete the backup task.
##mydumper features
①Multi-threading Backup
② Because it is a multi-threaded logical backup, multiple backup files will be generated after the backup
③ Apply FTWRL( to the MyISAM table during backup FLUSH TABLES WITH READ LOCK), will block DML statements
④Ensure the consistency of backup data
⑤Support file compression
⑥Support exporting binlog
⑦Support multi-thread recovery
⑧Support working in daemon mode, timing Snapshots and continuous binary logs
⑨Support cutting backup files into chunks
Detailed explanation of mydumper parameters
$ mydumper --helpUsage:
  mydumper [OPTION...] multi-threaded MySQL dumping

Help Options:  -?, --help                  Show help optionsApplication Options:  -B, --database              要备份的数据库,不指定则备份所有库
  -T, --tables-list           需要备份的表,名字用逗号隔开
  -o, --outputdir             备份文件输出的目录
  -s, --statement-size        生成的insert语句的字节数,默认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     如果表数据是空,还是产生一个空文件(默认无数据则只有表结构文件)
  -x, --regex                 Regular expression for 'db.table' matching 使用正则表达式匹配'db.table'
  -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备份存储过程和函数
  -k, --no-locks              不使用临时共享只读锁,使用这个选项会造成数据不一致
  --less-locking              Minimize locking time on InnoDB tables.减少对InnoDB表的锁施加时间
  -l, --long-query-guard      设定阻塞备份的长查询超时时间,单位是秒,默认是60秒(超时后默认mydumper将会退出)
  -K, --kill-long-queries     Kill long running queries (instead of aborting)杀掉长查询 (不退出)
  -D, --daemon                Enable daemon mode启用守护进程模式,守护进程模式以某个间隔不间断对数据库进行备
  -I, --snapshot-interval     dump快照间隔时间,默认60s,需要在daemon模式下
  -L, --logfile               使用的日志文件名(mydumper所产生的日志), 默认使用标准输出
  --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            使用savepoints来减少采集metadata所造成的锁时间,需要 SUPER 权限
  --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
  -h, --host                  连接的主机名
  -u, --user                  用来备份的用户名
  -p, --password              用户密码
  -P, --port                  连接端口
  -S, --socket                使用socket通信时的socket文件
  -t, --threads               开启的备份线程数,默认是4
  -C, --compress-protocol     压缩与mysql通信的数据
  -V, --version               显示版本号
  -v, --verbose               输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2
Copy after login
Mydumper main process summary
1. The main thread FLUSH TABLES WITH READ LOCK, applies a global read-only lock to prevent DML statements from being written and ensure data Consistency
2. Read the binary log file name and the location of the log writing at the current point in time and record it in the metadata file for immediate point recovery
3. N (the number of threads can be specified, the default is 4) dump thread START TRANSACTION WITH CONSISTENT SNAPSHOT; Turn on read consistent transactions
4. dump non -InnoDB tables, first export the non-transaction engine tables
5. Main thread UNLOCK TABLES After the non-transaction engine backup is completed, release the global read-only lock
6. Dump InnoDB tables, export InnoDB tables based on transactions
7. Transaction end
mydumper less locking mode:
Mydumper uses --less-locking to reduce the lock waiting time. At this time, the execution mechanism of mydumper is roughly
1. Main thread FLUSH TABLES WITH READ LOCK (global lock)
2. Dump thread START TRANSACTION WITH CONSISTENT SNAPSHOT;
3. LL Dump thread LOCK TABLES non-InnoDB (thread internal lock)
4. Main thread UNLOCK TABLES
5. LL Dump thread dump non-InnoDB tables
## 6. LL DUmp thread UNLOCK non-InnoDB
7 , Dump thread dump InnoDB tables
mydumper backup flow chart


The above is the detailed content of Detailed introduction to the principle of mydumper. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Analysis of the function and principle of nohup Analysis of the function and principle of nohup Mar 25, 2024 pm 03:24 PM

Analysis of the role and principle of nohup In Unix and Unix-like operating systems, nohup is a commonly used command that is used to run commands in the background. Even if the user exits the current session or closes the terminal window, the command can still continue to be executed. In this article, we will analyze the function and principle of the nohup command in detail. 1. The role of nohup: Running commands in the background: Through the nohup command, we can let long-running commands continue to execute in the background without being affected by the user exiting the terminal session. This needs to be run

In-depth understanding of the batch Insert implementation principle in MyBatis In-depth understanding of the batch Insert implementation principle in MyBatis Feb 21, 2024 pm 04:42 PM

MyBatis is a popular Java persistence layer framework that is widely used in various Java projects. Among them, batch insertion is a common operation that can effectively improve the performance of database operations. This article will deeply explore the implementation principle of batch Insert in MyBatis, and analyze it in detail with specific code examples. Batch Insert in MyBatis In MyBatis, batch Insert operations are usually implemented using dynamic SQL. By constructing a line S containing multiple inserted values

In-depth discussion of the principles and practices of the Struts framework In-depth discussion of the principles and practices of the Struts framework Feb 18, 2024 pm 06:10 PM

Principle analysis and practical exploration of the Struts framework. As a commonly used MVC framework in JavaWeb development, the Struts framework has good design patterns and scalability and is widely used in enterprise-level application development. This article will analyze the principles of the Struts framework and explore it with actual code examples to help readers better understand and apply the framework. 1. Analysis of the principles of the Struts framework 1. MVC architecture The Struts framework is based on MVC (Model-View-Con

An in-depth discussion of the functions and principles of Linux RPM tools An in-depth discussion of the functions and principles of Linux RPM tools Feb 23, 2024 pm 03:00 PM

The RPM (RedHatPackageManager) tool in Linux systems is a powerful tool for installing, upgrading, uninstalling and managing system software packages. It is a commonly used software package management tool in RedHatLinux systems and is also used by many other Linux distributions. The role of the RPM tool is very important. It allows system administrators and users to easily manage software packages on the system. Through RPM, users can easily install new software packages and upgrade existing software

PyCharm Beginner's Guide: Comprehensive Analysis of Replacement Functions PyCharm Beginner's Guide: Comprehensive Analysis of Replacement Functions Feb 25, 2024 am 11:15 AM

PyCharm is a powerful Python integrated development environment with rich functions and tools that can greatly improve development efficiency. Among them, the replacement function is one of the functions frequently used in the development process, which can help developers quickly modify the code and improve the code quality. This article will introduce PyCharm's replacement function in detail, combined with specific code examples, to help novices better master and use this function. Introduction to the replacement function PyCharm's replacement function can help developers quickly replace specified text in the code

What is Dogecoin What is Dogecoin Apr 01, 2024 pm 04:46 PM

Dogecoin is a cryptocurrency created based on Internet memes, with no fixed supply cap, fast transaction times, low transaction fees, and a large meme community. Uses include small transactions, tips, and charitable donations. However, its unlimited supply, market volatility, and status as a joke coin also bring risks and concerns. What is Dogecoin? Dogecoin is a cryptocurrency created based on internet memes and jokes. Origin and History: Dogecoin was created in December 2013 by two software engineers, Billy Markus and Jackson Palmer. Inspired by the then-popular "Doge" meme, a comical photo featuring a Shiba Inu with broken English. Features and Benefits: Unlimited Supply: Unlike other cryptocurrencies such as Bitcoin

Detailed explanation of the principle of MyBatis paging plug-in Detailed explanation of the principle of MyBatis paging plug-in Feb 22, 2024 pm 03:42 PM

MyBatis is an excellent persistence layer framework. It supports database operations based on XML and annotations. It is simple and easy to use. It also provides a rich plug-in mechanism. Among them, the paging plug-in is one of the more frequently used plug-ins. This article will delve into the principles of the MyBatis paging plug-in and illustrate it with specific code examples. 1. Paging plug-in principle MyBatis itself does not provide native paging function, but you can use plug-ins to implement paging queries. The principle of paging plug-in is mainly to intercept MyBatis

An in-depth analysis of the functions and working principles of the Linux chage command An in-depth analysis of the functions and working principles of the Linux chage command Feb 24, 2024 pm 03:48 PM

The chage command in the Linux system is a command used to modify the password expiration date of a user account. It can also be used to modify the longest and shortest usable date of the account. This command plays a very important role in managing user account security. It can effectively control the usage period of user passwords and enhance system security. How to use the chage command: The basic syntax of the chage command is: chage [option] user name. For example, to modify the password expiration date of user "testuser", you can use the following command

See all articles