Mysql 数据库空间优化

查看那张表占用空间大

USE information_schema;

SELECT
TABLE_SCHEMA,
TABLE_NAME,
DATA_LENGTH,
INDEX_LENGTH
FROM
TABLES
GROUP BY
TABLE_SCHEMA
ORDER BY
DATA_LENGTH DESC;

修改表采用压缩

USE mydb;

ALTER TABLE mytable ROW_FORMAT = DYNAMIC;

OPTIMIZE TABLE mytable;

如果没有 DYNAMIC,那么采用 COMPRESSED 也可以

修改表采用 tokudb (需要安装 tokudb 引擎)

ALTER TABLE `mytable`
ENGINE=tokudb;

修改字符集

ALTER TABLE `mytable`
DEFAULT CHARACTER SET=gbk

删除日志文件

第一步:登陆进入mysql,并使用 show binary logs; 查看日志文件。

mysql> show binary logs;

第二步:查看正在使用的日志文件:show master status;

mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 425994852 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

当前正在使用的日志文件是mysql-bin.000005,那么删除日志文件的时候应该排除掉该文件。

mysql> purge binary logs to 'mysql-bin.000005';

删除除mysql-bin.000005以外的日志文件。

mysql 定时清理日志文件

如果每次等到发现空间不足的时候才去手动删除日志文件,这种方式是很不理想的。

那么,我们就需要设置mysql,让它能自动清理日志文件。

编辑mysql的配置文件,设置expire_logs_days(mysql定时删除日志文件)

[root@sam ~]# vim /etc/my.cnf

在my.cnf中,添加或修改expire_logs_days的值 (这里设置的自动删除时间为10天, 默认为0不自动删除)

expire_logs_days=10

修改后,重启mysql就会生效。

但是,在生产环境中,重启mysql数据库往往会付出很高的代价。

于是,可以在不重启mysql的情况下,修改expire_logs_days值

如下:

mysql> show variables like '%log%';
mysql> set global expire_logs_days = 10;

设置完后,可以通过 show variables like '%log%'; 看到expire_logs_days的值已被修改成10。

注意:通过这种方式设置expire_logs_days虽然不需要重启mysql即可生效,但是该方式在重启mysql之后,值会被恢复。

于是,建议通过mysql命令设置expire_logs_days的同时,也修改/etc/my.cnf下的expire_logs_days=10配置,这样在下次重启mysql的时候,expire_logs_days也一样是10;

参考

发表评论