Как сохранить базу данных mysql на компьютер
Обновлено: 07.07.2024
В панели DirectAdmin присутствует раздел, в котором производится контроль над базами данных, раздел Управление MySQL. В этом разделе отображается список баз данных пользователя и основные элементы управления:
В этом случае браузер предложит Вам сохранить на локальный компьютер файл <имя базы>.gz, в данном примере это файл user_database.gz. Файл предоставляется в сжатом виде, как архив gz.
Способ №2: Экспорт с помощью web-интерфейса phpMyAdmin.
phpMyAdmin — веб-приложение с открытым исходным кодом, написанное на языке PHP и представляющее собой веб-интерфейс для администрирования СУБД MySQL. phpMyAdmin позволяет через браузер осуществлять администрирование сервера MySQL, выполнять SQL-запросы и просматривать содержимое баз данных и таблиц. Приложение пользуется большой популярностью у веб-разработчиков, так как позволяет управлять СУБД MySQL с помощью дружественного интерфейса, без необходимости использования сложных SQL-запросов для выполнения простых задач.
Для перехода в web-интерфейс phpMyAdmin выбираем соответствующий пункт в панели Direct Admin:
Для доступа к phpMyAdmin требуется ввести логин и пароль пользователя базы данных, которые Вы указывали при создании базы. Первое, что нам нужно сделать после входа в интерфейс phpMyAdmin – выбрать интересующую нас базу данных из списка:
Далее будет предложено выбрать параметры экспорта базы данных.
1. Экспорт. Этот пункт позволяет нам выделить таблицы, которые необходимо включить в резервную копию базы данных. По умолчанию, в резервную копию будут включены все таблицы.
2. Формат резервной копии. На выбор предлагается несколько форматов резервной копии, в данном случае мы выбираем тип SQL. Остальные способы используются при выполнении специфических задач и используются очень редко.
3. Очень важный момент – галочка «Сохранить как файл». Если эта галочка установлена, Вам будет предложено скачать резервную копию. В противном случае, на экран будет выведена текстовая версия резервной копии в виде MySQL-запросов.
4. Шаблон имени файла. По умолчанию имя файла будет иметь следующий вид: <имя базы>.<формат файла>, в нашем примере это user_database.sql.
5. Сжатие. Этот пункт позволяет выбрать метод сжатия файла:
без сжатия, т.е. файл в формате sql, размер файла будет соответствовать размеру базы данных;
zip, т.е. файл в формате архива .zip, размер файла будет уменьшен за счет архивации резервной копии;
gzip, т.е. файл в формате .gz, размер файла будет уменьшен за счет архивации резервной копии;
После выбора всех необходимых параметров достаточно нажать кнопку «ОК» и дождаться подготовки резервной копии. Если база данных имеет значительный объем, на подготовку резервной копии может потребоваться некоторое время. В итоге Вам будет предложено сохранить файл резервной копии базы данных на локальный компьютер.
Способ №3: Экспорт с помощью скрипта Sypex Dumper.
Скачайте сам скрипт, распакуйте архив и загрузите файл dumper.php на Ваш с сервер, в каталог public_html. Для корректной работы скрипта потребуется создать каталог хранения резервных копий (дампов) баз данных. Для создания каталога перейдите в Менеджер файлов панели Direct Admin, перейдите в каталог public_html и создайте новый каталог backup.
После этого скрипт предложит выбрать действие над Вашими базами данных: «Backup / Создание резервной копии БД» и «Restore / Восстановление БД из резервной копии». Нас интересует первый пункт.
Пункт «БД» позволяет выбрать необходимую базу данных из списка Ваших баз данных. Фильтр таблиц позволяет указать таблицы, которые будут включены в резервную копию. Более детальную информацию о фильтрах Вы можете узнать на сайте разработчика скрипта Sypex Dumper. В пункте «Метод сжатия» Вы можете указать будет ли применяться сжатие Gzip при создании резервной копии (запакованный файл с расширением .gz), или же будет сохранена резервная копия в формате .sql. Пункт Степень сжатия используется, только если выбран метод сжатия Gzip. Чем больше значение этого параметра, тем меньше будет размер файла.
После выбора всех параметров создания резервной копии нажмите кнопку Применить. Начнется создание резервной копии, сам процесс будет отображаться на экране монитора:
После завершения процесса Вам будет предоставлен лог создания резервной копии, а так-же, предложение скачать файл резервной копии. Если Вы желаете продолжить работу со скриптом Sypex Dumper, нажмите кнопку «Вернуться». Стоит отметить тот факт, что резервная копия, создаваемая данным скриптом, будет храниться в каталоге backup, который мы создали предварительно, т.е. скачивать резервную копию не обязательно, она может храниться на сервере, в каталоге backup.
Способ №4: Экспорт с помощью скрипта Sypex Dumper.
Данный способ доступен только тем пользователям, у которых есть доступ к SSH (Secure SHell, удаленное управление операционной системой). Для экспорта резервной копии базы данных необходимо подключиться по SSH к серверу (к примеру, с помощью ssh-клиента Putty, если у Вас ОС Windows, или с помощью терминала, если у Вас ОС семейства Linux).
После подключения в командной строке достаточно выполнить следующую команду:
В этом случае команда для импорта базы данных будет иметь следующий вид:
Если после выполнения данной команда операционная система не сообщает об ошибках экспорта, значит резервная копия успешно экспортирована. Размер резервной копии базы данных не имеет значения.
Рассмотренные способы являются универсальными, и позволяют решить любую задачу экспорта баз данных.
С необходимостью резервного копирования, так или иначе, сталкивается каждый администратор и разработчик баз данных или сайтов.
Причём данную задачу часто решают разными методами. Кто-то по старинке использует «консоль», кто-то использует программу с графическим интерфейсом MySQL Workbench и создаёт резервные копии с помощью Reverse Engineer, как это советуют в ряде статей в интернете.
Однако MySQL Workbench предлагает и другой способ резервного копирования, который значительно проще, чем работа с Reverse Engineer и позволяет создавать резервные копии буквально несколькими кликами мыши.
Для этого выберите в главном меню Server-> DataExport.
После этого откроется вкладка с настройками.
Выберите нужную базу данных в левой части вкладки, после этого в правой её части отобразится список объектов базы данных.
По умолчанию в резервную копию будут сохранены все объекты. Поэтому, если сохранение некоторых из них не требуется, снимите флажок рядом с его названием.
В нижней части вкладки можно выбрать способ резервного копирования. Их всего 2.
- Export to Dump Project Folder – сохранение резервной копии осуществляется путём создания папки в которую помещаются скрипты для каждого из объектов базы данных по отдельности. Перед созданием резервной копии необходимо указать папку для сохранения скриптов.
- Export to Self-Contained File – все объекты базы данных сохраняются в 1 общий скрипт. Данный вариант более удобен и является классическим при создании резервных копий баз данных для программ и сайтов. Перед созданием резервной копии необходимо указать имя файла скрипта.
В этой статье использован способ сохранения Export to Self-Contained File.
После того, как выбраны база данных, её объекты, которые необходимо сохранить в резервной копии и способ резервного копирования, остаётся только нажать кнопку Start Export и резервная копия будет создана.
Эта статья в первую очередь рассчитана на новичков в администрировании, для тех, кто хочет научиться самостоятельно производить импорт и экспорт баз данных. Зачем вам может это понадобиться? Допустим, вы хотите самостоятельно сделать бэкап, чтобы в дальнейшем при необходимости восстановить определенную версию базы данных. Или вам нужно сделать перенос сайта на другой сервер либо в другую среду разработки. В общем, причин может быть множество, поэтому понимание того, как сначала сделать, а потом импортировать резервную копию, лишним не будет.
Для того, чтобы выполнить все дальнейшие действия, у вас должны быть:
а) доступ к серверу на базе Linux, на котором работает MySQL/MariaDB;
б) название базы данных и данные доступа к ней.
Используем консоль
Экспорт
Для того, чтобы произвести экспорт, мы будем использовать утилиту mysqldump. При помощи нее осуществляется работа с текстовыми файлами базы данных. Итак, вы должны знать название базы данных, а также иметь доступ (логин и пароль) к аккаунту, который имеет, по крайней мере, доступ read only (только для чтения).
Для экспорта базы данных введите вот такую команду:
в которой нужно ввести имя пользователя с необходимым доступом, название нужной вам базы данных, а также data-dump.sql – файл в текущей директории, куда будут сохранены данные.
После ввода этой команды вы не увидите никакого вывода на экране, однако вы можете проверить содержимое файла data-dump.sql для того, чтобы убедиться, что теперь он является резервной копией вашей базы данных.
Содержимое файла должно выглядеть примерно так, как показано ниже. В документе будет указано название базы данных (в данном случае MySQL), ее название и другие данные.
Импорт
Для того, чтобы импортировать существующий файл в MySQL или MariaDB, вам нужно начать с создания новой базы данных. Именно в нее вы затем загрузите содержимое резервной копии.
Сначала подключитесь к базе данных в качестве root-пользователя (либо другого пользователя, который сможет создать новую базу данных):
После того, как вы подключились к консоли MySQL, создайте новую базу данных (в данном случае new_database):
После этого на экране появился следующий вывод:
Теперь для выхода из консоли MySQL нажмите CTRL+D. Далее переходите к самому импорту. Сделать это можно, введя вот такую команду:
Команда очень похожа на команду экспорта, вам нужно ввести имя пользователя, название новой базы данных, куда вы будете импортировать данные (в качестве примера new_database), и название самого файла, который вы собираетесь импортировать (data-dump.sql).
И тогда при всех последующих запросах в данном сеансе автоматически будет использоваться эта база данных. Данную установку можно изменить, использовав команду use с названием другой базы данных.
Что касается команды show, то она используется для того, чтобы посмотреть информацию о самих базах данных, о таблицах, столбцах, которые они содержат, а также о состоянии сервера.
Допустим, нам нужно посмотреть, список таблиц в базе. Для этого вводим:
Хотите увидеть список столбцов в какой-то определенной таблице? Используйте команду SHOW COLUMNS FROM и название нужно вам таблицы:
Статистику по работе сервера можно получить в ответ на команду:
Используем phpMyAdmin
Экспорт и импорт баз данных можно также делать через phpMyAdmin. В общем и целом, пожалуй, это даже более простой путь, чем использование консоли.
Экспорт
Зайдите в phpMyAdmin и выберите базу данных, с которых вы хотите работать.
Далее выберите вкладку «Экспорт» и, в зависимости от своих предпочтений, быстрый или обычный метод экспорта. Второй подойдет для тех, кто хочет самостоятельно выставить все настройки.
Импорт
Выполнить импорт базы данных тоже совсем несложно. Как и в предыдущем случае, в списке слева выберите нужную вам базу данных, а затем перейдите во вкладку «Импорт».
Импорт успешно завершён, выполнено 32 запроса.
В списке слева вы можете выбрать базу данных, с которой работали, и посмотреть имеющиеся файлы, а также их содержимое (и изменить их).
Заключение
Выбор подходящего метода экспорта и импорта баз данных зависит только от вас и ваших предпочтений – кому-то проще работать в консоли, а для кого-то понятнее phpMyAdmin. Главное, нужно регулярно делать бэкапы, в том числе и ваших баз данных.
Кстати, полезную информацию о базах данных я также нашел в Справочном центре Timeweb.
Сохранение и восстановление базы данных MySQL и что делать, если статья безвозвратно утерянна
У многих наверняка назрел вопрос. Неужели нельзя произвести восстановление базы данных из сохраненной копии (бэкапа), тем более, почти на всех хостингах, сохранение баз данных происходит в автоматическом режиме?
Действительно ведь нигде, ни на компьютере ни в сети, не было не одной сохраненной копии статьи, над которой я трудился целых два дня. Мне было ужасно жаль свой труд и потраченное время, но больше всего я не хотел писать статью заново.
Со мной будут солидарны многие блогеры в том, что легче написать новый пост, чем восстанавливать утерянный.
Причины повреждения базы данных
Почему надо обязательно делать резервную копия базы данных? Давайте я перечислю несколько причин, пришедших мне на ум.
Причина №2. Технические проблемы на сервере Вашего хостинг-провайдера. Надо очень внимательно относится к выбору хостинга.
Причина №3. Повреждения базы данных в следствии взлома и заражение вирусами.
Причина №4. Повреждение во время установки плагинов и модулей или после обновления CMS.
Причина №5. Повреждение в ходе экспериментов или неумелых действий (как раз мой случай).
Я перечислил конечно далеко не все причины, но по-моему и этого вполне достаточно, чтобы задуматься о постоянном сохранении базы данных и своего труда.
Сохранение базы данных
Надо сказать, что способов сохранения баз данных довольно много и некоторые из них под силу применять только профессионалам. Я такие затрагивать не буду. Расскажу лишь о самых распространенных вариантах бэкапа.
Способ №1. Плагины WordPress
Я не буду вникать в настройки данных плагинов, информацию об этом вы можете без труда найти в интернете, перечислю лишь самые популярные из них и опишу их основные функции.
Плагин WordPress Database Backup. Умеет делать копию базы данных, в зависимости от настроек, сохраняя ее на сервере, сохраняя ее на компьютере или отправляя ее на e-mail, через указанный временной промежуток.
Хороший старый плагин, который теперь мало используют из-за функций бэкапа на самом сервере. Но не надо забывать, что на сервере бэкап делается один раз в сутки, а в плагине можно установить интервал два раза в сутки или вообще поставить на каждый час.
Плагин BackUpWordPress. Включает в себя функции создание резервных копий базы данных, а также файлов ресурса и сохранение их на сервере, на компьютере или на e-mail, через заданный временной промежуток.
При видимом преимуществе в функционале, по-моему плагин имеет один существенный недостаток, а именно минимальный временной интервал копирования один раз в сутки. Получается, что он полностью дублирует функцию бекапа на хостинге.
Способ №2. Ручное сохранение
Ручное сохранение базы данных, конечно не может сравниться с автоматическим, но тем не менее, бываю случаи, когда надо срочно сделать резервную копию, например перед обновлением движка или перед началом эксперимента (я как раз нарушил данное правило).
Далее нам предложат на выбор два варианта:
Как сделать резервную копию файлов сайта
Надо понимать, что база данных это еще не весь сайт и также необходимо регулярно делать резервные копии всех файлов сайта. Для этого можно использовать плагин, описанный выше, или делать копии в ручном режиме: напрямую с хостинга или с помощью FTP клиента.
Как скачивать файлы на домашний компьютер с помощью FTP клиента FileZilla, читайте здесь.
Итак, с сохранением базы данных и файлов сайта, мы разобрались, теперь я расскажу, как при необходимости произвести восстановление из бэкапа.
Восстановление базы данных MySQL из бэкапа
Прежде чем приступить к восстановлению базы данных, нам надо удалить поврежденные таблицы.
Для этого заходим в phpMyAdmin, выбираем название поврежденной базы данных и нам откроется страница с ее таблицами:
Идем дальше. Если вы делали бэкап без архивации, т.е. сохраняли файл с расширением .sql, то для восстановления базы данных, надо будет произвести действия схожие с переносом базы данных с Денвера на сервер.
Но не надо расстраиваться, просто запакуйте файл ваша_база.sql в архив и дайте ему название ваша_база.sql.zip и приступим к правильному восстановлению базы данных.
Радуемся успешному восстановлению базы данных MySQL.
Но в произошедшей со мной истории, которую я начал рассказывать в начале статьи, не было спасительной резервной копии за нужное число и время. Я сказал, что не сдался и это правда!
Что делать, если статья безвозвратно утеряна
Так, какие еще могут быть варианты? Конечно! Вездесущий Гугл, возможно успел проиндексировать страницу.
В моем случаи запрос выглядел так:
Опять неудача! Даже бродяга Гугл ничего не успел узнать о моем свежеиспеченном посте. Ужасно жаль! Хотя по кэшу Гугла можно восстановить всю базу данных, но в моем случаи это не помогло.
Не может быть, ведь где-то же она должна была сохраниться? Мысли неслись беспорядочным потоком, перебирая даже самые, на первый взгляд, немыслимые варианты.
И тут меня осенило. В последнее время я упорно занимался скоростью загрузки и мне приходилось настраивать блог на отображение страниц из кэша браузеров. Т.е, при открывании страницы, отправляется запрос к базе данных, тем самым нагружая ее, а по-хорошему, страница должна подгружаться из кэша браузера. Идея!
Браузер! Ведь только он мог видел мою статью и наверняка где-то она хранится, остается только ее найти.
Я аж подпрыгнул на месте, но явилась мне совсем не то, что я ожидал. Вот отрывок страницы кэша:
Да уж, здесь без шифровальной машины не обойтись.
Точно! Надо найти файлы кэша на компьютере.
На заметку! Все файлы кэша браузера Google Chrome хранятся в C:\Users\Имя пользователя\AppData\Local\Google\Chrome\User Data\Default\Cache.
Но когда я открыл папку кэша, то понял, что на поиски нужного файла у меня уйдет уйма времени и проще будет написать статью заново.
Немного переварив очередное фиаско, я принялся штудировать интернет, на предмет облегчения работы с файлами кэша. Ведь как-то же с ними работают.
После распаковки, копируем в директорию с программой, файл русификатора и утилита будет полностью на русском языке.
После запуска утилиты, я был приятно удивлен наличию URL адресов к каждому файлу кэша и морю другой, полезной для поиска, информации.
Воспользовавшись горячими клавишами CTRL + F, я открыл функцию поиска по утилите, куда вбил URL потерянной страницы:
Я даже в глубине души почувствовал, что это именно тот файл, который мне нужен, и который я так долго искал.
Я чувствовал глубокое удовлетворение, вперемешку с облегчением, с добавлением коктейля из разных положительных эмоций. Чудо все-таки произошло и наконец-то я мог спокойно лечь спать.
Базу данных MySQL можно скопировать, если временно выключить MySQL-сервер и просто скопировать файлы из папки /var/lib/mysql/db/. Если сервер не выключить, по очевидным причинам вероятна потеря и порча данных. Для больших нагруженных баз эта вероятность близка к 100%. Кроме того, при первом запуске с «грязной» копией базы данных MySQL-сервер начнет процесс проверки всей базы, который может затянуться на часы.
В большинстве «живых» проектов регулярное выключение сервера БД на длительное время неприемлемо. Для решения этой проблемы применяется трюк, основанный на снэпшотах файловой системы. Снэпшот — это что-то вроде «фотографии» файловой системы на определенный момент времени, сделанный без реального копирования данных (и потому быстро). Аналогичным образом работает «ленивое копирование» объектов во многих современных языках программирования.
Общая схема действий такова: блокируются все таблицы, сбрасывается файловый кэш БД, делается снэпшот файловой системы, разблокируются таблицы. После этого файлы спокойно копируются из снэпшота, после чего он уничтожается. «Блокирующая» часть такого процесса занимает время порядка секунд, что уже терпимо. В качестве расплаты на какое-то время, пока «жив» снэпшот, снижается производительность файловых операций, что в первую очередь бьет по скорости операций записи в базу.
Некоторые файловые системы, например, ZFS, поддерживают снятие снэпшотов нативно. Если вы не пользуетесь ZFS, но на вашем сервере стоит менеджер томов LVM, вы также сможете скопировать базу MySQL через снэпшот. Наконец, под *nix можно воспользоваться драйвером снэпшотов R1Soft Hot Copy, но этот способ не заработает в контейнере openvz (процесс бэкапа MySQL описан здесь).
Для баз MyISAM существует официальная бесплатная утилита mysqlhotcopy, которая «правильно» копирует файлы баз MyISAM без остановки сервера. Существует аналогичная утилита для InnoDB, но она платная, хотя и возможностей в ней больше.
Копирование файлов — самый быстрый способ перебросить базу данных целиком с одного сервера на другой.
2. Копирование через текстовые файлы
Для того, чтобы считать в бэкап данные из production-базы, необязательно дергать файлы. Можно выбрать данные запросом и сохранить их в текстовый файл. Для этого используется SQL-команда SELECT INTO OUTFILE и парная ей LOAD DATA INFILE. Выгрузка производится построчно (можно отобрать для сохранения только нужные строки, как в обычном SELECT). Структура таблиц нигде не указывается — об этом должен заботиться программист. Он также должен позаботиться о включении команд SELECT INTO OUTFILE в транзакцию, если это необходимо для обеспечения целостности данных. На практике SELECT INTO OUTFILE используется для частичного бэкапа очень больших таблиц, которые нельзя скопировать никаким другим образом.
В большинстве случаев намного более удобна созданная Игорем Романенко утилита mysqldump. Утилита mysqldump формирует файл, содержащий все SQL-команды, необходимые для полного восстановления БД на другом сервере. Отдельными опциями можно добиться совместимости этого файла с практически любой СУБД (не только MySQL), кроме того, существует возможность выгрузки данных в форматах CSV и XML. Для восстановления данных из таких форматов существует утилита mysqlimport.
Утилита mysqldump консольная. Существуют её надстройки и аналоги, позволяющие управлять бэкапом через веб-интерфейс, например, украинская тулза Sypex Dumper (их представитель zapimir есть на хабре).
Недостатки универсальных утилит бэкапа в текстовые файлы — это относительно невысокая скорость работы и отсутствие возможности делать инкрементные бэкапы.
3. Инкрементные бэкапы
Традиционно рекомендуют держать 10 бэкапов: по одному на каждый день недели, а также бэкапы двухнедельной, месячной и квартальной давности — это позволит достаточно глубоко откатиться в случае порчи каких-либо данных.
Храниться бэкапы должны точно не на том же диске, что и живая база, и не на том же сервере. На случай пожаров и прочих катаклизмов лучше всего арендовать пару юнитов в соседнем дата-центре.
Эти требования могут стать проблемой для больших баз. Прокачка бэкапа 100-гигабайтной базы по 100-мбитной сети займет часа три, на которые полностью забьет канал.
Частично решить эту проблему позволяют инкрементные бэкапы, когда полный бэкап делается, скажем, только по воскресеньям, а в остальные дни пишутся только данные, добавленные или измененные за прошедшие сутки. Сложность в том, как выявить эти самые «данные, изменившиеся за сутки».
Здесь практически вне конкуренции система Percona XtraBackup, которая содержит модифицированный движок InnoDB, анализирует двоичные логи MySQL и вытаскивает из них необходимую информацию. Почти такими же возможностями обладает платная InnoDB Hot Backup, упомянутая выше.
Общая проблема с любыми бэкапами в том, что они всегда отстают. В случае фатального сбоя основного сервера восстановить систему можно будет только с некоторым «откатом» по времени, что очень и очень разочарует её пользователей. Если в системе так или иначе были затронуты финансовые потоки, подобный «откат» может в прямом смысле влететь в копеечку.
4. Репликация
Репликация — это очень здорово, только использовать её нужно по назначению. Реплика — это полная копия базы, но это не резервная копия! Очевидно же, что если на мастере выполнить DROP TABLE или UPDATE users SET password=«Haha!», изменения будут тут же скопированы на слейв, и откатить их назад станет невозможно.
Репликацию можно совместить с бэкапом на уровне файлов базы, останавливая слейв, а не мастера.
Читайте также: