Резервирование и копирование баз данных

Резервирование и копирование баз данных с помощью программы mysqldump

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

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

% mysqldump samp_db > /usr/archives/mysql/samp_db.1999-10-02

В результате ее выполнения будет создан файл, начинающийся следующим образом:

# MySQL dump 6.0
# 
# Host: localhost Database: samp db
# --------------------------------------------
# Server version		3.23.2-alpha-log
# 
# Table structure for table ‘adsence’
# 
CREATE TABLE adsence {
	student id int (10) unsigned DEFAULT ‘0’ NOT NULL,
	date date DEFAULT ‘0000-00-00’ NOT NULL,
	PRIMARY KEY (student id, date)
};
#
# Dumpling data for table ‘adsence’
#
INSERT INTO adsence VALUES (3, ‘1999-09-03’);
INSERT INTO adsence VALUES (5, ‘1999-09-03’);
INSERT INTO adsence VALUES (10, ‘1999-09-06’);

Остальная часть файла также состоит из операторов CREATE TABLE и INSERT.

Чтобы сразу после создания сжать файл архива, необходимо ввести вместо представленной выше следующую команду:

% mysqldump samp_db | gzip > /usr/archives/mysql/samp_db.1999-10-02.gz

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

% mysqldump samp_db student score event adsence > gradebook.sql 
% mysqldump samp_db member president > hist-league.sql

Если создаваемые файлы архивов планируется использовать для периодического обновления содержимого другой базы данных, можно добавить в командную строку mysqldump опцию -add-drop-table. В этом случае в файл архива будут заноситься операторы drop table if EXISTS. При загрузке файла архива с этими операторами в другой базе данных сообщения о существовании таблиц появляться не будут. Администраторы, управляющие двумя серверами, могут применять этот способ для периодической загрузки данных из баз данных одного сервера в базы другого.

Для переноса содержимого базы данных на другой сервер вовсе необязательно создавать файлы архива. Достаточно убедиться в существовании этой базы данных на другом компьютере, а затем создать канал (pipe), чтобы mysql смог напрямую считывать вывод команды mysqldump. Так, например, копирование базы данных с компьютера pit-viper.snake.net на boa.snake.net легко выполняется с помощью следующих команд:

% mysqladmin -h boa.snake.net create samp db 
% mysqldump samp_db | mysql -h boa.snake.net samp_db

Чтобы впоследствии обновить информацию базы данных на компьютере boa.snake.net, запустите только вторую команду, добавив опцию -add-drop-table во избежание ошибок записи в уже существующие таблицы:

% mysqldump -add-drop-table samp_db | mysql -h boa.snake.net samp_db

В строке команды mysqladmin можно использовать и другие полезные опции.

• Комбинация опций -flush-logs и —lock-tables полезна для контроля состояния базы данных. Первая опция закрывает и повторно открывает файлы журналов обновлений, а вторая —блокирует все резервируемые таблицы. Если сервер создает журналы обновлений с порядковыми именами, каждый новый журнал будет содержать все запросы на изменение базы данных с момента ее последнего резервирования. (Блокировка таблиц закрывает доступ к базе данных пользователям, пытающимся внести изменения.)

  Если опция -flush-logs применяется для согласования времени создания журнала обновлений и времени резервирования, лучше архивировать сразу всю базу данных. При резервировании отдельных таблиц довольно трудно синхронизировать журналы обновлений с файлами архивов. В процессе восстановления содержимое журнала обновлений обычно извлекается для каждой базы данных отдельно. Невозможно рассортировать эту информацию еще и по таблицам, поэтому администратору это придется делать самостоятельно.

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

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

• Эффекта, прямо противоположного результатам опции —opt, можно достичь с помощью опции —delayed. Эта опция заставляет команду mysqldump записывать в файл архива операторы INSERT DELAYED вместо операторов INSERT. Опция —delayed оказывается весьма полезной, если при загрузке файла архива в другую базу данных администратор желает уменьшить влияние этой операции на выполнение текущих запросов.

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

  % mysqldump --opt samp_db | mysql --compress -h boa.snake.net samp_db

  Команда mysqldump имеет и множество других опций.