I want to reduce the size of the ibdata1 and ib_logfile0 files on my MySQL server 5.7. However, I do not want to activate the
What would be the best option to reduce the size of above mentioned files without activating
ibdata1 is messy to shrink. For 5.7, see https://dev.mysql.com/doc/refman/5.7/en/innodb-resize-system-tablespace.html for thorough details on dumping, deleting, and reloading. That’s essentially the only option.
You may as well change
my.cnf) before the load.
To resize just
innodb_fast_shutdown = 0
- do graceful shutdown
innodb_file_per_table=ON for ‘large’ tables, at least when doing
ALTER. Then maintenance on such tables not bloat ibdata1. (I prefer
OFF for small tables. Yeah, it is messy to have some tables FPT, some not.)
Note: changing FPT is not a way to shrink
ibdata1 only grows.
I recommend not letting free disk space drop below the size of the largest table.