首页 / MYSQL / 如何从MySQL中完全删除表空间？
如何从MySQL中完全删除表空间？
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了如何从MySQL中完全删除表空间？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10343字，纯文字阅读大概需要15分钟。
内容图文
我有一个名为camera_main_live的MySQL数据库,我使用Adminer进行管理.从外观来看,它是空的.因此,当我想添加一些内容时,我首先运行此脚本(将Adminer设置为在出错时停止)：
DROP TABLE IF EXISTS `Addresses`;
CREATE TABLE `Addresses` (
`Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`UserId` int(10) unsigned NOT NULL,
`FirstName` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`LastName` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Address` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Street` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Town` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`State` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Code` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`Country` char(50) COLLATE utf8_unicode_ci DEFAULT 'Ireland',
`Billing` tinyint(3) unsigned DEFAULT '0',
`Shipping` tinyint(3) unsigned DEFAULT '0',
PRIMARY KEY (`Id`),
KEY `IndexUserId` (`UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED;
Error in query (1813): Tablespace for table ‘`camera_main_live`.`Addresses`’ exists. Please DISCARD the tablespace before IMPORT.
好的,让我们试着澄清一下.
ALTER TABLE `Addresses` DROP TABLESPACE;
Error in query (1146): Table ‘camera_main_live.Addresses’ doesn’t exist
我们似乎陷入了僵局.
此时我们放弃SQL命令并尝试不同的东西：
root@Web-Dev-HP-ProDesk:/var/lib/mysql/camera_main_live# ll
total 76
drwx------ 2 mysql mysql 4096 Jun 23 14:44 ./
drwx------ 13 mysql mysql 4096 Jun 23 11:12 ../
-rw-rw---- 1 mysql mysql 65536 Jun 10 14:09 Addresses.ibd
-rw-rw---- 1 mysql mysql 61 Jun 16 12:43 db.opt
啊,所以有问题！
root@Web-Dev-HP-ProDesk:/var/lib/mysql/camera_main_live# rm Addresses.ibd
root@Web-Dev-HP-ProDesk:/var/lib/mysql/camera_main_live# ll
total 12
drwx------ 2 mysql mysql 4096 Jun 23 14:48 ./
drwx------ 13 mysql mysql 4096 Jun 23 11:12 ../
-rw-rw---- 1 mysql mysql 61 Jun 16 12:43 db.opt
但是没有！ CREATE TABLE命令抛出相同的错误.并重新创建Addresses.ibd.让我们尝试一些更激烈的事情.
root@Web-Dev-HP-ProDesk:/var/lib/mysql/camera_main_live# cd ..
root@Web-Dev-HP-ProDesk:/var/lib/mysql# rm -r camera_main_live/
然后,
CREATE DATABASE `camera_main_live` COLLATE 'utf8_unicode_ci';
全新的数据库.它必须干净而空,对吗？不,不是.我再次得到同样的错误：表空间仍然存在.它的翻转是什么,我怎么能摆脱它？
# mysql --version
mysql Ver 14.14 Distrib 5.6.24, for debian-linux-gnu (x86_64) using EditLine wrapper
# uname --all
Linux Web-Dev-HP-ProDesk 3.19.0-21-generic #21-Ubuntu SMP Sun Jun 14 18:31:11 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 15.04
Release: 15.04
Codename: vivid
我在几个不同的本地数据库上看到了这一点：每次都有不同的表.截至今天,camera_main_live似乎已经修复了自己,这更令人困惑.但问题仍存在于其他地方.例如,在数据库测试中,
CREATE TABLE `ProductsRelatedTypes` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Position` int(11) NOT NULL,
`Title` char(100) COLLATE utf8_unicode_ci NOT NULL,
`Alert` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Error in query (1813): Tablespace for table ‘`test`.`ProductsRelatedTypes`’ exists. Please DISCARD the tablespace before IMPORT.
DESC `ProductsRelatedTypes`
Error in query (1146): Table ‘test.ProductsRelatedTypes’ doesn’t exist
SELECT * FROM `ProductsRelatedTypes`
Error in query (1146): Table ‘test.ProductsRelatedTypes’ doesn’t exist
SHOW TABLES LIKE "ProductsRelatedTypes"
No rows.
root@Web-Dev-HP-ProDesk:/var/lib/mysql/test# ll | grep Related
-rw-rw---- 1 mysql mysql 8610 Jun 29 15:06 ProductsRelated.frm
-rw-rw---- 1 mysql mysql 0 Jun 29 15:06 ProductsRelated.MYD
-rw-rw---- 1 mysql mysql 1024 Jun 29 15:06 ProductsRelated.MYI
-rw-rw---- 1 mysql mysql 65536 Jun 29 11:12 ProductsRelatedTypes.ibd
root@Web-Dev-HP-ProDesk:/var/lib/mysql/test# rm ProductsRelatedTypes.ibd
CREATE TABLE `ProductsRelatedTypes` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Position` int(11) NOT NULL,
`Title` char(100) COLLATE utf8_unicode_ci NOT NULL,
`Alert` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Error in query (1050): Table ‘`test`.`ProductsRelatedTypes`’ already exists
咦？
DESC `ProductsRelatedTypes`
Error in query (1146): Table ‘test.ProductsRelatedTypes’ doesn’t exist
SELECT * FROM `ProductsRelatedTypes`
Error in query (1146): Table ‘test.ProductsRelatedTypes’ doesn’t exist
SHOW TABLES LIKE "ProductsRelatedTypes"
No rows.
root@Web-Dev-HP-ProDesk:/var/lib/mysql/test# ll | grep Related
-rw-rw---- 1 mysql mysql 8610 Jun 29 15:06 ProductsRelated.frm
-rw-rw---- 1 mysql mysql 0 Jun 29 15:06 ProductsRelated.MYD
-rw-rw---- 1 mysql mysql 1024 Jun 29 15:06 ProductsRelated.MYI
-rw-rw---- 1 mysql mysql 65536 Jun 29 15:13 ProductsRelatedTypes.ibd
所以当.ibd文件存在时,我被告知表空间已经存在.当我删除它时,我被告知该表已经存在,即使它显然没有.让我困惑的颜色.
查询information_schema,我得到这个信息：
mysql> SELECT * FROM `INNODB_SYS_TABLES` WHERE `Name` LIKE "%Address%";
+----------+--------------------------------------+------+--------+-------+-------------+------------+---------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+----------+--------------------------------------+------+--------+-------+-------------+------------+---------------+
| 2826 | camera_main_live2/Addresses | 1 | 15 | 2812 | Antelope | Compact | 0 |
| 3789 | ronayne_mytools_main_live2/Addresses | 1 | 16 | 3775 | Antelope | Compact | 0 |
| 1312 | test/Addresses | 1 | 15 | 1298 | Antelope | Compact | 0 |
+----------+--------------------------------------+------+--------+-------+-------------+------------+---------------+
3 rows in set (0.01 sec)
mysql> SELECT * FROM `INNODB_SYS_TABLESPACES` WHERE `Name` LIKE "%Address%";
+-------+--------------------------------------+------+-------------+----------------------+-----------+---------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE |
+-------+--------------------------------------+------+-------------+----------------------+-----------+---------------+
| 1298 | test/Addresses | 0 | Antelope | Compact or Redundant | 16384 | 0 |
| 2812 | camera_main_live2/Addresses | 0 | Antelope | Compact or Redundant | 16384 | 0 |
| 3775 | ronayne_mytools_main_live2/Addresses | 0 | Antelope | Compact or Redundant | 16384 | 0 |
+-------+--------------------------------------+------+-------------+----------------------+-----------+---------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM `TABLESPACES`;
Empty set (0.00 sec)
在这些中都没有任何迹象表明存在camera_main_live.Addresses.对information_schema.TABLES的任何查询都会失败：
Error in query (1018): Can’t read dir of ‘.’ (errno: 24 – Too many open files)
现在,重新启动计算机后,
mysql> SHOW STATUS LIKE 'open_%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Open_files | 61 |
| Open_streams | 0 |
| Open_table_definitions | 90 |
| Open_tables | 84 |
| Opened_files | 191 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
+--------------------------+-------+
7 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE "table_open_cache";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 431 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE "innodb_open_files";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_open_files | 431 |
+-------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE "open_files_limit";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 1024 |
+------------------+-------+
1 row in set (0.01 sec)
尝试从命令提示符创建表：
mysql> CREATE TABLE `Addresses` ( `Id` int(10) unsigned NOT NULL AUTO_INCREMENT, `UserId` int(10) unsigned NOT NULL, `FirstName` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `LastName` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `Address` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `Street` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `Town` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `State` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `Code` char(30) COLLATE utf8_unicode_ci DEFAULT NULL, `Country` char(50) COLLATE utf8_unicode_ci DEFAULT 'Ireland', `Billing` tinyint(3) unsigned DEFAULT '0', `Shipping` tinyint(3) unsigned DEFAULT '0', PRIMARY KEY (`Id`), KEY `IndexUserId` (`UserId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED;
ERROR 1813 (HY000): Tablespace for table '`test`.`Addresses`' exists. Please DISCARD the tablespace before IMPORT.
mysql> SHOW WARNINGS; +---------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
| Error | 1813 | Tablespace for table '`test`.`Addresses`' exists. Please DISCARD the tablespace before IMPORT. |
| Error | 1030 | Got error 184 from storage engine |
+---------+------+------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
解决方法:
解决此问题的步骤(至少在Windows操作系统中)：
>停止MySQL服务
>删除table.ibd文件
>将所有文件从MySQL Data文件夹移动到备份文件夹
>启动MySQL服务(将再次创建所有主文件)
>创建您需要的表格
内容总结
以上是互联网集市为您收集整理的如何从MySQL中完全删除表空间？全部内容，希望文章能够帮你解决如何从MySQL中完全删除表空间？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。