首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >MySQL从5.7 -> 8.0.11更新后恢复丢失的数据

MySQL从5.7 -> 8.0.11更新后恢复丢失的数据
EN

Database Administration用户
提问于 2018-06-11 13:18:39
回答 1查看 7.2K关注 0票数 5

我有一个坞-合成文件,在这里我使用正式的mysql:latest映像作为我的数据库服务器。

docker pull mysqldocker-compose up之后,图像从5.7 -> 8.0.11更新

此更新破坏了我所有的mysql数据.

所有.frm文件都被删除了。

当我试图重新启动mysql服务器时,即使使用innodb_force_recovery [1-6],也会出现以下错误

代码语言:javascript
运行
复制
2018-06-05T19:19:14.624533Z 0 [ERROR] [FATAL] InnoDB: Table flags are 0 in the data dictionary but the flags in file ./ibdata1 are 0x4000!
2018-06-05 19:19:14 0x7fd24a8b6740  InnoDB: Assertion failure in thread 140541170509632 in file ut0ut.cc line 942
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
19:19:14 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
-> Executing /opt/docker/provision/entrypoint.d/05-permissions.sh
max_used_connections=0
-> Executing /opt/docker/provision/entrypoint.d/20-nginx.sh
max_threads=151
-> Executing /opt/docker/provision/entrypoint.d/20-php-fpm.sh
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68195 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
mysqld(my_print_stacktrace+0x2c)[0x55d85cb951ec]
mysqld(handle_fatal_signal+0x479)[0x55d85c4c3e59]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x110c0)[0x7fd24a4940c0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcf)[0x7fd248c20fff]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7fd248c2242a]
mysqld(+0x628387)[0x55d85c49a387]
mysqld(_ZN2ib5fatalD1Ev+0x12d)[0x55d85cd63c8d]
mysqld(+0xf9ead1)[0x55d85ce10ad1]
mysqld(+0xf9f108)[0x55d85ce11108]
mysqld(_Z6fil_ioRK9IORequestbRK9page_id_tRK11page_size_tmmPvS8_+0x2b0)[0x55d85ce1a230]
mysqld(_Z13buf_read_pageRK9page_id_tRK11page_size_t+0xce)[0x55d85cdcf1ee]
mysqld(_Z16buf_page_get_genRK9page_id_tRK11page_size_tmP11buf_block_tmPKcmP5mtr_tb+0x4aa)[0x55d85cd9e34a]
mysqld(_Z31trx_rseg_get_n_undo_tablespacesPm+0x143)[0x55d85cd41e23]
mysqld(+0x6274fb)[0x55d85c4994fb]
mysqld(_Z34innobase_start_or_create_for_mysqlv+0x2f3d)[0x55d85cd0ecdd]
mysqld(+0xd69f63)[0x55d85cbdbf63]
mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x4f)[0x55d85c50ebff]
mysqld(+0xb138e6)[0x55d85c9858e6]
mysqld(_Z40plugin_register_builtin_and_init_core_sePiPPc+0x2f0)[0x55d85c988ad0]
mysqld(+0x64a566)[0x55d85c4bc566]
mysqld(_Z11mysqld_mainiPPc+0xc71)[0x55d85c4be121]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf1)[0x7fd248c0e2e1]
mysqld(_start+0x2a)[0x55d85c4b480a]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

我已经搜索了如何从现有的.ibd文件中恢复数据的解决方案,但是没有什么效果。

我到目前为止尝试过的

由于我有一个数据库的本地副本,但由于数据已经过时,我希望能够以某种方式重新导入这些数据。

1.尝试重构表并将.ibd文件导入表空间:

  • 创建了一个单独的mysql实例
  • CREATE DATABASE new_db; USE new_db;
  • CREATE TABLE wp_options(...)来根据我的本地转储创建表的结构。
  • alter table wp_options discard tablespace
  • 将我要还原的wp_options.ibd复制到new_db文件夹中
  • alter table wp_options import tablespace

5.7上,它给出了错误:

代码语言:javascript
运行
复制
ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table `dnmc`.`wp_options` : Unsupported``

8.0.11上,服务器刚刚崩溃。

2.创建表空间并将其分配给表

我偶然发现了一个:https://www.percona.com/blog/2016/10/03/mysql-8-0-general-tablespaces-file-per-database-no-frm-files/

所以我跑了:

代码语言:javascript
运行
复制
CREATE TABLESPACE wp_options ADD DATAFILE "/var/lib/mysql/dnmc_orig/wp_options.ibd" Engine=InnoDB;

这给了我以下错误,无论是在5.7还是8.0.11上。

代码语言:javascript
运行
复制
ERROR 3121 (HY000): Incorrect File Name '/var/lib/mysql/dnmc_orig/wp_options.ibd'.
EN

回答 1

Database Administration用户

回答已采纳

发布于 2018-06-18 21:46:10

我终于成功地恢复了数据。我的问题是,我试图用docker-compose来恢复数据,但不知怎么不起作用。

所以我采取了另一种方法

1.运行mysql:8的停靠映像并将“损坏”文件夹挂载到

代码语言:javascript
运行
复制
docker run -d -e MYSQL_ROOT_PASSWORD=root -v /path/to/corrupt/folder:/var/lib/mysql --name mrestore mysql:8

mysql服务器启动时没有任何问题,我可以用

mysql -u root -p

但是,当我运行命令mysql>show databases;时,它给了我一个错误,比如:

代码语言:javascript
运行
复制
The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

2. Fix用户不存在错误

一个快速的google搜索让我找到了一个有同样问题的人的答案:https://stackoverflow.com/questions/49992868/mysql-errorthe-user-specified-as-a-definer-mysql-infoschemalocalhost-doe

即:

代码语言:javascript
运行
复制
mysql -u root -p
mysql> SET GLOBAL innodb_fast_shutdown = 1;
mysql_upgrade -u root -p

3.转储数据库

最后,我能够启动mysql并转储我认为丢失的数据库

代码语言:javascript
运行
复制
mysqldump -u root -p databasename > databasename.sql
票数 4
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://dba.stackexchange.com/questions/209295

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档