mydumper备份原理和使用方法

mydumper介绍

MySQL自身的mysqldump工具支持单线程工作，依次一个个导出多个表，没有一个并行的机，这就使得它无法迅速的备份数据。

mydumper作为一个实用工具，能够良好支持多线程工作，可以并行的多线程的从表中读入数据并同时写到不同的文件里，这使得它在处理速度方面快于传统的mysqldump。其特征之一是在处理过程中需要对列表加以锁定，因此如果我们需要在工作时段执行备份工作，那么会引起DML阻塞。但一般现在的MySQL都有主从，备份也大部分在从上进行，所以锁的问题可以不用考虑。这样，mydumper能更好的完成备份任务。

 

mydumper特性

• 多线程备份 因为是多线程逻辑备份，备份后会生成多个备份文件 备份时对MyISAM表施加FTWRL(FLUSH TABLES WITH READ LOCK),会阻塞DML语句 保证备份数据的一致性 支持文件压缩 支持导出binlog 支持多线程恢复 支持以守护进程模式工作，定时快照和连续二进制日志 支持将备份文件切块

  mydumper的安装

  mydumper使用c语言编写，使用glibc库

  mydumper安装所依赖的软件包，glibc, zlib, pcre, pcre-devel, gcc, gcc-c++, cmake, make, mysql客户端库文件

  1. 安装依赖软件包，将mysql客户端库文件路径添加至/etc/ld.so.conf, 如/usr/local/mysql/lib 解压软件包进入目录，cmake .
  2. make && make install

      

     安装完成后生成两个二进制文件mydumper和myloader位于/usr/local/bin目录下

      

     mydumper备份机制

     mydumper工作流程图

     

     主要步骤概括

     1. 主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁，以阻止DML语句写入，保证数据的一致性 读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中，以供即使点恢复使用 N个（线程数可以指定，默认是4）dump线程 START TRANSACTION WITH CONSISTENT SNAPSHOT; 开启读一致的事物 dump non-InnoDB tables, 首先导出非事物引擎的表 主线程 UNLOCK TABLES 非事物引擎备份完后，释放全局只读锁 dump InnoDB tables, 基于事物导出InnoDB表 事物结束

        备份所生成的文件

        • 所有的备份文件在一个目录中，目录可以自己指定 目录中包含一个metadata文件

          　　　　记录了备份数据库在备份时间点的二进制日志文件名，日志的写入位置，

          　　　　如果是在从库进行备份，还会记录备份时同步至主库的二进制日志文件及写入位置

          • 每个表有两个备份文件：

            　　　　database.table-schema.sql 表结构文件

            　　　　database.table.sql 表数据文件

            　　　　如果对表文件分片，将生成多个备份数据文件，可以指定行数或指定大小分片

            mydumper和myloader的使用

            mydumper参数

            view source print ?

            01. -B, --database              要备份的数据库，不指定则备份所有库

            02. -T, --tables-list           需要备份的表，名字用逗号隔开

            03. -o, --outputdir             备份文件输出的目录

            04. -s, --statement-size        生成的insert语句的字节数，默认1000000

            05. -r, --rows                  将表按行分块时，指定的块行数，指定这个选项会关闭 --chunk-filesize

            06. -F, --chunk-filesize        将表按大小分块时，指定的块大小，单位是 MB

            07. -c, --compress              压缩输出文件

            08. -e, --build-empty-files     如果表数据是空，还是产生一个空文件（默认无数据则只有表结构文件）

            09. -x, --regex                 是同正则表达式匹配 'db.table'

            10. -i, --ignore-engines        忽略的存储引擎，用都厚分割

            11. -m, --no-schemas            不备份表结构

            12. -k, --no-locks              不使用临时共享只读锁，使用这个选项会造成数据不一致

            13. --less-locking              减少对InnoDB表的锁施加时间（这种模式的机制下文详解）

            14. -l, --long-query-guard      设定阻塞备份的长查询超时时间，单位是秒，默认是60秒（超时后默认mydumper将会退出）

            15. --kill-long-queries         杀掉长查询 (不退出)

            16. -b, --binlogs               导出binlog

            17. -D, --daemon                启用守护进程模式，守护进程模式以某个间隔不间断对数据库进行备份

            18. -I, --snapshot-interval     dump快照间隔时间，默认60s，需要在daemon模式下

            19. -L, --logfile               使用的日志文件名(mydumper所产生的日志), 默认使用标准输出

            20. --tz-utc                    跨时区是使用的选项，不解释了

            21. --skip-tz-utc               同上

            22. --use-savepoints            使用savepoints来减少采集metadata所造成的锁时间，需要 SUPER 权限

            23. --success-on-1146           Not increment error count and Warning instead of Critical in case of table doesn't exist

            24. -h, --host                  连接的主机名

            25. -u, --user                  备份所使用的用户

            26. -p, --pass<a href="http://www.it165.net/edu/ebg/" target="_blank"class="keylink">word</a>              密码

            27. -P, --port                  端口

            28. -S, --socket                使用socket通信时的socket文件

            29. -t, --threads               开启的备份线程数，默认是4

            30. -C, --compress-protocol     压缩与mysql通信的数据

            31. -V, --version               显示版本号

            32. -v, --verbose               输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3= info, 默认为 2

              

            myloader使用参数

            view source print ?

            01. -d, --directory                   备份文件的文件夹

            02. -q, --queries-per-transaction     每次事物执行的查询数量，默认是1000

            03. -o, --overwrite-tables            如果要恢复的表存在，则先drop掉该表，使用该参数，需要备份时候要备份表结构

            04. -B, --database                    需要还原的数据库

            05. -e, --enable-binlog               启用还原数据的二进制日志

            06. -h, --host                        主机

            07. -u, --user                        还原的用户

            08. -p, --pass<a href="http://www.it165.net/edu/ebg/" target="_blank"class="keylink">word</a>                    密码

            09. -P, --port                        端口

            10. -S, --socket                      socket文件

            11. -t, --threads                     还原所使用的线程数，默认是4

            12. -C, --compress-protocol           压缩协议

            13. -V, --version                     显示版本

            14. -v, --verbose                     输出模式, 0 = silent, 1 = errors, 2 = warnings,3 = info, 默认为2

            使用案例
            备份game库到/backup/01文件夹中，并压缩备份文件

            mydumper -u root -p ### -h localhost -B game -c -o /backup/01

             

            备份所有数据库，并备份二进制日志文件，备份至/backup/02文件夹

            mydumper -u root -p ### -h localhost -o /backup/02

             

            备份game.tb_player表，且不备份表结构，备份至/backup/03文件夹

            mydumper -u root -p ### -h localhost -T tb_player -m -o /backup/03

             

            还原

            mysqlload -u root -p ### -h localhost -B game -d /backup/02

            mydumper的less locking模式

            mydumper使用--less-locking可以减少锁等待时间，此时mydumper的执行机制大致为

            • 主线程 FLUSH TABLES WITH READ LOCK （全局锁） Dump线程 START TRANSACTION WITH CONSISTENT SNAPSHOT; LL Dump线程 LOCK TABLES non-InnoDB （线程内部锁） 主线程UNLOCK TABLES LL Dump线程 dump non-InnoDB tables LL DUmp线程 UNLOCK non-InnoDB Dump线程 dump InnoDB tables

附：mydumper usage

Mydumper Usage
==============

Synopsis
--------

:program:`mydumper` [:ref:`OPTIONS <mydumper-options-label>`]

Description
-----------

:program:`mydumper` is a tool used for backing up MySQL database servers much
faster than the mysqldump tool distributed with MySQL.  It also has the
capability to retrieve the binary logs from the remote server at the same time
as the dump itself.  The advantages of mydumper are:

  * Parallelism (hence, speed) and performance (avoids expensive character set conversion routines, efficient code overall)
  * Easier to manage output (separate files for tables, dump metadata, etc, easy to view/parse data)
  * Consistency - maintains snapshot across all threads, provides accurate master and slave log positions, etc
  * Manageability - supports PCRE for specifying database and tables inclusions and exclusions

.. _mydumper-options-label:

Options
-------

The :program:`mydumper` tool has several available options:

.. program:: mydumper

.. option:: --help, -?

   Show help text

.. option:: --host, -h

   Hostname of MySQL server to connect to (default localhost)

.. option:: --user, -u

   MySQL username with the correct privileges to execute the dump

.. option:: --password, -p

   The corresponding password for the MySQL user

.. option:: --port, -P

   The port for the MySQL connection.

   .. note::

      For localhost TCP connections use 127.0.0.1 for :option:`--host`.

.. option:: --socket, -S

   The UNIX domain socket file to use for the connection

.. option:: --database, -B

   Database to dump

.. option:: --tables-list, -T

   A comma separated list of tables to dump

.. option:: --threads, -t

   The number of threads to use for dumping data, default is 4

   .. note::

      Other threads are used in mydumper, this option does not control these

.. option:: --outputdir, -o

   Output directory name, default is export-YYYYMMDD-HHMMSS

.. option:: --statement-size, -s

   The maximum size for an insert statement before breaking into a new
   statement, default 1,000,000 bytes

.. option:: --rows, -r

   Split table into chunks of this many rows, default unlimited

.. option:: --compress, -c

   Compress the output files

.. option:: --compress-input, -C

   Use client protocol compression for connections to the MySQL server

.. option:: --build-empty-files, -e

   Create empty dump files if there is no data to dump

.. option:: --regex, -x

   A regular expression to match against database and table

.. option:: --ignore-engines, -i

   Comma separated list of storage engines to ignore

.. option:: --no-schemas, -m

   Do not dump schemas with the data
   
.. option:: --no-data, -d

   Do not dump table data
   
.. option:: --triggers, -G

   Dump triggers

.. option:: --events, -E

   Dump events

.. option:: --routines, -R

   Dump stored procedures and functions

.. option:: --long-query-guard, -l

   Timeout for long query execution in seconds, default 60

.. option:: --kill-long-queries, -K

   Kill long running queries instead of aborting the dump

.. option:: --version, -V

   Show the program version and exit

.. option:: --verbose, -v

   The verbosity of messages.  0 = silent, 1 = errors, 2 = warnings, 3 = info.
   Default is 2.

.. option:: --binlogs, -b

   Get the binlogs from the server as well as the dump files (You need to compile with -DWITH_BINLOG=ON)

.. option::  --daemon, -D

   Enable daemon mode

.. option:: --snapshot-interval, -I

   Interval between each dump snapshot (in minutes), requires
   :option:`--daemon`, default 60 (minutes)

.. option:: --logfile, -L

   A file to log mydumper output to instead of console output.  Useful for
   daemon mode.

.. option:: --no-locks, -k

   Do not execute the temporary shared read lock.

   .. warning::
      
      This will cause inconsistent backups.

.. option:: --[skip-]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.

.. option:: --less-locking

   Minimize locking time on InnoDB tables grabbing a LOCK TABLE ... READ 
   on all non-innodb tables.

.. option:: --chunk-filesize -F

   Split tables into chunks of this output file size. This value is in MB

.. option:: --success-on-1146

   Not increment error count and Warning instead of Critical in case of table doesn't exist

.. option:: --use-savepoints

   Use savepoints to reduce metadata locking issues, needs SUPER privilege




Myloader Usage
==============

Synopsis
--------

:program:`myloader` :option:`--directory <myloader --directory>` = /path/to/mydumper/backup [:ref:`OPTIONS <myloader-options-label>`]

Description
-----------

:program:`myloader` is a tool used for multi-threaded restoration of mydumper
backups.

.. _myloader-options-label:

Options
-------

The :program:`myloader` tool has several available options:

.. program:: myloader

.. option:: --help, -?

   Show help text

.. option:: --host, -h

   Hostname of MySQL server to connect to (default localhost)

.. option:: --user, -u

   MySQL username with the correct privileges to execute the restoration

.. option:: --password, -p

   The corresponding password for the MySQL user

.. option:: --port, -P

   The port for the MySQL connection.

   .. note::

      For localhost TCP connections use 127.0.0.1 for :option:`--host`.

.. option:: --socket, -S

   The UNIX domain socket file to use for the connection

.. option:: --threads, -t

   The number of threads to use for restoring data, default is 4

.. option:: --version, -V

   Show the program version and exit

.. option:: --compress-protocol, -C

   Use client protocol compression for connections to the MySQL server

.. option:: --directory, -d

   The directory of the mydumper backup to restore

.. option:: --database, -B

   An alternative database to load the dump into

   .. note::

      For use with single database dumps.  When using with multi-database dumps
      that have duplicate table names in more than one database it may cause 
      errors.  Alternatively this scenario may give unpredictable results with
      :option:`--overwrite-tables`.

.. option:: --source-db, -s

   Database to restore, useful in combination with --database
   
.. option:: --queries-per-transaction, -q

   Number of INSERT queries to execute per transaction during restore, default
   is 1000.

.. option:: --overwrite-tables, -o

   Drop any existing tables when restoring schemas

.. option:: --enable-binlog, -e

   Log the data loading in the MySQL binary log if enabled (off by default)

.. option:: --verbose, -v

   The verbosity of messages.  0 = silent, 1 = errors, 2 = warnings, 3 = info.
   Default is 2.