澳门在线威尼斯官方 > 电脑数据库 > 威尼斯澳门在线一次非法关机导致mysql数据表损坏

原标题:威尼斯澳门在线一次非法关机导致mysql数据表损坏

浏览次数:73 时间:2019-10-13

You should execute mysql_upgrade each time you upgrade MySQL.

  • recovering (with keycache) MyISAM-table 'db.MYI' 
    Data records: 0 
    Data records: 2

 

9、执行下面的命令修复./mysql/db表:
[root@localhost mysql]# myisamchk -of db.MYI 

13、打开网页,已经可以正常访问了,说明MySQL数据库的数据表修复成功。

mysql> grant all on house.* to test@'192.168.%' identified by 'test1249';

排查修复数据表的经过:

 

**/usr/local/mysql/libexec/mysqld: Table './mysql/plugin' is marked as crashed and last (automatic?) repair failed 
/usr/local/mysql/libexec/mysqld: Table 'plugin' is marked as crashed and last (automatic?) repair failed 
110726 17:27:13 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 
110726 17:27:13 [ERROR] /usr/local/mysql/libexec/mysqld: Table './mysql/db' is marked as crashed and last (automatic?) repair failed 
110726 17:27:13 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/db' is marked as crashed and last (automatic?) repair failed 
110726 17:27:13 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended

参考资料:

1、访问网页,提示错误,连接不到数据库。

 

6、又发现./mysql/user表损坏。
[root@localhost mysql]# myisamchk -of user.MYI 

 

11、查看服务是否在运行。
[root@localhost ~]# netstat -anp | grep mysqld 
tcp0  0  
0.0.0.0:3306 
0.0.0.0:*   LISTEN
4360/mysqld  
unix  2  [ ACC ] STREAM LISTENING 14172
4360/mysqld /tmp/mysql.sock
这时发现服务已运行。

ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 45, found

cd /var/lib/mysql/mysql 
myisamchk -of host.MYI 

If mysql_upgrade finds that a table has a possible incompatibility, it performs a table check and, if problems are found, attempts a table repair. If the table cannot be repaired, see Section 2.11.3, “Rebuilding or Repairing Tables or Indexes” for manual table repair strategies.

  • recovering (with keycache) MyISAM-table 'user.MYI' 
    Data records: 6

 

**7、表修复成功,但是还是启动不了服务,继续看错误日志。

 

**(昨天安装postfix好像yum安装了mysql),用netstat -anp |grep mysqld 命令查看mysql服务没有起来,用mysql -uroot -p也连接不到服务器。

#  mysql_upgrade -u root -p

Enter password: 

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

Checking system database.

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.db_database_info                             OK

mysql.db_instance_info                             OK

mysql.db_server_info                               OK

mysql.engine_cost                                  OK

......................................................

......................................................

Upgrade process completed successfully.

Checking if update is needed.

**2、启动mysql服务,卸载和关闭rpm安装的mysql服务

因为备份的数据库版本为MySQL 5.6.20,目标数据库的MySQL版本为5.7.21,因为两个数据库版本不一致,在还原过后,忘记升级数据库(还原后没有使用mysql_upgrade升级数据结构)。其实从错误信息也能看出一二,需要执行mysql_upgrade命令会检查所有数据库中的所有表与当前版本的MySQL服务器是否不兼容。 mysql_upgrade还会升级系统表,以便你可以使用一些新特性或功能。

4、修复数据库表

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:威尼斯澳门在线一次非法关机导致mysql数据表损坏

关键词:

上一篇:基于keepalived搭建MySQL的高可用集群

下一篇:没有了