Home / server / Questions / 126729
Accepted
Thomas
Asked: 2010-03-27 12:09:27 +0800 CST

How do I free up deleted data to the filesystem in MySQL

  • 772

I have deleted several GB of MySQL data, but it has not been returned to the filesystem. My disks are getting full so how do I do that? All the data is in the ibdata1 file since it's InnoDB.

I have tried mysqloptimize -A -o but it did not help.

Edit: Server version: 5.0.51a-24+lenny2+spu1 (Debian)

mysql

3 Answers

  1. Best Answer

    since you seem to have monolithic innodb file - you cannot shrink it.

    if you can afford downtime do as follows:

    1. stop mysql.
    2. take a file-based backup of all files / directories from mysql data directory [ including ibdata and innodb log files ].
    3. start mysql, run mysqldump -uroot -ppass -A > file.sql
    4. stop mysql again
    5. reconfigure mysql to use file-per-table for innodb [ add to my.cnf innodb_file_per_table ]
    6. remove innodb ibdata / log files / directory with your database
    7. start mysql and recover data from your backup from point #3 - cat file.sql|mysql -uroot -ppass

    in the future you will be able just to backup & drop single table and restore it afterwards - thanks to file-per-table option.

    after removing the data (step 6) you need to boot with "mysqld --skip-grant-tables", otherwise mysqld will not boot because there aren't grant tables.

    • 4

  2. If you have innodb_file_per_table enabled, following the instructions below. However, if this was not already in use, your schema will need to be recreated in full for the space to be freed.

    With InnoDB, you need to alter the table. Newer versions of MySQL also perform the same function with optimize table as well.

    Be careful with large tables, as these commands lock the table.

    • 1

  3. When you put data in a table that is allowed to expand, it increases in size. When you DELETE data from the table, that size remains the same.

    There are a couple of things you can do:

    1) ALTER the table, like Warner suggested, to reduce the size.

    2) OPTIMIZE the table (if this is supported). That should also reduce the size.

    3) Back up the table, drop the table, then re-import the data into a new table.

    In every case, you should back up the data first. You should run a cronjob to do optimize or alter periodically, if you remove data on a regular basis. That remains true whether you're running MySQL, MSSQL, or Oracle. They all require maintenance.

    @Warner: Well, mostly preference. I'll preface this by saying I don't use MySQL for anything where performance is a factor, and if I did, I'd optimize, then alter the table to make it larger so there isn't an expansion penalty.

    For my needs it's 100% about automation and ease of maintenance. Running optimize keeps everything neat and tidy.

    • 1