MySQL的innodb_force_recovery

MySQL在转储文件或者导入数据的过程中，出现中断、失败或者异常，造成数据无法回滚，可以通过innodb_force_recovery强力迫使InnoDB存储引擎运行，同时阻止后台操作运行，以便转储表数据。
应用程序在使用数据库时，innodb_force_recovery必须为0，否则应用启动失败。

java.sql.SQLException: Operation not allowed when innodb_forced_recovery > 0.
[mysqld]
innodb_force_recovery = 1

innodb_force_recovery有6个可选项，如下所示，更详细的描述请查阅官方文档。

innodb_force_recovery	描述
1	SRV_FORCE_IGNORE_CORRUPT
2	SRV_FORCE_NO_BACKGROUND
3	SRV_FORCE_NO_TRX_UNDO
4	SRV_FORCE_NO_IBUF_MERGE
5	SRV_FORCE_NO_UNDO_LOG_SCAN
6	SRV_FORCE_NO_LOG_REDO

SELECT … INTO OUTFILE 转储表数据

mysql> select * from level into outfile 'lecel.txt';
Query OK, 3 rows affected (0.00 sec)

转储开关secure-file-priv

mysql> select * into outfile 'C:\Desktop\temp\level.txt' from level ;
1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

检查当前secure-file-priv的状态，NULL表示禁止转储

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.04 sec)

设置secure-file-priv，空字符串表示“去掉导入的目录限制”

更多secure-file-priv的选项，请查阅官方文档

[mysqld]
secure_file_priv=

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.06 sec)