一次数据表损坏的修复过程

发表于 | 分类于 | 阅读次数:

一、数据库表损坏

现象

mysql数据库非正常停止，导致所有数据库（除自身数据库信息）信息表名正常显示，但打开表提示：表不存在，数据库数据存放路径信息显示占用大小正常，证明数据没有丢失，数据库启动报错如下信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2019-01-25 13:46:19 13621 [Note] Server socket created on IP: '::'.

2019-01-25 13:46:19 13621 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.ht

ml for how you can resolve the problem.

2019-01-25 13:46:19 13621 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.

2019-01-25 13:46:19 13621 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.ht

ml for how you can resolve the problem.

2019-01-25 13:46:19 13621 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting

.html for how you can resolve the problem.

2019-01-25 13:46:19 13621 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.

2019-01-25 13:46:19 13621 [Note] Event Scheduler: Loaded 0 events

2019-01-25 13:46:19 13621 [Note] /usr/local/mysql/bin/mysqld: ready for connections.

Version: '5.6.29-log'  socket: '/usr/local/mysql/mysqld.sock'  port: 3306  Source distribution

2019-01-25 13:47:23 13621 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown

上述日志获取到： InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. 尽管表“slave_worker_info”已经存在，但是获取不到信息。而且报大量类似表的错误信息

解决

根据错误提示及尝试打开表操作证明数据没有丢失，只是表丢失而已，把表删除，新建表结构，重新挂在数据节点即可：

  1. 备份原始表数据（例如：这里操作auth_user表）

    1
    2
    3
    4
    5
    ls dataDir

      auth_user.frm

      auth_user.ibd

  2. 删除表

    1
    DROP TABLE IF EXISTS `auth_user

  3. 删除ibd文件

    1
    rm -rf  push_user_question_record.ibd

  4. 重新创建表

    1
    CREATE TABLE `auth_user

  5. 丢弃新建表的表空间

    1
    ALTER TABLE auth_user DISCARD TABLESPACE;

  6. 复制备份的数据信息

    1
    \cp /BACKUPDIR/auth_user.* MYSQLDATA/DATABASES/

  7. 授权

    1
    chown -R mysql.mysql base_area.*

  8. 挂载数据

    1
    ALTER TABLE auth_user IMPORT TABLESPACE;

二、用户添加索引权限

mysql给用户添加索引权限：

1
grant index on *.* to username@'%'

注意：

这里只是给用户赋予了添加和删除索引的权限，如果想要修改的话，用户\<username>还需要有alter权限，否则会报如下错误：

1
1142 - ALTER command denied to user 'xxxx'@'xx.xx.xx.xx' tor table 'TABLE_NAME'

添加alter权限：

1
grant alter on  xx.xx to username@'xx'

三、停止执行过长的sql

1
2
3
4
mysql> show processlist;

此时可以看到MySQL中正在运行的进程：
mysql> kill [Id NAME]