I wanted to do a backup of all data before trying anything but had repeated problems because of the mysql and performance_schema databases - Dump All Databases in MySQL - Fails on mysql and performance_schemaDump All Databases in MySQL - Fails on mysql and performance_schema . In the end I decided to back up my data and try something different with the option of reinstalling everything if it all went wrong. This is what I did:-
- Back up my data as both a self-contained (sql) file and as a project folder.
- Stopped the server and edited /etc/mysql/my.cnf to contain “innodb_file_per_table = 1”
- Deleted ibdata1, ib_logfile0 and ib_logfile1, and all my data
- Restarted the server
- Reloaded my data from the self contained file
So far everything looks fine with SQL scripts and Python programmes searching and manipulating the data as expected. The total space taken on disc is now just over 20GB with ibdata1 having shrunk from over 40GB to 17MB.
Obviously I am not yet expert enough to recommend this to others but this worked for me. Others comments welcome...