Содержание

Резервное копирование mysql базы данных

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

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

Содержание статьи:

Резервное копирование базы данных

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

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

$ mysqldump опции имя_базы [имя_таблицы] > файл.sql

По умолчанию утилита будет выводить все в стандартный вывод, поэтому нам нужно перенаправить эти данные в файл, что мы и делаем с помощью оператора «>». Опции указывают параметры аутентификации и работы, а имя базы и таблицы — данные которые нужно экспортировать. Теперь рассмотрим кратко опции, которые будем использовать:

  • -A — копировать все таблицы из всех баз данных;
  • -i — записывать дополнительную информацию в комментариях;
  • -c — использовать имена колонок для инструкции INSERT;
  • -a — включать все возможные опции в инструкцию CREATE TABLE;
  • -k — отключает первичные ключи на время копирования;
  • -e — использовать многострочный вариант инструкции INSERT;
  • -f — продолжить даже после ошибки;
  • -h — имя хоста, на котором расположен сервер баз данных, по умолчанию localhost;
  • -n — не писать инструкции для создания базы данных;
  • -t — не писать инструкции для создания таблиц;
  • -d — не записывать данные таблиц, а только их структуру;
  • -p — пароль базы данных;
  • -P — порт сервера баз данных;
  • -Q — брать все имена таблиц, баз данных, полей в кавычки;
  • -X — использовать синтаксис XML вместо SQL;
  • -u — пользователь, от имени которого нужно подключаться к базе данных.

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

mysqldump -u имя_пользователя -p имя_базы > data-dump.sql

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

head -n 5 data-dump.sql

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

mysqldump -h хост -P порт -u имя_пользователя -p имя_базы > data-dump.sql

Копирование таблицы mysql может быть выполнено простым добавлением имени таблицы в конец строки:

mysqldump -u имя_пользователя -p имя_базы имя_таблицы > data-dump.sql

Также, чтобы выполнять автоматическое резервное копирование базы mysql может понадобиться сразу задать пароль, для этого указывайте его сразу после опции -p, без пробела:

mysqldump -u имя_пользователя -pпароль имя_базы > data-dump.sql

Мы можем делать бэкап вручную время от времени, но это не совсем удобно, поскольку есть другие важные дела. Поэтому используем планировщик cron, чтобы автоматизировать процесс. Тут есть два способа более простой, и более сложный, но точный. Допустим, нам нужно создавать резервную копию каждый день, тогда просто создайте скрипт в папке /etc/cron.daily/ со следующим содержимым:

sudo vi /etc/cron.daily/mysql-backup

!/bin/bash
/usr/bin/mysqldump -u имя_пользователя -pпароль имя_базы > /backups/mysql-dump.sql

Папку /backups/mysql-dump.sql нужно заменить на свою папку для резервных копий. Осталось дать скрипту права на выполнение:

chmod ugo+x /etc/cron.daily/mysql-backup

Дальше планировщик будет запускать его каждый день и делать копирование базы mysql. Но есть еще один, более точный способ, который позволяет указать точное время выполнения. Сначала выполните команду:

sudo crontab -e

Добавьте в открывшейся файл такую строку и сохраните изменения:

30 2 * * * /usr/bin/mysqldump -u имя_пользователя -pпароль имя_базы > /backups/mysql-dump.sql

Команда будет выполняться каждый день, в 2:30, это удобно, поскольку ночью обычно меньше нагрузка на сервер. Как вы поняли, первое число — это минуты, второе — часы, третье день, дальше неделя и месяц. Звездочка значит, что этот параметр не имеет значения.

Восстановление из резервной копии

Восстановить резервную копию mysql или mariadb из существующего SQL файла тоже очень просто. Поскольку использовался синтаксис sql мы просто можем выполнить все команды с помощью стандартного клиента mysql.

Сначала нужно создать новую базу данных. Для этого авторизуйтесь на mysql сервере с правами суперпльзователя:

mysql -u root -p

Затем создайте новую базу данных, например, с именем new_database, если база данных уже существует, то этого делать не нужно:

mysql> CREATE DATABASE new_database;

Дальше закройте оболочку, нажав сочетание клавиш Ctrl+Q и импортируйте данные из файла командой:

mysql -u пользователь -p база_данных < data-dump.sql

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

Выводы

Теперь вы знаете как выполняется копирование базы данных mysql, а также как восстановить скопированную информацию. Мы рассмотрели все возможные опции mysqldump чтобы вы могли настроить утилиту так, как вам нужно. Резервное копирование базы данных mysql это очень важный момент и в определенной ситуации может сохранить много времени, поэтому обязательно настройте у себя на сервере!

losst.ru

Бэкап всех баз mysql в отдельные файлы

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

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

Самый простой способ сделать бэкап всех базы с помощью mysqldump следующий:

# /usr/bin/mysqldump -uroot -hlocalhost -p'password' --all-databases | /usr/bin/gzip -c > /backup/mysql/`date "+%Y-%m-%d"`.gz

На выходе будет один сжатый файл с именем вида 2018-09-27.gz, в котором будет дамп всех mysql баз сервера, в том числе служебных таблиц. Работать с таким файлом потом очень неудобно. Это подходит только для случая, когда вы на таком же сервере будете восстанавливать все базы данных.

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

# mysql -u root -p --one-database destdbname < alldatabases.sql

У меня она не заработала. Консольная mysql выдавала ошибку. Я не стал подробно разбираться. Затем попробовал с помощью sed выдернуть информацию только о нужной базе, но сходу не получилось, так как дамп был большой. Просто просмотреть его была проблема, а тренироваться на тестовых дампах — тратить лишнее время. Проще было пойти на исходный сервер и вытащить только нужную базу данных.

Заодно заменил скрипт бэкапа на следующий:

for i in `mysql -uroot -p'password' -e'show databases;' | grep -v information_schema | grep -v Database`; 
    do 
	/usr/bin/mysqldump -uroot -p'password' $i | /usr/bin/gzip -c > /backup/mysql/`date +%Y-%m-%d`-$i.sql.gz;
    done

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

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

Онлайн курс Основы сетевых технологий

Теоретический курс с самыми базовыми знаниями по сетям. Курс подходит и начинающим, и людям с опытом. Практикующим системным администраторам курс поможет упорядочить знания и восполнить пробелы. А те, кто только входит в профессию, получат на курсе базовые знания и навыки, без воды и избыточной теории. После обучения вы сможете ответить на вопросы:
  • На каком уровне модели OSI могут работать коммутаторы;
  • Как лучше организовать работу сети организации с множеством отделов;
  • Для чего и как использовать технологию VLAN;
  • Для чего сервера стоит выносить в DMZ;
  • Как организовать объединение филиалов и удаленный доступ сотрудников по vpn;
  • и многое другое.
Уже знаете ответы на вопросы выше? Или сомневаетесь? Попробуйте пройти тест по основам сетевых технологий. Всего 53 вопроса, в один цикл теста входит 10 вопросов в случайном порядке. Поэтому тест можно проходить несколько раз без потери интереса. Бесплатно и без регистрации. Все подробности на странице .
Помогла статья? Есть возможность отблагодарить автора

serveradmin.ru

Как сделать резервную копию базы/таблицы в MySQL

В данной статье мы рассмотрим примеры того, как можно сделать резервную копию (бэкап, backup) базы данных MySQL (или же определенной таблицы из этой базы).

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

Содержимое статьи:

Бэкап БАЗЫ (БАЗ) данных

Для создания резервных копий баз данных MySQL из терминала Linux, существует специальная утилита mysqldump, которая устанавливается вместе с сервером. Ниже рассмотрим несколько различных примеров, используя которые можно делать резервные копии как целых баз, так и необходимых таблиц в конкретной базе.

Создаем резервную копию ОДНОЙ базы

mysqldump -u root -p database_name > database_name_backup.sql

-u root — аргумент, означающий, что мы будем подключаться к MySQL серверу под учетной записью root (может быть любая учетная запись, имеющая необходимые права на нужную таблицу).
-p — аргумент, означающий, что необходимо ввести пароль для авторизации (т.е. доступ для данного пользователя без пароля — не разрешен). В случае, когда пароль не требуется, данный аргумент можно упустить.
database_name — это имя базы данных, резервную копию которой мы делаем.
database_name_backup.sql — это название бекапа, который будет создан. Создается он в текущем каталоге из которого вы запускаете данную команду. Если вам необходимо сохранить резервную копию в какой-либо определенный каталог, то можно сразу указать путь до этого каталога, написав вместо database_name_backup.sql, /tmp/database_name_backup.sql. Таком образом, резервная копия будет создана в каталоге /tmp

Создаем резервную копию НЕСКОЛЬКИХ баз

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

mysqldump -u root -p --databases database_name_1 database_name_2 database_name_3 > databases_backup.sql

--databases — аргумент, указывающий, что далее будут перечислены базы данных, резервные копии которых мы хотим сделать.
database_name_1 database_name_2 database_name_3 — имена баз данных, резервные копии которых мы хотим сделать. Разделяются пробелом.

Создаем резервную копию ВСЕХ баз

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

mysqldump -u root -p --all-databases > databases_backup.sql

--all-databases, аргумент, указывающий, что необходимо сделать резервную копию всех доступных баз данных.

Если вы получаете ошибку «mysqldump: 1044 Access denied when using LOCK TABLES», то скорей всего вы пытаетесь делать резервную копию не под учетной записью root, а под какой то другой, у которой недостаточно прав на системные базы данных, вроде sys и mysql, либо другие. Поэтому, необходимо выдать нужные права пользователю на все базы, подробней об этом можно прочитать в данной статье: Ошибка: mysqldump: 1044 Access denied when using LOCK TABLES, либо делать резервные копии с помощью учетной записи root.

Бэкап ТАБЛИЦЫ (ТАБЛИЦ) из определенной базы данных

Создаем резервную копию ОДНОЙ таблицы из базы

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

mysqldump -u root -p database_name table_name > table_name_backup.sql

table_name — это имя таблицы, резервную копию которой мы хотим сделать и которая находится в базе данных database_name.

Создаем резервную копию НЕСКОЛЬКИХ таблиц из базы

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

mysqldump -u root -p database_name table_name_1 table_name_2 table_name_3 > tables_backup.sql

table_name_1 table_name_2 table_name_3 — это названия таблиц, резервные копии которых мы хотим сделать. В нашем примере данные таблицы находятся в базе данных database_name.



sysadmin.ru

Средства создания горячих BackUp`ов MySQL / Habr

Доброго времени суток. Недавно я задался вопросом о том, как делать горячие BackUp`ы MySQL-серверов — ниже компиляция из прочитанного. Заранее хочу сказать, что данный пост является скорее большой заметкой, чем полноценной статьёй. Я намеренно уклоняюсь от описания синтаксиса — на эту тему уже немало написано — я же ставил перед собой другую цель — составить краткий обзор основных методов с характерными особенностями:

1. C помощью утилиты mysqldump. Данная программка крайне популярна среди пользователей веб-хостингов. Читая содержимое таблиц, она создаёт файл с SQL-инструкциями для последующего заполнения. Но, как правило, при использовании люди забывают про три ключевых момента:
  • Если не использовать блокировку таблиц, вполне можно получить нарушение логических связей между содержимым таблиц(если в процессе создания копии кто-то решит оставить запись в базе). Здесь косвенно может помочь накатывание части bin-log`a после восстановления из дампа. Так что если по каким-то причинам не блокируете таблицы — применяйте ключ —flush-log — при его использовании старый лог будет закрыт и начат новый. Если кто-то что-то запишет в процессе создания бэкапа — это отразится в начале журнала и вы без проблем перенесёте это изменение в базу. Я бы советовал после окончания бэкапа так же выполнить mysqladmin -flush-logs и положить в бэкап помимо dump-файла предпоследний бинарный журнал.
  • При использовании ключа —lock-tables все таблицы получают блокировку записи, запросы встают в очередь. Это может привести к таймаутам на стороне клиента.
  • Стоит так же иметь ввиду, что подъём(как и создание дампа) большой базы, сохраненной таким образом может изрядно затянуться — в первом случае вы сгребаете из базы все записи, а при обратном варианте — скармливаете их ей. Тем не менее, это один из немногих способов сбэкапить базу из консоли, не имея root-доступа.

Восстановление: путём скармливания dump-файла утилите mysql через STDIN.

2. С помощью утилиты mysqlhotcopy. Еще одно средство из штатного набора MySQL. Идея такова: база ставится на блокировку, после чего средствами cp или scp её файлы копируются в другое место.

  • В отличие от предыдущего варианта сохраняются именно табличные файлы, а не набор инструкций по воссозданию базы, то есть скорость ограничивается лишь операционной системой и вашими железками.
  • По моему разумению — вполне подойдёт для бэкапа больших баз.
  • Работает только с MyISAM и ARCHIVE-таблицами.
  • Выполняется только с сервера на котором лежит база, при условии наличия прав к файлам с таблицами MySQL.

Восстановление: путём копирования сохраненных файлов в каталог данных MySQL.

3. С помощью LVM.
LVM — дополнительный слой между файловой системой и самим жестким диском. Одной из примечательных особенностей LVM является возможность снять на лету образ с тома. Схема действий будет следующей: заблокировать все таблицы базы, снять snapshot с тома, разблокировать таблицы.

  • Данный метод подразумевает предварительный FLUSH с блокировкой всех таблиц(лучше скриптик написать для этих целей).
  • Для применения этого метода необходимо, чтобы данные MySQL(для Linux они скорее всего будут храниться в директории /var/lib/mysql) находились на LVM-томе(желательно отдельном, дабы не бэкапить лишнее).
  • Учитывая, что мы говорим о горячем бэкапе — если вы собираетесь применять данный метод — решение о размещении лучше принять на этапе конфигурации сервера.

Восстановление: путём копирования сохраненных с образа файлов в каталог данных MySQL.

4. С помощью репликации. Несмотря на то, что этот вариант многие считают геморроем, мне такой способ резервирования кажется самым правильным. Логика такого подхода заключается в постоянной синхронизации основного(master) сервера с вторичным(slave). Подробней о репликации можно почитать здесь.

  • Требуется конфигурация отдельного MySQL-сервера. Причем желательно — на автономном железе.
  • Остановка slave-сервера не сыграет никакой роли на master`е — можно делать «холодный» бэкап.
  • В случае падения master`a можно в кратчайшие сроки(было бы разумно автоматизировать этот процесс) перевести всю нагрузку на slave, а после восстановления отсинхронизировать с ним master и вернуть всё на прежние места.
  • Slave может стать по совместительству площадкой для хранения бэкапов.
  • Важно! Существование реплики не освобождает вас от создания бэкапов. Выполнение какого-нибудь DROP’а коснётся обоих серверов!

Восстановление: вывод slave-сервера на место master`a, либо восстановление одним из вышеуказанных методов(в зависимости от выбранного).

Итого: Как видно, у каждого метода есть свои плюсы и минусы: вряд ли есть смысл бэкапить маленький форум с применением реплик, а базы, где счет идёт на гигабайты вряд ли будет удобно поднимать из файлов, сделанных mysqldump`ом — каждый метод хорош в определенных условиях.

На этом этом я заканчиваю своё повествование, надеюсь оно будет вам полезно. Спасибо за внимание и до новых встреч в эфире. 🙂

habr.com

MySQL backup базы данных: описание нескольких разных способов

Проводим back-up в системе MySQL

От автора: база данных без резервного копирования или, попросту говоря, бэкапа подобна средневековому замку без рыцарей — в любой момент кто-то или что-то может нарушить ее целостность. Практика показывает, что не существует идеальных БД: они постоянно «падают», перегружаются и начинают работать некорректно. Чтобы максимально обезопасить себя и свой проект, веб-мастера создают MySQL backup copy. Это файл, который хранит данные обо всех внесенных изменениях в конкретный момент времени. Сегодня расскажем, как делать это грамотно. Причем, не единственным способом.

Разные подходы

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

Просто скопируйте

Это самый простой способ, который может предложить веб-разработка, и самый примитивный. Как вы поняли, можно просто скопировать все файлы и сохранить их там, где это будет удобно. Необходимо перейти в папку хранения баз данных. Она находится по пути библиотек lib, mysql/db/. Далее выберите файл с именем базы, для которой нужно создать бэкап. Естественно, что для такого метода необходима полная остановка сервера. Если этого не сделать, вероятны два неблагоприятных варианта развития событий:

искажение данных при копировании их с массивной базы, которая активно используется;

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

Проводим back-up в системе MySQL

Как создать сайт самому?

Какие технологии и знания необходимы сегодня, чтобы создавать сайты самостоятельно? Узнайте на интенсиве!

Зарегистрироваться

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

Проводим back-up в системе MySQL

Но и для такого случая программисты придумали трюк. Как вы делаете снимок экрана на своем смартфоне, также можно провести снапшот файловой системы, на которой размещена база. Это не настоящее резервное копирование, а скорее моментальное фото, которое впоследствии даст представление о положении вещей в конкретное время. Не только «сиквел», но и другие языки программирования поддерживают снэпшоты. Можно определить последовательность действий.

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

Слив кэша файловой системы.

«Фото» момента в системе.

Разблокировка таблиц.

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

Если такой способ кажется вам наиболее адекватным, то пора задуматься о системе, которая будет поддерживать его напрямую. К ним относится ZFS. Даже если вы его не используете, но на машину установлен Logical Volume Manager, то снепшоты в MySQL так же легко доступны. Существует и программное обеспечение для таких снимков, которое доступно на Linux-осях. Разработчики активно используют Hot Copy, но он тоже не является панацеей для всех случаев.

Копия из текста

Можно проводить бэкапы и другим путем. Если речь идет о базах класса production, то изымать файлы из нормальной работы совершенно необязательно благодаря специфическим SQL-командам, таким как SELECT INTO OUTFILE и LOAD DATA INFILE. Можно провести селективную выгрузку, а не копировать весь файл целиком. Выбору подлежит каждая отдельная строка таблицы.

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

Через интерфейс веб-приложения

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

Приложение написано на языке «препроцессора», и его основной функцией является как раз бэкап в MySQL. Функционал также рассчитан и на то, чтобы восстановить БД из уже готовой копии, но практика показывает, что лучше использовать Sypex Dumper для двух этапов: копирования и восстановления. Основным преимуществом этого ПО называют невероятную оптимизированность. Веб-приложение позволяет работать не только с простыми проектами, но с базами, размер которых доходит до тысячи мегабайт.

Основной задачей при создании была разработка такой утилиты, которая бы решала проблемы копирования там, где не справлялась популярная phpMyAdmin. Разработчику не понравилось то, что она не решает проблему копирования массивных БД. Изначально инструмент входил в систему управления контентом, которую автор создал для своих проектов. Качественным преимуществом стала упрощенная работа с русскими кодировками, решалось большинство проблем, которые с ними возникают. Последние версии дампера были оснащены возможностями технологии AJAX, а сама программа была переведена на большинство языков.

В основном, преимуществами Sypex Dumper можно назвать:

генерация бекапа без сторонних утилит. Все происходит благодаря одному только PHP;

Проводим back-up в системе MySQL

Как создать сайт самому?

Какие технологии и знания необходимы сегодня, чтобы создавать сайты самостоятельно? Узнайте на интенсиве!

Зарегистрироваться

взаимодействие с БД абсолютно любого размера. Даже в 2018 году актуальны разработки, которые компания совершила в 2013-ом;

быстродействие. В сравнении с phpMyAdmin, Sypex Dumper — пуля;

кроссплатформенность;

смарт-Shift в плане работы с кодировкой;

невысокие системные требования к машине, на которой запускается утилита.

Из репликации

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

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

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

Детально о дампе

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

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

Чтобы создать дамп, необходимо обратиться к консоли системы управления базами данных, в нашем случае — MySQL. Для этого заготовлена специальная команда:

mysqldump -uuser -ppass db_name > file_to_save

mysqldump -uuser -ppass db_name > file_to_save

Важно, чтобы пользователь имел достаточные права для слива информации. В значении «db_name» необходимо прописывать название БД, с которой вы будете работать. Последняя переменная — это предварительно созданный файл, в котором будет храниться дамп.

Если все пройдет хорошо, создастся файл с расширением .sql, в котором и будет сохранен дамп. На самом деле, за простотой этого способа скрывается универсальность и доступность. Особенно распространен метод для пользователей Linux. Также он хорош тем, что подходит новичкам: без лишних движений пользователь получает готовый дамп.

Можно создавать дампы с использованием phpMyAdmin. Хотя действия исключительно с помощью PHP показали, насколько более эффективен отказ от стороннего программного обеспечения. Для начала, необходимо войти и выбрать ту базу, с которой будем копировать информацию. Далее проходим процедуру авторизации: нужно, чтобы пользователь обладал необходимым кругом полномочий. После того как в левом баре появился список информации различного рода, снова выбираем нашу БД. Жмите на экспорт и сливайте дамп. Чтобы при восстановлении не возникало проблем, пропишите параметр DROP TABLE/VIEW/PROCEDURE/FUNCTION/EVENT. Теперь у вас есть zip-архив, из которого можно провести восстановление. Как только база данных упадет, вы сможете восстановить ее при помощи данного бэкапа.

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

При помощи трехбуквенного кода можно совершить задуманное: Q, c и e. Чтобы реализовать эти параметры, необходимо лишь включить их в запрос для создания данных:

mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

Например, «Q» ставит неймы в инвертированные кавычки, «с» проводит полную вставку, учитывая неймы колонок, а «e» — это расширение вставки. Таким образом, все оптимизируется, а объем файлов уменьшается.

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

Проводим back-up в системе MySQL

Как создать сайт самому?

Какие технологии и знания необходимы сегодня, чтобы создавать сайты самостоятельно? Узнайте на интенсиве!

Зарегистрироваться

webformyself.com

MySQL шпаргалки / Habr

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

Сегодня я решил опубликовать свои шпаргалки, на самые часто встречающиеся ошибки при работе с MySQL.

Работа с бекапами

Делаем бекап
mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

Создаём структуру базы без данных
mysqldump --no-data - u USER -pPASSWORD DATABASE > /path/to/file/schema.sql

Если нужно сделать дамп только одной или нескольких таблиц
mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql

Создаём бекап и сразу его архивируем
mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz

Создание бекапа с указанием его даты
mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`

Заливаем бекап в базу данных
mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql

Заливаем архив бекапа в базу
gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE
или так
zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

Создаём новую базу данных
mysqladmin -u USER -pPASSWORD create NEWDATABASE

Удобно использовать бекап с дополнительными опциями -Q -c -e, т.е.
mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql, где:

  • -Q оборачивает имена обратными кавычками
  • -c делает полную вставку, включая имена колонок
  • -e делает расширенную вставку. Итоговый файл получается меньше и делается он чуть быстрее

Для просмотра списка баз данных можно использовать команду:
mysqlshow -u USER -pPASSWORD

А так же можно посмотреть список таблиц базы:
mysqlshow -u USER -pPASSWORD DATABASE

Для таблиц InnoDB надо добавлять —single-transaction, это гарантирует целостность данных бекапа.
Для таблиц MyISAN это не актуально, ибо они не поддерживают транзакционность.

Подробнее

Общие факты

  • Полезно под каждую базу на боевом сервере создавать своего пользователя
  • Кодировка базы может быть любой, если она UTF8
  • В большинстве случаев лучше использовать движок InnoDB
  • В php лучше забыть про сильно устаревшее расширение mysql и по-возможности использовать pdo или mysqli
  • Новую копию MySQL всегда можно настроить и оптимизировать
  • Без особой нужды не стоит открывать MySQL наружу. Вместо этого можно сделать проброс портов
    ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST
Работа с данными
Числа

  • На 32-битных системах практически нет смысла ставить для типа INTEGER свойство UNSIGNED, так как такие большие числа в php не поддерживаются.
    На 64-битных системах, php поддерживает большие числа, вплоть до MySQL BIGINT со знаком.
  • Связанные таблицы («Foreign keys») должны иметь полное сходство по структуре ключей. Т.е. если у нас на одной таблице для поля указано «INTEGER UNSIGNED DEFAULT 0 NOT NULL» то и на другой должно быть указано аналогично
  • Для хранения булевых значений, нужно использовать TINYINT(1)
  • А деньги лучше хранить в DECIMAL(10, 2), где первое число обозначает количество всех знаков, включая запятую, а второе — количество знаков после запятой. Итого, у нас получится что DECIMAL(10,2) может сохранить 9999999,99
Строки

  • В старых версиях (до 5.0.3) VARCHAR была ограничена 255 символами, но сейчас можно указывать до 65535 символов
  • Помните, что тип TEXT ограничен только 64 килобитами, поэтому что бы сохранять «Войну и Мир» пользуйтесь «LONGTEXT»
  • Самая правильная кодировка для вашей БД UTF8
Даты

Не забывайте, что
  • DATE, TIME, DATETIME — выводятся в виде строк, поэтому поиск и сравнение дат происходит через преобразование
  • TIMESTAMP — хранится в виде UNIX_TIMESTAMP, и можно указать автоматически обновлять колонку
  • Сравнивая типы данных DATETIME и TIMESTAMP, не забывайте делать преобразование типов, например:
    SELECT * FROM table WHERE `datetime` = DATE(`timestamp`)
Перечисления

  • Для перечислений правильно использовать тип ENUM
  • Правильно пишется так: ENUM(‘мама’, ‘мыла’, ‘раму’)
  • Можно ставить значение по-умолчанию, как и для любой строки
  • В базе поле с перечислением хранится как число, поэтому скорость работы — потрясающе высокая
  • Количество перечислений ~ 65 тысяч

dev.mysql.com/doc/refman/4.1/en/storage-requirements.html
help.scibit.com/mascon/masconMySQL_Field_Types.html

Отладка
  • Если запросы тормозят, то можно включить лог для медленных запросов в /etc/mysql/my.cnf
  • А потом оптимизировать запросы через EXPLAIN
  • И наблюдать за запросами удобно через программу mytop

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

habr.com

Резервное копирование MySQL. Бэкап MySQL. Сделать бэкап MySQL

Резервное копирование и восстановление баз MySQL

В данном документе подробно рассматриваются принципы и процедуры, которые необходимо соблюдать для реализации стратегии резервного копирования MySQL на уровне предприятия при использовании агента Bacula Enterprise Edition для MySQL.

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

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

Резервное копирование MySQL доступно для платформ Linux 32 и 64 бита (платформы Debian, Ubuntu, CentOS и др.), и поддерживает MySQL 4.0.x, 4.1.x, 5.0.x, 5.5.x, 5.6.x.

Как сделать бэкап MySQL: дамп или бинарный лог?

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

 

Возможности автоматического бэкапа MySQLДамп файлБинарный лог
Возможность восстановления единичного объекта MySQL (таблица, схема…)Да[1]Нет
Скорость резервного копирования MySQLМедленноБыстро
Скорость восстановления MySQLОчень медленноБыстро
Размер бэкапа базы MySQLМаленькийБольшой
Возможность восстановления MySQL до контрольной точкиДаДа
Поддержка инкрементального/дифференциального бэкапа MySQLДаДа
Онлайн бэкап MySQLДаДа
СогласованностьДаДа
Восстановление MySQL до предыдущей основной версииДа[2]Нет
Возможность восстановить MySQL до новой основной версииДаНет

[1] Чтобы восстановить единичный объект MySQL, необходимо отредактировать дамп файл.

[2] Чтобы восстановить базу MySQL до предыдущей версии, Вам, возможно, потребуется отредактировать SQL файл, если вы используете функции, недоступные в предыдущей версии. Как правило, восстановление MySQL до предыдущей версии не поддерживается и не гарантируется.

резервное копирование mysql

Рисунок 1: Связь между бэкапом и бинарными логами

Автоматический бэкап MySQL с внутренним агентом

Автоматический бэкап базы данных MySQL в режиме дампа

На протяжении всего срока существования БД MySQL создает логи, которые можно использовать для репликации и/или защиты БД с помощью технологии P.I.T.R (восстановление MySQL до заданной контрольной точки).
По умолчанию агент MySQL создает дамп каждой БД отдельно. Это значит, что, если вам нужно восстановить весь сервер, все БД будут согласованы по-отдельности, но их резервные копии не не будут создаваться в одно и тоже время. Значит базы данных MySQL не будут согласованы глобально. Чтобы решить данную проблему, агент для резервного копирования MySQL также будет сохранять лог файлы, создаваемые во время резервного копирования. Эти лог файлы можно будет считать впоследствии, чтобы гарантировать согласованность баз данных на определенный момент времени.

На рисунке 1, показан процесс создания бэкапов баз данных MySQL БД1, БД2 и БД3 (процесс занимает несколько часов). Во время данного процесса генерируются 3 лог файла. Эти файлы включаются в полный бэкап MySQL. Следующий инкрементальный или дифференциальный бэкап MySQL сохранит только бинарные логи, созданные после полного бэкапа. Чтобы гарантировать, что только одна копия каждого лог файла включена в бэкап, необходимо активировать функцию Accurate для выполнения задачи.

В примере выше, первый инкрементальный бэкап, созданный после полного бэкапа, будет включать логи 5 и 6, второй инкрементальный бэкап будет включать логи 7 и 8. Дифференциальный бэкап включал бы лог файлы 5, 6, 7 и 8.
При использовании функции all_databases будут создаваться дампы всех БД одновременно. При этом лог файлы , созданные по завершении полного бэкапа, не будут включаться в него, а логи, сгенерированные до завершения задачи, будут включены в полный бэкап. В примере на рисунке 2 показано, что полный бэкап сгенерирует единый дамп файл «all-databases.sql», который будет включать лог файлы 2 и 3. Первый последующий инкрементальный бэкап будет включать логи 4, 5 и 6.

резервное копирование mysql

Рисунок 2: Связь между функцией all_databases и бинарными логами

Как сделать бэкап базы данных MySQL в режиме бинарных логов

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

Утилита бэкапа MySQL может создавать резервные копии данных из хранилищ InnoDB, XtraDB, и MyISAM на немодифицированных серверах MySQL 5.0, 5.1 и 5.5, также как это делает утилита Percona Server с XtraDB.
Более подробную информацию об утилите Percona вы найдете на сайте:

http://www.percona.com/doc/percona-xtrabackup.

Оценка информации при бэкапе таблицы MySQL

Команда estimate позволяет отобразить всю информацию, найденную агентом MySQL. В случае режима дампа, наше ПО не может оценить размер дамп файла для БД. Вместо этого оно отобразит размер БД.

Информация о резервном копировании MySQL в режиме дампа

Агент MySQL сгенерирует следующие файлы в каталоге Bacula для сервера, имеющего единую БД “test”.

резервное копирование mysql

 

ФайлТипПояснение
global-grants.sqlглобальныйСписок пользователей, их пароли и специальные функции
settings.txtглобальныйТекущие переменные для mysql сервера
my.cnfглобальныйКонфигурация MySQL
createdb.sqlБДСкрипт создания БД
schema.sqlБДСкрипт создания схемы БД
data.sqlБДДанные БД в формате дампа
grants.sqlБДСписок всех пользователей, связанных с БД

Таблица 2. Содержание бэкапа MySQL в режиме дампа

Восстановление MySQL

Bacula позволяет восстановить бэкап MySQL в нескольких режимах восстановления:

  • Восстановление MySQL из дамп файла или бинарных логов
  • Восстановление пользователей и ролей
  • Восстановление единой БД MySQL
  • Восстановление MySQL до контрольной точки

Чтобы восстановить бэкап MySQL в режиме бинарных логов, агент использует утилиту percona.

резервное копирование mysql

Рисунок 3: Содержимое сервера во время восстановления MySQL

бэкап mysql

Рисунок 4: Содержимое БД во время восстановления MySQL

Как сделать бэкап базы данных MySQL без использования плагина бесплатно

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

Данный способ полностью бесплатен, поскольку позволяет делать бэкап MySQL с помощью open source версии Bacula Community и без дополнительных плагинов. Для резервного копирования небольших баз данных MySQL можно использовать простые bash-скрипты для резервного копирования баз данных. Для случая с резервным копированием баз MySQL можно сделать скрипт бэкапа MySQL, который будет запускаться на клиенте и делать dump базы данных MySQL.

#!/bin/bash  

mysqldump -uuser -ppassword —all-databases | gzip > /opt/mysql_backup/backup.`date +%F`.sql.gz 

find /home/bacula-backup/ -type f -mtime +3 -exec rm -f {} \;

Этот скрипт бэкапа MySQL сохранит дамп всех БД MySQL в директорию /opt/mysql_backup/ из которой мы и будем делать резервные копии дампов базы данных, при помощи директивы Client Run Before Job.

Пример задачи на бэкап базы MySQL:

Job {

   Name = «BackupSmallMysqlServer»

   Type = Backup

   Level = Incremental

   Client = mysqlserver1

   FileSet = «mysqlserver»  

   Schedule = «WeeklyCycle»  

   Storage = SD1  

   Messages = Standard

   Pool = Mysql

   ClientRunBeforeJob = «/opt/sbin/mysql.sh»

   SpoolAttributes = yes

   Priority = 10

   Write Bootstrap = «/var/lib/bacula/%c.bsr»

     }

   FileSet {  

     Name = «mysqlserver»  

     Include {    

        Options {      

          signature = MD5      

          compression = GZIP    

                }

     File = /opt/mysql_backup/  

              }

            }

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

Как сделать бэкап нагруженных баз данных MySQL?

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

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

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

www.backup-solutions.ru

No related posts.