MySQL ibdata1 takes up space for large free MySQL ibdata1 files

MySQL ibdata1 takes up space for large free MySQL ibdata1 files

Background: A recent company server alarm, disk space alarm super threshold. Originally MySQL database Data directory is particularly large, the previous article describes the change MySQL data files directory, this article mainly describes how to put the space occupied by MySQL released.

Description: MySQL has a variety of storage engines, such as MyISAM,InnoDB are very common. If you use the InnoDB, and the MySQL default configuration file, MySQL data will be stored in the Ibdata1 file, after a period of time this file will become very large, consuming a lot of disk space. How to release the space occupied by MySQL.

MySQL ibdata1 store data, indexes, etc., is the most important data of MySQL.

Steps:

1, backing up the database

Back up all databases, execute commands mysqldump-uusername-ppassword--all-databases >/backup/all.sql or just back up the required libraries.

Deleting a database
Drop database A;
Drop database B;

Stop database: Service mysqld stop

2. Modify the MySQL configuration file

Modify the My.cnf file to add the following configuration

Innodb_file_per_table

Use a separate InnoDB file for each table to modify the/etc/my.cnf file

3. Delete the original data file

Delete the original ibdata1 file and log file ib_logfile*, delete the application database folder under the Data directory (Mysql,test,information_schema the database itself folder do not delete)

4. Restore the database

Start the Database Services service mysqld start

Restore all databases, execute command mysql-uusername-ppassword <all.sql

After these steps, you can see that the new Ibdata1 file is only dozens of M, and the data and indexes become small IBD files for a single table, which are under the folder of the corresponding database.

This article is from the "Technical Achievement Dream" blog, please be sure to keep this source http://pizibaidu.blog.51cto.com/1361909/1697016

MySQL ibdata1 takes up space for large free MySQL ibdata1 files