Архивация периодических таблиц MySQL

Оглавление

  1. Оглавление
  2. Введение
  3. Подготовка сервера со скриптом
  4. Подготовка пользователя на ведущем сервере
  5. Подготовка пользователя на ведомом сервере
  6. Подготовка пользователя на архивном сервере
  7. Настройка скрипта
  8. Настройка планировщика задач

Введение

Некоторые приложения записывают в базу данных события, привязанные к определённой дате. Как правило, наиболее востребованными данными являются самые свежие данные. Но при росте объёма данных в таблице замедляется поиск, даже при наличии подходящего индекса. Дело в том, что индекс может не помещаться целиком в оперативной памяти или вытесняется на диск более востребованными данными, а необходимость обновлять большой индекс при вставке новых данных в таблицу приводит к замедлению операций вставки.

Для решения этой проблемы в настоящее время обычно используют деление таблицы на отдельные секции, соответствующие году, месяцу или суткам. У каждой секции при этом имеется собственный индекс, поэтому при добавлении в таблицу новых данных нужно обновлять только индекс последней секции таблицы. Самые востребованные данные также как правило находятся в последней секции таблицы.

Но до появления поддержки секций таблиц в MySQL разработчикам приложений приходилось решать эту задачу самостоятельно. Одним из таких приложений является биллинговая система BGBilling. В ней для хранения периодических данных используются отдельные таблицы с именами, оканчивающимися шестью или восемью цифрами, соответствующими определённому месяцу или суткам. Такой же подход к хранению периодических данных используется в ERP-системе BGERP. Мне попадалась ещё одна система собственной разработки, разработчик которой черпал вдохновение из одной из упомянутых двух программ.

Поскольку приложение лишь добавляет данные в эти таблицы, но не удаляет, количество таблиц со временем растёт вместе с занимаемым ими местом на диске. Поэтому устаревшие таблицы имеет смысл удалять. Однако не всегда эти таблицы стоит удалять окончательно. Во-первых, таблицу можно удалить по ошибке. Во-вторых, могут возникнуть исключительные ситуации, в которых может понадобиться найти информацию в давно устаревших данных.

Для решения задачи архивации периодических таблиц я написал настраиваемый скрипт archive tables.sh, который использует файл конфигурации /etc/archive_tables.conf.

Предполагается, что скрипт может быть настроен на произвольном сервере. Команды удаления устаревших таблиц должны выполняться на ведущем сервере MySQL, а для снятия резервных копий таблиц может использоваться ведомый сервер, на котором настроена репликация баз данных с ведущего сервера. Сжатые резервные копии могут отправляться на сервер-хранилище по протоколу SSH. Но скрипт можно разместить на любом сервере - ведущем, ведомом, хранилище. Ведомый сервер может отсутствовать - в таком случае можно настроить скрипт на снятие резервных копий архивируемых таблиц с ведущего сервера. Может отсутствовать и хранилище - в таком случае архивы таблиц будут сохраняться на том же сервере, где настроен скрипт.

Подготовка сервера со скриптом

Создадим на сервере, где будет работать скрипт архивации таблиц, пользователя, от имени которого будет запускаться скрипт. В случае с Linux создать пользователя можно следующим образом: 

# groupadd archive
# useradd -c "User for archiving tables" -d /home/archive -m -g archive archive

В случае с FreeBSD соответствующие команды будут иметь следующий вид:

# pw add group archive
# pw add user archive -g archive -c "User for archiving tables" -d /usr/home/archive -m

Если после архивации таблиц их нужно будет скопировать на удалённый сервер по SSH, то сгенерируем SSH-ключ для доступа из скрипта к удалённому серверу:

# ssh-keygen -N "" -f ~/.ssh/archive

Создадим в домашнем каталоге пользователя, от имени которого будет выполняться скрипт, файл ~/.my.cnf с настройками подключения к ведомому серверу. Если есть только один сервер, то вписываем в файл настройки для подключения к нему:

[client]
user = archive
password = 'p4$$w0rd'
host = 192.168.1.3

И сразу же поменяем права доступ к этому файлу так, чтобы его не могли прочитать посторонние пользователи:

# chmod u=rw,go= /home/archive/.my.cnf

Владельцем файла нужно назначить пользователя, от имени которого будет запускаться скрипт архивации таблиц:

# chown archive:archive /home/archive/.my.cnf

Теперь, если ведущий сервер отличается от ведомого, создадим аналогичный файл /home/archive/.main.cnf с настройками подключения к ведущему серверу:

[client]
user = archive
password = 'p4$$w0rd'
host = 192.168.1.2

Поменяем права доступ к этому файлу так, чтобы его не могли прочитать посторонние пользователи:

# chmod u=rw,go= /home/archive/.main.cnf

Владельцем файла сделаем пользователя, от имени которого будет запускаться скрипт архивации таблиц:

# chown archive:archive /home/archive/.main.cnf

Для работы скрипта понадобится каталог, в который будут помещаться архивные копии таблиц. Если нет выделенного сервера для хранения архивных копий таблиц, то архивы таблиц будут сохраняться в этот каталог. Создадим каталог и назначим его владельцем пользователя, от имени которого будет работать скрипт:

# mkdir -p /archive/
# chmod u=rwx,go= /archive/
# chown archive:archive /archive/

Подготовка пользователя на ведущем сервере

На ведущем сервере MySQL нужно создать пользователя с правами SELECT, DROP и ALTER для всех баз данных, таблицы из которых предполагается архивировать.

Предположим, что IP-адрес ведущего сервера - 192.168.1.2, а IP-адрес ведомого - 192.168.1.3. Предположим также, что пользователь будет называться archive, а его пароль - p4$$w0rd. В таком случае создать пользователя на ведущем сервере можно с помощью одного из следующих запросов:

GRANT USAGE ON *.* TO `archive`@`192.168.1.3` IDENTIFIED BY 'p4$$w0rd';
CREATE USER `archive`@`192.168.1.3` IDENTIFIED BY 'p4$$w0rd';

Пользователь будет обрабатывать таблицы базы данных ufadisc. Выдать необходимые права доступа к этой базе данных можно с помощью запроса следующего вида:

GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `ufadisc`.* TO `archive`@`192.168.1.3`;

Не забудьте после этого применить изменения с помощью следующей команды;

FLUSH PRIVILEGES;

Подготовка пользователя на ведомом сервере

В домашнем каталоге пользователя, от имени которого будет работать скрипт архивации таблиц, нужно создать файл ~/.my.cnf, аналогичный описанному выше файлу /etc/mysql/archive.cnf, в котором нужно указать учётные данные для подключения к ведомому серверу. Пользователю нужно выдать глобальные права SHOW DATABASES и RELOAD, и права SELECT, LOCK TABLES для резервного копирования таблиц каждой из баз данных.

Сначала создадим пользователя одной из следующих команд:

GRANT USAGE ON *.* TO `archive`@`localhost` IDENTIFIED BY 'p4$$w0rd';
CREATE USER `archive`@`localhost` IDENTIFIED BY 'p4$$w0rd';

Выдадим глобальные права доступа:

GRANT SHOW DATABASES, RELOAD ON *.* TO `archive`@`localhost`;

Выдадим права доступа к базе данных:

GRANT SELECT, LOCK TABLES ON `ufadisc`.* TO `archive`@`localhost`;

Подготовка пользователя на архивном сервере

В качестве сервера для хранения архивов может выступать любой Unix-сервер, поддерживающий доступ по SSH. В случае с Linux создать на нём пользователя, от имени которого к нему будет подключаться скрипт архивации, можно следующим образом:

# groupadd archive
# useradd -c "User for store archive tables" -d /home/archive -m -g archive archive

В случае с FreeBSD соответствующие команды будут иметь следующий вид:

# pw add group archive
# pw add user archive -g archive -c "User for store archive tables" -d /usr/home/archive -m

Скопируем публичный ключ из файла ~/.ssh/archive.pub на сервере, где будет запускаться скрипт архивации таблиц и вернёмся на сервер, на который будем записывать архивы. Создадим на нём файл с ключами, владельцам которых разрешён вход по SSH от имени созданного пользователя:

# mkdir /home/archive/.ssh
# chown archive:archive /home/archive/.ssh
# cat <<END > /home/archive/.ssh/authorized_keys
ТУТ ПУБЛИЧНЫЙ SSH-КЛЮЧ  
END
# chown archive:archive /home/archive/.ssh/authorized_keys

Настройка скрипта

Теперь пришло время настроить скрипт, для чего нужно создать файл конфигурации с именем /etc/archive_tables.conf. Если есть выделенный ведущий сервер, то нужно указать путь к файлу с настройками подключения к нему:

PROFILE=/home/archive/.main.cnf

Если выделенного сервера нет, то прописываем путь к файлу с настройками подключения к ведомому серверу, который в данном случае является единственным:

PROFILE=/home/archive/.main.cnf

Далее настраиваем путь к локальному каталогу, в который будут сохраняться архивные копии таблиц:

BACKUP_PATH=/archive/

Если архивные копии таблиц нужно перемещать на удалённый сервер, то необходимо заполнить следующие четыре переменные с настройками имени пользователя, доменного имени или IP-адреса сервера-хранилища, пути для сохранения архивных копий на удалённом сервере и путём к приватному SSH-ключу:

RUSER=archive
RSERVER=server.domain.tld
RPATH=/archive/
RKEY=/home/archvie/.ssh/archive_key

Если в качестве ведущего сервера используется Percona XtraDB Cluster или другой сервер из кластера Galera, то можно перечислить доменные имена или IP-адреса всех серверов, входящих в кластер, чтобы скрипт проверял перед каждой операцией удаления или усечения таблицы состояние серверов, входящих в кластер. Скрипт будет ждать, когда количество запросов, ожидающих репликации на другие серверы, снизится до нуля:

PXC_HOSTS="
"

В зависимости от сервера, может потребоваться выполнять усечение таблиц одним из нескольких способов. Для настройки используемого способа усечения таблиц предназначена переменная TRUNCATE_MODE:

TRUNCATE_MODE=truncate

Переменная может принимать одно из следующих значений:

  • truncate - выполнить запрос TRUNCATE TABLE ... для усечения таблицы.
  • truncate_alter - выполнить запрос TRUNCATE TABLE ..., после чего выполнить запрос ALTER TABLE ... ENGINE=InnoDB. Этот режим может пригодиться в случае использования MySQL версии 5.1, в котором запрос TRUNCATE не приводит к уменьшению размера файла, в котором хранится таблица. Для того, чтобы уменьшить размер файла, над таблицей выполняется фиктивный запрос ALTER TABLE ... ENGINE=InnoDB.
  • create_drop_rename - выполнить запрос CREATE TABLE ... LIKE ... для создания новой таблицы, аналогичной по структуре очищаемой, DROP TABLE ... для удаления существующей таблицы и RENAME TABLE ... TO ... для подстановки новой пустой таблицы на место удалённой. В кластере Galera операции TRUNCATE TABLE ... выполняются одновременно на всех узлах кластера, а в ходе выполнения этой операции происходит блокировка других запросов, даже никак не связанных с усекаемой таблицей. Для избежания подобной блокировки выполняется последовательность из трёх операций. К сожалению, операции по изменению структуры таблиц нельзя объединить в транзакцию - MySQL в любом случае выполнит каждую такую операцию как отдельную транзакцию. Из-за этого клиенты могут столкнуться с ситуацией отсутствия таблицы, что приведёт к ошибке выполнения запроса. Но из двух зол - кратковременные единичные сбои выполнения отдельных запросов и блокировка всех поступающих запросов до окончания операции - стоит выбрать меньшее зло.

На сервере баз данных может быть несколько баз данных, к которым применимы единые правила архивации. Имя каждого правила может состоять из произвольных латинских букв и цифр. Список правил архивации указывается в переменной ARCHIVE_SETS:

ARCHIVE_SETS="
        DISC
"

Далее для каждого из правил настраиваются переменные с именами DATABASES_<имя правила>, MONTH_TABLES_<имя правила>, DAY_TABLES_<имя правила>. Например, для указанного выше правила с именем DISC переменная DATABASES_DISC может содержать список баз данных, обрабатываемых этим правилом, в следующем виде:

DATABASES_DISC="
        neftekamskdisc
        oktyabrskydisc
        ufadisc
        sterlitamakdisc
        ishimbaydisc
        salavatdisc
        orenburgdisc
        orskdisc
"

В переменной MONTH_TABLES_DISC перечисляются правила архивации месячных таблиц с суффиксами вида ГГГГММ. В первой колонке указывается регулярное выражение, с которым должно совпадать имя архивируемой таблицы. Во второй колонке указывается количество месяцев, по истечении которого соответствующие таблицы должны быть заархивированы. В третьей колонке указывается 1, если нужно выполнить архивацию таблицы, а 0 указывается, если создавать архив таблицы не требуется. В последней четвёртой колонке указывается 1, если нужно удалить таблицу, а 0 соответствует очистке таблицы. Например, правила могут быть такими:

MONTH_TABLES_DISC="
        # Архивируем и удаляем таблицы старше 3 лет и 2 месяцев
        ^bg_links_[0-9]{6}$ 38 1 1
        ^log_arp_session_[0-9]{6}$ 38 1 1
        ^log_fdb_session_[0-9]{6}$ 38 1 1
"

В переменной DAY_TABLES_DISC перечисляются правила архивации суточных таблиц с суффиксами вида ГГГГММДД. Смысл всех колонок аналогичен, за исключением второй колонки, в которой указывается количество не месяцев, а суток.

DAY_TABLES_DISC="
"

В переменных PXC_HOSTS, ARCHIVE_SETS, DATABASES_<имя правила>, MONTH_TABLES_<имя правила> и DAY_TABLES_<имя правила> для наглядности можно использовать комментарии, которые должны начинаться с символа решётки, отступы и пустые строки.

Настройка планировщика задач

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

# crontab -eu archive

Или можно запустить команду от имени самого пользователя archive:

$ crontab -e

Например, строчка для настройки запуска архивации таблиц 2 числа каждого месяца в 50 минут после полуночи может выглядеть следующим образом:

50      0       2       *       *       /usr/local/bin/archive_tables.sh