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

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

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

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

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

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

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

mysqldump > file.sql

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

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

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

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

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

mysqldump -u root -p database_name > database_name.sql

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

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-databases для резервного копирования всех баз данных 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-20180617.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

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 в указанное время.

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

1. Создайте файл с именем .my.cnf в вашем домашнем каталоге пользователя:

   sudo nano ~/.my.cnf

   Скопируйте и вставьте следующий текст в файл.my.cnf.

   user = dbuser password = dbpasswd

   Не забудьте заменить dbuser и dbpasswd на пользователя базы данных и пароль пользователя.

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

   chmod 600 ~/.my.cnf

   Создайте каталог для хранения резервных копий:

   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 вашим реальным именем пользователя.

Вы также можете создать еще один cronjob, чтобы удалить любые резервные копии старше 30 дней:

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

Конечно, вам нужно настроить команду в соответствии с вашим местоположением резервной копии и именами файлов. Чтобы узнать больше о команде find, ознакомьтесь с нашим Руководством по поиску файлов в Linux с помощью командной строки.

Вывод

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

Вы также можете проверить учебник о том, как сбросить пароль root для MySQL, если вы его забыли.

бэкап mysql mariadb mysqldump