Menu
I've created some very large databases and have since dropped a few. I've noticed my disk space has not recovered as much as I had expected. For instance, the last database I added actually used up all of my free space and aborted, so I dropped that schema. Before this I had 12.4 GB free, now I only have 7.52 GB free.
What's going on here? How do I get my ~5 GB back?
332
But, if you are using innodb_file_per_table then you can reclaim the space by running OPTIMIZE TABLE on that table. OPTIMIZE TABLE will create a new identical empty table. Then it will copy row by row data from the old table to the new one.
In MySQL, you can use the mysql command to restore the database from a dump file. mysqldump is a command-line utility used to generate a MySQL logical database backup as a single . sql file with a set of SQL statements. The utility helps you dump MySQL tables, multiple databases, or their objects.
If a database backup doesn't exist, a dropped table can be recovered from SQL database data and transaction log files. When it comes to recovery from a database in the Simple recovery model, it might fail as the Drop table transaction might have been overwritten. Even then, recovery is possible from the MDF file.
From http://dev.mysql.com/doc/refman/5.1/en/innodb-data-log-reconfiguration.html:
Currently, you cannot remove a data file from the tablespace. To decrease the size of your tablespace, use this procedure:
Use mysqldump to dump all your InnoDB tables.
Stop the server.
Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.
Remove any .frm files for InnoDB tables.
Configure a new tablespace.
Restart the server.
Import the dump files.
Innodb creates a filesystem (the "tablespace") within the data files themselves. It never "shrinks" the data files when data is removed, since the reorganization of the data within the file could be costly (there's no guarantee that the data removed was at the end, or even contiguous). By recreating the database as described above, it makes the file as large as necessary for all the data, but no larger.
121
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
