MysqlInnoDB _ MySQL

This article mainly introduces how to release disk space after MysqlInnoDB deletes data. Innodb only marks deleted data as deleted and does not actually release the occupied disk space, this leads to the continuous growth of InnoDB database files. This article describes how the Innodb database that releases disk space only marks deleted data and does not actually release the occupied disk space, this leads to the continuous growth of InnoDB database files.

If innodb_file_per_table = 1 is set during database creation, InnoDB creates a data file for each TABLE, and then only needs to run the optimize table command to release all deleted disk space.

After running the optimize table Table name, although TABLE does not support optimize, doing recreate + analyze instead will be reported at the end, it is actually successful :)

-------------------------------------------------------------

If you do not set this parameter and want to release the space and completely release the deleted data, you need to export the database, delete the InnoDB database file, and then import it.

The basic steps are as follows:

1. use the mysqldump command to export the InnoDB database

2. stop MySQL

3. delete all InnoDB database files and logs

4. start MySQL and automatically recreate InnoDB database files and log files

5. import the database files backed up earlier.

----------------------------------------------

Specific commands:

The code is as follows:

# Back up a database:

Mysqldump-uroot-proot -- quick -- force -- all-databases> mysqldump. SQL
# Stopping a 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