Solutions to the increasing size of MySQL ibdata1 files

Solutions to the increasing size of MySQL ibdata1 files

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 ).

Please also point out any errors.

This article permanently updates link: https://www.bkjia.com/Linux/2018-02/151061.htm