Какую программу использовать для резервного копирования MySQL большого объема без лока базы?

Question

[no1]

Есть mysql+nginx веб-сервер под статистику. Раз в сутки Mysqldump при объеме базы >1 Гб намертво лочит сервер минут на 5-10, что достаточно неприятно. Вариант с простым dump&restore не подойдет из-за наличия innodb таблиц, какие есть еще альтернативы?

Отдельно копировать по таблицам неинтересно, т.к. главная таблица статистики >50% объема базы. Выделенный/дублирующий сервер под базу не предлагать, машина всего одна.

Answer 1

[Wott]

вообще стандартное средство в таких случаях — поднять slave и с ним делать все что надо

Answer 2

[pr0tect0r]

mysqlhotcopy
набор Maatkit (Percona toolkit)
Утилита mk-parallel-dump делает снимок (дамп) таблиц и баз данных в параллельном режиме. Применяемая в паре с ней mk-parallel-restore, — загружает ранее сделанные резервные дампы. Главное отличительное свойство этих утилит от штатных средств и методов MySQL — использование параллельных методов доступа к базе данных, что заметно повышает скорость выполнения операции.

И, наконец, более сложный и избирательный вариант бэкапа представляет mk-archiver — эта утилита выгружает записи, отобранные по заданному критерию, с одной таблицы MySQL в другую, этого же (или любого другого) MySQL-сервера, либо — в указанный файл. Утилита спроектирована с учетом очень простой возможности его расширения (подключение своих постпроцессов и предварительных фильтров) и добавления собственной логики сохранения данных.

Comments

[Сергей Береснев]

А как они борются с тем, что база может меняться в процессе дампинга?

[no1]

1. mysqlhotcopy — только для myisam таблиц, мимо

2. Первое что написано в доках к mk-parallel-dump:

mk-parallel-dump is not a backup program! It is only designed for fast data exports, for purposes such as quickly loading data into test systems. Do not use mk-parallel-dump for backups.

Как-то неспортивно после такой характеристики использовать скрипт на продакшне ;)

Answer 3

[zuborg]

посмотрите ещё на Percona XtraBackup, может пригодится

Answer 4

[Тагир Валеев]

Можно решать вопрос на уровне файловой системы с помощью snapshot'ов
en.wikipedia.org/wiki/Snapshot_%28computer_storage%29#File_systems
en.wikipedia.org/wiki/Shadow_Copy
Принцип copy-on-write: когда вы делаете копию файла, он ссылается на те же блоки, что и исходный, но если какой-то блок файла начинает модифицироваться, этот блок реально копируется.

Comments

[zuborg]

Один из заслуживающих внимания подходов, но есть несколько ньюансов.
1. Требуется сброс данных из буферов в файловую систему (FLUSH TABLES)
2. Для целосности требуется удерживать блокировку на запись на время сброса буферов и создание снепшота (FLUSH TABLES WITH READ LOCK)
3. При большой активности и загрузке диска время, потраченное на обе операции (сброс + снепшот) может быть весьма большим, порядка минут.
4. Для уменьшения этого времени стоит сделать предварительные сбросы буферов mysql и FS, без блокировки, сделав просто FLUSH TABLES и создав после этого снепшот и удалив его. После этого полное создание бекапа со снепшотом пройдет быстрее.
5. Даже с блокировкой снепшот получится «грязный». Во первых, mysql запустит его проверку, это займет время при восстановлении, во вторых, у меня на innodb были случаи, что на горячих снепшотах рассинхронизировалась впоследствии репликация. На снепшотах после остановки mysqld проблем с репликацией не было.

Answer 5

[serjs]

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

xtrabackup если начинать использовать то сразу на перконовский стек переходить, но нужно учитывать все особенности работы форка в виде плюшек — инкременты, более быстрый бекап/откат

Comments

[sergey_novikov]

первый абзац — это mylvmbackup

Answer 6

[Nastradamus]

Странно… у меня база 1.5 гига, тоже статистика — основное что там есть (bitrix). НО после перехода на innodb mysqldump перестал вешать сайт.

Comments

[Валерий Сотников]

@nateless: У меня ничего не блокирует при дампе:
Как сделать экпорт в кодировке utf8 из большой БД MySQL с помощью mysqldump?

Answer 7

[Push_Ok]

самый простой способ, это репликачия мастер мастер, можно и мастер слейв, и со второго мастера\слейва только снимать дапм =)

Answer 8

[linjan]

Percona Toolkit предоставляет утилиты xtrabackup, которая работает с InnoDB таблицами без блокирования таблиц. Крайне рекомендую.

Percona XtraBackup is an open source, free MySQL® hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. With Percona XtraBackup, you can achieve the following benefits:

Backups that complete quickly and reliably
Uninterrupted transaction processing during backups
Savings on disk space and network bandwidth
Automatic backup verification
Higher uptime due to faster restore time