主页 / dba / 问题 / 209295
Accepted
ad_on_is
Asked: 2018-06-12 05:18:39 +0800 CST

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

  • 772

我有一个 docker-compose 文件，我在其中使用官方mysql:latest图像作为我的数据库服务器。

在 adocker pull mysql和 a之后docker-compose up，图像从5.7 -> 8.0.11更新

此更新损坏了我所有的 mysql 数据。

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

当我尝试重新启动 mysql-server 时，我收到以下错误，即使使用innodb_force_recovery [1-6]

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错误：

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/

所以我跑：

CREATE TABLESPACE wp_options ADD DATAFILE "/var/lib/mysql/dnmc_orig/wp_options.ibd" Engine=InnoDB;

这给了我以下错误，无论是 on5.7和8.0.11.

ERROR 3121 (HY000): Incorrect File Name '/var/lib/mysql/dnmc_orig/wp_options.ibd'.
mysql-5.7 mysql-8.0

1 个回答

  1. Best Answer

    我终于成功地恢复了数据。我的问题是，我试图用 a 恢复数据docker-compose，但不知何故不起作用。

    所以我采取了不同的方法

    1.运行一个docker镜像mysql:8并将“损坏”文件夹挂载到它

    docker run -d -e MYSQL_ROOT_PASSWORD=root -v /path/to/corrupt/folder:/var/lib/mysql --name mrestore mysql:8

    mysql-server 启动没有任何问题，我能够运行 mysql

    mysql -u root -p.

    但是当我运行命令时mysql>show databases;，它给了我一个错误，例如：

    The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

    2.修复用户不存在错误

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

    这是：

    mysql -u root -p
mysql> SET GLOBAL innodb_fast_shutdown = 1;
mysql_upgrade -u root -p

    3.转储数据库

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

    mysqldump -u root -p databasename > databasename.sql
    • 4

相关问题