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.
1 2 3 4 5 6 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | 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
1 2 | [mysqld] innodb_file_per_table = 1 |
4. Reinstall MySQL Server
1 2 3 4 5 6 7 8 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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
1 2 3 | service mysql stop rsync -avHK "${BACKUP_DIR}/mysql/" /var/lib/mysql/ service mysql start |