删除表中数据以前
[root@RM uar3]# du -sh *
3.3G apache-tomcat-7.0.54
150M instalRM4UAR
0 mariadb
903M mariadb-5.5.33a-linux-x86_64
64G mariadb-data
4.2G realmonitor
8.0K rm_netelement_s1u.sql
40K switchFlowReportWeb
0 tomcat
删除表数据
delete from nagios_statehistory where state_time < '2017-01-01 00:00:00';
delete from alarm_info where alarm_time < '2017-01-01 00:00:00';
delete from switchflowout where CreateTime < '1483200000';
delete from switchflowin where CreateTime < '1483200000';
删除表数据后
[root@RM uar3]# du -sh *
3.3G apache-tomcat-7.0.54
150M instalRM4UAR
0 mariadb
903M mariadb-5.5.33a-linux-x86_64
70G mariadb-data
4.2G realmonitor
8.0K rm_netelement_s1u.sql
40K switchFlowReportWeb
0 tomcat
DELETE只是将数据标识位删除，并没有整理数据文件，当插入新数据后，会再次使用这些被置为删除标识的记录空间。
最简单的：optimize table phpernote_article;对表进行优化
优化时出现下面问题
mysql> optimize table nagios_statehistory;
+---------------------------------+----------+----------+----------------------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------------------------+----------+----------+----------------------------------------------------------------------------------------------------+
| realmonitor.nagios_statehistory | optimize | Error | Table './realmonitor/nagios_statehistory' is marked as crashed and last (automatic?) repair failed |
| realmonitor.nagios_statehistory | optimize | Error | Table 'nagios_statehistory' is marked as crashed and last (automatic?) repair failed |
| realmonitor.nagios_statehistory | optimize | error | Corrupt |
+---------------------------------+----------+----------+----------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
修复数据表操作：
停止数据库
[root@RM uar3]# /etc/init.d/mariadb stop
Shutting down MySQL..
1、service mysqld stop;
2、cd /var/lib/mysql/db_name/
3、myisamchk -r tablename.MYI (修复单张数据表)
myisamchk -r *.MYI (修复所有数据表)
[root@RM realmonitor]# myisamchk -r nagios_statehistory.MYI
- recovering (with sort) MyISAM-table 'nagios_statehistory.MYI'
Data records: 807753
- Fixing index 1
Data records: 1042062
[root@RM realmonitor]# /etc/init.d/mariadb start
Starting MySQL... [ OK ]
mysql> optimize table nagios_statehistory;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 6
Current database: realmonitor +---------------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------------------+----------+----------+----------+
| realmonitor.nagios_statehistory | optimize | status | OK |
+---------------------------------+----------+----------+----------+
1 row in set (3.64 sec)