Самый эффективный способ бэкапа mysql.

Вы не уточнили движек баз.

Прошу прощения. Смешанные. В основном InnoDB и MyISAM.

Какое критичное время простоя?

Скажем так, время простоя не сильно критично, но и злоупотреблять не хотелось бы. 2-4 минуты в офлайне, особенно в ночные часы, думаю, не повредят.

Ну так может lvm пользовать? :)

Я в этом деле слегка нуб, поэтому хотел бы спросить на счёт того, есть ли риск, что базы побьются или по каким-либо причинам я потом не смогу накатить снимок?

Вопрос достаточно тривиальный

Нетривиальный :-/ Зависит от конкретных условий. При чём для ряда условий качественных решений вообще нет. А так:

— mysqldump --tab=... --quick + mysqlimport. Этот вариант намного быстрее, чем mysqldump + mysql, но неприменим при использовании репликации. Плюс остановка сервера на время дампа.

— Репликация на бэкап-сервер и mysqldump с него. Недостаток в требовании бэкап-сервера

— Снэпшоты LVM с работающей базой MySQL. Много паразитной возни со снэпшотами и последующим объединением. Сильное снижение производительности на время снэпшота.

— Простое копирование файлов БД. Работает только для MyISAM.

Большое спасибо за столь развёрнутый ответ. Моё внимание ещё до поднятия топика привлёк способ с mysqldump, но я так и не понял, имеет ли для него значение тип базы. Ведь у MyISAM присутствуют файлы *.frm, *.MYD и *.MYI, что происходит с ними при бэкапе таким образом?

но я так и не понял, имеет ли для него значение тип базы

Нет. Он работает в любых случаях и его единственный недостаток — невысокая скорость дампа. Скажем, базу на 10Гб дампит на достаточно мощной машине минут 10..15. Недостаток — потому что во время дампа БД доступна только на чтение и любая попытка записать что-то лочит базу уже и на чтение.

Ведь у MyISAM присутствуют файлы *.frm, *.MYD и *.MYI, что происходит с ними при бэкапе таким образом?

mysqldump делает текстовый файл с SQL-запросами, содержащими создание БД нужной структуры и заполнение её данными.

Спасибо за разъяснения. Не могли бы посоветовать способ, который по вашему мнению подходит лучше? Опишу ситуацию подробнее. Имеется домашний сервер... ну как домашний, стоит дома, конфигурация серверная. На нём крутится два десятка сайтов, из них 4-5 особо важны и их желательно бэкапить почаще, остальные можно редко и без особых нервов. Мне нужна процедура максимально простая и безопасная, чтобы с ней мог справиться даже обыкновенный уверенный пользователь линукса, на уровне «ты вот это напиши в терминале и оно заработает», чтобы я мог передать заботу по бэкапу другому человеку и у него не возникло непредвиденных обстоятельств в процессе (насколько это возможно). При необходимости я могу поднять второй сервер для репликации, но предпочёл бы обойтись без этого. Заранее спасибо.

DxdV

Скажем так, время простоя не сильно критично, но и злоупотреблять не хотелось бы. 2-4 минуты в офлайне, особенно в ночные часы, думаю, не повредят.

Если базы небольшие по объёму, то наиболее простой способ, на мой взгляд, - использование mysqldump.

DxdV

Спасибо за разъяснения. Не могли бы посоветовать способ, который по вашему мнению подходит лучше? Опишу ситуацию подробнее. Имеется домашний сервер... ну как домашний, стоит дома, конфигурация серверная. На нём крутится два десятка сайтов, из них 4-5 особо важны и их желательно бэкапить почаще, остальные можно редко и без особых нервов. Мне нужна процедура максимально простая и безопасная, чтобы с ней мог справиться даже обыкновенный уверенный пользователь линукса, на уровне «ты вот это напиши в терминале и оно заработает», чтобы я мог передать заботу по бэкапу другому человеку и у него не возникло непредвиденных обстоятельств в процессе (насколько это возможно). При необходимости я могу поднять второй сервер для репликации, но предпочёл бы обойтись без этого. Заранее спасибо.

Какой размер баз?

У всех сайтов, кроме одного базы мелкие, 100-200 мегабайт и вряд ли существенно увеличатся. У одного сайта база около 1,5 гб и в перспективе будет только расти.

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

Хм, не проще ли передать заботу по бэкапу скрипту? Сам использую mysqldump, но у меня базы небольшие, самая большая - 3Гб, остальные и до 500 Мб не дотягивают.

Скрипту - это само собой. Я имею в виду, чтобы в случае чего, человек понимал, как это сделать руками. Хотя бы в общих чертах.

Простые методы бекапа:
1) mysqldump databasename |gzip -c > databasename.sql.gz

2) offline бекап файлов:

/etc/init.d/mysql stop
tar czf mysql-full.tar.gz /var/lib/mysql
/etc/init.d/mysql start

Сложный, для innodb неблокирующий:

innobackupex .
innobackupex --aplly-log ./2013-....
cd ./2013-...
tar czf ../mysql-full.tar.gz .

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

Выводы:
- для баз < 10GB можно и просто использовать mysqldump
- для баз с большим количеством myisam использовать offline backup или снапшоты файловой системы
- для баз с большим количеством innodb использовать xtrabackup/innodb hot backup

Спасибо. Думаю, что вопрос закрыт и я, вероятно, остановлюсь на mysqldump. Всем большое спасибо за помощь и подробные ответы на кучу моих вопросов.

DxdV

У всех сайтов, кроме одного базы мелкие, 100-200 мегабайт и вряд ли существенно увеличатся. У одного сайта база около 1,5 гб и в перспективе будет только расти.

На таком размере можно спокойно использовать mysqldump.
Вполне вероятно даже не производя остановку инстанса СУБД.

mysqldump databasename | gzip

За такое надо бить по рукам! :) Всё время, пока будет идти компрессия, БД будет залочена. Только через промежуточный файл. Дамп в него пройдёт намного быстрее, чем он потом будет паковаться.

Вот таким скриптиком можно сдампить все БД на сервере:

#!/bin/bash

for db in `ls /var/lib/mysql`; do
    if [ -d /var/lib/mysql/$db ]; then
        echo Dump $db
        FILE=$db-$(date +%y-%m-%d_%H:%S).sql
        mysqldump $db > $FILE
        if [ -f $FILE ]; then
            if [ -f $FILE.gz ]; then rm $FILE.gz; fi
            echo "    ... compress $FILE"
            nice -n +19 ionice -c3 gzip $FILE
        fi
    fi
done

Есть ещё xtrabackup от Percon-ы, если нужно без лока баз.

два чая этому господину

оно конечно крутое, но я лично не нашел в нем возможности восстановить из бекапа конкретную базу, а не все базы. даже с innodb_file_per_table = 1. такой способ вообще есть?

— Репликация на бэкап-сервер и mysqldump с него. Недостаток в требовании бэкап-сервера

Плюсую вот этот вариант. Требование бэкап-сервера может быть упрощено до «иметь на том же сервере отдельный экземпляр мускуля». Если сервер только один, а его простой критичен - самый годный вариант.

Извиняюсь за глупый вопрос. Мне нужно останавливать сервер mysql перед применением скрипта или нет?

А ещё вот ругается на что-то с невозможностью коннекта :(

Dump ecshop
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
... compress ecshop-13-09-11_17:19.sql
Dump elevator_online
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
... compress elevator_online-13-09-11_17:19.sql
Dump en_gnb
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
... compress en_gnb-13-09-11_17:19.sql

скрипт то он как пример написал, навскидку. понятно что там не хватает

mysqldump -u user -ppassword $db > $FILE

Спасибо большое. Я просто в этом очень слабо разбираюсь, поэтому воспринял совет буквально.

Просто в ~/.my.cnf можно записать логин с паролем, чтобы не вводить каждый раз:

[client]
user=username
password=xxxxxxx

Если ещё и хост не localhost, то добавить

host=host.tld

Мне нужно останавливать сервер mysql перед применением скрипта или нет?

Нет, не нужно. Более того, если сервер остановить, то mysqldump не сработает. Он забирает данные с сервера как любой клиент.

Спасибо, добавил параметры вызова mysqldump по совету выше и всё заработало. Ещё один глупый вопрос: бэкап происходит полный? То есть бэкапится вся структура базы и всё её содержимое? При восстановлении из копий я получу полноценную базу со всей содержавшейся в ней инфой?

Это если нет myisam иначе грустно все-же.

бэкап происходит полный? То есть бэкапится вся структура базы и всё её содержимое?

Да. Точнее — зависит от ключей mysqldump. Всё настраивается. По умолчанию сейчас происходит полный дамп. Проще посмотреть в полученный дамп и убедиться. Там должны быть для каждой таблицы:
DROP TABLE IF EXITS ... — удаление старой тапблицы, есть есть
CREATE TABLE ... — создание новой
потом куча INSERT … INSERT … INSERT — заполнение таблицы данными.

Нужно обратить внимание на соответствие кодировок. Если mysql и базы нормально настроены, то всё автоматом будет, но если нет — то может быть фигня. Скажем, русские символы необратимо в знаки '???? ???' перейдут.

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

там, майисам - в 99% случаев mysqldump хватает за глаза.

Еще маленький нюанс. Если в базах есть хранимые процедуры, то mysqldump надо запускать с ключем '--routines', по умолчанию они не бекапятся.

За такое надо бить по рукам!

пока будет идти _запись на диск_ , БД будет залочена.

Сжатие быстрее чем запись на диск, особенно lz4, qcompress, lzo.

Если критично время блокировки то mysqldump на мастере вообще нет смысла использовать, есть https://launchpad.net/mydumper , xtrabackup/innobackupex, mylvmbackup

Сжатие быстрее чем запись на диск

Ну, не знаю, что за процессор для такого нужен. Q9400, дамп размером ~10Гб.
Время дампа — несколько минут (легко посчитать, что, скажем, при скорости 50Мб/с это будет 3 минуты, реально и меньше бывает)
Время упаковки — около 8 минут (получается около 20Мбайт/сек).

А если в bz2 паковать?

есть https://launchpad.net/mydumper , xtrabackup/innobackupex, mylvmbackup

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

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