Is There a Workaround to Reduce MySQL Table size?

Question

Long story short. I have a MySQL database that has only one table and is around 50GB, while my drive storage is almost full and cannot get reduced.

So the only way is to reduce the size of the 50GB MySQL data table (the app I use is MySQL Workbench).

I tried the Delete function but it does not work becase it requires additional storage (which I don't have) to perform the Optimize function so that the underlying IBD file can be truly reduced.

I tried copying the IBD file to a different drive in the same computer and reopening it there using MySQL but it does not work as it seems opening an IBD file is not straight forward and very complicated based on my online research. In addition, then I tried changing MySQL's folder to a different drive path with the IBD file saved there by editing the my.ini file but the app cannot work after the change of folder path.

So, is there any workaround such as creating a new database in some format through querying partial data from the existing table that MySQL can open (showing in the schema) before deleting the existing IBD file completely?

And what is the possible reason that MySQL app cannot run after I changed the folder path in my.ini file? since the current drive has a very small storage, I will mostly likely change it to a different drive which is bigger to cater for future database growth.

Answer 1

Check the disk space available. Estimate how big the table should be after deleting rows. If it looks like it will fit:

Plan A:

CREATE TABLE x LIKE real;
DROP any secondary indexes from `x`(keep the PRIMARY KEY)
INSERT INTO x SELECT * FROM real
    WHERE ... -- select only the desired rows
DROP TABLE real;
RENAME TABLE x TO real;
ALTER TABLE real
    ADD INDEX ...;-- each of the secondary indexes

Plan B:

SELECT ... INTO OUTFILE csv... FROM real;
TRUNCATE TABLE real;
LOAD DATA INFILE csv ...

Plan C:

Like Plan B, but run from another machine -- that way the csv file will not be taking disk space;

Plan D:

Use mysqldump from another machine. (Like Plan C; write the dump directly to this other server.)

More discussion in my blog: https://mysql.rjweb.org/doc.php/deletebig