#MYSQL SHRINK DATABASE HOW TO#
#MYSQL SHRINK DATABASE FREE#

Shrink all the files in a database and you’ve shrunk the database. Of course, from a file system standpoint, a database is nothing more than a set of files, so that makes sense. The informations_schema is using the memory db-engine and is dropped and regenerated upon stop/restart of mysqld. DBCC SHRINKFILE, as the name implies, shrinks files not databases. And there are no files associated with the them, not even a database directory.

The program ships with most MySQL installations, and can be used via the. It is in fact just a bunch of read-only views, not tables. Enter mysqldump, a database backup program originally written by Igor Romanenko. In such a case, it would be unproductive to shrink the database repeatedly. If you notice that the size of a database grows again after you have shrunk it, it means that the database needs this space for its regular operations.

#MYSQL SHRINK DATABASE FREE#

Regarding the information_schema, that is not necessary nor possible to drop. Do you need to shrink a database All databases need some free space to execute the necessary operations. However, ibdata1 will not shrink unless you do the steps above. As you usually drop the database soon after, the ibd* files will be deleted.īy using the command ALTER TABLE ENGINE=innodb or OPTIMIZE TABLE one can extract data and index pages from ibdata1 to separate files.

Select your database from the menu on the left and then click on the Size column at the top. You can check your database tables from Site Tools > Site > MySQL > PHPMyAdmin > Access.

#MYSQL SHRINK DATABASE HOW TO#

When you create a new database for analysis, the tables will be located in separate ibd* files, not in ibdata1. How to reduce the size of my database Find which are the largest database tables first. When you start MySQL in step 5 the ibdata1 and ib_log files will be recreated.

Drop all databases except the above 2 databases.

Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases This is one of the most controversial topics I have ever dealt with over the years as a MySQL DBA and in the DBA StackExchange.

As you want to reclaim the space from ibdata1 you actually have to delete the file:

Mysql shrink database