MySQL/InnoDB one file per table

Після видалення бази данних чи таблиці MySQL розмір файлу ibdata1 не змінюється, що в результаті може призвести до повного заповнення диску. Для вирішення цієї проблеми існує опція innodb_file_per_table, що дозволяє зберігати кожну таблицю в окремому файлі на відміну від стандартної поведінки зберігання таблиць. Як наслідок, при видаленні таблиці, видалиться файл, що відповідає за її вміст та звільниться простір на диску. Опція innodb_file_per_table увімкнена по замовчуванню в Mysql версії 5.6.6 і старше.

Щоб перейти до нового варіанту зберігання таблиць необхідно виконати декілька пунктів. Розглянемо варіант міграції всіх баз на новий метод їхнього зберігання. Перш за все, про всяк випадок, забекапимо всю директорію /var/lib/mysql:

# cd ~
# mkdir backup
# cd backup
# service mysql stop
# cp -ra /var/lib/mysql mysqldata_backup
# service mysql start

Зробимо дамп всіх існуючих баз:

# mysqldump --routines --events --flush-privileges --all-databases > all-db.sql

Створимо sql-процедуру на видалення всіх баз данних окрім бази даних mysql:

# mysql -e "SELECT DISTINCT CONCAT ('DROP DATABASE ',TABLE_SCHEMA,' ;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA <> 'mysql' AND TABLE_SCHEMA <> 'information_schema';" | tail -n+2 > drop.sql

Видаляємо все зайве:

# mysql < drop.sql

Перевіряємо в консолі mysql чи наявні ще хоча б якісь бази InnoDB:

mysql> SELECT table_name, table_schema, engine FROM information_schema.tables WHERE engine = 'InnoDB';

Якщо ні - це добрий знак.

Зупиняємо mysql і видаляємо указані файли з диску:

# service mysql stop
# rm /var/lib/mysql/ibdata1 && rm /var/lib/mysql/ib_logfile0 && rm /var/lib/mysql/ib_logfile1

Редагуємо файл конфігурації my.cnf. Додаємо опцію "innodb_file_per_table  = 1" до секції [mysqld]. Має виглядати на кшталт цього:

# vim /etc/mysql/my.cnf
...
[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
innodb_file_per_table  = 1
innodb_file_format = barracuda
...

З використанням опції innodb_file_per_table можна скористатись додатковою фічею innodb_file_format = barracuda, що ймовірно зекономить місце, що займають таблиці.

Запускаємо mysql і розгортаємо повний дамп:

# service mysql start
# mysql < all-db.sql

Генеруємо нову базу performance_schema:

# mysql_upgrade --force

Перевіряємо результат трудів. Ось так виглядала ibdata1 до змін:

# du -sm * | sort -nr
99 ibdata1
9 my_db
5 ib_logfile1
5 ib_logfile0
1 statusnet
1 mysql
1 all-db.sql
0 mysql.sock

А так після додавання innodb_file_per_table  = 1:

# du -sm * | sort -nr
219 my_db
18 ibdata1
10 statusnet
5 ib_logfile1
5 ib_logfile0
1 test
1 mysql_upgrade_info
1 mysql
0 mysql.sock

Вміст директорії для бази my_db тепер матиме приблизно такий вигляд:

# ls -l /var/lib/mysql/my_db
...
-rw-rw----. 1 mysql mysql     8772 вер 16 12:33 wishlist_item_option.frm
-rw-rw----. 1 mysql mysql   114688 вер 16 12:33 wishlist_item_option.ibd
...

# file wishlist_item_option.frm wishlist_item_option.ibd
wishlist_item_option.frm: MySQL table definition file Version 10
wishlist_item_option.ibd: data 

Із додатковими перевагами і певними недоліками цього методу можна ознайомитись тут http://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html

Посилання:
https://rtcamp.com/tutorials/mysql/enable-innodb-file-per-table/
http://stackoverflow.com/a/484773/2971192