Резервируем данные БД Mysql

Причины создания резервных копии обсуждать не стоит, давайте разберемся, как делаются бекапы БД Mysql

1. Создаем резервную копию. MySQLDump

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

mysqldump [options] db_name [tbl_name ...]

mysqldump [options] --databases db_name ...

mysqldump [options] --all-databases

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

--add-drop-table Добавляет DROP TABLE перед каждой командой CREATE TABLE

--no-data Экспортируем только структуру базы данных без самих данных. Чаще всего применяется при проектировании баз данных.

--opt Комбинирует в себе ряд опций, дает наиболее быстрый для чтения дамп.

-Q, --quote-names Берем в кавычки имена таблиц и столбцов - это очень полезная опция. Если название одного из столбцов будет совпадать с спецсловом (например, name), получим ошибку при импорте

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

-h, --host=.. Выполнить дамп данных MySQL сервера на указанном хосте. Значение хоста по умолчанию localhost.

-P port_num, --port=port_num Номер порта TCP/IP, используемого для подключения к хосту. Значение по умолчанию 3306

-u Имя_пользователя, --user=Имя_пользователя Имя пользователя MySQL-сервера, используемое при подключении к серверу.

-pВаш_пароль, --password=Ваш_пароль Используемый пароль при подключении к серверу. Если не ввести значение, утилита предложит ввести пароль.

Практическое применение

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

mysqldump –-user=USER_NAME –p --all-databases > all_databases.sql

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

mysqldump –-user=USER_NAME –p --quote-names --add-drop-table --databases DB_NAME1 DB_NAME2 > backup.sql

Делаем резервную копию нескольких таблиц. Это полезно в случае, если остальные таблицы статичные.

mysqldump –-user=USER_NAME –p --quote-names --add-drop-table DB_NAME table1 table2 > backup.sql

2. Восстановление данных

Для восстановления данных используем стандартную утилиту mysql

Восстановление из дампа со всеми базами данных или несколькими базами

mysql –u USER_NAME -p < backup.sql

Восстановление в остальных случаях (одна база данных или некоторые таблицы из базы данных)

mysql –u USER_NAME -p  DB_NAME< backup.sql

3. Автоматизируем процесс создания резервных копий

Для автоматического создания резервных копий в планировщик (cron) можно добавить задание

mysqldump –-user=USER_NAME –pPASSWORD --quote-names --add-drop-table --databases DB_NAME > /home/user/backup_DB_NAME.sql

Но в таком случае мы будем иметь только один бекап.

mysqldump --user=USER_NAME -pPASSWORD --quote-names --add-drop-table --opt --databases DB_NAME  | gzip > `date + /home/user/backup_DB_NAME.sql.%Y%m%d.gz`

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

Использованные материалы:

http://www.mysql.ru/docs/man/mysqldump.html

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html