≡菜单

如何使用innodb_file_per_table缩小MySQL ibdata1大小

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

MySQL系统表空间是ibdata1,位于/ var / lib / mysql下

单个ibdata1文件包含MySQL3d捕鱼达人中的所有表和索引。因此,如果您的3d捕鱼达人很大,那么该文件的大小将非常大。

在本教程中,我们’ll解释如何重建整个MySQL3d捕鱼达人,以及如何将较大的MySQL系统表空间文件分解为较小的单个MySQL表文件。

MySQL的ibdata1

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的数据后,请回收这些未使用的空间。有一种方法可以做到,但是太复杂了(在下面解释),并且涉及到关闭MySQL3d捕鱼达人。

因此,如何避免将所有表和索引存储在单个ibdata1文件中?而是分别存储在多个表文件中?

2.设置innodb_file_per_table参数

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

# 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 MySQL3d捕鱼达人。

service mysqld restart

注意:出于某种原因,如果要在3d捕鱼达人运行时设置此参数,请不要’如果要关闭MariaDB,可以从mysql提示符下进行以下全局设置。

mysql> set global innodb_file_per_table=1;

3.将新表(和索引)作为单独的文件

从现在开始,当您创建一个新的MySQL表时,’将获取单个文件。

在此示例中,我创建了一个名为employee的新表,并向其中上载了大约20GB的数据。

mysql> use 怪胎

mysql> create table employee ...

mysql> insert into employee ..

这里将发生两件事:

首先,它将使用3d捕鱼达人名称创建一个子目录“thegeekstuff”在/ var / lib / mysql目录下

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

其次,在该3d捕鱼达人目录名称下(即thegeekstuff目录下),您可以’将看到单个文件EMPLOYEE.IBD正在创建。该文件的大小将是您仅上传到该表的数据的大小。在这种情况下,由于我们将价值20GB的数据上传到该表中,因此EMPLOYEE.IBD文件大小约为20GB,如下所示。

# cd / var / lib / mysql /thegeekstuff/

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

注意:如果您使用的是MyISAM3d捕鱼达人,则您’将看到单独的.MYD,.FRM和.MYI文件

4.从ibdata1中提取现有表

接下来,如果要从ibdata1中提取现有表到它’自己的单个文件,那么您必须优化表。

假设您在thegeekstuff3d捕鱼达人下有一个名为Benefits的表。该收益表是在我们在my.cnf中设置innodb_file_per_table之前创建的。

因此,收益表仍将位于ibdata1文件下。将此内容从ibdata1中移出’自己的IBD文件,我们必须优化表,如下所示。

mysql> use 怪胎

mysql> optimize table benefits;

这将为权益表创建以下单个文件。

# cd / var / lib / mysql /thegeekstuff/

# 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.备份3d捕鱼达人

首先,暂时停止mysql3d捕鱼达人,然后对整个3d捕鱼达人进行冷备份。万一出问题了,您可以使用此冷备份进行还原。

mkdir /backup

cd /var/lib

cp -r mysql /backup

其次,对所有3d捕鱼达人进行mysqldump备份。

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

有关mysqldump的详细信息,请参阅 如何 use mysqldump 文章。

7.删除所有3d捕鱼达人

接下来,一个一个地删除所有3d捕鱼达人。要查看所有要删除的3d捕鱼达人,请使用“show databases”

mysql> 显示3d捕鱼达人;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 怪胎       |
| sales              |
| mysql              |
+--------------------+

在此示例中,我们删除了mysql中存在的两个3d捕鱼达人(thegeekstuff和sales)。

# mysql -u root -ptmppassword
mysql> drop database 怪胎;

mysql> drop database sales;

注意:不要删除information_schema和mysql3d捕鱼达人。

8.删除ibdata和ib_logfile

接下来,关闭MySQL3d捕鱼达人。

service mysqld stop

接下来,除去ibdata1文件和所有单独的ib_logfile *文件:

cd / var / lib / mysql /

rm ibdata1

rm ib_logfile0

rm ib_logfile1

9.导入所有3d捕鱼达人

注意:此时,您的my.cnf文件中应该已经包含以下内容。

[mysqld]
innodb_file_per_table

启动MySQL3d捕鱼达人。

service mysqld start

从我们之前进行的mysqldump备份中导入所有3d捕鱼达人。

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

在此阶段,将从头开始创建ibdata1文件(即MySQL系统表空间),在我们的示例中,它将不再是100GB。

现在,ibdata1的大小将只有几个MB。所有3d捕鱼达人表都将作为单独的文件存储在/ var / lib / mysql /下的相应3d捕鱼达人子目录下

如果您喜欢这篇文章,您可能还会喜欢..

  1. 50个Linux Sysadmin教程
  2. 50个最常用的Linux命令(包括示例)
  3. 排名前25位的最佳Linux性能监视和调试工具
  4. 妈妈,我找到了! – 15个实用的Linux Find命令示例
  5. Linux 101 Hacks第二版电子书 Linux 101黑客手册

Bash 101 Hacks书 Sed和Awk 101黑客手册 Nagios Core 3书 Vim 101黑客手册

{ 9 评论… 加一 }

  • 马可 2016年2月18日,上午2:59

    谢谢拉梅什,太好了“how to” as always!
    我有一个问题。
    有可能还是更好的喷气机将工作:
    我有一些3d捕鱼达人。如果我用rsync备份整个目录,如下所示:
    rsync -a–delete-after ‘/var/lib/mysql/’ ‘/backup/mysql’

    我可以从该备份中使用rsync还原3d捕鱼达人吗?如果我还原所有这些3d捕鱼达人或仅还原其中一个3d捕鱼达人,则在还原后mysql是否可以工作?

    谢谢。

  • 乔瑟 2016年2月18日,上午10:12

    因为我没有’看完这篇文章,沉淀似乎很棒。可以认为mySQL提到的文件有多大’不能正常工作或感觉不到性能

  • 匿名 2016年2月18日,上午10:55

    优化步骤是否必要?韩元’在启用innodb_file_per_table设置的情况下,看似强制性的转储,删除,还原过程会自行创建单个数据文件吗?

  • 翻转 2016年8月17日,上午3:25

    怎么样呢‘performance_schema’3d捕鱼达人?是否也应该在#7处将其删除?还是应该留在原地,就像‘information_schema’?

  • 夏莎 2016年11月30日,上午4:03

    嗨!
    您需要重新访问导入命令恕我直言

    mysql命令不接受标志–all-databases
    (至少在v5.5.53中)

    我遇到以下错误:

    root @ test:/ var / lib / mysql#mysql -u debian-sys-maint -p–all-databases <./all_databases_backup_deb.sql
    mysql:未知选项'–all-databases'

  • 史蒂夫 2017年3月1日,晚上11:15

    感谢您的指导。它救了我的皮。

  • 罗伯特·皮特拉(Robert Pitera) 2017年4月9日,上午10:42

    万一有人避风港’t弄清楚了,只需删除–所有3d捕鱼达人参数工作正常。

  • 奥马尔 2017年5月3日,上午6:18

    但是如何处理每个表中的文件?当我从表中删除行时会发生什么,会减少该表的文件吗?因为这是我们试图解决的问题。

  • 系统管理员 2017年5月8日,下午6:39

    不起作用。

    [email protected] mysql]# mysql -u root -p 所有3d捕鱼达人</backup/all-database.sql
    输入密码:
    错误1049(42000):未知3d捕鱼达人'all-databases'

发表评论