Clean - Shrink ibdata1

To shrink ibdata1 once and for all you must do the following:

MySQLDump all databases into a SQL text file (call it SQLData.sql)
Example, change directory to the /home directory which may be a partition with more space.
mysqldump -uroot -p --all-databases --extended-insert --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers > all-databases.sql

Drop all databases (except mysql schema) via PHPMyAdmin or shell.

service mysql stop

Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile

At this point, there should only be the mysql schema in /var/lib/mysql

service mysql start

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable; and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

  • 130 Users Found This Useful

Was this answer helpful?

Related Articles

How do I import into a MariaDB - MySQL database?

Log into your control panel and go into the MariaDB - MySQL section. Make sure you have an...

When trying to connect to MariaDB or MySQL you receive error 2003 Cannot connect to MySQL on x.x.x.x on port 3306 (10060)

Please ensure you have added the remote IP to the allow list in your hosting control panel...

Premature end of script headers

If you receive an Internal Server Error 500 and checking your log files displays "Premature end...

Disable MySQL strict mode on cPanel server

To disable mysql strict mode on a cPanel server, add the following to the end of the /etc/my.cnf...

The MySQL server is currently offline admin bin Cpanel/cpmysql/DBCACHE: exit 11

# This error shows in all user control panels via the "MySQL Databases" icon...   The MySQL...