Для полезности резервные копии должны выполняться регулярно. Полное резервное копирование (снимок данных в определённый момент времени) в MySQL можно выполнить с помощью нескольких инструментов. Например, MySQL Enterprise Backup может выполнить резервное копирование всего экземпляра с оптимизациями для минимизации накладных расходов и предотвращения перерывов при резервном копировании файлов данных InnoDB; mysqldump предоставляет онлайн-резервное копирование. В этом обсуждении используется mysqldump.

Предположим, что мы выполняем полное резервное копирование всех наших таблиц InnoDB во всех базах данных с помощью следующей команды в воскресенье в 13:00, когда нагрузка низкая:

$> mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql

Полученный файл .sql, созданный mysqldump, содержит набор SQL-команд INSERT, которые могут быть использованы для повторной загрузки резервированных таблиц впоследствии.

Эта операция резервного копирования приобретает глобальную блокировку на чтение всех таблиц в начале дампа (используя FLUSH TABLES WITH READ LOCK). Как только эта блокировка получена, считываются координаты двоичного журнала, и блокировка снимается. Если при вызове команды FLUSH выполняются длинные операторы обновления, операция резервного копирования может приостановиться до завершения этих операторов. После этого дамп становится без блокировки и не нарушает чтения и записи в таблицы.

Ранее предполагалось, что таблицы для резервного копирования являются InnoDB таблицами, поэтому --single-transaction использует согласованное чтение и гарантирует, что данные, увиденные mysqldump, не изменятся. (Изменения, внесённые другими клиентами в таблицы InnoDB, не видны процессу mysqldump.) Если операция резервного копирования включает не транзакционные таблицы, для обеспечения согласованности необходимо, чтобы они не изменялись во время резервного копирования. Например, для MyISAM таблиц в базе данных mysql не должно быть административных изменений в учетных записях MySQL во время резервного копирования.

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

Для создания резервных копий с приращениями необходимо сохранить изменения с приращениями. В MySQL эти изменения представлены в двоичном журнале, поэтому сервер MySQL всегда должен запускаться с опцией --log-bin для включения этого журнала. При включённом двоичном протоколировании сервер записывает каждое изменение данных в файл во время обновления данных. Рассматривая каталог данных сервера MySQL, который был запущен с опцией --log-bin и работал в течение нескольких дней, мы находим эти двоичные логи MySQL:

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index

Каждый раз при перезапуске сервер MySQL создаёт новый двоичный лог-файл, используя следующее число в последовательности. Во время работы сервера вы также можете заставить его закрыть текущий двоичный лог-файл и начать новый вручную, выполнив команду SQL FLUSH LOGS или с помощью команды mysqladmin flush-logs. У mysqldump также есть опция для сброса логов. Файл .index в каталоге данных содержит список всех двоичных логов MySQL в каталоге.

Двоичные логи MySQL важны для восстановления, потому что они образуют набор резервных копий с приращениями. Если вы убедитесь, что сбросили логи при создании полной резервной копии, двоичные лог-файлы, созданные после этого, содержат все изменения данных, внесённые после резервного копирования. Давайте немного изменим предыдущую команду mysqldump, чтобы она сбрасывала двоичные логи MySQL в момент создания полной резервной копии и чтобы файл дампа содержал имя текущего двоичного лога:

$> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases > backup_sunday_1_PM.sql

После выполнения этой команды в каталоге данных появляется новый двоичный лог-файл, gbichot2-bin.000007, потому что опция --flush-logs заставляет сервер сбросить свои логи. Опция --master-data заставляет mysqldump записывать информацию из двоичного лога в свой вывод, поэтому полученный файл дампа .sql включает эти строки:

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

Так как команда mysqldump сделала полную резервную копию, эти строки означают два момента:

В понедельник в 13:00 мы можем создать резервную копию с приращениями, сбросив логи, чтобы начать новый двоичный лог-файл. Например, выполнение команды mysqladmin flush-logs создаёт gbichot2-bin.000008. Все изменения между полной резервной копией в воскресенье в 13:00 и понедельник в 13:00 находятся в файле gbichot2-bin.000007. Эта резервная копия с приращениями важна, поэтому её следует скопировать в безопасное место. (Например, заархивировать её на ленту или DVD, или скопировать на другой компьютер.) Во вторник в 13:00 выполните ещё одну команду mysqladmin flush-logs. Все изменения между понедельником в 13:00 и вторником в 13:00 находятся в файле gbichot2-bin.000008 (который также следует скопировать в безопасное место).

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

$> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases --delete-master-logs > backup_sunday_1_PM.sql