mysql之表修复

原创

一念为云 2021-12-13 16:11:15 博主文章分类：运维 ©著作权

文章标签 mysql 修复 文章分类 运维

©著作权归作者所有：来自51CTO博客作者一念为云的原创作品，请联系作者获取转载授权，否则将追究法律责任

针对MYISAM存储引擎

基本方法：

1，repair

登录mysql客户端，使用内置命令repair修复

比如：

repair table t01;
+----------+--------+----------+----------+

| Table | Op   | Msg_type | Msg_text |
| ----- | ---- | -------- | -------- |
|       |      |          |          |

+----------+--------+----------+----------+

| test.t01 | repair | status | OK   |
| -------- | ------ | ------ | ---- |
|          |        |        |      |

+----------+--------+----------+----------+
1 row in set (0.00 sec)

如果表为INNODB引擎

repair table t01;
+----------+--------+----------+---------------------------------------------------------+
| Table    | Op     | Msg_type | Msg_text                                                |
+----------+--------+----------+---------------------------------------------------------+
| test.t01 | repair | note     | The storage engine for the table doesn't support repair |
+----------+--------+----------+---------------------------------------------------------+
1 row in set (0.00 sec)

支持选项：

QUIK：用在表没有被修改的情况下，速度最快

EXTENDED：尝试恢复每行数据，会产生垃圾数据行，非必要不使用

USE_FRM:在.myi文件受损或者丢失的情况下，利用.frm的定义重建索引

比如，如果t01.MYI文件丢失

check table t01;
+----------+-------+----------+---------------------------------------------------------------+
| Table    | Op    | Msg_type | Msg_text                                                      |
+----------+-------+----------+---------------------------------------------------------------+
| test.t01 | check | Error    | Can't find file: 't01' (errno: 2 - No such file or directory) |
| test.t01 | check | status   | Operation failed                                              |
+----------+-------+----------+---------------------------------------------------------------+
2 rows in set (0.00 sec)

尝试修复：

repair table t01;
+----------+--------+----------+---------------------------------------------------------------+
| Table    | Op     | Msg_type | Msg_text                                                      |
+----------+--------+----------+---------------------------------------------------------------+
| test.t01 | repair | Error    | Can't find file: 't01' (errno: 2 - No such file or directory) |
| test.t01 | repair | status   | Operation failed                                              |
+----------+--------+----------+---------------------------------------------------------------+
2 rows in set (0.00 sec)

使用USE_FRM选项再次尝试修复：

repair table t01 USE_FRM;
+----------+--------+----------+------------------------------------+
| Table    | Op     | Msg_type | Msg_text                           |
+----------+--------+----------+------------------------------------+
| test.t01 | repair | warning  | Number of rows changed from 0 to 4 |
| test.t01 | repair | status   | OK                                 |
+----------+--------+----------+------------------------------------+
2 rows in set (0.00 sec)

再次检查表：

mysql> check table t01;
+----------+-------+----------+----------+
| Table    | Op    | Msg_type | Msg_text |
+----------+-------+----------+----------+
| test.t01 | check | status   | OK       |
+----------+-------+----------+----------+
1 row in set (0.00 sec)

2，myqlcheck

在.MYI损坏或者丢失的情况下

mysqlcheck test -u root -p
Enter password: 
test.t01
Error    : Can't find file: 't01' (errno: 2 - No such file or directory)
status   : Operation failed

加上--use_frm参数

mysqlcheck -r test --use-frm -u root -p
Enter password: 
test.t01
warning  : Number of rows changed from 0 to 4
status   : OK

3，myisamchk

root@d9265735e7fd:/var/lib/mysql/test# myisamchk t01
Checking MyISAM file: t01
Data records:       4   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links

root@d9265735e7fd:/var/lib/mysql/test# myisamchk -r t01

- recovering (with sort) MyISAM-table 't01'
  Data records: 4
- Fixing index 1

选项：

-B/--backup:Make a backup of the .MYD file as 'filename-time.BAK'
--correct-checksum:Correct checksum information for table.
-e/--extend-check:尝试恢复每个数据行，会产生垃圾数据，非必要不用
-f/--force:覆盖掉同名.TMD文件
-k/--keys-used=#：加快速度
-o/--safe-recover：使用-r修复不了时，使用，会比较慢

针对innodb存储引擎

基本步骤：

1，修改配置文件，增加配置项

默认配置如下：

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log

# By default we only accept connections from localhost

#bind-address   = 127.0.0.1

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

增加一条

innodb_force_recovery = 1

2，转储表

重启mysql，转储表（如果不能转储，可尝试加大innodb_force_recovery，该值小于4被认为是安全的，否则会造成数据表的永久损坏）

mysql> select * into outfile '/var/lib/mysql-files/test.t01.sql'  from test.t01;
Query OK, 4 rows affected (0.00 sec)

转储文件需带上secure_file_priv变量所指定的路径，否则会无法转储

mysql> select * into outfile '/root/test.t01.sql'  from test.t01;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

mysql> show variables like '%secure_file_priv%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

3，创建新表结构

create table t02 as select * from t01 where 1=2;

4，还原配置文件

将之前配置文件的添加项删除，或者注释，然后重启mysql

5，导入转储数据

mysql> load data infile '/var/lib/mysql-files/test.t01.sql' into table t02;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0