Enabling the InnoDB File-Per-Table tablespaces and migrate the MySQL database for Zabbix 3.2.6 on CentOS 7

As the Zabbix Ops, we want to split the large ibdata1 file, so than we can reduce the obsolete data like history_uint table.

[ jonny@zabbix-server ~ ]
$ ls -lh /var/lib/mysql/
total 16G
-rw-rw---- 1 mysql mysql 16K Aug 15 10:36 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Aug 15 10:36 aria_log_control
-rw-rw---- 1 mysql mysql 15G Aug 15 12:45 ibdata1
-rw-rw---- 1 mysql mysql 5.0M Aug 15 12:45 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Aug 15 12:45 ib_logfile1
drwx------ 2 mysql mysql 4.0K Aug 15 10:36 mysql
srwxrwxrwx 1 mysql mysql 0 Aug 15 10:44 mysql.sock
drwx------ 2 mysql mysql 4.0K Aug 15 10:36 performance_schema
drwx------ 2 mysql mysql 6 Aug 15 10:36 test
drwx------ 2 mysql mysql 8.0K Aug 15 13:59 zabbix

▲ We can see the ibdata1 filesize is very large.


My environment:

  1. CentOS 7.3.1611
  2. Kernel 3.10.0–862.6.3
  3. Apache 2.4.6–45
  4. MariaDB 5.5.52–1
  5. PHP 5.4.45
The innodb_file-per-table option default is enabling after MySQL 5.6.6, but it’s not in this case.

Stop the zabbix service

Before my start, we need stop the zabbix-server service.

[ jonny@zabbix-server ~ ]
$ sudo systemctl stop zabbix-server

Backup the database

If the disk was full, please add the other storage, and mount to /mnt/, or someone path first.

  1. Snapshot (Important): If this Zabbix server is built on Virtual Machine, please snapshot it. If we get some problem, we can use this to quick recovery.
  2. Use the mysqldump command.
[ jonny@zabbix-server ~ ]
$ sudo su -c "mysqldump -u zabbix -p --all-databases --add-drop-table > /mnt/zabbix_db.sql"
[ jonny@zabbix-server ~ ]
$ ls -lh /mnt/zabbix_db.sql
-rw-r — r — 1 root root 6.3G Aug 15 03:43 zabbix_db.sql

3. Backup the /var/lib/mysql directory.

[ jonny@zabbix-server ~ ]
$ rsync -avP /var/lib/mysql /mnt/

Enable the File-Per-Table tablespaces

1. Stop the MariaDB service.

[ jonny@zabbix-server ~ ]
$ sudo systemctl stop mariadb

2. Backup the /etc/my.cnf.

[ jonny@zabbix-server ~ ]
$ sudo cp /etc/my.cnf /etc/my.cnf.20180815

3. Add innodb_file_per_table=1under [mysqld]in /etc/my.cnf.

[ jonny@zabbix-server ~ ]
$ sudo vim /etc/my.cnf
[mysqld]
...
# Enable the File-Per-Table tablespaces.
innodb_file_per_table=1

Rebuild the database

Please make sure the backup is working, this step will clean up all data.

1. Remove all files under /var/lib/mysql/.

[ jonny@zabbix-server ~ ]
$ sudo rm -rf /var/lib/mysql/*
I was try only remove ib*, but it’s not working.

2. Initialization the database.

[ jonny@zabbix-server ~ ]
$ sudo /usr/bin/mysql_install_db
Installing MariaDB/MySQL system tables in ‘/var/lib/mysql’ …
180815 10:36:15 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 14533 …
OK
Filling help tables…
180815 10:36:15 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 14543 …
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:
'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h zabbix-server.example.tw password 'new-password'
Alternatively you can run:
'/usr/bin/mysql_secure_installation'
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.
You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe - datadir='/var/lib/mysql'
You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/mysql-test' ; perl mysql-test-run.pl
Please report any problems at http://mariadb.org/jira
The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Support MariaDB development by buying support/new features from MariaDB Corporation Ab. You can contact us about this at sales@mariadb.com.
Alternatively consider joining our community based development effort:
http://mariadb.com/kb/en/contributing-to-the-mariadb-project/

3. List the database directory after we initialization.

[ jonny@zabbix-server ~ ]
$ ls -lh /var/lib/mysql
total 28K
-rw-rw---- 1 root root 16K Aug 15 10:36 aria_log.00000001
-rw-rw---- 1 root root 52 Aug 15 10:36 aria_log_control
drwx------ 2 root root 4.0K Aug 15 10:36 mysql
drwx------ 2 root root 4.0K Aug 15 10:36 performance_schema
drwx------ 2 root root 6 Aug 15 10:36 test

4. Change the file permission.

[ jonny@zabbix-server ~ ]
$ sudo chown -R mysql:mysql /var/lib/mysql/

5. List the database directory again.

[ jonny@zabbix-server ~ ]
$ ls -l /var/lib/mysql/
total 28700
-rw-rw---- 1 mysql mysql 16384 Aug 15 10:36 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Aug 15 10:36 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Aug 15 10:41 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Aug 15 10:41 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Aug 15 10:40 ib_logfile1
drwx------ 2 mysql mysql 4096 Aug 15 10:36 mysql
drwx------ 2 mysql mysql 4096 Aug 15 10:36 performance_schema
drwx------ 2 mysql mysql 6 Aug 15 10:36 test

6. Start the MariaDB service.

[ jonny@zabbix-server ~ ]
$ sudo systemctl start mariadb

Rebuild the Account

1. Login the MySQL shell with root, and switch to mysql database.

[ jonny@zabbix-server ~ ]
$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]>

2. Change the database admin password.

MariaDB [mysql]> UPDATE user SET Password=PASSWORD("<FIXME>") WHERE User="root";
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

3. Create a database for Zabbix.

MariaDB [mysql]> CREATE DATABASE zabbix;
Query OK, 1 row affected (0.00 sec)

4. Create a database account for Zabbix.

MariaDB [mysql]> CREATE USER 'zabbix'@'localhost' IDENTIFIED BY '<FIXME>';
Query OK, 0 rows affected (0.00 sec)

5. Setting permission of Zabbix account.

MariaDB [mysql]> GRANT ALL PRIVILEGES ON zabbix.* TO 'zabbix'@'localhost';
Query OK, 0 rows affected (0.00 sec)

6. Immediately apply this permission.

MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Verify the per_table setting

Before we import before backup data, please verify the innodb_file_per_table value is ON.

MariaDB [mysql]> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
MariaDB [mysql]>

Recovery with import before backup sql file

[ jonny@zabbix-server ~ ]
$ time mysql -u zabbix -p zabbix < /mnt/zabbix_db.sql
Enter password:
real    31m57.704s
user 0m50.054s
sys 0m1.945s
It maybe need much time, we can drink coffee or do something else.

Reduce the large table

1. Find the large tables.

[ root@zabbix-server ~ ]
# ls -lhtrS /var/lib/mysql/zabbix | tail
...

-rw-rw---- 1 mysql mysql 192M Aug 15 14:01 events.ibd
-rw-rw---- 1 mysql mysql 288M Aug 15 14:01 trends.ibd
-rw-rw---- 1 mysql mysql 660M Aug 15 14:01 trends_uint.ibd
-rw-rw---- 1 mysql mysql 1.1G Aug 15 14:01 history.ibd
-rw-rw---- 1 mysql mysql 14G Aug 15 13:53 history_uint.ibd

2. Login the MySQL shell with zabbix, and switch to zabbix database.

[ jonny@zabbix-server ~ ]
$ mysql -u zabbix -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 957
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use zabbix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

3. Count the history_uint table.

MariaDB [zabbix]> select count(itemid) from history_uint;
+---------------+
| count(itemid) |
+---------------+
| 177487167 |
+---------------+
1 row in set (1 min 14.37 sec)

4. Clean up the history_uint table.

MariaDB [zabbix]> truncate table history_uint;
Query OK, 0 rows affected (0.38 sec)

5. Count the history_uint table again.

MariaDB [zabbix]> select count(itemid) from history_uint;
+---------------+
| count(itemid) |
+---------------+
| 107 |
+---------------+
1 row in set (0.00 sec)

6. See the disk usage.

[ jonny@zabbix-server ~ ]
$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/cl-root 22G 5.1G 17G 24% /
devtmpfs 1.9G 0 1.9G 0% /dev
tmpfs 1.9G 0 1.9G 0% /dev/shm
tmpfs 1.9G 8.5M 1.9G 1% /run
tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup
/dev/sda1 1014M 322M 693M 32% /boot
...
/dev/sdb1 32G 23G 9.7G 70% /mnt

7. Find the large tables again.

[ root@zabbix-server ~ ]
# ls -lhtrS /var/lib/mysql/zabbix | tail
...

-rw-rw---- 1 mysql mysql 192M Aug 15 14:01 events.ibd
-rw-rw---- 1 mysql mysql 288M Aug 15 14:01 trends.ibd
-rw-rw---- 1 mysql mysql 660M Aug 15 14:01 trends_uint.ibd
-rw-rw---- 1 mysql mysql 1.1G Aug 15 14:01 history.ibd

Good luck, have fun.

Synchronized on Enabling the InnoDB File-Per-Table tablespaces and migrate the MySQL database for Zabbix 3.2.6 on CentOS 7 | 凍仁的筆記.

Reference

  1. 處理 MySQL 的 ibdata1 文檔過大問題 | 掃文資訊
  2. MySQL :: MySQL 5.6 Reference Manual :: 14.7.4.1 Enabling and Disabling File-Per-Table Tablespaces
  3. MySQL 開啟 innodb_file_per_table 及轉換現有資料表 | Linux 技術手札
  4. MySQL 新增使用者及建立資料庫權限 | Linux 技術手札
  5. MySQL 修改密碼與忘記密碼重設 @ 小殘的程式光廊
  6. Zabbix History Table Clean Up | whatizee
  7. Optimizing disk usage of Zabbix and PostgreSQL | Modio
  8. History tables Explanation | ZABBIX Forums
Like what you read? Give Chu-Siang Lai a round of applause.

From a quick cheer to a standing ovation, clap to show how much you enjoyed this story.