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


Содержание материала:

резервное копирование (backup) всех баз mysql

Запись создана февраля 11, 2009

Прошелся по нескольким блогам. почитал и удивился тому что для того чтобы сделать дампы всех баз mysql люди используют mysqldump —all-databases. На мой взгляд вариант более чем неудобный.

Предложу более правильный вариант, снимаются дампы со всех баз данных каждая база в отдельный файл. Для пояснения:
/backup/mysql/ — папка куда будем складывать дампы.
megapass — пароль root к mysql

итак собственно сам скрипт:

теперь поясню что делается, в цикле вывода имен всех баз данных кроме information_schema и Database выполняется mysqldump в файл дата-имя_базы, затем дамп жмется gzip-ом.

На выходе получаем пачку файлов на подобии:
2009-02-11-shakirov_kayako.gz
2009-02-11-shakirov_mantis.gz
2009-02-11-shakirov_openfire.gz

Например если в системе несколько пользователей и базы данных у них сделаны правильно (имя базы с префиксом имени пользователя, например shakirov_base), то можно делать бакапы баз разных пользователей в разные папки. С полученными бакапами можно поступать как удобно, хранить на отдельном диске, разделе. внешнем ftp сервере или заливать куда-то по scp.

Схожие темы

Комментарии

6 комментариев to “резервное копирование (backup) всех баз mysql”

    SeoMazzi on июня 3, 2009 10:15

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

Сенк за совет, заюзаю!

А каким образом все их восстановить за раз?

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

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

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

Сделать копию всех статических HTML- и прочих документов просто. Так же несложно периодически «откладывать в сторонку» и копии скриптов. Гораздо более сложной представляется задача создания копии (далее backup) такой динамичной структуры, как база данных MySQL. Основные трудности, которые возникают перед администратором размещенного на хостинге сайта, обычно бывают такие:

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

2. Отсутствие у администратора знаний о том, как вообще надо делать backup. Обычно такая задача возникает только, когда «клюнул жареный петух». То есть, в случае аварии, вторжения хакеров или в других внештатных ситуациях. Веб-мастеры просто не готовы к немедленному backup и начинают судорожно изучать документацию по MySQL, а время идет…

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

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

Как сделать копию базы MySQL

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

Сразу возьмем простую задачу, которую будем решать с помощью mysqldump, и разберемся, что к чему. Есть хостинг, есть база данных DBNAME, которую выделил Вам хостинг-провайдер. Есть хост HOST, на котором размещен сервер MySQL, логин LOGIN к нему, порт PORT, на котором работает сервер, а также пароль PASS. Имея все эти данные, можно сделать dump (дамп, копию) базы DBNAME так (выполняем в unix shell):

> mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME > dump.txt

После выполнения данной команды в файле dump.txt у нас будет копия MySQL-базы DBNAME. Это произойдет только в том случае, конечно, если все параметры Вы зададите верно, в соответствии с настройками своего хостинга. Сразу нужно сказать, что программа mysqldump производит вывод результатов прямо Вам на STDIN, то есть, на экран. Нужно перенаправлять вывод в какой-либо файл. Например, как в данном случае — » > dump.txt «. Если этого не сделать, а база большая, Вы получите на экран все те мегабайты информации, которые в ней содержатся.

Немного расскажем о том, что же делает mysqldump. Эта программа создает сценарий восстановления Ваших данных. То есть, вывод mysqldump — это не какие-то абстрактные и нечитаемые двоичные данные, а осмысленный текст сценария. Например, если в Вашей базе была таблица test, в которой было поле test2 с типом данных integer и одна-единственная запись «1111», то mysqldump создаст примерно такой сценарий:

# MySQL dump 8.14
#
# Host: HOST Database: DBNAME
#———————————————————
# Server version 3.23.39-log

#
# Table structure for table ‘test’
#

CREATE TABLE test (
test2 int(11) default NULL
) TYPE=MyISAM;

#
# Dumping data for table ‘test2’
#

INSERT INTO test2 VALUES (‘1111’);

Таким образом, mysqldump «опишет» все Ваши таблицы и создаст INSERT-команды для восстановления данных в таблицах. Итак, мы перенаправляем вывод mysqldump в текстовый файл, который потом будем использовать для восстановления. Рассмотрим и этот процесс — воссоздание базы из резервной копии.

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

> mysql -uLOGIN -PPORT -hHOST -pPASS DBNAME «, а можно — вот этот ключ. Кому что нравится;

Кроме перечисленных ключей mysqldump имеет и еще некоторое количество очень полезных возможностей, которые Вы можете применять по обстоятельствам. Полная документация по mysqldump доступна на странице http://www.mysql.com/doc/m/y/mysqldump.html.

Еще один очень полезный совет по использованию mysqldump в хостинговой среде. Как правило, при использовании хостинга на пользователя налагаются некоторые ограничения. Например, нельзя занять больше некоторого количества физической памяти (RAM, ОЗУ). mysqldump по умолчанию помещает все полученные от MySQL-сервера данные в память, а потом записывает все это на диск. Соответственно, если провайдер дает Вам занять, например, 30Мб памяти, а база, копию которой Вы делаете с помощью mysqldump, занимает 50Мб, конечно, тут возникнет ошибка — mysqldump не сможет отработать корректно и завершится аварийно, о чем Вам сообщит. Чтобы «заставить» mysqldump писать данные сразу на диск, а не хранить их, пусть даже и временно, в памяти, используйте ключ —quick. Это решит проблему.

Автоматизация резервного копирования

Теперь подумаем, как бы нам автоматизировать процесс создания резервных копий базы данных. Итак, существует программа — cron. Она позволяет запускать процессы в указанное пользователем время или с определенной периодичностью. Сразу оговоримся — cron в общем случае существует только под Unix, так что, если Вы используете для хостинга ОС Windows, проконсультируйтесь со своим хостинг-провайдером о том, как лучше запускать процессы в нужное время. Да и вообще, пожалуй, этот пункт будет интересен только unix-пользователям.

В unix shell запускаем crontab -e и создаем такое правило запуска процесса создания копий базы:

0 0 * * * mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date «+%Y-%m-%d»`.gz

Эта команда, запускаясь из cron в полночь (00:00) каждых суток, делает дамп Вашей базы DBNAME и архивирует его архиватором gzip в файл-архив с именем, соответствующим текущей дате. Например, если мы делаем dump 3 января 2002 года, имя файла с архивом будет 2002-01-03.gz. Для того, чтобы получить файлы, по именам которых можно удобно узнать дату их создания, мы используем команду date, которая является стандартной для всех unix-систем. Эта команда позволяет задавать произвольный формат вывода даты, что мы и использовали — date «+%Y-%m-%d». Мы поместили эту команду в обратные одинарные кавычки (backticks), что в unix shell заставляет вставить в команду (утрируя) результат выполнения другой команды.

Сохраняем правило для cron и ждем результатов. Итак, каждый день мы будем иметь на диске заархивированную копию нашей базы данных. Можно быстро найти нужный архив по его названию и восстановить то, что испортилось, например. Кстати, если Вы хотите автоматизировать удаление старых архивов, попробуйте воспользоваться cron и командой find, которая обычно есть в unix. Запуская периодически find

/каталог-с-архивами -name «*.gz» -mtime +7, Вы будете удалять архивы, которые «старше» семи дней. Прочитайте документацию по find — она доступна по команде man find в unix shell.

Если у Вас есть машина, постоянно подключенная к интернет, можно так же по cron копировать созданный Вами backup на нее. Конечно, провайдерская хостинг-машина — это очень надежная штука. Однако, как говорится, «береженого Бог бережет». Старая как мир истина в определенных условиях может и Вам помочь. Используйте для копирования на другую машину команды ftp и scp. Добавьте их запуск в cron. Если Ваша машина поддерживает соединение по протоколу ssh, используйте secure copy клиент для копирования файлов — scp. Читайте документацию по этой команде в man-странице man scp. Примерный запуск: scp 2002-01-03.gz login@your.host.ru: — закачиваем файл 2002-01-03.gz на машину your.host.ru авторизовавшись там под логином login.

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

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

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

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

Мы предполагаем, что у вас уже установлен MySQL в системе Linux с правами администратора, и мы предполагаем, что у вас уже есть небольшой опыт работы в MySQL.

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

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

Параметры указанной команды следующие:

  • [username]: действительное имя пользователя MySQL.
  • [password]: действительный пароль MySQL для пользователя.
  • [database_name]: действительное имя базы данных, резервную копию которой вы хотите сделать .
  • [dump_file.sql]: имя файла резервной копии, которую вы хотите сгенерировать.

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

Чтобы сделать резервную копию одной базы данных, используйте команду следующим образом. Команда создаст дамп структуры базы данных [rsyslog] с данными в один файл дампа с именем rsyslog.sql.

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

Если вы хотите сделать резервную копию нескольких баз данных, выполните следующую команду. В следующем примере команда создает резервную копию структуры баз данных [rsyslog, syslog] и самих данных в одном файле с именем rsyslog_syslog.sql.

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

Если вы хотите сделать резервную копию всех баз данных, используйте следующую команду с опцией –all-database. Следующая команда выполняет резервное копирование всех баз данных с их структурой и данными в файл all-database.sql.

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

Если вам требуется только резервное копирование структуры базы данных без данных, используйте в команде параметр –no-data. Приведенная ниже команда экспортирует структуру базы данных [rsyslog] в файл rsyslog_structure.sql.

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

Для резервного копирования исключительно данных из базы данных (без структуры), используйте параметр –no-create-info вместе со следующей командой. Эта команда переносит базу данных [rsyslog]Data в файл rsyslog_data.sql.

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

С помощью приведенной ниже команды вы можете сделать резервную копию одной таблицы или определенных таблиц вашей базы данных. Например, следующая команда создает резервную копию таблицы wp_posts только из базы данных wordpress.

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

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

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

Следующая команда переносит резервную копию базы данных [ gallery ] удаленного сервера [172.16.25.126] на локальный сервер:

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

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

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

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

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

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

Спасибо за уделенное время на прочтение статьи!

Если возникли вопросы, задавайте их в комментариях.

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

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

В данной статье рассмотрим несколько практических примеров резервирования восстановления баз с помощью mysqldump. Утилита mysqldump – это эффективный инструмент для создания резервной копии базы данных MySQL. Он позволяет создать *.sql файл с совокупностью (дампом) всех таблиц и данных основной базы данных (источника).

С целью резервирования баз данных MyISAM, лучше использовать инструмент mysqlhotcopy, который мы обязательно опишем в следующих статьях, так как с ними он работает быстрее и эффективнее.

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

Базовыми командами для создания резервной копии и восстановления базы данных MySQL с помощью mysqldump есть:

Создание резервной копии:

# mysqldump –u[пользователь] –p[пароль_пользователя] [имя_базы] › [название_файла_резервной_копии_базы].sql

# mysqldump -uroot -pqwerty my_db › my_db-dump1.sql

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

# mysql -u[пользователь] -p[пароль_пользователя] [имя_базы] ‹ [название_файла_резервной_копии_базы].sql

# mysql -uroot -pqwerty my_db ‹ my_db-dump1.sql

В данных командах:

-u – параметр, который указывает логин, с помощью которого в данном случае осуществляется подключение к базе данных;

-p – параметр, который указывает пароль пользователя данного логина. Если после данного параметра не указать пароль, то после запуска команды его необходимо будет ввести дополнительно;

[имя_базы] – имя базы данных, резервную копию которой необходимо создать;

[название_файла_резервной_копии_базы].sql – пользователь может указать любое удобное название файла резервной копии базы данных. Если указать название файла как в предоставленном примере, то резервная копия базы будет создана в папке из которой запускалась команда, а именно:
C:\Program Files\MySQL\MySQL Server 5.7\bin

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

# mysqldump -uroot -pqwerty my_db › C:\Users\Valery\Documents\MySQL_Backup\my_db-dump1.sql
# mysql -uroot -pqwerty my_db ‹ C:\Users\Valery\Documents\MySQL_Backup\my_db-dump1.sql

Далее рассмотрим разные варианты создания и восстановления резервной копии базы данных MySQL.

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

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

# mysqldump –u[пользователь] –p[пароль_пользователя] [имя_базы] › [название_файла_резервной_копии_базы].sql

# mysqldump -uroot -pqwerty my_db > my_db-dump1.sql

Резервная копия нескольких баз данных

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

Для этого введите команду show databases (в Workbench)

или # mysqlshow –uroot -p (в консоли).

Если необходимо одновременно создать резервную копию нескольких баз данных (например, my_db и test), то для этого необходимо выполнить такую команду:

# mysqldump -uroot -pqwerty –databases my_db test › my_db_test_backup.sql

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

Если есть необходимость создать бэкап всех баз данных вашего профайла MySQL, то это можно сделать с помощью параметра –all-databases.

# mysqldump -uroot -pqwerty –all-databases › all-databases_backup.sql

Резервная копия отдельной таблицы

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

# mysqldump -uroot -p my_db wp_commentmeta › table_ my_db-wp_commentmeta.sql

Примечание. Чтобы просмотреть список таблиц базы, введите команду:
#mysqlshow –uroot –p my_db

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

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

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


Задача: сделать резервную копию базы данных или всех баз данных сервера MySQL.

Мы можем пойти двумя путями:

  • Остановить сервер MySQL и просто скопировать базы
  • Сделать бекап на-лету (без остановки сервера MySQL)

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

С другой стороны, при копировании на-лету не нужно останавливать сервер MySQL (что, очевидно, создало бы отказ в обслуживании на время бекапа) и не нужно знать путь к базам данным на сервере. И еще один момент — не всегда название базы = название ее каталога в файловой системе!

Путь первый: грамотно сделаем бекап на-лету

Для этого нам понадобится утилита «mysqldump». Она идет в комплекте с самим сервером MySQL. Вот пример вызова это утилиты:

В этом примере мы используем утилиту под пользователем root (параметр -p указывает, что нужно запросить пароль для пользователя) для создания резервной копии базы данных под названием «mydatabase». Результат бекапа будет записан в виде дампа SQL в файл mydatabase.bak

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

  • все базы данных будут вывалены в один файл, который, при их большом суммарном объеме, превратится просто в огромную глыбу дампа;
  • нам нужно выполнять бекап из-под пользователя, который имеет право доступа ко всем базам данных — тобишь — из-под root или его аналога.

В этом примере все базы будут вывалены в дамп под названием alldatabases.bak

А теперь сделаем бекап двух или более баз. В нашем примере две БД (mydatabase1 и mydatabase2) будут забекаплены в один файл mydatabases.bak

Делаем бекап на-лету — все базы, но каждую базу в свой файл

Техника не моя — взята с сайта itblog.su и подправлена.

Для этого пишем скриптик с вот таким содержанием:

, т.е. -p и слитно — пароль).

Ключик -f в gzip заставляет его перезаписывать уже существующие файлы без вопроса «Вы уверены?».

Не забудьте сделать скрипт исполняемым.

В данном случае будет произведен бекап всех баз MySQL сервера, но по-отдельности и без остановки сервера. Каждая база будет положена в свой файл с называнием базы + расширение bak, а потом этот файл еще и будет сжат gzip ( + .gz).

Вот что мы получим (пример):

Где вместо mydatabase1 и т.д. будут имена баз данных (именно имена баз данных, а не имена каталогов, в которых эти базы хранятся).

Путь второй: тупо загасим MySQL и скопируем

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

Топ-пост этого месяца:  Какие товары, услуги или сайты имеет смысл продвигать в социальных сетях (SMO, SMM) и привлекать

Итак, гасим сервер.

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

В данном примере предполагается, что база данных назвается mydatabase. Вам нужно указывать название каталога той базы, которую Вы бекапите. Ну и результат появится в виде одного файла mydatabase.bak.tar

И стартуем сервер MySQL обратно.

Гыы, а я сделал бекап копированием без остановки MySQL и у меня получилось

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

Бэкап базы данных MySQL на сервере Ubuntu

Что такое MySQL?

MySQL — это популярная система управления базами данных (СУБД), использующая для управления данными язык запросов SQL. MySQL идеально подходит для хранения данных сайта или веб-приложения.

Резервное копирование (или бэкап) — очень важная для сохранности любых данных операция. Особенно это касается баз данных. Бэкап базы данных MySQL можно выполнить несколькими способами, о чём и пойдёт речь в этой статье.

Примечание: Для выполнения руководства использовался сервер Ubuntu 12.04 и MySQL 5.5, но более современные версии программного обеспечения будут работать подобным образом.

Бэкап базы данных MySQL при помощи mysqldump

Утилита mysqldump — один из самых простых и удобных способов создания резервной копии MySQL.

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

mysqldump -u username -p database_to_backup > backup_name.sql

Восстановление БД

Чтобы восстановить дамп БД, созданный при помощи mysqldump, нужно просто перенаправить вывод в файл MySQL.

Для этого создайте пустую БД для хранения импортированных данных. Войдите в MySQL:

mysql -u username -p

Создайте новую БД, чтобы переместить в неё данные из дампа, а затем закройте командную строку MySQL:

CREATE DATABASE database_name;
exit

Перенаправьте дамп-файл в файл БД:

mysql -u username -p database_name

Скопированные данные будут восстановлены в новой БД.

Копирование таблицы MySQL в текстовый файл

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

Общий синтаксис команды:

SELECT * INTO OUTFILE ‘table_backup_file’ FROM name_of_table;

Данный оператор сохранит данные из таблицы в файл на сервере MySQL. Имейте в виду: если файл с таким именем уже существует, операция не будет выполнена.

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

Утилита automysqlbackup

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

Чтобы установить эту программу, введите в терминал:

sudo apt-get install automysqlbackup

Главный конфигурационный файл утилиты находится в /etc/default/automysqlbackup; откройте его с правами администратора:

sudo nano /etc/default/automysqlbackup

Как видите, данный файл по умолчанию присваивает множество переменных из файла /etc/mysql/debian.cnf, который содержит данные для авторизации. Из этого файла automysqlbackup считывает пользователя, пароль и БД, резервные копии которых нужно создать.

Стандартное место хранения резервных копий — /var/lib/automysqlbackup. Найдите этот каталог и ознакомьтесь со структурой бэкапов:

ls /var/lib/automysqlbackup
daily monthly weekly

Каталог daily содержит подкаталог для каждой БД, в котором хранится сжатый sql дамп, полученный в результате последнего запуска команды:

ls -R /var/lib/automysqlbackup/dailey
.:
database_name information_schema performance_schema
./database_name:
database_name_2013-08-27_23h30m.Tuesday.sql.gz
./information_schema:
information_schema_2013-08-27_23h30m.Tuesday.sql.gz
./performance_schema:
performance_schema_2013-08-27_23h30m.Tuesday.sql.gz

Для настройки автоматического запуска резервного копирования система Ubuntu устанавливает вместе с этой программой демона cron.

Репликация баз данных

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

Репликация — это процесс зеркалирования данных с ведущего сервера на другие (тип master-slave) или с любого сервера связки на остальные серверы (тип master-master).

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

Чтобы устранить эту проблему, можно:

  • Временно отключить репликацию
  • Или временно сделать сервер резервного копирования доступным только для чтения.

Временное отключение репликации

Чтобы временно отключить репликацию на slave-сервере, введите:

mysqladmin -u user_name -p stop-slave

Есть ещё один метод, который не отключает репликацию полностью, а просто ставит её на паузу:

mysql -u user_name -p -e ‘STOP SLAVE SQL_THREAD;’

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

После этого просто возобновите репликацию:

mysqladmin -u user_name -p start-slave

Настройка доступа к серверу резервного копирования

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

Это можно сделать как на сервере master, так и на slave.

Для начала откройте MySQL с правами root:

Запишите все кэшированные изменения на диск и настройте систему только для чтения с помощью команд:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

Выполните бэкап при помощи mysqldump.

После того как резервное копирование будет выполнено, верните систему в её обычное состояние:

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

Нерекомендуемые методы резервного копирования

Скрипт mysqlhotcopy

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

Во-первых, этот скрипт копирует только данные, хранящиеся при помощи механизмов MyISAM и Archive. Большинство пользователей не меняют механизмы хранения для своих БД, а MySQL, начиная с версии 5.5, по умолчанию использует механизм InnoDB. Следовательно, скрипт mysqlhotcopy не может скопировать такой тип данных.

Во-вторых, данные, скопированные при помощи этого скрипта, можно запустить только на той же машине, на которой хранится БД. То есть mysqlhotcopy не сможет скопировать данные с удалённого сервера.

Копирование файлов таблиц

Следующий метод, который не рекомендуется применять, — это простое копирование файлов таблиц MySQL.

Этот подход имеет те же недостатки, что и скрипт mysqlhotcopy.

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

Заключение

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

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

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

Скрипт для бэкапа mysql баз

Написал для себя простенький скрипт (linux bash) для бэкапа всех баз на одном сервере. Его отличительные особенности:

  • Наличие списка исключений (т.е. бэкапим все кроме…)
  • Получение списка всех БД из MySQL (не надо добавлять вновь созданные базы к бекапу)
  • Создание директории под бэкап вида “…/YYYY/mm/dd/HH-MM/”
  • Бэкап каждой базы в отдельный файл вида “YYYY-mm-dd.HH-MM.databasename.backup.sql” (mysqldump бэкапит все в один файл)
  • Архивирование бэкапа в тарбол
  • Зачистка .sql

Собственно к написанию скрипта меня сподвигло именно то что mysqldump бэкапит все что ему сказано в один файл (если требуется восстановить одну базу, то попробуй ее выцарапай из общего дампа…), а создавать отдельную строку для бэкапа всякой новой БД геморно (об этом как минимум надо вспомнить!).

В общем если интересно – прошу под кат:

Собственно сам скрипт

#!/bin/bash

##
# MySQL backup utility
# @author dmitry.bykadorov@gmail.com
##

# current date
DATE=`date +%Y-%M-%d`

# y/m/d/h/m separately
YEAR=`date +%Y`
MONTH=`date +%m`
DAY=`date +%d`
HOURS=`date +%H`
MINUTES=`date +%M`

# database credentials
DBUSER=””
DBPASS=””
DBHOST=””

# create list of databases
mysql -h $DBHOST -u $DBUSER -p$DBPASS -e “show databases;” > /tmp/databases.list


# create backup dir (e.g. ../2011/01/01/04-00)
BACKUP_DIR=/home/backups/mysql/$YEAR/$MONTH/$DAY/$HOURS-$MINUTES
mkdir –parents –verbose $BACKUP_DIR

# excludes list (Database is a part of SHOW DATABASES output)
EXCLUDES=( ‘Database’ ‘information_schema’ )
NUM_EXCLUDES=$

for database in `cat /tmp/databases.list`
do skip=0
let count=0 while [ $count -lt $NUM_EXCLUDES ] ; do # check if this name in excludes list if [ “$database” = $ ] ; then let skip=1 fi let count=$count+1 done
if [ $skip -eq 0 ] ; then echo “++ $database” # now we can backup current database cd $BACKUP_DIR backup_name=”$YEAR-$MONTH-$DAY.$HOURS-$MINUTES.$database.backup.sql” backup_tarball_name=”$backup_name.tar.gz” `/usr/bin/mysqldump -h “$DBHOST” –databases “$database” -u “$DBUSER” –password=”$DBPASS” > “$backup_name”` echo ” backup $backup_name” `/bin/tar -zcf “$backup_tarball_name” “$backup_name”` echo ” compress $backup_tarball_name” `/bin/rm “$backup_name”` echo ” cleanup $backup_name” fi
done

echo “done!”

#!/bin/bash ## # MySQL backup utility # @author dmitry.bykadorov@gmail.

com ## # current date DATE=`date +%Y-%M-%d` # y/m/d/h/m separately YEAR=`date +%Y` MONTH=`date +%m` DAY=`date +%d` HOURS=`date +%H` MINUTES=`date +%M` # database credentials DBUSER=”” DBPASS=”” DBHOST=”” # create list of databases mysql -h $DBHOST -u $DBUSER -p$DBPASS -e “show databases;” > /tmp/databases.list # create backup dir (e.g. ..

/2011/01/01/04-00) BACKUP_DIR=/home/backups/mysql/$YEAR/$MONTH/$DAY/$HOURS-$MINUTES mkdir –parents –verbose $BACKUP_DIR # excludes list (Database is a part of SHOW DATABASES output) EXCLUDES=( ‘Database’ ‘information_schema’ ) NUM_EXCLUDES=$ <#EXCLUDES[@]>for database in `cat /tmp/databases.

list` do skip=0 let count=0 while [ $count -lt $NUM_EXCLUDES ] ; do # check if this name in excludes list if [ “$database” = $ ] ; then let skip=1 fi let count=$count+1 done if [ $skip -eq 0 ] ; then echo “++ $database” # now we can backup current database cd $BACKUP_DIR backup_name=”$YEAR-$MONTH-$DAY.$HOURS-$MINUTES.$database.backup.

sql” backup_tarball_name=”$backup_name.tar.gz” `/usr/bin/mysqldump -h “$DBHOST” –databases “$database” -u “$DBUSER” –password=”$DBPASS” > “$backup_name”` echo ” backup $backup_name” `/bin/tar -zcf “$backup_tarball_name” “$backup_name”` echo ” compress $backup_tarball_name” `/bin/rm “$backup_name”` echo ” cleanup $backup_name” fi done `/bin/rm /tmp/databases.list` echo “done!”

Можете его свободно:

  • использовать (никаких гарантий я вам конечно же не даю – на свой страх и риск )))
  • модифицировать (например добавить выгрузку бэкапа на другой сервер или на Amazon S3 )))
  • критиковать ))

P.S. Если таки будете использовать – не забудьте подставить ваши mysql credentials ))

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

Утилита 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 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

Примеры навеяны постом Александра Макарова –

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

mysqldump –no-create-info –no-data –triggers –routines –events -uroot -p database | gzip >

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

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

Еще пару слов о бекапе в 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, вот слайд объясняющий нововведения:

» Делаем резервные копии баз данных при помощи mysqldump

Делаем резервные копии баз данных при помощи mysqldump

Делать резервные копии (бэкапы) очень важно для серьезных проектов, в которых часто обновляются данные.

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

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

Для создания бэкапов у mysql есть утилита mysqldump. Её то мы и будем использовать. Для тестирования можем зайти на сервер по SSH либо пробовать на локальной базе. Синтаксис команды прост:

Тут всё интуитивно понятно. LOGIN — имя пользователя базы, PASS — его пароль, а HOST — имя или айпи адрес сервера, в большинстве случаев это localhost, хотя зависит от хостинга. DBNAME — имя базы для которой делаем бэкап. mysqldump — непосредственно сама команда. backup.

sql — имя файла, в который будет помещен дамп, перед именем файла можно и даже нужно указывать папку, например /home/backups/backup.sql.

Иногда перед командой нужно дописать путь к папке в которой лежит утилита, на UNIX серверах это как правило /usr/bin/, в этом случае команда будет выглядеть так:

Допустим мы хотим сделать бэкап для базы testing и подключаемся мы обычно к ней под пользователем root с паролем 123456.

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

Если мы хотим забэкапить все базы на сервере добавляем ключ —all-databases

mysqldump —all-databases-uroot -hlocalhost -p123456 > backup.sql

Если хотим сэкономить место на диске, бэкап можно сразу архивировать

Очень удобно в названии файла помещать дату дату бэкапа

mysqldump -uroot -hlocalhost -p123456 testing | gzip> `date «+%Y-%m-%d»`_backup.sql

Я обычно пользуюсь такой схемой

Таким образом у меня в папке бэкапов хранится 31 файл и я могу восстановить базу любого дня последнего месяца. Название файла является числом.

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

Если же найти утилиту не получается в интерфейса хостинга, попробуйте добавить команду крона через SSH. Выполните команду crontab -e и создайте приблизительно такое правило:

Эта запись говорит планировщику, что необходимо запускать команду «mysqldump -uroot -hlocalhost -p123456 testing > backup.sql» каждый день в 3 часа 0 минут.

Ну и напоследок дополнительные настройки, которые может принимать утилита 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» будет заменена на 0×616263.

—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=
Параметр позволяет задать протокол подключения к серверу.

—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.

—tz-utc
при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE=’+00:00′, который позволит обмениваться дампа в различных временных зонах.

—verbose, -v
Расширенный режим вывода. Вывод более детальной информации о работе программы.

–version, -V
Вывести информацию о версии программы.


—where=’where-condition’, -w ‘where-condition’
Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны: «—where=user=’test’» «-wuserid>1″ «-wuserid

Резервное копирование 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. Но есть еще один, более точный способ, который позволяет указать точное время выполнения. Сначала выполните команду:

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

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

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

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

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

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

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

mysql> CREATE DATABASE new_database;

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

mysql -u пользователь -p база_данных https://losst.ru/rezervnoe-kopirovanie-mysql-bazy-dannyh

Дамп и восстановление базы данных MySQL

Дамп и восстановление базы данных MySQL довольно просто и удобно делать удаленно через SSH или прямо через консоль сервера. Удаленно, это можно делать используя программы Putty/Kitty.

Также указанные ниже примеры Вы можете выполнять и на Windows запустив командную строку ‘cmd‘.

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

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

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

# Бекап одной базы данных в файл dump_file.sqlmysqldump -uroot -p your_base > dump_file.sql# На windows дамп лучше всего создавать немного другой командой, которая предотвращает# случайное затирание строк дампа из за конвертации символов перевода строки ‘
‘ в ‘
‘mysqldump -uroot -p your_base -r dump_file_utf8.sql# Если Вам нужен бекап только отдельных таблиц, а не всей базы данных# (указываем наименования таблиц через пробел после названия базы данных)mysqldump -uroot -p your_base TABLE1 TABLE2 TABLE3 > dump_file.sql# Если нужно создать бекап только структуры базы данных без самих данныхmysqldump -uroot -p –no-data your_base > dump_file.sql# Бекап всех баз данных в файл текущая_дата.gzmysqldump -uroot -p –all_databases | gzip -c > ‘date “+%Y-%m-%d”’.gz# Бекап, где для каждой записи создается отдельный INSERT# и с явным указанием кодировки базы данных UTF-8mysqldump -uroot -p –default-character-set=utf8 your_base –extended-insert=FALSE | gzip -c > ‘date “+%Y-%m-%d”’.gz

В приведенном выше примере, для создания бекапа используется утилита mysqldump, которая входит в состав mysql. Далее указываются параметры для создания бекапа базы данных, которые разберем подробнее:

  • -u – параметр указывает логин, который будет использоваться для подключения к базе данных. В примере мы используем логин root, который нужно указать в этом параметре без пробела! В результате у нас это выглядит как -uroot
  • -p – параметр указывает что нужно ввести пароль для указанного логина. Мы его оставили пустым, в результате чего пароль нужно будет ввести после нажатия “Enter” при выполнении команды. Тем не менее, можно указать пароль сразу же здесь, как и в параметре логина, без пробела после -p, однако этот способ не является безопасным, так как консоль сохраняет Ваши команды в лог файл и если Вы его регулярно не очищаете, то он может быть просмотрен злоумышленником.
  • your_base – вместо этой строки в примере, вам необходимо указать реальное имя Вашей базы данных, для которой Вы создаете бекап.
  • > – оператор который показывает направление действия, т.е. как бы указывает, что вы собираетесь сделать запись из базы в файл.
  • dump_file.sql – это название Вашего файла .slq в которую нужно сохранить Вашу базу данных. Он указывается через пробел после оператора ‘>’. Вы можете задать любое другое имя. Например, чтобы в имени система автоматически вставила текущее время, достаточно указать строку вида:после этой строки в примере указывается расширение файла ‘.gz‘. В результате будет создан файл вида ‘2014-11-15.gz‘.Внимание! Если Вы указываете только имя файла, то он будет сохранен в той же директории, относительно которой Вы выполняете данную команду. Т.е. если Вы видите в строке приглашения ввода команд что-то вроде [root@dvs home]#, где root@dvs это логин и имя сервера, то файл будет создан в директории /home. Чтобы изменить сохранение файла по другому пути, укажите вместо имени полный путь для сохранения файла, например: /var/www/backup/dump_file.sql.
  • Во втором примере, вместо оператора ‘>‘ используется оператор ‘|‘, который указывает на необходимость выполнения дополнительной команды gzip c параметром ‘-c‘ которая позволяет сразу же запаковать дамп в архив, а только затем сохранить его в файл вида ‘2014-11-15.gz‘, о чем сообщает оператор ‘>‘.
  • Параметр –no-data позволяет создать дамп только структуры базы данных без самих данных. В некоторых случаях довольно полезно, когда данные не нужны.
  • Параметры –default-character-set=utf8 и –extended-insert=FALSE. Первый позволяет Вам явно указать кодировку, которая используется этой базой данных, тем самым избежать сохранение базы в неверной кодировке Вместо utf8 можно указать любую другую кодировку, например cp1251. Второй параметр позволяет указать, что при экспорте для каждой записи необходимо создать отдельную команду INSERT. В некоторых случаях это может потребоваться при частичном восстановлении данных из дампа.

Восстановление базы данных из файла дампа MySQL

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

# Восстанавливаем базу данных your_base из файла дампа dump_filemysql -uroot -p your_base

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

CodeNet / Приложения / Базы данных / MySQL

Резервное копирование баз MySQL. Гораздо более сложной представляется задача создания копии такой динамичной структуры, как база данных MySQL.

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

Именно по этой причине пользователи в условиях хостинга могут, конечно, рассчитывать на восстановление в случае удаления каких-то данных по вине самого пользователя, но вовсе не факт, что провайдер сделает восстановление MySQL-базы сразу по получению запроса. Лучше делать для себя копию и в случае чего ее использовать.

Можно даже периодически копировать этот свой backup на другую, не провайдерскую машину – так надежнее, на всякий случай.

Сделать копию всех статических HTML- и прочих документов просто. Так же несложно периодически “откладывать в сторонку” и копии скриптов. Гораздо более сложной представляется задача создания копии (далее backup) такой динамичной структуры, как база данных MySQL. Основные трудности, которые возникают перед администратором размещенного на хостинге сайта, обычно бывают такие:

  1. Отсутствие физического доступа к файлам базы данных. Как правило, провайдеры хостинга предоставляют возможность работы с базой данных только через скрипты или специальный mysql-клиент, но не дают прав на доступ непосредственно к файлам, в которых содержатся данные из MySQL-базы.
  2. Отсутствие у администратора знаний о том, как вообще надо делать backup. Обычно такая задача возникает только, когда “клюнул жареный петух”. То есть, в случае аварии, вторжения хакеров или в других внештатных ситуациях. Веб-мастеры просто не готовы к немедленному backup и начинают судорожно изучать документацию по MySQL, а время идет…
  3. В случае, если веб-мастер не владеет в достаточной мере навыками работы со специализированными утилитами из пакета MySQL, могут возникать трудности, связанные с ограничениями, налагаемыми хостинг-провайдером на пользовательские аккаунты. Например, если база очень большая и ее размер превышает лимит на доступную пользователю память (RAM), backup сделать будет сложно. Нужно пользоваться тонкими настройками утилит резервного копирования, что иногда тоже вызывает трудности на практике.

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

Как сделать копию базы MySQL

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

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

Можно сказать, что mysqldump – это основной инструмент, которым Вам придется пользоваться в том случае, если Вы будете делать backup MySQL.

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

Есть хост HOST, на котором размещен сервер MySQL, логин LOGIN к нему, порт PORT, на котором работает сервер, а также пароль PASS.

Имея все эти данные, можно сделать dump (дамп, копию) базы DBNAME так (выполняем в unix shell):

> mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME > dump.txt

После выполнения данной команды в файле dump.txt у нас будет копия MySQL-базы DBNAME. Это произойдет только в том случае, конечно, если все параметры Вы зададите верно, в соответствии с настройками своего хостинга.

Сразу нужно сказать, что программа mysqldump производит вывод результатов прямо Вам на STDIN, то есть, на экран. Нужно перенаправлять вывод в какой-либо файл. Например, как в данном случае – ” > dump.txt “.

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

Немного расскажем о том, что же делает mysqldump. Эта программа создает сценарий восстановления Ваших данных.

То есть, вывод mysqldump – это не какие-то абстрактные и нечитаемые двоичные данные, а осмысленный текст сценария.

Например, если в Вашей базе была таблица test, в которой было поле test2 с типом данных integer и одна-единственная запись “1111”, то mysqldump создаст примерно такой сценарий:

# MySQL dump 8.14 # # Host: HOST Database: DBNAME #——————————————————– # Server version 3.23.39-log # # Table structure for table ‘test’ # CREATE TABLE test ( test2 int(11) default NULL ) TYPE=MyISAM; # # Dumping data for table ‘test2’ # INSERT INTO test2 VALUES (‘1111’);

Таким образом, mysqldump “опишет” все Ваши таблицы и создаст INSERT-команды для восстановления данных в таблицах. Итак, мы перенаправляем вывод mysqldump в текстовый файл, который потом будем использовать для восстановления. Рассмотрим и этот процесс – воссоздание базы из резервной копии.

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

> mysql -uLOGIN -PPORT -hHOST -pPASS DBNAME ”, а можно – вот этот ключ. Кому что нравится;

Кроме перечисленных ключей mysqldump имеет и еще некоторое количество очень полезных возможностей, которые Вы можете применять по обстоятельствам. Полная документация по mysqldump доступна на странице http://www.mysql.com/doc/m/y/mysqldump.html.

Еще один очень полезный совет по использованию mysqldump в хостинговой среде. Как правило, при использовании хостинга на пользователя налагаются некоторые ограничения. Например, нельзя занять больше некоторого количества физической памяти (RAM, ОЗУ). mysqldump по умолчанию помещает все полученные от MySQL-сервера данные в память, а потом записывает все это на диск.

Соответственно, если провайдер дает Вам занять, например, 30Мб памяти, а база, копию которой Вы делаете с помощью mysqldump, занимает 50Мб, конечно, тут возникнет ошибка – mysqldump не сможет отработать корректно и завершится аварийно, о чем Вам сообщит. Чтобы “заставить” mysqldump писать данные сразу на диск, а не хранить их, пусть даже и временно, в памяти, используйте ключ –quick.

Это решит проблему.

Автоматизация резервного копирования

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

Сразу оговоримся – cron в общем случае существует только под Unix, так что, если Вы используете для хостинга ОС Windows, проконсультируйтесь со своим хостинг-провайдером о том, как лучше запускать процессы в нужное время.

Да и вообще, пожалуй, этот пункт будет интересен только unix-пользователям.

В unix shell запускаем crontab -e и создаем такое правило запуска процесса создания копий базы:

0 0 * * * mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date “+%Y-%m-%d”`.gz

Эта команда, запускаясь из cron в полночь (00:00) каждых суток, делает дамп Вашей базы DBNAME и архивирует его архиватором gzip в файл-архив с именем, соответствующим текущей дате. Например, если мы делаем dump 3 января 2002 года, имя файла с архивом будет 2002-01-03.gz.

Для того, чтобы получить файлы, по именам которых можно удобно узнать дату их создания, мы используем команду date, которая является стандартной для всех unix-систем. Эта команда позволяет задавать произвольный формат вывода даты, что мы и использовали – date “+%Y-%m-%d”.

Мы поместили эту команду в обратные одинарные кавычки (backticks), что в unix shell заставляет вставить в команду (утрируя) результат выполнения другой команды.

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

Кстати, если Вы хотите автоматизировать удаление старых архивов, попробуйте воспользоваться cron и командой find, которая обычно есть в unix. Запуская периодически find

/каталог-с-архивами -name “*.gz” -mtime +7, Вы будете удалять архивы, которые “старше” семи дней.

Прочитайте документацию по find – она доступна по команде man find в unix shell.

Если у Вас есть машина, постоянно подключенная к интернет, можно так же по cron копировать созданный Вами backup на нее. Конечно, провайдерская хостинг-машина – это очень надежная штука. Однако, как говорится, “береженого Бог бережет”. Старая как мир истина в определенных условиях может и Вам помочь.

Используйте для копирования на другую машину команды ftp и scp. Добавьте их запуск в cron. Если Ваша машина поддерживает соединение по протоколу ssh, используйте secure copy клиент для копирования файлов – scp. Читайте документацию по этой команде в man-странице man scp. Примерный запуск: scp 2002-01-03.gz : – закачиваем файл 2002-01-03.

gz на машину your.host.ru авторизовавшись там под логином login.

Дополнительные возможности

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

Разбираемся с утилитами для бэкапа баз данных – «Хакер»

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

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

Для начала разберемся с тем, какие вообще бывают бэкапы.

Сервер баз данных не является обычным десктопным приложением, и, чтобы обеспечить выполнение всех свойств ACID (Atomic, Consistency, Isolated, Durable), используется ряд технологий, а поэтому создание и восстановление БД из архива имеет свои особенности. Существуют три различных подхода к резервному копированию данных, каждый из которых имеет свои плюсы и минусы.

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

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

При восстановлении администратору необходимо будет выполнить несколько команд, чтобы подготовить все необходимое (создать пустую базу данных, учетные записи и прочее).

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

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

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

Например, в PostgreSQL логи упреждающей журнализации WAL (Write Ahead Logs) и специальная функция (Point in Time Recovery — PITR), позволяющая вернуться к определенному состоянию базы. С их помощью легко реализуется третий сценарий, когда бэкап уровня файловой системы объединяется с резервной копией WAL-файлов.

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

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

Сайт: pgbarman.org, sf.net/projects/pgbarman

Лицензия: GNU GPL

Поддерживаемые СУБД: PostgreSQL

PostgreSQL поддерживает возможности физического и логического бэкапа, добавляя к ним еще один уровень WAL (см. врезку), который можно назвать непрерывным копированием. Но управлять при помощи штатных инструментов несколькими серверами не очень удобно даже админу со стажем, а в случае сбоя счет идет на секунды.

Barman (backup and recovery manager) — внутренняя разработка компании 2ndQuadrant, предоставляющей услуги на базе PostgreSQL. Предназначен для физического бэкапа PostgreSQL (логический не поддерживает), архивирования WAL и быстрого восстановления после сбоев.

Поддерживаются удаленный бэкап и восстановление нескольких серверов, функции point-in-time-recovery (PITR), управление WAL. Для копирования и подачи команд на удаленный узел используется SSH, синхронизация и бэкап при помощи rsync позволяет сократить трафик.


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

Реализованы различные сервисные и диагностические функции, позволяющие контролировать состояние сервисов и регулировать полосу пропускания. Поддерживаются Pre/Post-скрипты.

Конфигурационный файл Barman

Barman написан на Python, управление политиками резервного копирования производится при помощи понятного INI-файла barman.conf, который может находиться в /etc или домашнем каталоге пользователя.

В поставке идет готовый шаблон с подробными комментариями внутри. Работает только на *nix-системах. Для установки в RHEL, CentOS и Scientific Linux следует подключить EPEL — репозиторий, в котором содержатся дополнительные пакеты.

В распоряжении пользователей Debian/Ubuntu официальный репозиторий:

$ sudo apt-get install barman

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

Сайт: sypex.net/ru/products/dumper

Лицензия: BSD

Поддерживаемые СУБД: MySQL

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

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

Умеет работать с объектами MySQL — представлениями, процедурами, функциями, триггерами и событиями.

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

В одном дампе могут быть объекты с разными кодировками. Причем легко импорт/экспорт произвести в несколько этапов, останавливая процесс во время нагрузки. При возобновлении процедура начнется с места остановки.

При восстановлении поддерживается четыре варианта:

  • CREATE + INSERT — стандартный режим восстановления;
  • TRUNCATE + INSERT — меньше времени на создание таблиц;
  • REPLACE — восстанавливаем в рабочей базе старые данные, не затирая новые;
  • INSERT IGNORE — добавляем в базу удаленные или новые данные, не трогая существующие.

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

Управление производится при помощи веб-браузера, интерфейс с использование AJAX локализован из коробки и создает впечатление работы с настольным приложением. Также возможно запускать задания из консоли и по расписанию (через cron).

Для работы Dumper понадобится классический L|WAMP-сервер, установка обычная для всех приложений, написанных на PHP (копируем файлы и устанавливаем права), и не будет сложной даже для новичка. Проект предоставляет подробную документацию и видеоуроки, демонстрирующие работу с Sypex Dumper.

Есть две редакции: Sypex Dumper (бесплатно) и Pro (10 долларов). Вторая имеет больше возможностей, все отличия приведены на сайте.

Сайт: sqlbackupandftp.com

Лицензия:коммерческая, есть версия Free

Поддерживаемые СУБД: MS SQL Server

MS SQL Server — одно из популярных решений, а потому встречается достаточно часто. Задание резервного копирования создается при помощи среды SQL Server Management Studio, собственно Transact-SQL и командлетов модуля SQL PowerShell (Backup-SqlDatabase).

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

Новичку действительно вначале потребуется потренироваться, «набив руку», поэтому, даже несмотря на все сказанное, сторонним разработчикам есть где развернуться. К тому же бесплатная версия SQL Server Express не может похвастаться встроенными инструментами для резервного копирования.

Для более ранних версий MS SQL (до 2008) можно найти бесплатные утилиты, например SQL Server backup, но в большинстве подобные проекты уже коммерциализировались, хотя и предлагают всю функциональность часто за символическую сумму.

Резервное копирование всех баз данных Mysql одной командой в Debian

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

Резервное копирование, вещь лишняя и ненужная до того момента когда ….

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

В общем изрядно потрепав себе нервы пришло время делать backup всего и вся.

А начнем мы с задач:

  1. Обеспечить бесперебойную систему работы сайтов, включая моменты восстановления и резервного копирования.
  2. Обеспечить себя backup на все случаи жизни! Ни один сайт не должен ни при каких обстоятельствах умереть совсем.
  3. Все должно делаться, автоматически включая удаления старых backup, но не противоречить пункту 2.
  4. Все backup должны быть индивидуальные для каждого сайта свой файл.
  5. Удобная каталогизация.
  6. Все файлы должны архивироваться (место все же не резиновое).
  7. Backup должен работать исключительно на регулярных выражениях.
  8. Несколько временных интервалов.
  9. Логируем все, не дай бог пойдет не так, а мы не в курсе.
  10. Как можно легче, админ не программист;) Скрипт моя твоя не понимать, не особо полезен.

Имеется: Debian6×64, 2Tb, 32Gb, SSD 60Gb — 300 баз данных разных пользователей mysql + innodb. 5 Gb базы данных. Первое, что сразу захотелось сделать, это попросту скопировать /etc local/mysql/ и пришел на ум сразу изящный вариант.

Создаем папку для бэкапа: mkdir /var/backup/SQl_backup/

Создаем файл скрипта:

и вписываем вот этот текст.

Вносим задачу в crontab

добавляем строчку, бэкап в 2 ночи

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

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

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

Теперь все работает правильно, но нам нужны backup адекватные. Так же требуется ограничить количество файлов, и структурировать максимально удобным образом. Мало того, у потоковой архивации есть неприятная плюшка, заключающаяся в том что `date +%Y-%m-%d` во время создания файла и архивации могут расходиться, и если мы добавим часы минуты и секунды то получим увлекательную штуку, все что за секунду попало в архив то ужалось, что больше секунды не попало под этот фильтр и не сжалось. Соответственно остается вероятность такого же в минутах часах, и теоретически днях. Что нас уже не устраивает, если жене давать столь подробнее название, то архиватор при попадание уже созданного файла просто останавливается и ждет ввода притом gzip не может по умолчанию нажимать ДА.

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

Что уже дало на выходе вот это

Создаем папку с форматом внимание ! …// в которую и сложим все файлы последнее это цифирное выражение дня недели! -%u и вуали, все строчки кода превратились в маленький скрипт, по дороге нам пришлось отказаться от потокового сжатия, его минусы оказались столь значительны что использование его оказалось невозможным, в частности слишком много, но выпазило по догори, и первая ошибка останавливала скрипит полость, хоть он у нас и легируется в файл, но веже недопустимая роскошь. Даже если один дамп не отработал, остальные обязаны корректно отработать. Плюс мы сделали отличную каталогизацию.

Но и от этого варианта пришлось отказаться, слишком трудно и много но. Нам нужно 100% работающий скрипт который невозможно повредить.

И так упрощаем, с этого места уже можно писать в консоль, а не просто читать.

и вставляем вот этот скрипт

Выполняем с консоли:

Для уменьшения нагрузки на рабочий сервер:

Добавляем в крон каждое утро 4.00

Теперь каждое утро в 4–00 будет делаться бэкап, по умолчанию он будет лежать в папке ласт, остальное будет сжато и лежать в папке соответствующего года.

В результате за год у вас будет сделано 12*7 бэкапов.

С виду достаточно много, но если перейти к цифрам то текстовые фалы очень хорошо ужимаются и все мои базы сжались до 400 мегабайт. Фактически отдать 84*400 всего то 40 Гб места, что мягко говоря против 2ТБ винтов копейки. Зато в замен у вас получаются бэкапы каждой последней недели каждого месяца. И последняя неделя свежая. Плюс в папке ласт лежит самый последний бэкап.

Можно усложнить скрипт для удаления старых бэкапов и обрезки их до состояния 1 срез в позапрошлом году. Но это уже по желанию. Фактически сейчас скрипт делает срез и архивит его. А дальше уже кроном делаем что вам надо. Как показала практика базы данных в архиве очен

Разбираемся с утилитами для бэкапа баз данных

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

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

Виды бэкапов баз данных

Для начала разберемся с тем, какие вообще бывают бэкапы. Сервер баз данных не является обычным десктопным приложением, и, чтобы обеспечить выполнение всех свойств ACID (Atomic, Consistency, Isolated, Durable), используется ряд технологий, а поэтому создание и восстановление БД из архива имеет свои особенности. Существуют три различных подхода к резервному копированию данных, каждый из которых имеет свои плюсы и минусы.

При логическом, или SQL, бэкапе (pg_dump, mysqldump, SQLCMD) создается мгновенный снимок содержимого базы с учетом транзакционной целостности и сохраняется в виде файла с SQL-командами (можно выбрать всю базу или отдельные таблицы), при помощи которого можно воссоздать базу данных на другом сервере. На это требуется время (особенно для больших баз) для сохранения и восстановления, поэтому очень часто эту операцию выполнять нельзя и ее производят во время минимальной нагрузки (например, ночью). При восстановлении администратору необходимо будет выполнить несколько команд, чтобы подготовить все необходимое (создать пустую базу данных, учетные записи и прочее).

Физический бэкап (уровня файловой системы) — копирование файлов, которые СУБД использует для хранения данных в базе данных. Но при простом копировании игнорируются блокировки и транзакции, которые, скорее всего, будут неправильно сохранены и нарушены. При попытке присоединить этот файл он будет в несогласованном состоянии и приведет к ошибкам. Чтобы получить актуальный бэкап, базу данных нужно остановить (можно уменьшить время простоя, использовав два раза rsync — вначале на работающей, потом на остановленной). Недостаток этого метода очевиден — нельзя восстановить определенные данные, только всю базу данных. При старте БД, восстановленной из архива файловой системы, нужно будет провести проверку на целостность. Здесь используются разные вспомогательные технологии. Например, в PostgreSQL логи упреждающей журнализации WAL (Write Ahead Logs) и специальная функция (Point in Time Recovery — PITR), позволяющая вернуться к определенному состоянию базы. С их помощью легко реализуется третий сценарий, когда бэкап уровня файловой системы объединяется с резервной копией WAL-файлов. Вначале восстанавливаем файлы резервной копии файловой системы, а затем при помощи WAL база приводится к актуальному состоянию. Это чуть более сложный подход для администрирования, но зато нет проблем с целостностью БД и восстановлением баз до определенного времени.

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

Barman

Лицензия: GNU GPL

Поддерживаемые СУБД: PostgreSQL

PostgreSQL поддерживает возможности физического и логического бэкапа, добавляя к ним еще один уровень WAL (см. врезку), который можно назвать непрерывным копированием. Но управлять при помощи штатных инструментов несколькими серверами не очень удобно даже админу со стажем, а в случае сбоя счет идет на секунды.

Barman (backup and recovery manager) — внутренняя разработка компании 2ndQuadrant, предоставляющей услуги на базе PostgreSQL. Предназначен для физического бэкапа PostgreSQL (логический не поддерживает), архивирования WAL и быстрого восстановления после сбоев. Поддерживаются удаленный бэкап и восстановление нескольких серверов, функции point-in-time-recovery (PITR), управление WAL. Для копирования и подачи команд на удаленный узел используется SSH, синхронизация и бэкап при помощи rsync позволяет сократить трафик. Также Barman интегрируется со стандартными утилитами bzip2, gzip, tar и подобными. В принципе, можно использовать любую программу сжатия и архивирования, интеграция не займет много времени. Реализованы различные сервисные и диагностические функции, позволяющие контролировать состояние сервисов и регулировать полосу пропускания. Поддерживаются Pre/Post-скрипты.

Конфигурационный файл Barman

Хакер #183. Малварь для Android

Barman написан на Python, управление политиками резервного копирования производится при помощи понятного INI-файла barman.conf, который может находиться в /etc или домашнем каталоге пользователя. В поставке идет готовый шаблон с подробными комментариями внутри. Работает только на *nix-системах. Для установки в RHEL, CentOS и Scientific Linux следует подключить EPEL — репозиторий, в котором содержатся дополнительные пакеты. В распоряжении пользователей Debian/Ubuntu официальный репозиторий:

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

Sypex Dumper

Лицензия: BSD

Поддерживаемые СУБД: MySQL

Вместе с MySQL поставляются утилиты mysqldump, mysqlhotcopy, позволяющие легко создать дамп базы данных, они хорошо документированы, и в интернете можно найти большое количество готовых примеров и фронтендов. Последние позволяют новичку быстро приступить к работе. Sypex Dumper представляет собой PHP-скрипт, позволяющий легко создать и восстановить копию базы данных MySQL. Создавался для работы с большими базами данных, работает очень быстро, понятен и удобен в использовании. Умеет работать с объектами MySQL — представлениями, процедурами, функциями, триггерами и событиями.

Еще один плюс, в отличие от других инструментов, при экспорте производящих перекодирование в UTF-8, — в Dumper экспорт производится в родной кодировке. Результирующий файл занимает меньше места, а сам процесс происходит быстрее. В одном дампе могут быть объекты с разными кодировками. Причем легко импорт/экспорт произвести в несколько этапов, останавливая процесс во время нагрузки. При возобновлении процедура начнется с места остановки. При восстановлении поддерживается четыре варианта:

  • CREATE + INSERT — стандартный режим восстановления;
  • TRUNCATE + INSERT — меньше времени на создание таблиц;
  • REPLACE — восстанавливаем в рабочей базе старые данные, не затирая новые;
  • INSERT IGNORE — добавляем в базу удаленные или новые данные, не трогая существующие.

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

Управление производится при помощи веб-браузера, интерфейс с использование AJAX локализован из коробки и создает впечатление работы с настольным приложением. Также возможно запускать задания из консоли и по расписанию (через cron).

Для работы Dumper понадобится классический L|WAMP-сервер, установка обычная для всех приложений, написанных на PHP (копируем файлы и устанавливаем права), и не будет сложной даже для новичка. Проект предоставляет подробную документацию и видеоуроки, демонстрирующие работу с Sypex Dumper.

Есть две редакции: Sypex Dumper (бесплатно) и Pro (10 долларов). Вторая имеет больше возможностей, все отличия приведены на сайте.

SQL Backup And FTP

Лицензия:коммерческая, есть версия Free

Поддерживаемые СУБД: MS SQL Server

MS SQL Server — одно из популярных решений, а потому встречается достаточно часто. Задание резервного копирования создается при помощи среды SQL Server Management Studio, собственно Transact-SQL и командлетов модуля SQL PowerShell (Backup-SqlDatabase). На сайте MS можно найти просто огромное количество документации, которая позволяет разобраться с процессом. Документация хотя и полная, но очень специфическая, а информация в интернете часто противоречит друг другу. Новичку действительно вначале потребуется потренироваться, «набив руку», поэтому, даже несмотря на все сказанное, сторонним разработчикам есть где развернуться. К тому же бесплатная версия SQL Server Express не может похвастаться встроенными инструментами для резервного копирования. Для более ранних версий MS SQL (до 2008) можно найти бесплатные утилиты, например SQL Server backup, но в большинстве подобные проекты уже коммерциализировались, хотя и предлагают всю функциональность часто за символическую сумму.

SQL Backup And FTP позволяет одним щелчком произвести бэкап MS SQL

Например, разработка SQL Backup And FTP и One-Click SQL Restore соответствует принципу «настроил и забыл». Обладая очень простым и понятным интерфейсом, они позволяют создавать копии баз данных MS SQL Server (включая Express) и Azure, сохранять зашифрованные и сжатые файлы на FTP и облачных сервисах (Dropbox, Box, Google Drive, MS SkyDrive или Amazon S3), результат можно тут же просмотреть. Возможен запуск процесса как вручную, так и по расписанию, отправка сообщения о результате задания по email, запуск пользовательских скриптов.

Поддерживаются все варианты бэкапа: полный, дифференциальный, журнал транзакций, копирование папки с файлами и многое другое. Старые резервные копии удаляются автоматически. Для подключения к виртуальному узлу используется SQL Management Studio, хотя здесь могут быть нюансы и это будет работать не во всех таких конфигурациях. Для загрузки предлагается пять версий — от бесплатной Free до навороченной Prof Lifetime (на момент написания этих строк стоила всего 149 долларов). Функционала Free вполне достаточно для небольших сетей, в которых установлено один-два SQL-сервера, все основные функции активны. Ограничено количество резервных БД, возможность отправки файлов на Google Drive и SkyDrive и шифрование файлов. Интерфейс хотя и не локализован, но очень прост и понятен даже новичку. Нужно лишь подключиться к SQL-серверу, после чего будет выведен список баз данных, следует отметить нужные, настроить доступ к удаленным ресурсам и указать время выполнения задания. И все это в одном окне.

Но есть одно «но». Сама программа не предназначена для восстановления архивов. Для этого предлагается отдельная бесплатная утилита One-Click SQL Restore, понимающая и формат, созданный командой BACKUP DATABASE. Админу необходимо лишь указать архив и сервер, на который восстановить данные, и нажать одну кнопку. Но в более сложных сценариях придется использовать RESTORE.

Утилита One-Click SQL Restore предназначена для восстановления баз MS SQL

Особенности бэкапа MS SQL Server

Создание резервной копии и восстановление СУБД имеет свои отличия, которые нужно учитывать, особенно их много при переносе архива на другой сервер. Для примера разберем некоторые нюансы MS SQL Server. Для архивирования при помощи Transact-SQL следует использовать команду BACKUP DATABASE (есть и разностная DIFFERENTIAL) и журнал транзакций BACKUP LOG.

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

Простая ситуация — бэкап и перенос баз на другие версии SQL Server. Эта операция поддерживается, но в случае SQL Server будет работать, если версия сервера, на которой разворачивается копия, такая же или новее, чем та, на которой она создавалась. Причем есть ограничение: новее не более чем на две версии. После восстановления БД будет находиться в режиме совместимости с той версией, с которой осуществлялся переход, то есть новые функции будут недоступны. Это легко поправить, изменив COMPATIBILITY_LEVEL. Можно это сделать при помощи GUI или SQL.

Определить, на какой версии была создана копия, можно, просмотрев заголовок файла архива. Чтобы не экспериментировать, при переходе на новую версию SQL Server следует запустить бесплатную утилиту Microsoft Upgrade Advisor.

Iperius

Лицензия:коммерческая, есть версия Free

Поддерживаемые СУБД: Oracle 9–11, XE, MySQL, MariaDB, PostgreSQL и MS SQL Server

Когда приходится управлять несколькими типами СУБД, без комбайнов не обойтись. Выбор большой. Например, Iperius — легкая, очень простая в использовании и одновременная мощная программа для резервного копирования файлов, имеющая функцию горячего резервирования баз данных без прерывания в работе или блокирования. Обеспечивает полный или инкрементальный бэкап. Может создавать полные образы дисков для автоматической переустановки всей системы. Поддерживает резервное копирование на NAS, USB-устройства, стример, FTP/FTPS, Google Drive, Dropbox и SkyDrive. Поддерживает сжатие zip без ограничения в размере файлов и AES256-шифрование, запуск внешних скриптов и программ. Включает весьма функциональный планировщик заданий, возможно параллельное или последовательное выполнение нескольких заданий, результат отправляется на email. Поддерживаются многочисленные фильтры, переменные для персонализации путей и настроек.

Настройка задания в Iperius

Возможность закачки по FTP позволяет легко обновлять информацию на нескольких веб-сайтах. Открытые файлы резервируются при помощи технологии VSS (теневого копирования томов), что позволяет производить горячий бэкап не только файлов СУБД, но и других приложений. Для Oracle также задействуется средство организации резервного копирования и восстановления данных RMAN (Recovery Manager). Чтобы не перегружать канал, есть возможность настройки полосы пропускания. Управление резервированием и восстановлением производится при помощи локальной и веб-консоли. Все функции на виду, поэтому для настройки задания потребуется лишь понимание процесса, в документацию заглядывать даже не придется. Просто следуем подсказкам мастера. Также можно отметить менеджер учетных записей, что очень удобно при большом количестве систем.

Базовые функции предлагаются бесплатно, но возможность резервирования БД заложена только в версиях Advanced DB и Full. Поддерживается установка от XP до Windows Server 2012.

Handy Backup

Лицензия:коммерческая

Поддерживаемые СУБД:Oracle, MySQL, IBM DB2 (7–9.5) и MS SQL Server

Одна из самых мощных систем управления реляционными базами данных — IBM DB2, имеющая уникальные функции по масштабированию и поддерживающая множество платформ. Поставляется в нескольких редакциях, которые построены на одной базе и отличаются функционально. Архитектура баз данных DB2 позволяет управлять практически всеми типами данных: документами, XML, медиафайлами и так далее. Особо популярна бесплатная DB2 Express-C. Бэкап очень прост:

Или снапшот, использующий функцию Advanced Copy Services (ACS):

Но нужно помнить, что в случае снимков мы не можем восстанавливать (db2 recover db) отдельные таблицы. Есть и возможности по автоматическому бэкапу, и многое другое. Продукты хорошо документированы, хотя в русскоязычном интернете руководства встречаются редко. Также далеко не во всех специальных решениях можно найти поддержку DB2.

Например, Handy Backup позволяет выполнять бэкап нескольких типов серверов баз данных и сохранять файлы практически на любой носитель (жесткий диск, CD/DVD, облачное и сетевое хранилище, FTP/S, WebDAV и другие). Возможен бэкап баз данных через ODBC (только таблицы). Это одно из немногих решений, поддерживающих DB2, и к тому же имеет логотип «Ready for IBM DB2 Data Server Software». Вся процедура выполняется при помощи обычного мастера, в котором необходимо лишь выбрать нужный пункт и сформировать задачу. Сам процесс настройки настолько прост, что разобраться сможет и новичок. Можно создать несколько заданий, которые будут запускаться по расписанию. Результат фиксируется в журнале и отправляется по email. Во время работы задания остановка сервиса не требуется. Архив автоматически сжимается и шифруется, что гарантирует его безопасность.

Работа мастера создания нового задания в Handy Backup

Работу с DB2 поддерживают две версии Handy Backup — Office Expert (локальный) и Server Network (сетевой). Работает на компьютерах под управлением Win8/7/Vista/XP или 2012/2008/2003. Сам процесс развертывания несложен для любого админа.

В MySQL, каковы методы резервного копирования баз данных?

Я использую mysql

Как часто вы создаете резервную копию своей базы данных?

Как вы обычно делаете резервную копию своей базы данных?

Экспортировать все данные в формат sql или cvs и хранить их в папке

Я использую mysqldump :

Настройка cronjob для запуска script, который выполняет mysqldump и сохраняет дамп на отдельном диске из самой базы данных (или удаленного сервера), является довольно простым и эффективным способом резервного копирования базы данных, на мой взгляд. Вы даже можете сбросить каждую базу данных с помощью переключателя —all-databases

Если у вас более одного сервера MySQL, вы также можете использовать replication

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

Топ-пост этого месяца:  Как реализовать Ajax прогрузку следующего поста внутри статьи
Добавить комментарий