{"id":3080,"date":"2010-11-09T06:29:54","date_gmt":"2010-11-09T04:29:54","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3080"},"modified":"2010-11-09T06:29:54","modified_gmt":"2010-11-09T04:29:54","slug":"an-argument-for-using-mysqldump","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/an-argument-for-using-mysqldump","title":{"rendered":"An argument for using mysqldump"},"content":{"rendered":"

I fully agree with Morgan’s An argument for not using mysqldump<\/a>. This post does not come to contradict it, but rather shed a positive light on mysqldump<\/em>.<\/p>\n

I usually prefer an LVM snapshot based backup, or using XtraBackup. And, with databases as large as dozens of GB and above, I consider mysqldump<\/em> to be a poor alternative. Poor in runtime, poor in overhead while taking the backup.<\/p>\n

However once in a while I get to be reminded that mysqldump just works<\/em>.<\/p>\n

As a recent example, I had a server which was killed after an ALTER TABLE statement hanged forever (table already ALTERed, but old scheme never dropped). The old table data still hanged around the file system, but was not recognized by InnoDB. Trying out DISCARD TABLESPACE did not do the job, and eventually file was dropped.<\/p>\n

So far, reasonable. InnoDB would complain about some table it never recognized in the first place, but all would work. That is, until backup was concerned. With innobackup<\/em> or XtraBackup the restore would fail on some internal problem. LVM would work, but would only copy+paste the problem: innobackup<\/em> would never again be able to be used on this database.<\/p>\n

It turned out a 120GB<\/strong> InnoDB compressed data (roughly 250GB<\/strong> uncompressed) would dump in –single-transaction<\/strong> in a matter of 4<\/strong> hours and would restore in a matter of some 20<\/strong> hours. A whole lot more than the 3<\/strong> hours total it would take for an LVM backup for that database. But the data would load well; no missing tablespaces.<\/p>\n

I’ve had similar incidents in the past. Not to mention the issue of compressing shared tablespace file.<\/p>\n

There’s something about being able to say “I’m not sure how long this is going to take; maybe a day or two. But in the end, we will have problems P1, P2 & P3 resolved<\/em>“.<\/p>\n

I like the clean state<\/em> you get from a mysqldump<\/em> restore.<\/p>\n","protected":false},"excerpt":{"rendered":"

I fully agree with Morgan’s An argument for not using mysqldump. This post does not come to contradict it, but rather shed a positive light on mysqldump. I usually prefer an LVM snapshot based backup, or using XtraBackup. And, with databases as large as dozens of GB and above, I consider mysqldump to be a […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[7,9],"class_list":["post-3080","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-backup","tag-mysqldump"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-NG","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3080","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=3080"}],"version-history":[{"count":6,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3080\/revisions"}],"predecessor-version":[{"id":3086,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3080\/revisions\/3086"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3080"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3080"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3080"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}