Free deleted InnoDB disk space

The InnoDB database is marked as deleted and does not really release the disk space it occupies, which results in a growing InnoDB database file. If you want to completely release these deleted data, you need to export the database, delete the InnoDB database file, and then pour. The following are the basic steps:

1 Exporting the InnoDB database using the mysqldump command

2 Stop MySQL

3 Delete all InnoDB database files and logs

4 start MySQL and automatically rebuild InnoDB database files and log files

5 Importing database files that were previously backed up

Specific commands:

# BACKUP DATABASE:

Mysqldump-uroot-proot--quick--force--all-databases > Mysqldump.sql

# Stop Database

Service Mysqld Stop

# Delete these large files

Rm/usr/local/mysql/var/ibdata1
rm/usr/local/mysql/var/ib_logfile*

# Manually delete all database folders except MySQL, and then start the database

Service mysqld Start

# Restore Data

Mysql-uroot-proot < Mysqldump.sql

Another way is to set up innodb_file_per_table when you create a database, so that InnoDB creates a data file for each table and then only needs to run the Optimize table command to free up all the deleted disk space.

Edit My.ini or my.cnf add innodb_file_per_table=1 # 1 to InnoDB section for enabled, 0 for disabled

The value of the variable can be viewed through the MySQL statement:

Mysql> Show variables like '%per_table% ';