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

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

Мы не можем просто переместить файлы данных в datadir, так как таблицы не зарегистрированы в движках, поэтому база данных выдаст ошибку. В настоящее время единственный эффективный метод — это полное восстановление в тестовой базе данных, а затем экспорт базы данных, требующей восстановления, или создание частичной резервной копии. Это было протестировано только с InnoDB. Кроме того, если у вас есть хранимые процедуры или триггеры, то их необходимо будет удалить и воссоздать.

Некоторые из проблем, которые решает этот метод:

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

Одиночный узел

Ниже приведен процесс восстановления отдельной базы данных.

Сначала нам потребуется структура таблиц из резервной копии mariadb-dump с опцией --no-data. Рекомендуется выполнять это по крайней мере один раз в день или каждые шесть часов с помощью cronjob. Так как это только структура, она будет очень быстрой.

mariadb-dump -u root -p --all-databases --no-data > nodata.sql

Используя SED, чтобы вернуть только необходимую структуру таблиц, затем используйте vim или другой текстовый редактор, чтобы убедиться, что ничего не осталось.

sed -n '/Current Database: `DATABASENAME`/, /Current Database:/p' nodata.sql > trimednodata.sql
vim trimednodata.sql

Я не буду описывать процесс резервного копирования, так как он описан ранее в других документах, таких как full-backup-and-restore-with-mariabackup. Подготовьте резервную копию с любыми incremental-backup-and-restores, которые у вас есть, а затем выполните следующее в папке полной резервной копии с опцией --export, чтобы сгенерировать файлы с расширением .cfg, которые InnoDB будет искать.

Mariabackup --prepare --export --target-dir=/media/backups/fullbackupfolder

После выполнения этих шагов мы можем импортировать структуру таблиц. Если вы использовали опцию --all-databases, то вам необходимо либо использовать SED, либо открыть файл в текстовом редакторе и экспортировать необходимые таблицы. Вам также необходимо войти в базу данных и создать базу данных, если файл дампа её не содержит. Выполните следующую команду:

Mysql -u root -p schema_name < nodata.sql

После того, как структура будет в базе данных, мы теперь зарегистрировали таблицы в движке. Далее мы выполним следующие операторы в базе данных information_schema, чтобы экспортировать операторы для импорта/удаления табличных пространств и удаления/создания внешних ключей, которые мы будем использовать позже. (Измените предложение CONSTRAINT_SCHEMA и TABLE_SCHEMA в условии WHERE на базу данных, которую вы восстанавливаете. Также добавьте следующие строки после вашего SELECT и перед FROM, чтобы MariaDB экспортировала файлы в операционную систему)

SELECT ...
into outfile '/tmp/filename.sql'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM ...

Следующие операторы нам понадобятся позже.

USE information_schema;
select concat("ALTER TABLE ",table_name," DISCARD TABLESPACE;")  AS discard_tablespace
from information_schema.tables 
where TABLE_SCHEMA="DATABASENAME";

select concat("ALTER TABLE ",table_name," IMPORT TABLESPACE;") AS import_tablespace
from information_schema.tables 
where TABLE_SCHEMA="DATABASENAME";

SELECT 
concat ("ALTER TABLE ", rc.CONSTRAINT_SCHEMA, ".",rc.TABLE_NAME," DROP FOREIGN KEY ", rc.CONSTRAINT_NAME,";") AS drop_keys
FROM REFERENTIAL_CONSTRAINTS AS rc
where CONSTRAINT_SCHEMA = 'DATABASENAME';

SELECT
CONCAT ("ALTER TABLE ", 
KCU.CONSTRAINT_SCHEMA, ".",
KCU.TABLE_NAME," 
ADD CONSTRAINT ", 
KCU.CONSTRAINT_NAME, " 
FOREIGN KEY ", "
(`",KCU.COLUMN_NAME,"`)", " 
REFERENCES `",REFERENCED_TABLE_NAME,"` 
(`",REFERENCED_COLUMN_NAME,"`)" ," 
ON UPDATE " ,(SELECT UPDATE_RULE FROM REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA)," 
ON DELETE ",(SELECT DELETE_RULE FROM REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA),";") AS add_keys
FROM KEY_COLUMN_USAGE AS KCU
WHERE KCU.CONSTRAINT_SCHEMA = 'DATABASENAME'
AND KCU.POSITION_IN_UNIQUE_CONSTRAINT >= 0
AND KCU.CONSTRAINT_NAME NOT LIKE 'PRIMARY';

После выполнения этих операторов и их экспорта в каталог Linux или копирования из графического интерфейса.

Выполните операторы ALTER DROP KEYS в базе данных

ALTER TABLE schemaname.tablename DROP FOREIGN KEY key_name;
...

После завершения выполните операторы DROP TABLE SPACE в базе данных

ALTER TABLE test DISCARD TABLESPACE;
...

Выйдите из базы данных и перейдите в каталог расположения полной резервной копии. Выполните следующие команды для копирования всех файлов .cfg и .ibd в datadir, например, /var/lib/mysql/testdatabase (измените расположение datadir, если необходимо). Узнайте больше о файлах, создаваемых Mariabackup, в files-created-by-mariabackup

cp *.cfg /var/lib/mysql
cp *.ibd /var/lib/mysql

После перемещения файлов очень важно, чтобы MySQL владел файлами, иначе он не сможет получить к ним доступ и возникнет ошибка при импорте табличных пространств.

sudo chown -R mysql:mysql /var/lib/mysql

Выполните операторы импорта табличных пространств в базе данных.

ALTER TABLE test IMPORT TABLESPACE;
...

Выполните операторы добавления ключей в базе данных

ALTER TABLE schmeaname.tablename ADD CONSTRAINT key_name FOREIGN KEY (`column_name`) REFERENCES `foreign_table` (`colum_name`) ON UPDATE NO ACTION ON DELETE NO ACTION;
...

Мы успешно восстановили отдельную базу данных. Для проверки правильности выполнения операции мы можем выполнить основные проверки некоторых таблиц.

use database
SELECT * from test limit 10;

Узлы-реплики

Если у вас настроена пара мастер-реплики, лучше всего следовать вышеуказанным шагам для узла-мастера, а затем выполнить либо полное mariadb-dump, либо создать новую полную резервную копию mariabackup и восстановить её на реплике. Дополнительную информацию о восстановлении реплики с помощью mariabackup можно найти в Настройка реплики с использованием Mariabackup.

После выполнения команды ниже, скопируйте её на реплику и используйте команду LESS linux для получения оператора change master. Помните, что необходимо следовать этой процедуре: остановить реплику > восстановить данные > выполнить оператор CHANGE MASTER > запустить реплику снова.

mariadb-dump -u user -p --single-transaction --master-data=2 > fullbackup.sql

Пожалуйста, следуйте Настройка реплики с использованием Mariabackup по восстановлению реплики с помощью Mariabackup.

$ mariabackup --backup \
   --slave-info --safe-slave-backup \
   --target-dir=/var/mariadb/backup/ \
   --user=mariabackup --password=mypassword

Кластер Galera

Для работы этого процесса с кластером Galera, нам необходимо понимать, что некоторые операторы не реплицируются между узлами Galera. Один из них — DISCARD и IMPORT для операторов ALTER TABLES, и эти операторы необходимо выполнить на всех узлах. Мы также должны выполнить шаги на уровне операционной системы на каждом сервере, как показано ниже.

Выполните операторы ALTER DROP KEYS на ОДНОМ узле, так как они реплицируются.

ALTER TABLE schemaname.tablename DROP FOREIGN KEY key_name;
...

После завершения выполните операторы DROP TABLE SPACE на КАЖДОМ узле, так как они не реплицируются.

ALTER TABLE test DISCARD TABLESPACE;
...

Выйдите из базы данных и перейдите в каталог расположения полной резервной копии. Выполните следующие команды для копирования всех файлов .cfg и .ibd в datadir, например, /var/lib/mysql/testdatabase (измените расположение datadir, если необходимо). Узнайте больше о файлах, создаваемых Mariabackup, в files-created-by-mariabackup. Этот шаг должен быть выполнен на всех узлах. Вам нужно скопировать файлы резервной копии на каждый узел; мы можем использовать одну и ту же резервную копию на всех узлах.

cp *.cfg /var/lib/mysql
cp *.ibd /var/lib/mysql

После перемещения файлов очень важно, чтобы MySQL владел файлами, иначе он не сможет получить к ним доступ и возникнет ошибка при импорте табличных пространств.

sudo chown -R mysql:mysql /var/lib/mysql

Выполните операторы импорта табличных пространств на КАЖДОМ узле.

ALTER TABLE test IMPORT TABLESPACE;
...

Выполните операторы добавления ключей на ОДНОМ узле

ALTER TABLE schmeaname.tablename ADD CONSTRAINT key_name FOREIGN KEY (`column_name`) REFERENCES `foreign_table` (`colum_name`) ON UPDATE NO ACTION ON DELETE NO ACTION;
...