innodb structure database corruption recovery method

Restore the database from backup disk data, suitable for ordinary users to operate

First followhttps://www.west.cn/faq/list.asp?unid=753 to mount the backup disk

1. windows system

    Find the mysql installation directory,

    If it was installed by our company's Website Building Assistant, then in D:\soft_php_package\mysql\, rename the data directory, and then copy the data directory under the same path on the backup disk back,

    then start it again. If it still cannot start, you can try backing up the entire mysql directory and then overwriting the entire mysql directory;

    If you installed mysql yourself, find the my.ini file in the installation directory and open it with Notepad, for example

    basedir="D:/phpStudy/MySQL/"   #This is the mysql installation path

    datadir="D:/phpStudy/MySQL/data/" #This is the mysql data storage path

    Rename the data directory, then copy the data directory under the same path on the backup disk back. If it still cannot start, you can try overwriting the entire mysql directory;

    If it still cannot be started/recovered, you can back up the data and reinstall mysql

2. linux system   

    If it is the wdcp environment installed by default by our company, then in /home/wddata/var, rename the var directory, and then copy the var directory under the same path on the backup disk back,

    cd /home/wddata/

    mv var  var.bak

    cp -rfpu /home1/wddata/var /home/wddata/

    service mysqld start

    If you installed mysql yourself, find the my.cnf file, for example

    basedir="/etc/mysql/"   #This is the mysql installation path

    datadir="/etc/mysql/var/" #This is the mysql data storage path

    Rename the var directory, then copy the var directory under the same path on the backup disk back. If it still cannot start, you can try overwriting the entire mysql directory

    If it still cannot be started/recovered, back up the existing mysqldata directory, then reinstall mysql

It is strongly recommended to restore according to the above solution

The following is a reference for professional technicians to rebuild an innodb database; non-professionals please do not attempt, as it may cause data loss!!!

Skip recovery mode, export data and rebuild the database

    Find the my.ini file in the mysql installation directory, and add on the next line under [mysqld]

    innodb_force_recovery = 6

    innodb_purge_threads = 0

    After saving, start mysql again

Create a file in D:\mysql\, name it db.txt, and open it with Notepad

Write the names of databases that use the innodb structure, one per line

Then create a new file on the desktop, name it dump.bat, open it with Notepad, and copy the following code

echo off

cls

md D:\deleted\sql

for /f %%i in (D:\mysql\db.txt) do (

echo %%i

mysqldump -uroot -ppassword -Q -a --opt --default-character-set=gbk %%i>D:\deleted\sql\%%i-gbk.sql

mysqldump -uroot -ppassword -Q -a --opt --default-character-set=utf8 %%i>D:\deleted\sql\%%i-utf8.sql

)

Replace 'password' with the actual mysql password

Stop the service, remove the innodb_force_recovery parameter from my.ini, cut ibdata and ib_logfile under the data directory to another location, and start the service

Import data

Create im.bat

echo off

cls

md D:\deleted\bkup

for /f %%i in (D:\mysql\db.txt) do (

echo %%i

move /y d:\mysql\data\%%i D:\deleted\bkup\

mysql -uroot -ppassword -e "create database %%i;"

mysql -uroot -ppassword --default-character-set=utf8 %%i<D:\deleted\sql\%%i-utf8.sql

)

Replace 'password' with the actual mysql password

For linux, similarly after modifying the my.cnf parameters, export the data, cut ibdata and ib_logfile under the var directory to another location, and start the service

Import data