InnoDB表存在于MySQL中，但表示将数据库复制到新服务器后它们不存在 [英] InnoDB tables exist in MySQL but says they do not exist after copying database to new server

问题描述

我使用mysqldump导出数据库，然后将其导入到另一台服务器上的MySQL中.现在，如果我执行显示表格"，则可以看到我的所有表格，但实际上无法选择或描述其中的任何表格.

错误1146(42S02):表'mydatabase.user'不存在

我所有的表都是InnoDB.我看到一个人在哪里使用old_passwords的问题，因此我在my.cnf中将其明确设置为0，并确保mysql表中的所有密码均为41个十六进制数字，因为新密码应为十六进制数字. >

解决方案

显示表格"的原因起作用是因为mysqld将仅在数据库目录中扫描.frm文件.只要它们存在，它就会看到表定义.

如果您将数据导入MySQL并发生此错误消息，我将立即执行的第一件事是运行以下命令:(顺便说一句，这是MySQL 5.1.45，但仍可在MySQL 5.x中运行)

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

如果将数据导入到的服务器显示InnoDB已禁用，那么您将遇到大问题.这是您应该做的:

1)删除新导入数据库服务器中的所有数据

2)清理InnoDB设置

3)运行SHOW ENGINES；并确保InnoDB完全正常运行！！！

4)将mysqldump重新加载到新的导入服务器中

尝试一下！

I used mysqldump to export my database and then I imported it into MySQL on my other server. I can now see all my tables if I do "show tables" but I can't actually select from or describe any of them.

ERROR 1146 (42S02): Table 'mydatabase.user' doesn't exist

All of my tables are InnoDB. I saw one issue people had where they were using old_passwords, so I explicitly set that to 0 in my.cnf and I made sure all of the passwords in the mysql table were 41 hexadecimal digits as they should be for the new passwords.

解决方案

The reason "show tables;" works is because mysqld will scan the database directory for .frm files only. As long as they exist, it sees a table definition.

If you imported the data into MySQL and this error message happens, the first thing I would immediately do is run this command: (BTW This is MySQL 5.1.45, but works in MySQL 5.x anyway)

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

If the server you imported the data into says InnoDB is disabled, then you have a big problem. Here is what you should do:

1) Drop all the Data from the New Import DB Server

2) Cleanup InnoDB Setup

3) run SHOW ENGINES; and make sure InnoDB is fully operational !!!

4) Reload the mysqldump into the new import server

Give it a Try !!!

这篇关于InnoDB表存在于MySQL中，但表示将数据库复制到新服务器后它们不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！