mydumper怎么用

小编给大家分享一下mydumper怎么用，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

下载：

https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz

预备安装：

yum install cmake*yum install glib2-devel zlib-devel pcre-devel openssl-devel

安装：

[root@iZ252affh68Z ~]# tar -zxvf mydumper-0.9.1.tar.gzmydumper-0.9.1/CMakeLists.txtmydumper-0.9.1/READMEmydumper-0.9.1/binlog.cmydumper-0.9.1/binlog.hmydumper-0.9.1/cmake/mydumper-0.9.1/common.hmydumper-0.9.1/config.h.inmydumper-0.9.1/docs/mydumper-0.9.1/g_unix_signal.cmydumper-0.9.1/g_unix_signal.hmydumper-0.9.1/mydumper.cmydumper-0.9.1/mydumper.hmydumper-0.9.1/myloader.cmydumper-0.9.1/myloader.hmydumper-0.9.1/server_detect.cmydumper-0.9.1/server_detect.hmydumper-0.9.1/cmake/modules/mydumper-0.9.1/cmake/modules/CppcheckTargets.cmakemydumper-0.9.1/cmake/modules/FindGLIB2.cmakemydumper-0.9.1/cmake/modules/FindMySQL.cmakemydumper-0.9.1/cmake/modules/FindPCRE.cmakemydumper-0.9.1/cmake/modules/FindSphinx.cmakemydumper-0.9.1/cmake/modules/Findcppcheck.cmakemydumper-0.9.1/cmake/modules/Findcppcheck.cppmydumper-0.9.1/docs/CMakeLists.txtmydumper-0.9.1/docs/_build/mydumper-0.9.1/docs/_static/mydumper-0.9.1/docs/authors.rstmydumper-0.9.1/docs/compiling.rstmydumper-0.9.1/docs/examples.rstmydumper-0.9.1/docs/files.rstmydumper-0.9.1/docs/index.rstmydumper-0.9.1/docs/mydumper_usage.rstmydumper-0.9.1/docs/myloader_usage.rstmydumper-0.9.1/docs/_build/conf.py.inmydumper-0.9.1/docs/_build/sources.cmake.in[root@iZ252affh68Z ~]# cd mydumper-0.9.1[root@iZ252affh68Z mydumper-0.9.1]# cmake .-- The C compiler identification is GNU 4.4.7-- The CXX compiler identification is GNU 4.4.7-- Check for working C compiler: /usr/bin/cc-- Check for working C compiler: /usr/bin/cc -- works-- Detecting C compiler ABI info-- Detecting C compiler ABI info - done-- Check for working CXX compiler: /usr/bin/c++-- Check for working CXX compiler: /usr/bin/c++ -- works-- Detecting CXX compiler ABI info-- Detecting CXX compiler ABI info - done-- Using mysql-config: /mnt/software/mysql/bin/mysql_config-- Found MySQL: /mnt/software/mysql/include, /mnt/software/mysql/lib/libmysqlclient.so;/usr/lib64/libpthread.so;/usr/lib64/libm.so;/usr/lib64/librt.so;/usr/lib64/libdl.so-- Found ZLIB: /usr/lib64/libz.so (found version "1.2.3") -- Found PkgConfig: /usr/bin/pkg-config (found version "0.23") -- checking for one of the modules 'glib-2.0'-- checking for one of the modules 'gthread-2.0'-- checking for module 'libpcre'--   found libpcre, version 7.8-- Found PCRE: /usr/include  CMake Warning at docs/CMakeLists.txt:9 (message):  Unable to find Sphinx documentation generator-- -------------------------------------------------- MYSQL_CONFIG = /mnt/software/mysql/bin/mysql_config-- CMAKE_INSTALL_PREFIX = /usr/local-- BUILD_DOCS = ON-- WITH_BINLOG = OFF-- RUN_CPPCHECK = OFF-- Change a values with: cmake -D=-- -------------------------------------------------- -- Configuring done-- Generating done-- Build files have been written to: /root/mydumper-0.9.1[root@iZ252affh68Z mydumper-0.9.1]# makeScanning dependencies of target mydumper[ 25%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o[ 50%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o[ 75%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.oLinking C executable mydumper[ 75%] Built target mydumperScanning dependencies of target myloader[100%] Building C object CMakeFiles/myloader.dir/myloader.c.oLinking C executable myloader[100%] Built target myloader[root@iZ252affh68Z mydumper-0.9.1]# make install[ 75%] Built target mydumper[100%] Built target myloaderInstall the project...-- Install configuration: ""-- Installing: /usr/local/bin/mydumper-- Removed runtime path from "/usr/local/bin/mydumper"-- Installing: /usr/local/bin/myloader-- Removed runtime path from "/usr/local/bin/myloader"

确认是否安装成功：缺少libmysqlclient.so.20

[root@iZ252affh68Z mydumper-0.9.1]# whereis mydumpermydumper: /usr/local/bin/mydumper[root@iZ252affh68Z mydumper-0.9.1]# [root@iZ252affh68Z mydumper-0.9.1]# ldd /usr/local/bin/mydumper linux-vdso.so.1 =>  (0x00007fffe131a000)libmysqlclient.so.20 => not foundlibpthread.so.0 => /lib64/libpthread.so.0 (0x0000003541e00000)libm.so.6 => /lib64/libm.so.6 (0x0000003542600000)librt.so.1 => /lib64/librt.so.1 (0x0000003542a00000)libdl.so.2 => /lib64/libdl.so.2 (0x0000003542200000)libglib-2.0.so.0 => /lib64/libglib-2.0.so.0 (0x00007f1a283ad000)libgthread-2.0.so.0 => /lib64/libgthread-2.0.so.0 (0x00007f1a281a9000)libpcre.so.0 => /lib64/libpcre.so.0 (0x00007f1a27f7b000)libz.so.1 => /lib64/libz.so.1 (0x0000003542e00000)libc.so.6 => /lib64/libc.so.6 (0x0000003541a00000)/lib64/ld-linux-x86-64.so.2 (0x0000003541600000)

解决办法：

[root@iZ252affh68Z mydumper-0.9.1]# locate  libmysqlclient.so.20/mnt/software/mysql/lib/libmysqlclient.so.20/mnt/software/mysql/lib/libmysqlclient.so.20.2.1[root@iZ252affh68Z mydumper-0.9.1]# cp /mnt/software/mysql/lib/libmysqlclient.so.20 /usr/lib64[root@iZ252affh68Z mydumper-0.9.1]# [root@iZ252affh68Z mydumper-0.9.1]# mydumper** (mydumper:25013): CRITICAL **: Error connecting to database: Access denied for user 'root'@'localhost' (using password: NO)

查看版本号，已经安装成功了

[root@iZ252affh68Z mydumper-0.9.1]# mydumper -Vmydumper 0.9.1, built against MySQL 5.7.12

查看帮助：

[root@iZ252affh68Z mydumper-0.9.1]# 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  -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  -U, --updated-since         Use Update_time to dump only tables updated in the last U days  --trx-consistency-only      Transactional consistency only  -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

备份全库：

mydumper -u root -p 'xxxxxDB2015!@#'  -o /mnt/backup/

备份mysqlhqdb数据库：

mydumper -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -o /mnt/backup/

备份多张表(tableA,tableB)：

mydumper -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -T tableA,tableB -o /mnt/backup/

备份tableA表的数据，不备份表结构

mydumper -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -T tableA -m -o /mnt/backup/

备份tableA表的数据，并进行压缩

mydumper -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -T tableA -c -o /mnt/backup/

还原mysqlhqdb库：

myloader -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -d /mnt/backup/

还原tableA表

myloader -u root -p 'xxxxxDB2015!@#' -B mysqlhqdb -o tableA -d /mnt/backup/

mysqldump和mydumper的速度对比

首先用mydumper进行全库备份，耗时为12分钟

[root@iZ252affh68Z mnt]# time mydumper -u root -p 'xxxxxDB2015!@#'  -o /mnt/backup/real12m19.526suser3m29.925ssys0m36.017s

再用mysqldump全库备份，耗时为18分钟

[root@iZ252affh68Z mnt]# time mysqldump -uroot -p'xxxxxDB2015!@#' --single-transaction --all-databases > /mnt/backup/all.sql-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.real18m0.563suser5m53.119ssys0m48.295s

以上是"mydumper怎么用"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！