您好,登錄后才能下訂單哦!
這篇文章主要介紹了多線程備份工具mydumper怎么用,具有一定借鑒價值,感興趣的朋友可以參考下,希望大家閱讀完這篇文章之后大有收獲,下面讓小編帶著大家一起了解一下。
安裝
點(diǎn)擊(此處)折疊或打開
依賴安裝
debian
apt-get install libglib2.0-dev
apt-get install libmysqlclient-dev
apt-get install libghc-zlib-dev
apt-get install libpcre++-dev
centos
yum install glib2-devel mysql-devel zlib-devel pcre-devel
# wget https://launchpad.net/ubuntu/+archive/primary/+files/mydumper_0.9.1.orig.tar.gz
# tar xzvf mydumper_0.9.1.orig.tar.gz
# cd mydumper-0.9.1/
# cmake .
# make && make
說明
點(diǎn)擊(此處)折疊或打開
# mydumper --help
Usage:
mydumper [OPTION...] multi-threaded MySQL dumping
Help Options:
-?, --help Show help options
Application Options:
-B, --database (指定要導(dǎo)出的數(shù)據(jù)庫)
-T, --tables-list (指定要導(dǎo)出的表,多個表逗號分隔,不支持正則表達(dá)式)
-o, --outputdir (指定輸出目錄)
-s, --statement-size (導(dǎo)出的insert語句長度指定,單位字節(jié),默認(rèn)100wziji)
-r, --rows (即水平拆分表進(jìn)行存儲,單位行數(shù),這個選項會使--chunk-filesize失效)
-F, --chunk-filesize (即水平拆分表進(jìn)行存儲,單位 MB)
-c, --compress (壓縮輸出文件)
-e, --build-empty-files (空數(shù)據(jù)的表也生成文件)
-x, --regex (正則表達(dá)式 匹配'db.table')
-i, --ignore-engines (設(shè)定需要忽略的存儲引擎表,多個以逗號分隔)
-m, --no-schemas (不要導(dǎo)出schema庫中有數(shù)據(jù)的表)
-d, --no-data (僅僅導(dǎo)出表結(jié)構(gòu),不要數(shù)據(jù))
-G, --triggers (導(dǎo)出觸發(fā)器)
-E, --events (導(dǎo)出event)
-R, --routines (導(dǎo)出存儲過程)
-k, --no-locks (導(dǎo)出數(shù)據(jù)過程中不要臨時使用讀鎖). WARNING: This will cause inconsistent backups
--less-locking Minimize locking time on InnoDB tables.
-l, --long-query-guard (定義超過多久的查詢?yōu)閘ong querY ,默認(rèn)60,單位 s)
-K, --kill-long-queries (kill掉long query)(instead of aborting)
-D, --daemon (使用守護(hù)進(jìn)程的模式)
-I, --snapshot-interval (dump快照之間的間隔設(shè)置,需要帶上--daemon參數(shù)啟動,默認(rèn)60,單位minutes)
-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
--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 (事務(wù)一致)
-t, --threads Number of threads to use, default 4(備份線程數(shù),默認(rèn)為4)
-C, --compress-protocol Use compression on the MySQL connection(連接壓縮)
-v, --verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
默認(rèn)連接參數(shù)
-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
測試
點(diǎn)擊(此處)折疊或打開
mydumper -u root -p xxx -B db_slave -e -o db_slave_dir
目錄文件如下:
其中各文件說明
建庫文件 db_slave-schema-create.sql (通用格式dbname-schema-create.sql)
點(diǎn)擊(此處)折疊或打開
# cat db_slave-schema-create.sql
CREATE DATABASE `db_slave` /*!40100 DEFAULT CHARACTER SET latin1 */;
建表文件 db_slave.t_check_flag-schema.sql(通用格式dbname.tbname-schema.sql)
點(diǎn)擊(此處)折疊或打開
# cat db_slave.t_check_flag-schema.sql
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
CREATE TABLE `t_check_flag` (
`db_ip` char(16) NOT NULL DEFAULT '',
`check_flag` tinyint(4) DEFAULT '0' COMMENT '1 monitor 0 not '
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
數(shù)據(jù)文件 db_slave.t_check_flag.sql(通用格式dbname.tbname.sql)
點(diǎn)擊(此處)折疊或打開
# cat db_slave.t_check_flag.sql
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
/*!40103 SET TIME_ZONE='+00:00' */;
INSERT INTO `t_check_flag` VALUES
("192.168.111.129",1),
("192.168.111.130",1),
("192.168.111.26",1);
整體導(dǎo)出信息文件 metadata(這里記錄了開始時間,結(jié)束時間,以及導(dǎo)出開始時的binlog信息,對于建立從庫比較方便)
點(diǎn)擊(此處)折疊或打開
# cat metadata
Started dump at: 2016-11-19 13:38:56
SHOW MASTER STATUS:
Log: 2104.014976
Pos: 85965870
GTID:(null)
Finished dump at: 2016-11-19 13:38:56
過程描述
打開general_log可以看到如下日志
點(diǎn)擊(此處)折疊或打開
148901 Connect root@localhost on db_slave
148901 Query SET SESSION wait_timeout = 2147483
148901 Query SET SESSION net_write_timeout = 2147483
148901 Query SHOW PROCESSLIST
148901 Query FLUSH TABLES WITH READ LOCK
148901 Query SELECT @@tokudb_version
148901 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
148901 Query /*!40101 SET NAMES binary*/
148901 Query SHOW MASTER STATUS
148901 Query SELECT @@gtid_current_pos
148901 Query SELECT @@default_master_connection
148901 Query SHOW SLAVE STATUS
148902 Connect root@localhost on
148902 Query SET SESSION wait_timeout = 2147483
148902 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
148902 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
148902 Query /*!40103 SET TIME_ZONE='+00:00' */
148902 Query /*!40101 SET NAMES binary*/
148903 Connect root@localhost on
148903 Query SET SESSION wait_timeout = 2147483
148903 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
148903 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
148903 Query /*!40103 SET TIME_ZONE='+00:00' */
148903 Query /*!40101 SET NAMES binary*/
148904 Connect root@localhost on
148904 Query SET SESSION wait_timeout = 2147483
148904 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
148904 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
148904 Query /*!40103 SET TIME_ZONE='+00:00' */
148904 Query /*!40101 SET NAMES binary*/
148905 Connect root@localhost on
148905 Query SET SESSION wait_timeout = 2147483
148905 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
148905 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
148905 Query /*!40103 SET TIME_ZONE='+00:00' */
148905 Query /*!40101 SET NAMES binary*/
148901 Init DB db_slave
148901 Query SHOW TABLE STATUS
148901 Query SHOW CREATE DATABASE `db_slave`
148901 Query UNLOCK TABLES /* FTWRL */
148902 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `db_slave`.`t_check_flag`
148903 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `db_slave`.`t_master_instance`
148901 Quit
148904 Query SHOW CREATE TABLE `db_slave`.`t_check_flag`
148905 Query SHOW CREATE TABLE `db_slave`.`t_master_instance`
①連接數(shù)據(jù)庫
②判斷長查詢相關(guān),--long-query-guard和--kill-long-queries
③flush tables with read locks ,備份mysiam表完成會立即unlock tables; 對于innodb表,mydumper·使用single transaction的方式進(jìn)行備份。因此mysiam表的大小及數(shù)量影響著mysiam的備份時間進(jìn)而影響著業(yè)務(wù)是否只讀。
④通過 -t 參數(shù)指定的線程數(shù)量進(jìn)行創(chuàng)建,并進(jìn)行工作worker子線程
⑤確定當(dāng)前要導(dǎo)出的表,并把待導(dǎo)出表加入到隊列中。
⑥備份完成退出(其中mysiam完成會unlock tables;)
總結(jié):
①可以使用 -t 參數(shù)指定線程數(shù),多線程同時備份速度可以提升,但是也依賴于磁盤的IO性能
②導(dǎo)出過程要加讀鎖,建議在業(yè)務(wù)低峰期進(jìn)行。
③在從庫備份的時候,metadata中也是記錄當(dāng)前從庫已執(zhí)行到的binlog文件以及位置
④定時任務(wù)備份可使用 -v 參數(shù)輸出相應(yīng)等級的信息,作為備份日志。
⑤當(dāng)庫過于龐大的時候,建議使用 -c 參數(shù)對結(jié)果進(jìn)行壓縮。
⑥單表過于龐大的時候,可以使用 -r 或者 -F 參數(shù)分解,避免單表備份時間過長(不拆的話就是 one thread 進(jìn)行導(dǎo)出)
感謝你能夠認(rèn)真閱讀完這篇文章,希望小編分享的“多線程備份工具mydumper怎么用”這篇文章對大家有幫助,同時也希望大家多多支持億速云,關(guān)注億速云行業(yè)資訊頻道,更多相關(guān)知識等著你來學(xué)習(xí)!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。