Sometime ago I noticed my WordPress sites wont load. I found the problem with MySQL.
The server I am running on is a VPS server with:
- Debian 8.1
- MariaDB - Ver 15.1 Distrib 10.1.14-MariaDB
- 2048 MB RAM
- 45GB SSD
- 2 vCores
What I did was to add
innodb_force_recovery=2 right after the
[mysqld] section tag. I restarted mysql and it loaded in read only mode.
Then, I wanted to find which table is corrupt, so I ran
mysqlcheck -u root -p --all-databases and then entered my password to login. The output started going table by table (line-by-line) showing
OK at the end. Then it gets into the biggest database I have which is made of three tables (2.1 million rows, 45 million rows, and 1.9 million rows). It does not show any output at that stage, and it just hangs there.
Is there a better way to find out which table is corrupted? Can I print only the bad tables, instead of printing OK for everything?
/var/log/syslog has the following entries after mysql service restart:
[Note] /usr/sbin/mysqld: Normal shutdown [Note] Event Scheduler: Purging the queue. 0 events [Note] InnoDB: FTS optimize thread exiting. [Note] InnoDB: Starting shutdown... [Note] InnoDB: Shutdown completed; log sequence number 32047521550 [Note] /usr/sbin/mysqld: Shutdown complete [Note] /usr/sbin/mysqld (mysqld 10.1.14-MariaDB-1~jessie) starting as process 29607 ... [Note] Using unique option prefix 'myisam_recover' is error-prone and can break in the future. $ [Note] InnoDB: Using mutexes to ref count buffer pool pages [Note] InnoDB: The InnoDB memory heap is disabled [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins [Note] InnoDB: Memory barrier is not used [Note] InnoDB: Compressed tables use zlib 1.2.8 [Note] InnoDB: Using Linux native AIO [Note] InnoDB: Using SSE crc32 instructions [Note] InnoDB: Initializing buffer pool, size = 256.0M [Note] InnoDB: Completed initialization of buffer pool [Note] InnoDB: Highest supported file format is Barracuda. [Note] InnoDB: 128 rollback segment(s) are active. [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.29-76.2 started; log sequence numbe$ [Note] InnoDB: !!! innodb_force_recovery is set to 2 !!! [Note] InnoDB: Dumping buffer pool(s) not yet started [Note] Plugin 'FEEDBACK' is disabled. [Note] Server socket created on IP: '127.0.0.1'. [Note] Reading of all Master_info entries succeded [Note] Added new Master_info '' to hash table [Note] /usr/sbin/mysqld: ready for connections. Version: '10.1.14-MariaDB-1~jessie' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution May 23 16:38:38 warp debian-start: Checking for corrupt, not cleanly closed and upgrade needing tables. mysqld: InnoDB: innodb_force_recovery is on: we do not allow mysqld: InnoDB: database modifications by the user. Shut down mysqld: InnoDB: mysqld and edit my.cnf so thatInnoDB: innodb_force_... is removed.