How Can I Efficiently Clean Up My InnoDB Storage Engine to Improve Database Performance?

How to Clean an InnoDB Storage Engine

Despite its benefits, the InnoDB storage engine can become cluttered with data from deleted tables, potentially bloating the database over time. To clean up this unnecessary data and maintain database efficiency, follow these steps:

Step 1: Dump Database Data

Export the entire database into a SQL text file using mysqldump. For instance, you can create a file named SQLData.sql.

Step 2: Drop Databases

Delete all databases except for mysql and information_schema. Ensure you have all user grants by copying the mysql directory to a backup location (/var/lib/mysql_grants).

Step 3: Flush Transactional Changes

Run the command SET GLOBAL innodb_fast_shutdown = 0; to purge any remaining transactional changes.

Step 4: Shutdown and Modify Configuration

Shut down MySQL and add the following lines to your /etc/my.cnf file:

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Step 5: Clean Up Files

Delete ibdata and ib_logfile files from your MySQL data directory.

Step 6: Restart MySQL

Restart MySQL to recreate ibdata1 and ib_logfile* files.

Step 7: Import Data

Import the previously dumped SQLData.sql file to restore your database data.

After completing these steps, ibdata1 will contain only table metadata, while each InnoDB table will be stored independently. Running OPTIMIZE TABLE will now effectively shrink the table files (/var/lib/mysql/mydb/mytable.ibd).

The above is the detailed content of How Can I Efficiently Clean Up My InnoDB Storage Engine to Improve Database Performance?. For more information, please follow other related articles on the PHP Chinese website!