Недавно была заметка про знание СУБД. Видел комментарии, мол пусть DBA разбираются в нюансах СУБД, а админам это ни к чему. Расскажу про один случай, который со мной на днях случился. Сразу скажу, что это не будет рекомендацией к действиям, так как сам я ситуацию хоть и разрешил, но не лучшим образом. Плюс, понервничал немного.
Приходит уведомление в мониторинг, что дамп базы выполнен с ошибкой. База MySQL, размер дампа ~10ГБ. Так как размер позволяет, я бэкаплю её дампом несколько раз в день. Он проходит быстро, проблем не вызывает. Как настроен мониторинг, рассказывал отдельно.
Смотрю ошибку в логе дампа:
В таблицу постоянно что-то пишется. Движок сайта по кронам сам её чистит. Судя по всему, что-то пошло не так. Сделал дамп без этой таблицы, добавив ключ
Первым делом хотел поднять копию виртуалки и попробовать разобраться там. Но, к сожалению, виртуалка очень большая. Восстанавливаться будет долго. Захотелось решить проблему здесь и сейчас, потому что было время и возможность. На первый взгляд она не особо критичная и грозит только потерей таблицы в худшем случае. Забегая вперёд скажу, что этот худший случай и произошёл.
Захожу в консоль mysql и смотрю:
Ничего хорошего. Пробую починить автоматически:
В логе mariadb куча ошибок:
Для надёжности зажмурился и перезапустил службу mariadb. Перезапуск прошёл штатно, база поднялась, но таблица не ожила. Всё те же ошибки. Причём данные в неё, судя по всему, записывались.
Тут я немного приуныл, так как знаний не хватает решить проблему. Не понятна причина. Я узнал, что хранится в таблице и понял, что эти данные не представляют ценности и могут быть потеряны. Дальше поискал инфу в гугле и поговорил с chatgpt. Последний не предложил ничего нового из того, что я видел в гугле, но накидал неактуальных рекомендаций для движка MyISAM, хотя у меня InnoDB.
Нашёл следующие варианты решения проблемы:
1️⃣ Удалить и пересоздать все индексы.
2️⃣ Создать пустую таблицу с аналогичной структурой, скопировать туда данные, исходную удалить, новую таблицу переименовать.
3️⃣ Запустить сервер с активным параметром
4️⃣ Пересобрать таблицу командой:
Судя по отзывам, последняя команда будет выполняться очень долго. Третья может привести к долгому старту службы или ещё каким-то проблемам. Не хотелось запускать их на работающем сервере. Решил пересоздать индексы. Их там всего два, но один PRIMARY. Его нельзя просто взять и удалить. Вычитал на stackoverflow один совет. Зайти через phpmyadmin, убрать чекбокс Auto Increment на столбце с этим индексом, сохранить, потом зайти, вернуть AI. Возможно на живой таблице это и сработало бы, но у меня вылетела ошибка, ничего не сохранилось, таблица вообще умерла.
С этого момента некоторые юзеры начали получать ошибку MySQL Query Error! Дальше не стал экспериментировать, грохнул таблицу и создал заново. Структуру посмотрел в живом дампе. Всё сразу заработало, данные потекли в таблицу, дамп нормально создаётся.
Если у кого-то есть советы по теме, с удовольствием выслушаю. Ещё раз уточню, что стал делать опасные манипуляции, когда понял, что данные позволительно потерять. И я мог себе позволить простой в 10-15 минут.
#mysql
Приходит уведомление в мониторинг, что дамп базы выполнен с ошибкой. База MySQL, размер дампа ~10ГБ. Так как размер позволяет, я бэкаплю её дампом несколько раз в день. Он проходит быстро, проблем не вызывает. Как настроен мониторинг, рассказывал отдельно.
Смотрю ошибку в логе дампа:
mysqldump: Error 1034: Index for table 'b_stat_session_data' is corrupt; try to repair it when dumping table `b_stat_session_data` at row: 329011В таблицу постоянно что-то пишется. Движок сайта по кронам сам её чистит. Судя по всему, что-то пошло не так. Сделал дамп без этой таблицы, добавив ключ
--ignore-table=dbname.b_stat_session_data. Потом сразу проверил более старые дампы и бэкап виртуалки. Всё на месте, можно разбираться.Первым делом хотел поднять копию виртуалки и попробовать разобраться там. Но, к сожалению, виртуалка очень большая. Восстанавливаться будет долго. Захотелось решить проблему здесь и сейчас, потому что было время и возможность. На первый взгляд она не особо критичная и грозит только потерей таблицы в худшем случае. Забегая вперёд скажу, что этот худший случай и произошёл.
Захожу в консоль mysql и смотрю:
> CHECK TABLE b_stat_session_data;Warning | InnoDB: The B-tree of index PRIMARY is corrupted. |Warning | InnoDB: Index IX_GUEST_MD5 is marked as corrupted |error | CorruptНичего хорошего. Пробую починить автоматически:
> OPTIMIZE TABLE b_stat_session_data;note | Table does not support optimize, doing recreate + analyze insteaderror | Got error 106 "Transport endpoint is already connected" from storage engine InnoDBstatus | Operation failedВ логе mariadb куча ошибок:
[ERROR] mariadbd: Index for table 'b_stat_session_data' is corrupt; try to repair itДля надёжности зажмурился и перезапустил службу mariadb. Перезапуск прошёл штатно, база поднялась, но таблица не ожила. Всё те же ошибки. Причём данные в неё, судя по всему, записывались.
Тут я немного приуныл, так как знаний не хватает решить проблему. Не понятна причина. Я узнал, что хранится в таблице и понял, что эти данные не представляют ценности и могут быть потеряны. Дальше поискал инфу в гугле и поговорил с chatgpt. Последний не предложил ничего нового из того, что я видел в гугле, но накидал неактуальных рекомендаций для движка MyISAM, хотя у меня InnoDB.
Нашёл следующие варианты решения проблемы:
innodb_force_recovery. alter table b_stat_session_data engine = innodbСудя по отзывам, последняя команда будет выполняться очень долго. Третья может привести к долгому старту службы или ещё каким-то проблемам. Не хотелось запускать их на работающем сервере. Решил пересоздать индексы. Их там всего два, но один PRIMARY. Его нельзя просто взять и удалить. Вычитал на stackoverflow один совет. Зайти через phpmyadmin, убрать чекбокс Auto Increment на столбце с этим индексом, сохранить, потом зайти, вернуть AI. Возможно на живой таблице это и сработало бы, но у меня вылетела ошибка, ничего не сохранилось, таблица вообще умерла.
С этого момента некоторые юзеры начали получать ошибку MySQL Query Error! Дальше не стал экспериментировать, грохнул таблицу и создал заново. Структуру посмотрел в живом дампе. Всё сразу заработало, данные потекли в таблицу, дамп нормально создаётся.
Если у кого-то есть советы по теме, с удовольствием выслушаю. Ещё раз уточню, что стал делать опасные манипуляции, когда понял, что данные позволительно потерять. И я мог себе позволить простой в 10-15 минут.
#mysql
Please open Telegram to view this post
VIEW IN TELEGRAM
Моя недавняя история с ошибкой MySQL таблицы получила неожиданное продолжение. Оно меня настолько удивило, что решил написать об этом отдельно. Напомню, что там побилась одна таблица базы движка InnoDB. Я попытался её починить, но когда понял, что данные можно потерять, просто удалил и пересоздал её, так как не хотелось на работающем сервере эксперименты ставить.
Сама ситуация для меня разрешилась благополучно, но не считаю, что оптимально. В следующий раз данные могут быть нужны. Поэтому я решил её проработать и записать решение, которое не приведёт к потере данных. Для этого я пометил бэкап виртуалки за ту ночь, как неудаляемый. А через некоторое время нашёл место, где её можно спокойно развернуть и поработать с ней.
☝️ Бэкап, кстати, почти всю ночь восстанавливался. И это очень долго. Поэтому я всегда делаю отдельно бэкапы данных внутри виртуалок. Систему можно поднять быстро, залить основные данные, нужные для запуска сервиса, а остальное копировать по ходу дела. Это если архитектура сервиса и данных позволяет.
Виртуалку я восстановил, запустил, убедился, что она работает и выключил. Позже появилось время с ней поработать более внимательно. Настроил там сеть, подключился и стал смотреть. Каково же было моё удивление, когда этой ошибки я там не обнаружил. СУБД работает нормально,
Сначала подумал, что перепутал версии бэкапа. Но нет. Создание дампа логируется локально. Посмотрел лог бэкапа, там последняя операция завершилась с ошибкой именно на той таблице. В логе службы mariadb за то время тоже видны ошибки с таблицей. Смотрю прямо на сервере:
После последней ошибки сервер пару раз перезагружался и вуаля, таблица починилась сама. Хотя никаких встроенных процедур по исправлению ошибок для InnoDB в MariaDB не предусмотрено. И по логам не видно, чтобы что-то делалось.
На основном сервере саму виртуалку я не перезагружал, но службу mariadb перезапускал. Мне тогда это не помогло.
Расстроился из-за этой ситуации, так как потратил кучу времени и всё без толку. Никаких рабочих рецептов не придумал. В следующий раз придётся опять разбираться, когда с этим столкнусь.
Как сказал один читатель к заметке про проблемы с СУБД. В базе иногда происходит какая-то неведомая хрень. Сразу понятен уровень специалиста. Знает, о чём говорит. Теперь я тоже знаю.
#mysql
Сама ситуация для меня разрешилась благополучно, но не считаю, что оптимально. В следующий раз данные могут быть нужны. Поэтому я решил её проработать и записать решение, которое не приведёт к потере данных. Для этого я пометил бэкап виртуалки за ту ночь, как неудаляемый. А через некоторое время нашёл место, где её можно спокойно развернуть и поработать с ней.
☝️ Бэкап, кстати, почти всю ночь восстанавливался. И это очень долго. Поэтому я всегда делаю отдельно бэкапы данных внутри виртуалок. Систему можно поднять быстро, залить основные данные, нужные для запуска сервиса, а остальное копировать по ходу дела. Это если архитектура сервиса и данных позволяет.
Виртуалку я восстановил, запустил, убедился, что она работает и выключил. Позже появилось время с ней поработать более внимательно. Настроил там сеть, подключился и стал смотреть. Каково же было моё удивление, когда этой ошибки я там не обнаружил. СУБД работает нормально,
CHECK TABLE отрабатывает нормально, дамп делается. Сначала подумал, что перепутал версии бэкапа. Но нет. Создание дампа логируется локально. Посмотрел лог бэкапа, там последняя операция завершилась с ошибкой именно на той таблице. В логе службы mariadb за то время тоже видны ошибки с таблицей. Смотрю прямо на сервере:
2024-11-23T20:22:17.678755+03:00 mariadbd[738]: 2024-11-23 20:22:17 7451105 [Note] InnoDB: You can use CHECK TABLE to scan your table for
corruption. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2024-11-23T20:22:17.678779+03:00 mariadbd[738]: 2024-11-23 20:22:17 7451105 [ERROR] InnoDB: We detected index corruption in an InnoDB type table. You have to dump + drop + reimport the table or, in a case of widespread corruption, dump all InnoDB tables and recreate the whole tablespace. If the mariadbd server crashes after the startup or when you dump the tables. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2024-11-23T20:22:17.678805+03:00 mariadbd[738]: 2024-11-23 20:22:17 7451105 [ERROR] Got error 126 when reading table 'b_stat_session_data'
2024-11-23T20:22:17.678833+03:00 mariadbd[738]: 2024-11-23 20:22:17 7451105 [ERROR] mariadbd: Index for table 'b_stat_session_data' is corrupt; try to repair it
После последней ошибки сервер пару раз перезагружался и вуаля, таблица починилась сама. Хотя никаких встроенных процедур по исправлению ошибок для InnoDB в MariaDB не предусмотрено. И по логам не видно, чтобы что-то делалось.
На основном сервере саму виртуалку я не перезагружал, но службу mariadb перезапускал. Мне тогда это не помогло.
Расстроился из-за этой ситуации, так как потратил кучу времени и всё без толку. Никаких рабочих рецептов не придумал. В следующий раз придётся опять разбираться, когда с этим столкнусь.
Как сказал один читатель к заметке про проблемы с СУБД. В базе иногда происходит какая-то неведомая хрень. Сразу понятен уровень специалиста. Знает, о чём говорит. Теперь я тоже знаю.
#mysql
Telegram
ServerAdmin.ru
Недавно была заметка про знание СУБД. Видел комментарии, мол пусть DBA разбираются в нюансах СУБД, а админам это ни к чему. Расскажу про один случай, который со мной на днях случился. Сразу скажу, что это не будет рекомендацией к действиям, так как сам я…
Для тех, кто много работает с базами данных, есть простой и удобный инструмент для их проектирования – dbdiagram.io. Мы не разработчики, тем не менее, покажу, как этот сервис может быть полезен. У него удобная визуализация структуры базы данных. Намного удобнее, чем, к примеру, в привычном phpmyadmin.
Можно выгрузить обычный дам из СУБД без данных и загрузить его в dbdiagram. Получите наглядную схему базы. При желании, её можно отредактировать, или просто экспортнуть в виде картинки. Покажу на примере MySQL. Выгружаем дамп базы postfix без данных, только структуру:
Передаём к себе на комп полученный дамп. В dbdiagram.io открываем Import ⇨ From MySQL ⇨ Upload .sql ⇨ Submit. Наблюдаем схему базы данных со всеми связями. Причём она сразу отформатирована по таблицам так, что все их наглядно видно.
При желании можно что-то изменить в структуре и выгрузить новый sql файл уже с изменениями. Если выбрать Export ⇨ To PNG, то сразу же получите аккуратную картинку со схемой, без лишних вопросов. Очень быстро и удобно, если нужно куда-то приложить схему базы данных.
⇨ 🌐 Сайт
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#mysql #postgresql
Можно выгрузить обычный дам из СУБД без данных и загрузить его в dbdiagram. Получите наглядную схему базы. При желании, её можно отредактировать, или просто экспортнуть в виде картинки. Покажу на примере MySQL. Выгружаем дамп базы postfix без данных, только структуру:
# mysqldump --no-data -u dbuser -p postfix > ~/schema.sqlПередаём к себе на комп полученный дамп. В dbdiagram.io открываем Import ⇨ From MySQL ⇨ Upload .sql ⇨ Submit. Наблюдаем схему базы данных со всеми связями. Причём она сразу отформатирована по таблицам так, что все их наглядно видно.
При желании можно что-то изменить в структуре и выгрузить новый sql файл уже с изменениями. Если выбрать Export ⇨ To PNG, то сразу же получите аккуратную картинку со схемой, без лишних вопросов. Очень быстро и удобно, если нужно куда-то приложить схему базы данных.
⇨ 🌐 Сайт
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#mysql #postgresql
На днях разбирался с одной относительно простой задачей, но из-за того, что затупил, потратил на неё очень много времени. Так как проделал множество всевозможных операций, решил всё подробно описать. Пригодится в других похожих историях.
Есть MySQL сервер, у которого, как мне показалось, очень большая нагрузка по CPU. Проект немного стух, записи в базу должно быть мало и мне было странно видеть высокую нагрузку от СУБД. Решил разобраться, в чём там дело.
Для начала просто посмотрел нагрузку через
В таких случаях часто помогает strace. Можно подцепиться к процессу и посмотреть, что он пишет:
В моём случае я не получил результата. В выводе просто было пусто. Я не понял, почему. Стал разбираться дальше. Смотрю потоки процесса:
Вижу, что CPU нагружает сильно больше всех остальных один поток с SPID 19547. Смотрю по нему информацию:
В выводе только цифры и отсылка к основному потоку mysqld. То есть вообще не понятно, что там реально происходит.
Тут до меня доходит подцепиться к потоку через strace:
Вижу системные вызовы futex (синхронизацией потоков), io_submit (асинхронные операции ввода-вывода) и некоторые другие.
Смотрю, в какие файлы пишет mysqld:
Это
А на серваке выполнялись десятки простых SELECT за считанные миллисекунды или ещё быстрее. Не отслеживал. И они тупо не попадали в вывод. И я думал, что запросов мало, а их было дохрена. Они и давали нагрузку на CPU.
Запросы увидел так. Не перезапуская сервер выполнил в консоли mysql:
Так же это было видно в выводе:
В разделе I/O. Эти потоки отвечают за асинхронные операции ввода-вывода (AIO).
Достаточно было на несколько секунд запустить, чтобы понять всю картину. Потом сразу отключил, чтобы не нагружать диски.
Осталось разобраться, а что на диск то записывается. Вроде как файлы
xb_doublewrite - это некий буфер InnoDB для повышения надёжности хранения, отключать не рекомендуется, хотя можно.
В общем, время я по сути потратил впустую, если не считать вот эту заметку итоговым результатом. Думаю, она мне ещё понадобится. Времени уже не оставалось дальше разбираться с этой историей. В целом, нагрузка там рабочая, абсолютно некритичная, так что разбираться с ней дальше большой нужды не было. Но я всё равно планирую подумать, как её снизить. И вообще не совсем понятно, почему её так много, с учётом того, что сайт активно кэшируется. Надо будет разбираться.
Отдельно отмечу, что очень активно нагружал этой темой DeepSeek, но он особо не помог. Да, много всякой информации давал и анализировал мои результаты, но фактически я сам догадался в чём причина. Он как-то больше по кругу ходил и всякие команды накидывал.
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#mysql #perfomance
Есть MySQL сервер, у которого, как мне показалось, очень большая нагрузка по CPU. Проект немного стух, записи в базу должно быть мало и мне было странно видеть высокую нагрузку от СУБД. Решил разобраться, в чём там дело.
Для начала просто посмотрел нагрузку через
top. Mysql почти постоянно кушает одно ядро и периодически остальные дёргает. Дополнительно смотрю в iotop и pidstat и вижу постоянно запись со стороны службы mysqld с pid 19535:# iotop -obPat# pidstat -p 19535 -d 1В таких случаях часто помогает strace. Можно подцепиться к процессу и посмотреть, что он пишет:
# strace -e trace=write -p 19535В моём случае я не получил результата. В выводе просто было пусто. Я не понял, почему. Стал разбираться дальше. Смотрю потоки процесса:
# ps -L -o spid,%mem,%cpu,cmd 16005Вижу, что CPU нагружает сильно больше всех остальных один поток с SPID 19547. Смотрю по нему информацию:
# cat /proc/19535/task/19547/stat# cat /proc/19535/task/19547/statusВ выводе только цифры и отсылка к основному потоку mysqld. То есть вообще не понятно, что там реально происходит.
Тут до меня доходит подцепиться к потоку через strace:
# strace -p 19547Вижу системные вызовы futex (синхронизацией потоков), io_submit (асинхронные операции ввода-вывода) и некоторые другие.
Смотрю, в какие файлы пишет mysqld:
# inotifywait -m /var/lib/mysqlЭто
ib_logfile0 и xb_doublewrite. Никак не могу понять, почему он туда активно пишет, когда запросов к базе особо нет. И вот тут я как раз и затупил. Я смотрел запросы через mytop и SHOW FULL PROCESSLIST; И там их было очень мало. Эти команды показывают запросы в моменте.А на серваке выполнялись десятки простых SELECT за считанные миллисекунды или ещё быстрее. Не отслеживал. И они тупо не попадали в вывод. И я думал, что запросов мало, а их было дохрена. Они и давали нагрузку на CPU.
Запросы увидел так. Не перезапуская сервер выполнил в консоли mysql:
> SET GLOBAL general_log = 'ON';> SET GLOBAL general_log_file = '/var/log/mysql/general.log';Так же это было видно в выводе:
> SHOW ENGINE INNODB STATUS;В разделе I/O. Эти потоки отвечают за асинхронные операции ввода-вывода (AIO).
Достаточно было на несколько секунд запустить, чтобы понять всю картину. Потом сразу отключил, чтобы не нагружать диски.
Осталось разобраться, а что на диск то записывается. Вроде как файлы
ib_logfile0 это файлы журналов транзакций InnoDB, а разве SELECT вызывает транзакции? Я тут сильно не погружался, но мельком глянул информацию и понял, что всякие очистки устаревших данных, обновление статистики, индексов, хэшей могут тоже провоцировать запись, а точнее обновление этого файла.xb_doublewrite - это некий буфер InnoDB для повышения надёжности хранения, отключать не рекомендуется, хотя можно.
В общем, время я по сути потратил впустую, если не считать вот эту заметку итоговым результатом. Думаю, она мне ещё понадобится. Времени уже не оставалось дальше разбираться с этой историей. В целом, нагрузка там рабочая, абсолютно некритичная, так что разбираться с ней дальше большой нужды не было. Но я всё равно планирую подумать, как её снизить. И вообще не совсем понятно, почему её так много, с учётом того, что сайт активно кэшируется. Надо будет разбираться.
Отдельно отмечу, что очень активно нагружал этой темой DeepSeek, но он особо не помог. Да, много всякой информации давал и анализировал мои результаты, но фактически я сам догадался в чём причина. Он как-то больше по кругу ходил и всякие команды накидывал.
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#mysql #perfomance
У меня уже были заметки про фишки LVM, сегодня расскажу про ещё одну, которая может пригодиться. С помощью LVM и снепшотов можно делать консистентные бэкапы баз данных Mysql на уровне файлов, а не дампов, практически без простоя. Для таких задач в СУБД Percona есть инструмент под названием XtraBackup, а вот если у вас обычная бесплатная MySQL или MariaDB, то с бэкапами на уровне файлов там не так всё просто, особенно если вы уже не можете делать дампы из-за их больших размеров.
Идея там вот в чём. Мы закрываем все открытые таблицы, сбрасываем кэш, блокируем изменение данных, после этого делаем снепшот раздела с базами данных и сразу отключаем блокировки. Это занимает буквально пару секунд, на которые блокируются все базы, а потом спокойно копируем файлы сервера, примонтировав снепшот.
Это всё хорошо работало до появления движка InnoDB. У него есть нюансы, когда блокировки не предотвращают изменение данных в таблицах. Нужно дополнительно сбрасывать на диск так называемые dirty_pages. Покажу всё это на практике. Способ немного костыльный и вряд ли подойдёт для прода, так как есть много нюансов. Но в каких-то ситуациях может помочь быстро снять консистентную копию без остановки сервера.
Для того, чтобы всё получилось, раздел, где хранятся данные MySQL сервера, должен располагаться на LVM, а в Volume Group должно быть свободное место для снепшотов. Проверяем так:
Если места нет, надо добавить. Не буду на этом останавливаться, рассказывал ранее. Первым делом заходим в консоль mysql, сбрасываем кэш и включаем блокировки:
Если база не сильно большая и нагруженная, кэш быстро сбросится. Наблюдать можно так:
Следим за значением
После этого возвращаем обратно настройку с dirty_pages в исходное значение (по умолчанию 90) и снимаем блокировки:
Когда это делается автоматически скриптом, всё это происходит быстро. Но есть нюансы. Команда блокировки дожидается завершения всех запросов. Если у вас там есть какие-то очень длинные запросы, то вся база будет заблокирована, пока они не завершатся. Так что имейте это ввиду.
Монтируем снепшот и копируем данные:
После копирования снепшот надо обязательно удалить.
Получили консистентный бэкап всех баз данных практически без остановки сервера, но с кратковременной блокировкой изменений.
Я собрал всё это в скрипт и протестировал на сервере с MariaDB и Zabbix Server. Сохранил бэкап локально, потом остановил MariaDB, полностью удалил директорию
Скрипт особо не отлаживал, так что аккуратнее с ним. Просто убедился, что работает. Сначала немного накосячил, так как после включения блокировки закрывал сессию, делал снепшот и подключался снова. Так нельзя, потому что команда
Такой вот небольшой трюк в копилку LVM, который можно применять не только к СУБД, но и другим данным.
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#lvm #mysql #backup
Идея там вот в чём. Мы закрываем все открытые таблицы, сбрасываем кэш, блокируем изменение данных, после этого делаем снепшот раздела с базами данных и сразу отключаем блокировки. Это занимает буквально пару секунд, на которые блокируются все базы, а потом спокойно копируем файлы сервера, примонтировав снепшот.
Это всё хорошо работало до появления движка InnoDB. У него есть нюансы, когда блокировки не предотвращают изменение данных в таблицах. Нужно дополнительно сбрасывать на диск так называемые dirty_pages. Покажу всё это на практике. Способ немного костыльный и вряд ли подойдёт для прода, так как есть много нюансов. Но в каких-то ситуациях может помочь быстро снять консистентную копию без остановки сервера.
Для того, чтобы всё получилось, раздел, где хранятся данные MySQL сервера, должен располагаться на LVM, а в Volume Group должно быть свободное место для снепшотов. Проверяем так:
# pvsЕсли места нет, надо добавить. Не буду на этом останавливаться, рассказывал ранее. Первым делом заходим в консоль mysql, сбрасываем кэш и включаем блокировки:
> SET GLOBAL innodb_max_dirty_pages_pct = 0;> FLUSH TABLES WITH READ LOCK;Если база не сильно большая и нагруженная, кэш быстро сбросится. Наблюдать можно так:
> SHOW ENGINE INNODB STATUS\G;Следим за значением
Modified db pages. Оно должно стать 0. После этого можно делать снэпшот:# lvcreate --size 5G --snapshot --name mysql_snapshot /dev/vgroup/rootПосле этого возвращаем обратно настройку с dirty_pages в исходное значение (по умолчанию 90) и снимаем блокировки:
> SET GLOBAL innodb_max_dirty_pages_pct = 90;> UNLOCK TABLES;Когда это делается автоматически скриптом, всё это происходит быстро. Но есть нюансы. Команда блокировки дожидается завершения всех запросов. Если у вас там есть какие-то очень длинные запросы, то вся база будет заблокирована, пока они не завершатся. Так что имейте это ввиду.
Монтируем снепшот и копируем данные:
# mkdir /mnt/mysql_backup# mount /dev/vgroup/mysql_snapshot /mnt/mysql_backup# rsync -avz --delete /mnt/mysql_backup/var/lib/mysql/ user@10.20.1.5:/mnt/backup/mysql/После копирования снепшот надо обязательно удалить.
# umount /mnt/mysql_backup# lvremove -f /dev/vgroup/mysql_snapshotПолучили консистентный бэкап всех баз данных практически без остановки сервера, но с кратковременной блокировкой изменений.
Я собрал всё это в скрипт и протестировал на сервере с MariaDB и Zabbix Server. Сохранил бэкап локально, потом остановил MariaDB, полностью удалил директорию
/var/lib/mysql и восстановил из бэкапа. Потом запустил СУБД. Запустилось без проблем, никаких ошибок. Бэкап получается консистентный.Скрипт особо не отлаживал, так что аккуратнее с ним. Просто убедился, что работает. Сначала немного накосячил, так как после включения блокировки закрывал сессию, делал снепшот и подключался снова. Так нельзя, потому что команда
FLUSH TABLES WITH READ LOCK живёт, пока открыто соединение. Надо в рамках него делать всё необходимое. Запустил lvcreate через SYSTEM. Такой вот небольшой трюк в копилку LVM, который можно применять не только к СУБД, но и другим данным.
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#lvm #mysql #backup