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 '20 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.

  • Sorry but this generic advice doesn't really answer the asker's question. Do you have any particular commands they can use? Jun 11 '20 at 12:29
  • i've edit my post. thank you for your comment Jun 12 '20 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.