How To Fix Corrupted Table in MariaDB Database when Service not start with signal 6
Some times MariaDB get currupted and you can get : [ERROR] mysqld got signal 6 ; that probably means that one of your databses are in bad state or have currupted Table data inside, here is how you can fix this , start mysql in recovery mode -> add this line to etc/my.cnf :
[mysqld] port = 8889 innodb_force_recovery=3 innodb_purge_threads=0
*. The innodb_force_recovery have 6 levels, 3 include 1+2 so innodb_force_recovery=6 is the most intensive level. Now Lets try to start again :
systemctl start mariadb.service
This start procedure will start the DB in read-only mode ! now lets check all the databases :
mysqlcheck --all-databases
You will get all status of all the databases table including the Corrupted ones,
zabbix.sysmaps OK zabbix.sysmaps_elements OK zabbix.sysmaps_link_triggers OK zabbix.sysmaps_links OK zabbix.task OK zabbix.task_close_problem OK zabbix.timeperiods OK zabbix.trends OK zabbix.trends_uint Warning : InnoDB: The B-tree of index "PRIMARY" is corrupted. error : Corrupt zabbix.trigger_depends OK zabbix.trigger_discovery OK zabbix.trigger_tag OK zabbix.triggers OK zabbix.users OK zabbix.users_groups OK zabbix.usrgrp OK zabbix.valuemaps OK
To Check one Database (zabbix in this examples):
mysqlcheck -c zabbix
To Check all Databases on the server :
mysqlcheck -c --all-databases
To Optimize one table from the Database :
mysqlcheck -o zabbix actions
To Repair the Database:
mysqlcheck --auto-repair zabbix
To Repair one table from the Database:
mysqlcheck -r zabbix trends.uint
You can combine check, optimize and repair functionality together using “–auto-repair”
mysqlcheck --auto-repair -c -o zabbix
lets assume you want to empty one table which have corrupted data inside, in this example zabbix.trends_uint have index corrupted, lets empty this table :
[root@localhost ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 226 Server version: 5.5.56-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> TRUNCATE TABLE zabbix.trends_uint; Query OK, 0 rows affected (0.03 sec) MariaDB [(none)]> quit
Now stop the service :
systemctl stop mariadb.service
And then delete or # the lines we add to the etc/my.cnf
[mysqld] #port = 8889 #innodb_force_recovery=3 #innodb_purge_threads=0
Then start the service again
systemctl stop mariadb.service
The service should run with no more errors !!!
Additional Useful Mysqlcheck Options
