{"id":8180,"date":"2019-08-29T10:01:17","date_gmt":"2019-08-29T10:01:17","guid":{"rendered":"https:\/\/www.milesweb.in\/hosting-faqs\/?p=8180"},"modified":"2022-02-22T17:15:49","modified_gmt":"2022-02-22T11:45:49","slug":"ideas-to-fix-corrupted-tables-in-mysql","status":"publish","type":"post","link":"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/","title":{"rendered":"Ideas To Fix Corrupted Tables in MySQL"},"content":{"rendered":"<p>MySQL tables can sometimes get corrupted. It means that an error can occur and the data held within them can become unreadable. So, the attempts to read from a corrupted table will generally result into the server crashing.<\/p>\n<p><strong>Below are common causes of corrupted tables:<\/strong><\/p>\n<ul>\n<li>The MySQL server stops in between of a write.<\/li>\n<li>Similar table that is modified by the server is modified by an external program.<\/li>\n<li>The machine gets shut down without giving any warning.<\/li>\n<li>The computer hardware fails.<\/li>\n<li>The MySQL code consists of a software bug.<\/li>\n<\/ul>\n<p>In case you detect that one of your tables has been corrupted, take a backup of your data directory prior to troubleshooting or attempting to fix the table. This will help to reduce the risk of data loss.<\/p>\n<p>First, stop the MySQL service:<\/p>\n<pre class=\"lang:default decode:true \">$ sudo systemctl stop mysql<\/pre>\n<p>After this, copy your entire data into a new backup directory. The data directory is <span class=\"code-block-sml\">\/var\/lib\/mysql\/<\/span> is the default one on Ubuntu systems:<\/p>\n<pre class=\"lang:default decode:true \">$ cp -r \/var\/lib\/mysql \/var\/lib\/mysql_bkp<\/pre>\n<p>Once the backup is taken, you can begin investigating if the table is in fact corrupted. In case the table uses the <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/myisam-storage-engine.html\" target=\"_blank\" rel=\"nofollow noopener\">MyISAM storage engine<\/a>, you can verify if it\u2019s corrupted by running a CHECK TABLE statement from the MySQL prompt:<\/p>\n<pre class=\"lang:default decode:true \">mysql&gt; CHECK TABLE table_name;<\/pre>\n<p>A message will get displayed after using the above command which will help you know whether or not it\u2019s corrupted. If the MyISAM table is actually corrupted, it can basically be repaired by issuing a <span class=\"code-block-sml\">REPAIR TABLE<\/span> statement:<\/p>\n<pre class=\"lang:default decode:true \">mysql&gt; REPAIR TABLE table_name;<\/pre>\n<p>If you assume that the corrupted table was repaired successfully, you will get the below output:<\/p>\n<pre class=\"lang:default decode:true \">Output\n+--------------------------+--------+----------+----------+\n| Table | Op | Msg_type | Msg_text |\n+--------------------------+--------+----------+----------+\n| database_name.table_name | repair | status | OK |\n+--------------------------+--------+----------+----------+<\/pre>\n<p>If the table corruption isn\u2019t resolved yet, you can check the alternative methods for repairing the corrupted tables in the MySQL documentation.<\/p>\n<p>Alternatively, if the corrupted table uses the InnoDB storage engine, then it will have a different process for repairing. InnoDB is the default storage engine in MySQL since the version 5.5, and it offers automated corruption checking and repair operations. InnoDB finds corrupted pages by performing checksums on every page it reads, and if there is a checksum discrepancy it will automatically stop the MySQL server.<\/p>\n<p>You rarely need to repair InnoDB tables, as InnoDB offers a crash recovery process that can resolve most issues when the server restarts. But, if you face a situation where you need to rebuild a corrupted InnoDB table, use the \u201cDump and Reload\u201d method recommended by the MySQL documentation. This includes regaining access to the corrupted table by using the mysqldump utility for creating a logical backup of the table. This will keep the table structure and the data within it, and then reload the table back into the database.<\/p>\n<p>Keeping this in mind, try to restart the MySQL service to see if it allows you access to the server:<\/p>\n<pre class=\"lang:default decode:true \">$ sudo systemctl restart mysql<\/pre>\n<p>In case the server remains crashed or otherwise inaccessible, then it is better to enable InnoDB\u2019s <span class=\"code-block-sml\">force_recovery<\/span> option. This can be done by editing the <span class=\"code-block-sml\">mysqld.cnf<\/span> file:<\/p>\n<pre class=\"lang:default decode:true \">$ sudo nano \/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/pre>\n<p>In the <span class=\"code-block-sml\">[mysqld]<\/span> section, add the following command:<\/p>\n<pre class=\"lang:default decode:true\">                          \/etc\/mysql\/mysql.conf.d\/mysqld.cnf\n. . .\n[mysqld]\n. . .\ninnodb_force_recovery=1<\/pre>\n<p>Now, save and close the file, and then try to restart the MySQL service again. If you are successfully able to access the corrupted table, use the <span class=\"code-block-sml\">mysqldump<\/span> utility to dump your table data to a new file. This file can be named as per your preference, but here it\u2019s named as <span class=\"code-block-sml\">out.sql<\/span>:<\/p>\n<pre class=\"lang:default decode:true \">$ mysqldump database_name table_name &gt; out.sql<\/pre>\n<p>Drop the table from the database. To avoid the need to reopen the MySQL prompt, use the following syntax:<\/p>\n<pre class=\"lang:default decode:true \">$ mysql -u user -p --execute=\"DROP TABLE database_name.table_name\"<\/pre>\n<p>After this, restore the table with the dump file you just created:<\/p>\n<pre class=\"lang:default decode:true \">$ mysql -u user -p &lt; out.sql<\/pre>\n<p>Remember that the InnoDB storage engine is generally highly fault-tolerant as compared to the older MyISAM engine. Tables using InnoDB can still be corrupted, but due to its auto-recovery features, the risk of table corruption and crashes gets much lowered.<\/p>\n<p><strong>Also Read:<\/strong><\/p>\n<p><a href=\"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-create-mysql-tables-in-phpmyadmin\/\"><strong>How To Create MySQL Tables In phpMyAdmin?<\/strong><\/a><\/p>\n<p><a href=\"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-search-mysql-tables-in-phpmyadmin\/\"><strong>How to search MySQL tables in phpMyAdmin?<\/strong><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>MySQL tables can sometimes get corrupted. It means that an error can occur and the data held within them can become unreadable. So, the attempts to read from a corrupted table will generally result into the server crashing. Below are common causes of corrupted tables: The MySQL server stops in between of a write. Similar [&hellip;]<\/p>\n","protected":false},"author":16,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[230],"tags":[205,469,735],"class_list":["post-8180","post","type-post","status-publish","format-standard","placeholder-for-hentry","category-database","tag-mysql","tag-mysql-database","tag-mysql-tables"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Ideas To Fix Corrupted Tables in MySQL<\/title>\n<meta name=\"description\" content=\"The article described the ways to fix the corrupted tables in MySQL databse.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Ideas To Fix Corrupted Tables in MySQL\" \/>\n<meta property=\"og:description\" content=\"The article described the ways to fix the corrupted tables in MySQL databse.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/\" \/>\n<meta property=\"og:site_name\" content=\"Web Hosting FAQs by MilesWeb\" \/>\n<meta property=\"article:published_time\" content=\"2019-08-29T10:01:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-02-22T11:45:49+00:00\" \/>\n<meta name=\"author\" content=\"Pallavi Godse\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Pallavi Godse\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/\"},\"author\":{\"name\":\"Pallavi Godse\",\"@id\":\"https:\/\/www.milesweb.in\/hosting-faqs\/#\/schema\/person\/7e3952607fa9eb4e82fea9f7cad9c945\"},\"headline\":\"Ideas To Fix Corrupted Tables in MySQL\",\"datePublished\":\"2019-08-29T10:01:17+00:00\",\"dateModified\":\"2022-02-22T11:45:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/\"},\"wordCount\":639,\"commentCount\":0,\"keywords\":[\"MySQL\",\"MySQL database\",\"MySQL tables\"],\"articleSection\":[\"Database\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/\",\"url\":\"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/\",\"name\":\"Ideas To Fix Corrupted Tables in MySQL\",\"isPartOf\":{\"@id\":\"https:\/\/www.milesweb.in\/hosting-faqs\/#website\"},\"datePublished\":\"2019-08-29T10:01:17+00:00\",\"dateModified\":\"2022-02-22T11:45:49+00:00\",\"author\":{\"@id\":\"https:\/\/www.milesweb.in\/hosting-faqs\/#\/schema\/person\/7e3952607fa9eb4e82fea9f7cad9c945\"},\"description\":\"The article described the ways to fix the corrupted tables in MySQL databse.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.milesweb.in\/hosting-faqs\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Ideas To Fix Corrupted Tables in MySQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.milesweb.in\/hosting-faqs\/#website\",\"url\":\"https:\/\/www.milesweb.in\/hosting-faqs\/\",\"name\":\"Web Hosting FAQs by MilesWeb\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.milesweb.in\/hosting-faqs\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.milesweb.in\/hosting-faqs\/#\/schema\/person\/7e3952607fa9eb4e82fea9f7cad9c945\",\"name\":\"Pallavi Godse\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.milesweb.in\/hosting-faqs\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/3602d0ac1ab89332aea8205dcf474af4ff4eb319cfd584544038908cd5e4c966?s=96&d=blank&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/3602d0ac1ab89332aea8205dcf474af4ff4eb319cfd584544038908cd5e4c966?s=96&d=blank&r=g\",\"caption\":\"Pallavi Godse\"},\"description\":\"Pallavi is a Digital Marketing Executive at MilesWeb and has an experience of over 4 years in content development. She is interested in writing engaging content on business, technology, web hosting and other topics related to information technology.\",\"sameAs\":[\"https:\/\/www.milesweb.in\"],\"url\":\"https:\/\/www.milesweb.in\/hosting-faqs\/author\/pallavi\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Ideas To Fix Corrupted Tables in MySQL","description":"The article described the ways to fix the corrupted tables in MySQL databse.","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:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/","og_locale":"en_US","og_type":"article","og_title":"Ideas To Fix Corrupted Tables in MySQL","og_description":"The article described the ways to fix the corrupted tables in MySQL databse.","og_url":"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/","og_site_name":"Web Hosting FAQs by MilesWeb","article_published_time":"2019-08-29T10:01:17+00:00","article_modified_time":"2022-02-22T11:45:49+00:00","author":"Pallavi Godse","twitter_misc":{"Written by":"Pallavi Godse","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/#article","isPartOf":{"@id":"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/"},"author":{"name":"Pallavi Godse","@id":"https:\/\/www.milesweb.in\/hosting-faqs\/#\/schema\/person\/7e3952607fa9eb4e82fea9f7cad9c945"},"headline":"Ideas To Fix Corrupted Tables in MySQL","datePublished":"2019-08-29T10:01:17+00:00","dateModified":"2022-02-22T11:45:49+00:00","mainEntityOfPage":{"@id":"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/"},"wordCount":639,"commentCount":0,"keywords":["MySQL","MySQL database","MySQL tables"],"articleSection":["Database"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/","url":"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/","name":"Ideas To Fix Corrupted Tables in MySQL","isPartOf":{"@id":"https:\/\/www.milesweb.in\/hosting-faqs\/#website"},"datePublished":"2019-08-29T10:01:17+00:00","dateModified":"2022-02-22T11:45:49+00:00","author":{"@id":"https:\/\/www.milesweb.in\/hosting-faqs\/#\/schema\/person\/7e3952607fa9eb4e82fea9f7cad9c945"},"description":"The article described the ways to fix the corrupted tables in MySQL databse.","breadcrumb":{"@id":"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.milesweb.in\/hosting-faqs\/ideas-to-fix-corrupted-tables-in-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.milesweb.in\/hosting-faqs\/"},{"@type":"ListItem","position":2,"name":"Ideas To Fix Corrupted Tables in MySQL"}]},{"@type":"WebSite","@id":"https:\/\/www.milesweb.in\/hosting-faqs\/#website","url":"https:\/\/www.milesweb.in\/hosting-faqs\/","name":"Web Hosting FAQs by MilesWeb","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.milesweb.in\/hosting-faqs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.milesweb.in\/hosting-faqs\/#\/schema\/person\/7e3952607fa9eb4e82fea9f7cad9c945","name":"Pallavi Godse","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.milesweb.in\/hosting-faqs\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/3602d0ac1ab89332aea8205dcf474af4ff4eb319cfd584544038908cd5e4c966?s=96&d=blank&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3602d0ac1ab89332aea8205dcf474af4ff4eb319cfd584544038908cd5e4c966?s=96&d=blank&r=g","caption":"Pallavi Godse"},"description":"Pallavi is a Digital Marketing Executive at MilesWeb and has an experience of over 4 years in content development. She is interested in writing engaging content on business, technology, web hosting and other topics related to information technology.","sameAs":["https:\/\/www.milesweb.in"],"url":"https:\/\/www.milesweb.in\/hosting-faqs\/author\/pallavi\/"}]}},"_links":{"self":[{"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/posts\/8180","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/users\/16"}],"replies":[{"embeddable":true,"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/comments?post=8180"}],"version-history":[{"count":10,"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/posts\/8180\/revisions"}],"predecessor-version":[{"id":16583,"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/posts\/8180\/revisions\/16583"}],"wp:attachment":[{"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/media?parent=8180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/categories?post=8180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/tags?post=8180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}