The ibdata1 file of MySQL is too large and the mysqlibdata1 file is too large.

The ibdata1 file of MySQL is too large and the mysqlibdata1 file is too large.
Handling the problem that the ibdata1 file of MySQL is too large

I encountered a MySQL database installed in yum when installing zabbix monitoring. Later I used it for a while to find that the ibdata1 space under the data Directory was very large, on the contrary, my zabbix database has a small space, which is inconvenient for backing up the zabbix database later. So I want to solve it.

What is an ibdata1 file?

Ibdata1 is a file used to build the tablespace of the innodb system. This file contains the metadata, revocation record, modification buffer, and dual-write buffer of the innodb table. If the file-per-table option is enabled, the file does not necessarily contain data of all tables. When the innodb_file_per_table option is enabled, the data and indexes of the newly created table will not exist in the system tablespace, but will be stored in the. ibd file of the respective tables.
Obviously, this file will become larger and larger. The innodb_autoextend_increment option specifies the step that the file will automatically grow every time. The default value is 8 Mb.

Why is the size of the ibdata1 file growing?

Ibdata1 stores data, indexes, and caches, and is the primary data of MYSQL. Therefore, as the database grows larger, the table grows, Which is unavoidable. If it takes a long time to get bigger and bigger, it is not that convenient for us to process logs and space, so we don't know where to start. Next we need to deal with this situation, store data in sub-databases.

What should we do?

First, back up the database file, and then directly Delete the ibdata file (for the sake of insurance, it is best to perform full backup once, to ensure data security and integrity), and then re-import the database file!

The procedure is as follows (incomplete, but you must first understand the general situation and principle ):

1. Stop services and back up all databases once
mysqldump -uroot -ppassword --all-databases --add-dorp-table > /root/all_mysql.sql
2. After the backup is completed, stop the database.
Systemctl stop mariadb or service mysqld stop
3. modify the configuration file
Add the following configuration under [mysqld]: innodb_file_per_table = 1 to verify whether the configuration takes effect. After restarting mysql, run # service mysqld restart
4. Verification
Mysql-uroot-ppassword mysql
Show variables like '% per_table % ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Innodb_file_per_table | ON |
+ ----------------------- + ------- +
1 row in set (0.00 sec)
The innodb_file_per_table status changes to ON
5. Delete ibdata1 files and logs
rm -rf ibdata1
rm -rf ib_logfile*
6. Restore the database
Mysql-uuser-ppassword
Source/root/all_mysql. SQL
Data files are stored separately (the shared tablespace is changed to an independent tablespace file for each table ).