Apa yang dimaksud dengan ibdata1 file?

File ibdata1 berfungsi untuk menyimpan informasi data internal engine InnoDB mulai dari metadata setiap table InnoDB, change buffer, dublewrite buffer, undo log dan lain sebagainya. Beberapa variable innodb bisa di deklarasikan di dalam file /etc/my.cnf untuk perubahan nilainya, mulai dari innodb_ibuf_max_size untuk menentukan nilai chnage buffer dan variabel lainnya. Ketika variabel innodb_file_per_table aktif maka setiap table akan tersimpan di dalam masing-masing tablespace nya namun untuk shared tablespace masih tetap digunakan untuk menyimpan internal data yang lain seperti yang disebutkan sebelumnya sehingga memungkinkan terjadi pembengkakan ukuran file ibdata1 tersebut.

Apa penyebab file size ibdata membengkak?

Hal tersebut umumnya terjadi karena adanya transaksi query terhadap database yang tertahan lama dan masih aktif dalam database query processlist sehingga menyebabkan file size ibdata1 terus membengkak. Dari hal tersebut sudah bisa dipertimbangkan untuk pemrosesan lebih lanjut. Jika memang sekiranya commit terhadap transaksi query database tersebut maka bisa diabaikan namun apabila transaksi query tersebut dapat di-kill prosesnya maka file size ibdata1 akan berhenti bertambah.

Bagaimana mengurangi size file ibdata1 yang sudah besar?

Perihal pengurangan (shrink) besar ukuran file ibdata1 tidak memungkinkan untuk dapat diproses. Namun dapat ditangani dengan beberapa tahapan proses penghapusan file ibdata1 sebagai berikut.

  1. Backup seluruh database, kecuali database mysql, information_schema, performance_schema.
  2. Drop seluruh database kecuali 3 nama database mysql, information_schema, performance_schema tersebut.
  3. Stop daemon database mysql server.
  4. Backup dan delete file /var/lib/mysql/ibdata1 beserta dengan file log nya /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0
  5. Start kembali daemon database mysql server.
  6. Importkan kembali seluruh sql database yang sebelumnya terbackup.

Pada proses starting database mysql, maka fresh file ibdata1 akan tergenerate kembali secara otomatis dengan size yang masih bersih dari cache metadata tablespace innodb.
Dari tahapan proses tersebut, apabila database server hanya memiliki satu database saja yang digunakan maka dengan cara manual pemrosesan backup, drop, create, import satu database akan tetap efisien dan efektif. Namun bagaimana apabila di dalam server memiliki banyak database bahkan sampai ratusan seperti dalam ruang lingkup server shared hosting yang bisa menyimpan ratusan bahkan mungkin ribuan database, tentu cara manual satu persatu tidak akan efektif dan efisien.
Tahapan tersebut bisa dijadikan algoritma oleh masing – masing database/server administrator untuk pemrosesan secara efektif dan efisien tentunya. Dalam kasus ini didokementasikan pemrosesan refresh file size ibdata1 dalam server dengan sistem operasei GNU/Linux CentOS atau CloudLinux yang umum digunakan dalam lingkungan shared hosting server sehingga umumnya memiliki banyak database.

Setiap tahapan untuk melakukan generate ulang fresh file ibdata1 tersebut dideskripsikan dalam bentuk bash script sebagai berikut.

#!/bin/bash
# Adit Thaufan
# adit[at]chrootid.com
# Follow Chroot ID for more scripts 🙂
# https://twitter.com/chrootid
# https://plus.google.com/+cyberbtux
# https://facebook.com/chrootid
TIMESTAMP=$(date +"%F")
BACKUP_DIR="/backup/sql/$TIMESTAMP"
BACKUP_LIBSQL="/backup/libsql/$TIMESTAMP"
MYSQL_USER="root"
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump
mkdir -p "$BACKUP_DIR/" "$BACKUP_LIBSQL/"
echo "=========================="
echo "backup all mysql databases"
echo "=========================="
databases=$($MYSQL -Bse "SHOW DATABASES;" | grep -Ev "(information_schema|mysql|performance_schema)")
for db in $databases; do
  echo "backuping database $db"
  $MYSQLDUMP --force --opt --databases $db > "$BACKUP_DIR/$db.sql"
  echo "droping database $db"
  DBERROR=`$MYSQL -e "drop database $db;"|awk '{print $1}'`
  if [ $DBERROR == 'ERROR' ]
      then
      rsync -ah /var/lib/mysql/$db $BACKUP_LIBSQL
      rm -rf /var/lib/mysql/$db
  else
      echo "database $db dropped"
  fi
done
echo "================="
echo "stop mysql daemon"
echo "================="
CEKOS=`cat /etc/redhat-release|awk '{print $1}'`
if [ $CEKOS == 'CentOS' ]
        then
        CEKVER=`cat /etc/redhat-release|awk '{print $3}'|cut -d. -f1`
        if [ $CEKVER -gt 7 ]
        then
        systemctl stop mysql.service
        else
        service mysql stop
        fi
elif [ $CEKOS == 'CloudLinux' ]
        then
        CEKVER=`cat /etc/redhat-release|awk '{print $4}'|cut -d. -f1`
        if [ $CEKVER -gt 7 ]
        then
        systemctl stop mysql.service
        else
        service mysql stop
        fi
fi
echo "=============================="
echo "backup ibdata1 dan ib_logfile*"
echo "=============================="
mv /var/lib/mysql/ibdata1 /backup
mv /var/lib/mysql/ib_logfile* /backup
echo "=================="
echo "start mysql daemon"
echo "=================="
CEKOS=`cat /etc/redhat-release|awk '{print $1}'`
if [ $CEKOS == 'CentOS' ]
        then
        CEKVER=`cat /etc/redhat-release|awk '{print $3}'|cut -d. -f1`
        if [ $CEKVER -gt 7 ]
        then
        systemctl start mysql.service
        else
        service mysql start
        fi
elif [ $CEKOS == 'CloudLinux' ]
        then
        CEKVER=`cat /etc/redhat-release|awk '{print $4}'|cut -d. -f1`
        if [ $CEKVER -gt 7 ]
        then
        systemctl start mysql.service
        else
        service mysql start
        fi
fi
echo "======================="
echo "import all mysql backup"
echo "======================="
touch /backup/listbackupdb
ls $BACKUP_DIR/|cut -d. -f1 > /backup/listbackupdb
cat /backup/listbackupdb|while read listdb; do
	echo "creating database $listdb"
	$MYSQL -e "create database $listdb"
	echo "importing database $listdb"
	$MYSQL $listdb < $BACKUP_DIR/$listdb.sql
done

Atau bisa juga di copy paste yang telah didokumentasikan melalui URL http://script.chrootid.com/ibdata-shrink.txt tersebut.