mysql存储文件_mysql下存储文件问题

问题：磁盘空间报警，经查发现ibdata1和mysql-bin日志占用空间太多(其中ibdata1超过120G，mysql-bin超过80G)

原因：ibdata1是存储格式，在INNODB类型数据状态下，ibdata1用来存储文件的数据和索引，而库名的文件夹里的那些表文件只是结构而已。

innodb存储引擎有两种表空间的管理方式，分别是：

1)共享表空间(可拆分为多个小的表空间文件)，这个是我们目前多数数据库使用的方法；

2)独立表空间，每一个表有一个独立的表空间(磁盘文件)

对于两种管理方式，各有优劣，具体如下：

①共享表空间：

优点：可以将表空间分成多个文件存放到不同的磁盘上(表空间文件大小不受表大小的限制，一个表可以分布在不同步的文件上)。

缺点：所有数据和索引存放在一个文件中，则随着数据的增加，将会有一个很大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样如果对于一个表做了大量删除操作后表空间中将有大量空隙。对于共享表空间管理的方式下，一旦表空间被分配，就不能再回缩了。当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了。

②独立表空间：在配置文件(my.cnf)中设置： innodb_file_per_table

特点：每个表都有自已独立的表空间；每个表的数据和索引都会存在自已的表空间中。

优点：表空间对应的磁盘空间可以被收回(Drop table操作自动回收表空间，如果对于删除大量数据后的表可以通过:alter table tbl_name engine=innodb;回缩不用的空间。

缺点：如果单表增加过大，如超过100G，性能也会受到影响。在这种情况下，如果使用共享表空间可以把文件分开，但有同样有一个问题，如果访问的范围过大同样会访问多个文件，一样会比较慢。如果使用独立表空间，可以考虑使用分区表的方法，在一定程度上缓解问题。此外，当启用独立表空间模式时，需要合理调整innodb_open_files参数的设置。

解决:

1)ibdata1数据太大：只能通过dump，导出建库的sql语句，再重建的方法。

2)mysql-bin Log太大：

①手动删除：

删除某个日志：mysql>PURGE MASTER LOGS TO ‘mysql-bin.010′;

删除某天前的日志：mysql>PURGE MASTER LOGS BEFORE ’2010-12-22 13:00:00′;

②在/etc/my.cnf里设置只保存N天的bin-log日志

expire_logs_days = 30 //Binary Log自动删除的天数

MySql ibdata1文件

MySql innodb如果是共享表空间，ibdata1文件越来越大，达到了30多个G，对一些没用的表进行清空：

truncate table xxx;

然后optimize table xxx; 没有效果

因为对共享表空间不起作用。

mysql ibdata1存放数据，索引等，是MYSQL的最主要的数据。

如果不把数据分开存放的话，这个文件的大小很容易就上了G，甚至几十G。对于某些应用来说，并不是太合适。因此要把此文件缩小。

无法自动收缩，必须数据导出，删除ibdata1，然后数据导入，比较麻烦，因此需要改为每个表单独的文件。

解决方法：数据文件单独存放(共享表空间如何改为每个表独立的表空间文件)。

步骤如下：

1)备份数据库

备份全部数据库，执行命令

#mysqldump -q -uroot -ppassword --add-drop-table --all-databases >/home/backup/all.sql

做完此步后，停止数据库服务。

#service mysqld stop

2)找到my.ini或my.cnf文件

linux下执行

# /usr/libexec/mysqld --verbose --help | grep -A 1 'Default options'

Default options are read from the following files in the given order:

/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

windows环境下可以：

mysqld --verbose --help > mysqlhelp.txt

notepad mysqlhelp.txt

在里面查找Default options，可以看到查找my.ini的顺序，以找到真实目录

3)修改mysql配置文件

打开my.ini或my.cnf文件

[mysqld]下增加下面配置

innodb_file_per_table=1

验证配置是否生效，可以重启mysql后,执行

#service mysqld restart

#mysql -uroot -ppassword

mysql> show variables like '%per_table%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

1 row in set (0.00 sec)

mysql>

看看innodb_file_per_table变量是否为ON

4)删除原数据文件

删除原来的ibdata1文件及日志文件ib_logfile*，删除/var/lib/mysql目录下的应用数据库文件夹(mysql文件夹不要删)

5)还原数据库

启动数据库服务

从命令行进入MySQL Server

还原全部数据库，执行命令

#service mysqld start

#mysql -uroot -pocs < /home/backup/all.sql

经过以上几步后，可以看到新的ibdata1文件就只有几十M了，数据及索引都变成了针对单个表的小ibd文件了，它们在相应数据库的文件夹下面。

# ll

total 295028

drwx------  2 mysql mysql     36864 Apr 22 14:16 glpi

drwx------  2 mysql mysql     36864 Feb 15 13:45 glpi-1

-rw-rw----  1 mysql mysql  10485760 Apr 22 14:27 ibdata1

-rw-rw----. 1 mysql mysql 270532608 Apr 22 14:14 ibdata1-1

-rw-rw----  1 mysql mysql   5242880 Apr 22 14:27 ib_logfile0

-rw-rw----. 1 mysql mysql   5242880 Apr 22 14:14 ib_logfile0_bak

-rw-rw----  1 mysql mysql   5242880 Apr 22 14:28 ib_logfile1

-rw-rw----. 1 mysql mysql   5242880 Apr 21 22:50 ib_logfile1_bak

drwx------  2 mysql mysql      4096 Apr 22 14:16 mrbs

drwx------  2 mysql mysql      4096 Apr 14 12:05 mrbs-1

drwx------. 2 mysql mysql      4096 Apr 22 14:16 mysql

srwxrwxrwx  1 mysql mysql         0 Apr 22 14:16 mysql.sock

drwx------  2 mysql mysql     12288 Apr 22 14:16 ocsweb

drwx------  2 mysql mysql     12288 Nov 16  2011 ocsweb-1

# ll mrbs

total 808

-rw-rw---- 1 mysql mysql     61 Apr 22 14:16 db.opt

-rw-rw---- 1 mysql mysql  10492 Apr 22 14:16 mrbs_area.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_area.ibd

-rw-rw---- 1 mysql mysql   9264 Apr 22 14:16 mrbs_entry.frm

-rw-rw---- 1 mysql mysql 131072 Apr 22 14:16 mrbs_entry.ibd

-rw-rw---- 1 mysql mysql   9442 Apr 22 14:16 mrbs_repeat.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_repeat.ibd

-rw-rw---- 1 mysql mysql   8888 Apr 22 14:16 mrbs_room.frm

-rw-rw---- 1 mysql mysql 114688 Apr 22 14:16 mrbs_room.ibd

-rw-rw---- 1 mysql mysql   8688 Apr 22 14:16 mrbs_users.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_users.ibd

-rw-rw---- 1 mysql mysql   8658 Apr 22 14:16 mrbs_variables.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_variables.ibd

-rw-rw---- 1 mysql mysql   8738 Apr 22 14:16 mrbs_zoneinfo.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_zoneinfo.ibd

# ll mrbs-1

total 88

-rw-rw---- 1 mysql mysql    61 Apr 14 12:05 db.opt

-rw-rw---- 1 mysql mysql 10492 Apr 14 12:05 mrbs_area.frm

-rw-rw---- 1 mysql mysql  9264 Apr 14 12:05 mrbs_entry.frm

-rw-rw---- 1 mysql mysql  9442 Apr 14 12:05 mrbs_repeat.frm

-rw-rw---- 1 mysql mysql  8888 Apr 14 12:05 mrbs_room.frm

-rw-rw---- 1 mysql mysql  8688 Apr 14 12:05 mrbs_users.frm

-rw-rw---- 1 mysql mysql  8658 Apr 14 12:05 mrbs_variables.frm

-rw-rw---- 1 mysql mysql  8738 Apr 14 12:05 mrbs_zoneinfo.frm