Zabbix Server database migration from insufficient disk space in Linux system

has not been to care about Zabbbix server storage space issues, the recent Zabbix alert/root directory disk space is not enough, so log in Zabbix to see a bit, found that the root directory only 1.3MB, what to do? The first thought is the extension root directory, the results found not to use the LVM, the use of the standard, thought can also be saved, and Windows will be the same as the D disk, e disk Delete, and then D disk space expansion to the C drive OK Ah, so began to operate (note that early backup of the virtual machine, the best to do a snapshot, It's a bad problem. Recovery)

[[email protected] /] # df -h
File System Capacity Used Available Used% Mount Point
/ dev / sda2 9.8G 9.8G 1.3M 100% /
/ dev / sda5 6.2G 33M 6.1G 1% / home
/ dev / sda1 197M 143M 55M 73% / boot

Looking at the file system, I need to expand the sda2, that is, I need to delete Sda3, SDA4, Sda5 to expand Sda2, found that this is not possible, because the need to expand sda2 need umount/dev/sda2 to expand to solve, and the system disk is not able to umount , the device is prompted to be busy. It seems that this scheme has been overturned (because I only know this way to extend the standard type of disk, there may be other good ways I don't know.) ）

[[email protected] /] # fdisk -l
Disk / dev / sda: 21.5 GB, 21474836480 bytes, 41943040 sectors
Units = sector of 1 * 512 = 512 bytes
Sector size (logical / physical): 512 bytes / 512 bytes
I / O size (minimum / optimal): 512 bytes / 512 bytes


Disk label type: dos
Disk identifier: 0x0003dc46
Device Boot Start End Blocks Id System
/ dev / sda1 * 2048 411647 204800 83 Linux
/ dev / sda2 411648 20891647 10240000 83 Linux
/ dev / sda3 20891648 29083647 4096000 82 Linux swap / Solaris
/ dev / sda4 29083648 41943039 6429696 5 Extended
/ dev / sda5 29085696 41943039 6428672 83 Linux

Now the disk can not be expanded, it seems to only be able to detect which file occupies a larger space, and then from the file to solve the problem, by detecting is now/var/lib/mysql/under the Libdata1 file is too large has reached the 6.7G (detection method: du-sh/*, And then gradually detect it, I immediately think of the reason for the Zabbix database.

[[email protected] mysql] # ls -lh
Total usage 6.7G
-rw-rw ----. 1 mysql mysql 16K December 1 14:15 aria_log.00000001
-rw-rw ----. 1 mysql mysql 52 Dec 1 14:15 aria_log_control
-rw-rw ----. 1 mysql mysql 6.7G Dec 29 14:24 ibdata1
-rw-rw ----. 1 mysql mysql 5.0M Dec 29 14:24 ib_logfile0
-rw-rw ----. 1 mysql mysql 5.0M Dec 29 14:21 ib_logfile1
drwx ------. 2 mysql mysql 4.0K Nov 16 17:10 mysql
srwxrwxrwx 1 mysql mysql 0 Dec 29 13:45 mysql.sock
drwx ------. 2 mysql mysql 4.0K Nov 16 17:10 performance_schema
drwx ------. 2 mysql mysql 8.0K Nov 16 17:17 zabbix_db

Then Google found Zabbix database its table pattern is shared tablespace mode, as the data growth ibdata1 will be more and more large, performance impact, and innodb the data and indexes are placed under IBDATA1.

Shared tablespace mode:

InnoDB by default, all database InnoDB engine table data is stored in a single space: Ibdata1, the ibdata1 file does not shrink automatically when the database is added or deleted, and the backup of a single database becomes a problem. You can usually only export data using mysqldump and then import to resolve this issue.

Standalone tablespace mode:

Advantages:
1. Each table has its own independent table space.
2. The data and index of each table will exist in the table space itself.
3, you can implement a single table in a different database to move.
4, the space can be recycled (drop/truncate table mode operation table Space cannot be automatically recycled)
5. For tables that use stand-alone table spaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too severely, and there are opportunities to deal with it.
Disadvantages:
Single-table increments are larger than shared space.

Conclusion:
Shared table spaces have some advantages over insert operations, but there are no independent table spaces in the other, so we want to change to a separate table space. The Innodb_open_files parameter needs to be adjusted when a stand-alone tablespace is enabled.

But here is another problem, even if we will now adjust the space, but because our root directory itself is only 10G, after the database is large, or this situation will occur, so we still have to adjust the database, the location of its storage moved root directory, while adjusting to a separate table space, To solve this problem once and for all.

First, add a 100G disk on the virtual machine, then partition the disk, then mount the disk to the/zabbixdb directory, back up the database, migrate the database storage location to the Zabbixdb directory, and then modify the database as a standalone tablespace mode, and then restore the database;

1. Add disk and format disk

[[email protected] _server /] # fdisk / dev / sdb
[[email protected] _server /] # mkfs.xfs -f / dev / sdb1
[[email protected] _server /] # fdisk -l
Disk / dev / sdb: 107.4 GB, 107374182400 bytes, 209715200 sectors
Units = sector of 1 * 512 = 512 bytes
Sector size (logical / physical): 512 bytes / 512 bytes
I / O size (minimum / optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0xabdf27bd
Device Boot Start End Blocks Id System
/ dev / sdb1 2048 209715199 104856576 83 Linux

2. Mount the disk and set the boot automatically mount

[[email protected]_server /]# mkdir /ZabbixDB
[[email protected]_server /]# mount /dev/sdb1 /ZabbixDB
[[email protected]_server /]# vi /etc/fstab
[[email protected]_server /]# /dev/sdb1 /ZabbixDB xfs default 1 2

3. Backing Up the database

Because the database is relatively large, let's first look at which tables have more historical data

MariaDB [zabbix_db]> select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema=‘zabbix_db‘;
+----------------------------+---------------+------------+
| table_name                 | total_mb      | table_rows |
+----------------------------+---------------+------------+
| events                     |  116.76562500 |     904289 |
| history                    |  555.81250000 |    7038124 |
| history_uint               | 5674.50000000 |   59219866 |
| trends                     |   46.57812500 |     581682 |
| trends_uint                |  270.95312500 |    2228048 |
+----------------------------+---------------+------------+
113 rows in set (0.05 sec)

You can see that history and History_uint both tables have the most historical data.

In addition, there are some data in trends,trends_uint.

Because the amount of data is too large, it is almost impossible to delete the data in the normal way.

So decide to use TRUNCATE table directly to quickly empty the data of these tables, then use mysqldump to export the data, delete the shared tablespace data file, and re-import the data.

2) Stop related services and avoid writing data

[[Email protected]_server/]# systemctl stop zabbix-server[[email protected]_server/]# systemctl Stop httpd

3) Clear Historical data

[[email protected]_server /]# mysql -uroot -p
mysql > use zabbix;
Database changed
mysql > truncate table history;
Query OK, 123981681 rows affected (0.23 sec)
mysql > optimize table history;
1 row in set (0.02 sec)
mysql > truncate table history_uint;
Query OK, 57990562 rows affected (0.12 sec)
mysql > optimize table history_uint;
1 row in set (0.03 sec)

4) backing Up the database

[[Email protected]_server/]# mysqldump-uroot-p zabbix_db >/home/zabbix_db.sql

5) Stop the database and delete the shared tablespace data file

[[email protected]_server /]# systecmtl stop mariadb
[[email protected]_server /]# cd /var/lib/mysql
[[email protected]_server /]# rm -rf ib*

4, the Migration database storage location

[[email protected] _server /] # mkdir / ZabbixDB / DB
[[email protected] _server /] # chown -vR mysql: mysql / ZabbixDB / DB
[[email protected] _server /] # chmod -vR 700 / etc / ZabbixDB / DB
[[email protected] _server /] # cp -av / var / lib / mysql * / ZabbixDB / DB
[[email protected] _server /] # vi /etc/my.cnf
[mysqld]
#You can modify InnoDB to be an independent table space mode. Each table in each database will generate a data space.
innodb_file_per_table = 1
#Modify the database storage directory
datadir = / ZabbixDB / DB / mysql
socket = / var / lib / mysql / mysql.sock

Start the database service again

[Email protected]_server/]# systemctl start mariadb

After starting the service, we need to check whether the function of the standalone table space has been turned on properly, when on

[[email protected]_server /]#mysql -u root -p
MariaDB [(none)]> show variables like ‘%per_table%‘;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

5. Restore the database

[[email protected]_server /]# mysqldump -uroot -p zabbix < /data/zabbix_db.sql
or [[email protected]_server /]# mysql -uroot -p zabbix < /data/zabbix_db.sql

  

	
   


  

6. Recovery of related services

[[Email protected]_server/]# systemctl start zabbix-server[[email protected]_server/]# systemctl start httpd

At this point we re-test the database and disk space, we will find that the database has been stored in the/zabbixdb/db/mysql directory, and the root directory space is back to normal.

[[email protected] _server mysql] # pwd
/ ZabbixDB / DB / mysql
[[email protected] _server mysql] # ls -l
Total dosage 29712
-rwxrwxrwx. 1 mysql mysql 16384 Dec 29 18:16 aria_log.00000001
-rwxrwxrwx. 1 mysql mysql 52 Dec 29 18:16 aria_log_control
-rwxrwxrwx. 1 mysql mysql 8820 Dec 29 16:42 columns_priv.frm
-rwxrwxrwx. 1 mysql mysql 0 Dec 29 16:42 columns_priv.MYD
-rwxrwxrwx. 1 mysql mysql 4096 Dec 29 16:42 columns_priv.MYI
-rwxrwxrwx. 1 mysql mysql 9582 Dec 29 16:42 db.frm
-rwxrwxrwx. 1 mysql mysql 880 Dec 29 16:42 db.MYD
-rwxrwxrwx. 1 mysql mysql 5120 Dec 29 16:42 db.MYI 

#磁盘空间

[[email protected] _server mysql] # df -lh
File System Capacity Used Available Used% Mount Point
/ dev / sda2 9.8G 3.1G 6.8G 31% /
/ dev / sdb1 100G 1.9G 99G 2% / ZabbixDB 

At this point, Zabbix disk space and database storage location migration is complete.

This article from "System operation and Maintenance" Systemdevops "" blog, declined reprint!

Zabbix Server database migration from insufficient disk space in Linux system