problem
The phenomenon is very simple. The database server is down, of course, without stopping the database service.
After the machine restarts, try to restart the MySQL service, but it fails. View the error log:
Resolution process
At the beginning, the focus was on this log:
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 406067 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
I thought there was a problem with some MySQL parameter settings, and combined with Google's results, I modified/etc/my.cnf, which still failed. After the problem was solved, MySQL was running normally before, so the configuration would not be a problem. At that time, it was also a "medical emergency".
1. Forcing InnoDB Recovery
Set the recovery mode to start MySQL, and add the following configuration in/etc/my.cnf:
[mysqld]
innodb_force_recovery = 1
The later value is set to 1. If 1 fails to start successfully, it will be gradually increased to 2/3/4. Until MySQL can be started!!!
Forcing InnoDB Recovery provides six levels of repair modes. Note that when the value is greater than 3, the data file will be permanently damaged and cannot be recovered. The introduction of the six levels is abstracted as follows:
1. (SRV_FORCE_IGNORE_CORRUPT)
Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.
2. (SRV_FORCE_NO_BACKGROUND)
Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.
3. (SRV_FORCE_NO_TRX_UNDO)
Does not run transaction rollbacks after crash recovery.
4. (SRV_FORCE_NO_IBUF_MERGE)
Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes.
5. (SRV_FORCE_NO_UNDO_LOG_SCAN)
Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed. This value can permanently corrupt data files.
6. (SRV_FORCE_NO_LOG_REDO)
Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.
Start MySQL in recovery mode
/usr/local/mysql/bin/mysqld_safe -user=mysql&
one
After the restart is successful, test whether the database can connect normally: mysql - uroot - p123456
Data backup
The recovery model database is read-only, which is of course related to the recovery level.
What we need to do now is to back up the database data, clear the previous error data, and finally restore from the backup data.
mysqldump -uroot -p123456 --all-databases > all_mysql_backup.sql
Original data cleaning or backup
The database service needs to be stopped before data cleaning.
Backing up the data directory of the database is equivalent to restoring the database to the state it was in when it was just installed.
mkdir data-bak
cd data
mv * ../data-bak/
data recovery
database initialized
Since all data has been deleted, MySQL initialization is required.
cd /usr/local/mysql
./scripts/mysql_install_db --user=mysql&
Backup data recovery
Log in to MySQL:
mysql -u root -p123456
After logging in, execute the following statements in the database to recover data:
source /app/all_mysql_backup.sql
Check the data after recovery.
