删除数据库后如何回收磁盘空间?
问题:删除数据库后如何回收磁盘空间?
在InnoDB中,/var/lib/mysql/ibdata1是最繁忙的文件。它通常包含六种类型的信息:
- 表数据
- 表索引
- MVCC(多版本并发控制)数据
- 回滚段
- 回滚空间
- 表元数据(数据字典)
- 双写缓冲区(后台写入,以防依赖于操作系统缓存)
- 插入缓冲区(管理非唯一二级索引的更改)
许多人创建多个ibdata文件,希望能够更好地管理磁盘空间和提高性能,但这种观念是错误的。
运行OPTIMIZE TABLE命令对存储在共享表空间文件ibdata1中的InnoDB表进行优化会导致两个问题:
- 将表的数据和索引放在ibdata1中的连续位置
- 由于连续的数据和索引页被追加到ibdata1中,导致ibdata1的增长
但是,您可以将表数据和表索引从ibdata1中分离出来并独立管理。
如果在/etc/my.cnf(或my.ini)中添加了innodb_file_per_table选项,可以在所有InnoDB表上运行OPTIMIZE TABLE命令。运行此命令后,将为每个表生成一个.ibd文件。例如,如果有一个名为mydb.mytable的表,并且datadir为/var/lib/mysql,则会生成以下文件:
- /var/lib/mysql/mydb/mytable.frm
- /var/lib/mysql/mydb/mytable.ibd
.ibd文件包含该表的数据页和索引页。但是,请注意,每个表的数据字典条目仍然存在于数据字典中。此时不能简单地删除ibdata1,因为ibdata1的大小并没有缩小。
要彻底缩小ibdata1,必须执行以下步骤:
1. 将所有数据库导出到.sql文本文件中(例如SQLData.sql)
2. 删除所有数据库(除了mysql和information_schema)
3. 登录mysql并运行SET GLOBAL innodb_fast_shutdown = 0;(将从ib_logfile0和ib_logfile1完全刷新所有剩余的事务更改)
4. 关闭MySQL
5. 在/etc/my.cnf(或my.ini)中添加以下行:
[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
6. 删除ibdata*和ib_logfile*,可选择地,您可以删除/var/lib/mysql中的所有文件夹,除了/var/lib/mysql/mysql。
7. 启动MySQL(这将重新创建默认大小为10MB的ibdata1和1G的ib_logfile0和ib_logfile1)
8. 导入SQLData.sql
现在,ibdata1仍然会增长,但只包含表的元数据,因为每个InnoDB表都存在于ibdata1之外。ibdata1将不再包含其他表的InnoDB数据和索引。
例如,假设有一个名为mydb.mytable的InnoDB表。如果在/var/lib/mysql/mydb中查看,将会看到代表该表的两个文件:
- mytable.frm(存储引擎头)
- mytable.ibd(表的数据和索引)
使用/etc/my.cnf中的innodb_file_per_table选项,可以运行OPTIMIZE TABLE mydb.mytable命令,而/var/lib/mysql/mydb/mytable.ibd文件的大小将会缩小。
我在我的职业生涯中多次使用过此方法。事实上,第一次使用此方法时,我将一个50GB的ibdata1文件缩小到了只有500MB!
这个方法非常有效。我们的两台机器上的磁盘空间不足,这个方法清理了大约三分之一的磁盘空间。虽然删除数据库有点可怕,但它确实有效!我们甚至看到了轻微的性能提升。
值得注意的是,如果需要,提高innodb_open_tables的值。默认值为300。
如果遇到无法重启mysql的情况(因为mysql数据库模式被删除),请回到步骤2。您已经对mysql模式进行了物理复制。可以按照以下步骤恢复它:
1. mkdir /var/lib/mysql/mysql
2. cp /var/lib/mysql_grants/* /var/lib/mysql/mysql
3. chown -R mysql:mysql /var/lib/mysql/mysql
然后回到步骤6并继续。
关于第5步,将innodb_log_file_size设置为innodb_buffer_pool_size的25%的问题,这个规则已经过时了。您应该根据实际情况调整日志文件的大小。
通过将表数据和索引从ibdata1中分离出来并独立管理,可以回收磁盘空间。这个方法可能需要一些时间和努力,但是非常值得。通过在/etc/my.cnf中添加innodb_file_per_table选项,并执行一系列步骤,可以实现这一目标。这种方法在短期和长期内都非常有效,并且可以在不影响数据的情况下缩小ibdata1文件的大小。
当删除一个数据库后,磁盘空间没有得到释放的原因可能是因为数据库被删除后,磁盘上的数据文件并没有被完全清除。这可能导致磁盘空间的浪费。为了解决这个问题,可以采取以下步骤:
1. 在根用户权限下,使用以下命令将所有数据库备份到一个SQL文件中,并压缩保存:
mysqldump --all-databases --single-transaction | gzip -c > /tmp/mysql.all.sql.gz
2. 停止MySQL服务:
service mysql stop
3. 将MySQL数据目录重命名为一个备份目录,并创建一个新的空的MySQL数据目录:
mv /var/lib/mysql /var/lib/mysql.old; mkdir -m700 /var/lib/mysql; chown mysql:mysql /var/lib/mysql
4. 如果使用的是MySQL 5.5版本,执行以下命令来初始化MySQL数据库:
mysql_install_db
如果使用的是MySQL 5.7版本,执行以下命令来初始化MySQL数据库:
mysqld --initialize-insecure
5. 启动MySQL服务:
service mysql start
6. 使用以下命令将之前备份的SQL文件还原到新的MySQL数据库中:
zcat /tmp/mysql.all.sql.gz | mysql
7. 最后,重新启动MySQL服务:
service mysql restart
通过按照上述步骤操作,可以确保在删除数据库后,磁盘空间能够得到正确释放。