Recovering MySQL InnoDB after power failure

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:

mkdir /root/mysql_backup
cp -fpr /var/lib/mysql/* /root/mysql_backup/

Then I opened the MySQL server configuration file in a text editor:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Added option:

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):

/etc/init.d/mysqld start

Dump all databases:

mysqldump -u root -A > /root/dumpall.sql

Stopped MySQL server and commented out “innodb_force_recovery” in the configuration:

/etc/init.d/mysqld stop
nano /etc/mysql/mysql.conf.d/mysqld.cnf

Removed database files:

rm -rf /var/lib/mysql/*

I started the MySQL server and imported the previously created dump:

/etc/init.d/mysqld start
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