If you’ve been around the database space at least for a little while and know your way around MySQL or any of its flavors, you will surely know what InnoDB is – it’s one of the main storage engines offered by MySQL and it’s also the default storage engine ever since MySQL 5.5.5 was released in 2010. InnoDB comes with many unique quirks and features unique to itself – but perhaps the most fascinating one is the main file of the storage engine – ibdata1.
What Is ibdata1?
ibdata1 is the king of the entire InnoDB infrastructure – the file is frequently referred to as the “tablespace data file” and that’s because of a good reason: the file stores everything related to InnoDB including, but not limited to:
As far as MySQL is concerned, ibdata1 is exclusive to its flagship storage engine – InnoDB (the storage engine is also the default one when using Percona XtraDB.) InnoDB is widely known for its ability to support ACID transactions – ACID is a very important feature guaranteeing data integrity and consistency even when problems arise – for example if we’re running a query and our electricity goes out, our data won’t be affected. However, there’s a caveat – while the data stored inside InnoDB can be deleted whenever we desire,
data stored in ibdata1 cannot
. While the tables based upon InnoDB can be dropped anytime by executing a simple DROP TABLE command (see below), the size of the ibdata1 file can be only defined in the my.cnf file.
Ask any seasoned DBA who’s working with MySQL, and you can be sure that he will advise you that it’s best to leave ibdata1 alone and go do other stuff instead. Part of that answer is hidden behind the core of how ibdata1 is built in the first place – rewind and have a read through a couple of paragraphs before this one – remember how we told you that ibdata1 stores all of the data relevant to InnoDB?
The problem with ibdata1 is that when we’re working with bigger data sets, the file can get unfathomably large – and if we don’t have the
innodb_file_per_table
option set to 1 (which is the default option starting from MySQL 5.6.6), it cannot shrink.
We need to delete all of the databases (folders) under the /var/lib/mysql/mysql*.*.**/data folder (replace *.*.** with your MySQL version) directory except the “mysql” and “performance_schema” folders (they are required for MySQL to function correctly.)
We need to ensure that MySQL is stopped and delete the ibdata1 and ib_logfile0 & ib_logfile1 files. These files are vital for InnoDB since they both store all of the redo logs and MySQL reads through them once it’s restarted, but if the appropriate databases aren’t in the exact same place they were when MySQL was last shut down, MySQL will have issues starting up.
Finally, we need to restart MySQL as a whole.
Problem solved – from now on, deleting tables based on the InnoDB storage engine will be a piece of cake even if we won’t be able to access MySQL through a GUI – deleting both of the files associated with a table will do the trick since from now on, ibdata1 will only store metadata associated with the tables running the InnoDB or XtraDB storage engines, but not the data itself.
In our example, ibdata1 is set to be of 10GB in size initially, but it can extend (
autoextend
) and the maximum size (
max
) of this file should not exceed 20GB. Since in the previous step we‘ve ensured that the file will only store metadata relevant to the tables and not the data itself, 20GB of space should be more than enough.
In this article, we have walked you through the most important file in the entire MySQL infrastructure – ibdata1. We‘ve taught you how to ensure that this file doesn‘t cause any problems in the long run, and demonstrated some features of DbVisualizer along the way. If you‘ve enjoyed reading this article,
keep an eye on our blog
to find all kinds of articles related to DbVisualizer and the art of database engineering to help unleash the power of databases and help your company succeed, and we will see you in the next one.
We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy
here ↗
