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.
My Environment:
※ The innodb_file-per-table option default is enabling after MySQL 5.6.6, but it’s not in this case.
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.
3. Backup the /var/lib/mysql directory.
2. Backup the /etc/my.cnf.
3. Add innodb_file_per_table=1 under [mysqld] in /etc/my.cnf.
1. Remove all files under /var/lib/mysql/. (I was tried only remove ib*, but it's not working.)
2. Initialization the MySQL.
3. List the database directory after we initialization.
4. Change the file permission.
5. List the database directory again.
6. Start the MariaDB service.
2. Change the database admin password.
3. Create a database for Zabbix.
4. Create a database account for Zabbix.
5. Setting permission of Zabbix account.
6. Immediately apply this permission.
※ It maybe need much time, we can drink coffee or do something else.
2. Login the MySQl shell with zabbix, and switch to zabbix database.
3. Count the history_uint table.
4. Clean up the history_uint table.
5. Count the history_uint table again.
6. See the disk usage.
7. Find the large tables again.
Good luck, have fun.
[ jonny@zabbix-server ~ ]
$ ls -lh /var/lib/mysql/ [Enter]
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
- Zabbix: 3.2.6-1
- MariaDB: 5.5.52-1
- Apache: 2.4.6-45
- 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 [Enter]
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 > zabbix_db.sql" [Enter]
[ jonny@zabbix-server ~ ]
$ ls -lh zabbix_db.sql [Enter]
-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 ~ ]
$ sudo rsync -avP /var/lib/mysql /mnt/
Enable the File-Per-Table tablespaces
1. Stop the MariaDB service.[ jonny@zabbix-server ~ ]
$ sudo systemctl stop mariadb [Enter]
2. Backup the /etc/my.cnf.
[ jonny@zabbix-server ~ ]
$ sudo cp /etc/my.cnf /etc/my.cnf.20180815 [Enter]
3. Add innodb_file_per_table=1 under [mysqld] in /etc/my.cnf.
[ jonny@zabbix-server ~ ]
$ sudo vim /etc/my.cnf [Enter]
[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/. (I was tried only remove ib*, but it's not working.)
[ jonny@zabbix-server ~ ]
$ sudo rm -rf /var/lib/mysql/* [Enter]
2. Initialization the MySQL.
[ jonny@zabbix-server ~ ]
$ sudo /usr/bin/mysql_install_db [Enter]
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 [Enter]
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/ [Enter]
5. List the database directory again.
[ jonny@zabbix-server ~ ]
$ ls -l /var/lib/mysql [Enter]
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 [Enter]
Rebuild the account
1. Login the MySQL shell with root, and switch to mysql database.[ jonny@zabbix-server ~ ]
$ mysql -u root -p [Enter]
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 < zabbix_db.sql [Enter]
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 [Enter]
...
-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]
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 [Enter]
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/vda1 1014M 322M 693M 32% /boot tmpfs 379M 0 379M 0% /run/user/8002 /dev/vdb1 32G 23G 9.7G 70% /mnt
7. Find the large tables again.
[ root@zabbix-server ~ ]
# ls -lhtrS /var/lib/mysql/zabbix | tail [Enter]
...
-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 | Medium.
Reference:
★ 處理 MySQL 的 ibdata1 文檔過大問題 | 掃文資訊
★ [MySQL :: MySQL 5\.6 Reference Manual :: 14\.7\.4\.1 Enabling and Disabling File\-Per\-Table Tablespaces"
★ MySQL 開啟 innodb_file_per_table 及轉換現有資料表 | Linux 技術手札
★ MySQL 新增使用者及建立資料庫權限 | Linux 技術手札
★ MySQL 修改密碼與忘記密碼重設 @ 小殘的程式光廊
留言
張貼留言
喜歡這篇文章嗎？歡迎在底下留言讓凍仁知道。😉