主页 / dba / 问题 / 11043
Accepted
Ran
Asked: 2012-01-21 05:31:00 +0800 CST

MySql - 为实时数据库更改 innodb_file_per_table

  • 772

我有一个大型 MySql 数据库（150GB），直到现在我才注意到innodb_file_per_table设置为off导致整个数据库托管在一个文件（ibdata1）上。我想激活innodb_file_per_table并让它追溯地将数据库分成几个文件，最好的方法是什么？

mysql innodb

2 个回答

  1. Best Answer

    实际上只有一种方法可以解决这个问题。您必须使用 mysqldumps 导出数据，删除所有数据库，关闭 mysqld，删除 ib_logfile0，删除 ib_logfile1，删除 ibdata1，innodb_file_per_table在[mysqld]标题下添加，启动 mysql。

    我于 2010 年 10 月在 StackOverflow 中发布了这个答案

    以下是垂直列出的步骤：

    步骤 01) MySQLDump 所有数据库到一个 SQL 文本文件（称为 SQLData.sql）

    步骤02）删除所有数据库（mysql模式除外）

    步骤 03) 关闭 mysql

    CAVEAT：要从 InnoDB 文件中完全清除未提交的事务，请运行此

    mysql -uroot -p... -Ae"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop

    步骤 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

    此时，/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。

    试试看。如果您对此还有其他问题，请给我发电子邮件。相信我。这将在短期和长期有效。！！！

    有一种替代方法可以在不收缩 ibdata1 的情况下提取 InnoDB 表。

    步骤 01) 将以下行添加到 /etc/my.cnf

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

    步骤 02)service mysql restart

    步骤 03) 要提取名为 mydb.mytable 的单个 InnoDB 表，请执行以下操作：

    ALTER TABLE mydb.mytable ENGINE=InnoDB;

    这将创建一个文件 pleus 保留原始结构文件

    • /var/lib/mysql/mydb/mytable.frm
    • /var/lib/mysql/mydb/mytable.ibd

    您可以对每个 InnoDB 表执行此操作。不幸的是，ibdata1 将保持 150GB。

    • 33

  2. Rolando指出，如果您想回收 ibdata 的空间，则转储/恢复是您唯一的选择。执行此操作也可能是最好的。

    但是，如果您只是想减少损失并“丢失”硬盘上的 150GB，您可以简单地innodb_file_per_table在 my.cnf 中启用并重新启动服务器。

    然后对于每个表，发出：

    ALTER TABLE x DISABLE KEYS;
ALTER TABLE x ENGINE=InnoDB;
ALTER TABLE x ENABLE KEYS;

    这里的问题是大型表空间需要一段时间。

    我的建议是设置你的实时数据库的从属，在从属上运行转换，然后关闭主/从并将新的数据空间复制到主，或者一旦它赶上就将它提升为主.

    您将很难在没有停机的情况下进行此更改。

    • 5

相关问题