Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use mydumper, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Download:

Https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz ready for installation:

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

Installation:

[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 message-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-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"

Confirm successful installation: missing 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)

Solution:

[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)

Check the version number. It has been installed successfully.

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

View help:

[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

Back up the entire library:

Mydumper-u root-p 'xxxxxDB2015 recent events'-o / mnt/backup/

Back up the mysqlhqdb database:

Mydumper-u root-p 'xxxxxDB2015 mysqlhqdb mnt/backup/'-B xxxxxDB2015

Back up multiple tables (tableA,tableB):

Mydumper-u root-p 'xxxxxDB2015 tableA,tableB mnt/backup/'-B mysqlhqdb-T tableA,tableB-o / mnt/backup/

Back up the data of the tableA table, not the table structure

Mydumper-u root-p 'xxxxxDB2015benchmark mnt/backup/'-B mysqlhqdb-T tableA-m-o / mnt/backup/

Back up and compress the data from the tableA table

Mydumper-u root-p 'xxxxxDB2015benchmark mnt/backup/'-B mysqlhqdb-T tableA-c-o / mnt/backup/

Restore the mysqlhqdb library:

Myloader-u root-p 'xxxxxDB2015 mysqlhqdb mnt/backup/'-B xxxxDB2015

Restore the tableA table

Myloader-u root-p 'xxxxxDB2015 tableA tableA'-B mysqlhqdb-o xxxxDB2015

Speed comparison between mysqldump and mydumper

First, make a full library backup with mydumper, which takes 12 minutes.

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

Then use mysqldump to back up the full database, which takes 18 minutes.

[root@iZ252affh68Z mnt] # time mysqldump-uroot-paired 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 above is all the content of this article "how to use mydumper". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.