Как сделать резервную копию и восстановить базу данных MySQL с помощью Mysqldump

Делай бекапы - не будет факапов

Февраль 23, 2020 Мерион Нетворкс

6 минут чтения

В этом руководстве мы рассказываем, как выполнить резервное копирование и восстановление баз данных MySQL или MariaDB из командной строки с помощью утилиты mysqldump.

Обучайся в Merion Academy

Пройди курс по
сетевым технологиям

Начать

Файлы резервных копий, созданные утилитой mysqldump, представляют собой набор операторов SQL, которые можно использовать для воссоздания исходной базы данных. Команда mysqldump также может генерировать файлы в формате CSV и XML. Вы также можете использовать утилиту mysqldump для переноса вашей базы данных MySQL на другой сервер MySQL.

Синтаксис команды Mysqldump

Прежде чем приступить к использованию команды mysqldump, начнем с обзора основного синтаксиса.

Выражения утилиты mysqldump имеют следующую форму:

mysqldump [options] > file.sql

options - параметры mysqldump
file.sql - дамп (резервная копия) файла

Для использования команды mysqldump сервер MySQL должен быть доступен и запущен.

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

Наиболее распространенный вариант использования инструмента mysqldump - резервное копирование одной базы данных.

Например, чтобы создать резервную копию базы данных с именем database_name, используя пользователя root, и сохранить ее в файл с именем database_name.sql, вы должны выполнить следующую команду:

mysqldump -u root -p database_name > database_name.sql

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

Если вы вошли в систему как тот же пользователь, которого вы используете для выполнения экспорта, и этот пользователь не требует пароля, вы можете пропустить опции -u и -p:

mysqldump database_name > database_name.sql

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

ля резервного копирования нескольких баз данных MySQL одной командой вам нужно использовать параметр --database, за которым следует список баз данных, которые вы хотите сделать резервную копию. Каждое имя базы данных должно быть разделено пробелом.

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

Команда выше создаст файл дампа, содержащий обе базы данных.

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

Используйте опцию --all-database для резервного копирования всех баз данных MySQL:

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

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

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

Утилита mysqldump не предоставляет возможность резервного копирования всех баз данных в отдельные файлы, но мы легко достигаем этого с помощью простого цикла bash FOR:

for DB in $(mysql -e 'show databases' -s --skip-column-names); do
    mysqldump $DB > "$DB.sql";
done

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

Создание сжатой резервной копии базы данных MySQL

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

mysqldump database_name | gzip > database_name.sql.gz

Создать резервную копию с отметкой времени

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

mysqldump  database_name > database_name-$(date +%Y%m%d).sql

Команда выше создаст файл в следующем формате database_name-20200223.sql

Восстановление дампа MySQL

Вы можете восстановить дамп MySQL с помощью инструмента mysql. Общий синтаксис команды выглядит следующим образом:

mysqld  database_name < file.sql

В большинстве случаев вам необходимо создать базу данных куда вы будете производить импорт. Если база данных уже существует, сначала вам нужно удалить ее.

В следующем примере первая команда создаст базу данных с именем database_name, а затем импортирует в нее дамп database_name.sql:

mysql -u root -p -e "create database database_name";
mysql -u root -p database_name < database_name.sql

Восстановление одной базы данных MySQL из полного дампа MySQL

Если вы создали резервную копию всех своих баз данных с помощью параметра -all-database и хотите восстановить одну базу данных из файла резервной копии, который содержит несколько баз данных, используйте параметр --one-database, как показано ниже:

mysql --one-database database_name < all_databases.sql

Экспорт и импорт базы данных MySQL одной командой

Вместо того, чтобы создавать файл дампа из одной базы данных и затем импортировать резервную копию в другую базу данных MySQL, вы можете использовать следующую однострочную команду:

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

Команда выше передаст вывод клиенту mysql на удаленном хосте и импортирует его в базу данных с именем remote_database_name. Перед выполнением команды убедитесь, что база данных уже существует на удаленном сервере.

Автоматизация резервного копирования с помощью Cron

Автоматизация процесса резервного копирования баз данных так же проста, как создание задания cron, которое будет запускать команду mysqldump в указанное время.

Подробно про cron можно прочитать в нашей статье.

Чтобы настроить автоматическое резервное копирование базы данных MySQL с помощью cronjob, выполните следующие действия:

Создайте файл с именем .my.cnf в вашем домашнем каталоге пользователя:
```
sudo nano ~/.my.cnf
```
Скопируйте и вставьте следующий текст в файл .my.cnf.
```
[client]
user = dbuser
password = dbpasswd
```
Не забудьте заменить dbuser и dbpasswd на пользователя базы данных и пароль пользователя.
Ограничьте права доступа к файлу учетных данных, чтобы только ваш пользователь имел к нему доступ, используя команду cmod (подробнее про которую можно прочесть тут):
```
chmod 600 ~/.my.cnf
```
Создайте каталог для хранения резервных копий при помощи комадны mkdir (про нее тоже есть статья):
```
mkdir ~/db_backups
```
Откройте ваш пользовательский файл crontab:
```
crontab -e
```
Добавьте следующее задание cron, которое будет создавать резервную копию имени базы данных mydb каждый день в 3 часа ночи:
```
0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql	
```
Не забудьте заменить username вашим реальным именем пользователя. Вы также можете создать еще один cron job, чтобы удалить любые резервные копии старше 30 дней:
```
find /path/to/backups -type f -name "*.sql" -mtime +30 -delete
```
Конечно, вам нужно настроить команду в соответствии с вашим местоположением резервной копии и именами файлов. Чтобы узнать больше о команде find, ознакомьтесь с нашим Руководством по поиску файлов в Linux с помощью командной строки.

Заключение

Это руководство охватывает только основы, но оно должно быть хорошим началом для тех, кто хочет научиться создавать и восстанавливать базы данных MySQL из командной строки с помощью утилиты mysqldump. Если вы хотите найти больше материалов про базы данных, то просто наберите sql в нашем поиске!