Как выполнить инкрементное или дифференциальное резервное копирование с помощью основных команд MySQL?

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

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

• Процесс резервного копирования перегружает ЦП и память из-за способа получения данных. К серверу запускаются различные SQL-запросы, и сервер должен обработать результаты, чтобы отправить их на mysqldump. В то же время он mysqldumpдолжен собирать эту информацию и сохранять ее в формате SQL, чтобы иметь возможность воспроизвести ее позже.

• Размер резервных копий намного больше, чем данные на диске. В случае таблиц с двоичными данными (блобы) они выводятся в шестнадцатеричном формате.

• Последующая или одновременная обработка (с использованием конвейеров) сжатия увеличила бы время резервного копирования или нагрузку на процессор, а также время восстановления, поскольку пришлось бы выполнять процесс распаковки.

• Продолжительность резервного копирования чрезвычайно велика по сравнению с эквивалентом чтения данных непосредственно с диска.

• Восстановление происходит очень медленно по сравнению с записью непосредственно на диск.

• Хотя инструмент предоставляет такие опции, как --single-transactionсоздание согласованных резервных копий на уровне базы данных, он не является надежным (*) и может блокировать доступ ко всем таблицам и базам данных на сервере, если журнал транзакций переполняется во время больших дампов. транзакция завершена.

• Такие параметры, как --lock-tablesблокировка использования таблиц во время создания дампа, приводят к блокировке запросов к этим таблицам.

• Процесс восстановления перегружает ЦП, диск и память сервера из-за интерпретации и проверки каждого сделанного SQL-запроса, а также из-за проверки ключей при вставке каждой записи, тем больше, чем больше записей было вставлено (хотя воздействие можно уменьшить, используя такие параметры, как --disable-keys).

(*) Из документации :

Во время создания --single-transactionдампа, чтобы обеспечить допустимый файл дампа (правильное содержимое таблицы и координаты двоичного журнала), никакое другое соединение не должно использовать следующие операторы: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. Непротиворечивое чтение не изолировано от этих операторов, поэтому их использование в таблице, подлежащей дампу, может привести к SELECTтому, что mysqldump извлекает содержимое таблицы для получения неправильного содержимого или сбоя .

Использование ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLEво время резервного копирования может привести к неверным результатам или сбою.

Предложенное решение

Если вы можете использовать другой инструмент (совершенно бесплатный, в отличие от MySQL), я рекомендую использовать инструмент резервного копирования, разработанный Percona, который называется XtraBackup :

Percona XtraBackup — это бесплатное комплексное онлайн-решение для резервного копирования с открытым исходным кодом для всех версий Percona Server для MySQL, MySQL® и MariaDB®. С более чем 2 100 000 загрузок Percona XtraBackup выполняет онлайн неблокирующее, сильно сжатое и высокозащищенное резервное копирование в транзакционных системах, чтобы приложения оставались полностью доступными во время плановых периодов обслуживания.

На испанском:

Percona XtraBackup — это бесплатное комплексное решение для резервного копирования с открытым исходным кодом для всех версий Percona Server, MySQL® и MariaDB®. С более чем 2 100 000 загрузок Percona XtraBackup выполняет высоконадежное, сильно сжатое, неблокирующее онлайн-резервное копирование в транзакционные системы, поэтому приложения остаются полностью доступными во время плановых периодов обслуживания.

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

В документации по инкрементальным бэкапам вы можете увидеть пример использования:

Чтобы сделать полную резервную копию, просто запустите:

xtrabackup --backup --target-dir=/data/backups/base

Чтобы выполнить инкрементное резервное копирование предыдущего:

xtrabackup --backup --target-dir=/data/backups/incremental1 \
    --incremental-basedir=/data/backups/base

Функционирование

Процесс подробно описан на сайте продукта .

Он делает двоичную копию файлов на диске, сводя к минимуму воздействие на сервер, анализируя журнал транзакций, чтобы отметить точную начальную точку ( LSNпорядковый номер журнала) и, следовательно, иметь возможность обеспечить полную согласованность резервного копирования при обновлении журнала. данные в фоновом режиме.

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

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

В настоящее время нет веских причин поддерживать таблицы в MyISAM, так что это не должно быть проблемой.

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

Кроме того, поскольку восстановление выполняется в другом каталоге (который затем необходимо переместить в рабочий каталог), сервер MySQL может быть запущен на другом порту для доступа к восстановленным данным.

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

Недостатки

Если таблицы не оптимизированы (в них есть «остатки для очистки», измененные или удаленные записи, оставляющие старые значения, занимающие неиспользуемое место), при резервном копировании остаток будет скопирован в полную резервную копию (но не в инкрементальную). ), так что рекомендуется выполнить OPTIMIZE TABLEдля тех таблиц большего размера или которые имеют тенденцию накапливать больший остаток перед полным резервным копированием.

Бэкап через LVM

Еще один способ выполнить непротиворечивое резервное копирование — использовать моментальные снимки LVM .

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

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

Процесс будет осуществляться следующим образом:

# Creamos un LV para el snapshot
lvcreate -L1G -n mysqlbackup -s mysqldatos

Где:

• -L1Gпозволяет изменить исходный раздел на 1 ГБ до его заполнения. Если активность базы данных высока, может потребоваться моментальный снимок большего размера.

• -n mysqlbackupбудет называть mysqlbackupэтот логический том.

• -s mysqldatosуказывает, что это будет снимок логического тома mysqldatos.

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

# Averiguamos el nombre del dispositivo donde se ha creado el volumen lógico
lvdisplay | egrep "LV Path*mysqlbackup$"
# Creamos el punto de montaje (si no existe)
[ -d /mnt/mysqlbackup ] || mkdir /mnt/mysqlbackup
# Montamos el snapshot
mount /dev/mapper/vg-mysqlbackup /mnt/ops/mysqlbackup
# [hacemos el backup del tipo que sea]
# ...
# Desmontamos y borramos el snapshot
umount /mnt/ops/mysqlbackup
lvremove /dev/mapper/vg-mysqlbackup

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

Некоторое время назад я искал школу и проект, который у меня был, вот что.

Инкрементное резервное копирование вашей базы данных с помощью Git

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

В конкретном случае MySQL мы сначала сделали бы что-то вроде этого:

$ mkdir mydatabase && cd mydatabase
$ mysqldump --skip-extended-insert > data.sql
$ git init
$ git add data.sql
$ git commit -m "Initial dump of mydatabase"

С этого момента мы можем автоматизировать процесс с помощью такого простого скрипта:

$ cat /usr/bin/git_backup_database!/bin/sh
BACKUP_DIRECTORY=$1
DATABASE=$2

cd $BACKUP_DIRECTORY &&
mysqldump --skip-extended-insert $DATABASE > data.sql &&
git commit -am "Updating dump `date +%Y_%m_%d__%H_%M`"`

В зависимости от объема запросов, которые есть у вашей базы, вам будет интересно с той или иной периодичностью ставить ее в cron. Дополнительно было бы целесообразно запустить $ git gc для оптимизации репозитория. Например, два раза в день и один раз в неделю техническое обслуживание:

0 0 * * * /usr/bin/git_backup_database /path/to/mydatabase mydatabase
0 12 * * * /usr/bin/git_backup_database  /path/to/mydatabase mydatabase
0 1 * * 1 cd /path/to/mydatabase && git gc

Так же с другого компа никто не мешает сделать так $ git clone ssh://equipo:path/to/mydatabaseи иметь всю историю БД во флеше (ну это относительно, свое займет...) или вообще запланировать $git pull чтобы иметь несколько бэкапов на разных машинах. Короче говоря, открываются бесконечные возможности.

Как мы уже упоминали в комментариях, я думаю, что у вас есть вариант, который до сих пор не рассматривался, который заключается в том, чтобы заставить платформу работать вместо того, чтобы искать несколько более «ручное» решение.

Ваша проблема в том, что он сильно разрастается, BBDDи я понимаю, что вы не хотите backupпостоянно блокировать файл BBDD.

Я могу дать вам 3 решения, в зависимости от того, что вам нужно или можете себе позволить на уровне платформы, я рекомендую одно или другое, в этом случае я располагаю их в порядке сложности/стоимости приложения.

MySQL Master/Slave

В данном случае у нас есть одна master de MySQLи та slaveже машина. Таким образом, пока вы можете продолжать писать в него master, у slaveнего есть полная копия BBDDтой, которую вы можете только читать, но которую вы можете блокировать столько раз, сколько хотите, и которую вы можете беззастенчиво давить, так как он будет получать updatesих как он идет. что я могу

Установить его относительно просто, я оставляю вам руководство по установкеmaster/slave de MySQL

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

В нем хорошо то, что его можно собрать с двух машин, поэтому стоимость платформы совсем небольшая и вы можете расширять ее сколько угодно. Количество чтений очень велико, а количество операций записи такое же, как у master. Таким же образом, если происходит сбой slave, ничего не происходит, а в случае сбоя masterвы можете изменить и slaveсохранить masterприложение.

mysql-кластер

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

Здесь у вас есть руководство по установкеcluster de MySQL , это не так просто, как , master/slaveно дает вам гораздо больше возможностей, чем это. Например, это automatic shardingдолжно позволить вам значительно увеличить количество операций записи. Если вы не знаете, что это такое sharding, они комментируют это на StackOverflow на английском языке , наверняка чтение вам поможет.

Кластер камбуза

Это одно clusterиз master/master/masterэтого, вы можете читать и писать с трех серверов одновременно, я думаю, что это слишком много для того, что вам нужно, но если вы думаете, что приложение может сильно увеличиться в размере, это очень хорошо вариант.

Проблема в том, что должно быть как минимум 3 сервера, чтобы он clusterбыл достаточно консистентным, так как то, что вызывается, должно существовать quorum. Количество чтений не увеличивается так сильно, как в a, cluster de MySQLпотому что он не имеет, automatic shardingно, в отличие от master/slaveсервера, может выйти из строя без каких-либо проблем, потому что он quorumможет продолжать существовать.

Здесь вы узнаете, как установить galera в CentOS7 и установить galera в Ubuntu 16.04 , так как я не знаю, какую систему вы используете, я оставлю вас обоих. Я всегда предпочитаю CentOSили Debianдля всего, что связано с серверами.

различия между GaleraиMySQL Cluster

Различий между Galeraи кластерами Mysqlдовольно много, я рекомендую вам прочитать этот слайд-шоу , поскольку, если мы поместим его здесь, ответ будет почти бесконечным, и тогда мнения каждого поступят относительно того, что лучше или хуже.

Я думаю, что с помощью этого я могу дать вам представление о вариантах, которые у вас есть на уровне платформы, без необходимости тянуть что-то более «странное», например, помещать gitв файлMySQL

Если вам нужна дополнительная информация, прокомментируйте, и я помогу вам, чем смогу.