Huge ibdata1 file in MySQL/MariaDB, how to shrink or purge

IT Systems
#1

Unfortunately there is no process or command what can shrink or purge ibdata1 file. There is only one way is to drop all you DB’s with InnoDB engine and restore them from the backup.

Depends on the size of you database you need plan your downtime for the service.

First, add following to the MySQL config file (/etc/my.cnf or other location depends on your installation):

[mysqld]
innodb_file_per_table=1

This change will allow to store all indexes separately per table.
https://dev.mysql.com/doc/refman/8.0/en/innodb-file-per-table-tablespaces.html

Then follow next steps what I applied at many my servers successfully and without issues:

  1. Backup all you InnoDB engine DB’s with mysqldump command:

mysqldump -u super_user -p db_name > db_name.sql

Note: you can avoid backing up mysql and performance_schema databases.
Note: this command will help you to find databases with InnoDB engine:

SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = ‘innodb’;

  1. After the backup process is completed you will need to drop all InnoDB databases ( I sum you backed up all of them):

mysql -u super_user -p -e “drop database db_name;”

  1. Stop mysql/mariadb service:

systemctl stop mariadb
or
systemctl stop mysqld
or
service mariadb stop
or
service mysqld stop

  1. Delete all following files from the MySQL db location, usually /var/lib/mysql:

rm -rf ibdata1
rm -rf ib_logfile0
rm -rf ib_logfile1

  1. Start mysql/mariadb service:

systemctl start mariadb
or
systemctl start mysqld
or
service mariadb start
or
service mysqld start

Note: at this stage ibdata1 and ib_log files will be recreated

  1. Create empty databases where you will restore from the backup:

mysql -u super_user -p -e “create database db_name;”

  1. Restore all backed up databases back:

mysql -u super_user -p dbname < db_name.sql