Once, after the power was turned off, the UPS was discharged, then the generator automatic start did not work because it turned on and off several times, as a result, the server with MySQL was also started and disconnected from the power supply several times, later the tables of different databases were damaged , including the default database with mysql users, as a result, the MySQL daemon did not start.
First, I created a directory and made a copy of all the database files in it:
cp -fpr /var/lib/mysql/* /root/mysql_backup/
Then I opened the MySQL server configuration file in a text editor:
innodb_force_recovery = 4
I tried to start the MySQL server (it started up for me, if it didn’t start, you can increase the value to 5 or 6 for example and try to start it):
Dump all databases:
mysqldump -u root -A > /root/dumpall.sql
Stopped MySQL server and commented out “innodb_force_recovery” in the configuration:
Removed database files:
rm -rf /var/lib/mysql/*
I started the MySQL server and imported the previously created dump:
mysql -u root -p < /root/dumpall.sql
As a result, the database with MySQL users was restored, as well as other databases.
Sometimes the databases may not be fully restored, for example, some tables or data may be missing, therefore it is necessary to compare them with previous backup copies.
For important operations, such as payments, user actions, it is advisable to write logs in a separate table or file, so that you can restore the data after the last backup.
Sometimes I completely deleted the MySQL server, and then install and import a dump or backup:
apt-get purge mysql-server mysql-client mysql-common
apt-get install mysql-server
During deletion, the following text was displayed:
The following packages will be REMOVED:
libdbd-mysql-perl* libmysqlclient-dev* libmysqlclient20* mysql-client* mysql-client-5.7* mysql-common* mysql-server* mysql-server-5.7*
See also my article:
Configuring replication in MySQL