mysql is stopping because of ibdata1 file size

Question

on mysql /var/lib/mysql

there is one file called 'ibdata1' which is very large and it took large space from my server and the mysql didn't start

how can I stop growing ibdata1 file size please help me

many thanks

Answer 1

It's a common question since mysql 5.5.

common way to resolve this issue:

1. Dump all databases
2. Stop MySQL server
3. Add "innodb_file_per_table" option to your my.cnf
4. import your databases

I found a shell script here. for your reference. https://github.com/uberhacker/shrink-ibdata1

Reference:

• [1] https://vdachev.net/2007/02/22/mysql-reducing-ibdata1/
• [2] https://dba.stackexchange.com/questions/39125/ibdata1-grows-exponentially-when-innodb-file-per-table-is-configured
• [3] https://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql
• [4] https://dba.stackexchange.com/questions/190618/shrink-ibdata1-and-ib-logfile0