MySQL ZFS Backup v2

Обновил и расширил функциональность своего скрипта, осуществляющего бэкап базы данных MySQL с помощью ZFS снапшотов.

Новая версия умеет понимать параметры, передаваемые из командной строки, умеет работать с файлом конфигурации и обрабатывать несколько датасетов за раз. Типичный пример использования скрипта mysql_zfs_backup.sh:

db# ./mysql_zfs_backup.sh -u mysqluser -p mysqlpass -d rpool/mysql -d ssd/mysql
Replication is running: 0 seconds master delay
Snapshots will be rotated for ZFS datasets: rpool/mysql ssd/mysql
Done

Конфигурация по-умолчанию читается из файла mysql_zfs_backup.conf:

mysql_user='mysqluser'
mysql_pass='mysqlpass'
zfs_datasets='rpool/mysql ssd/mysql'
quiet='0'

И, кстати:

db# /bin/sh -c 'time -h ./mysql_zfs_backup.sh'
Replication is running: 0 seconds master delay
Snapshots will be rotated for ZFS datasets: rpool/mysql
Done
        3.09s real              0.02s user              0.00s sys
db# du -h -c /var/db/mysql | grep total
 79G    total

Файлы: сам скрипт, пример конфига.

dbForge Studio for MySQL

Кстати, для проектирования баз данных я использую dbForge Studio for MySQL. Это полностью бесплатный программный пакет на русском языке, гибкий и мощный. Умеет ssh туннели с авторизацией по ключам, так что необходимость в phpmyadmin целиком и полностью отпадает.

mysql-master-master 1.x

Внедрил первую ветку mysql-master-master. Впечатления остались двойственные: сама задумка неплоха, но авторы откровенно забили на проект, многие issues открыты больше года и до сих пор не закрываются.

Так, например, при установке read_only агент не делает flush tables with read lock, что может серьезно поломать репликацию. Кроме того, авторы забили и на включение-выключение event scheduler в MySQL 5.1.

Ну и само собой, сие поделие от специалистов Percona не поддерживает FreeBSD, пришлось долго и упорно патчить.

Тюнинг ZFS, MySQL и InnoDB

Тюнинг MySQL и InnoDB

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

innodb_flush_method = O_DIRECT

Механизм буфера двойной записи (doublewrite) используется в InnoDB для того, чтобы удостовериться, что измененные страницы были записаны в файл данных. Этот механизм позволяет избежать потери данных при внезапном отключении сервера. Поскольку у ZFS есть свой механизм обеспечения целостности данных, буфер двойной записи можно отключить:

skip-innodb_doublewrite

Запись в файлы данных InnoDB должна осуществляться блоками по 16Кб, что соответствует размеру страницы, используемом в этом движке. Поскольку размер блока записи по-умолчанию составляет 128Кб, файлы данных нужно вынести на отдельную файловую систему.

innodb_data_home_dir=/var/db/mysql/ibdata
# zfs create data/mysql/ibdata
# zfs set recordsize=16K data/mysql/ibdata
# mv /var/db/mysql/ibdata1 /var/db/mysql/ibdata/

Тюнинг ZFS

База данных осуществляет чтение в произвольном порядке, который нельзя предсказать.
Отключение префетча позволяет избежать ненужных операций чтения. Добавим в /boot/loader.conf строку

vfs.zfs.prefetch_disable=1

ZFS кэширует данные в ARC, используя свободную оперативную память. Поскольку страницы InnoDB уже кэшируются в буфер пуле, отключим кэширование файлов данных InnoDB:

# zfs set primarycache=metadata data/mysql/ibdata

См. также: MySQL на ZFS под FreeBSD: подготовка

Бэкапим MySQL на ZFS

Кстати, бэкапиться с ZFS на слейвах очень и очень просто:

#!/bin/sh
user='mysqlbackup';
pass='superpass';
/usr/local/bin/mysql -u $user -p$pass -e 'STOP SLAVE; FLUSH LOGS; FLUSH TABLES WITH READ LOCK; \
SYSTEM zfs destroy -r data/mysql@yesterday; \
SYSTEM zfs rename -r data/mysql@today @yesterday; \
SYSTEM zfs snapshot -r data/mysql@today; \
UNLOCK TABLES; START SLAVE;'

Скрипт выполняется примерно за секунду на 30 гиговой базе.

Optimizing MySQL Performance with ZFS

Наткнулся в интернетах на интереснейшую презентацию с The 2009 MySQL Conference & Expo. Умные мужчины из Sun Microsystems в течении почти часа рассказывают о тонкостях использования MySQL на ZFS.

MySQL на ZFS под FreeBSD: подготовка

Итак, что есть на руках:

# uname -rm
8.0-RELEASE amd64
# atacontrol list
ATA channel 2:
    Master:  ad4 ST3500320NS/SN06 SATA revision 1.x
    Slave:       no device present
ATA channel 3:
    Master:  ad6 ST3500320NS/SN06 SATA revision 1.x
    Slave:       no device present
# zpool status
  pool: tank
 state: ONLINE
 scrub: none requested
config:

        NAME        STATE     READ WRITE CKSUM
        tank        ONLINE       0     0     0
          mirror    ONLINE       0     0     0
            ad4s1d  ONLINE       0     0     0
            ad6s1d  ONLINE       0     0     0

errors: No known data errors

Имеем мы 64 битную FreeBSD 8-й ветки, релиз которой состоялся совсем недавно Операционная система установлена на ZFS зеркале из двух SATA жестких дисков. FreeBSD ZFS Tuning Guide рекомендует именно такие версии софта для наиболее стабильной работы. Для начала создадим в уже имеющемся ZFS пуле с названием tank файловые системы, которые мы будем использовать для хранения данных Не забыв при этом остановить сервер MySQL и сделать бэкап директории с данными.
Читать далее MySQL на ZFS под FreeBSD: подготовка

MySQL и электрики

Макхост предоставляет недорогой и качественный хостинг выделенных серверов. К сожалению, раз в 1-2 месяца на них нападает неведомая хуйня и сервера внезапно выключаются по питанию. Дабы избежать крахов базы данных приходится идти на такие вот извращения:

innodb_flush_log_at_trx_commit=1
sync_binlog=1

Интенсивность ввода-вывода при этом увеличивается на порядок, зато при внезапном отключении сервера не ломается репликация.