Оптимизация MySQL сервера. Часть 7. Резервное копирование MySQL

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

Эта публикация сугубо мой конспект прочитанной литературы. Информация может быть не очень структурирована и удобочитаема. Материал публикации предоставляется без каких-либо гарантий со стороны автора.

Список первоисточников

1. O’Reilly High Performance MySQL 3rd Edition.
2. O’Reilly High Performance MySQL 4rd Edition.

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

В качестве подопытное тестовой Бд я буду использовать базу Sakila Sample Database.

mysqldump

Утилита mysqldump – самый базовый инструмент. Он есть в стандартном комплекте поставки дистрибутива и используется очень часто в качестве основного инструмента резервного копирования. Да, у него есть свои особенности, но он прост и понятен.

Достоинства mysqldump:

1. Доступность “из коробки”.

2. Простота использования.

3. Большое количество документации.

Недостатки mysqldump:

1. Отсутствие инкрементальных или разностных резервных копий.

2. Скорость работы на больших базах данных.

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

Самый базовый пример создания резервное копии базы данных:

mysqldump sakila > sakila.sql

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

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

mysqldump --single-transaction sakila > sakila.sql

Очень часто вам необходимо указать логин и пароль пользователя для подключения к базе данных для выполнения операции резервного копирования. Вы можете передать имя пользователя через параметр -u, а также указать запрос интерактивного ввода пароля через параметр -p:

mysqldump -u root -p --single-transaction sakila > sakila.sql

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

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

mysqldump --single-transaction --databases sakila tst_db > db_backups.sql

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

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

mysqldump --single-transaction --no-data sakila > sakila.sql

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

Вариант резервного копирования отдельной таблицы из базы данных:

mysqldump --single-transaction sakila city > sakila_city.sql

Восстановление базы данных

Пример восстановление базы данных:

mysql sakila < sakila.sql

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

Восстановление отдельной таблицы:

mysql sakila < sakila_payment.sql

Percona XtraBackup

Percona XtraBackup – это сторонняя утилита для резервного копирования баз данных MySQL.

Достоинства Percona XtraBackup:

1. Большое количество настроек для оптимизации процесса резервного копирования.

2. Есть возможность выполнять сжатие или шифрование резервной копии.

3. Поддержка инкрементального типа резервного копирования.

Недостатки Percona XtraBackup:

1. Сложно отнести к недостаткам, но все же т.к. Percona XtraBackup сторонний продукт, то необходим установка из пакета или исходников. Вполне может быть, что для каких-то дистрибутивов нет готового пакета.

Установка Percona XtraBackup (Ubuntu Server 22.04)

Доступные варианты установки приведены в документации к продукту.

Установка Percona XtraBackup включает следующие шаги:

sudo apt update
sudo apt install -y libcurl4-openssl-dev
wget http://archive.ubuntu.com/ubuntu/pool/main/o/openssl/libssl1.1_1.1.0g-2ubuntu4_amd64.deb
sudo dpkg -i libssl1.1_1.1.0g-2ubuntu4_amd64.deb
wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.26-18/binary/debian/focal/x86_64/percona-xtrabackup-80_8.0.26-18-1.focal_amd64.deb
sudo dpkg -i percona-xtrabackup-80_8.0.26-18-1.focal_amd64.deb

Проверим, что Percona XtraBackup установилась успешно:

xtrabackup -v

root@mysql01:/home/roman# xtrabackup -v
2024-01-15T15:24:49.491582-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql --innodb_buffer_pool_size=2G --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT --open_files_limit=65535 --innodb_flush_log_at_trx_commit=1 --server-id=1 
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)

Резервное копирование с помощью Percona XtraBackup

Полное резервное копирование сервера:

xtrabackup --backup --target-dir=/data/bkp/

Содержимое директории /data/bkp посл завершения резервного копирования:

root@mysql01:/home/roman# ls /data/bkp/
backup-my.cnf  binlog.000004  binlog.index  ib_buffer_pool  ibdata1  mysql  mysql.ibd  performance_schema  sys  undo_001  undo_002  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile  xtrabackup_tablespaces

Если вы не хотите хранить все файлы большой кучей в одной директории, то можете собрать их в один файл. В терминологии Percona XtraBackup данный способ называется потоковая резервная копия (streaming backup). Выполняется следующим образом:

xtrabackup --backup --stream=xbstream > /data/bkp/mysql-bkp-$(date +%F).xbstream

Содержимое директории после потокового резервного копирования:

root@mysql01:/home/roman# ls /data/bkp/
mysql-bkp-2024-01-15.xbstream

Также вы можете выполнить компрессию резервное копии, добавив опцию –compress:

xtrabackup --backup --stream=xbstream > /data/bkp/mysql-bkp-$(date +%F).xbstream

Сравнение резервной сжатой резервной копии и резервной копии без сжатия. Набо данных в обоих случаях идентичен:

root@mysql01:/home/roman# ls -lh /data/bkp/
total 73M
-rw-r--r-- 1 root root  71M Jan 15 16:06 mysql-bkp-2024-01-15.xbstream
-rw-r--r-- 1 root root 1.6M Jan 15 16:08 mysql-bkp-2024-01-15.xbstream.compress

Есть еще ряд полезных опций. Например, –parallel и –compress-threads, которые выполняют работу в несколько потоков. Да, это увеличивает нагрузку на ЦП, но уменьшая время создания резервной копии.

Восстановление с помощью Percona XtraBackup

Для потоковой резервной копии предварительно её нужно распаковать через параметр -x:

xtrabackup -x < /data/bkp/mysql-bkp-2024-01-15.xbstream

Если вы выполняли сжатие потоковой резервной копии, то дополнительно укажите параметр –decompress:

xtrabackup -x --decompress < /data/bkp/mysql-bkp-2024-01-15.xbstream.compress

root@mysql01:/home/roman# xtrabackup -x --decompress < /data/bkp/mysql-bkp-2024-01-15.xbstream.compress
2024-01-15T16:35:32.319844-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --decompress=1 
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)
2024-01-15T16:35:32.421115-00:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

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

xtrabackup --prepare --target-dir=/data/bkp

Последний шаг – восстановление всех файлов в директорию с данными сервера MySQL:

xtrabackup --copy-back --target-dir=/data/bkp --datadir==/va/lib/mysql

mydumper

Страница проекта на GitHub.

Достоинства mydumper:

1. Поддержка многопоточности

2. Гранулярность восстановления.

3. Простота установки.

Недостатки mydumper:

1. Отсуствие разностного или инкрементального резервного копирования.

Установка mydumper

На странице проекта на GitHub представлены шаги для установки mydumper для популярных дистрибутивов. Я буду использовать дистрибутив Ubuntu Server 22.04. Шаги по установки следующие:

apt-get install libatomic1
release=$(curl -Ls -o /dev/null -w %{url_effective} https://github.com/mydumper/mydumper/releases/latest | cut -d'/' -f8)
wget https://github.com/mydumper/mydumper/releases/download/${release}/mydumper_${release:1}.$(lsb_release -cs)_amd64.deb
dpkg -i mydumper_${release:1}.$(lsb_release -cs)_amd64.deb

Резервное копирование с помощью mydumper

Самый простой вариант – резервное копирование всех баз данных:

mydumper

Итоговый набор данных будет помещен в отдельную директорию с разбиением на таблицы для каждой базы данных:

Можно выполнить фильтрацию по имени баз через параметр –regex:

mydumper --regex '^(^(test\.))'

Восстановление с помощью mydumper

Пример восстановления всех таблиц из набора резервного копирования через mydumper:

myloader --directory=export-20110614-094953 --overwrite-tables --user=root

Ссылки на другие статьи этого цикла

Часть 1. Тестирование производительности MySQL.

Часть 2. Профилирование нагрузки сервера MySQL.

Часть 3. Базовая конфигурация и оптимизация настроек MySQL.

Часть 4. Признаки проблем с производительностью MySQL.

Часть 5. Полезные запросы для MySQL.

Часть 6. Нюансы настройки репликации MySQL.