清理zabbix数据库ibdata1文件

zabbix经过长时间的运行导致ibdata1文件的数据持续增加，mysql之前是没有配置表空间，InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：ibdata1，久而久之给后续的维护带来障碍

1. 查看文件大小

[root@zabbix ~]# ll -h /var/lib/mysql/ibdata1 
-rw-rw---- 1 mysql mysql 39G Mar 23 15:26 /var/lib/mysql/ibdata1

&查看表数据，基本也就是history的数据比较多，可以选择清空
MariaDB [zabbix]> select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema='zabbix';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    30574
Current database: zabbix
+----------------------------+--------------+------------+
| table_name                 | total_mb     | table_rows |
+----------------------------+--------------+------------+
| history                    | 977.00000000 |   12077740 |
| history_log                |   0.03125000 |          0 |
| history_str                |  37.56250000 |     265294 |
| history_text               |  21.14062500 |     195417 |
| history_uint               | 563.85937500 |    6866603 |

2. 停止zabbix主服务，清理表数据

2.1 完全清理历史数据

&我是用docker启动的
[root@zabbix zabbix]# docker-compose -f docker-zabbix.yaml stop
[root@zabbix ~]# mysql -uroot -p
Enter password:
&清理表数据
MariaDB > truncate table history;
Query OK, 2423866533 rows affected (0.23 sec) 
&优化表重新组织表数据和相关索引数据的物理存储
MariaDB > optimize table history; 
1 row in set (0.02 sec)
 
MariaDB > truncate table history_uint;
Query OK, 752790562 rows affected (0.12 sec) 
 
MariaDB > optimize table history_uint;
1 row in set (0.03 sec)

2.2 筛选日期清理历史数据，按照表结构clock字段时间戳
时间：2023-02-27 14:29:02 时间戳：1677479342

&清理小于2月27之前的数据
MariaDB [(none)]> DELETE FROM history WHERE clock < 1677479342;
MariaDB [(none)]> DELETE FROM history_uint WHERE clock < 1677479342;

3. 备份原数据，删除ibdata1文件，配置独立表空间

&备份数据
[root@zabbix ~]# mysqldump -uroot -p zabbix > zabbix.sql
&停止数据库
[root@zabbix ~]# systemctl stop mariadb
&移动文件到临时目录
[root@zabbix ~]# cd /var/lib/mysql/
[root@zabbix ~]# mv ibdata1 /tmp/
&配置参数
[root@zabbix ~]# vi /etc/my.cnf
[mysqld]
innodb_file_per_table=1
&启动mysql
[root@zabbix ~]# systemctl start mariadb
&查看配置已生效
MariaDB [(none)]> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

4. 恢复数据

4.1 我这里的版本为5.5.68-MariaDB，使用mysqldump恢复时报错

&(1)
mysqldump: Got error: 1146: Table XXXXXXXXX‘ doesn‘t exist when using LOCK TABLES
删除/var/lib/mysql/zabbix/所有.frm文件 

&(2) 
*mysqldump导入后无效
mysqldump -uroot -p zabbix < zabbix.sql    
*增加--database参数正常
mysqldump -uroot -p --database zabbix < zabbix.sql
*还有一种source文件导入
[root@zabbix ~]# mysql -uroot -p
MariaDB [(none)]> use zabbix;

Database changed
MariaDB [zabbix]> source /root/zabbix.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

5. 启动zabbix

&我是用docker启动的
[root@zabbix zabbix]# docker-compose -f docker-zabbix.yaml up -d 
&ibdata1文件大小已下降
[root@zabbix zabbix]# cd /var/lib/mysql/
[root@zabbix mysql]# ll -h ibdata1 
-rw-rw---- 1 mysql mysql 18M Mar 27 15:04 ibdata1