{"id":3819,"date":"2025-07-06T14:17:50","date_gmt":"2025-07-06T06:17:50","guid":{"rendered":"https:\/\/www.laixuexila.com\/?p=3819"},"modified":"2025-07-06T14:17:50","modified_gmt":"2025-07-06T06:17:50","slug":"%e9%92%88%e5%af%b9-innodb-%e8%a1%a8%e6%8d%9f%e5%9d%8f%e5%9c%ba%e6%99%af%e7%9a%84%e8%87%aa%e5%8a%a8%e5%af%bc%e5%87%ba%e8%84%9a%e6%9c%ac%e6%96%b9%e6%a1%88","status":"publish","type":"post","link":"https:\/\/www.laixuexila.com\/index.php\/2025\/07\/06\/%e9%92%88%e5%af%b9-innodb-%e8%a1%a8%e6%8d%9f%e5%9d%8f%e5%9c%ba%e6%99%af%e7%9a%84%e8%87%aa%e5%8a%a8%e5%af%bc%e5%87%ba%e8%84%9a%e6%9c%ac%e6%96%b9%e6%a1%88\/","title":{"rendered":"\u9488\u5bf9 InnoDB \u8868\u635f\u574f\u573a\u666f\u7684\u81ea\u52a8\u5bfc\u51fa\u811a\u672c\u65b9\u6848"},"content":{"rendered":"\n<p>\u4e0b\u9762\u662f\u4e00\u4e2a<strong>\u9488\u5bf9 InnoDB \u8868\u635f\u574f\u573a\u666f\u7684\u81ea\u52a8\u5bfc\u51fa\u811a\u672c\u65b9\u6848<\/strong>\uff0c\u7ed3\u5408 <code>innodb_force_recovery<\/code> \u53c2\u6570\uff0c\u652f\u6301\u81ea\u52a8\u6302\u8d77 MySQL \u670d\u52a1\u3001\u8bbe\u7f6e\u6062\u590d\u6a21\u5f0f\u3001\u5bfc\u51fa\u6240\u6709\u53ef\u8bfb\u53d6\u8868\u7684\u6570\u636e\uff0c\u518d\u6e05\u7406\u6062\u590d\u914d\u7f6e\uff0c\u5b89\u5168\u6062\u590d\u6570\u636e\u3002<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u4f7f\u7528\u573a\u666f<\/h2>\n\n\n\n<p>\u9002\u7528\u4e8e\u4ee5\u4e0b\u60c5\u51b5\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>MySQL \u65e0\u6cd5\u6b63\u5e38\u542f\u52a8\uff0c\u63d0\u793a InnoDB \u8868\u7a7a\u95f4\u635f\u574f<\/li>\n\n\n\n<li>\u67d0\u4e9b\u8868\u62a5\u9519\uff1a<code>InnoDB: corruption<\/code>, <code>page checksum mismatch<\/code><\/li>\n\n\n\n<li>\u65e0\u6cd5\u8fdb\u5165\u6570\u636e\u76ee\u5f55\u6b63\u5e38\u64cd\u4f5c\uff0c\u4f46\u5e0c\u671b<strong>\u5c3d\u53ef\u80fd\u5bfc\u51fa\u6570\u636e\u800c\u4e0d\u662f\u76f4\u63a5\u5220\u5e93<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u4e00\u3001\u539f\u7406\u8bf4\u660e<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>innodb_force_recovery<\/code> \u662f InnoDB \u63d0\u4f9b\u7684\u201c\u5e94\u6025\u542f\u52a8\u6a21\u5f0f\u201d<\/li>\n\n\n\n<li>\u8303\u56f4\u4ece <code>1~6<\/code>\uff0c\u6570\u5b57\u8d8a\u5927\uff0c\u8d8a\u5371\u9669\uff08\u4f1a\u8df3\u8fc7\u66f4\u591a\u6062\u590d\u903b\u8f91\uff09<\/li>\n\n\n\n<li><strong>\u53ef\u7528\u6027\u539f\u5219\uff1a\u5c3d\u91cf\u4ece\u4f4e\u7ea7\u522b\uff081\uff09\u5c1d\u8bd5\uff0c\u9010\u6b65\u63d0\u9ad8<\/strong><\/li>\n<\/ul>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>\ud83d\udea8 <strong>\u5f3a\u63d0\u9192<\/strong>\uff1a<code>&gt;= 4<\/code> \u65f6\u4f1a\u7981\u6b62\u5199\u64cd\u4f5c\uff0c\u53ea\u80fd\u7528\u4e8e\u5bfc\u51fa\uff0c\u4e0d\u80fd\u7528\u4e8e\u7ebf\u4e0a\u5199\u5165\uff01<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u4e8c\u3001\u81ea\u52a8\u5bfc\u51fa\u811a\u672c\uff08\u63a8\u8350\u7528 bash\uff09<\/h2>\n\n\n\n<p>\u811a\u672c\uff1a<code>innodb_auto_dump.sh<\/code><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>#!\/bin\/bash\n\n# ==== \u914d\u7f6e\u9879 ====\nMYSQL_CNF=\"\/etc\/my.cnf\"\nBACKUP_DIR=\"\/data\/innodb_force_dump\/$(date +%F_%H%M)\"\nMYSQL_USER=\"root\"\nMYSQL_PASS=\"your_password\"\n\n# ==== Step 1. \u505c\u6b62 MySQL ====\necho \"&#91;INFO] \u505c\u6b62 MySQL \u670d\u52a1...\"\nsystemctl stop mysqld\n\n# ==== Step 2. \u8bbe\u7f6e\u6062\u590d\u6a21\u5f0f ====\necho \"&#91;INFO] \u8bbe\u7f6e innodb_force_recovery = 3...\"\nsed -i '\/^\\&#91;mysqld\\]\/a innodb_force_recovery=3' $MYSQL_CNF\n\n# ==== Step 3. \u542f\u52a8 MySQL ====\necho \"&#91;INFO] \u542f\u52a8 MySQL \u670d\u52a1 (\u5e94\u6025\u6a21\u5f0f)...\"\nsystemctl start mysqld\nsleep 5\n\n# \u68c0\u67e5\u662f\u5426\u542f\u52a8\u6210\u529f\nif ! mysqladmin ping -u$MYSQL_USER -p$MYSQL_PASS &gt;\/dev\/null 2&gt;&amp;1; then\n  echo \"&#91;ERROR] MySQL \u542f\u52a8\u5931\u8d25\uff0c\u8bf7\u5c1d\u8bd5\u66f4\u9ad8\u7b49\u7ea7\u7684 recovery\u3002\"\n  exit 1\nfi\n\n# ==== Step 4. \u81ea\u52a8\u5bfc\u51fa\u6240\u6709\u6570\u636e\u5e93 ====\nmkdir -p \"$BACKUP_DIR\"\necho \"&#91;INFO] \u5bfc\u51fa\u6240\u6709\u6570\u636e\u5e93\u6570\u636e\u81f3 $BACKUP_DIR ...\"\n\n# \u83b7\u53d6\u7528\u6237\u6570\u636e\u5e93\uff08\u6392\u9664\u7cfb\u7edf\u5e93\uff09\nDBS=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -Nse \\\n    \"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','performance_schema','information_schema','sys');\")\n\nfor DB in $DBS; do\n  echo \"&#91;INFO] \u5bfc\u51fa\u6570\u636e\u5e93\uff1a$DB\"\n  mkdir -p \"$BACKUP_DIR\/$DB\"\n  mysqldump -u$MYSQL_USER -p$MYSQL_PASS \\\n    --single-transaction --skip-lock-tables --quick \\\n    $DB &gt; \"$BACKUP_DIR\/$DB.sql\"\n\n  if &#91;&#91; $? -ne 0 ]]; then\n    echo \"&#91;WARN] \u5bfc\u51fa\u5931\u8d25\uff1a$DB\" &gt;&gt; \"$BACKUP_DIR\/errors.log\"\n  fi\ndone\n\n# ==== Step 5. \u6062\u590d\u6b63\u5e38\u6a21\u5f0f ====\necho \"&#91;INFO] \u79fb\u9664\u6062\u590d\u6a21\u5f0f\uff0c\u51c6\u5907\u91cd\u5efa\u6570\u636e\u5e93...\"\nsed -i '\/innodb_force_recovery\/d' $MYSQL_CNF\n\nsystemctl stop mysqld\nsleep 5\nsystemctl start mysqld\n\n# ==== Step 6. \u5b8c\u6210\u63d0\u793a ====\necho \"&#91;OK] InnoDB \u5e94\u6025\u5bfc\u51fa\u5b8c\u6210\uff0c\u5907\u4efd\u4f4d\u7f6e\uff1a$BACKUP_DIR\"\necho \"\u8bf7\u68c0\u67e5 backup \u4e2d\u7684 .sql \u6587\u4ef6\u8fdb\u884c\u540e\u7eed\u6062\u590d\u64cd\u4f5c\u3002\"<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u4e09\u3001\u6062\u590d\u5df2\u5bfc\u51fa\u6570\u636e<\/h2>\n\n\n\n<p>\u6062\u590d\u8fc7\u7a0b\u5efa\u8bae\u5728<strong>\u6e05\u7a7a\u6216\u65b0\u6570\u636e\u5e93<\/strong>\u4e0a\u6267\u884c\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -u root -p &lt; \/data\/innodb_force_dump\/2025-07-06_1130\/mydb.sql<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u56db\u3001\u5efa\u8bae\u7684\u6062\u590d\u6d41\u7a0b\u56fe<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>\u53d1\u73b0\u635f\u574f\n   \u2193\n\u4fee\u6539 my.cnf\uff1ainnodb_force_recovery=1\n   \u2193\n\u82e5\u65e0\u6cd5\u542f\u52a8 \u2192 \u63d0\u9ad8\u81f3 3\u30014...\n   \u2193\n\u542f\u52a8\u6210\u529f \u2192 \u5bfc\u51fa\u6570\u636e\uff08\u63a8\u8350 mysqldump\uff09\n   \u2193\n\u79fb\u9664\u6062\u590d\u914d\u7f6e\uff0c\u91cd\u542f MySQL\n   \u2193\n\u65b0\u5efa\u6570\u636e\u5e93 \u2192 \u5bfc\u5165\u6570\u636e<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u4e94\u3001\u5173\u4e8e <code>innodb_force_recovery<\/code> \u53c2\u6570\u8be6\u89e3<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>\u7b49\u7ea7<\/th><th>\u884c\u4e3a<\/th><th>\u98ce\u9669<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td>\u8df3\u8fc7 Insert Buffer \u6062\u590d<\/td><td>\u5b89\u5168<\/td><\/tr><tr><td>2<\/td><td>\u8df3\u8fc7 Redo \u65e5\u5fd7\u5e94\u7528<\/td><td>\u5b89\u5168<\/td><\/tr><tr><td>3<\/td><td>\u5ffd\u7565 Undo \u65e5\u5fd7\uff08\u53ef\u8bfb\u810f\u6570\u636e\uff09<\/td><td>\u53ef\u63a5\u53d7<\/td><\/tr><tr><td>4<\/td><td>\u8df3\u8fc7 Purge \u64cd\u4f5c<\/td><td>\u4f1a\u6709\u5783\u573e\u6570\u636e\u6b8b\u7559<\/td><\/tr><tr><td>5<\/td><td>\u8df3\u8fc7\u540e\u53f0\u7ebf\u7a0b\u542f\u52a8<\/td><td>\u4e25\u91cd\u4e0d\u4e00\u81f4\u98ce\u9669<\/td><\/tr><tr><td>6<\/td><td>\u7981\u7528\u6240\u6709\u6062\u590d\uff0c\u5141\u8bb8\u8bfb\u8868<\/td><td>\u6700\u540e\u624b\u6bb5\uff0c\u53ef\u80fd\u7834\u574f\u6570\u636e\u7ed3\u6784<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u516d\u3001\u5b98\u65b9\u4e0e\u63a8\u8350\u53c2\u8003<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/forcing-innodb-recovery.html\">MySQL \u5b98\u65b9\u6587\u6863\uff1aInnoDB Recovery<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.percona.com\/blog\/2018\/12\/12\/recover-corrupt-innodb-tables\/\">Percona \u5173\u4e8e InnoDB \u635f\u574f\u7684\u5b9e\u6218\u6307\u5357<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.percona.com\/blog\/innodb_corruption_recovery\/\">innodb_force_recovery \u53c2\u6570\u5206\u6790<\/a><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u4e03\u3001\u98ce\u9669\u63d0\u9192\u4e0e\u5efa\u8bae<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>\u9879\u76ee<\/th><th>\u5efa\u8bae<\/th><\/tr><\/thead><tbody><tr><td>\u6b63\u5f0f\u73af\u5883\u4e2d\u64cd\u4f5c\u524d\u8bf7\u5907\u4efd\u6570\u636e\u76ee\u5f55<\/td><td><code>tar -czf \/backup\/mysql_data.tgz \/var\/lib\/mysql<\/code><\/td><\/tr><tr><td>\u5bfc\u51fa\u8fc7\u7a0b\u5efa\u8bae\u6267\u884c\u540e\u624b\u52a8\u6821\u9a8c\u6bcf\u4e2a SQL \u6587\u4ef6<\/td><td>\u68c0\u67e5\u662f\u5426\u7f3a\u8868\u6216\u4e3a\u7a7a<\/td><\/tr><tr><td>\u5982\u4f7f\u7528\u7cfb\u7edf\u670d\u52a1\uff0c\u8bf7\u7528 systemctl \u7ba1\u7406 mysqld<\/td><td>\u907f\u514d kill -9<\/td><\/tr><tr><td>\u5bfc\u51fa\u5931\u8d25\u53ef\u4ee5\u914d\u5408 <code>mysqlpump<\/code> \u6216 <code>mydumper<\/code> \u91cd\u8bd5<\/td><td>\u589e\u52a0\u7a33\u5b9a\u6027<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p>\u66f4\u591a\u8be6\u7ec6\u5185\u5bb9\u8bf7\u5173\u6ce8\u5176\u4ed6\u76f8\u5173\u6587\u7ae0\uff01<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4e0b\u9762\u662f\u4e00\u4e2a\u9488\u5bf9 InnoDB \u8868\u635f\u574f\u573a\u666f\u7684\u81ea\u52a8\u5bfc\u51fa\u811a\u672c\u65b9\u6848\uff0c\u7ed3\u5408 innodb_force_recovery  [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":3820,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35],"tags":[],"class_list":["post-3819","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts\/3819","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/comments?post=3819"}],"version-history":[{"count":1,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts\/3819\/revisions"}],"predecessor-version":[{"id":3821,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts\/3819\/revisions\/3821"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/media\/3820"}],"wp:attachment":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/media?parent=3819"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/categories?post=3819"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/tags?post=3819"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}