Shulou(Shulou.com)05/31 Report--

How to set an independent tablespace for Zabbix MySQL innodb_file_per_table, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

The MySQL DB used in the background of a zabbix monitoring system went down. When connected to MySQL DB server, I saw that the hard disk was almost full. It was found that the MySQL ibdata1 files used by zabbix had more than 300G, which almost occupied the space of the whole hard disk.

# df-hFilesystem Size Used Avail Use% Mounted on/dev/sda3 99G 15G 79G 17% / devtmpfs 3.9G 0 3.9G 0 / devtmpfs 3.9G 0 3.9G 0 / dev/shmtmpfs 3.9G 8.4m 3.9G 1% / runtmpfs 3.9G 03.9G 0% / sys/fs/cgroup/dev/sda1 488m 105M 348M 24% / boot/dev/sda2 378G 355G 4.1G 99% / datatmpfs 798M 0798M 0% / run/user/0# lltotal 371225844 Muhammad r-1 mysql mysql 16384 Apr 17 21:42 aria_log.00000001-rw-r- 1 mysql mysql 52 Apr 17 21:42 aria_log_control-rw-rw---- 1 mysql mysql 1224704 Apr 22 : 38 ddl_log.log-rw-r- 1 mysql mysql 380123480064 Apr 23 13:20 ibdata1-rw-r- 1 mysql mysql 5242880 Apr 23 13:20 ib_logfile0-rw-r- 1 mysql mysql 5242880 Apr 23 13:20 ib_logfile1drwx- 2 mysql mysql 4096 Apr 17 21:42 mysqldrwx- 2 mysql mysql 4096 Apr 17 21:42 performance_schemadrwx- 2 mysql mysql 4096 Apr 22 22:38 zabbix

When you look at the db version, you still use MariaDB 5.5.56.

# mysql-V

Mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86 / 64) using readline 5.1

This version of DB uses shared tablespaces by default. It is estimated that no independent tablespaces are set. Sure enough:

MariaDB [(none)] > show variables like 'innodb_file_per_table' +-- +-+ | Variable_name | Value | +-- +-+ | innodb_file_per_table | OFF | +-- + 1 row in set (0.00 sec)

Common tablespaces are used here, and the space cannot be shrunk even if the zabbix large table historical data is cleaned and alter table xxx engine=innodb; is performed.

Temporary improvement measures:

Because the disk is full, in order to restore the use of zabbix monitoring as soon as possible, users are asked to temporarily add a 500G hard disk to server. Copy the DB data to a new disk with more space, and remount the original disk as / data1, and mount the new disk as the original / data

After DB service is enabled, zabbix monitoring returns to normal.

Permanent improvement measures:

The disk used by Zabbix MySQL DB is too large, and although the task of cleaning up historical partition data has been set, too much monitoring data causes the existing retention policy to fill the hard disk. As mentioned above, the ibdata1 files used in shared tablespaces cannot be recycled, so we can only find ways to delete part of the historical data and switch to independent tablespaces.

Idea: export DB data to backup (large tables only back up recent historical data), delete the original common tablespace ibdata1 file, modify independent tablespace configuration, import backup data, and modify the strategy of cleaning historical partition data.

Check the usage size of each table in zabbix DB:

Select TABLE_NAME, (sum (DATA_LENGTH) + sum (INDEX_LENGTH)) / 1024 from information_schema.tables where table_schema= "zabbix" GROUP BY TABLE_NAMEORDER BY 2 DESCTABLE_NAME (sum (DATA_LENGTH) + sum (INDEX_LENGTH)) / 1024 history_uint 104518.12500000 history 24653.62500000 trends_uint 5394.67187500 event_recovery 1188.37500000 trends 1111.68750000 history_str 200.14062500

1. Stop the zabbix service

# systemctl stop zabbix-server

two。 Export zabbix DB basic table structure and data in addition to the two largest history tables

# mysqldump-h227.0.0.1-uroot-paired password`-default-character-set=utf8-- databases zabbix-R-- ignore-table=zabbix.history-- ignore-table=zabbix.history_uint-- log-error=zabbix_base.log > zabbix_base.sql

Description of main parameters:

-R exports procedure and function

-- ignore-table specifies the name of the table that you do not want to export, and if there are multiple tables that do not want to export, write multiple-- ignore-table

3. Export zabbix DB history and history_uint table data and structure for the last 7 days

Because the timestamp is saved in zabbix table, find out the timestamp corresponding to the time.

MariaDB [(none)] > select unix_timestamp ('2020-4-16') +-- + | unix_timestamp ('0-4-16') | +-+ | 1586966400 | +-+ 1 row in set (2020 sec)

Export history table data and structure for nearly 7 days

# mysqldump-h227.0.0.1-uroot-paired password'--default-character-set=utf8-- databases zabbix-- tables history-- where= "clock > 1586966400"-- log-error=history.log > history.sql

Export history_uint table data and structure for nearly 7 days

# mysqldump-h227.0.0.1-uroot-paired password'--default-character-set=utf8-- databases zabbix-- tables history_uint-- where= "clock > 1586966400"-- log-error=history_uint.log > history_uint.sql

4. Turn off the DB service

# systemctl stop mariadb.service

5. Modify innodb_file_per_table independent tablespace parameters

# vi / etc/my.cnfinnodb_file_per_table=1

6. Delete ibdata1 and log files (Note: try to make a backup before operation)

# rm-rf ibdata1 # rm-rf ib_logfile0# rm-rf ib_logfile1

Note: the main purpose of deleting ibdata1 is to free up space. An empty one will be automatically rebuilt after restarting the DB service. The log file is deleted to avoid the following error:

[Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!

[ERROR] InnoDB: redo logfile'. / ib_logfile0' exists. Creating system tablespace with existing redo log files is not recommended. Please delete all redo log files before creating new system tablespace.

7. Enable the DB service

# systemctl start mariadb.service

Note: after DB is enabled, ibdata1 is deleted, but zabbix DB still exists, but table under DB cannot be accessed.

8. Import the data backed up above

# mysql-uroot-ppassword-h227.0.0.1 zabbix < zabbix_base.sql# mysql-uroot-ppassword-h227.0.0.1 zabbix < history.sql# mysql-uroot-ppassword-h227.0.0.1 zabbix < history_uint.sql

At this point, the shared table space is changed to an independent table space, and the usage space of the / data hard disk is greatly reduced (/ data1 is the disk added when the temporary countermeasure is added, which is the size of the pre-migration DB file).

[root@vswhzb01 mysql] # du-sh * 16K aria_log.000000014.0K aria_log_control128M ibdata164M ib_logfile05.0M ib_logfile0_old64M ib_logfile15.0M ib_logfile1_old1016K mysql212K performance_schema41G zabbix [root@vswhzb01 mysql] # df-hFilesystem Size Used Avail Use% Mounted on/dev/sda3 99G 16G 79G 17% / devtmpfs 3.9G 0 3.9G 0% / devtmpfs 3.9G 0 3.9G 0% / dev/shmtmpfs 3.9G 8.4m 3.9G 1% / runtmpfs 3.9G 03.9G 0% / sys/fs/cgroup/dev/sda1 488M 105M 348M 24% / boottmpfs 798M 0798M 0% / run/user/0/dev/sda2 378G 55G 304G 16% / data/dev/sdb1 493G 355G 113G 76% / data1

9. Enable the zabbix service

# systemctl start mariadb.service

10. Finally, remember to adjust the historical partition deletion policy, otherwise the hard drive will run out if there is too much monitoring data.

Note: for zabbix historical partition deletion settings, please refer to the previous article http://blog.itpub.net/25583515/viewspace-2638892/.

DROP PROCEDURE IF EXISTS zabbix.partition_maintenance_all;DELIMITER $$CREATE PROCEDURE `partition_maintenance_ all` (SCHEMA_NAME VARCHAR (32)) BEGIN CALL partition_maintenance (SCHEMA_NAME, 'history', 30,24,7); CALL partition_maintenance (SCHEMA_NAME,' history_log', 30,24,7); CALL partition_maintenance (SCHEMA_NAME, 'history_str', 30,24,7) CALL partition_maintenance (SCHEMA_NAME, 'history_text', 30,24,7); CALL partition_maintenance (SCHEMA_NAME,' history_uint', 15,24,7); CALL partition_maintenance (SCHEMA_NAME, 'trends', 180,24,7); CALL partition_maintenance (SCHEMA_NAME,' trends_uint', 180,24,7); END$$ DELIMITER

Later, because the independent tablespace innodb_file_per_table setting has been used, even if the hard disk is full of DB again, you can free up OS disk space by using drop partition and other methods.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.