В данной статье поговорим о стратегии backup в mysql. Статья основана на официальной документации. В каких случаях нам нужен backup?
-Аварийное завершение операционной системы
-Отключение питания
-Проблемы с файловой системой
-Проблемы с железом
Примеры в данной статье не содержат инструкций –user и –password для утилиты mysqldump и других клиентов mysql. Их необходимо прописывать отдельно к приведенным ниже примерам.
Представим, что данные хранятся в InnoDB, который поддерживает транзакции и автоматическое восстановление после аварийного завершения. Предположим также, что MySQL сервер находится под нагрузкой во время аварийного завершения.
Файлы данных InnoDB могут не содержать данные об аварийном завершении, но InnoDB читает логи и находит в них список подтвержденных и неподтвержденных транзакций, которые не были сохранены в файлы данных. InnoDB автоматически откатывает транзакции, которые не были подтверждены. Информация об этом сохраняется в error log. Вот пример error log
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 13674004 InnoDB: Doing recovery: scanned up to log sequence number 0 13739520 InnoDB: Doing recovery: scanned up to log sequence number 0 13805056 InnoDB: Doing recovery: scanned up to log sequence number 0 13870592 InnoDB: Doing recovery: scanned up to log sequence number 0 13936128 ... InnoDB: Doing recovery: scanned up to log sequence number 0 20555264 InnoDB: Doing recovery: scanned up to log sequence number 0 20620800 InnoDB: Doing recovery: scanned up to log sequence number 0 20664692 InnoDB: 1 uncommitted transaction(s) which must be rolled back InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx no 16745 InnoDB: Rolling back of trx no 16745 completed InnoDB: Rollback of uncommitted transactions completed InnoDB: Starting an apply batch of log records to the database... InnoDB: Apply batch completed InnoDB: Started mysqld: ready for connections |
В случае проблем с файловой системой или проблем с железом, мы можем предположить, что данные не доступны после перезапуска. Это значит, что MySQL не может прочитать данные. В этом случае нужно переформатировать диск, решить проблему с железом, и после этого воспользоваться BackUP, который должен быть сделан заранее. Об этом мы и поговорим далее.
Стратегия BackUP
Backup файлы должны делаться регулярно. Полный backup (снимок данных на определенное время) может быть сделан несколькими инструментами MySQL. В данной статье мы рассмотрим утилиту mysqldump.
Представьте, что мы делаем полный BackUP всех наших таблиц во всех базах данных в воскресенье в 13.00, используя следующую команду
Примечание для Windows, чтобы корректно работали инструкции mysqldump, необходимо проделать следующее (пример для MySQL 5.5)
Win+X –> Командная строка (администратор), далее нужно изменить директорию таким образом
|
1 |
cd C:\Program Files\MySQL\MySQL Server 5.5\bin |
Далее можем прописать следующее
|
1 |
mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql |
Полученный .sql файл, созданный mysqldump содержит набор SQL INSERT инструкций, которые используются, чтобы перезагрузить архивные таблицы позже.
Чтобы проделать эту операцию, необходимо предварительно заблокировать все таблицы следующей инструкцией.
|
1 |
FLUSH TABLES WITH READ LOCK |
Как только эта блокировка запрашивается, получаются координаты binary log . Если в это время обновляются инструкции, процесс Backup будет ждать, пока они не закончатся. После этого таблицы освобождаются от блокировки. Инструкция –single-transaction необходима для движка InnoDB.
Полное архивирование необходимо, но она занимает много времени и места на диске, иногда также удобно обойтись частичным архивированием отдельных таблиц. Более эффективно делать полный изначальный архив и в дальнейшем архивировать только изменения. Это занимает меньше времени. Цена этого способа в том, что невозможно воспользоваться полным восстановлением из архива.
Изменения в БД в MySQL фиксируются в binary log, таким образом MySQL сервер должен быть запущен с параметром –log-bin. В этом режиме сервер записывает каждое изменение в БД в этот лог. Выглядит он примерно следующим образом
|
1 2 3 4 5 6 7 |
-rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001 -rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002 -rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003 -rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004 -rw-rw---- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005 -rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006 -rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index |
Каждый раз, когда он сервер перезапускается, он создает новый binary log, используя следующий номер в последовательности. Когда сервер запущен, можно попросить его закрыть текущий binary log и запустить следующий с помощью инструкции
|
1 |
FLUSH LOGS SQL |
Файл .index в директории содержит список всех binary log.
Если вы командуете “flush logs” при выполнении full backup, binary log создаются после этого и содержат все данные после BackUP. То есть, пример можно изменить таким образом
(В командной строке под администратором)
|
1 2 |
mysqldump --single-transaction --flush-logs --master-data=2 \ --all-databases > backup_sunday_1_PM.sql |
После выполнения этой команды, директория данных будет содержать новый binary log file, gbichot2-bin.000007
Параметр –masterdata просит прописать данные binary log во внешнем файле, таким образом файл .sql будет содержать следующие строки
|
1 2 |
-- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4; |
Эти 2 строки означают следующее
-Дамп содержит все изменения, сделанные до того как изменения были записаны в gbichot2-
bin.000007
-Все изменения данных залогированные после backup отсутствуют в дамп файле, но присутствуют в gbichot2-
bin.000007
В понедельник в 13 00 мы можем создать частичное восстановление Backup, сбросив логи и начав новый binary log file. Например, выполнение инструкции –flush-logs создаст gbichot2-
bin.000008. Все изменения между Воскресеньем 13.00 и понедельником 13.00 будут в файле gbichot2-bin.000007.
Во вторник в 13.00 можно еще раз исполнить команду –flush-logs и все изменения между понедельником 13.00 и вторником 13.00 будут в файле gbichot2-bin.000008.
Все файлы дампов лучше хранить в отдельном месте – на отдельном компьютере, двд и так далее.
Чтобы освободить место на диске, можно удалять ненужные binary logs и делать full backup, например таким образом
|
1 2 |
shell> mysqldump --single-transaction --flush-logs --master-data=2 \ --all-databases --delete-master-logs > backup_sunday_1_PM.sql |
Восстановление из Backup
Теперь предположим, что у нас случилась катастрофа в среду в 8 утра. Нам нужно срочно восстановить данные. Чтобы сделать это мы должны восстановить полный backup, который у нас есть (на воскресенье 13.00). Это можно сделать следующим образом
|
1 |
shell> mysql < backup_sunday_1_PM.sql |
Все данные будут восстановлены к моменту – “Воскресенье 13.00”. Далее, чтобы восстановить все последующие изменения, мы должны воспользоваться частичными архивами – gbichot2-bin.000007 и gbichot2-
bin.000008. Достанем их из безопасного места и проделаем следующее
|
1 |
shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql |
Файлы желательно хранить в другом месте, не там где работает mysql, для этого можно воспользоваться командой log-bin которая позволяет настроить место хранения. Данные у нас восстановлены на вторник 13.00, последним шагом будет
|
1 |
shell> mysqlbinlog gbichot2-bin.000009 ... | mysql |
Теперь дата актуальности данных среда 13.00
Утилита mysqldump
Архивирование баз
mysqldump на выходе может давать 2 типа файлов .txt и .sql, если воспользоваться параметром –tab, то мы получим оба варианта, если без этого параметра, то только .sql
Архивировать все базы
|
1 |
shell> mysqldump --all-databases > dump.sql |
Архивировать только конкретные базы
|
1 |
shell> mysqldump --databases db1 db2 db3 > dump.sql |
Если нужно удалить базы перед восстановлением, можно использовать параметр –add-drop-database
Архивировать одну базу
|
1 |
mysqldump --databases test > dump.sql |
или
|
1 |
mysqldump test > dump.sql |
Архивировать отдельные таблицы
|
1 |
shell> mysqldump test t1 t3 t7 > dump.sql |
Восстановление баз
Если дамп был создан с опциями –all-databases or –databases, то он содержит в себе инструкции CREATE, а значит мы можем не указывать базу, в которую будем загружать архив. Можем написать просто
|
1 |
shell> mysql < dump.sql |
Можно тоже самое сделать в клиенте MySQL
|
1 |
mysql> source dump.sql |
Если в дампе нет инструкций CREATE, тогад нам нужно создать сначала БД
|
1 |
shell> mysqladmin create db1 |
потом собственно произвести загрузку
|
1 |
shell> mysql db1 < dump.sql |
Либо альтернативно в консоли MySQL
|
1 2 3 |
mysql> CREATE DATABASE IF NOT EXISTS db1; mysql> USE db1; mysql> source dump.sql |
Как сделать копию БД?
|
1 2 3 |
shell> mysqldump db1 > dump.sql shell> mysqladmin create db2 shell> mysql db2 < dump.sql |
Копирование БД с одного сервера на другой
На 1 сервере
|
1 |
shell> mysqldump --databases db1 > dump.sql |
Далее копируем файл dump.sql на второй сервер
На втором сервере
|
1 |
shell> mysql < dump.sql |
Использование инструкции –databases создает команды CREATE, поэтому на 2 сервере мы не создавали БД и не ставили её по умолчанию.
Альтернативный путь
На 1 сервере
|
1 |
shell> mysqldump db1 > dump.sql |
На 2 сервере
|
1 2 |
shell> mysqladmin create db1 shell> mysql db1 < dump.sql |
Дамп хранимых процедур, триггеров, событий
–events Этот параметр добавляет в архив события
–routines: Этот параметр добавляет в архив хранимые процедуры и функции
–triggers: Этот параметр добавляет в архив триггеры (они включены по умолчанию)
Если при загрузке нам что-то не нужно можем использовать инструкции
–skipevents, –skip-routines, or –skip-triggers.