减少mysql中ibdata大小的最佳方法是什么？

#1 楼

请记住，InnoDB基础结构中最繁忙的文件是/ var / lib / mysql / ibdata1

该文件通常包含许多类的信息（当innodb_file_per_table为0时）


表数据
表索引
MVCC（多版本并发控制）数据


回滚段
撤消表空间


表元数据
请参阅图片表示法


许多人创建了多个ibdata文件，希望可以实现更好的磁盘空间管理和性能。

不幸的是，针对存储在ibdata1中的InnoDB表的OPTIMIZE TABLE有两件事：


使表的数据和索引在ibdata1内部连续br />它使ibdata1增长，因为连续数据被附加到ibdata1中。

您可以将ibdata1中的表数据和表索引分离出来，并使用innodb_file_per_table对其进行独立管理。
一次又一次地收缩ibdata1您必须执行以下操作

步骤01）MySQL将所有数据库转储到SQL文本文件中（称为SQLData.sql）（此处有更多详细信息）

步骤02）全部删除数据库（mysql，performance_schema和information_schema除外）

步骤03）关闭mysql

步骤04）将以下行添加到/etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

侧面注：无论您为innodb_buffer_pool_size设置什么，请确保innodb_log_file_size为innodb_buffer_pool_size的25％。

步骤05）删除ibdata1，ib_logfile0和ib_logfile1

在thi如此看来，/ var / lib / mysql
中应该只有mysql模式

步骤06）重新启动mysql

这将重新创建10MB的ibdata1、1G的ib_logfile0和ib_logfile1每个

步骤07）将SQLData.sql重新加载到mysql中

ibdata1将增长，但仅包含表元数据

每个InnoDB表将存在于ibdata1之外

假设您有一个名为mydb.mytable的InnoDB表。如果进入/ var / lib / mysql / mydb，将看到代表该表的两个文件


mytable.frm（存储引擎标题）
mytable.ibd（主页表数据和mydb.mytable的表索引）

ibdata1将不再包含InnoDB数据和索引。

在/etc/my.cnf中使用innodb_file_per_table选项，您可以可以运行OPTIMIZE TABLE mydb.mytable并且文件/var/lib/mysql/mydb/mytable.ibd实际上会缩小。

我作为MySQL DBA在我的职业生涯中做了很多次。实际上，我第一次这样做是，我将一个50GB的ibdata1文件压缩为500MB。

尝试一下。如果您对此还有其他疑问，请给我发送电子邮件。相信我。这将在短期和长期内发挥作用。 !!!

如果您想查看MyISAM和InnoDB中存储了多少实际数据，请运行以下查询：

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;