Всё нужно бэкапить, в том числе и mysql. Но как это делать правильнее всего?
Многие предложат утилиту mysqldump, но она блокирует таблицу на время создания дампа (для InnoDB это можно обойти, использовав параметр –single-transaction в утилите mysqldump), соответственно в это время нельзя будет внести никакие данные в эту таблицу. Да и время на создания дампа уходит много. Если для вас это не критично, можете использовать эту утилиту.
А если же критично? Что тогда?
Тогда есть несколько вариантов.
1) Если у вас тип таблиц InnoDB.
Используйте утилиту Percona xtrabackup, которая делает бэкапы очень быстро, при этом не блокирует таблицы. Вот как работает эта утилита. От простого копирования содержимого директории с данными (/var/lib/mysql) данный подход отличается тем, что данные копируются особым образом, таким же, как с ними работает движок InnoDB и вместе с данными копируются лог-файлы. На 2-ом этапе подготовки (prepare) xtrabackup запускает встроенный в него слегка модифицированный InnoDB-движок и выполняет crash-recovery, используя информацию из лог-файлов. Этот шаг необходим, поскольку в процессе копирования mysql-сервер продолжает изменять базу и без него была бы нарушена целостность данных.
2) Репликация.
Так же для бэкапа можно использовать репликацию баз на другой сервер (или на тот же, но поднять вторую копию сервера mysql на другом порту). Этот способ подходит для любого типа таблиц, но требует наличия второго экземпляра БД.
3) Snapshot
Если ваша ФС поддерживает снапшоты, то можно делать так: разместить БД на отдельной ФС и делать снапшоты только этой ФС. Очень удобно и быстро это делается на zfs.
4) mysqldump (нежелательный)
Если так уже сложилось, что нужно делать через mysqldump, то рекомендую делать это так:
#mysqldump --routines --triggers -uUSER -pPASS dbname | bzip2 > dbname.sql.bz2
, где параметр –routines – означает делать дамп хранимых процедур (по умолчанию они не делаются), а –triggers – тригеров (по умолчанию они делаются, но лучше принудительно включить). Но при её выполнении блокируются таблицы.
Решение этой проблемы в случае использования движка хранения данных InnoDB весьма тривиально. У mysqldump есть опция –single-transaction, которая добавляет SQL-выражение BEGIN перед дампом всех данных из БД, что позволяет получить дамп одной транзакцией, без блокировки таблиц. Но будьте осторожны при использовании single-transaction (тут есть bug, когда данные оказываются неконсистентные, исправлено в 5.7.41 и 8.0.33), особенно, если у вас в таблице есть текст в различных кодировках. Вот выдержка из сайта percona.com:
Q: But if you use the —single–transaction option for mysqldump, you can only specify one character set in the default?
A: Yes, and this is OK: all data will be converted into this character set. And then, when you will restore the dump, it will be converted back to the character set specified in column definitions.
Можно немного ускорить дамп, выполнив копирование через pipe и сразу вставлять строки в базу.
– на конечном хосте выполняем такую команду:
# nc -4dl 1.1.1.1 6625 | gunzip -c | mysql -p mydatabase
где 1.1.1.1 и 6625 – IP и порт куда будут передаваться данные
PS. Под linux использовать так:
# nc -l -p 6625 | gunzip -c | mysql -p mydatabase
– на исходном:
# mysqldump -p mydatabase | gzip -c | nc 1.1.1.1 6625
Для MyISAM можно воспользоваться опцией –lock-tables, которая блокирует таблицы с помощью READ LOCAL, что позволяет выполнять запросы INSERT в MyISAM-таблицы во время выполнения дампа.
P.S. Кроме того, при дампе больших таблиц с помощью mysqldump рекомендуется использовать опцию –quick
Если в базу дампе есть большие INSERT’ы, то используя опцию skip-extended-insert, можно их подробить на более мелкие.
Итого, что бы не задумываться, можно запускать дамп так:
# mysqldump --routines --triggers --lock-tables --single-transaction -uUSER -pPASS dbname | bzip2 > dbname.sql.bz2
5) Паралельный дамп нескольких баз одновременно.
Если ресурсы позволяют, то можно выполнять одновременно. Для этого можно использовать утилиту gnu parallel или mk-parallel-dump из набора maatkit
6) mydumper
- mydumper is a tool used for backing up MySQL database servers much
faster than the mysqldump tool distributed with MySQL. It also has the
capability to retrieve the binary logs from the remote server at the same time
as the dump itself. The advantages of mydumper are: - Parallelism (hence, speed) and performance (avoids expensive character set conversion routines, efficient code overall)
- Easier to manage output (separate files for tables, dump metadata, etc, easy to view/parse data)
- Consistency – maintains snapshot across all threads, provides accurate master and slave log positions, etc
- Manageability – supports PCRE for specifying database and tables inclusions and exclusions
7) Быстрое восстановление
Достаточно неплохой обзор и сравнение методов восстановления.
А здесь восстановление через binlog
