Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

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

Что такое дамп базы данных

Дамп (англ. dump – сбрасывать) – файл, включающий в себя содержимое памяти компьютера или базы данных. В нашем случае это файл с расширением .sql. Он содержит особые данные, благодаря которым можно легко воссоздать копию БД.

Копирование базы данных может быть полезно, когда нужно выполнить: 

  • Перенос данных на другой хостинг. Не нужно повторно создавать БД и вносить в нее все данные руками. Достаточно создать дамп и импортировать его в новый проект.
  • Резервное копирование. Отличный способ для экспериментов с веб-сайтом или сервером: вносите корректировки в базу данных и не бойтесь, что произойдет сбой. В случае неисправности всегда можно будет все восстановить из дампа.

Помимо этого, дамп может заполнить не только пустую базу, но и заменить содержимое ранее созданной. Давайте перейдем к практической части и посмотрим на наглядном примере, как все это можно организовать.

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

Создаем дамп базы данных MySQL

Существует несколько способов создания дампов: через консольное окно или с помощью phpMyAdmin. Рассмотрим последовательно каждый из методов, а также попробуем восстановить БД из дампа.

Способ 1: Консольное окно MySQL

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

Для подключения вы можете воспользоваться такими программами, как PuTTY и WinSCP – они распространяются в бесплатном доступе. Остановимся на первой утилите и посмотрим, как с ее помощью можно сделать дамп базы данных MySQL.

  1. Открываем официальный сайт Putty и загружаем оттуда последнюю версию программы.Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin
  2. Устанавливаем к себе на компьютер PuTTY и запускаем ее. Первым делом переходим в раздел «Connection» и находим там подраздел «Tunnels». В нем изменяем следующие параметры: указываем 336 в строке «Source port» и прописываем localhost:3306 в «Destination». В завершение нажимаем на кнопку «Add» — это действие позволит нам добавить созданный порт.Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin
  3. Переходим в раздел «Sessions» — там вводим свой адрес или домен, в нижней части жмем на «Open».Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin
  4. Вводим логин и пароль для подключения к БД.Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

Обратите внимание, что если на компьютере функционирует сервер с БД, то соединение через порт 3306 будет некорректно. В таких случаях рекомендуется использовать другие значения, например, 3307, 3308 и так далее.

Теперь мы можем переходить к удаленному администрированию БД: создадим дамп базы данных MySQL. Для этого введем в консоль следующий запрос:

mysqldump -uDataBase -pPASSWRD DataBase_NAME > FileName

  • -uDataBase —имя базы в формате типа -u[root]
  • -pPasswrd —пароль от базы в формате типа -p[123456]
  • DataBase_NAME — имя БД
  • FileName — название файла

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

mysqldump -u -p DataBase_NAME > FileNameToSave

Для понимания можете взглянуть на пример с использованием пользователя и пароля:

mysqldump -uAdmin -p123456789 WordPressDB > WordPressDump.sql

Таким образом будет создан файл WordPressDump.sql, содержащий в себе все нужные данные для точного копирования. Посмотрим, как этот файл импортировать в проект через консоль:

mysql -uUSER -pPASSWRD -f DataBase_NAME < FileNameToEnter

Аналогично подставляем свои данные в команду и в итоге получаем:

mysql -uWordPressDB -p123456789 -f WordPressDB < WordPeressDump.sql

Также при импорте мы можем указать кодировку — для этого достаточно добавить ключ default-character-set. В итоге код преобразуется:

mysql -uAdmin -p123456789 -f —default-character-set=cp1251 WordPressDB < WordPeressDump.sql

Вот такими несложными действиями можно сделать копирование через консольное окно. Теперь давайте «покопаемся» в phpMyAdmin и выполним в нем копирование БД.

Способ 2: Инструмент phpMyAdmin

PhpMyAdmin по умолчанию предустановлен на каждой CMS. Доступ к нему осуществляется через личный кабинет пользователя на хостинге либо через локальный веб-сервер на домашнем ПК.

Подключаемся к phpMyAdmin и экспортируем БД:

  1. Открываем личный кабинет хостинга, на котором установлен веб-ресурс, и переходим в phpMyAdmin. На Timeweb это выполняется через раздел «База данных MySQL». Если вы используете локальный сервер на OpenServer, то достаточно в панели задач кликнуть правой кнопкой мыши по его иконке, перейти в меню «Дополнительно» и выбрать «PhpMyAdmin».Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin
  2. Вводим логин и пароль, в результате чего попадаем в систему phpMyAdmin. В левой части выбираем БД для копирования и кликаем по ней левой кнопкой мыши.Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin
  3. Переходим в раздел «Экспорт» и выбираем метод экспорта. Первый минимизирован – получится обычная БД без особых настроек, второй разрешает вносить важные уточнения. Например, мы можем удалять таблицы, изменять кодировку, добавлять особые параметры формата и многое другое. Перед сохранением файла указываем его формат и только потом нажимаем на кнопку «Вперед».Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

В заключение стоит сказать, что дамп базы данных – это незаменимый файл, без которого не обходится ни один серверный переезд. Используйте его для переноса базы на хостинге или с локальной машины, а также для создания резервных копий. Удачи!

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

Как сделать дамп базы данных MySQL и PostgreSQL. Использование утилиты phpMyAdmin

Дамп базы данных — это сохранение информации из базы данных в текстовом SQL-файле. 

Web-сайты состоят из файлов и базы данных. Именно в базе данных хранится вся часто изменяемая информация сайта:

  • имена пользователей и их настройки;
  • товары для интернет-магазина;
  • статьи для новостного портала.

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

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

Файлы с дампами имеют тип .SQL. Их размеры могут достигать сотен мегабайт для больших нагруженных сайтов. 

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

Пример содержимого текстового файла с дампом базы данных. Видно, что файл состоит из отдельных команд языка SQL

Работа с дампом базы данных MySQL

MySQL — это один из самых популярных серверов управления базами данных, который активно используется для создания web-сайтов.

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

На виртуальном хостинге пользователь не имеет доступа на сервер и не может работать с командной строкой, поэтому для него остается вариант работы с дампами базы данных с использованием web-интерфейса, который либо встроен в панель управления хостинга, либо предоставляется утилитой phpMyAdmin.

Чтобы получить дамп базы данных MySQL на VPS/VDS и выделенном сервере, можно воспользоваться командной строкой сервера или специальным web-интерфейсом. 

Использование функций панели управления

На примере провайдера Reg.ru и панели ISPManager разберем, как сделать дамп базы данных.

1. Зайдите в меню “Базы данных”. 2. Нажмите на кнопку “Скачать”, чтобы скачать дамп базы данных на локальный компьютер. 

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

3. Чтобы импортировать дамп базы данных с локального компьютера на сервер, перейдите в раздел “Закачать” и нажмите на кнопку “Выберите файл”. Затем выберите файл с дампом на компьютере и запустите процесс закачки файла на сервер нажатием кнопки “Ok”.

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

Также имеется пункт меню “Локальный дамп”, который позволяет осуществить процедуру импорта дампа базы данных из предварительного закачанного на сервер файла. Достаточно просто найти и выбрать предварительно загруженный файл с дампом в одном из каталогов сайта:

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

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

Использование web-интерфейса PHPMyAdmin

Практически у всех провайдеров виртуального хостинга в панель управления хостингом включена утилита PHPMyAdmin. Она используется в качестве удобного web-интерфейса для работы с базой данных MySQL. Например, у провайдера Beget ссылка на запуск phpMyAdmin располагается в разделе управления базами данных:

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

В числе прочих функций в PHPMyAdmin встроен механизм получения дампа базы данных. Для этого следует воспользоваться пунктом меню “Экспорт”:

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

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

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

При просмотре этого файла в блокноте видно, что он состоит из отдельных команд SQL

Обратной операцией по отношению к экспорту дампа базы данных является восстановление данных из этого дампа. Для этого в PHPMyAdmin нужно воспользоваться меню “Импорт”:

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

Кнопкой “Choose file” выбирается нужный файл на локальном компьютере, после нажатия кнопки “Вперед” происходит процедура импорта.

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

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

К недостаткам использования PHPMyadmin можно отнести возможные проблемы при работе с дампами очень больших баз данных размерами в сотни мегабайт. Импорт большого дампа связан с ограничениями web-сервера и PHP на размер загружаемого файла, и может потребовать предварительно разделить его на несколько частей, что представляет собой кропотливую и трудоемкую работу.

Читайте также:  Виртуализация серверов: что такое, какая бывает, объяснение простыми словами

phpMyAdmin позволяет провести импорт дампа базы данных, предварительно упакованного в архив формата .zip или .tgz. Эту хитрость рекомендуется использовать, если нужно провести импорт очень большого файла размером в сотни мегабайт, который после упаковки в архив станет намного меньше.

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

К тому же эта утилита предоставляет подробную диагностику ошибок импорта в случае их возникновения.

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

Использование командной строки сервера

Для владельцев виртуальных и выделенных серверов доступна возможность работы с дампами базы данных из командной строки сервера. Этот метод лишен ограничений по размеру файла с дампом, работает быстро и надежно.

Для создания дампа пользователю необходимо знать название базы данных, имя пользователя и пароль. Сохранить данные в дамп можно утилитой mysqldump.

Синтаксис команды для создания дампа:

mysqldump -u username -p databasename > filename.sql

  • username — это имя пользователя базы данных;
  • databasename — название базы данных;
  • filename — имя создаваемого текстового файла с дампом. 

Желательно присваивать этому файлу тип .SQL. 

При запуске описанной команды будет запрошен пароль к базе данных для конкретного пользователя. После его ввода система сохранит все данные в файл с указанным названием.

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

Пример: после запуска процедуры создания дампа командой mysqldump, с помощью команды “ls” проверили, что в папке сайта появился файл dump.sql с дампом базы данных.

Для импорта дампа базы данных нужно будет воспользоваться командой “mysql” с синтаксисом:

mysql -u username -p databasename < filename.sql

  • username — это имя пользователя;
  • databasename — имя базы данных;
  • filename.sql — название файла с дампом.
  • Система запросит пароль к базе данных и выполнит процедуру импорта.
  • Пример: восстановление базы данных командой mysql из файла с дампом dump.sql

Если при работе процедуры импорта на экран не было выведено никаких сообщений об ошибке, то, значит, импорт состоялся успешно, и все данные из дампа уже находятся в базе данных. Эта операция называется “восстановить базу из дампа”.

Работа с дампом базы PostgreSQL

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

С Postgres работают на виртуальных и выделенных серверах.

В командной строке создать дамп базы PostgreSQL можно командой pg_dump с синтаксисом:

pg_dump -U username databasename > filename.sql

  • username — это имя пользователя
  • PostgreSQL, databasename — название базы данных,
  • filename — название файла, куда сохраняется дамп.
  1. Пример: после использования команды pg_dump дамп базы данных сохранен в файл dump.sql
  2. Для импорта дампа в базу данных используется команда psql с синтаксисом:

psql -U username databasename < filename.sql

  • username — это имя пользователя;
  • PosgreSQL, databasename — название базы данных;
  • filename — название файла, куда сохраняется дамп.
  • Пример: использование команды psql для импорта дампа в базу данных
  • Также создавать дампы в PostgreSQL можно через web-интерфейс phpPgAdmin (работает аналогично phpMyAdmin для MySQL)

Создание дампа делается через меню “Export”. На экране экспорта выбирается опции “Structure and Data” и “Download”, затем нажимается кнопка “Export”. На локальный компьютер сохраняется текстовый файл с дампом.

  1. Для обратной операции импорта данных из файла с дампом в базу данных в phpPgAdmin используется меню “SQL”. 
  2. На экране SQL выбирается файл с дампом с помощью кнопки “Choose File”, затем нажимается кнопка “Execute”. 
  3. После этого данные из файла с дампом импортируются в базу данных:

Выводы

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

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

Не нашли ответ на свой вопрос?
Задайте его экспертам! Ответ приходит очень быстро и прямо на ваш email.

Рейтинги хостинг-провайдеров по задачам сайта

  • Европа
  • Египет
  • Индия
  • Израиль
  • Испания
  • Италия
  • Казахстан
  • Канада
  • Китай
  • Колумбия
  • Корея
  • Кыргызстан
  • Латвия
  • Мексика
  • Нидерланды
  • Норвегия
  • Пакистан
  • Польша
  • Россия
  • Украина
  • Сингапур
  • Саудовская Аравия
  • Турция
  • США
  • Филиппины
  • Финляндия
  • Франция
  • Швейцария
  • Швеция
  • Эстония
  • ЮАР
  • Япония

Панель управления

От панели управления зависит ваше удобство в настройке хостингесайта.

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

  • Собственная
  • cPanel
  • ISP Manager
  • DirectAdmin
  • Parallels Plesk

Вид хостинга

Облачный хостинг — распределение нагрузки на несколько серверов, если сервер с вашим сайтом перегружен или не работает. Это гарантия того что пользователи в любом случае смогут видеть ваш сайт. Но это дорогая, более сложная опция, которую предоставляют далеко не все провайдеры.

Виртуальный хостинг — подходит для большинства проектов начального уровня с посещаемостью до 1000 человек в сутки. В таком хостинге мощность сервера делится между несколькими хостинговыми аккаунтами. Услуга проста в настройке даже для новичков.

VPS — подходит для более сложных проектов с достаточно большой нагрузкой и посещаемостью до 10000 человек в сутки. Здесь мощность сервера фиксированная для каждого виртуального сервера, при этом сложность настройки увеличивается.

Выделенный сервер — нужен для очень сложных и ресурсоемких проектов. Для вас выделяют отдельный сервер,мощность которого будете использовать только вы. Дорого и сложно настраивать.

Размещение и обслуживание вашего собственного сервера в дата-центре хостинга — это не очень популярная услуга и требуется в исключительных случаях.

CMS

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

Тип виртуализации

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

  С её помощью ресурсы физического сервера распределяются между виртуальными (VPS/VDS).

Основные виды: аппаратная (KVM), паравиртуализация, виртулизация на уровне ОС (OpenVZ).

  • OpenVZ
  • Virtuozzo
  • KVM
  • Hyper-V
  • Xen
  • VMware

Прочее

Абузоустойчивый хостинг — компании, которые разрешают размещать практически любой контент, даже запрещенный (спам, варез, дорвеи, порнографические материалы). Такие компании не удаляют контент вашего веб-сайта при первой же жалобе (“абузе”).

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

Безопасный хостинг — тот, где администрация постоянно обновляет ПО установленное на серверах, устанавливает базовую защиту от DDoS-атак, антивирус и файерволлы, блокирует взломанные сайты и помогает их «лечить».

Защита от DDOS — компании, которые предоставляют хостинг с защитой от DDoS-атак. Такие пакеты ощутимо дороже обычных, но они стоят своих денег, так как ваш сайт будет защищен от всех видов сетевых атак.

Бесплатный тест

Тестовый период — предоставляется хостером бесплатно на 7-30 дней, чтобы вы могли удостовериться в его качестве.

Moneyback — период на протяжении которого хостер обязуется вернуть деньги, если вам не понравится хостинг.

  • Тестовый период
  • Moneyback
  • VPS/VDS с тестовым периодом
  • Регистраторы доменов
  • Домен в подарок
  • SSL-сертификаты

Технологии и ОС

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

ASP.NET — платформа для разработки веб-приложений от Майкрософт.

ОС — операционная система, установленная на сервере хостинга. Мы рекомендуем размещать на серверах с Linux, если нет особых требований у разработчиков сайта.

  • Linux
  • Windows
  • ASP.net
  • MySQL
  • PHP
  • Java
  • Python
  • Node.js
  • Django

Утилита mysqldump и шпаргалка по параметрам

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

Утилита mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

Так же mysqldump имеет возможность развертывания баз данных из созданного sql-файла.

Создание дампа

Разберем пример простейшее использования, задампим базу данных «database» при помощи перенаправления потока в файл «database.sql»:

mysqldump -uroot -h82.82.82.82 -p database > database.sql

где:

  • -u или -–user=… — имя пользователя
  • -h или —host=… — удаленный хост (для локального хоста можно опустить этот параметр)
  • -p или —password — запросить пароль
  • database — имя базы данных
  • database.sql — файл для дампа

Для того чтобы сделать дамп несколько баз данных, необходимо использовать параметр —databases (или сокращенно -B), пример:

mysqldump -uroot -h82.82.82.82 -p -B database1 database2 database3 > databases.sql

А для того чтобы сделать дамп всех баз данных, необходимо использовать параметр —all-databases (или сокращенно -A), пример:

mysqldump -uroot -h82.82.82.82 -p -A > all-databases.sql

Развертывание дампа

Перенаправляем поток в обратную сторону и развертываем базу данных:

mysql -uroot -h82.82.82.82 -p database < database.sql

Читайте также:  Правильная настройка Mozilla Thunderbird: руководство по использованию(с картинками)

Или через mysql-console:

mysql> use database;
mysql> source database.sql

Ну, а если у нас gz-архив к примеру, то:

zcat database.sql.gz | mysql -uroot -h82.82.82.82 -p database

Пример использование некоторых параметров

Например, нам нужны данные с «продакшен версии базы» для «версии разработчика», то есть нам нужна «песочница». Выбираем не более 100 записей:

mysqldump -uroot -h82.82.82.82 -p —where=»true limit 100″ database > database.sql

Или нам нужна только структура, без данных:

mysqldump -uroot -h82.82.82.82 -p —no-data database > database.sql

Примеры навеяны постом Александра Макарова — http://rmcreative.ru/blog/post/ljogkiy-damp-mysql

Делаем дамп только триггеров, процедур и событий:

mysqldump —no-create-info —no-data —triggers —routines —events -uroot -p database | gzip > ~/database.sql.gz

Шпаргалка по параметрам

Приведу некоторые параметры, которые могут понадобится при работе с утилитой mysqldump.

—add-drop-databaseДобавляет оператор DROP DATABASE перед каждым оператором CREATE DATABASE.—add-drop-tableДобавляет оператор DROP TABLE перед каждым оператором CREATE TABLE.—add-locksДобавляет оператор LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).—all-databases, -AСохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.—allow-keywordsРазрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца.—comments, -iДанный параметр позволяет добавить в дамп дополнительную информацию, такую, как версия mysqldump, версия MySQL, имя хоста, на котором расположен сервер MySQL.—compactДанный параметр требует от mysqldump создать дамп, используя как можно более компактный формат. Параметр является противоположным —comments.—compatible=nameПараметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместо ключевого слова name можно использовать: «ansi», «mysql323», «mysql40», «postgresql», «oracle», «mssql», «db2», «maxdb», «no_key_options», «no_table_options», «no_field_options». Можно использовать несколько значений, разделив их запятыми.—complete-insert, -cИспользуется полная форма оператора INSERT (с именами столбцов).—create-optionsДобавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры.—databases, -BПараметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп.—delayedИспользовать команду INSERT DELAYED при вставке строк.—delete-master-logsНа главном сервере репликации автоматически удаляются бинарные логи (logbin) после того, как дамп был успешно создан при помощи mysqldump. Этот параметр автоматически включает параметр «—master-data».—disable-keys, -KДля каждой таблицы, окружает оператор INSERT выражениями /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; в выводе результата дампа. Это ускорит загрузку данных на сервер для таблиц типа MyISAM, так как индексы создаются после внесения всех данных.—extended-insert, -eИспользовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).—flush-logs, -FЗаписать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа.—force, -fПродолжать даже если в процессе создания дампа произошла ошибка.—hex-blobПараметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность «abc» будет заменена на 0x616263.—ignore-table=db_name.tbl_nameПозволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «—ignore-table», указывая по одной таблице в каждом из параметров.—insert-ignoreДобавляет ключевое слово IGNORE в оператор INSERT.—lock-all-tables, -xУказание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных.—lock-tables, -lУказание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп.—no-autocommitВключает все операторы INSERT, относящиеся к одной таблице, в одну транзакцию, что приводит к увеличению скорости загрузки данных.—no-create-db, -nПодавляет создание в дампе операторов CREATE DATABASE, которые автоматически добавляются при использовании параметров —databases и —all-databases.—no-data, -dПодавляет создание операторов INSERT в дампе, что может быть полезно при создании дампа структуры базы данных без самих данных.—optПараметр предназначен для оптимизации скорости резервирования данных и является сокращением, включающим следующие опции: —quick —add-drop-table —add-locks —create-options —disable-keys —extended-insert —lock-tables —set-charset. Начиная с MySQL 4.1, параметр —opt используется по умолчанию, т.е. все вышеперечисленные параметры включаются по умолчанию, даже если они не указываются. Для того чтобы исключить такое поведение, необходимо воспользоваться параметров —skip-opt—order-by-primaryУказание параметра приводит к тому. что каждая таблица сортируется по первичному ключу или первому уникальному индексу.—port, -PНомер TCP порта, используемого для подключения к хосту.—protocol={TCP|SOCKET|PIPE|MEMORY}Параметр позволяет задать протокол подключения к серверу.—quick, -qПозволяет начать формирование дампа, не дожидаясь полной загрузки данных с сервера и экономя тем самым память.—quote-names, -QПомещает имена баз данных, таблиц и столбцов в обратные апострофы `. Начиная с MySQL 4.1, данный параметр включен по умолчанию.—replaceДобавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3.—result-file=/path/to/file, -r /path/to/fileПараметр направляет дамп в файл file. Этот параметр особенно удобен в Windows, без использования командной строки. когда можно перенаправить результат в файл при помощи последовательностей > и >>.—routines, -RДанный параметр создает дамп хранимых процедур и функций. Доступен с MySQL 5.1.2.—single-transactionПараметр создает дамп в виде одной транзакции.—skip-commentsДанный параметр позволяет подавить вывод в дамп дополнительной информации.—socket=/path/to/socket, -S /path/to/socketФайл сокета для подсоединения к localhost.—tab=/path/, -T /path/При использовании этого параметра в каталоге path для каждой таблицы создаются два отдельных файла: tbl_name.sql, содержащий оператор CREATE TABLE, и tbl_name.txt, который содержит данные таблиц, разделенные символом табуляции. Формат данных может быть переопределен явно с помощью параметров —fields-xxx и —lines-xxx.—tablesПерекрывает действия параметра —databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц.—triggersСоздается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр —skip-triggers.—events, -EСоздается дамп событий. Смотрите MySQL Event Scheduler или встроенный диспетчер событий в MySQL.—tz-utcпри использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE='+00:00', который позволит обмениваться дампа в различных временных зонах.—verbose, -vРасширенный режим вывода. Вывод более детальной информации о работе программы.—version, -VВывести информацию о версии программы.—where='where-condition', -w 'where-condition'Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны.—xml, -XПредставляет дамп базы данных в виде XML.—first-slave, -xБлокирует все таблицы во всех базах данных.—debug=…, -#Отслеживать прохождение программы (для отладки).—helpВывести справочную информацию и выйти из программы.

Еще пару слов о бекапе в MySQL

mysqlhotcopy для MyISAM

Для быстрого резервирования БД с типом таблиц ISAM и MyISAM можно использовать «mysqlhotcopy», которая скопирует файлы *.frm, *.MYD и *.MYI:

# mysqlhotcopy db_name /path/to/dir

Для InnoDB не подойдет данный способ, потому что при этом типе не обязательно все файлы будут храниться в директории базы данных.

xtrabackup для InnoDB

Для InnoDB есть xtrabackup, рекомендую посмотреть!
UPD: XtraBackup — резервное копирование для innoDB

Бин-лог и репликации

Для репликации «mysqldump» не предназначена, для этого есть бин-лог (—log-bin):

# mysqlbinlog binlog.[0-9]* | mysql

Для полной репликации необходимо вести бин-лог с самого начала работы БД, то есть еще до создания структур и данных.

Резервирование данныс в MySQL 6.x

С версии MySQL 6.x доступен online-backup, вот слайд объясняющий нововведения:

Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

Миграция с помощью дампа и восстановления — база данных Azure для MySQL

  • 08/30/2021
  • Чтение занимает 9 мин
    • s
    • o
    • O

Область применения:
Отдельный сервер Базы данных Azure для MySQL
Гибкий сервер Базы данных Azure для MySQL

В этой статье описываются два распространенных способа архивации и восстановления баз данных в базе данных Azure для MySQL:

  • дамп и восстановление из командной строки (с помощью mysqldump);
  • дамп и восстановление с помощью PHPMyAdmin.

Дополнительные сведения и примеры использования переноса баз данных в Базу данных Azure для MySQL см. в руководстве по переносу баз данных. Это руководство содержит инструкции, которые позволят успешно спланировать и выполнить миграцию с MySQL на Azure.

Перед началом

Прежде чем приступить к выполнению этого руководства, необходимо выполнить следующее:

Совет

Если вы хотите перенести большие базы данных размером более 1 ТБ, попробуйте использовать такие средства сообщества, как mydumper/myloader, которые поддерживают параллельный экспорт и импорт. Узнайте, как перенести большие базы данных MySQL.

Распространенные варианты использования дампа и восстановления

Типичные варианты использования:

  • Переход с другого поставщика управляемых служб. Большинство поставщиков управляемых служб не предоставляют доступ к файлу физического хранилища из соображений безопасности, поэтому миграцию возможно выполнить только через логическое резервное копирование и восстановление.
  • Миграция из локальной среды или виртуальной машины. База данных Azure для MySQL не поддерживает восстановление физических резервных копий, что делает логическое резервное копирование и восстановление единственным возможным подходом.
  • Перемещение хранилища резервных копий из локально избыточного в геоизбыточное хранилище. База данных Azure для MySQL позволяет настроить локально избыточное или геоизбыточное хранилище для резервного копирования только при создании сервера. После подготовки сервера невозможно изменить тип избыточности для хранилища резервных копий. Чтобы переместить хранилище резервных копий из локально избыточного хранилища в геоизбыточное хранилище, единственным вариантом является дамп и восстановление.
  • Миграция из альтернативных подсистем хранилища в InnoDB. База данных Azure для MySQL поддерживает только подсистему хранилища InnoDB и, следственно, не поддерживает альтернативные подсистемы хранилища. Если таблицы настроены с помощью других подсистем хранилища, преобразуйте их в формат ядра InnoDB перед перемещением в базу данных Azure для MySQL.
    Например, если у вас есть WordPress или WebApp, использующее таблицы MyISAM, то перед восстановлением в базе данных Azure для MySQL вам сначала нужно преобразовать эти таблицы в формат InnoDB путем перемещения. Используйте предложение ENGINE=InnoDB, чтобы задать ядро, используемое при создании таблицы, а затем передайте данные в совместимую таблицу перед восстановлением.
    INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns

Читайте также:  Бэкап сайта: что такое и как грамотно его сделать

Важно!

  • Чтобы избежать проблем с совместимостью, должна использоваться одна версия MySQL в системах источника и назначения при дампе баз данных. Например, если вы используете существующий сервер MySQL Server версии 5.7, тогда вы должны выполнить перемещение в базу данных Azure для MySQL, настроенную на запуск версии 5.7. Команда mysql_upgrade не будет работать в базе данных Azure для MySQL и является неподдерживаемой.
  • Если вам необходимо обновить все версии MySQL, используйте сначала дамп или экспорт базы данных ранней версии, чтобы получить наиболее актуальную версию MySQL в собственной среде. Затем перед выполнением перемещения в базу данных Azure для MySQL запустите mysql_upgrade.

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

  • Используйте параметр exclude-triggers в mysqldump при выполнении дампа баз данных. Исключите триггеры из файлов дампа, чтобы избежать сбоев запуска команд триггера во время восстановления данных.
  • Используйте параметр single-transaction, чтобы задать режим изоляции транзакций REPEATABLE READ и отправлять на сервер инструкцию SQL START TRANSACTION перед созданием дампа данных. Формирование дампа нескольких таблиц в одной транзакции приведет к использованию дополнительных ресурсов хранилища во время восстановления. Параметры single-transaction и lock-tables являются взаимоисключающими, так как LOCK TABLES приводит к неявной фиксации всех ожидающих транзакций. Для формирования дампа больших таблиц используйте параметр single-transaction с параметром quick.
  • Используйте многострочный синтаксис extended-insert с несколькими списками VALUE. Это позволяет уменьшить размер файла дампа и ускорить операции вставки при перезагрузке файла.
  • Используйте параметр order-by-primary в mysqldump при выполнении дампа баз данных, чтобы данные были добавлены в сценарий в порядке первичных ключей.
  • Используйте параметр disable-keys в mysqldump при выполнении дампа данных, чтобы отключить ограничения для внешнего ключа перед загрузкой. Отключение проверок внешнего ключа обеспечивает значительный прирост производительности. Включите ограничения и проверьте данные после загрузки, чтобы обеспечить целостность данных.
  • Используйте секционированные таблицы, когда это необходимо.
  • Загружайте данные в параллельном режиме. Не выполняйте слишком много параллельных операций, так как можно достигнуть лимита ресурсов. Отслеживайте ресурсы с помощью метрик, доступных на портале Azure.
  • Используйте параметр defer-table-indexes в mysqlpump при выполнении дампа баз данных для создания индекса после загрузки данных таблиц.
  • Используйте параметр skip-definer в mysqlpump, чтобы опустить определитель и предложения SQL SECURITY из инструкций создания для представлений и хранимых процедур. При перезагрузке файла дампа создаются объекты, которые используют значения DEFINER и SQL SECURITY по умолчанию.
  • Скопируйте файлы резервной копии в большой двоичный объект Azure или хранилище Azure и выполните восстановление из них, что должно быть намного быстрее, чем восстановление через Интернет.

Создание базы данных в целевой службе базы данных Azure для сервера MySQL Server

Создайте пустую базу данных в целевой базе данных Azure для сервера MySQL, куда необходимо перенести данные. Для этого используйте такое средство, как MySQL Workbench или mysql.exe. База данных может иметь то же имя, что и база данных, содержащая данные дампа. Вы также можете создать базу данных с другим именем.

  • Чтобы подключиться, найдите сведения о подключении на странице Обзор базы данных Azure для MySQL.
  • Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

  • Добавьте сведения о подключении в MySQL Workbench.
  • Дамп базы данных MySQL и PostgreSQL: Как сделать, использование утилиты phpMyAdmin

Подготовка целевого сервера базы данных Azure для MySQL для быстрой загрузки данных

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

  • max_allowed_packet — задайте значение 1073741824 (т. е. 1 ГБ), чтобы предотвратить проблемы с переполнением из-за длинных строк.
  • slow_query_log — установите значение OFF, чтобы отключить журнал запросов с задержкой. Это позволит избежать накладных расходов, вызванных ведением журнала запросов с задержкой при загрузке данных.
  • query_store_capture_mode — задайте значение NONE, чтобы отключить хранилище запросов. Это позволит устранить издержки, вызванные операциями выборки в хранилище запросов.
  • innodb_buffer_pool_size — увеличьте масштаб сервера до 32 виртуальных ядер, оптимизированных для памяти, в разделе ценовой категории на портале во время миграции, чтобы увеличить innodb_buffer_pool_size. Innodb_buffer_pool_size можно увеличить только путем увеличения масштаба вычислений для сервера базы данных Azure для MySQL.
  • innodb_io_capacity и innodb_io_capacity_max — измените значение на 9000 в параметрах сервера на портале Azure, чтобы улучшить использование операций ввода-вывода для оптимизации скорости миграции.
  • innodb_write_io_threads и innodb_write_io_threads — измените значение на 4 в параметрах сервера на портале Azure, чтобы повысить скорость миграции.
  • Масштабирование уровня хранилища — число операций ввода-вывода в секунду для сервера базы данных Azure для MySQL увеличиваются с увеличением уровня хранилища. Для ускорения загрузки может потребоваться увеличить уровень хранилища, чтобы увеличить число подготовленных операций ввода-вывода в секунду. Помните, что масштаб хранилища можно только увеличивать, а не уменьшать.

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

Создание дампа и восстановление с помощью служебной программы mysqldump

Создание файла резервной копии из командной строки с помощью mysqldump

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

$ mysqldump —opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

Необходимо указать следующие параметры:

  • [uname] — имя пользователя базы данных;
  • [pass] — пароль для базы данных (обратите внимание, что между «-p» и паролем нет пробела);
  • [dbname] — имя базы данных;
  • [backupfile.sql] — имя файла резервной копии базы данных;
  • [—opt] — параметр mysqldump.

Например, чтобы создать резервную копию базы данных с именем testdb на сервере MySQL с именем пользователя testuser и без пароля и сохранить ее в файл testdb_backup.sql, используйте приведенную ниже команду. Команда создает резервную копию базы данных testdb в файле с именем testdb_backup.

sql, который содержит все инструкции SQL, необходимые для повторного создания базы данных.

Убедитесь, что имя пользователя testuser имеет по меньшей мере разрешения на инструкцию SELECT для таблиц в дампе, SHOW VIEW для представлений в дампе, TRIGGER для триггеров в дампе и LOCK TABLES, если не используется параметр —single-transaction.

GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'testuser'@'hostname' IDENTIFIED BY 'password';

Теперь запустите mysqldump, чтобы создать резервную копию базы данных testdb.

$ mysqldump -u root -p testdb > testdb_backup.sql

Чтобы выбрать для создания резервной копии конкретные таблицы в базе данных, укажите имена этих таблиц в виде списка, разделенного пробелами. Например, чтобы создать резервную копию только для таблиц table1 и table2 из базы данных testdb, используйте этот пример:

$ mysqldump -u root -p testdb table1 table2 > testdb_tables_backup.sql

Чтобы создать резервную копию сразу нескольких баз данных, используйте параметр —databases и укажите имена этих баз данных в виде списка, разделенного пробелами.

$ mysqldump -u root -p —databases testdb1 testdb3 testdb5 > testdb135_backup.sql

Восстановление базы данных MySQL с помощью командной строки или MySQL Workbench

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

mysql -h [hostname] -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

В этом примере восстановите данные в созданную базу данных в целевой базе данных Azure для сервера MySQL Server.

Ниже приведен пример использования команды mysql для отдельного сервера.

$ mysql -h mydemoserver.mysql.database.azure.com -u myadmin@mydemoserver -p testdb < testdb_backup.sql

Ниже приведен пример использования команды mysql для гибкого сервера.

$ mysql -h mydemoserver.mysql.database.azure.com -u myadmin -p testdb < testdb_backup.sql

дамп и восстановление с помощью PHPMyAdmin

Выполните указанные ниже действия, чтобы создать дамп базы данных и восстановить ее, используя PHPMyadmin.

Примечание

Для отдельного сервера имя пользователя должно иметь формат «username@servername», а для гибкого сервера можно просто использовать формат username. Если использовать «username@servername» для гибкого сервера, то произойдет сбой подключения.

Экспорт с помощью PHPMyadmin

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

  1. Откройте phpMyAdmin.
  2. Выберите свою базу данных. Щелкните имя базы данных в списке слева.
  3. Щелкните ссылку Экспорт. Появится страница для просмотра дампа базы данных.
  4. В области экспорта щелкните ссылку Выбрать все, чтобы выбрать все таблицы в базе данных.
  5. В области параметров SQL щелкните необходимые параметры.
  6. Щелкните параметр Сохранить как файл, выберите соответствующий вариант сжатия, а затем нажмите кнопку Перейти. Появится диалоговое окно, предлагающее сохранить файл локально.

Импорт с помощью PHPMyAdmin

Импорт базы данных выполняется подобно экспорту. Выполните следующие действия:

  1. Откройте phpMyAdmin.
  2. На странице настройки phpMyAdmin щелкните Добавить, чтобы добавить базу данных Azure для сервера MySQL Server. Укажите сведения о подключении и учетные данные.
  3. Создайте базу данных, присвоив ей соответствующее имя. Затем выберите эту базу данных, щелкнув ее имя в списке в левой части экрана. Чтобы перезаписать существующую базу данных, щелкните имя базы данных, установите все флажки рядом с именами таблиц, а затем выберите Удалить, чтобы удалить существующие таблицы.
  4. Щелкните ссылку SQL, чтобы отобразилась страница, на которой можно ввести команды SQL или передать файл SQL.
  5. Нажмите кнопку обзора, чтобы найти файл базы данных.
  6. Нажмите кнопку Перейти, чтобы экспортировать резервную копию, выполнить команды SQL и повторно создать базу данных.

Известные проблемы

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

Дальнейшие действия

Ссылка на основную публикацию