Как восстановить базу данных MySQL "безопасно"

Question

Как вам следует "безопасно" восстановить (и сохранить) базу данных MySQL? Под "безопасным" я подразумеваю: при восстановлении следует создавать / перезаписывать нужную базу данных, но не рисковать изменять что-либо за пределами этой базы данных.

Я уже прочитал https://dev.mysql.com/doc/refman/5.7/en/backup-types.html.

У меня есть внешние пользователи. Они и я, возможно, захотим обменять резервные копии для восстановления. У нас нет коммерческой резервной копии MySQL Enterprise, и мы не ищем стороннего коммерческого предложения.

В Microsoft SQL Server есть BACKUP а также RESTORE команды. BACKUP создает файл, содержащий только ту базу данных, которую вы хотите; и его строки, и вся его схема / структура включены. RESTORE принимает такой файл и создает или перезаписывает его структуру. Пользователь может восстановить базу данных с тем же именем или указать другое имя базы данных. Такое поведение именно то, что я ищу.

В MySQL я наткнулся на 3 варианта:

1. Большинство людей, кажется, используют mysqldump создать "файл дампа", и mysql чтобы прочитать это обратно. Файл дампа содержит список произвольных операторов MySQL, которые просто выполняются mysql, Это совершенно неприемлемо: файл может содержать любые операторы SQL. (Ограничение прав доступа для восстановления пользователя, чтобы попытаться гарантировать, что он не может делать что-либо "непослушное", недопустимо.) Существует также проблема, что пользователь, возможно, создал файл дампа с опцией "Включить схему CREATE" (MySQL Workbench), который жестко кодирует исходное имя базы данных для отдыха. Такой "дамповый" подход совершенно мне не подходит, и я нахожу удивительным, что кто-то будет использовать его в производственной среде.

2. Я сталкивался с MySQL SELECT ... INTO OUTFILE а также LOAD DATA INFILE заявления. По крайней мере, они не содержат код SQL для выполнения. Тем не менее, они выглядят как большая работа, имеют дело с таблицей за раз, а не со всей базой данных, и не имеют дело со структурой таблиц, вы должны знать это самостоятельно для восстановления. E сть mysqlimport вспомогательная утилита командной строки, но я не вижу ничего для экспорта, и я не вижу ее для восстановления полной базы данных.

3. Последнее - использовать то, что MySQL называет "физическим (необработанным)", а не "логическим" резервным копированием. Это работает с базами данных каталогов и самих файлов. Это эквивалент SQL Server detach/attach метод резервного копирования / восстановления. Но, согласно https://dev.mysql.com/doc/refman/5.7/en/backup-types.html, он имеет все возможные предостережения, например: "Резервные копии переносимы только на другие машины с идентичным или подобным оборудованием. характеристики." (Я понятия не имею, например, некоторые пользователи Windows или Windows, я понятия не имею об их архитектуре) и "Резервное копирование может выполняться, когда сервер MySQL не работает. Если сервер работает, необходимо выполнить соответствующую блокировку, чтобы что сервер не изменяет содержимое базы данных во время резервного копирования. " (не говоря уже о восстановлении).

Так может ли что-нибудь удовлетворить (что я считаю) мои скромные требования, как указано выше, для резервного копирования / восстановления MySQL? Действительно ли я единственный человек, который считает вышеуказанные 3 единственными, но неприемлемыми, возможными решениями?

Answer 1

1 - mysqldump - Я использую это довольно часто, обычно в тех средах, где я обрабатываю все детали самостоятельно. У меня есть одна конфигурация, в которой я использую ее для отправки копий базы данных разработки - для полного сброса / восстановления - другим разработчикам. Это, вероятно, самое быстрое решение, имеет некоторые разумные параметры конфигурации (например, для включения / исключения определенных таблиц) и генерирует очень функциональный код SQL (например, каждый пакет INSERT достаточно мал, чтобы избежать проблем с блокировкой / скоростью). Для решения "заменить всю базу данных" или "заменить таблицы ключей в конкретной базе данных" это работает очень хорошо. Я не слишком обеспокоен проблемой "произвольных команд SQL " - если это проблема, то у вас, вероятно, есть другие проблемы с пользователями, пытающимися "делать свое дело".

2 - SELECT ... INTO OUTFILE а также LOAD DATA INFILE - Проблема в том, что если у вас есть действительно большие таблицы, то LOAD DATA INFILE оператор может вызвать проблемы, потому что он пытается загрузить все сразу. Вы также должны добавить код для создания (если необходимо) или очистить таблицы до LOAD DATA,

3 - Физическая (необработанная) передача файлов. Это может работать, но при ограниченных обстоятельствах. У меня была одна ситуация с базой данных в несколько гигабайт, и я решил сжать необработанные файлы, переместить их на новый компьютер, распаковать и просто сказать MySQL "все уже есть". В основном это работало хорошо. Но я не рекомендовал бы это для любого процесса, выполняемого без присмотра / конечного пользователя, из-за МНОГИХ возможных проблем.

Что я рекомендую?

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

2 - Напишите свою собственную программу. Я делал это несколько раз. Первоначально это более трудоемкий процесс, но он позволяет вам контролировать каждый аспект процесса и передавать файл, который содержит только данные без какого-либо реального кода SQL. Вы можете управлять конкретной базой данных, таблицами и т. Д. Одна из них заключается в том, что если вы вносите какие-либо изменения в структуру таблиц, индексы и т. Д., Вам нужно будет убедиться, что информация каким-то образом передается получающей проблеме, чтобы она могла изменить структуры по мере необходимости - это не проблема с mysqldump поскольку он обычно заменяет таблицы, создает новые структуры, индексы и т. д. Это может быть написано на любом языке, который может подключаться к MySQL - он не обязательно должен совпадать с языком вашего приложения.

Answer 2

Если вы не собираетесь использовать сторонние инструменты (например, innobackupex), тогда вы можете использовать... mysqldump, который входит в пакет mysql.
Я не могу понять, почему это не приемлемо для вас, почему вам не нравятся команды sql в этих дампах. При восстановлении одной базы данных на сервере, который уже содержит другие базы данных, рекомендуется использовать отдельного пользователя с правами только на запись в восстановленную базу данных. Тогда даже если пользователь, выполняющий восстановление, изменит команды sql и попытается записать в другую базу данных, он не сможет.
При выполнении резервного копирования (физической копии файлов базы данных) все экземпляры должны быть выключены, а сервер MySQL не запущен. Подобное оборудование означает, что вам нужно иметь те же каталоги, что и на исходном сервере (если вы не измените my.cnf перед запуском сервера и размещением всех файлов в правильных каталогах).
При входе в mysql старайтесь не сравнивать его с сервером sql - это совершенно другой подход и философия.
Но если вы все-таки убедили себя в использовании стороннего инструмента - я рекомендую innobackex от Percona, который, между прочим, бесплатный.

Answer 3

Инструмент экспорта, который дополняет mysqlimport является mysqldump --tab, Это выводит файлы CSV как SELECT...INTO OUTFILE, Он также выводит структуру таблицы в гораздо меньших файлах.sql. Итак, для каждой таблицы есть два файла.

Как только вы воссоздаете свои таблицы из файлов.sql, вы можете использовать mysqlimport импортировать все файлы данных. Вы даже можете использовать mysqlimport --use-threads возможность заставить его загружать несколько файлов данных параллельно.

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