Mysql – Reducing the size of thesql files

MySQLmysql-5.7

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.

Best 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.