MySQL and ibdata1

;;;;;;;  Борьба с разросшимся ibdata1 файлом MySQL
;;;;;;;  Случилась недавно проблемка. Получил уведомление о том, что на рабочем сервере заканчивается свободное место. Был слегка удивлен, ведь на нем хостились всего лишь тестовые версии сайтов, для демонстрации клиентам.
;;;;;;;
;;;;;;;  Пришлось локализовать «распухшее» место программой du:
;;;;;;;  # ищем переростка в корне директории больше 200 Мб
;;;;;;;  $ du -sh /*  --threshold=200M | sort -h
;;;;;;;
;;;;;;;  # ищем в следующей самой жирной директории, например
;;;;;;;  $ du -sh /var/*  --threshold=200M | sort -h
;;;;;;;
;;;;;;;  # ... и так далее, пока не локализуем виновника
;;;;;;;  В моем случае это оказался файл ibdata1 — файл с базами данных.
;;;;;;;  Как выяснилось, до 5.6.6, MySQL по умолчанию не «резала» таблицы и базы на отдельные папки и файлы, а сохраняла все в один файл.
;;;;;;;
;;;;;;;  Данные эти мне не нужны, поэтому TRUNCATE и дело с концом.
;;;;;;;
;;;;;;;  И вот тут меня ждал очень неприятный сюрприз — размер файла ibdata1 не уменьшился вообще.
;;;;;;;  ALTER TABLE <...> ENGINE=innodb не помогло. OPTIMIZE TABLE <...> — тоже.
;;;;;;;
;;;;;;;  Решение
;;;;;;;  Нашел решние на Stackoverflow.
;;;;;;;  Оказалось, что это, типа, так и должно быть. Файл можно уменьшить только удалив ibdata1 и ib_log.
;;;;;;;
;;;;;;;  1. Делаем дамп базы
;;;;;;;  Надо учесть, что базы mysql и performance_schema мы удалять не будем.
;;;;;;;  Самый простой способ, если баз не так много:
;;;;;;;
;;;;;;;  $ mysqldump  -uroot -proot --databases db_name1 [db_name2 db_name3...] > dump.sql
;;;;;;;  Если баз много, то можно воспользоваться такой конструкцией:
;;;;;;;
;;;;;;;  $ echo 'show databases;' | mysql -uroot -proot | grep -v ^mysql$ | grep -v ^performance_schema$ | xargs mysqldump -uroot -proot  --databases > dump.sql
;;;;;;;  Если баз, которые хочется исключить, больше двух, то можно, конечно, все их перечислить в примере выше. Но как по мне, так увеличивается вероятность ошибиться.
;;;;;;;  Поэтому я предлагаю сделать 3 простых шага:
;;;;;;;
;;;;;;;  # 1. получаем список всех таблиц в файл
;;;;;;;  $ echo 'show databases;' | mysql -uroot -proot > dblist.txt
;;;;;;;  # 2. удалить из этого файлика все ненужные таблицы
;;;;;;;  # 3. Сделать дамп
;;;;;;;  $ cat dblist.txt | xargs mysqldump -uroot -proot --databases > dump.sql
;;;;;;;  2. Удаляем все базы данных.
;;;;;;;  КРОМЕ mysql и performance_schema
;;;;;;;
;;;;;;;  $ mysql -uroot -proot
;;;;;;;  mysql > DROP DATABASE db_name1;
;;;;;;;  mysql > DROP DATABASE db_name2;
;;;;;;;  ...
;;;;;;;  mysql > DROP DATABASE db_nameN;
;;;;;;;  Важно из справки по DROP DATABASE
;;;;;;;  Оператор DROP DATABASE удаляет все таблицы в указанной базе данных и саму базу. Если вы выполняете DROP DATABASE на базе данных, символически связанной с другой, то удаляется как ссылка, так и оригинальная база данных. Будьте ОЧЕНЬ внимательны при работе с этой командой!
;;;;;;;
;;;;;;;  3. Тормозим mysql
;;;;;;;  Ubuntu:
;;;;;;;
;;;;;;;  $ sudo service mysqld stop
;;;;;;;  ## или
;;;;;;;  $ sudo service mysql stop
;;;;;;;  CentOS 7
;;;;;;;
;;;;;;;  $ sudo systemctl stop mysqla
;;;;;;;  4. Исправляем конфиг my.cnf
;;;;;;;  mcedit  \etc\mysql\my.cnf....
;;;;;;;  Чтобы найти все места откуда забирается конфиг, можно выполнить следующую команду:
;;;;;;;
;;;;;;;  $ mysql --help
;;;;;;;  По идее, вы должны будете увидеть нечто подобное (строки 8-9):
;;;;;;;
;;;;;;;  $ mysql --help
;;;;;;;
;;;;;;;  mysql  Ver 15.1 Distrib 10.3.20-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
;;;;;;;  Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
;;;;;;;
;;;;;;;  Usage: mysql [OPTIONS] [database]
;;;;;;;
;;;;;;;  Default options are read from the following files in the given order:
;;;;;;;  Слеши в обратном порядке потому что паста не поддерживает это
;;;;;;;  \etc\my.cnf \etc\mysql\my.cnf ~\.my.cnf
;;;;;;;  ...
;;;;;;;  ...
;;;;;;;  После того, как нашли конфиг, в него нужно найти секцию [mysqld] и добавить (или исправить, если есть) значение параметра:
;;;;;;;
;;;;;;;  innodb_file_per_table=1
;;;;;;;  5. Удаляем файлы ibdata1 и ib_log
;;;;;;;  6. Стартуем mysql
;;;;;;;  Ubuntu:
;;;;;;;
;;;;;;;  $ sudo service mysqld start
;;;;;;;  ## или
;;;;;;;  $ sudo service mysql start
;;;;;;;  CentOS 7
;;;;;;;
;;;;;;;  $ sudo systemctl start mysql
;;;;;;;  7. Восстанавливаем дамп
;;;;;;;  $ mysql -uroot -proot < dump.sql
;;;;;;;  Profit