How to reduce size of ibdata1 file in MySQL

If you use Innodb engine for your MySQL database, you may have noticed that the /var/lib/mysql/ibdata1 file is the most important and heavy file of your MySQL server. Day by day, this file will increase and can quickly consume all your hard drive. Reducing its size can be a little bit tricky. When "innodb _ file _ per _ table" is set to "0" in your my.cnf file, the ibdata1 file normally stores many classes of information, such as table data, table indexes, MVCC (Multiversioning Concurrency Control) data, table metadata, and so on. You can check this if you want a well explained schema. Many people create multiple ibdata files hoping for better diskspace management and performance. It does not help. Unfortunately, OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things: Makes the table's data and ...