zabbix ibdata1文件太大如何缩小

枫狐狸

MySql innodb如果是共享表空间，ibdata1文件越来越大，达到了30多个G，对一些没用的表进行清空：
truncate table xxx;
然后optimize table xxx; 没有效果
因为对共享表空间不起作用。
mysql ibdata1存放数据，索引等，是MYSQL的最主要的数据。
如果不把数据分开存放的话，这个文件的大小很容易就上了G，甚至几十G。对于某些应用来说，并不是太合适。因此要把此文件缩小。
无法自动收缩，必须数据导出，删除ibdata1，然后数据导入，比较麻烦，因此需要改为每个表单独的文件。
解决方法：数据文件单独存放(共享表空间如何改为每个表独立的表空间文件)。
步骤如下：

1）备份数据库
备份全部数据库，执行命令
#mysqldump -q -uroot -ppassword --add-drop-table --all-databases >/home/backup/all.sql
备份前最好清空趋势表、历史表，直接truncate table即可。
history
history_log
history_str
history_text
history_uint
events    #事件表
trends    #趋势表
trends_uint   #趋势表



做完此步后，停止数据库服务。
#service mysqld stop
#Centos7.X版本mariadb数据库
systemctl stop mariadb

2）修改mysql配置文件
打开my.ini或my.cnf文件
[mysqld]下增加下面配置
innodb_file_per_table=1
验证配置是否生效，可以重启mysql后,执行
#service mysqld restart
#mysql -uroot -ppassword
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name      | Value |
+-----------------------+-------+
| innodb_file_per_table | ON   |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql>

看看innodb_file_per_table变量是否为ON

3）删除原数据文件
删除原来的ibdata1文件及日志文件ib_logfile*，删除/var/lib/mysql目录下的应用数据库文件夹(mysql文件夹不要删)
删除/var/lib/mysql/zabbix目录下的所有文件

4)修改备份文件如果备份前没有清理历史表、趋势表，则在恢复数据库前将备份文件中的插入语句注释掉
sed -i 's/INSERT INTO `history`/--INSERT INTO `history`/g' /home/backup/all.sql
sed -i 's/INSERT INTO `history_log`/--INSERT INTO `history_log`/g' /home/backup/all.sql
sed -i 's/INSERT INTO `history_text`/--INSERT INTO `history_text`/g' /home/backup/all.sql
sed -i 's/INSERT INTO `history_uint`/--INSERT INTO `history_uint`/g' /home/backup/all.sql
sed -i 's/INSERT INTO `events`/--INSERT INTO `events`/g' /home/backup/all.sql
sed -i 's/INSERT INTO `trends`/--INSERT INTO `trends`/g' /home/backup/all.sql
sed -i 's/INSERT INTO `trends_uint`/--INSERT INTO `trends_uint`/g' /home/backup/all.sql


5）还原数据库
启动数据库服务
从命令行进入MySQL Server
#service mysqld start
#Centos7.X版本mariadb数据库
systemctl start mariadb

还原全部数据库，执行命令
#mysql -uroot -ppassword < /home/backup/all.sql

ybx13218464908

一看就是dba留下的坑