scratchpads.ru

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

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

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

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

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

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

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

mysqldump [options] > file.sql

• 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. После успешной аутентификации начнется процесс дампа. В зависимости от размера базы данных процесс может занять некоторое время.

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

mysqldump database_name > database_name.sql

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

Для резервного копирования нескольких баз данных MySQL с помощью одной команды вам нужно использовать параметр --database за которым следует список баз данных, которые вы хотите --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 не предоставляет возможности резервного копирования всех баз данных в отдельные файлы, но мы легко достигаем этого с помощью простого цикла FOR bash :

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 . Общий синтаксис команды следующий:

mysql  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-databases и хотите восстановить одну базу данных из файла резервной копии, содержащего несколько баз данных, используйте параметр --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 в указанное время.

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

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

   sudo nano ~/.my.cnf

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

   [client] user = dbuser password = dbpasswd

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

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

   chmod 600 ~/.my.cnf

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

   mkdir ~/db_backups

4. Откройте ваш пользовательский файл 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 своим фактическим именем пользователя. Мы также избегаем знаков процента ( % ), потому что они имеют особое значение в crontab.

Вы также можете создать другое задание cron для удаления любых резервных копий старше 30 дней:

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

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

Выводы

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

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

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