Mariadb 意外斷電，InnoDB數據庫恢复

Mariadb 意外斷電，InnoDB數據庫恢复

2016-10-21

1)  Stop MariaDB   ( #systemctl stop mariadb.service )

2)  修改/etc/my.cnf 文件 (innodb_force_recovery = 6); (innodb_purge_threads = 0 )
innodbforcerecovery可以設置为1-6,大的數字包含前面所有數字的影響。
1. (SRVFORCEIGNORECORRUPT):忽略檢查到的corrupt頁。
2. (SRVFORCENOBACKGROUND):阻止主線程的運行，如主線程需要執行full purge操作，會導致crash。
3. (SRVFORCENOTRXUNDO):不執行事務回滾操作。
4. (SRVFORCENOIBUFMERGE):不執行插入緩沖的合並操作。
5. (SRVFORCENOUNDOLOGSCAN):不查看重做日志，InnoDB存儲引擎會將未提交的事務視为已提交。
6. (SRVFORCENOLOG_REDO):不執行前滾的操作。

3) 再次启動，如果還無法启動則刪除數據目錄datafile下的 ibdata1,ib_logfile*等文件。
启動後導出MySQL數據庫，重新恢复即可。

/var/lib/MySQL/ibdata1
=====================================================================
InnoDB: Error: page 10407 log sequence number 2183857188
InnoDB: is in the future! Current system log sequence number 1584842.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
There could be multiple reasons for these errors depending of the damage due to mysql crash, one is innodb log files are corrupt and not in sync with innodb tablespace.

As you said mysql crashed during a table being truncated so check if that table and its data is available and accessible using select queries, also check .ibd file exist for that table.
if all data is ok then take a dump, delete/move mysql directory, create new mysql structure using mysql_install_db and restore the database.
================================================================
http://www.tecmint.com/mysql-backup-and-restore-commands-for-database-administration/

How to Backup All MySQL Databases?

If you want to take backup of all databases, then use the following command with option –all-database. The following command takes the backup of all databases with their structure and data into a file called all-databases.sql.

# mysqldump -u root -ptecmint --all-databases > all-databases.sql

How to Restore Single MySQL Database

To restore a database, you must create an empty database on the target machine and restore the database using msyql command. For example the following command will restore the rsyslog.sql file to the rsyslogdatabase.

# mysql -u root -ptecmint rsyslog < rsyslog.sql

If you want to restore a database that already exist on targeted machine, then you will need to use themysqlimport command.

# mysqlimport -u root -ptecmint rsyslog < rsyslog.sql

In the same way you can also restore database tables, structures and data. If you liked this article, then do share it with your friends.