{"id":5246,"date":"2020-03-17T09:13:00","date_gmt":"2020-03-17T01:13:00","guid":{"rendered":"https:\/\/www.ldhost.cn\/jc\/?p=5246"},"modified":"2020-03-15T17:21:21","modified_gmt":"2020-03-15T09:21:21","slug":"%e5%a6%82%e4%bd%95%e5%af%b9mysql%e6%95%b0%e6%8d%ae%e5%ba%93%e4%b8%ad%e7%9a%84innodb%e8%bf%9b%e8%a1%8c%e6%95%85%e9%9a%9c%e6%8e%92%e9%99%a4","status":"publish","type":"post","link":"https:\/\/www.ldhost.cn\/jc\/ot\/5246.html","title":{"rendered":"\u5982\u4f55\u5bf9MySQL\u6570\u636e\u5e93\u4e2d\u7684InnoDB\u8fdb\u884c\u6545\u969c\u6392\u9664"},"content":{"rendered":"\n

\u95ee\u9898
\u5728\u670d\u52a1\u5668<\/a>\u4e2d\u6267\u884cMySQL\u67e5\u8be2\u5931\u8d25\uff1a\u6587\u4ef6\u4e2d\u7684\u9519\u8bef\u4fe1\u606f\uff1a\u201c .\/ psa \/ misc.frm\u201d
\u5f53\u8fd0\u884cmysqldump\u548cmysqlcheck\u65f6\uff0c\u5c06\u663e\u793a\u4e00\u6761\u6709\u5173\u4e0d\u5b58\u5728\u7684\u8868\u7684\u6d88\u606f\uff08\u4e3a\u8fdb\u884c\u9a8c\u8bc1\uff0c\u8bf7\u4f7f\u7528MySQL\u7ba1\u7406\u5458\u5e10\u6237\uff09\uff1a
mysqlcheck -uadmin -p db_example
db_example.BackupTasks
error : Can’t find file: ‘BackupTasks.MYD’ (errno: 2)
\u7528SELECT\u8bed\u53e5\u67e5\u8be2\u8868\u662f\u4e0d\u53ef\u80fd\u7684\uff1a

mysql> select * from db_example.misc;
ERROR 1033 (HY000): Incorrect information in file: ‘.\/db_example\/misc.frm’
\u65e0\u6cd5\u8fd8\u539f\u8be5\u8868\uff0c\u56e0\u4e3aInnoDB\u5185\u6838\u4e0d\u652f\u6301\u6062\u590d\u3002

mysql> repair table misc;
+————————-+——–+———-+———————————————————+
| Table | Op | Msg_type | Msg_text |
+————————-+——–+———-+———————————————————+
| psa.APSApplicationItems | repair | note | The storage engine for the table doesn’t support repair |
+————————-+——–+———-+———————————————————+
\u539f\u56e0
InnoDB\u7684\u635f\u574f\u901a\u5e38\u4e0e\u786c\u4ef6\u6545\u969c\u6709\u5173\u3002\u7531\u4e8e\u7535\u6e90\u6545\u969c\u6216\u5185\u5b58\u635f\u574f\uff0c\u5c06\u4fdd\u5b58\u635f\u574f\u7684\u9875\u9762\u3002\u5982\u679c\u5c06InnoDB\u6570\u636e\u5e93\u5b58\u50a8\u5728\u7f51\u7edc\u9644\u52a0\u5b58\u50a8\uff08NAS\uff09\u4e0a\uff0c\u4e5f\u53ef\u80fd\u4f1a\u51fa\u73b0\u6b64\u95ee\u9898\u3002<\/p>\n\n\n\n

\u89e3\u51b3\u65b9\u6848
\n\u6709\u51e0\u79cd\u8fd8\u539fMySQL\u7684\u65b9\u6cd5\uff1a<\/p>\n\n\n\n

\u4e00\u3001\u5f3a\u5236InnoDB\u6062\u590d
\u505c\u6b62mysqld\u5e76\u5907\u4efd\/ var \/ lib \/ mysql \/\u6587\u4ef6\u5939\u4e2d\u7684\u6240\u6709\u6587\u4ef6\uff1a
\/etc\/init.d\/mysqld stop
mkdir \/root\/mysql_backup
cp -r \/var\/lib\/mysql\/* \/root\/mysql_backup\/
\u5c06innodb_force_recovery\u9009\u9879\u6dfb\u52a0\u5230\/etc\/my.cnf\u4e2d\u7684[mysqld]\u90e8\u5206\u3002\u8be5\u9009\u9879\u5c06\u5141\u8bb8\u60a8\u542f\u52a8mysqld\u5e76\u8f6c\u50a8\u6570\u636e\u5e93\u3002
\/etc\/my.cnf<\/p>\n\n\n\n

[mysqld]<\/p>\n\n\n\n

\ninnodb_force_recovery = 4
\n\u6ce8\u610f \u60a8\u53ef\u4ee5\u5c06\u6b64\u9009\u9879\u589e\u52a0\u52305\u62166-\u76f4\u5230\u83b7\u5f97\u6700\u4f73\u8f6c\u50a8\u3002\n<\/p>\n\n\n\n

\u542f\u52a8mysqld\u670d\u52a1\uff1a
\/etc\/init.d\/mysqld start
\u8f6c\u50a8\u6240\u6709\u6570\u636e\u5e93\uff1a
mysqldump -uadmin -p -A > \/root\/dumpall.sql
\u5982\u679c\u521b\u5efa\u8f6c\u50a8\u65f6\u53d1\u751f\u4ee5\u4e0b\u9519\u8bef
\uff1a\u4f7f\u7528LOCK TABLES\u65f6\u6587\u4ef6\u201c xxxxxxxx.frm\u201d\u4e2d\u7684\u4fe1\u606f\u4e0d\u6b63\u786e\u00bb<\/p>\n\n\n\n

\u589e\u52a0innodb_force_recovery\uff0c\u7136\u540e\u91cd\u8bd5\u3002\u5982\u679c\u65e0\u6cd5\u521b\u5efa\u6570\u636e\u5e93\u8f6c\u50a8\uff0c\u8bf7\u5c1d\u8bd5\u4f7f\u7528\u65b9\u6cd5II\uff08\u590d\u5236\u8868\u7684\u5185\u5bb9\uff09\u6216\u65b9\u6cd5III\uff08\u4ece\u5907\u4efd\u8fd8\u539f\uff09\u3002<\/p>\n\n\n\n

\u505c\u6b62mysqld\u5e76\u5220\u9664\u635f\u574f\u7684\u6570\u636e\uff1a
\/etc\/init.d\/mysqld stop
rm -rf \/var\/lib\/mysql\/*
\u4ece\/etc\/my.cnf\u6587\u4ef6\u4e2d\u5220\u9664innodb_force_recovery\u9009\u9879\uff0c\u7136\u540e\u8fd0\u884cmysqld\uff1a
\/etc\/init.d\/mysqld start
\u7ed3\u679c\uff0c\u5c06\u8fd8\u539f\u4e3bmysql\u6570\u636e\u5e93\u548cInnoDB\u6570\u636e\u5e93\u5f15\u64ce\u3002
\u4ece\u8f6c\u50a8\u8fd8\u539f\u6570\u636e\u5e93\uff1a
mysql -uadmin -p > dumpall.sql<\/p>\n\n\n\n


\u4e8c\u3001\u590d\u5236\u8868\u683c\u5185\u5bb9
\u505c\u6b62mysqld\u5e76\u5907\u4efd\/ var \/ lib \/ mysql \/\u6587\u4ef6\u5939\u4e2d\u7684\u6240\u6709\u6587\u4ef6\uff1a
\/etc\/init.d\/mysqld stop
mkdir \/root\/mysql_backup
cp -r \/var\/lib\/mysql\/* \/root\/mysql_backup\/
\u5c06innodb_force_recovery\u9009\u9879\u6dfb\u52a0\u5230\/etc\/my.cnf\u4e2d\u7684[mysqld]\u90e8\u5206\u3002\u8be5\u9009\u9879\u5c06\u5141\u8bb8\u60a8\u542f\u52a8mysqld\u5e76\u8f6c\u50a8\u6570\u636e\u5e93\u3002
\/etc\/my.cnf<\/p>\n\n\n\n

[mysqld]<\/p>\n\n\n\n

innodb_force_recovery = 1
\u5c1d\u8bd5\u521b\u5efa\u526f\u672c\uff1a
CREATE TABLE <\u65b0\u8868> LIKE <\u635f\u574f\u8868>;
INSERT INTO < \u65b0\u8868 > SELECT * FROM < \u635f\u574f\u8868 >;
\u5982\u679c\u53ef\u884c\uff0c\u8bf7\u5220\u9664\u635f\u574f\u7684\u8868\u5e76\u4e3a\u5176\u91cd\u65b0\u547d\u540d\u3002
DROP TABLE < \u635f\u574f\u8868 >;
RENAME TABLE <\u65b0\u8868> TO < \u635f\u574f\u8868 >;<\/p>\n\n\n\n


\u4e09\uff0cInnoDB\u8868\u6062\u590d
\u5982\u679c\u53d1\u751f\u4ee5\u4e0b\u9519\u8bef\uff0c\u5219\u9700\u8981InnoDB\u8868\u6062\u590d<\/p>\n\n\n\n

mysql> USE\u6570\u636e\u5e93\u540d;
\nmysql> SELECT * FROM table1;
\n\u9519\u8bef1146\uff0842S02\uff09\uff1a\u8868’databasename.table1’\u4e0d\u5b58\u5728
\nmysql>
\n\u6216\u8005\u5c1d\u8bd5\u901a\u8fc7mysqldump\u8f6c\u50a8<\/p>\n\n\n\n

$ mysqldump -uroot -p\u6570\u636e\u5e93\u540d> databasename.sql
\u8f93\u5165\u5bc6\u7801\uff1a
mysqldump\uff1a\u5f97\u5230\u9519\u8bef\uff1a1146\uff1a\u4f7f\u7528\u9501\u5b9a\u8868\u65f6\uff0c\u8868’databasename.table1’\u4e0d\u5b58\u5728
[red @ hellsrv\u301c]
\u8b66\u544a\u5728\u5f00\u59cb\u4efb\u4f55\u64cd\u4f5c\u4e4b\u524d\uff0c\u6211\u4eec\u5efa\u8bae\u60a8\u5907\u4efd\u6570\u636e\u5e93\u6587\u4ef6\u3002
\u901a\u8fc7mysqldump\u521b\u5efa\u5907\u4efd\u5c06\u5931\u8d25\uff08\u7531\u4e8e\u9519\u8bef\uff09\u3002\u60a8\u5c06\u9700\u8981\u5728\u6587\u4ef6\u7cfb\u7edf\u7ea7\u522b\u590d\u5236\u57fa\u672c\u6587\u4ef6\uff1a <\/p>\n\n\n\n

\u670d\u52a1mysqld\u505c\u6b62
\ncp -R \/ var \/ lib \/ mysql \/\u6570\u636e\u5e93\u540d\/ home \/\u7528\u6237\u540d\/\u5907\u4efd
\n\u4e3a\u4e86\u6062\u590dInnoDB\u8868\uff0c\u6211\u4eec\u9700\u8981\u627e\u51fa\uff1a<\/p>\n\n\n\n

\u5b66\u4e60\u8868\u7ed3\u6784
\n\u6709\u6570\u636e\u6587\u4ef6\uff08\u610f\u5473\u7740\u6587\u4ef6\u7cfb\u7edf\u7ea7\u522b\u7684\u6587\u4ef6\uff09
\n\u6587\u4ef6\u7cfb\u7edf\u7ea7\u522b\u7684InnoDB\u8868\u5305\u542b\u4e24\u4e2a\u6587\u4ef6\uff1a<\/p>\n\n\n\n

.frm\u6587\u4ef6\u5305\u542b\u8868\u7ed3\u6784\uff1b
\n.ibd\u6587\u4ef6\u5b9e\u9645\u6570\u636e
\n\u6062\u590d\u8ba1\u5212\uff1a<\/p>\n\n\n\n

\u627e\u51fa\u635f\u574f\u8868\u7684\u7ed3\u6784\uff1b
\u5efa\u7acb\u65b0\u7684\u8868\uff1b
\u5728\u65b0\u6570\u636e\u5e93\u4e2d\u521b\u5efa\u6240\u9700\u7ed3\u6784\u7684\u8868\uff1b
\u5c06\u6570\u636e\u4ece\u65e7\u8868\u590d\u5236\u5230\u65b0\u8868\uff1b
\u5982\u679c\u6570\u636e\u5df2\u635f\u574f\uff0c\u60a8\u53ef\u4ee5\u5c1d\u8bd5\u4f7f\u7528innochecksum\u5b9e\u7528\u7a0b\u5e8f\u5c06\u5176\u8fd8\u539f
\u6211\u4eec\u4f7f\u7528\u8868\u7ed3\u6784\u8bfb\u53d6\u5b9e\u7528\u7a0b\u5e8f\uff1a<\/p>\n\n\n\n

mysqlfrm-\u8bca\u65ad\u88681.frm
\n\u521b\u5efa\u8868table1<\/code>\uff08
\n id<\/code> int\uff0810\uff09unsigned NOT NULL\u6ce8\u91ca’ID’\uff0c
\n title<\/code> varchar\uff08128\uff09NOT NULL\u6ce8\u91ca’Title’\uff0c
\n\u4e3b\u952ePRIMARY<\/code>\uff08id<\/code>\uff09
\n\uff09ENGINE = InnoDB;
\n\u8fd8\u5efa\u8bae\u627e\u51fa\u65e7\u5e93\u7684\u7f16\u7801\uff1a<\/p>\n\n\n\n

mysql>\u4eceINFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME =’databasename’\u4e2d\u9009\u62e9DEFAULT_CHARACTER_SET_NAME\uff0cDEFAULT_COLLATION_NAME<\/p>\n\n\n\n