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.
- Backup seluruh database, kecuali database mysql, information_schema, performance_schema.
- Drop seluruh database kecuali 3 nama database mysql, information_schema, performance_schema tersebut.
- Stop daemon database mysql server.
- Backup dan delete file /var/lib/mysql/ibdata1 beserta dengan file log nya /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0
- Start kembali daemon database mysql server.
- 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.