Fix corrupted MyISAM tables in MySQL database
How can the MyISAM table corruption error be repaired in the Ezeelogin database (MySQL or MariaDB)?
This error may occur when the disk space on the gateway server is full, preventing the writing of any data to the Ezeelogin database table and causing table corruption. Table corruption can be analyzed and repaired using myisamchk command. myisamchk is a command-line tool provided by MySQL for maintaining and repairing MyISAM tables. It can be employed for tasks such as identifying and fixing table corruption, optimizing table performance, and recovering data from damaged tables.
1. Check the ezsh log of the gateway user to view the error messages.
root@gateway ~]# tail -f /home/{username}/ezsh.log
eg: root@gateway ~]# tail -f /home/alex/ezsh.log
Query failed (INSERT INTO 'jqusxi_sshlogs' ('user_id', 'server_id', 'serveractivity_id', 'ssh_user', 'type', 'status', 'file', 'encryption, 'mexecid', 'created') VALUES ('791', '650', '239145', 'log', 'full', 'begin', '/var/log/ezlogin/full/alex/log~jumpserver~Wed_Oct_25-09:00:32_2023', '0', '', 'NOW())): Error 145: Table './ezlogin_xltao/jqusxi_sshlogs' is marked as crashed and should be repaired
SSH log entry (/var/log/ezlogin/full/alex/log~jumpserver~Wed_Oct_25-09:00:32_2023) creation failed: 0
2. Refer to the below steps to find the corrupted table:
a) Find the corrupted tables from the MySQL error log. Run the below commands to view the MySQL error log.
root@gateway ~]# less /var/log/mysql/error.log
b) Check for corruption or error in the table. Run the below command to view the details of the SSH logs table and list all Ezeelogin database tables.
- root@gateway ~]# mysql -u root -p -e "USE $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf); check table $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)sshlogs"
+------------------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------------+-------+----------+----------+
| ezlogin_xltao.jqusxi_sshlogs | check | status | OK |
+------------------------------+-------+----------+----------+
- root@gateway ~]# mysqlcheck -u root -p "$(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)"
ezlogin_xltao.jqusxi_acl_functions OK
ezlogin_xltao.jqusxi_authlogs OK
ezlogin_xltao.jqusxi_blogs OK
ezlogin_xltao.jqusxi_commandgroup_commands OK
ezlogin_xltao.jqusxi_commandgroups OK
------
3. Backup the current database before making any changes or repair starts.
root@gateway ~]# mysqldump -u root -p ezlogin_db > ezlogin_db_backup.sql
eg: root@gateway ~]# mysqldump -u root -p ezlogin_xltao > ezlogin_db_backup.sql
Refer below command to find all the Ezeelogin database table names and the file can be used to analyze or repair using myisamchk.
root@gateway ~]# find /var/lib/mysql -type f -name ""$(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)"*.MYI"
/var/lib/mysql/ezlogin_xltao/jqusxi_commands.MYI
/var/lib/mysql/ezlogin_xltao/jqusxi_ips.MYI
/var/lib/mysql/ezlogin_xltao/jqusxi_proxylogs.MYI
/var/lib/mysql/ezlogin_xltao/jqusxi_sshlogs_field_values.MYI
/var/lib/mysql/ezlogin_xltao/jqusxi_ldap.MYI
4. Try to fix the table corruption with myisamchk:
a). Check the tables having the issue using myisamchk. Change the working directory and run the below command to check for the table.
root@gateway ~]# cd /var/lib/mysql/ezlogin_xltao/
root@gateway ~]# myisamchk db_prefix_table.MYI
eg: root@gateway ~]# myisamchk jqusxi_sshlogs.MYI
b) Try to repair the table using -r option. Run the below command to repair the table.
root@gateway ~]# cd /var/lib/mysql/ezlogin_xltao/
root@gateway ~]# myisamchk -r db_prefix_table.MYI
c) If the standard repair option doesn't work, run the below command with -o option to try the fast recovery option.
root@gateway ~]# cd /var/lib/mysql/ezlogin_xltao/
root@gateway ~]# myisamchk -o db_prefix_table.MYI
d) Try the below command to force the recovery option. Replace the table prefix name to force the recovery option.
root@gateway ~]# cd /var/lib/mysql/ezlogin_xltao/
root@gateway ~]# myisamchk --safe-recover db_prefix_table.MYI
Refer to the article MyISAM Table Maintenance and Crash Recovery for more information on MyISAM table maintenance