【MySQL】數(shù)據(jù)庫邏輯備份工具--mydumper-創(chuàng)新互聯(lián)

[root@wallet01?~]#?yum?install?-y?glib2-devel?mysql-devel?zlib-devel?\
pcre-devel?openssl-devel?cmake

[root@wallet01?~]#?git?clone?https://github.com/maxbube/mydumper.git
Initialized?empty?Git?repository?in?/root/mydumper/.git/
error:??while?accessing?https://github.com/maxbube/mydumper.git/info/refs
fatal:?HTTP?request?failed

[root@wallet01?~]#?yum?update?-y?nss?curl?libcurl

[root@wallet01?~]#?git?clone?https://github.com/maxbube/mydumper.git
Initialized?empty?Git?repository?in?/root/mydumper/.git/
remote:?Enumerating?objects:?1185,?done.
remote:?Total?1185?(delta?0),?reused?0?(delta?0),?pack-reused?1185
Receiving?objects:?100%?(1185/1185),?983.01?KiB?|?573?KiB/s,?done.
Resolving?deltas:?100%?(724/724),?done.

[root@wallet01?~]#?cd?mydumper
[root@wallet01?mydumper]#?cmake?.
[root@wallet01?mydumper]#?make
[root@wallet01?mydumper]#?make?install

[root@wallet01?~]#?mydumper?--help

??-B,?--database??????????????需要備份的數(shù)據(jù)庫，不指定則備份全部庫
??-T,?--tables-list???????????需要備份的表，多表用逗號隔開
??-O,?--omit-from-file????????File?containing?a?list?of?database.table?entries?to?skip,?one?per?line?(skips?before?applying?regex?option)
??-o,?--outputdir?????????????備份目錄
??-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??????????????壓縮備份文件
??-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
??-N,?--insert-ignore?????????Dump?rows?with?INSERT?IGNORE
??-m,?--no-schemas????????????不備份表結(jié)構(gòu)，僅備份表數(shù)據(jù)
??-d,?--no-data???????????????不備份表數(shù)據(jù)，僅備份表結(jié)構(gòu)
??-G,?--triggers??????????????備份與表關(guān)聯(lián)的觸發(fā)器
??-E,?--events????????????????備份數(shù)據(jù)庫的事件
??-R,?--routines??????????????備份數(shù)據(jù)庫的存儲過程和函數(shù)
??-W,?--no-views??????????????不備份數(shù)據(jù)庫的視圖
??-k,?--no-locks??????????????Do?not?execute?the?temporary?shared?read?lock.??WARNING:?This?will?cause?inconsistent?backups
??--no-backup-locks???????????Do?not?use?Percona?backup?locks
??--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??????????????
??--use-savepoints????????????Use?savepoints?to?reduce?metadata?locking?issues,?needs?SUPER?privilege
??--lock-all-tables???????????Use?LOCK?TABLE?for?all,?instead?of?FTWRL
??--trx-consistency-only??????Transactional?consistency?only
??--complete-insert???????????Use?complete?INSERT?statements?that?include?column?names
??-h,?--host??????????????????The?host?to?connect?to
??-u,?--user??????????????????Username?with?the?necessary?privileges
??-p,?--password??????????????User?password
??-a,?--ask-password??????????Prompt?For?User?password
??-P,?--port??????????????????TCP/IP?port?to?connect?to
??-S,?--socket????????????????UNIX?domain?socket?file?to?use?for?connection
??-t,?--threads???????????????備份使用的線程數(shù)量，默認(rèn)是?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

備份全部庫：除了information_schema與performance_schema之外的庫都會被備份
[mysql@wallet01?~]$?mydumper?-u?root?-p?abcd.1234?-o?/home/mysql/backup

備份指定的庫?
[mysql@wallet01?~]$?mydumper?-u?root?-p?abcd.1234?-B?tpcc100?-o?/home/mysql/backup
[mysql@wallet01?~]$?cd?backup
[mysql@wallet01?backup]$?ls?-lh
total?732M
-rw-rw-r--?1?mysql?mysql??141?Feb??1?09:08?metadata
-rw-rw-r--?1?mysql?mysql?1.4K?Feb??1?09:07?tpcc100.customer-schema.sql
-rw-rw-r--?1?mysql?mysql?172M?Feb??1?09:08?tpcc100.customer.sql
-rw-rw-r--?1?mysql?mysql??804?Feb??1?09:07?tpcc100.district-schema.sql
-rw-rw-r--?1?mysql?mysql??11K?Feb??1?09:07?tpcc100.district.sql
-rw-rw-r--?1?mysql?mysql??481?Feb??1?09:07?tpcc100.history-schema.sql
-rw-rw-r--?1?mysql?mysql??19M?Feb??1?09:07?tpcc100.history.sql
-rw-rw-r--?1?mysql?mysql??431?Feb??1?09:07?tpcc100.item-schema.sql
-rw-rw-r--?1?mysql?mysql?7.9M?Feb??1?09:07?tpcc100.item.sql
-rw-rw-r--?1?mysql?mysql??304?Feb??1?09:07?tpcc100.new_order-schema.sql
-rw-rw-r--?1?mysql?mysql?1.1M?Feb??1?09:07?tpcc100.new_order.sql
-rw-rw-r--?1?mysql?mysql??615?Feb??1?09:07?tpcc100.order_line-schema.sql
-rw-rw-r--?1?mysql?mysql?204M?Feb??1?09:08?tpcc100.order_line.sql
-rw-rw-r--?1?mysql?mysql??525?Feb??1?09:07?tpcc100.orders-schema.sql
-rw-rw-r--?1?mysql?mysql??14M?Feb??1?09:07?tpcc100.orders.sql
-rw-rw-r--?1?mysql?mysql???66?Feb??1?09:07?tpcc100-schema-create.sql
-rw-rw-r--?1?mysql?mysql?1.2K?Feb??1?09:07?tpcc100.stock-schema.sql
-rw-rw-r--?1?mysql?mysql?316M?Feb??1?09:08?tpcc100.stock.sql
-rw-rw-r--?1?mysql?mysql??730?Feb??1?09:07?tpcc100.warehouse-schema.sql
-rw-rw-r--?1?mysql?mysql?1.2K?Feb??1?09:07?tpcc100.warehouse.sql

備份指定的表
[mysql@wallet01?~]$?mydumper?-u?root?-p?abcd.1234?-B?tpcc100?-T?customer?-o?/home/mysql/backup
[mysql@wallet01?~]$?cd?backup
[mysql@wallet01?backup]$?ls?-lh
total?172M
-rw-rw-r--?1?mysql?mysql??141?Feb??1?09:20?metadata
-rw-rw-r--?1?mysql?mysql?1.4K?Feb??1?09:20?tpcc100.customer-schema.sql
-rw-rw-r--?1?mysql?mysql?172M?Feb??1?09:20?tpcc100.customer.sql
-rw-rw-r--?1?mysql?mysql???66?Feb??1?09:20?tpcc100-schema-create.sql

備份多表
[mysql@wallet01?~]$?mydumper?-u?root?-p?abcd.1234?-B?tpcc100?-T?customer,orders?-o?/home/mysql/backup
[mysql@wallet01?~]$?cd?backup
[mysql@wallet01?backup]$?ls?-lh
total?186M
-rw-rw-r--?1?mysql?mysql??141?Feb??1?09:24?metadata
-rw-rw-r--?1?mysql?mysql?1.4K?Feb??1?09:24?tpcc100.customer-schema.sql
-rw-rw-r--?1?mysql?mysql?172M?Feb??1?09:24?tpcc100.customer.sql
-rw-rw-r--?1?mysql?mysql??525?Feb??1?09:24?tpcc100.orders-schema.sql
-rw-rw-r--?1?mysql?mysql??14M?Feb??1?09:24?tpcc100.orders.sql
-rw-rw-r--?1?mysql?mysql???66?Feb??1?09:24?tpcc100-schema-create.sql

僅備份表結(jié)構(gòu)
[mysql@wallet01?~]$?mydumper?-u?root?-p?abcd.1234?-B?tpcc100?-T?customer?-d?-o?/home/mysql/backup
[mysql@wallet01?~]$?cd?backup
[mysql@wallet01?backup]$?ls?-lh
total?12K
-rw-rw-r--?1?mysql?mysql??141?Feb??1?09:25?metadata
-rw-rw-r--?1?mysql?mysql?1.4K?Feb??1?09:25?tpcc100.customer-schema.sql
-rw-rw-r--?1?mysql?mysql???66?Feb??1?09:25?tpcc100-schema-create.sql

僅備份表數(shù)據(jù)
[mysql@wallet01?~]$?mydumper?-u?root?-p?abcd.1234?-B?tpcc100?-T?customer?-m?-o?/home/mysql/backup
[mysql@wallet01?~]$?cd?backup
[mysql@wallet01?backup]$?ls?-lh
total?172M
-rw-rw-r--?1?mysql?mysql??141?Feb??1?09:27?metadata
-rw-rw-r--?1?mysql?mysql?172M?Feb??1?09:27?tpcc100.customer.sql

[root@wallet01?~]#?myloader?--help

??-d,?--directory???????????????????備份目錄
??-q,?--queries-per-transaction?????Number?of?queries?per?transaction,?default?1000
??-o,?--overwrite-tables????????????如果表已經(jīng)存在則刪除
??-B,?--database????????????????????還原到另一個數(shù)據(jù)庫，例如備份A庫，還原為B庫
??-s,?--source-db???????????????????需要還原的數(shù)據(jù)庫（備份文件中）
??-e,?--enable-binlog???????????????Enable?binary?logging?of?the?restore?data
??-h,?--host????????????????????????The?host?to?connect?to
??-u,?--user????????????????????????Username?with?the?necessary?privileges
??-p,?--password????????????????????User?password
??-a,?--ask-password????????????????Prompt?For?User?password
??-P,?--port????????????????????????TCP/IP?port?to?connect?to
??-S,?--socket??????????????????????UNIX?domain?socket?file?to?use?for?connection
??-t,?--threads?????????????????????還原使用的線程數(shù)量，默認(rèn)是?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
??
還原全部庫???????
[mysql@wallet01?~]$?myloader?-u?root?-p?abcd@1234?-o?-d?/home/mysql/backup?

還原指定的庫
[mysql@wallet01?~]$?myloader?-u?root?-p?abcd@1234?-o?-s?tpcc100?-d?/home/mysql/backup?

備份的tpcc100庫還原為tpcc200庫
[mysql@wallet01?~]$?myloader?-u?root?-p?abcd@1234?-o?-B?tpcc200?-s?tpcc100?-d?/home/mysql/backup

還原指定的表
[mysql@wallet01?~]$?mysql?-uroot?-pabcd@1234?tpcc100?<?/home/mysql/backup/tpcc100.customer-schema.sql

[mysql@wallet01?~]$?mysql?-uroot?-pabcd@1234?tpcc100?<?/home/mysql/backup/tpcc100.customer.sql

創(chuàng)新互聯(lián)建站提供高防服務(wù)器、云服務(wù)器、香港服務(wù)器、成都移動服務(wù)器托管等

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)scvps.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。

網(wǎng)站標(biāo)題：【MySQL】數(shù)據(jù)庫邏輯備份工具--mydumper-創(chuàng)新互聯(lián)
網(wǎng)頁URL：http://bm7419.com/article8/gesip.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、建站公司、網(wǎng)站設(shè)計(jì)公司、App設(shè)計(jì)、定制網(wǎng)站、網(wǎng)站維護(hù)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)