I want to reduce the size of the ibdata1 and ib_logfile0 files on my Mysql server 5.7 I do not want to activate the innodb_file_per_table option. What would be the best option to reduce the size without activating innodb_file_per_table? Can someone help me?
Thanks
You should check the parameters of your mysql configuration file (usually the configuration file is mysqld.cnf ) and see what you have set in the parameters innodb_data_file_path and innodb_log_file_size .
Usually the ibdata1 part in a default that is 48MB (as far as I remember) but it gets larger as the content of your database grows. The downside is that it does not decrease when you delete information. If you would like to restrict the maximum size of your tablespace, and your configuration file say, for example
innodb_data_file_path = /ibdata/ibdata1:48M:autoextend
You could just put
innodb_data_file_path = /ibdata/ibdata1:96M
(that is, it does not autoincrement, but you give yourself a wider margin so that afterwards you will not miss an error that the tablespace was filled).
If the parameter does not appear, it means that mysql is using the default value (again, it can be 48M autoextend or 10M autoextend depending on the version)
If you wanted to instead simply reclaim the space freed by having deleted a very large table, your only option would be to dump a backup of the current database, stop the service mysql , delete the ibdata , restart the service and load the dump.
Regarding the size of the logfiles, if in your configuration file you had
innodb_log_file_size = 48M
You could change it to another size, stop the service, delete the log files and restart the service. In this case I would back the full contents of the mysql data folder in case something goes wrong.
If the parameter does not appear, it means that you are using the default parameter.
In general, I would recommend either using innodb_file_per_table (which I do not know what you find wrong) or simply attacking more storage to the machine. Space is cheap. The data is valuable .
But, anyway, if you're determined to do it, even though it's not such a good idea, and you want to see how it's done and how it turns out to be purely investigative, at least make sure you get a copy of the data folder (usually ibdata ).