Git, mysql, бэкапы и очумелые ручки

Что-то давно мы тут не изобретали бессмысленных велосипедов.

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

Хотя сайтики и так себе, но потерять данные и разбираться с их заказчиками всё равно не хотелось бы. Поэтому нужно делать резервные копии.

Ничего из файловой системы сохранять не надо, базы небольшие, ставить и настраивать навороченные системы бэкапов лениво, а руки чешутся сделать что-нибудь бессмысленное и беспощадное. Ну так возьмём и сделаем это.

База для примера

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

Итого, изначальный вес базы около, допустим, 3 Мб, а прирост и изменения где-то килобайт на 20 в день.

Бэкапим

Пишем простой скриптик для начала:

$params = array(
    'dbname'   => 'test',
    'username' => 'test',
    'password' => 'test',
);
 
$filename = 'dump-'.date('Y-m-d').'.sql';
$cmd = 'mysqldump -u'.$params['username'].' -p'.$params['password'].' '.$params['dbname'].' > '.$filename;
system($cmd);

Ставим на крон и каждое утро получаем свеженький дымыщийся файлик. Не забываем периодически выкачивать куда-нибудь.

Получаем в папке на каждый день по файлу. Изначальный размер — 1,7 Мб. Размер растёт примерно на 20К ежедневно.

Проблема, что база изменяется слабо, но каждый день у нас новый файл на 1,7 М.

Ну, понятно, добавляем:

system('gzip -9 '.$filename);

И теперь у нас файлы вдвое меньше, но всё равно 800К на каждый день уходит.

Git

Прошло, допустим, 100 дней. Теперь у нас есть папка забитая кучей файлов, которые надо бы как-то чистить или ротировать. Объём всех файлов достиг 100 Мб.

А мы возьмём и положим его в git-репу. Каждый день нам нужно не создать новый файл, а заменить старый и закоммитить.

$params = array(
    'dbname'   => 'test',
    'username' => 'test',
    'password' => 'test',
);
 
$dir = __DIR__.'/storage';
$filename = $dir.'/dump.sql';
 
$cmd = 'mysqldump -u'.$params['username'].' -p'.$params['password'].' '.$params['dbname'].' | gzip -9c > '.$filename.'.gz';
system($cmd);
 
$cmd = array();
$cmd[] = 'cd '.$dir;
$cmd[] = 'unset GIT_DIR';
if (!is_dir($dir.'/.git')) {
    $cmd[] = 'git init';
}
$cmd[] = 'git add .';
$cmd[] = 'git commit -m "'.date('Y-m-d').'"';
 
system(implode(' && ', $cmd));

Теперь у нас один файл, который, если нужно, можно откатить на любой день. И ещё у нас репа, которую легко можно выкачивать из любого места через git pull.

Но размер репы (каталога «.git«) составляет 62 Мб. Все файлы, что у нас были, git, как есть, пихает в своё хранилище.

Слияние файлов

Дампы то у нас каждый раз почти одинаковые, но как-то по-человечески слить их проблематично, так как мы их сжимаем. А с бинарными архивами особенно ничего не сделаешь.

А мы их не будем сжимать:

$cmd = 'mysqldump -u'.$params['username'].' -p'.$params['password'].' '.$params['dbname'].' > '.$filename;

Сами файлы теперь больше, но git может их оптимально слить.

Смотрим размер каталога .git: опять больше 60 Мб. Всё потому, что git сильно не парится с оптимизацией в процессе работы, чтобы не затормаживать процесс. А чтобы запарился, нужно запустить git gc.

Запускаем, и, о чудо! Итоговый размер репы становится 3 Мб.

А заодно теперь можно в каком-нибудь GUI для git сравнивать дампы по дням.

ignore-table

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

extended-insert

По умолчанию mysqldump формирует дамп с так называемыми «расширенными» INSERT’ами. То есть вся вставка данных таблицы происходит одним запросом:

INSERT INTO `table` VALUES ('id1', 'one'), ('id2', 'two'), ('id3', 'three')

Это не здорово, так как diff’ить файлы лучше по строкам, а тут при изменении данных изменяется вся единственная строка.

Отключить это можно с помощью опции --extended-insert=FALSE:

$cmd = 'mysqldump --extended-insert=FALSE -u'.$params['username'].' -p'.$params['password'].' '.$params['dbname'].' > '.$filename;

Теперь сами файлики должны стать больше, но, возможно, git сможет их оптимальнее слить.

Как показывает эксперимент, в плане объёма это не помогает — всё те же 3 Мб в итоге.

Однако, теперь намного приятнее смотреть diff всего этого — сразу видно, какие строки добавились, какие изменились.

Разбить по таблицам

Сохраним дамп не одним файлом, а каждую таблицу отдельно:

$tables = getTablesFromDB();
 
$options = array(
    '--user='.$params['username'],
    '--password='.$params['password'],
    '--extended-insert=FALSE',
    '--dump-date=FALSE',
);
$options = implode(' ', $options);
 
foreach ($tables as $table) {
    $filename = $dir.'/'.$table.'.sql';
    $cmd = 'mysqldump '.$options.' '.$params['dbname'].' '.$table.' > '.$filename;
    echo $cmd.\PHP_EOL;
    system($cmd);
}
 
// git commit

Плюсы такого подхода:

• Некоторые таблицы могут изменять далеко не каждый день. В этом случае в хранилище git’а будет храниться один объект.
• Сливать отдельные файлы быстрее
• diff нагляднее
• Объём хранилища сократился с 3 до 1,8 Мб

Не забываем указать опцию --dump-date=FALSE, а лучше сразу --skip-comments. Иначе каждый раз к дампу будет добавляться комментарий вида «Dump completed on 2013-04-23 17:09:02» и файл будет обновляться всегда.

Применимость

Базы объёмом до 100 Мб и не слишком резво изменяющиеся/разрастающиеся, данный способ обрабатывает вполне стабильно и достаточно быстро.

Реализация

Не следует использовать подобные кривые велосипеды! Поставьте себе лучше какую-нибудь систему.

А вообще вот: Backuper на githab’е.