mydumper安装及安装故障汇总

我不是女神ヾ 2022-08-02 00:21 212阅读 0赞

mydumper是针对mysql数据库备份的一个轻量级第三方的开源工具，备份方式术语逻辑备份。它支持多线程，备份速度远高于原生态的mysqldump以及众多优异特性。因此该工具是DBA们的不二选择。本文主要描述该工具的安装及其出现异常的解决方法。

1、mydumper特性(直接参考readme)  
  \* 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  
  It does not support schema dumping and leaves that to 'mysqldump --no-data'

2、获取mydumper及安装方法  
下载地址：[https://launchpad.net/mydumper][https_launchpad.net_mydumper]  
== How to build it? ==  
Run:  
 cmake .  
 make

One needs to install development versions of required libaries (MySQL, GLib, ZLib, PCRE):  
NOTE: you must use the correspondent mysql devel package.

\* Ubuntu or Debian: apt-get install libglib2.0-dev libmysqlclient15-dev zlib1g-dev libpcre3-dev libssl-dev  
\* Fedora, RedHat and CentOS: yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel  
\* openSUSE: zypper install glib2-devel libmysqlclient-devel pcre-devel zlib-devel  
\* MacOSX: port install glib2 mysql5 pcre pkgconfig cmake  
 (You may want to run 'port select mysql mysql5' afterwards)

One has to make sure, that pkg-config, mysql\_config, pcre-config are all in $PATH

Binlog dump is disabled by default to compile with it you need to add -DWITH\_BINLOG=ON to cmake options  
\#\#\#如果要使用binlog dump在编译的时候需要使用-DWITH\_BINLOG=ON

3、快照工作原理  
== How does consistent snapshot work? ==

This is all done following best MySQL practices and traditions:

\* As a precaution, slow running queries on the server either abort the dump, or get killed  
\* Global write lock is acquired ("FLUSH TABLES WITH READ LOCK")  
\* Various metadata is read ("SHOW SLAVE STATUS","SHOW MASTER STATUS")  
\* Other threads connect and establish snapshots ("START TRANSACTION WITH CONSISTENT SNAPSHOT")  
\*\* On pre-4.1.8 it creates dummy InnoDB table, and reads from it.  
\* Once all worker threads announce the snapshot establishment, master executes "UNLOCK TABLES" and starts queueing jobs.

This for now does not provide consistent snapshots for non-transactional engines - support for that is expected in 0.2 :)

4、如何过滤数据库  
== How to exclude (or include) databases? ==

Once can use --regex functionality, for example not to dump mysql and test databases:

mydumper --regex '^(?!(mysql|test))'

Of course, regex functionality can be used to describe pretty much any list of tables.

== How to exclude MERGE or Federated tables ==

Use same --regex exclusion syntax. Again, engine-specific behaviors are targetted for 0.2

5、实际安装过程  
\# ls mydump\*  
mydumper-0.6.2.tar.gz  
\# tar -xvf mydumper-0.6.2.tar.gz  
\# cd mydumper-0.6.2  
\[root@GZ-APP-BAK01 mydumper-0.6.2\]\# cmake .  
\[root@GZ-APP-BAK01 mydumper-0.6.2\]\# make && make install

6、安装过程中的异常  
错误1：  
CMake Error: The following variables are used in this project, but they are set to NOTFOUND.  
Please set them or make sure they are set and tested correctly in the CMake files:  
PCRE\_INCLUDE\_DIR (ADVANCED)  
   used as include directory in directory /root/mydumper-0.6.2  
PCRE\_PCRE\_LIBRARY (ADVANCED)  
    linked by target "mydumper" in directory /root/mydumper-0.6.2  
    linked by target "myloader" in directory /root/mydumper-0.6.2  
\#\#\#需要安装pcre-devel包，yum install pcre-devel

错误2：  
CMake Error: The following variables are used in this project, but they are set to NOTFOUND.  
Please set them or make sure they are set and tested correctly in the CMake files:  
MYSQL\_INCLUDE\_DIR (ADVANCED)  
   used as include directory in directory /home/robin/mydumper-0.6.2  
   used as include directory in directory /home/robin/mydumper-0.6.2  
   used as include directory in directory /home/robin/mydumper-0.6.2  
\#\#\#出现上述错误，是由于没有配置MYSQL\_INCLUDE\_DIR目录。  
\#\#\#如果是编译安装到非缺省路径，可以将mysql安装路径添加到/etc/profile或者家目录~/.bash\_profile后source生效

错误3：  
\[root@GZ-APP-BAK01 ~\]\# mydumper --help|more  
mydumper: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory  
\[root@GZ-APP-BAK01 ~\]\# mydumper  
mydumper: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory  
\#\#\#出现上述错误，应考虑建立软链  
\# which libmysqlclient.so.18  
/app/soft/mysql/lib/libmysqlclient.so.18  
\# ln -s  /app/soft/mysql/lib/libmysqlclient.so.18 /usr/lib/libmysqlclient.so.18

错误4：  
\# mydumper -uusr1 -ppwd -B blos -o /tmp/bak  
option parsing failed: Error parsing option -r, try --help  
\#\#\#参数和字符串不能连着写，笔者刚开始是也是丈二和尚摸不着头脑。

\# mydumper --version  
mydumper 0.6.2, built against MySQL 5.6.22

7、获取帮助  
\# mydumper --help|more  
Usage:  
  mydumper \[OPTION...\] multi-threaded MySQL dumping

Help Options:  
  -?, --help                  Show help options

Application 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  
  -k, --no-locks              Do not execute the temporary shared read lock.  WARNING: This will cause inconsistent backups  
  --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  
  -h, --host                  The host to connect to  
  -u, --user                  Username with privileges to run the dump  
  -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

[https_launchpad.net_mydumper]: https://launchpad.net/mydumper