如何使用 innodb_file_per_table 缩小 MySQL ibdata1 大小

在 MySQL 中，当你使用 InnoDB 时，所有的表和索引都存储在 MySQL 系统表空间下。

MySQL系统表空间为ibdata1，位于/var/lib/mysql下

单个 ibdata1 文件包含 MySQL 数据库中的所有表和索引。所以，如果你有一个很大的数据库，这个文件大小会变得非常大。

在本教程中，我们将解释如何重建整个 MySQL 数据库，并将大型 MySQL 系统表空间文件分解为小的单个 MySQL 表文件。

1. 大 MySQL（和 MariaDB）系统表空间

默认的大 MySQL 系统表空间方法有一个主要缺点。

以此场景为例：您已将 100GB 的数据上传到 MySQL 的多个表中。

现在，ibdata1 文件大小将约为 100GB+。

# cd /var/lib/mysql

# ls -lh ibdata1
-rw-r-----。1 mysql mysql 101G Jan 21 21:10 ibdata1

几天后，您从所有这些表中删除了大约 50GB 的数据。ibdata1 文件大小不会减少到 50GB+ 左右，它仍然会保持在 100GB+ 左右。

在上述情况下，稍后当您向表中添加 10GB 的数据时，ibdata1 文件大小不会增长到 110GB，而是保持在 100GB。因为，文件里面还有50GB以上删除数据的未使用空间。

问题是，从 ibdata1 文件中删除 50GB 的数据后，您无法回收那些未使用的空间。有一种方法可以做到，但太复杂（如下所述），并且涉及关闭 MySQL 数据库。

那么，我们如何避免将所有表和索引存储在单个 ibdata1 文件中；而是单独存储在多个表文件中？

2.设置innodb_file_per_table参数

为此，您应该在“mysqld”部分下的 /etc/my.cnf 文件中使用 innodb_file_per_table 参数，如下所示：

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

注意：如果您使用的是 MySQL 5.6.6（或 MariaDB）及更高版本，以上为默认设置。

在这个例子中，在 CentOS 6 上，你从 yum 存储库安装时得到的默认 MySQL 仍然是 5.1.73，如下所示。

# mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

因此，在这种情况下，我们应该在 my.cnf 文件中设置 innodb_file_per_table。

任何时候对 my.cnf 进行任何更改，都应该重新启动 MariaDB MySQL 数据库。

service mysqld restart

注意：出于某种原因，如果您想在数据库运行时设置此参数，并且不想关闭 MariaDB，您可以在 mysql 提示符下执行以下设置全局。

mysql> set global innodb_file_per_table=1;

3. 新表（和索引）作为单独的文件

从现在开始，当您创建新的 MySQL 表时，您将获得单个文件。

在这个例子中，我创建了一个名为employee 的新表，并向其中上传了大约 20GB 的数据。

mysql> use tiamo

mysql> create table employee ...

mysql> insert into employee ..

这里会发生两件事：

首先，它会在 /var/lib/mysql 目录下创建一个数据库名为“thegeekstuff”的子目录

# ls -l /var/lib/mysql/
drwx------. 2 mysql mysql 266240 Jan 5 12:11 tiamo
..

其次，在此数据库目录名称下（即在geekstuff 目录下），您将看到创建了单个文件EMPLOYEE.IBD。此文件的大小将是您仅上传到该表的数据的大小。在本例中，由于我们将 20GB 的数据上传到该表中，因此 EMPLOYEE.IBD 文件大小约为 20GB，如下所示。

#cd /var/lib/mysql/tiamo/

# ls -lh 
-rw-r-----。1 mysql mysql 21G Jan 21 21:17 employee.ibd

注意：如果您使用的是 MyISAM 数据库，您将看到单独的 .MYD、.FRM 和 .MYI 文件

4.从ibdata1中提取现有表

接下来，如果您想将 ibdata1 中的现有表提取到它自己的单个文件中，那么您必须优化该表。

假设您在 geekstuff 数据库下有一个名为 Benefits 的表。这个福利表是在我们在 my.cnf 中设置 innodb_file_per_table 之前创建的。

因此，好处表仍将在 ibdata1 文件下。要将其从 ibdata1 移到它自己的 IBD 文件中，我们必须优化表，如下所示。

mysql> use tiamo

mysql> optimize table benefits;

这将为福利表创建以下单个文件。

#cd /var/lib/mysql/tiamo/

# ls -lh 
-rw-r-----。1 mysql mysql 21G Jan 21 21:17 Benefits.ibd

在这个例子中，请记住原始 ibdata1 文件仍然没有缩小。它仍然在100GB左右。

# ls -lh /var/lib/mysql/ibdata1
-rw-r-----。1 mysql mysql 101G Jan 21 21:10 ibdata1

注意：您还可以执行以下操作。

mysql> alter table benefits engine=InnoDB;

5.缩小ibdata1文件大小

请记住，ibdata1 仍然保持相同的 101G 大小，它尚未减小大小。

# ls -lh /var/lib/mysql/ibdata1
-rw-r-----。1 mysql mysql 101G Jan 21 21:10 ibdata1

要缩小ibdata1文件，您需要执行以下步骤：

6. 备份数据库

首先，暂时停止mysql数据库，对整个数据库进行冷备份。如果出现问题，您可以使用此冷备份进行恢复。

mkdir /backup

cd /var/lib

cp -r mysql /backup

其次，对所有数据库进行 mysqldump 备份。

mysqldump -u root -ptmppassword --all-databases > /backup/all-database.sql

7. 删除所有数据库

接下来，一一删除所有数据库。要查看所有要删除的数据库，请使用“show databases”

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| tiamo      |
| sales              |
| mysql              |
+--------------------+

在此示例中，我们将删除 mysql 中存在的两个数据库（thegeekstuff 和 sales）。

# mysql -u root -ptmppassword
mysql> drop database tiamo;

mysql> drop database sales;

注意：不要删除information_schema 和mysql 数据库。

8.删除ibdata和ib_logfile

接下来，关闭 MySQL 数据库。

service mysqld stop

接下来，删除 ibdata1 文件和所有单独的 ib_logfile* 文件：

cd /var/lib/mysql/

rm ibdata1

rm ib_logfile0

rm ib_logfile1

9. 导入所有数据库

注意：此时，您的 my.cnf 文件中应该已经有了以下内容。

[mysqld]
innodb_file_per_table

启动 MySQL 数据库。

service mysqld start

从我们之前进行的 mysqldump 备份中导入所有数据库。

mysql -u root -ptmppassword --all-databases < /backup/all-database.sql

在这个阶段，ibdata1 文件，即 MySQL 系统表空间将从头开始创建，在我们的例子中，它不再是 100GB。

现在 ibdata1 的大小将只有几 MB。所有数据库表将作为单独的文件存储在/var/lib/mysql/下对应的数据库子目录下