How to shrink/purge ibdata1 file in MySQL

user_373908 0
Viewed 591386

I am using MySQL in localhost as a "query tool" for performing statistics in R, that is, everytime I run a R script, I create a new database (A), create a new table (B), import the data into B, submit a query to get what I need, and then I drop B and drop A.

It's working fine for me, but I realize that the ibdata file size is increasing rapidly, I stored nothing in MySQL, but the ibdata1 file already exceeded 100 MB.

I am using more or less default MySQL setting for the setup, is there a way for I can automatically shrink/purge the ibdata1 file after a fixed period of time?

innodb mysql database
9 Answers

Quickly scripted the accepted answer's procedure in bash:

#!/usr/bin/env bash
dbs=$(mysql -BNe 'show databases' | grep -vE '^mysql$|^(performance|information)_schema$')
mysqldump --events --triggers --databases $dbs > alldatabases.sql && \
    echo "$dbs" | while read -r db; do
        mysqladmin drop "$db"
    done && \
    mysql -e 'SET GLOBAL innodb_fast_shutdown = 0' && \
    /etc/init.d/mysql stop && \
    rm -f /var/lib/mysql/ib{data1,_logfile*} && \
    /etc/init.d/mysql start && \
    mysql < alldatabases.sql

Save as purge_binlogs.sh and run as root.

Excludes mysql, information_schema, performance_schema (and binlog directory).

Assumes you have administrator credendials in /root/.my.cnf and that your database lives in default /var/lib/mysql directory.

You can also purge binary logs after running this script to regain more disk space with:

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;
user_49046950

What nobody seems to mention is the impact innodb_undo_log_truncate setting can have.

After reading Percona's blog post about the topic, I've enabled in my MariaDB 10.6 the truncation of UNDO LOG entries which filled 95% of ibdata1, and, after a complete drop and restore, from that moment on my ibdata1 never grew anymore.

With the default innodb_undo_log_truncate = 0 my ibdata1 easily reached 10% of databases space occupation, aka tens of Gigabytes.

With innodb_undo_log_truncate = 1, ibdata1 it's firm at 76 Mb.

user_2202390
Related