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

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

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

mysqldump -uLOGIN -hHOST -pPASS DBNAME > backup.sql

Тут всё интуитивно понятно. LOGIN — имя пользователя базы, PASS — его пароль, а HOST — имя или айпи адрес сервера, в большинстве случаев это localhost, хотя зависит от хостинга. DBNAME — имя базы для которой делаем бэкап. mysqldump — непосредственно сама команда. backup.sql — имя файла, в который будет помещен дамп, перед именем файла можно и даже нужно указывать папку, например /home/backups/backup.sql. Иногда перед командой нужно дописать путь к папке в которой лежит утилита, на UNIX серверах это как правило /usr/bin/, в этом случае команда будет выглядеть так:

/usr/bin/mysqldump -uLOGIN -hHOST -pPASS DBNAME > backup.sql

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

mysqldump -uroot -hlocalhost -p123456 testing > backup.sql

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

mysqldump -uroot -hlocalhost -p123456 testing table1 table2 table3 > backup.sql

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

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

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

mysqldump -uroot -hlocalhost -p123456 testing | gzip> backup.sql

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

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

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

mysqldump -uroot -p123456 testing  > /usr/home/backups/date "+%d".sql

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

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

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

0 3 * * * mysqldump -uroot -hlocalhost -p123456 testing > backup.sql

Эта запись говорит планировщику, что необходимо запускать команду «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={TCP|SOCKET|PIPE|MEMORY}
Параметр позволяет задать протокол подключения к серверу.

—quick, -q
Позволяет начать формирование дампа, не дожидаясь полной загрузки данных с сервера и экономя тем самым память.

—quote-names, -Q
Помещает имена баз данных, таблиц и столбцов в обратные апострофы `. Начиная с MySQL 4.1, данный параметр включен по умолчанию.

—replace
Добавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3.

—result-file=/path/to/file, -r /path/to/file
Параметр направляет дамп в файл file. Этот параметр особенно удобен в Windows, без использования командной строки. когда можно перенаправить результат в файл при помощи последовательностей > и >>.

--routines, -R
Данный параметр создает дамп хранимых процедур и функций. Доступен с MySQL 5.1.2.

—single-transaction
Параметр создает дамп в виде одной транзакции.

skip-comments
Данный параметр позволяет подавить вывод в дамп дополнительной информации.

—socket=/path/to/socket, -S /path/to/socket
Файл сокета для подсоединения к localhost.

—tab=/path/, -T /path/
При использовании этого параметра в каталоге path для каждой таблицы создаются два отдельных файла: tbl_name.sql, содержащий оператор CREATE TABLE, и tbl_name.txt, который содержит данные таблиц, разделенные символом табуляции. Формат данных может быть переопределен явно с помощью параметров —fields-xxx и —lines-xxx.

—tables
Перекрывает действия параметра —databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц.

—triggers
Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр —skip-triggers.

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

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

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

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

—xml, -X
Представляет дамп базы данных в виде XML.

first-slave, -x
Блокирует все таблицы во всех базах данных.

--debug=…, -#
Отслеживать прохождение программы (для отладки).

—help
Выводится справка и завершается работа программы.

Похожие записи

  • Нет похожих записей

Ваш комментарий

Я не робот