mysql - innodb恢复后如何重启mysql

Question

我安装了 strator 服务器的完整备份，并且必须在 my.cnf 中使用“innodb_force_recovery=1”才能启动我的数据库。

但是，我无法写入/更改数据库/表，并要求取消注释“innodb_force_recovery=1”选项。

注释掉后，我无法启动 SQL Server。任何帮助将不胜感激。我得到以下堆栈跟踪:

从 innodb_force_recovery=1 开始

160108  3:28:04 [Note] Event Scheduler: Loaded 0 events
160108  3:28:04 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.38-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
160108  3:28:21 [Note] /usr/sbin/mysqld: Normal shutdown

160108  3:28:21 [Note] Event Scheduler: Purging the queue. 0 events
160108  3:28:21  InnoDB: Starting shutdown...
160108  3:28:23  InnoDB: Shutdown completed; log sequence number 946126358
160108  3:28:23 [Note] /usr/sbin/mysqld: Shutdown complete

160108  3:28:41 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160108  3:28:41 [Note] Plugin 'FEDERATED' is disabled.
160108  3:28:41 [Warning] option 'innodb-buffer-pool-size': signed value 2097152 adjusted to 5242880
160108  3:28:41 [Warning] option 'innodb-additional-mem-pool-size': signed value 512000 adjusted to 524288
160108  3:28:41 InnoDB: The InnoDB memory heap is disabled
160108  3:28:41 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160108  3:28:41 InnoDB: Compressed tables use zlib 1.2.8
160108  3:28:41 InnoDB: Using Linux native AIO
160108  3:28:41 InnoDB: Initializing buffer pool, size = 5.0M
160108  3:28:41 InnoDB: Completed initialization of buffer pool
160108  3:28:41 InnoDB: highest supported file format is Barracuda.
160108  3:28:41  InnoDB: Waiting for the background threads to start
160108  3:28:42 InnoDB: Error: tablespace size stored in header is 4224 pages, but
160108  3:28:42 InnoDB: the sum of data file sizes is only 3840 pages
160108  3:28:42 InnoDB: 5.5.38 started; log sequence number 946126358
160108  3:28:42 InnoDB: !!! innodb_force_recovery is set to 1 !!!
160108  3:28:42 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160108  3:28:42 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
160108  3:28:42 [Note] Server socket created on IP: '0.0.0.0'.
160108  3:28:42 [Note] Event Scheduler: Loaded 0 events
160108  3:28:42 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.38-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

在没有 innodb_force_recovery=1 的情况下重新启动

160108  3:29:28 [Note] /usr/sbin/mysqld: Normal shutdown

160108  3:29:28 [Note] Event Scheduler: Purging the queue. 0 events
160108  3:29:28  InnoDB: Starting shutdown...
160108  3:29:29  InnoDB: Shutdown completed; log sequence number 946126358
160108  3:29:29 [Note] /usr/sbin/mysqld: Shutdown complete

160108  3:29:29 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160108  3:29:29 [Note] Plugin 'FEDERATED' is disabled.
160108  3:29:29 [Warning] option 'innodb-buffer-pool-size': signed value 2097152 adjusted to 5242880
160108  3:29:29 [Warning] option 'innodb-additional-mem-pool-size': signed value 512000 adjusted to 524288
160108  3:29:29 InnoDB: The InnoDB memory heap is disabled
160108  3:29:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160108  3:29:29 InnoDB: Compressed tables use zlib 1.2.8
160108  3:29:29 InnoDB: Using Linux native AIO
160108  3:29:29 InnoDB: Initializing buffer pool, size = 5.0M
160108  3:29:29 InnoDB: Completed initialization of buffer pool
160108  3:29:29 InnoDB: highest supported file format is Barracuda.
160108  3:29:30  InnoDB: Waiting for the background threads to start
160108  3:29:31 InnoDB: Error: tablespace size stored in header is 4224 pages, but
160108  3:29:31 InnoDB: the sum of data file sizes is only 3840 pages
160108  3:29:31 InnoDB: Cannot start InnoDB. The tail of the system tablespace is
160108  3:29:31 InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an
160108  3:29:31 InnoDB: inappropriate way, removing ibdata files from there?
160108  3:29:31 InnoDB: You can set innodb_force_recovery=1 in my.cnf to force
160108  3:29:31 InnoDB: a startup if you are trying to recover a badly corrupt database.
160108  3:29:31 [ERROR] Plugin 'InnoDB' init function returned error.
160108  3:29:31 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160108  3:29:31 [ERROR] Unknown/unsupported storage engine: InnoDB
160108  3:29:31 [ERROR] Aborting

160108  3:29:31 [Note] /usr/sbin/mysqld: Shutdown complete

160108  3:29:32 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160108  3:29:32 [Note] Plugin 'FEDERATED' is disabled.
160108  3:29:32 [Warning] option 'innodb-buffer-pool-size': signed value 2097152 adjusted to 5242880
160108  3:29:32 [Warning] option 'innodb-additional-mem-pool-size': signed value 512000 adjusted to 524288
160108  3:29:32 InnoDB: The InnoDB memory heap is disabled
160108  3:29:32 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160108  3:29:32 InnoDB: Compressed tables use zlib 1.2.8
160108  3:29:32 InnoDB: Using Linux native AIO
160108  3:29:32 InnoDB: Initializing buffer pool, size = 5.0M
160108  3:29:32 InnoDB: Completed initialization of buffer pool
160108  3:29:32 InnoDB: highest supported file format is Barracuda.
160108  3:29:32  InnoDB: Waiting for the background threads to start
160108  3:29:33 InnoDB: Error: tablespace size stored in header is 4224 pages, but
160108  3:29:33 InnoDB: the sum of data file sizes is only 3840 pages
160108  3:29:33 InnoDB: Cannot start InnoDB. The tail of the system tablespace is
160108  3:29:33 InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an
160108  3:29:33 InnoDB: inappropriate way, removing ibdata files from there?
160108  3:29:33 InnoDB: You can set innodb_force_recovery=1 in my.cnf to force
160108  3:29:33 InnoDB: a startup if you are trying to recover a badly corrupt database.
160108  3:29:33 [ERROR] Plugin 'InnoDB' init function returned error.
160108  3:29:33 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160108  3:29:33 [ERROR] Unknown/unsupported storage engine: InnoDB
160108  3:29:33 [ERROR] Aborting

160108  3:29:33 [Note] /usr/sbin/mysqld: Shutdown complete

160108  3:29:34 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160108  3:29:34 [Note] Plugin 'FEDERATED' is disabled.
160108  3:29:34 [Warning] option 'innodb-buffer-pool-size': signed value 2097152 adjusted to 5242880
160108  3:29:34 [Warning] option 'innodb-additional-mem-pool-size': signed value 512000 adjusted to 524288
160108  3:29:34 InnoDB: The InnoDB memory heap is disabled
160108  3:29:34 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160108  3:29:34 InnoDB: Compressed tables use zlib 1.2.8
160108  3:29:34 InnoDB: Using Linux native AIO
160108  3:29:34 InnoDB: Initializing buffer pool, size = 5.0M
160108  3:29:34 InnoDB: Completed initialization of buffer pool
160108  3:29:34 InnoDB: highest supported file format is Barracuda.
160108  3:29:34  InnoDB: Waiting for the background threads to start
160108  3:29:35 InnoDB: Error: tablespace size stored in header is 4224 pages, but
160108  3:29:35 InnoDB: the sum of data file sizes is only 3840 pages
160108  3:29:35 InnoDB: Cannot start InnoDB. The tail of the system tablespace is
160108  3:29:35 InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an
160108  3:29:35 InnoDB: inappropriate way, removing ibdata files from there?
160108  3:29:35 InnoDB: You can set innodb_force_recovery=1 in my.cnf to force
160108  3:29:35 InnoDB: a startup if you are trying to recover a badly corrupt database.
160108  3:29:35 [ERROR] Plugin 'InnoDB' init function returned error.
160108  3:29:35 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160108  3:29:35 [ERROR] Unknown/unsupported storage engine: InnoDB
160108  3:29:35 [ERROR] Aborting

160108  3:29:35 [Note] /usr/sbin/mysqld: Shutdown complete

Answer 1

innodb_force_recovery的目的是以只读模式启动MySQL并抢救用户数据。在极少数情况下，它可以用于修复表空间(例如，删除损坏的索引)，但这是一个异常(exception)。

当您进行转储时，您应该停止 MySQL，将现有 MySQL 文件移动到存档并从转储中重新创建数据库。

# service mysql stop
# sed -i 's/innodb_force_recovery=[1-6]/innodb_force_recovery=0' /etc/my.cnf
# mv /var/lib/mysql /var/lib/mysql.archive
# mkdir /var/lib/mysql
# chown -R mysql:mysql /var/lib/mysql
# service mysql start
# mysql < dump.sql