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.

  • Please provide SHOW CREATE TABLE for the largest table. I have a lot of possible tips for shrinking the data. – Rick James Aug 29 at 23:09

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.

| improve this answer | |
  • Sorry but this generic advice doesn't really answer the asker's question. Do you have any particular commands they can use? – Colin 't Hart Jun 11 at 12:29
  • i've edit my post. thank you for your comment – noor syamiera Jun 12 at 2:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.