How to fix InnoDB corruption cases for the MySQL/MariaDB databases on Plesk for Windows?

Connect to the server via RDP.

Create a backup of all current Plesk-related MySQL/MariaDB data and databases at %plesk_dir%MySQL\Data\.

Open the %plesk_dir%MySQL\my.ini file.

Add the following lines into the [PleskSQLServer] section:

innodb_force_recovery = 1
skip-grant-tables

Try to start PleskSQLServer.

In case it fails once again, set innodb_force_recovery to a greater value and try starting it again.

Warning: Only set innodb_force_recovery to a value greater than 0 in an emergency situation, so that you can start InnoDB and dump your tables. Values of 4 or greater can permanently corrupt data files. Therefore, increase this value incrementally, as necessary. Please see more details in the official MySQL Documentation.

Once PleskSQLServer is started in force recovery mode, create dumps of all databases using the following command executed via the PowerShell:

PS cd $env:plesk_dir\mysql\bin\
mkdir .\data_restore
.\mysql.exe -uadmin -P8306 -sNe "SHOW DATABASES" | findstr /V performance_schema | findstr /V information_schema > c:\db_list.txt
foreach ($var in get-content c:\db_list.txt) {.\mysqldump.exe -uadmin -P8306 $var > .\data_restore\$var.sql}

Stop PleskSQLServer service.

Remove all MySQL/MariaDB data except mysql folder and error logs from %plesk_dir%MySQL\Data\ directory.

Remove the line innodb_force_recovery from my.ini file

Start PleskSQLServer

Restore the databases from the dumps made during the step 7:

PS foreach ($var in get-content c:\db_list.txt) {.\mysql.exe -uadmin -P8306 -e"create database $var"}
foreach ($var in get-content c:\db_list.txt) {get-content .\data_restore\$var.sql | .\mysql.exe -uadmin -P8306 $var}

Remove the skip-grant-tables line from my.ini file and restart PleskSQLServer .

Connect to the server via RDP.

Create a backup of all current MySQL/MariaDB data and databases at %plesk_dir%Databases\MySQL\data

Open the %plesk_dir%Databases\MySQL\my.ini file.

Add the following line into the [MySQLD] section:

innodb_force_recovery = 1
skip-grant-tables

Try to start MySQL/MariaDB.

In case it fails once again, set innodb_force_recovery to a greater value and try starting it again.

Warning: Only set innodb_force_recovery to a value greater than 0 in an emergency situation, so that you can start InnoDB and dump your tables. Values of 4 or greater can permanently corrupt data files. Therefore, increase this value incrementally, as necessary. Please see more details in the Official MySQL Documentation.

Once MySQL/MariaDB is started in force recovery mode, create dumps of all databases using the following command executed via the PowerShell:

PS cd $env:plesk_dir\mysql\bin\
mkdir .\data_restore
.\mysql.exe -uadmin -P3306 -e"SHOW DATABASES" > c:\db_list.txt
foreach ($var in get-content c:\db_list.txt) {.\mysqldump.exe -uadmin -P3306 $var > .\data_restore\$var.sql}

Stop MySQL/MariaDB service.

Remove all MySQL/MariaDB data except mysql folder and error logs from %plesk_dir%Databases\MySQL\data directory.

Remove the line innodb_force_recovery from my.ini file and start MySQL/MariaDB.

Restore the databases from the dumps made during the step 7:

PS foreach ($var in get-content c:\db_list.txt) {.\mysql.exe -uadmin -P3306 -e"create database $var" }
foreach ($var in get-content c:\db_list.txt) {get-content .\data_restore\$var.sql | .\mysql.exe -uadmin -P3306 $var}

Remove the skip-grant-tables line from my.ini file and restart MySQL/MariaDB.

Connect to the server via RDP.

Make sure that there are Plesk daily dumps available on the server in the %plesk_dir%Mysql\Backup.

Stop the Plesk SQL server via the Plesk Services Monitor.

Create a backup of all current MySQL/MariaDB data and databases in %plesk_dir%MySQL\Data folder.

Start the Command Prompt and initialize MySQL/MariaDB by running the following command:

• For MariaDB >= 10.5:

  "%plesk_dir%MySQL\bin\mysql_install_db.exe" --datadir="%plesk_dir%MySQL\Data"

• For older MariaDB/MySQL versions:

  "%plesk_dir%MySQL\bin\mysql.exe" --initialize --datadir="%plesk_dir%MySQL\Data" --console

Apply correct permissions to MySQL/MariaDB directory:

plesk sbin ApplySecurity.exe --apply-to-directory --directory="%plesk_dir%MySQL"

Start Plesk SQL Server via the Plesk Services Monitor.

Create a root MySQL/MariaDB user and password:

plesk sbin mysqlmng_adm.exe --inject-root

plesk sbin mysqlmng_adm.exe --add-super-user --login=admin "--password=password" --allowed-host=localhost

Note: Replace password with the password of choice

plesk sbin psadb.exe --update-admin-password --password="password"

Log in to the MySQL database server with the password set in prior step:

"%plesk_dir%MySQL\bin\mariadb.exe" -uadmin -p

Create the psa database and log off from the database server:

CREATE DATABASE psa;
Query OK, 1 row affected (0.002 sec)
\q

Restore the Plesk psa database from a daily dump.

Create the apsc database:

plesk sbin mysqlmng_adm.exe --create-database --database=apsc

plesk sbin mysqlmng_adm.exe --add-user --database=apsc --new-user-login=apsc "--new-user-password=password" --allowed-host=localhost

plesk php "%plesk_dir%admin\plib\scripts\register_apsc_database.php" --register -host 127.0.0.1 -port 8306 -database apsc -login apsc -password "password"

Restore the apsc database from a daily dump as on step 9.

Connect to the server via RDP.

Create a backup of all current MySQL/MariaDB data and databases in %plesk_dir%Databases\MySQL\Data.

Remove all the data from the %plesk_dir%Databases\MySQL\Data folder.

Open Command Prompt as Administrator and execute the command below to initialize MariaDB.

Note: In the example below the command is provided for MariaDB 10.6 and Plesk directory is a default one (%plesk_dir%=C:\Program Files (x86)\Plesk). Please consider adjusting paths if required

"C:\Program Files\MariaDB 10.6\bin\mysql_install_db.exe" --datadir="%plesk_dir%Databases\MySQL\Data"

Start MariaDB via the Plesk Services Monitor.

Open the %plesk_dir%Databases\MySQL\my.ini file for editing, add the skip-grant-tables option to it under the [MySQLD] section:

[MySQLD]
...
skip-grant-tables

Save the changes.

Restart MariaDB via the Plesk Services Monitor to apply changes.

Log into the customers' MySQL/MariaDB without the password via the Command Prompt:

"%plesk_dir%\MySQL\bin\mysql.exe" -P3306 -u root

Execute the commands below one by one:

9.1. Reload the grant-tables policies by running the next command:

FLUSH PRIVILEGES;

9.2. Recreate the user by specifying a new password using the next two commands:

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' identified by 'password' WITH GRANT OPTION;

Note: Make sure to replace password with a new password of choice.

9.3. Tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command:

FLUSH PRIVILEGES;

Remove the skip-grant-tables from the MySQL/MariaDB configuration file %plesk_dir%Databases\MySQL\my.ini and restart the service again as in Step 7.

Log in to Plesk.

Navigate to Tools & Settings > Database Servers and click localhost.

In the opened page click the Settings button and specify new the password, that was set in Step 9.2 in the Administrator's password and Confirm password fields.

In the Command Prompt execute the command below to synchronize databases and database users with Plesk:

plesk repair mysql -y

Restore the customers' databases from a backup.