MariaDB数据库损坏：引擎中不存在表 [英]Database corruption with MariaDB : Table doesn't exist in engine-HQY 一个和谐有爱的空间

21

2020
05
09:24:40

MariaDB数据库损坏：引擎中不存在表 [英]Database corruption with MariaDB : Table doesn't exist in engine

I'm in an environement setup, running OSX with MariaDB 10.0.12-MariaDB Homebrew

我正处于环境设置中,使用MariaDB 10.0.12-MariaDB Homebrew运行OSX

I've screwed up the installation so I did completely removed MySQL and MariaDB from my setup and started again.

我搞砸了安装,所以我完全从我的设置中删除了MySQL和MariaDB,然后重新开始。

After finishing installing MariaDB, I've reimported my databases (innoDB) via a DB Dump from the production server. It worked fine. After a reboot, the day after, I can no longer access to the databases :

完成MariaDB的安装后,我通过生产服务器上的DB Dump重新导入了我的数据库(innoDB)。它工作正常。重新启动后,第二天,我无法再访问数据库:

Table 'my.table' doesn't exist in engine

表'my.table'在引擎中不存在

What's causing this and what's the solution ? I do see the structure of my database, but when I try to access it, it gives me this error message.

造成这种情况的原因是什么?解决方案是什么?我确实看到了我的数据库的结构,但是当我尝试访问它时,它给了我这个错误消息。

I did try mysql-upgrade --force and deleting rm ib_logfile1 ib_logfile0

我确实尝试了mysql-upgrade --force并删除了rm ib_logfile1 ib_logfile0

The data loss is not a problem here, the problem is that I can't spend 30 minutes on re-installing each database each time I do a reboot.

这里的数据丢失不是问题,问题是我每次重启时都不能花30分钟重新安装每个数据库。

Here's some logs :

这是一些日志:

140730  9:24:13 [Note] Server socket created on IP: '127.0.0.1'.
140730  9:24:14 [Note] Event Scheduler: Loaded 0 events
140730  9:24:14 [Warning] InnoDB: Cannot open table mysql/gtid_slave_pos from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
140730  9:24:14 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine
140730  9:24:14 [Note] /usr/local/Cellar/mariadb/10.0.12/bin/mysqld: ready for connections.
Version: '10.0.12-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew
140730 16:26:28 [Warning] InnoDB: Cannot open table db/site from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

3 个解决方案

#1

Something has deleted your ibdata1 file where InnoDB keeps the dictionary. Definitely it's not MySQL who does it.

有些东西删除了你的ibdata1文件,其中InnoDB保存了字典。绝对不是MySQL做的。

#2

Ok folks, I ran into this problem this weekend when my OpenStack environment crashed. Another post about that coming soon on how to recover.

好的伙计们,本周末,当我的OpenStack环境崩溃时,我遇到了这个问题。关于如何恢复即将到来的另一篇文章。

I found a solution that worked for me with a SQL Server instance running under the Ver 15.1 Distrib 10.1.21-MariaDB with Fedora 25 Server as the host. Do not listen to all the other posts that say your database is corrupted if you completely copied your old mariadb-server's /var/lib/mysql directory and the database you are copying is not already corrupted. This process is based on a system where the OS became corrupted but its files were still accessible.

我发现了一个适用于我的解决方案,其中一个SQL Server实例运行在Ver 15.1 Distrib 10.1.21-MariaDB下,Fedora 25 Server作为主机。如果您完全复制了旧的mariadb-server的/ var / lib / mysql目录并且您正在复制的数据库尚未损坏,请不要收听所有其他帖子说您的数据库已损坏。此过程基于操作系统已损坏但其文件仍可访问的系统。

Here are the steps I followed.

以下是我遵循的步骤。

Make sure that you have completely uninstalled any current versions of SQL only on the NEW server. Also, make sure ALL mysql-server or mariadb-server processes on the NEW AND OLD servers have been halted by running:
确保仅在NEW服务器上完全卸载了任何当前版本的SQL。此外,通过运行以下命令确保NEW AND OLD服务器上的所有mysql-server或mariadb-server进程已停止:
service mysqld stop or service mariadb stop.
服务mysqld停止或服务mariadb停止。
On the NEW SQL server go into the /var/lib/mysql directory and ensure that there are no files at all in this directory. If there are files in this directory then your process for removing the database server from the new machine did not work and is possibly corrupted. Make sure it completely uninstalled from the new machine.
在NEW SQL服务器上,进入/ var / lib / mysql目录并确保此目录中根本没有文件。如果此目录中有文件,则从新计算机中删除数据库服务器的过程不起作用,可能已损坏。确保从新机器上完全卸载它。
On the OLD SQL server:
在OLD SQL服务器上:
mkdir /OLDMYSQL-DIR cd /OLDMYSQL-DIR tar cvf mysql-olddirectory.tar /var/lib/mysql gzip mysql-olddirectory.tar
mkdir / OLDMYSQL-DIR cd / OLDMYSQL-DIR tar cvf mysql-olddirectory.tar / var / lib / mysql gzip mysql-olddirectory.tar
Make sure you have sshd running on both the OLD and NEW servers. Make sure there is network connectivity between the two servers.
确保在旧服务器和新服务器上都运行sshd。确保两台服务器之间存在网络连接。
On the NEW SQL server:
在新的SQL服务器上:
mkdir /NEWMYSQL-DIR
On the OLD SQL server:
在OLD SQL服务器上:
cd /OLDMYSQL-DIR scp mysql-olddirectory.tar.gz @:/NEWMYSQL-DIR
cd / OLDMYSQL-DIR scp mysql-olddirectory.tar.gz @:/ NEWMYSQL-DIR
On the NEW SQL server:
在新的SQL服务器上:
cd /NEWMYSQL-DIR gunzip mysql-olddirectory.tar.gz OR tar zxvf mysql-olddirectory.tar.gz (if tar zxvf doesn't work) tar xvf mysql-olddirectory.tar.gz
cd / NEWMYSQL-DIR gunzip mysql-olddirectory.tar.gz或tar zxvf mysql-olddirectory.tar.gz(如果tar zxvf不起作用)tar xvf mysql-olddirectory.tar.gz
You should now have a "mysql" directory file sitting in the NEWMYSQL-DIR. Resist the urge to run a "cp" command alone with no switches. It will not work. Run the following "cp" command and ensure you use the same switches I did.
您现在应该在NEWMYSQL-DIR中有一个“mysql”目录文件。抵制单独运行“cp”命令而没有开关的冲动。不起作用。运行以下“cp”命令并确保使用我所做的相同开关。
cd mysql/ cp -rfp * /var/lib/mysql/
cd mysql / cp -rfp * / var / lib / mysql /
Now you should have a copy of all of your old SQL server files on the NEW server with permissions in tact. On the NEW SQL server:
现在,您应该拥有新服务器上所有旧SQL服务器文件的副本,并且具有权限。在新的SQL服务器上:
cd /var/lib/mysql/

VERY IMPORTANT STEP. DO NOT SKIP

非常重要的一步。不要跳过

> rm -rfp ib_logfile*

Now install mariadb-server or mysql-server on the NEW SQL server. If you already have it installed and/or running then you have not followed the directions and these steps will fail.

现在在NEW SQL服务器上安装mariadb-server或mysql-server。如果您已经安装和/或运行它,那么您没有按照说明进行操作,这些步骤将失败。

FOR MARIADB-SERVER and DNF:

对于MARIADB-SERVER和DNF:

> dnf install mariadb-server
> service mariadb restart

FOR MYSQL-SERVER and YUM:

对于MYSQL-SERVER和YUM:

> yum install mysql-server
> service mysqld restart

#3

You may try to:

您可以尝试:

backup your database folder from C:\xampp\mysql\data (.frm & .ibd files only corresponding your table names)

从C:\ xampp \ mysql \ data备份数据库文件夹(.frm和.ibd文件仅对应您的表名)

reinstall xampp and recopy DB folder at C:\xampp\mysql\data

在C:\ xampp \ mysql \ data重新安装xampp和recopy DB文件夹

modify my.ini add
修改my.ini添加
```
[mysqld]
innodb_file_per_table = on
```
then open phpmyadmin (or any other DB viewer as Navicat or MYSQL Workbench) and run
然后打开phpmyadmin(或任何其他数据库查看器,如Navicat或MYSQL Workbench)并运行
```
ALTER TABLE tbl_name IMPORT TABLESPACE
```
for each table
对于每个表
Once you can open your tables make a full mysql dump
一旦你可以打开你的表做一个完整的mysql转储
delete everything and make a clean install