Резервное копирование данных в MySQL
Резервное копирование базы данных — это такая штука, которую вечно приходится настраивать для уже работающих проектов прямо на «живых» production-серверах.
Подобная ситуация легко объяснима. В самом начале любой проект еще пуст и там просто нечего копировать. В фазе бурного развития головы немногочисленных разработчиков заняты исключительно прикручиванием фишек и рюшек, а также фиксом критических багов с дедлайном «позавчера». И только когда проект «взлетит», приходит осознание, что главная ценность системы — это накопленная база данных, и её сбой станет катастрофой.
Эта обзорная статья — для тех, чьи проекты уже достигли этой точки, но жареный петух ещё не клюнул.
Базу данных MySQL можно скопировать, если временно выключить MySQL-сервер и просто скопировать файлы из папки /var/lib/mysql/db/. Если сервер не выключить, по очевидным причинам вероятна потеря и порча данных. Для больших нагруженных баз эта вероятность близка к 100%. Кроме того, при первом запуске с «грязной» копией базы данных MySQL-сервер начнет процесс проверки всей базы, который может затянуться на часы.
В большинстве «живых» проектов регулярное выключение сервера БД на длительное время неприемлемо. Для решения этой проблемы применяется трюк, основанный на снэпшотах файловой системы. Снэпшот — это что-то вроде «фотографии» файловой системы на определенный момент времени, сделанный без реального копирования данных (и потому быстро). Аналогичным образом работает «ленивое копирование» объектов во многих современных языках программирования.
Общая схема действий такова: блокируются все таблицы, сбрасывается файловый кэш БД, делается снэпшот файловой системы, разблокируются таблицы. После этого файлы спокойно копируются из снэпшота, после чего он уничтожается. «Блокирующая» часть такого процесса занимает время порядка секунд, что уже терпимо. В качестве расплаты на какое-то время, пока «жив» снэпшот, снижается производительность файловых операций, что в первую очередь бьет по скорости операций записи в базу.
Некоторые файловые системы, например, ZFS, поддерживают снятие снэпшотов нативно. Если вы не пользуетесь ZFS, но на вашем сервере стоит менеджер томов LVM, вы также сможете скопировать базу MySQL через снэпшот. Наконец, под *nix можно воспользоваться драйвером снэпшотов R1Soft Hot Copy, но этот способ не заработает в контейнере openvz (процесс бэкапа MySQL описан здесь).
Для баз MyISAM существует официальная бесплатная утилита mysqlhotcopy, которая «правильно» копирует файлы баз MyISAM без остановки сервера. Существует аналогичная утилита для InnoDB, но она платная, хотя и возможностей в ней больше.
Копирование файлов — самый быстрый способ перебросить базу данных целиком с одного сервера на другой.
Синтаксис и базовая команда
Создание дампа выполняется из командной строки Linux или Microsoft с помощью утилиты mysqldump. Она идет в составе с пакетом mysql/mariadb (mysql-client) и может быть запущена как локально на сервере СУБД, так и с удаленного компьютера.
Пример базовой команды для резервирования базы:
mysqldump -v -h127.0.0.1 -uroot -p base > /tmp/dump.sql
* в данном примере мы создадим резервную копию базы base и поместим его в папку /tmp, назвав сам файл dump.sql. Подключение к базе на сервере 127.0.0.1 происходит от пользователя root. Это самый простой пример создания дампа MySQL.
Базовые параметры команды mysqldump:
| Параметр | Описание |
|---|---|
| -h | Адрес сервера, к которому нужно подключиться. |
| -u | Учетная запись, от которой выполняется резервное копирование. Необходимо, чтобы у пользователя были соответствующие права. |
| -p | Пароль учетной записи. Его можно ввести в команде, например -p12345 (для скрипта) или оставить -p (безопаснее). |
* полный перечень параметров смотрите в официальном руководстве.
Резервные копии баз данных MyISAM или InnoDB/MyISAM
Приведённая выше команда, тем не менее, не работает, если вы используете таблицы MyISAM или смесь таблиц InnoDB/MyISAM, поскольку MyISAM не поддерживает транзакции. В этом случае вам нужно явным образом заблокировать все таблицы на время проведения резервного копирование. Как это сделать показано ниже.
Вышеприведённый MySQL оператор flush закрывает все открытые таблицы в сервере MySQL и получает блоки чтения всех таблиц во всех существующих базах данных, тем самым предотвращая любые записи в базы данных. Это обеспечивает последовательных снимок запущенной системы. Теперь продолжайте выполнять резервное копирование.
По окончании снимите глобальную блокировку баз данных.
Восстановление баз данных из резервной копии без phpMyAdmin в Windows
Для экспорта баз данных, phpMyAdmin работает довольно неплохо. Но с импортом возникло ряд проблем. Намного проще и в разы быстрее сделать импорт без phpMyAdmin и других помощников на PHP скриптах.
Для этого откройте командную строку Windows. Нажмите комбинацию WIN+x, затем выберите Windows PowerShell. Чтобы выйти из PowerShell и перейти в командную строку, выполните:
Затем смените текущую папку (укажите путь до подпапки bin внутри папки, куда установлена MySQL или MariaDB — у вас это может быть другой путь):
И запустите команду вида:
Например, моя резервная копия содержится в файле localhost.sql, который размещён в папке C:UsersAlexDownloads, тогда команда выглядит так:
Импорт базы MySQL
Как и в случае с экспортом импортировать базу данных в 2domains можно двумя способами:
- через phpMyAdmin
- подключившись по SSH
Рассмотрим каждый из этих способов.
Импорт базы данных через phpMyAdmin
Войдите в phpMyAdmin по инструкции.
Кликните по базе данных, которую хотите импортировать. Затем перейдите во вкладку «Импорт»:
Нажмите Выберите файл и укажите файл дампа базы (в формате .sql). При необходимости смените кодировку. Нажмите Вперёд:
Готово! Вы импортировали базу данных через phpMyAdmin. Если файл дампа слишком большой, воспользуйтесь вторым способом (подключитесь по SSH).
Импорт базы данных через подключение по SSH
Проверьте, присутствует ли файл дампа базы данных в корневой папке сайта. Если файла нет, загрузите его по FTP или через панель управления (в разделе «Менеджер файлов»).
mysql -uUSER -p DBNAME < DUMPFILE.sql
- USER — пользователь базы данных (например, «u1234567_dbuser»)
- DBNAME — имя экспортируемой базы данных (например, «u1234567_database»)
- DUMPFILE.sql — название файла, с которым сохранится дамп базы данных в формате sql.
Между флагами -u, -p и их аргументами (в данном случае USER и DBNAME) пробел отсутствует.
Как восстановить базу данных MySQL из резервной копии
Для восстановления резервной копии используется утилита mysql. Синтаксис:
Например, восстановим базу данных db_name из файла резервной копии db_backup.sql, которая хранится в корневом каталоге. Для этого используем команду:
