Утилита mysqldump: как сделать дамп базы MySQL?

Случаются аварийные ситуации, когда информация из баз данных теряется или повреждается. Например, если привилегированный пользователь случайно удалил или переписал данные. Чтобы не потерять все свои наработки, нужно регулярно делать резервную копию проекта. В этом случае никакие непредвиденные ситуации не смогут выбить вас из колеи, потому что вы сможете восстановить необходимую информацию в любой момент.

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

Содержание:
1. Mysqldump: что это такое
2. Гайд по созданию дампа баз данных
3. Как восстановить базу данных из дампа
Заключение

1. Mysqldump: что это такое

Утилита mysqldump выполняет резервное копирование. С помощью операторов SQL можно воссоздать исходные определения объектов и табличные данные. Mysqldump выгружает базы данных, копирует их или передает на другой SQL-сервер. С помощью mysqldump также можно создавать резервные выборки в форматах CSV и XML.

С mysqldump удобно работать: вы можете просматривать или даже редактировать вывод перед тем, как восстановить базу данных. Также утилита позволяет копировать базы данных и создавать небольшие вариации существующих баз для тестирования каких-либо гипотез и решений. Mysqldump также может извлекать и выгружать содержимое отдельной таблицы строка за строкой или же извлекать все содержимое таблицы и буферизовать его в памяти перед тем, как сбросить.

Существуют и другие методы создания копий в MySQL, но они обладают недостатками:

1. Утилита hotcopy из MySQL Enterprise — отличный способ выполнить резервное копирование, правда нее придется заплатить.
2. Самостоятельное копирование каталогов данных проблематично при переходах между операционными системами, поскольку места назначения будут разными.
3. Экспорт базы данных в текстовый файл сохранит контент, но вам придется заново создавать структуру проекта.
4. Можно создавать бэкапы баз данных из GUI-программ, например, MySQL Workbench. Но все придется делать вручную.

С бесплатной mysqldump можно работать на macOS, Windows, нескольких версиях Linux и других менее популярных операционных системах. Скачать утилиту и посмотреть список всех доступных операционных систем и версий программы можно на официальном сайте. Также рекомендуем вам ознакомиться с курсом по работе с MySQL от наших друзей, школы IAMPM.

2. Гайд по созданию дампа баз данных

Mysqldump довольно прост в использовании. Рассмотрим несколько базовых синтаксисов команд mysqldump:

mysqldump -u USERNAME -pPASSWORD DBNAME > DBBACKUP.sql

mysqldump -u USERNAME -pPASSWORD --databases DB1 DB2 DB3.. >DBBACKUP.sql

mysqldump -u USERNAME -pPASSWORD --all-databases > ALLDBBACKUP.sql

Расшифровываем синтаксис командной строки:

• -u — указывает ваше имя пользователя;
• -p — указывает пароль;
• DBNAME — имя базы данных, дамп которой вы создаете;
• DBBACKUP.sql — имя файла создаваемой копии;
• -h — указывает имя хоста сервера MySQL;
• –Databases — определяет нужную базу данных;
• -all-databases — необходим для дампа всего массива баз данных;
• –Default-auth = plugin — используется для указания подключаемого модуля аутентификации на стороне клиента, который будет использоваться;
• –Compress — применяется для включения сжатия в протоколе сервер/клиент;
• -P — указывает номера порта, используемого для подключения к MySQL.

Теперь рассмотрим, как создавать бэкапы базы данных с помощью mysqldump.

Утилита предоставляет несколько вариантов копирования:

1. mysqldump может выгружать определенные таблицы;
2. с помощью утилиты можно скопировать непосредственно базы данных;
3. mysqldump может экспортировать весь массив данных MySQL.

Остановимся подробнее на каждом из перечисленных методов.

Как экспортировать таблицы

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

В зависимости от параметров, которые передаются с командой, вам могут потребоваться расширенные права для использования этой опции. Но если речь идет о базовом резервном копировании, как в нашем случае, то прав на полное чтение должно быть достаточно. Запустите программу и напечатайте строку, чтобы создать копию таблицы. Команда выглядит так:

mysqldump [options] your_db_name [tbl_name ...]

В этой команде [options] следует заменить списком допустимых имен или флагов параметров, наиболее распространенными из которых являются -u и -p. [tbl_name…]многоточие заменяется именами таблиц, написанными через пробел.. Вот пример создания дампа таблицы с order и products для базы данных под названием store:

mysqldump -u your_username -p store orders products > name_of_file.sql

Когда вы введете команду, то вам предложат написать пароль, поскольку он не передается через -p. Символ > — это перенаправление вывода, используемое для создания файла дампа.

Как экспортировать базы данных

Шаги по экспорту базы данных аналогичны действиям по экспорту таблиц. Сама команда изменяется лишь незначительно:

mysqldump -u your_username -p --databases db_name1 db_name2 db_name3 > name_of_file.sql

В указанной команде экспортируемая база данных прописывается после параметра –databases. Если необходимо выгрузить несколько баз, то они перечисляются через пробел.

Как экспортировать весь сервер

Чтобы экспортировать весь сервер MySQL, введите эту команду:

mysqldump -u your_username -p --all-databases

При работе с дампами или при любых миграциях есть важный момент, о котором стоит помнить. Например, есть параметр –compatible. Его используют, чтобы выгружаемый файл можно было использовать в предыдущих версиях MySQL. Если вы пользуетесь PowerShell в Windows, то нужен параметр –result-file = name_of_file.sql. Это нужно, чтобы выходной файл создавался в формате кодировки ASCII и корректно загружался на других системах.

Копирование всего массива MySQL в отдельные файлы

Хотя через mysqldump не получится скопировать все данные MySQL в отдельные файлы, это легко можно сделать через цикл FOR в bash:

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

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

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

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

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

Так вы сохраните файл, где последняя часть названия это год, месяц и день создания копии: database_name-20210930.sql.

Создать дамп схемы базы данных без самих данных

Можно создать дамп только схемы базы данных, которая не содержит самих значений. Для этого нужно прописать параметр –no-data. Введите следующий запрос, чтобы сгенерировать дамп схемы:

mysqldump -u your_username -p --no-data database_name > name_of_file.sql

3. Как восстановить базу данных из дампа

После того, как вы создали копию базы данных, можно загружать файл с этими данными в MySQL в любой момент. Разберемся, как это делается.
Базовый синтаксис команды выглядит вот так:

mysql  database_name < file.sql

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

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

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

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

Восстановление базы данных MySQL из бэкапа всего сервера

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

mysql --one-database database_name < all_databases.sql

Одновременный экспорт и импорт базы данных MySQL

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

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

Эта команда отправит копию базы данных клиенту MySQL на удаленном сервере и загрузит его в базу remote_database_name. Перед запуском команды проверьте, существует ли база данных с таким именем на удаленном сервере.

Автоматизируйте создание бэкапов

Чтобы постоянно не прописывать команды для копирования файлов вручную, лучше автоматизировать этот процесс. Для этого можно использовать cronjob. Он будет автоматически запускать и выполнять резервное копирование в указанное время.

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

• Создайте пустой файл. Назовем его .my.cnf:

sudo nano ~/.my.cnf

• Скопируйте и вставьте этот текст в созданный файл:

[client]
user = dbuser
password = dbpasswd

Замените значение dbuser и dbpasswd на свои имя и пароль от учетной записи.

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

chmod 600 ~/.my.cnf

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

crontab -e

• Добавьте cronjob. С помощью этой команды копия вашей базы данных будет создаваться каждый день в определенное время в примере это 3 часа ночи.:

0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +\%Y\%m\%d).sql

• Также можно создать другой cronjob для удаления дампов базы данных по истечению определенного срока. В примере мы указали, что файл удаляется после 30 дней:

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

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

Заключение

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

В гайде мы рассмотрели только основы использования утилиты, это поможет самостоятельно разобраться в азах работы Mysqldump тем, кто работает с базами данных MySQL. Но если вы хотите углубиться в изучение, то курс от наших друзей IAMPM хорошо подойдет для этих целей.

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