mysql Innodb – Table ‘user’ is marked as crashed and should be repaired
I recently came across a Cpanel server (CentOS 5) upon which mysqld refused to start after /var was at 100%. After tailing the mysql error log in the default /var/lib/mysql/HOSTNAME.err it was no surprise to find that the mysql user table had been marked as crashed. On RHEL/CentOS servers, you cannot simply add the “innodb_force_recovery = 1″ (or whatever recovery level…2,3,4,5,6) to the /etc/my.cnf and do the regular service mysql start. You’ll have to edit the my.cnf to enable recovery and start mysql from the command line and not by the init script/service command. Only once you have mysql started on the command line can you run your repair on the mysql user table. Here is quick run-down with commands and queries to run on your Cpanel server;
[root@HOSTNAME mysql]# tail -f /var/lib/mysql/HOSTNAME.err 110108 10:37:45 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 1 3749263016. InnoDB: Doing recovery: scanned up to log sequence number 1 3749263050 InnoDB: Last MySQL binlog file position 0 79, file name ./host89-bin.000005 110108 10:37:45 InnoDB: Flushing modified pages from the buffer pool... 110108 10:37:45 InnoDB: Started; log sequence number 1 3749263050 InnoDB: !!! innodb_force_recovery is set to 1 !!! 110108 10:37:45 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'user' is marked as crashed and should be repaired 110108 10:37:45 mysqld ended
Now, go ahead and enable innodb forced recovery by opening your /etc/my.cnf in your favorite text editor and make sure you have something like this:
[mysqld] innodb_force_recovery = 1
Start mysql from the command line after enabling innodb forced recovery:
[root@HOSTNAME ~]#/usr/sbin/mysqld --skip-grant-tables --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/HOSTNAME.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
Finally, it’s time to get back in to mysql and get your life back… or mysql user table at least!
[root@HOSTNAME ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 173 to server version: 4.1.22-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use mysql; mysql> check table user; +------------+-------+----------+----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------+-------+----------+----------------------------------------------------------+ | mysql.user | check | warning | Table is marked as crashed | | mysql.user | check | warning | 6 clients are using or haven't closed the table properly | | mysql.user | check | error | Record at pos: 24992 is not remove-marked | | mysql.user | check | error | record delete-link-chain corrupted | | mysql.user | check | error | Corrupt | +------------+-------+----------+----------------------------------------------------------+ 5 rows in set (0.02 sec) mysql> repair table user; +------------+--------+----------+------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------+--------+----------+------------------------------------------+ | mysql.user | repair | warning | Number of rows changed from 1384 to 1385 | | mysql.user | repair | status | OK | +------------+--------+----------+------------------------------------------+ 2 rows in set (0.48 sec) mysql> check table user; +------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+-------+----------+----------+ | mysql.user | check | status | OK | +------------+-------+----------+----------+ 1 row in set (0.01 sec) mysql>exit
Now, don’t forget to REMOVE the innodb_force_recovery line from your my.cnf you added earlier! After that, just start mysql as you normally would. For more database information and management hop over to here.
[root@HOSTNAME mysql]# service mysql start