Резервное копирование базы данных MySQL с помощью снимков ZFS

Я нашел несколько сайтов, посвященных именно этому, но мне не хватает нескольких важных деталей. Общие шаги

Бегать FLUSH TABLES WITH READ LOCK
Сделайте снимок ZFS
Бегать UNLOCK TABLES

Различные источники сообщают, что InnoDB, который я использую, на самом деле не поддерживает FLUSH. В руководстве пользователя MySQL отмечается, что есть FLUSH TABLES...FOR EXPORT вариант для использования с InnoDB, но это требует указания каждой таблицы отдельно, а не резервного копирования всей базы данных. Я бы предпочел не указывать каждую таблицу в отдельности, потому что есть большая вероятность, что список таблиц не будет синхронизироваться с таблицами, которые действительно существуют.

Другая проблема, с которой я столкнулся, заключается в том, что я планировал сделать что-то вроде mysql -h"$HOST" -u"$USERNAME" -p"$PASSWORD" --execute="FLUSH TABLES WITH READ LOCK". Однако это снимает блокировку сразу после выхода из сеанса. Это имеет смысл, но также довольно раздражает, поскольку мне нужно удерживать блокировку чтения, когда я делаю снимок.

Другая моя идея - сделать горячее резервное копирование с помощью такого инструмента, как Percona XtraBackup, и сделать снимки резервной копии, но я бы предпочел не платить за запись всех моих данных во второе место только для их создания.

Вам нужна полная блокировка базы данных для последовательного резервного копирования (большинства) баз данных.

Руководство https://dev.mysql.com/doc/refman/5.5/en/backup-methods.html говорит ПРОМЫВКА СТОЛОВ С БЛОКИРОВКОЙ ЧТЕНИЯ подходит специально для снимков ZFS.

Создание резервных копий с помощью снимка файловой системы

Если вы используете файловую систему Veritas, вы можете сделать резервную копию следующим образом:

Из клиентской программы выполните FLUSH TABLES WITH READ LOCK.

Из другой оболочки выполните mount vxfs снимок.

Из первого клиента выполните UNLOCK TABLES.

Скопируйте файлы из снимка.

Размонтируйте снимок.

Подобные возможности создания снимков могут быть доступны в других файловых системах, например LVM или ZFS.

Это как-то смешно что они упустили тот факт, что вам нужно FLUSH TABLES table_a, table_b, table_c FOR EXPORT для InnoDB из этих инструкций. Также глупо указывать каждую таблицу таким образом. Но, как говорит EEAA, вы можете довольно легко создать список таблиц, когда начнете резервное копирование.

Что касается удержания блокировки, вы должны поддерживать соединение с базой данных во время выполнения снимка

Как правило, я бы использовал что-то вроде Perl или другого языка программирования, который может подключаться, блокировать базу данных и, поддерживая соединение с базой данных, делать снимок, затем разблокировать и отключиться. Это не сложно. Держу пари, что есть инструменты, которые уже делают это, но написать их легко.

Я говорю «просто, не сложно» и т. Д. Несколько раз. Я предполагаю, что у вас есть базовое программирование или хорошие навыки написания сценариев.

Я скопировал и адаптировал концептуально простой сценарий в Bash, который я нашел в другом сбое сервера. Почта по Tobia. Это должно пройти примерно 90% пути.

mysql_locked=/var/run/mysql_locked

# flush & lock MySQL, touch mysql_locked, and wait until it is removed
mysql -hhost -uuser -ppassword -NB <<-EOF &
    flush tables with read lock;
    delimiter ;;
    system touch $mysql_locked
    system while test -e $mysql_locked; do sleep 1; done
    exit
EOF

# wait for the preceding command to touch mysql_locked
while ! test -e $mysql_locked; do sleep 1; done

# take a snapshot of the filesystem, while MySQL is being held locked
zfs snapshot zpool/$dataset@$(date +"%Y-%m-%d_%H:%M")

# unlock MySQL
rm -f $mysql_locked

Здесь mysql Используемая вами команда запускается в фоновом режиме и касается файла. Он ждет в фоновом режиме, пока файл исчезнет, прежде чем выйти и, таким образом, разблокировать таблицы. Тем временем основной сценарий ожидает, пока файл не существует, затем создает моментальный снимок и удаляет файл.

Файл, на который указывает $mysql_locked должен быть доступен для обеих машин, что вы должны сделать достаточно легко, поскольку они оба могут получить доступ к общему набору данных (хотя они могут использовать разные пути, и вы должны это учитывать).

Если вы используете только InnoDB для всех таблиц и установите innodb_flush_log_at_trx_commit кому:

1 (содержимое буфера журнала InnoDB записывается в файл журнала при каждой фиксации транзакции, а файл журнала сбрасывается на диск) или,
2 (содержимое буфера журнала InnoDB записывается в файл журнала после каждой фиксации транзакции, и файл журнала сбрасывается на диск примерно раз в секунду),

тогда вам не нужно FLUSH TABLES перед созданием моментального снимка, просто запустите моментальный снимок ZFS напрямую. InnoDB может восстанавливать данные из журналов фиксации транзакций без потери данных.

Ссылка: https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

Это мое решение, как создать моментальный снимок ZFS при сохранении блокировки:

mysql << EOF
    FLUSH TABLES WITH READ LOCK;
    system zfs snapshot data/db@snapname
    UNLOCK TABLES;
EOF

Для myisam вам понадобится FLUSH TABLES WITH READ LOCK, потому что он не ведет журнал.

Вам вообще ничего не нужно для innodb, ИМО, потому что это ведение журнала. В любом случае он будет последовательным, просто автоматически откатывает журнал, если что-то происходит в момент, когда вы делаете снимок.

Если вам нужна согласованность на уровне приложения, ваше приложение должно использовать транзакции. Если ваше приложение использует транзакции и innodb, любой моментальный снимок будет согласован и автоматически спросит путь до уровня приложения.

Поскольку несколько лет назад у нас также были проблемы с FLUSH TABLES WITH READ LOCK для innoDB, и мы также не хотели полагаться на способность innoDBs восстанавливать данные из несогласованных файлов данных, мы сделали следующее: Использовали второй mariadb / mysql Компьютер базы данных настроен как вторичный (ранее известный как «подчиненный»). Всякий раз, когда вы хотите сделать снимок zfs, мы просто закрываем mariadb / mysql на вторичном сервере, затем делаем снимок zfs, а затем снова запускаем mariadb / mysql.

Эта установка выполняется примерно 4 года с использованием mysqld_multi (для запуска нескольких экземпляров DB на одной машине, но на разных портах) без каких-либо проблем. Более того, мы хотели использовать моментальные снимки zfs прямо сейчас в качестве записываемого экземпляра mariaDB / mysql. Итак, мы немедленно делаем zfs-clone из zfs-snapshot и запускаем контейнер mariaDB-Docker, используя только что созданный zfs-clone в качестве каталога данных. Кроме того, мы используем отдельный BindDNS и маршрутизацию, чтобы сделать контейнер mariaDB-Docker доступным во внутренней сети с DNS-именем. Таким образом, мы можем получить доступ к db-copys для чтения / записи из любой точки нашей внутренней офисной сети. (видеть https://github.com/ggtools/docker-tools/blob/master/dockerDDNS.py)

Это отличный инструмент для наших разработчиков программного обеспечения: они могут запросить полную копию большого экземпляра mysqsl / mariadb в несколько сотен или даже Тбайт за считанные секунды для целей тестирования и разработки.

Все это автоматизировано несколькими скриптами ruby, которые сами по себе доступны разработчикам как задание Jenkins / Hudson. Таким образом, мы даем разработчикам красивый и простой в использовании веб-интерфейс для создания DB-Copys, когда он им нужен.