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

7 min readAug 17, 2018

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:

CentOS 7.3.1611
Kernel 3.10.0–862.6.3
Apache 2.4.6–45
MariaDB 5.5.52–1
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.

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.
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 tried 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 …
OKTo start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your systemPLEASE 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.plPlease report any problems at http://mariadb.org/jiraThe 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 ServerCopyright (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 -ADatabase 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 | 凍仁的筆記.