SQL-Ex blog

Что такое логические резервные копии?

Логические резервные копии - это бэкапы данных, состоящие из операторов, которые позволяют воссоздать данные. Подумайте о том, как вы обычно делаете бэкапы в MySQL - не важно, используете ли вы функциональность экспорта в phpMyAdmin, или mysqldump для резервирования ваших особо ценных данных, оба эти варианта имеют одно общее - они создают операторы, которые воссоздают данные, получая в результате логические бэкапы. И это в точности то, что понимается под логическими резервными копиями - логические бэкапы предназначены для воссоздания данных путем выполнения операторов SQL.

Хотя иногда разработчикам может потребоваться воссоздание файлов.

Что такое физические резервные копии?

Физические резервные копии, как вы могли уже догадаться, это копии "физических" данных - файлов - которые составляют базу данных. Что касается MySQL, физический бэкап MySQL обычно состоит из копии каталога данных, находящегося здесь (это для Windows; в Linux каталогом является /var/lib/mysql/mysql5.7.36/data):

Логические бэкапы в MySQL

Создание логических бэкапов в MySQL легче, чем вы можете себе представить - подумайте о любой части MySQL, которая резервирует операторы для воссоздания данных. Есть такая? Тогда у вас есть логический бэкап.

В MySQL логические бэкапы выполняются с помощью:

• Функция экспорта в phpMyAdmin


• Использование mysqldump


• Или использование оператора SELECT [столбцы] INTO OUTFILE [файл] в конкретной базе данных:
  

phpMyAdmin

Вопреки популярному мнению, функция экспорта в phpMyAdmin может предложить довольно много вариантов выбора. Вот только некоторые из них:

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


• Возможность выбора формата бэкапа.


• Вариант, который блокирует таблицы при записи в них данных.


• Возможность экспорта таблиц отдельными файлами.


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

Некоторые из возможностей phpMyAdmin представлены на скриншоте ниже:



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

mysqldump

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

• Возможность продолжения создания бэкапа, даже если возникают ошибки.


• Возможность резервирования только схемы таблиц.


• Делать дамп данных, удовлетворяющих только заданному предложению WHERE.

Для использования mysqldump нам нужны привилегии, достаточные для выполнения запросов SELECT, а синтаксис должен выглядеть подобно следующему оператору (выполняется перед входом в базу данных через интерфейс командной строки в Windows или Linux):



Если вы заботитесь о безопасности, помните, что вы можете также не предоставлять имя пользователя (-u) и пароль (-p), указав их в главном конфигурационном файле MySQL в разделе [mysqldump], например, так (присвоение значений переменным user и password) - при этом никто не увидит ваши имя пользователя и пароль в списке последних выполненных команд в Linux:



Для большинства людей phpMyAdmin и mysqldump предоставляет достаточно контроля над бэкапами - однако некоторым может не хватить возможностей этих инструментов; тем, кто отважится погрузиться в мир больших данных, возможно, придется познать это на собственном опыте. Хотя phpMyAdmin и mysqldump могут предложить мощные методы для резервирования ваших данных, более быстрые, чем обычно (LOCK TABLES дает возможность разработчикам блокировать таблицы на определенное время до завершения резервирования, что делает эти операции значительно быстрее), оба эти метода имеют существенный недостаток — создаваемые ими резервные копии изобилуют операторами INSERT.

Тут нет ничего "неверного" с самими операторами INSERT, но их слабость в том, что они очень плохо приспособлены для обработки более пары миллионов строк за раз: корень причины состоит в том, что операторы INSERT вызывают большие накладные расходы для MySQL - среди прочего это включает также и парсинг. Представьте себе выполнение одного за другим 500000 запросов INSERT, когда MySQL должен выполнить следующие шаги:

1. Старт.


2. Проверка ограничений.


3. Открыть таблицы.


4. Инициализировать запрос.


5. Запросить блокировки.


6. Сделать работу (выполнить запрос).


7. Завершить запрос.


8. Закрыть таблицы.


9. Выполнить очистку.

Выполните эти шаги. Теперь выполните их снова и повторяйте еще для 499,998 итераций. Разве это звучит быстро? Конечно, если мы заблокируем таблицы, то сможем избежать шагов #3 и #8, но, тем не менее, наши запросы будут медленными - есть много вещей, которые MySQL должен предпринять при выполнении запросов INSERT. Хотя тут имеется решение - мы должны также рассмотреть запрос LOAD DATA INFILE [путь] INTO TABLE [таблица], который специально предназначен для больших наборов данных.

SELECT INTO OUTFILE & LOAD DATA INFILE

Альтернатива INSERT, которая специально разрабатывалась для больших наборов данных, выглядит примерно так:

LOAD DATA INFILE ‘/tmp/data.txt’ INTO TABLE demo_table [FIELDS TERMINATED BY|OPTIONALLY ENCLOSED BY] “|”;

Это, конечно, запрос LOAD DATA INFILE. Он предназначен специально для массовой загрузки данных и обладает следующими особенностями:

• Имеется возможность пропускать отдельные строки или столбцы.


• Имеется возможность загружать данные только в заданные столбцы.


• Его накладные расходы на парсинг существенно ниже.

Когда выполняется LOAD DATA INFILE, MySQL не выполняет столько операций, сколько их требуется для INSERT INTO, что делает команду существенно более быстрой. Чтобы сделать бэкап большого набора данных, используйте ее вариант - SELECT * INTO OUTFILE - например, так:



Затем используйте LOAD DATA INFILE для загрузки данных обратно в вашу базу данных (здесь мы также используем оператор IGNORE, чтобы игнорировать все потенциальные ошибки - дубликаты ключей и т.д.):

Резюме

• Бэкапы, созданные в phpMyAdmin или mysqldump, могут быть просто повторно импортированы в MySQL с использованием простого запроса, подобного этому, или с помощью интерфейса phpMyAdmin (удалите опции -u и -p, если имя пользователя и пароль заданы в my.cnf):
  mysql -u root -p [database_name] < database_backup.sql


• Для восстановления бэкапа, подготовленного с помощью SELECT … INTO OUTFILE, используйте LOAD DATA INFILE.

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

Физические резервные копии в MySQL

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

1. Убедитесь, что MySQL остановлен (в противном случае вы бы копировали файлы, с которыми MySQL продолжает работать).


2. Перейдите в каталог данных (наберите SELECT @@datadir во время подключения к вашему экземпляру MySQL, чтобы увидеть путь к этому каталогу):
   
   


3. Перейдите в каталог, где вы будете сохранять физический бэкап, и создайте в нем каталог с помощью команды mkdir:
   mkdir [directory_name]
   


4. Скопируйте файлы с именем ibdata1 (главный файл табличного пространства InnoDB), ib_logfile0 и ib_logfile1 (эксклюзивные для InnoDB файлы журналов undo и redo) в каталог, который вы только что создали, затем скопируйте сам каталог данных. Имейте в виду, что если каталог данных велик, операция копирования может занять некоторое время.


5. Вы получили физический бэкап!

Если вы по каким-то причинам еще используете MyISAM (которая является устаревшей системой хранения), то создание физических резервных копий будет еще проще - вам просто нужно скопировать папку данных, содержащую ваши базы данных, их данные (файлы .MYD) и связанные с ними индексы (файлы .MYI).

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

Резюме

Физические бэкапы копируют файлы с данными - для получения их копии при использовании InnoDB делается копия табличного пространства InnoDB (ibdata1) и журналов undo и redo (ib_logfile0 и ib_logfile1), а затем папки с данными. Для резервирования MyISAM делается копя всех файлов данных и индексов (файлов .MYD и .MYI) в папке данных.

Заключение

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