Procedure to recreate a MySQL environment that has corrupted system tables

28.11.2025

Mark McIlroy

This procedure was created over many hours working with ChatGPT. I doesn't work, needs more work.


1. Take a full backup of the server and only start this on a weekend as it make take a long time and in
the worst case several days of debugging.


2. Check that there is plenty of free disk space.

df -h


3. Dump the data to SQL if possible or get SQL files from a backup. Preferably a separate file for each database if there
aren't too many of them.

mysqldump root_cwp > /root/root_cwp.sql


4. Stop mysql

systemctl stop mariadb


5. Check that it's stopped several times in case it restarts

ps aux | grep mysqld		Should only show the 'grep' process not mysql

If necessary

systemctl disable mariadb

pkill -9 mysqld


6. Delete all files from /var/lib/mysql

rm -fr /var/lib/mysql/*

ls /var/lib/mysql			Check that it's empty

chown -R mysql:mysql /var/lib/mysql
chmod 750 /var/lib/mysql


7. Reinstall the mysql binaries

dnf reinstall mariadb-server mariadb-common mariadb-server-utils


8. Run the script to create fresh system tables

mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql


9. Run the mysql hardening script, installing a root password

/usr/bin/mysql_secure_installation


10. Start mysql

systemctl start mariadb
systemctl status mariadb	Press q at end of display


11. Import the databases, one at a time checking after each one

mysql
source inputfile.sql;


12. Create users and assign them to tables





Debugging

1. Check that the server is running

	 systemctl status mariadb


2. Check the socket file for communication

ls -l /var/lib/mysql/mysql.sock		However can be a different location, see /etc/my.cnf

Should exist and have full permissions


3. Run 

	/usr/libexec/mysqld --user=mysql --console	
	
		This will start mysql and print debugging messages. You may have to cancel the terminal session
		to exit this function.

4. If mysql is already running when you try to start it it will fail as it cannot lock files already open

try

systemctl disable mariadb
systemctl stop mariadb

and delete old lock files

rm -f /var/lib/mysql/aria_log_control
rm -f /var/lib/mysql/ibdata1
rm -f /var/lib/mysql/ib_logfile*