≡ Menu

How to Shrink MySQL ibdata1 Size using innodb_file_per_table

In MySQL, when you are using InnoDB, all the tables and indexes are stored under the MySQL system tablespace.

MySQL system tablespace is ibdata1, which is located under /var/lib/mysql

The single ibdata1 file contains all the tables and indexes in your MySQL database. So, if you have a big database, this file size will grow really big.

In this tutorial, we’ll explain how to rebuild your entire MySQL database, and break the big MySQL system tablespace file into small individual MySQL table files.

mysql ibdata1

1. Big MySQL (and MariaDB) System Tablespace

There is a major drawback with the default big MySQL system tablespace approach.

Take this scenario as an example: You’ve uploaded 100GB worth of data into multiple tables in your MySQL.

Now, the ibdata1 file size will be around 100GB+.

# cd /var/lib/mysql

# ls -lh ibdata1
-rw-r-----. 1 mysql mysql 101G Jan 21 21:10 ibdata1

After few days, you deleted around 50GB worth of data from all those tables. The ibdata1 file size will not be reduced to around 50GB+, it will still stay at around 100GB+.

In the above case, later when you add 10GB worth of data to the tables, the ibdata1 file size doesn’t grow to 110GB, and stays at 100GB. Because, the file still has the unused space inside from the above 50GB of deleted data.

The problem is, you can’t reclaim those unused space after you delete the 50GB of data from ibdata1 file. There is a way to do it, but is too complicated (explained below), and involves taking the MySQL database down.

So, how do we avoid storing all the tables and indexes in a single ibdata1 file; instead store in multiple table files individually?

2. Set the innodb_file_per_table parameter

For this, you should use innodb_file_per_table parameter inside your /etc/my.cnf file under the “mysqld” section as shown below:

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

Note: If you are using MySQL 5.6.6 (or MariaDB) and higher, the above is the default setting.

In this example, on CentOS 6, the default MySQL that you get when installing from yum repository is still 5.1.73 as shown below.

# mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

So, in this case, we should set the innodb_file_per_table in the my.cnf file.

Anytime you make any changes to my.cnf, you should restart the MariaDB MySQL database.

service mysqld restart

Note: For some reason if you want to set this parameter while the database is running, and don’t want to shutdown MariaDB, you can do the following set global from the mysql prompt.

mysql> set global innodb_file_per_table=1;

3. New Tables (and index) as individual files

From now on, when you create a new MySQL table, you’ll get individual files.

In this example, I created a new table called employee and uploaded around 20GB worth of data into it.

mysql> use thegeekstuff

mysql> create table employee ...

mysql> insert into employee ..

Two things will happen here:

First, it will create a subdirectory with the database name “thegeekstuff” under the /var/lib/mysql directory

# ls -l /var/lib/mysql/
drwx------. 2 mysql mysql   266240 Jan  5 12:11 thegeekstuff
..

Second, under this database directory name (i.e under thegeekstuff directory), you’ll see individual file EMPLOYEE.IBD getting created. The size of this file will be the size of the data that you uploaded only to that table. In this case, since we uploaded 20GB worth of data into this table, the EMPLOYEE.IBD filesize is around 20GB as shown below.

# cd /var/lib/mysql/thegeekstuff/

# ls -lh 
-rw-r-----. 1 mysql mysql  21G Jan 21 21:17 employee.ibd

Note: If you are using MyISAM database, you’ll see individual .MYD, .FRM and .MYI files

4. Extract existing tables from ibdata1

Next, if you want to extract an existing table from ibdata1 to it’s own individual file, then you have to optimize the table.

Let us say you have a table called benefits under thegeekstuff database. This benefits table was created before we set the innodb_file_per_table in the my.cnf.

So, the benefits table will still be under ibdata1 file. To move this out of ibdata1 into it’s own IBD file, we have to optimize the table as shown below.

mysql> use thegeekstuff

mysql> optimize table benefits;

This will create the following individual file for the benefits table.

# cd /var/lib/mysql/thegeekstuff/

# ls -lh 
-rw-r-----. 1 mysql mysql  21G Jan 21 21:17 benefits.ibd

In this example, keep in mind that the original ibdata1 file still did not shrink. It is still around 100GB.

# ls -lh /var/lib/mysql/ibdata1
-rw-r-----. 1 mysql mysql 101G Jan 21 21:10 ibdata1

Note: You can also do the following.

mysql> alter table benefits engine=InnoDB;

5. Shrink ibdata1 File Size

Keep in mind that the ibdata1 still remains the same 101G size, it didn’t reduce the size yet.

# ls -lh /var/lib/mysql/ibdata1
-rw-r-----. 1 mysql mysql 101G Jan 21 21:10 ibdata1

To shrink the ibdata1 file, you need to perform the following steps:

6. Backup the Database

First, temporarily stop mysql database, and take a cold backup of the whole database. In case something goes wrong, you can use this cold backup to restore.

mkdir /backup

cd /var/lib

cp -r mysql /backup

Second, take a mysqldump backup of all the database.

mysqldump -u root -ptmppassword --all-databases > /backup/all-database.sql

For details on mysqldump, refer to how to use mysqldump article.

7. Drop all your database

Next, drop all your database one by one. To view all the database to be dropped, use “show databases”

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| thegeekstuff       |
| sales              |
| mysql              |
+--------------------+

In this example, we are dropping two databases (thegeekstuff and sales) that exist in the mysql.

# mysql -u root -ptmppassword
mysql> drop database thegeekstuff;

mysql> drop database sales;

Note: Do not drop information_schema and mysql database.

8. Delete ibdata and ib_logfile

Next, shutdown the MySQL database.

service mysqld stop

Next, remove the ibdata1 file and all the individual ib_logfile* files:

cd /var/lib/mysql/

rm ibdata1

rm ib_logfile0

rm ib_logfile1

9. Import all the Database

Note: At this point, you should already have the following in your my.cnf file.

[mysqld]
innodb_file_per_table

Start the MySQL database.

service mysqld start

Import all the database from the mysqldump backup that we took earlier.

mysql -u root -ptmppassword --all-databases < /backup/all-database.sql

At this stage, the ibdata1 file, which is the MySQL system tablespace will be created from scratch, and in our example, it will not be 100GB anymore.

Now ibdata1 will be only few MB in size. All the database tables will be stored as individual files under the corresponding database subdirectory under /var/lib/mysql/

Add your comment

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

Comments on this entry are closed.

  • Marko February 18, 2016, 2:59 am

    Thanks Ramesh, great “how to” as always!
    I have one question.
    Is it possible or better jet would that work:
    I have a few databases. If I backup the whole directory with rsync like this:
    rsync -a –delete-after ‘/var/lib/mysql/’ ‘/backup/mysql’

    Can I restore databases with rsync from that backup? If I restore all of them or just one of the database, would mysql work after such restore ?

    Thanks.

  • jose February 18, 2016, 10:12 am

    Since I didn’t read the article, by precipitation seems awesome. How huge can be considered the mentioned file to the mySQL doesn’t work fine or lose performance perceptively

  • Anonymous February 18, 2016, 10:55 am

    is the optimize step necessary? Won’t the seemingly mandatory dump, drop, restore process create the individual data files on its own, given the innodb_file_per_table setting is enabled?

  • Evert August 17, 2016, 3:25 am

    What about the ‘performance_schema’ database? Should it also be deleted at #7? Or should it be left where it is, like ‘information_schema’?

  • Harshal Shah November 30, 2016, 4:03 am

    Hi!
    You need to revisit the import command IMHO

    the mysql command does not accept flag –all-databases
    (at least in v5.5.53)

    I got below error:

    root@test:/var/lib/mysql# mysql -u debian-sys-maint -p –all-databases < ./all_databases_backup_deb.sql
    mysql: unknown option '–all-databases'

  • Steve March 1, 2017, 11:15 pm

    Thank you for this tutorial. It saved my hide.

  • Robert Pitera April 9, 2017, 10:42 am

    In case anyone hasn’t figured it out, simply removing the –all-databases argument worked fine.

  • Omar May 3, 2017, 6:18 am

    But how it will work with file per table? what happen when I delete rows from table , will it reduce the file of that table? because this is the issue we try to solve.

  • sysadm May 8, 2017, 6:39 pm

    Does not work.

    root@server mysql]# mysql -u root -p all-databases < /backup/all-database.sql
    Enter password:
    ERROR 1049 (42000): Unknown database 'all-databases'