МДК.11.01 - 27 - Резервное копирование и восстановление

Введение

Резервное копирование и восстановление данных — это ключевые аспекты управления базами данных, которые обеспечивают защиту данных от потери в случае сбоев, ошибок или атак. В этой лекции мы рассмотрим основные методы резервного копирования и восстановления данных в MySQL, включая использование утилиты mysqldump, современные альтернативы и автоматизацию процессов. Особое внимание уделим репликации как инструменту создания резервных копий.

Основные аспекты резервного копирования

1. Что такое резервное копирование?

Резервное копирование — это процесс создания копий данных, которые можно использовать для восстановления системы в случае потери или повреждения данных. Это важный инструмент для обеспечения целостности и доступности информации.

Инфо

Регулярное резервное копирование помогает минимизировать риски, связанные с аппаратными сбоями, человеческими ошибками, программными сбоями и атаками (например, ransomware).

2. Инструменты для резервного копирования

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

mysqldump: Универсальная утилита для создания SQL-дампов.
mysqlpump: Улучшенная версия mysqldump с поддержкой параллельного копирования.
Percona XtraBackup: Инструмент для "горячего" резервного копирования без остановки сервера.
Репликация: Использование репликации для создания резервных копий на ведомых серверах.

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

1. Что такое `mysqldump`?

mysqldump — это встроенная утилита MySQL для создания резервных копий баз данных. Она генерирует SQL-файл, содержащий все команды, необходимые для восстановления структуры и данных базы данных.

Преимущества mysqldump:

Простота использования.
Кроссплатформенность (работает на Windows, Linux и macOS).
Возможность создания как полных, так и частичных резервных копий.
Лёгкость автоматизации.

Недостатки:

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

2. Пример создания резервной копии

Базовый синтаксис

mysqldump -u username -p database_name > backup_file.sql

-u username: Указывает имя пользователя MySQL.
-p: Запрашивает пароль при выполнении команды.
database_name: Имя базы данных, которую нужно скопировать.
backup_file.sql: Файл, в который будет сохранена резервная копия.

Пример для базы данных `admin_practice`

mysqldump -u root -p admin_practice > backup.sql

3. Дополнительные параметры `mysqldump`

Чтобы сделать резервное копирование более эффективным, можно использовать дополнительные параметры:

Создание дампа только структуры (без данных):
```
mysqldump -u root -p --no-data admin_practice > structure_backup.sql
```
Создание дампа только данных (без структуры):
```
mysqldump -u root -p --no-create-info admin_practice > data_backup.sql
```
Добавление блокировок таблиц для согласованности данных:
```
mysqldump -u root -p --lock-tables admin_practice > backup.sql
```
Использование транзакций для согласованности данных:
```
mysqldump -u root -p --single-transaction admin_practice > backup.sql
```

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

mysqldump -u root -p --all-databases > all_databases_backup.sql

Резервное копирование определённых таблиц:
```
mysqldump -u root -p admin_practice table1 table2 > tables_backup.sql
```

Восстановление из резервной копии

1. Базовый синтаксис

mysql -u username -p database_name < backup_file.sql

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

mysql -u root -p admin_practice < backup.sql

Особенности восстановления на Windows

На Windows символ < может работать некорректно, особенно если путь содержит пробелы или специальные символы. В таких случаях используйте альтернативные методы:

Использование команды SOURCE внутри MySQL CLI:
```
mysql -u root -p
```
После входа в MySQL CLI выполните:
```
USE admin_practice;
SOURCE C:/path/to/backup.sql;
```
Явное указание файла через параметр -e:
```
mysql -u root -p admin_practice -e "SOURCE C:/path/to/backup.sql"
```

Проверка целостности резервной копии

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

Проверка структуры таблиц:
```
SHOW TABLES;
```

Проверка данных в таблицах:

SELECT COUNT(*) FROM users;
SELECT * FROM actions LIMIT 10;

Репликация как инструмент резервного копирования

1. Что такое репликация?

Репликация — это процесс синхронизации данных между несколькими серверами MySQL. Она позволяет создавать резервные копии на ведомых серверах (slave), которые постоянно обновляются данными с ведущего сервера (master).

Инфо

Репликация особенно полезна для больших баз данных, где использование mysqldump может быть неэффективным.

2. Настройка репликации

Настройка на ведущем сервере (Master)

Откройте файл конфигурации MySQL (my.cnf или my.ini) и добавьте следующие строки:
```
server-id=1
log-bin=mysql-bin
binlog-do-db=admin_practice
```

Перезапустите сервер MySQL:

Linux:
```
sudo systemctl restart mysql
```
Windows:
```
net stop mysql && net start mysql
```

Создайте пользователя для репликации:

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;

Получите текущую позицию бинарного лога:
```
SHOW MASTER STATUS;
```

Настройка на ведомом сервере (Slave)

Откройте файл конфигурации MySQL и добавьте:
```
server-id=2
```

Перезапустите сервер MySQL:

Linux:
```
sudo systemctl restart mysql
```
Windows:
```
net stop mysql && net start mysql
```

Настройте репликацию:

CHANGE MASTER TO
MASTER_HOST='master_host',
MASTER_USER='replica_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1234;
START SLAVE;

Проверьте статус репликации:
```
SHOW SLAVE STATUS\G;
```

Автоматизация резервного копирования

1. На Linux

Используйте cron для создания автоматических задач. Например, добавьте следующую строку в crontab:

0 2 * * * mysqldump -u root -pPASSWORD admin_practice > /path/to/backup.sql

Эта команда создаст резервную копию каждый день в 2 часа ночи.

2. На Windows

Используйте Планировщик задач:

Создайте .bat файл с командой резервного копирования:
```
mysqldump -u root -pPASSWORD admin_practice > C:\path\to\backup.sql
```
Настройте задачу в Планировщике задач для запуска этого файла по расписанию.

Практическая часть

1. Создание тестовой базы данных

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

CREATE DATABASE admin_practice;
USE admin_practice;
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE actions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
INSERT INTO users (username) VALUES ('admin'), ('test_user');
INSERT INTO actions (user_id, action) VALUES
(1, 'Logged in'),
(2, 'Viewed dashboard'),
(1, 'Logged out');

2. Резервное копирование

Описание: Используйте утилиту mysqldump для создания резервных копий базы данных. На Windows важно учитывать особенности работы с путями и символами перенаправления.

Шаги тестирования:

Создайте дамп базы данных:
- Откройте командную строку (Command Prompt) или PowerShell.
- Выполните команду:
```
mysqldump -u root -p admin_practice > C:\path\to\backup.sql
```
  Убедитесь, что путь к файлу указан корректно. Если путь содержит пробелы, заключите его в двойные кавычки:
```
mysqldump -u root -p admin_practice > "C:\My Folder\backup.sql"
```
Проверьте содержимое файла backup.sql:
- Откройте файл backup.sql в текстовом редакторе (например, Notepad++ или Visual Studio Code).
- Убедитесь, что файл содержит SQL-скрипт со всеми командами для восстановления базы данных.
Удалите тестовую базу данных:
- Подключитесь к MySQL CLI:
```
mysql -u root -p
```
- Удалите базу данных:
```
DROP DATABASE admin_practice;
```
Восстановите базу данных из дампа:
- Воспользуйтесь одним из следующих методов:
  - Метод 1: Использование команды SOURCE внутри MySQL CLI:
```
mysql -u root -p
```
    После входа в MySQL CLI выполните:
```
CREATE DATABASE admin_practice;
USE admin_practice;
SOURCE C:/path/to/backup.sql;
```
  - Метод 2: Явное указание файла через параметр -e:
```
mysql -u root -p admin_practice < "C:\path\to\backup.sql"
```
    Если возникают проблемы с символом <, используйте альтернативный синтаксис:
```
mysql -u root -p -e "SOURCE C:/path/to/backup.sql"
```
Проверьте, что база данных и данные восстановлены:
- Подключитесь к MySQL CLI:
```
mysql -u root -p
```
- Проверьте восстановление данных:
```
USE admin_practice;
SELECT * FROM users;
SELECT * FROM actions LIMIT 10;
```

Примечание

Убедитесь, что путь к файлу backup.sql указан правильно. Если возникают проблемы с правами доступа, запустите командную строку или терминал с правами администратора.

Заключение

В этой лекции мы рассмотрели основные методы резервного копирования и восстановления данных в MySQL. Мы изучили использование утилиты mysqldump, современные альтернативы и способы автоматизации процессов. Особое внимание уделили репликации как инструменту создания резервных копий. Эти навыки помогут вам защитить ваши базы данных от потери данных и обеспечить их доступность.

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