gpt4 book ai didi

mysql - innodb恢复后如何重启mysql

转载 作者:行者123 更新时间:2023-11-29 21:39:15 26 4
gpt4 key购买 nike

我安装了 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

最佳答案

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

关于mysql - innodb恢复后如何重启mysql,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34668583/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com