Shrink ibdata1 and ib_logfile0

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 innodb_file_per_table option.

What would be the best option to reduce the size of above mentioned files without activating innodb_file_per_table?

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 innodb_log_file_size (in my.cnf) before the load.

To resize just iblog* —

1. innodb_fast_shutdown = 0
2. do graceful shutdown
3. change innodb_log_file_size
4. start

I recommend innodb_file_per_table=ON for ‘large’ tables, at least when doing CREATE or 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; ibdata1 only grows.

I recommend not letting free disk space drop below the size of the largest table.