{"id":1644,"date":"2010-01-05T22:51:35","date_gmt":"2010-01-06T03:51:35","guid":{"rendered":"http:\/\/g33kinfo.com\/info\/?p=1644"},"modified":"2010-01-05T22:51:35","modified_gmt":"2010-01-06T03:51:35","slug":"mydumper-fast-mysql-backup-tool","status":"publish","type":"post","link":"https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/","title":{"rendered":"MyDumper – Fast MySql Backup Tool"},"content":{"rendered":"

From mituzas.lt
\nhttp:\/\/mituzas.lt\/2009\/02\/03\/mydumper\/<\/a><\/p>\n

“Last weekend I ended up working on small pet project \u2013 and today I\u2019m kind of releasing it.<\/p>\n

So, I had that idea that there\u2019s no good tool to do logical dump of MySQL data for large sites \u2013 mysqldump doesn\u2019t provide too much of I\/O pressure, mk-parallel-dump is closer, but it doesn\u2019t do consistent snapshots, uses same mysqldump, as well as is written in Perl (haha!), and\u2026 I just wanted something new to hack, as proof of concept. For a while to use it one had to edit constants in code, but my colleague Mark contributed options support and it doesn\u2019t need recompiles anymore to run it \ud83d\ude42<\/p>\n

So, let me introduce\u00a0mydumper<\/a>. It doesn\u2019t dump table definitions, all it does is extracting data and writing it to files, fast.<\/p>\n

I took ~20GB-sized French Wikipedia core database (SHOW TABLE STATUS<\/a>), and tried dumping it with three different methods \u2013 mysqldump, mk-parallel-dump and mydumper (used 32 thread, chunked backup setting for last two).<\/p>\n

Dump times, smaller is better:<\/p>\n

mysqldump: 75m18s\nmaatkit:    8m13s\nmydumper:   6m44s \\o\/ WINNER \\o\/<\/pre>\n
There\u2019s no cache skew \u2013 I restarted mysqld before every test, and it is using O_DIRECT.<\/p>\n
At certain moments it seemed like gigabit network wasn\u2019t enough for the test\u2026 It seems, it was using underlying I\/O properly too:<\/p>\n
avg-cpu:  %user   %nice %system %iowait  %steal   %idle\n          12.58    0.00    3.28   48.14    0.00   36.00\n\nDevice:         rrqm\/s   wrqm\/s     r\/s     w\/s   rsec\/s   wsec\/s\nsda              19.00    26.00 3077.00    6.00 151720.00   306.00    \n\n   avgrq-sz avgqu-sz   await  svctm  %util\n      49.31    32.09   10.27   0.32 100.00<\/pre>\n
Though, once I tried from warm caches, and saw 2m rows read a second, I had a warm fuzzy feeling \ud83d\ude42<\/p>\n
Apparently the trick of having successful fast mysql dump was applying lots of pressure to underlying storage as well as using multi-processor capabilities. So we do, so can you!<\/p>\n
Oh, and easiest way to start is:<\/p>\n
bzr co lp:mydumper\/0.1\ncd mydumper\nmake\n.\/mydumper --help<\/pre>\n
Alternatively, one can use \u2018lp:mydumper\u2019 to get trunk \u2013 though various things (like startup options) can change. Feel free to file bugs, ask questions, and contribute with anything you think is worth contributing (thats why it ended up on Launchpad).<\/p>\n
FAQs page<\/a> can have answers to questions that might arise too \ud83d\ude42<\/p>\n
Update: Added also downloadable archive for bzr impaired at the\u00a0downloads<\/a> page.”<\/p>\n
This is not my work. Pulled from the http:\/\/mituzas.lt\/2009\/02\/03\/mydumper\/<\/a> site. But it was such a good tool, had to mention it here.<\/p>\n","protected":false},"excerpt":{"rendered":"
From mituzas.lt http:\/\/mituzas.lt\/2009\/02\/03\/mydumper\/ “Last weekend I ended up working on small pet project \u2013 and today I\u2019m kind of releasing it. So, I had that idea that there\u2019s no good tool to do logical dump of MySQL data for large sites \u2013 mysqldump doesn\u2019t provide too much of I\/O pressure, mk-parallel-dump is closer, but it… <\/p>\n
Read More<\/i><\/a><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-1644","post","type-post","status-publish","format-standard","hentry","category-info"],"yoast_head":"\nMyDumper - Fast MySql Backup Tool - Linux Shtuff<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MyDumper - Fast MySql Backup Tool - Linux Shtuff\" \/>\n<meta property=\"og:description\" content=\"From mituzas.lt http:\/\/mituzas.lt\/2009\/02\/03\/mydumper\/ “Last weekend I ended up working on small pet project \u2013 and today I\u2019m kind of releasing it. So, I had that idea that there\u2019s no good tool to do logical dump of MySQL data for large sites \u2013 mysqldump doesn\u2019t provide too much of I\/O pressure, mk-parallel-dump is closer, but it... Read More\" \/>\n<meta property=\"og:url\" content=\"https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/\" \/>\n<meta property=\"og:site_name\" content=\"Linux Shtuff\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/fb.me\/g33kinf0\" \/>\n<meta property=\"article:author\" content=\"https:\/\/fb.me\/g33kinf0\" \/>\n<meta property=\"article:published_time\" content=\"2010-01-06T03:51:35+00:00\" \/>\n<meta name=\"author\" content=\"g33kadmin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/drsinger1111\" \/>\n<meta name=\"twitter:site\" content=\"@drsinger1111\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/mydumper-fast-mysql-backup-tool\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/mydumper-fast-mysql-backup-tool\\\/\"},\"author\":{\"name\":\"g33kadmin\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\"},\"headline\":\"MyDumper – Fast MySql Backup Tool\",\"datePublished\":\"2010-01-06T03:51:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/mydumper-fast-mysql-backup-tool\\\/\"},\"wordCount\":368,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\"},\"articleSection\":[\"General Info\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/g33kinfo.com\\\/info\\\/mydumper-fast-mysql-backup-tool\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/mydumper-fast-mysql-backup-tool\\\/\",\"url\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/mydumper-fast-mysql-backup-tool\\\/\",\"name\":\"MyDumper - Fast MySql Backup Tool - Linux Shtuff\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#website\"},\"datePublished\":\"2010-01-06T03:51:35+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/mydumper-fast-mysql-backup-tool\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/g33kinfo.com\\\/info\\\/mydumper-fast-mysql-backup-tool\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/mydumper-fast-mysql-backup-tool\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MyDumper – Fast MySql Backup Tool\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#website\",\"url\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/\",\"name\":\"Linux Shtuff\",\"description\":\"Because I have CRS Syndrome...\",\"publisher\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\",\"name\":\"g33kadmin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\",\"url\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\",\"contentUrl\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\",\"width\":512,\"height\":512,\"caption\":\"g33kadmin\"},\"logo\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\"},\"description\":\"I am a g33k, Linux blogger, developer, student and Tech Writer for Liquidweb.com\\\/kb. My passion for all things tech drives my hunt for all the coolz. I often need a vacation after I get back from vacation....\",\"sameAs\":[\"https:\\\/\\\/thelinuxreport.com\",\"https:\\\/\\\/fb.me\\\/g33kinf0\",\"https:\\\/\\\/x.com\\\/https:\\\/\\\/twitter.com\\\/drsinger1111\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MyDumper - Fast MySql Backup Tool - Linux Shtuff","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/","og_locale":"en_US","og_type":"article","og_title":"MyDumper - Fast MySql Backup Tool - Linux Shtuff","og_description":"From mituzas.lt http:\/\/mituzas.lt\/2009\/02\/03\/mydumper\/ “Last weekend I ended up working on small pet project \u2013 and today I\u2019m kind of releasing it. So, I had that idea that there\u2019s no good tool to do logical dump of MySQL data for large sites \u2013 mysqldump doesn\u2019t provide too much of I\/O pressure, mk-parallel-dump is closer, but it... Read More","og_url":"https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/","og_site_name":"Linux Shtuff","article_publisher":"https:\/\/fb.me\/g33kinf0","article_author":"https:\/\/fb.me\/g33kinf0","article_published_time":"2010-01-06T03:51:35+00:00","author":"g33kadmin","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/drsinger1111","twitter_site":"@drsinger1111","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/#article","isPartOf":{"@id":"https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/"},"author":{"name":"g33kadmin","@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547"},"headline":"MyDumper – Fast MySql Backup Tool","datePublished":"2010-01-06T03:51:35+00:00","mainEntityOfPage":{"@id":"https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/"},"wordCount":368,"commentCount":0,"publisher":{"@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547"},"articleSection":["General Info"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/","url":"https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/","name":"MyDumper - Fast MySql Backup Tool - Linux Shtuff","isPartOf":{"@id":"https:\/\/g33kinfo.com\/info\/#website"},"datePublished":"2010-01-06T03:51:35+00:00","breadcrumb":{"@id":"https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/g33kinfo.com\/info\/mydumper-fast-mysql-backup-tool\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/g33kinfo.com\/info\/"},{"@type":"ListItem","position":2,"name":"MyDumper – Fast MySql Backup Tool"}]},{"@type":"WebSite","@id":"https:\/\/g33kinfo.com\/info\/#website","url":"https:\/\/g33kinfo.com\/info\/","name":"Linux Shtuff","description":"Because I have CRS Syndrome...","publisher":{"@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/g33kinfo.com\/info\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547","name":"g33kadmin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif","url":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif","contentUrl":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif","width":512,"height":512,"caption":"g33kadmin"},"logo":{"@id":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif"},"description":"I am a g33k, Linux blogger, developer, student and Tech Writer for Liquidweb.com\/kb. My passion for all things tech drives my hunt for all the coolz. I often need a vacation after I get back from vacation....","sameAs":["https:\/\/thelinuxreport.com","https:\/\/fb.me\/g33kinf0","https:\/\/x.com\/https:\/\/twitter.com\/drsinger1111"]}]}},"_links":{"self":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/posts\/1644","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/comments?post=1644"}],"version-history":[{"count":0,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/posts\/1644\/revisions"}],"wp:attachment":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/media?parent=1644"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/categories?post=1644"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/tags?post=1644"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}