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

Posted on

 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%';

9. 导入数据库逻辑备份：

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

10. 导入数据库用户授权：

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

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

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

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

— END —

View Comments ...