Replace MySQL Use Of ibdata1 With innodb_file_per_table

Overview

By default, MySQL versions prior to 5.6.6 store all table data in a single file called ibdata1. Since that version, the default has changed to store each table in its own set of files.  This is specified using the innodb_file_per_table setting.

The following steps will replace the use of the single file with that of one file per table.

This process has been successfully tested on Ubuntu 16.04 using MySQL 5.7.20.

1. Make a cold backup of the database in case anything goes wrong

Make a cold backup in case of a problem. Make sure there is enough space on the target partition/folder.

sudo su -
BACKUP_DIR="/backup"
service mysql stop
mkdir -p "${BACKUP_DIR}/mysql"
rsync -avHK --delete-excluded /var/lib/mysql/ "${BACKUP_DIR}/mysql/"
service mysql start

2. Export and drop non-system databases

cd /tmp

# Linux bin paths, change this if it can't be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"

# MySQL login credentials (will not be shown in 'ps' when used)
MYSQL_USER=root
MYSQL_PASS=
MYSQL_HOST=localhost

# Backup users and grants
echo "Backing up users and grants..."
${MYSQLDUMP} --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s\nhost = %s" "$MYSQL_USER" "$MYSQL_PASS" "$MYSQL_HOST") mysql --tables user db > "${BACKUP_DIR}/users_grants.sql"

# SYS_DB_LIST is a space separated list of system database names
SYS_DB_LIST="information_schema mysql performance_schema sys"

# DB_LIST is a list of all database names
DB_LIST="$(${MYSQL} --defaults-extra-file=<(printf "[mysql]\nuser = %s\npassword = %s\nhost = %s" "$MYSQL_USER" "$MYSQL_PASS" "$MYSQL_HOST") -Bse 'SHOW DATABASES')"

for DB in ${DB_LIST}
do
  SKIPDB=-1
  if [ "${EXCLUDE_DB}" != '' ]; then
    for SYS_DB in ${SYS_DB_LIST}
    do
      [ "$DB" == "$SYS_DB" ] && SKIPDB=1 || :
    done
  fi
 
  if [ "$SKIPDB" == "-1" ]; then
    echo "Exporting $DB..."
    ${MYSQLDUMP} --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s\nhost = %s" "$MYSQL_USER" "$MYSQL_PASS" "$MYSQL_HOST") --single-transaction --events --ignore-table=mysql.event $DB > "${BACKUP_DIR}/database_$DB.sql"
    echo "Dropping $DB..."
    ${MYSQL} --defaults-extra-file=<(printf "[mysql]\nuser = %s\npassword = %s\nhost = %s" "$MYSQL_USER" "$MYSQL_PASS" "$MYSQL_HOST") -Bse "DROP DATABASE $DB"
  fi
done

3. Configure MySQL to use innodb_file_per_table

Add to /etc/mysql/my.cnf

[mysqld]
innodb_file_per_table = 1

4. Reinstall MySQL Server

service mysql stop
apt remove mysql-server-5.7
apt autoremove
apt purge
cd /var/lib/mysql
rm -rf *
cd ..
apt install mysql-server-5.7

5. Import data back into database

service mysql start

echo "Restoring users and grants..."
${MYSQL} --defaults-extra-file=<(printf "[mysql]\nuser = %s\npassword = %s\nhost = %s" "$MYSQL_USER" "$MYSQL_PASS" "$MYSQL_HOST") mysql < "${BACKUP_DIR}/users_grants.sql"

for DB in $DB_LIST
do
  SKIPDB=-1
  if [ "${EXCLUDE_DB}" != '' ]; then
    for SYS_DB in ${SYS_DB_LIST}
    do
      [ "$DB" == "$SYS_DB" ] && SKIPDB=1 || :
    done
  fi
 
  if [ "$SKIPDB" == "-1" ]; then
    echo "Creating $DB..."
    ${MYSQL} --defaults-extra-file=<(printf "[mysql]\nuser = %s\npassword = %s\nhost = %s" "$MYSQL_USER" "$MYSQL_PASS" "$MYSQL_HOST") -Bse "CREATE DATABASE $DB"
    echo "Restoring $DB..."
    ${MYSQL} --defaults-extra-file=<(printf "[mysql]\nuser = %s\npassword = %s\nhost = %s" "$MYSQL_USER" "$MYSQL_PASS" "$MYSQL_HOST") --database=$DB < "${BACKUP_DIR}/database_$DB.sql"
  fi
done

To stop the change and restore the database to its previous state, do the following

1. Undo configuration change

Comment out the innodb_file_per_table option in /etc/mysql/my.cnf

2. Restore database from cold backup

service mysql stop
rsync -avHK "${BACKUP_DIR}/mysql/" /var/lib/mysql/
service mysql start

Leave a Reply