mydumper怎么用
小编给大家分享一下mydumper怎么用,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!
下载:
https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz预备安装:
yuminstallcmake*yuminstallglib2-develzlib-develpcre-developenssl-devel
安装:
[root@iZ252affh68Z~]#tar-zxvfmydumper-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~]#cdmydumper-0.9.1[root@iZ252affh68Zmydumper-0.9.1]#cmake.--TheCcompileridentificationisGNU4.4.7--TheCXXcompileridentificationisGNU4.4.7--CheckforworkingCcompiler:/usr/bin/cc--CheckforworkingCcompiler:/usr/bin/cc--works--DetectingCcompilerABIinfo--DetectingCcompilerABIinfo-done--CheckforworkingCXXcompiler:/usr/bin/c++--CheckforworkingCXXcompiler:/usr/bin/c++--works--DetectingCXXcompilerABIinfo--DetectingCXXcompilerABIinfo-done--Usingmysql-config:/mnt/software/mysql/bin/mysql_config--FoundMySQL:/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--FoundZLIB:/usr/lib64/libz.so(foundversion"1.2.3")--FoundPkgConfig:/usr/bin/pkg-config(foundversion"0.23")--checkingforoneofthemodules'glib-2.0'--checkingforoneofthemodules'gthread-2.0'--checkingformodule'libpcre'--foundlibpcre,version7.8--FoundPCRE:/usr/includeCMakeWarningatdocs/CMakeLists.txt:9(message):UnabletofindSphinxdocumentationgenerator----------------------------------------------------MYSQL_CONFIG=/mnt/software/mysql/bin/mysql_config--CMAKE_INSTALL_PREFIX=/usr/local--BUILD_DOCS=ON--WITH_BINLOG=OFF--RUN_CPPCHECK=OFF--Changeavalueswith:cmake-D<Variable>=<Value>------------------------------------------------------Configuringdone--Generatingdone--Buildfileshavebeenwrittento:/root/mydumper-0.9.1[root@iZ252affh68Zmydumper-0.9.1]#makeScanningdependenciesoftargetmydumper[25%]BuildingCobjectCMakeFiles/mydumper.dir/mydumper.c.o[50%]BuildingCobjectCMakeFiles/mydumper.dir/server_detect.c.o[75%]BuildingCobjectCMakeFiles/mydumper.dir/g_unix_signal.c.oLinkingCexecutablemydumper[75%]BuilttargetmydumperScanningdependenciesoftargetmyloader[100%]BuildingCobjectCMakeFiles/myloader.dir/myloader.c.oLinkingCexecutablemyloader[100%]Builttargetmyloader[root@iZ252affh68Zmydumper-0.9.1]#makeinstall[75%]Builttargetmydumper[100%]BuilttargetmyloaderInstalltheproject...--Installconfiguration:""--Installing:/usr/local/bin/mydumper--Removedruntimepathfrom"/usr/local/bin/mydumper"--Installing:/usr/local/bin/myloader--Removedruntimepathfrom"/usr/local/bin/myloader"
确认是否安装成功:缺少libmysqlclient.so.20
[root@iZ252affh68Zmydumper-0.9.1]#whereismydumpermydumper:/usr/local/bin/mydumper[root@iZ252affh68Zmydumper-0.9.1]#[root@iZ252affh68Zmydumper-0.9.1]#ldd/usr/local/bin/mydumperlinux-vdso.so.1=>(0x00007fffe131a000)libmysqlclient.so.20=>notfoundlibpthread.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@iZ252affh68Zmydumper-0.9.1]#locatelibmysqlclient.so.20/mnt/software/mysql/lib/libmysqlclient.so.20/mnt/software/mysql/lib/libmysqlclient.so.20.2.1[root@iZ252affh68Zmydumper-0.9.1]#cp/mnt/software/mysql/lib/libmysqlclient.so.20/usr/lib64[root@iZ252affh68Zmydumper-0.9.1]#[root@iZ252affh68Zmydumper-0.9.1]#mydumper**(mydumper:25013):CRITICAL**:Errorconnectingtodatabase:Accessdeniedforuser'root'@'localhost'(usingpassword:NO)
查看版本号,已经安装成功了
[root@iZ252affh68Zmydumper-0.9.1]#mydumper-Vmydumper0.9.1,builtagainstMySQL5.7.12
查看帮助:
[root@iZ252affh68Zmydumper-0.9.1]#mydumper--helpUsage:mydumper[OPTION...]multi-threadedMySQLdumpingHelpOptions:-?,--helpShowhelpoptionsApplicationOptions:-B,--databaseDatabasetodump-T,--tables-listCommadelimitedtablelisttodump(doesnotexcluderegexoption)-o,--outputdirDirectorytooutputfilesto-s,--statement-sizeAttemptedsizeofINSERTstatementinbytes,default1000000-r,--rowsTrytosplittablesintochunksofthismanyrows.Thisoptionturnsoff--chunk-filesize-F,--chunk-filesizeSplittablesintochunksofthisoutputfilesize.ThisvalueisinMB-c,--compressCompressoutputfiles-e,--build-empty-filesBuilddumpfilesevenifnodataavailablefromtable-x,--regexRegularexpressionfor'db.table'matching-i,--ignore-enginesCommadelimitedlistofstorageenginestoignore-m,--no-schemasDonotdumptableschemaswiththedata-d,--no-dataDonotdumptabledata-G,--triggersDumptriggers-E,--eventsDumpevents-R,--routinesDumpstoredproceduresandfunctions-k,--no-locksDonotexecutethetemporarysharedreadlock.WARNING:Thiswillcauseinconsistentbackups--less-lockingMinimizelockingtimeonInnoDBtables.-l,--long-query-guardSetlongquerytimerinseconds,default60-K,--kill-long-queriesKilllongrunningqueries(insteadofaborting)-D,--daemonEnabledaemonmode-I,--snapshot-intervalIntervalbetweeneachdumpsnapshot(inminutes),requires--daemon,default60-L,--logfileLogfilenametouse,bydefaultstdoutisused--tz-utcSETTIME_ZONE='+00:00'attopofdumptoallowdumpingofTIMESTAMPdatawhenaserverhasdataindifferenttimezonesordataisbeingmovedbetweenserverswithdifferenttimezones,defaultstoonuse--skip-tz-utctodisable.--skip-tz-utc--use-savepointsUsesavepointstoreducemetadatalockingissues,needsSUPERprivilege--success-on-1146NotincrementerrorcountandWarninginsteadofCriticalincaseoftabledoesn'texist--lock-all-tablesUseLOCKTABLEforall,insteadofFTWRL-U,--updated-sinceUseUpdate_timetodumponlytablesupdatedinthelastUdays--trx-consistency-onlyTransactionalconsistencyonly-h,--hostThehosttoconnectto-u,--userUsernamewithprivilegestorunthedump-p,--passwordUserpassword-P,--portTCP/IPporttoconnectto-S,--socketUNIXdomainsocketfiletouseforconnection-t,--threadsNumberofthreadstouse,default4-C,--compress-protocolUsecompressionontheMySQLconnection-V,--versionShowtheprogramversionandexit-v,--verboseVerbosityofoutput,0=silent,1=errors,2=warnings,3=info,default2
备份全库:
mydumper-uroot-p'xxxxxDB2015!@#'-o/mnt/backup/
备份mysqlhqdb数据库:
mydumper-uroot-p'xxxxxDB2015!@#'-Bmysqlhqdb-o/mnt/backup/
备份多张表(tableA,tableB):
mydumper-uroot-p'xxxxxDB2015!@#'-Bmysqlhqdb-TtableA,tableB-o/mnt/backup/
备份tableA表的数据,不备份表结构
mydumper-uroot-p'xxxxxDB2015!@#'-Bmysqlhqdb-TtableA-m-o/mnt/backup/
备份tableA表的数据,并进行压缩
mydumper-uroot-p'xxxxxDB2015!@#'-Bmysqlhqdb-TtableA-c-o/mnt/backup/
还原mysqlhqdb库:
myloader-uroot-p'xxxxxDB2015!@#'-Bmysqlhqdb-d/mnt/backup/
还原tableA表
myloader-uroot-p'xxxxxDB2015!@#'-Bmysqlhqdb-otableA-d/mnt/backup/
mysqldump和mydumper的速度对比
首先用mydumper进行全库备份,耗时为12分钟
[root@iZ252affh68Zmnt]#timemydumper-uroot-p'xxxxxDB2015!@#'-o/mnt/backup/real12m19.526suser3m29.925ssys0m36.017s
再用mysqldump全库备份,耗时为18分钟
[root@iZ252affh68Zmnt]#timemysqldump-uroot-p'xxxxxDB2015!@#'--single-transaction--all-databases>/mnt/backup/all.sql--Warning:Skippingthedataoftablemysql.event.Specifythe--eventsoptionexplicitly.real18m0.563suser5m53.119ssys0m48.295s
以上是“mydumper怎么用”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注亿速云行业资讯频道!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。