{"id":6487,"date":"2013-08-23T13:25:15","date_gmt":"2013-08-23T11:25:15","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=6487"},"modified":"2013-09-29T15:44:19","modified_gmt":"2013-09-29T13:44:19","slug":"trick-recovering-from-no-space-left-on-device-issues-with-mysql","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/trick-recovering-from-no-space-left-on-device-issues-with-mysql","title":{"rendered":"Trick: recovering from &#8220;no space left on device&#8221; issues with MySQL"},"content":{"rendered":"<p>Just read Ronald Bradford&#8217;s post on an <a href=\"http:\/\/ronaldbradford.com\/blog\/unnecessary-3am-emergency-call-2013-08-23\/\">unnecessary 3am (emergency) call<\/a>. I sympathize! Running out of disk space makes for some weird MySQL behaviour, and in fact whenever I encounter weird behaviour I verify disk space.<\/p>\n<p>But here&#8217;s a trick I&#8217;ve been using for years to avoid such cases and to be able to recover quickly. It helped me on such events as running out of disk space during ALTER TABLEs or avoiding purging of binary logs when slave is known to be under maintenance.<\/p>\n<p>Ronald suggested it &#8212; just put a dummy file in your <strong>@@datadir<\/strong>! I like putting a <strong>1GB<\/strong> dummy file: I typically copy+paste a <strong>1GB<\/strong> binary log file and call it <strong>&#8220;placeholder.tmp&#8221;<\/strong>. Then I forget all about it. My disk space should not run out &#8212; if it does it&#8217;s a cause for emergency. I have monitoring, but sometimes I&#8217;m hoping to make an operation on <strong>97%<\/strong>&#8211;<strong>99%<\/strong> utilization.<\/p>\n<p>If I do run out of disk space: well, MySQL won&#8217;t let me connect; won&#8217;t complete an important statement; not sync transaction to disk &#8212; bad situation. Not a problem in our case: we can magically recover <strong>1GB<\/strong> worth of data from the <strong>@@datadir<\/strong>, buying us enough time (maybe just minutes) to gracefully complete so necessary operations; connect, KILL, shutdown, abort etc.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Just read Ronald Bradford&#8217;s post on an unnecessary 3am (emergency) call. I sympathize! Running out of disk space makes for some weird MySQL behaviour, and in fact whenever I encounter weird behaviour I verify disk space. But here&#8217;s a trick I&#8217;ve been using for years to avoid such cases and to be able to recover [&hellip;]<\/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":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[119,46],"class_list":["post-6487","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-linux","tag-monitoring"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1GD","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6487","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=6487"}],"version-history":[{"count":5,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6487\/revisions"}],"predecessor-version":[{"id":6596,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6487\/revisions\/6596"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=6487"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=6487"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=6487"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}