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
Thank you for your post. we are repair the mysql successfull.
art
Thailand
You are very welcome. I’m glad to have helped you out.
Unpleasant problem. Solved on Windows using this:
— (1) starting server workaround
mysqld –console –skip-grant-tables –skip-external-locking
— (2) fixing problem
mysqlcheck –repair mysql user
— (3) stop server and start it normally
Thankyou, you are save my time and money…