Reducing the size of mysql files

Posted on

Question :

I have been working on MySQL database whose size is growing quite huge and I want to save on space on my cloud. I have tables whose engine is Innodb which holds the largest amount of data. I have already run OPTIMIZE TABLE on them which saved some space. Index data length is only a few MBs. I am looking for any other way to reduce the size of my growing tables and if possible the whole database. I am only keeping binary logs for 3 days for recovery. I will appreciate a solution that will cause no downtime or minimal downtime and risk. I also have a local replicated data of the same.

Answer :

u can do something like this:
add this to /etc/my.cnf

[mysqld]
expire_logs_days=3

and mysqld will delete them logs for you


if you talking about reduce the size of the table , i would advise this:

1st u need to sort from workbench or phpmyadmin , which tables consumes the most.
2nd maybe u can start plan on how to reduce that ‘biggest table’
3rd u think to think or consult with your superior , how long u need to keep the record , some company might wanna keep 3months record only ,then dump and keep somewhere(cloud or Harddisk) , or 1 year or 3 years .
4th before dump , maybe u can create archive table.
eg: your original table ‘test’ , archive table rename as ‘test_archive’ then move older records or records that u want to dump there.
5th dump and move to somewhere safer.

Leave a Reply

Your email address will not be published.