MySql - 為實時數據庫更改 innodb_file_per_table

我有一個大型 MySql 數據庫（150GB），直到現在我才注意到innodb_file_per_table設置為off導致整個數據庫託管在一個文件（ibdata1）上。我想啟動innodb_file_per_table並讓它追溯地將數據庫分成幾個文件，最好的方法是什麼？

實際上只有一種方法可以解決這個問題。您必須使用 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。

引用自：https://dba.stackexchange.com/questions/11043