MySQL数据库表空间高水位回收,并切换至独立表空间流程

 Translate this page into English

MySQL在5.5版本后引入了更高级的InnoDB表处理引擎,其支持以表为单位创建独立的表空间文件。但是在5.5版本中,该特性默认为未启用;或者低版本升级上来的数据库,表引擎从MyISAM转换为了InnoDB,其表空间依旧为共享类型。

随着表数据量的不断增长,这张共享的表空间大小也会随之变大。有别于独立表空间文件,共享表空间文件并不能通过OPTIMIZE TABLE方式进行释放,所以当物理空间不足时,需要通过一次完整的导出导入操作来降低高水位,释放物理空间。同时,建议一并进行表空间类型切换,方便后续维护管理。

问题描述

MySQL表空间文件(一般为ibdata1)占用过多的物理空间,但实际表的数据大小已经小于表空间所分配的大小,表空间占用无法得到释放。且经查,数据库的表空间使用类型为“共享”。

查询所有表的数据大小:

MySQL> SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',CONCAT(ROUND(table_rows/1000000,4),'M Lines') AS 'Number of Rows',CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size',CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size',CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'FROM information_schema.TABLES ORDER BY total DESC;

环境说明

环境适用于MySQL 5.5及以上版本,且数据库表使用的引擎为InnoDB。数据库默认信息如下:

  • 配置文件位置:/etc/my.cnf
  • 端口:3306
  • 数据库实例用户名:root
  • 数据库实例密码:password
  • MySQL运行用户:mysql
  • 数据库存储路径:/mysql/data/

操作步骤

0. 记录现有数据库用户授权,以备导入之用:

MySQL> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
MySQL> show grants for 'root'@'%';

记录下列出的SQL语句(即授权语句)。

1. 逻辑导出整个数据库:

$ mysqldump -uroot -ppassword --single-transaction --all-databases > /mysql/dump/mysqldump.sql

2. 停止数据库:

$ service mysql stop

3. 移动数据库文件(清空现有数据库),供备用:

$ mv -r /mysql/data/ /bak/mysql/data/

4. 将数据库表空间的使用类型从“共享”切换至“独立”。

修改配置文件/etc/my.cnf参数:

[mysqld]
innodb_file_per_table=1

5. 初始化一个空的数据库:

$ mysql_install_db --user=mysql --datadir=/mysql/data/ --pid-file=/mysql/data/mysql.pid --tmpdir=/tmp

注:如果初始化失败,提示ERROR: 1406 Data too long for column 'url' at row 1,则检查my.cnf中是否开启了如下参数,如果有则注释掉,在初始化完毕后再取消注释。

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

6. 修正/mysql/data下的文件属性,并重置数据库root用户密码:

$ chown -R mysql:mysql /mysql/data/*
$ mysqladmin -u root password 'password'

7. 启动数据库:

$ service mysql start

8. 确认表空间使用类型更改已生效:

MySQL> show variables like '%per_table%';

8. 导入数据库逻辑备份:

$ mysql -uroot -ppassword < /mysql/dump/mysqldump.sql

9. 导入数据库用户授权:

以root为例,其他用户根据实际需求分配权限。

MySQL> GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
MySQL> FLUSH PRIVILEGES;

10. 测试数据库是否能正常连接使用。

至此,数据库表空间的高水位情况得以解除,占用的磁盘空间已经释放。

END

0