mysql ibdata1 что за файл
Русские Блоги
Решение проблемы слишком большого размера файла MySQL ibdata1
Что такое файл ibdata1?
Что заставляет файл ibdata1 становиться все больше и больше?
ibdata1 хранит данные, индексы, кеши и т. д. Это самые важные данные MYSQL. Так что по мере того, как база данных становится все больше и больше, таблица будет становиться все больше, и это неизбежно. Если время будет все больше и больше, нам будет не так удобно иметь дело с журналами и пространством, и мы не знаем, с чего начать. Затем мы должны разобраться с этой ситуацией и сохранить данные в подбазе данных.
Как с этим бороться?
Сначала мы создаем резервную копию файла базы данных, а затем напрямую удаляем файл ibdata (на всякий случай лучше подготовить его один раз, чтобы обеспечить безопасность и полноту данных), а затем повторно импортируем файл базы данных!
Конкретные шаги заключаются в следующем (снимки экрана не полные, но вы должны сначала понять общую ситуацию и принципы):
1. Остановите бизнес и сделайте резервную копию всей базы данных один раз.
2. После завершения резервного копирования остановите базу данных.
systemctl stop mariadb или service mysqld stop
3. Измените файл конфигурации.
Добавьте следующую конфигурацию в [mysqld] innodb_file_per_table = 1, чтобы убедиться, что конфигурация вступила в силу, после перезапуска mysql выполните #service mysqld restart
show variables like ‘%per_table%’;
1 row in set (0.00 sec)
Статус innodb_file_per_table становится ВКЛ.
5. Удалите файлы и журналы ibdata1.
6. Восстановите базу данных.
Файлы данных хранятся отдельно (общее табличное пространство заменяется независимым файлом табличного пространства для каждой таблицы).
Сжатие и дефрагментация базы данных в MySQL и MariaDB
В данной статье мы рассмотрим методики сжатия и дефрагментации таблиц и баз данных в MySQL/MariaDB, которые позволят вам сэкономить место на диске с БД.
В крупных проектах со временем базы данных разрастаются до огромных размеров и всегда возникает вопрос, как же с этим бороться. Есть несколько вариантов для решения подобной проблемы. Вы можете уменьшить количество данных в самой базе, путем удаления старой информации, разделить базу на несколько, увеличить объем дискового пространства на сервере или сжать таблицы.
Другой важный аспект функционирование БД – необходимость периодической дефрагментации таблиц и баз данных, что позволяет существенно ускорить их работу.
Сжатие и оптимизация БД с типом таблиц InnoDB
Файлы ibdata1 и ib_log
На многих проектах с таблицами InnoDB встречается проблема с огромными размерами файлов ibdata1 и ib_log. Причина в большинвсте случае связан с неправильными настройками сервера MySQL/MariaDB или архитектурой БД. Вся информация из таблиц InnoDB хранится в файле ibdata1, пространство которого не высвобождается само по себе. Я предпочитаю хранить данные таблиц в отдельных файлах ibd*. Для этого нужно в конфигурационном файле my.cnf добавить строку:
Если же ваш сервер уже настроен и у вас есть несколько рабочих БД с таблицами InnoDB, нужно выполнить следующее:
После выполнения этой процедуры, все таблицы InnoDB будут хранится в отдельных файлах и файл ibdata1 не будет расти в геометрической прогрессии.
Сжатие таблиц InnoDB
Вы можете сжимать таблицы с данными типа text/BLOB. Если у вас есть подобные таблицы, вы можете сэкономить довольном много дискового пространства.
У меня имеется БД innodb_test с таблицами, которые потенциально можно сжать и высвободить дисковое пространство. Перед всеми работами я настоятельно рекомендую выполнить резервное копирование всех ваших БД. Подключаемся к серверу mysql:
В консоли mysql авторизуемся в нужной БД:
Чтобы вывести список таблиц и их размер, используйте запрос:
SELECT table_name AS «Table»,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS «Size in (MB)»
FROM information_schema.TABLES
WHERE table_schema = «innodb_test»
ORDER BY (data_length + index_length) DESC;
Где innodb_test — это имя вашей БД.
Есть вероятность, что некоторые таблицы можно сжать. Возьмём для примера таблицу b_crm_event_relations. Выполните запрос:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;
После выполнения, можно увидеть что за счет сжатия размер таблицы уменьшился с 26 до 11 Мб.
Благодаря сжатию таблиц вы можете сэкономить много дискового пространства на сервере. Но при работе со сжатыми таблицами вырастет нагрузка на процессор. Сжатие в таблицах нужно использовать, если у вас нет проблем с процессорными ресурсами, но есть проблема с местом на диске.
Сжатие таблиц MyISAM в MySQL
Для сжатия таблиц формата Myisam, нужно использовать специальный запрос с консоли сервера, а не в консоли mysql. Чтобы сжать нужную таблицу выполните:
Где /var/lib/mysql/test/modx_session — путь до вашей таблицы. К сожалению, у меня не было раздутой БД и пришлось выполнять сжатие на небольших таблицах, но результат все равно виден (файл сжался с 25 до 18 Мб):
Оптимизация таблиц и баз данных в MySQL/MariaDB
Для отптимизации таблиц и базы данных рекомендуется выполнять дефрагментацию. Проверим, есть ли в базе данных таблицы, которые требуют дефрагментации.
Войдем в консоль MySQL, выберем нужную БД и выполним запрос:
select table_name, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 50 order by data_free_mb;
Таким образом мы выведем все таблицы, которые имеют минимум 50 Мб неиспользуемого пространства:
data_length_mb — общий размер таблицы
data_free_mb — неиспользуемое пространство таблицы
Эти таблицы мы можем дефрагментировать. Проверим занимаемое место на диске до:
Чтобы оптимизировать эти таблицы, используйте следующую команду в консоли mysql:
# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;
После успешной дефрагментации, у вас должен быть примерно такой вывод результата:
Как видите, data_free_mb теперь равен 0 и в целом размеры таблицы значительно уменьшились (в 3-4 раза).
Также можно выполнить дефрагментацию с помощью утилиты mysqlcheck из консоли сервера:
Где innodb_test — это ваша БД
А b_workflow_file — имя нужной таблицы
Чтобы оптимизировать все таблицы нужной вам БД, запустите команду в консоли сервера:
Где innodb_test — имя желаемой БД.
Или запустите оптимизацию всех БД на сервере:
Если проверить размеры базы до и после оптимизации, то размер в целом уменьшился:
Таким образом для экономии места на сервере, вы можете периодически оптимизировать и сжимать ваши таблицы и БД. Повторюсь, перед проведением любых работ по оптимизации, создавайте резервную копию БД.
Что такое файл ibdata1 в моем каталоге / var / lib / mysql?
Зайдя в панель управления Webmin, я заметил, что практически все мое дисковое пространство заполнено. Я искал десять самых больших файлов / каталогов в моей системе и обнаружил, что файл с именем ibdata1 занимает около 94 ГБ пространства. Он находится в моем / var / lib / mysql каталоге.
Что делает ibdata1? Я в безопасности, чтобы удалить это? Я предполагаю, что это какая-то свалка, но это просто дикая догадка.
Файл ibdata1 является системным табличным пространством для инфраструктуры InnoDB.
Он содержит несколько классов для информации, жизненно важной для InnoDB
Обратите внимание на место ibdata1 во вселенной InnoDB (справа)
Вот мои прошлые статьи по отделению табличных данных и индексов от ibdata1
ЧТО ДЕЛАТЬ ДАЛЬШЕ
Вы можете продолжать хранить в ibdata1 все, но это делает создание снимков LVM настоящим трудоемким делом (мое личное мнение).
Вам нужно использовать мой пост StackOverflow и окончательно сжать этот файл.
Пожалуйста, запустите этот запрос:
Это скажет, сколько потраченного пространства может быть восстановлено после применения InnoDB Cleanup.
90 ГБ, куда некуда деваться.
Этот файл ibdata1 не, ibdatal и он содержит все ваши базы данных InnoDB. Если вы удалите его, вы потеряете все свои данные.
Если вы используете innodb в качестве движка MySQL, по умолчанию все ваши базы данных будут сохранены в ibdata1. Также есть файлы журналов ib_logfile0 и ib_logfile1. Не удаляйте эти файлы.
Борьба с разросшимся ibdata1 файлом MySQL
Случилась недавно проблемка. Получил уведомление о том, что на рабочем сервере заканчивается свободное место. Был слегка удивлен, ведь на нем хостились всего лишь тестовые версии сайтов, для демонстрации клиентам.
Начал разбираться что происходит. Первая идея – кто то, снова, нашел дыру и пытается спамить. Такое уже бывало. Рабочие папки exim (по моему) росли в размере с невероятной скоростью, пока последний не был отключен.
Теория не подтвердилась.
Пришлось локализовать “распухшее” место программой du :
В моем случае это оказался файл ibdata1 – файл с базами данных.
Как выяснилось, до 5.6.6, MySQL по умолчанию не “резала” таблицы и базы на отдельные папки и файлы, а сохраняла все в один файл.
Данные эти мне не нужны, поэтому TRUNCATE и дело с концом.
И вот тут меня ждал очень неприятный сюрприз – размер файла ibdata1 не уменьшился вообще.
ALTER TABLE ENGINE=innodb не помогло. OPTIMIZE TABLE – тоже.
Решение
1. Делаем дамп базы
Надо учесть, что базы mysql и performance_schema мы удалять не будем.
Самый простой способ, если баз не так много:
Если баз много, то можно воспользоваться такой конструкцией:
Если баз, которые хочется исключить, больше двух, то можно, конечно, все их перечислить в примере выше. Но как по мне, так увеличивается вероятность ошибиться.
Поэтому я предлагаю сделать 3 простых шага:
2. Удаляем все базы данных.
КРОМЕ mysql и performance_schema
Важно из справки по DROP DATABASE
Оператор DROP DATABASE удаляет все таблицы в указанной базе данных и саму базу. Если вы выполняете DROP DATABASE на базе данных, символически связанной с другой, то удаляется как ссылка, так и оригинальная база данных. Будьте ОЧЕНЬ внимательны при работе с этой командой!
3. Тормозим mysql
Ubuntu:
CentOS 7
4. Исправляем конфиг my.cnf
По идее, вы должны будете увидеть нечто подобное (строки 8-9):
После того, как нашли конфиг, в него нужно найти секцию [mysqld] и добавить (или исправить, если есть) значение параметра:
Файл ibdata1 в MySQL
Как же раздражает, когда на сервере место заканчивается. Прямо слов нет.
Причем происходит это постоянно. И заканчивается, главное, «под ноль», до последнего байта, в результате чего все «падает».
Нет, ну в самом деле, как так? Ось Дебиан, из софта все по-минимуму, сайтов всего 10, из которых большинство заглушки, какое-то реальное место занимают только два-три. И при этом уходит суммарно 30 гигабайт. Так же быть не должно?
Пошел инвестигейтить, хотя надо было этим заняться еще очень давно.
Так, сайты весят сколько и должны, чуть меньше 10 гигов. Остается еще 20 гигабайт, не может же Дебиан столько «съесть».
Стал «пробивать» все подряд, сначала корневые директории, потом их содержимое. И через пять минуток натыкаюсь на это:
15 гигабайт! И что это вообще такое?
Вот он и продолжал расти, день ото дня, месяц к месяцу. По чуть-чуть, но все же. За четыре года с момента изначального разворачивания окружения у меня и «накапало» 15 гигов. Ведь баз данных за это время было создано и удалено десятки, если не сотни.
Как сие пофиксить? Увы, только фактически «реланчем».
И раз: бэкапим все наши InnoDB базы, за исключением системных information_schema, mysql и performance_schema.
И два: дропаем все наши базы (акромя системых information_schema, mysql и performance_schema соответственно).
И три: выключаем MySQL.
И четыре: в конфигурационный файл my.cnf добавляем эту строчку:
Нужна для того, чтобы данные со всех InnoDB баз перестали писаться в один файлик ibdata1, и под каждую таблицу создавался свой системный файл. Вроде для MySQL 5.6 это стоит по дефолту, но у меня MySQL 5.5.
И пять: удаляем этот самый файлик ibdata1, а также файлы логов ib_logfile0 и ib_logfile1 (лежат там же).
И шесть: включаем MySQL.
И семь: импортируем наши дампы обратно.
Проверяем освободившееся место. Было 220 мегабайт. Стало 14 гигабайт. Счастье-то какое!
Комментарии
Нормальная тема, такая дырища в плане ресурсов на пустом месте.
Угу, погулил, у людей там и на сотни гигов разрастается, а если есть большая InnoDB табличка с частым insert/delete, так вообще в небеса улетает. По-хорошему, надо продолжать оптимизировать конфиги дальше, где возможно перейти на MyISAM, и т. д., но меня пока устроил и такой быстрофикс.
Здравствуйте. Столкнулся с такой же проблемой. Вы в статье написали первым пунктом, что нужно бэкапить все базы. Получается даже information_schema, mysql и performance_schema тоже бэкапить? И потом восстанавливать их из бэекапа?
Ответ на Здравствуйте. Столкнулся с… от Николай Александров
Нет, системные базы (information_schema, mysql и performance_schema) трогать не надо, они как были, так и остаются. Только созданные «пользовательские», я в том числе бэкапил и пересоздавал и базу под phpmyadmin, т. к. по сути тоже стороннее приложение.
Ответ на Добрый день!… от tulvit
Спасибо за ответ. Может было бы хорошо подправить первый пункт статьи, чтобы не было недоразумений. Ещё один вопрос, у меня есть базы как на движке MyISAM так и на InnoDB, проблема как я понял с этим возросшимся файлом касается только баз на InnoDB. Получается базы на MyISAM тоже можно не трогать, не удалять?
Ответ на Спасибо за ответ. Может было… от Николай Александров
Пост поправил, спасибо!
Получается базы на MyISAM тоже можно не трогать, не удалять?
Возможно, имеет смысл в том числе забэкапить и системные базы, или только InnoDB таблицы из системных баз, а потом импортнуть за компанию. Но утверждать не буду, просто мысли вслух, т. к. судя по гуглу, большинство обходится и без этого.
Но в целом да, получается, что «белое пятно» все-таки остается, что не хорошо. «Копать» дальше и тестить сейчас уже видимо не буду 🙁
Хотя еще чуток погулил:
— information_schema по ходу дела обновляется при каждом рестарте MySQL, так что можно не переживать.
— performance_schema содержит временные данные, и тоже так и так постоянно обновляется, можно удалять, можно не удалять.