I am in a bit of an awkward situation right now. I have a database with a few large InnoDB tables, one of which is about 145 GiB. I have been running a lot of maintenance on this table in particular, not realizing that a lot of that compounded in the ibdata1 file. Now I am in a situation where the ibdata1 file has grown so much that it has used up all of my disk space on my dedicated SQL SSD drive. I have read up on the matter and I know that I have to reload the database into the sql server for it to shrink the ibdata1 file. However, the mysqldump I took of my database is suspiciously small (it is about 2/3rds of my database size and is uncompressed), so what I want to do is load the dump file in a test database to check if there are any issues with it. The issue is, as I do not have any disk space left on my drive (this is a vicious cycle). So, what I would like to do is to move the ibdata1 file to my main HDD, which has plenty of space, and then add a line in the mysql configuration file telling the new location of the ibdata1 file, so I can free up enough space to load up a dummy databa. Can I do this? And if so, how do I do this?
Mysql's location; /mysql,
ibdata1 location; /tmpsql/ibdata1 (I can move this to let's say /var/lib/mysql or so if that would be better).
I already set innodb_file_per_table in the configuration file before it used up all my disk space. I have moved the ib_logfile* files as well to the same directory as ibdata1. The ibdata1 file was still used and extended regardless of this setting.
Please let me know if you need any additional information. Thanks in advance!