Downsize the mysqlibdata1 file-release disk space

How to narrow down the mysqlibdata1 file and release disk space? The mysqlibdata1 file stores data and indexes. If the data in the database is not stored separately, the size of the file will easily be GB or even dozens of GB. It is not suitable for some servers with small applications and disks. Therefore, we need to shrink this file.

How to narrow down the mysql ibdata1 file and release the disk space? Mysql ibdata1 files store data and indexes. If the data in the database is not stored separately, the size of the file will easily be GB or even dozens of GB. It is not suitable for some servers with small applications and disks. Therefore, we need to shrink this file.

Shrink mysql ibdata1 file-release disk space

?

Mysql ibdata1 files store data and indexes.

If the data in the database is not stored separately, the size of the file will easily be GB or even dozens of GB. It is not suitable for some servers with small applications and disks. Therefore, we need to narrow down this file.

Method: Separate data files.

?

See: http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#backup

?

Steps:

1. Back up the database

Enter MySQL Server 5.5/bin from the command line
Back up all databases and run the command mysqldump-q-uusername-pyourpassword -- add-drop-table -- all-databases>/all. SQL.

After this step, stop the database service.

2. Modify the mysql configuration file

Modify the my. ini file and add the following Configuration:

Iinnodb_file_per_table = 1

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 *, and delete the application database folder under the data Directory (do not delete the mysql folder)

4. Restore the database

Start Database Service

Enter MySQL Server 5.5/bin from the command line
Restore all databases and run mysql-uusername-pyourpassword. </all. SQL

After the preceding steps, we can see that the new ibdata1 file is only dozens of MB, and the data and indexes are converted into small ibd files for a single table, they are in the folder of the corresponding database.

?

Note: at present, there is no way to directly reduce the size of the ibdata file without exporting data and importing it again. For databases with bad paths and corrupted tablespace in ibdata files, you can only skip the damaged id, dump the data into an SQL file, and then import it.

?