Introduction and use of mydumper backup tool

Foreword:

The previous article has introduced the use of the mysqldump backup tool that comes with the MySQL system. In fact, there is an open source tool mydumper that is also suitable for MySQL logical backup. I haven't experienced it formally before, but I heard that it is much faster than mysqldump. Let's take a look at the specific effects.

1. Introduction to mydumper

mydumper is a community open source logical backup tool. This tool is mainly written in C language and is currently developed and maintained by personnel from companies such as MySQL and Facebook.

Official website address: https://launchpad.net/mydumper
GitHub address: https://github.com/maxbube/mydumper

According to the official introduction, mydumper mainly has the following features:

• Support multi-threaded export data, faster.
• Supports consistent backup.
• Support to compress the exported file to save space.
• Support multi-threaded recovery.
• Supports work in daemon mode, timed snapshots and continuous binary logs.
• Support to cut the backup file according to the specified size.
• The data is separated from the table building statement.

2. mydumper installation

There are about two installation methods, one is to compile and install, and the other is to install the rpm package directly by yum. Take the CentOS system as an example below to briefly show the installation method:

# 1.编译安装
[root@localhost ~]# yum -y  install glib2-devel mysql-devel zlib-devel pcre-devel zlib gcc-c++ gcc cmake
[root@localhost ~]# wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
[root@localhost ~]# tar zxf mydumper-0.9.1.tar.gz
[root@localhost ~]# cd mydumper-0.9.1/
[root@localhost mydumper-0.9.1]# cmake .
[root@localhost mydumper-0.9.1]# make
[root@localhost mydumper-0.9.1]# make install
# 安装完成后生成两个二进制文件 mydumper 和 myloader 位于 /usr/local/bin 目录下
[root@localhost bin]# ls /usr/local/bin/
mydumper  myloader

# 2.直接安装 rpm 包
# rpm包地址：https://github.com/maxbube/mydumper/releases 请根据自己的系统类型选择下载版本
[root@localhost ~]# yum install https://github.com/maxbube/mydumper/releases/download/v0.10.7-2/mydumper-0.10.7-2.el7.x86_64.rpm
# 安装完成后生成两个二进制文件 mydumper 和 myloader 位于 /usr/bin 目录下

3. Mydumper use

After the installation is complete, it can be clearly seen that mydumper and myloader are a set of corresponding executable programs, and the functions of the two are to export data and import data respectively. Let's first look at how to use mydumper to back up data.

First execute the mydumper --help command to get help information. Similar to mysqldump, mydumper can also customize many parameters. Refer to the official introduction. The following briefly describes some commonly used parameters.

Parameter name	abbreviation	Meaning
--user	-u	User used for backup
--pasword	-p	User password for connection
--host	-h	MySQL server address
--port	-P	MySQL port number
--threads	-t	The number of backup threads to start, the default is 4
--database	-B	The database to be backed up, all databases are backed up if not specified
--tables-list	-T	The tables to be backed up, whose names are separated by commas
--outputdir	-o	The directory where the backup file is output
--statement-size	-s	The number of bytes of the generated insert statement, the default is 1000000
--rows	-r	Split the table into rows. Specifying this option will turn off --chunk-filesize
--chunk-filesize	-F	When splitting the table by size, specify the split size in MB
--regex	-x	Use regular expression to match'db.table'
--compress	-c	Compress output files
--ignore-engines	-i	Ignored storage engine
--no-schemas	-m	Do not backup table structure
--no-data	-d	Do not backup table data
--triggers	-G	Backup trigger
--events	-E	Backup event
--routines	-R	Backup stored procedures and functions
--no-views	-W	Do not back up views
--no-locks	-k	Do not use temporary shared read-only locks, using this option will cause data inconsistency
--daemon	-D	Enable the daemon process mode, the daemon process mode will back up the database without interruption at a certain interval
--where		Export only selected data

After reading these parameters, do you want to try it out? Let's use it in detail and feel the effect.

# 备份全部数据库 
mydumper -u root -p 123456 -o /mysql_backup/all/
# 全量备份 会备份 mysql、sys 系统库及其他自建库

# 备份全部数据库 包含触发器、事件、存储过程及函数
mydumper -u root -p 123456 -G -R -E -o /mysql_backup/all2/

# 备份指定库
mydumper -u root -p 123456 -G -R -E -B db1 -o /mysql_backup/db1/

# 使用正则 排除系统库
mydumper -u root -p 123456 -G -R -E --regex '^(?!(mysql|sys))' -o /mysql_backup/all3

# 备份指定表
mydumper -u root -p 123456 -B db1 -T tb1,tb2 -o /mysql_backup/tb/

# 只备份表结构
mydumper -u root -p 123456 -d -B db1 -o /mysql_backup/nodata/

# 只备份表数据
mydumper -u root -p 123456 -m -B db1 -o /mysql_backup/noschema/

# 压缩备份某个表
mydumper -u root -p 123456 -B db1 -T tb1 -c -o /mysql_backup/compress/

The above are the corresponding backup statements for different scenarios. After the actual execution, we found that: mydumper backup will generate multiple files, such as database building, table building, table data, etc., all in different files. Take the full backup of the db1 library as an example, let's look at the naming rules and functions of the files that are backed up.

[root@localhost ~]# cd /mysql_backup/db1/
[root@localhost db1]# tree .
.
├── db1-schema-create.sql
├── db1-schema-post.sql
├── db1.tb1.metadata
├── db1.tb1-schema.sql
├── db1.tb1-schema-triggers.sql
├── db1.tb1.sql
├── db1.tb2.metadata
├── db1.tb2-schema.sql
├── db1.tb2.sql
├── db1.tb3.metadata
├── db1.tb3-schema.sql
├── db1.view1-schema.sql
├── db1.view1-schema-view.sql
└── metadata

0 directories, 14 files

The file names that may be backed up by each version are slightly different. The content of the file can be seen more clearly from the file name. The file naming rules are summarized as follows:

• dbname-schema-create.sql: database creation statement.
• dbname-schema-post.sql: Contains events, stored procedures and function creation statements (if they exist, there is this file).
• dbname.tbname.metadata: Record the number of rows in this table.
• dbname.tbname-schema.sql: The creation statement of this table.
• dbname.tbname-schema-triggers.sql: create a trigger statement (if there is a trigger in the table, there is this file).
• dbname.tbname.sql: insert data statement for the table (if the table is empty, the file does not exist).
• dbname.viewname-schema.sql: Create view statement (only list view fields).
• dbname.viewname-schema-view.sql: The actual statement to create the view.
• metadata: Record the start and end time of the backup and the location of the binary log.

summary:

This article briefly introduces the installation and usage of the mydumper backup tool, as a record of your own learning, I hope this article can be helpful to you, and those who want to experience the mydumper tool can refer to it. If you want to use this tool well, you need to accumulate actual combat experience yourself, and it will be more comfortable if you use it a lot.