Резервная копия — это образ базы данных в конкретный момент времени. К этому образу можно вернуться в случае непредвиденной потери данных. Резервные копии можно создавать сколь угодно часто. Нужно лишь помнить о том, что это достаточно трудоемкий процесс, продолжительность которого зависит от размера базы данных и скоростных характеристик оборудования.
Создание резервных копий требует от сервера значительных затрат ресурсов, вплоть до того, что работать с другими базами данных станет невозможно. Нужно спланировать этот процесс таким образом, чтобы он приходился на периоды минимальной загруженности сервера. Если используется репликация, то резервные копии лучше создавать на подчиненном сервере.
Если резервная копия была создана в полночь, а сбой базы данных произошел в полдень, половина дневных изменений окажется утерянной. В таком случае может помочь двоичный журнал. В нем фиксируются все изменения базы данных. С помощью утилиты mysqlbinlog можно преобразовать содержимое этого файла в запросы к восстановленной базе данных, которые позволят воссоздать ее состояние на момент сбоя. Таким образом, планируя схему резервного копирования, не забудьте учесть ротацию и архивирование двоичных журналов, чтобы они были синхронизированы с копиями базы данных.
Помните общие правила обращения с резервными копиями. Если они хранятся в той же файловой системе, что и сама база данных, то данные не защищены от сбоев файловой системы. Отсюда правило: копии должны находиться на отдельном носителе. Храните их на перезаписываемом компакт-диске, магнитной ленте или другом жестком диске. Резервные копии могут храниться дома у начальника или администратора компании. Их можно также пересылать по сети в другую систему. С помощью Internet это делать не сложно.
В процессе планирования необходимо предусмотреть тестирование копий и проверку возможности их восстановления на практике. Не ждите, пока случится катастрофа и вам придется учиться восстанавливать архивы. Создайте тестовую среду и потренируйтесь на ней. Можно попробовать восстановить архив во временную пустую базу данных или же воспользоваться более сложной методикой — например, запустить еще один сервер MySQL на другом порту либо на другом компьютере.
В MySQL существуют три основных способа архивирования данных. Первый — это копирование табличных файлов, второй — создание SQL-образов таблиц, третий — создание форматированных текстовых файлов. Первый способ является самым экономным и быстродействующим. Но для таблиц тех типов, которые поддерживают транзакции, последние два способа являются более гибкими. Например, все таблицы InnoDB хранятся в группе больших файлов, поэтому архивы нельзя будет сгруппировать по базам данных или таблицам.
Какой бы метод ни был выбран, не забудьте защитить таблицы от изменений на время резервного копирования. Если копируются табличные файлы, следует остановить сервер. В остальных случаях достаточно поставить блокировки чтения с помощью инструкции LOCK TABLES и выполнить инструкцию FLUSH TABLES. Последняя необходима для того, чтобы все изменения индексов были записаны в таблицы. Наличие блокировок чтения позволит другим потокам параллельно обращаться к таблицам с запросами на выборку.
Инструкции BACKUP TABLE и RESTORE TABLE копируют табличные файлы в указанный каталог. Естественно, серверный процесс должен иметь право записи в этот каталог. Программа MySQL копирует туда файлы с расширениями .frm и .MUD. Индексный файл (.MYI) можно воссоздать на основании первых двух, что позволит сэкономить место в архиве. В листинге 4.2 показан пример архивирования таблицы.
mysql>BACKUP TABLE dictionary TO '/tm/backup';+-----------------+--------+-----------+------------------------+| Table | Op | Msg_type | Msg_text |+-----------------+--------+-----------+------------------------+| test.dictionary | backup | status | ok |+-----------------+--------+-----------+------------------------+1 rows in set (0.27 sec)
Листинг 4.2. (html, txt)
Функции копирования файлов предоставляются операционной системой, поэтому данный способ создания резервных копий является самым быстрым. Таблица dictionary, скопированная в листинге 4.2, содержит более 100000 записей, а файл данных занимает почти 3 Мбайт. Как видите, процедура архивирования такой таблицы заняла менее секунды.
Инструкция BACKUP TABLE самостоятельно заботится о блокировании таблиц и очистке табличных буферов. Это означает, что, в отличие от других методов резервного копирования, дополнительные инструкции не нужны.
Инструкция RESTORE TABLE копирует архивные файлы в каталог базы данных и перестраивает индексы. Таблица не должна существовать на момент восстановления. В случае необходимости можно удалить ее с помощью инструкции DROP TABLE или же вручную удалить табличные файлы.
В листинге 4.3 показаны результаты восстановления таблицы dictionary, резервная копия которой была создана в листинге 4.2. Обратите внимание на то, что процесс восстановления длился гораздо дольше, чем архивирование. Причина в том, что на перестройку индексов уходит много времени.
mysql> RESTORE TABLE dictionary FROM '/tmp/backup';+-----------------+---------+-----------+--------------------------+| Table | Op | Msg_type | Msg_text |+-----------------+---------+-----------+--------------------------+| test.dictionary | restore | status | ok |+-----------------+---------+-----------+--------------------------+1 rows in set (1 min 22.24 sec)
Листинг 4.3. (html, txt)
Если резервные копии создаются вручную, то в архив можно также включить индексный файл. В этом случае в процессе восстановления таблицы индексный файл будет просто скопирован в каталог базы данных. Тем не менее его всегда можно воссоздать с помощью инструкции REPAIR TABLE. Предположим, таблица dictionary была полностью утеряна. Процесс ее восстановления начнем с копирования frm-файла обратно в каталог базы данных. Создать пустые файлы данных и индексов можно с помощью инструкции TRUNCATE TABLE. Затем необходимо скопировать старый файл данных поверх нового. После этого вводится инструкция REPAIR TABLE. В листинге 4.4 показано, как программа MySQL обнаруживает расхождение в количестве записей и перестраивает индексы.
mysql> REPAIR TABLE dictionary;+-----------------+---------+-----------+-----------------------------------------+| Table | Op | Msg_type | Msg_text |+-----------------+---------+-----------+-----------------------------------------+| state | repair | warning | number of rows changed from 0 to 104237 || test.state | repair | status | ok |+-----------------+---------+-----------+-----------------------------------------+2 rows in set (1 min 25.12 sec)
Листинг 4.4. (html, txt)
Для безопасного создания резервных копий лучше пользоваться специальной программой, чем делать все вручную. С этой целью в дистрибутив MySQL входит Perl-сценарий mysqlhotcopy. В листинге 4.5 показано, как с его помощью создаются копии таблиц привилегий. Команда ls позволяет убедиться, что все файлы, в том числе индексные, на месте.
# mysqlhotcopy mysql /trap/hcLocked 6 tables in 0 seconds.Flushed tables(mysql.columns_priv, mysql.db, mysql.func, mysql.host, mysql.tables_priv, mysql.user) in 0 seconds.Copying 18 files…Copying indices for 0 files…Unlocked tables.Mysqlhotcopy copied 6 tables |(18 files) in 1 second (1 seconds overall).# ls /tmp/hc/mysqlcolumns_priv.MYD db.MYD func.MYD host.MYD tables_priv.MYD user.MYDcolumns_priv.MYI db.MYI func.MYI host.MYI tables_priv.MYI user.MYIcolumns_priv.frm db.frm func.frm host.frm tables_priv.frm user.frm
Листинг 4.5. (html, txt)
Сценарий mysqlhotcopy блокирует одновременно все таблицы базы данных, после чего очищает табличные буферы и копирует файлы. Сценарий можно запускать во время работы сервера, даже если в этот момент пользователи делают запросы к базе данных. Естественно, пока происходит копирование таблиц, пользователям будет запрещено вносить в них изменения.
Формат табличных файлов понятен только программе MySQL. Если же создать SQL-образы таблиц, то их можно будет перенести в другие СУБД. Кроме того, в некоторых ситуациях полезно просматривать такие SQL-инструкции. Предположим, к примеру, что потеря данных оставалась незамеченной на протяжении нескольких месяцев. Возможно, пользователи удалили какие-то записи и лишь позднее обнаружили, что это было сделано неправильно. Нужно восстановить только удаленные записи, но не известно, когда точно они были удалены. Если резервные копии хранятся в формате SQL, можно просмотреть архивы и поискать, когда последний раз встречались требуемые записи. Недостатком такого способа резервного копирования является то, что процедура восстановления занимает много времени, поскольку программа MySQL вынуждена выполнять каждую инструкцию из архива.
Для создания sql-образа таблицы предназначена утилита mysqldump. Она записывает текст инструкций в поток stdout, поэтому нужно перенаправить результаты ее работы в файл. В листинге 4.6 показан созданный этой утилитой образ таблицы db из базы данных mysql. Утилита была запущена с опцией --opt которая включает режим оптимальных установок.
# MySQL dump # # Host: localhost Database: mysql# # Server version 4.12.25-log# # Table structure for table 'db' # DROP TABLE IF EXISTS db; CREATE TABLE db ( Host char(60) binary NOT NULL default '', Db char(64) binary NOT NULL default '', User char(16) binary NOT NULL default '', Select_priv enum('N', 'Y') NOT NULL default 'N', Insert_priv enum('N', 'Y') NOT NULL default 'N', Update_priv enum('N', 'Y') NOT NULL default 'N', Delete_priv enum('N', 'Y') NOT NULL default 'N', Create_priv enum('N', 'Y') NOT NULL default 'N', Drop_priv enum('N', 'Y') NOT NULL default 'N', Grant_priv enum('N', 'Y') NOT NULL default 'N', References_priv enum('N', 'Y') NOT NULL default 'N', Index_priv enum('N', 'Y') NOT NULL default 'N', Alter_priv enum('N', 'Y') NOT NULL default 'N', PRIMARY KEY (Host, Db, User), KEY User (User))TYPE=MyISAM COMMENT = 'Database privileges'; tt# Dumping data for table 'db'# LOCK TABLES db WRITE;INSERT INTO db VALUES('%', 'test', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y'),('%', 'test\\_%', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y'),('Localhost', 'freetime', 'httpd', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', 'N');UNLOCK TABLES;
Листинг 4.6. (html, txt)
He забудьте заблокировать все таблицы для чтения, прежде чем запускать утилиту mysqldump. В противном случае целостность результатов не гарантируется. Предположим, имеется приложение, которое хранит информацию о клиентах и их электронных адресах. Создание учетной записи нового клиента включает добавление записи в таблицу client и последующую вставку одной или нескольких записей в таблицу email_address. Если параллельно с этим создавать резервную копию базы данных, то может оказаться, что в промежутке между созданием образов таблиц client и email_address приложение попытается обновить обе эти таблицы. Доступ к первой таблице будет запрещен, а ко второй — нет. В результате в архиве появятся адреса, не соответствующие ни одной записи таблицы клиентов.
Чтобы восстановить данные из такого архива, достаточно выполнить SQL-сценарий в интерпретаторе mysql. Можно просто перенаправить сценарий на вход этой утилиты или же воспользоваться ее командой source. Интерпретатор выполнит все инструкции сценария так, как если бы они были введены в командной строке.
Утилита mysqldump имеет режим создания текстового образа таблицы. В этом режиме для каждой архивируемой таблицы создаются два файла. Один из них имеет расширение .sql и содержит соответствующую инструкцию CREATE TABLE. Второй файл имеет расширение .txt и содержит записи таблицы, причем для разделения полей применяются символы табуляции. В листинге 4.7 показана команда, создающая текстовый образ таблицы dictionary в каталоге /tmp.
[/tmp]# mysqldump --verbose --tab=/tmp test dictionary# Connecting to localhost...# Retrieving table structure for table dictionary...# Sending SELECT query... # Disconnecting from localhost...
Листинг 4.7. (html, txt)
Для восстановления данных из такого архива необходимо сначала создать таблицу, а затем выполнить инструкцию LOAD DATA INFILE, которая вставит записи в таблицу. Стандартный формат файла, создаваемого утилитой mysqldump, соответствует тому формату, который по умолчанию распознается инструкцией LOAD DATA INFILE. В листинге 4.8 демонстрируется загрузка данных в таблицу dictionary в среде mysql.
Создать файл, понимаемый инструкцией LOAD DATA INFILE, позволяет также инструкция SELECT с предложением INTO (листинг 4.9). Схему таблицы необходимо получить другим путем, например с помощью инструкции SHOW CREATE TABLE.
mysql> SELECT * FROM dictionary INTO OUTFILE 'tmp/dictionary.txt';Query OK, 104237 rows affected (6.42 sec)
Листинг 4.9. (html, txt)
Один из способов восстановления таблиц заключается в использовании двоичного журнала. Достаточно преобразовать его содержимое в SQL-инструкции и выполнить их. Предварительно необходимо заблокировать все таблицы для записи или отключить всех клиентов от сервера. Преобразование двоичного журнала осуществляется с помощью утилиты mysqlbinlog (листинг 4.10). Результаты ее работы нужно направить в файл или интерпретатору mysql. Обратите внимание: инструкция SET меняет метку текущего времени сеанса, чтобы дата создания таблицы осталась неизменной.
# mysqlbinlog --offset=1 --short-form red-bin.001use freetime;SET TIMESTAMP=991767105;UPDATE session SET LastAction = now() WHERE ID='fNbbnOLBYYlqesqa';use freetime;SET TIMESTAMP=991767134;UPDATE session SET LastAction = now() WHERE ID='fNbbnOLBYYlqesqa';use freetime;SET TIMESTAMP=991767134;DELETE FROM project_view WHERE Project=2 AND User=2;use freetime;SET TIMESTAMP=991767135;INSERT INTO project_view VALUES (2, 2, now());
