Linux бэкап базы данных mysql

Резервное копирование MySQL

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

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

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

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

Для экспорта информации из базы данных в формате SQL можно использовать утилиту mysqldump. Вот ее синтаксис:

$ mysqldump опции имя_базы [имя_таблицы] > файл.sql

По умолчанию утилита будет выводить все в стандартный вывод, поэтому нам нужно перенаправить эти данные в файл, что мы и делаем с помощью оператора «>». Опции указывают параметры аутентификации и работы, а имя базы и таблицы — данные которые нужно экспортировать. Теперь рассмотрим кратко опции, которые будем использовать:

• -A — копировать все таблицы из всех баз данных;
• -i — записывать дополнительную информацию в комментариях;
• -c — использовать имена колонок для инструкции INSERT;
• -a — включать все возможные опции в инструкцию CREATE TABLE;
• -k — отключает первичные ключи на время копирования;
• -e — использовать многострочный вариант инструкции INSERT;
• -f — продолжить даже после ошибки;
• -h — имя хоста, на котором расположен сервер баз данных, по умолчанию localhost;
• -n — не писать инструкции для создания базы данных;
• -t — не писать инструкции для создания таблиц;
• -d — не записывать данные таблиц, а только их структуру;
• -p — пароль базы данных;
• -P — порт сервера баз данных;
• -Q — брать все имена таблиц, баз данных, полей в кавычки;
• -X — использовать синтаксис XML вместо SQL;
• -u — пользователь, от имени которого нужно подключаться к базе данных.

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

mysqldump -u имя_пользователя -p имя_базы > data-dump.sql

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

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

mysqldump -h хост -P порт -u имя_пользователя -p имя_базы > data-dump.sql

Копирование таблицы MySQL может быть выполнено простым добавлением имени таблицы в конец строки:

mysqldump -u имя_пользователя -p имя_базы имя_таблицы > data-dump.sql

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

mysqldump -u имя_пользователя -pпароль имя_базы > data-dump.sql

Мы можем делать бэкап вручную время от времени, но это не совсем удобно, поскольку есть другие важные дела. Поэтому используем планировщик cron, чтобы автоматизировать процесс. Тут есть два способа более простой, и более сложный, но точный. Допустим, нам нужно создавать резервную копию каждый день, тогда просто создайте скрипт в папке /etc/cron.daily/ со следующим содержимым:

sudo vi /etc/cron.daily/mysql-backup

!/bin/bash
/usr/bin/mysqldump -u имя_пользователя -pпароль имя_базы > /backups/mysql-dump.sql

Папку /backups/mysql-dump.sql нужно заменить на свою папку для резервных копий. Осталось дать скрипту права на выполнение:

chmod ugo+x /etc/cron.daily/mysql-backup

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

Добавьте в открывшейся файл такую строку и сохраните изменения:

30 2 * * * /usr/bin/mysqldump -u имя_пользователя -pпароль имя_базы > /backups/mysql-dump.sql

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

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

Восстановить резервную копию mysql или mariadb из существующего SQL файла тоже очень просто. Поскольку использовался синтаксис sql мы просто можем выполнить все команды с помощью стандартного клиента mysql.

Сначала нужно создать новую базу данных. Для этого авторизуйтесь на mysql сервере с правами суперпльзователя:

Затем создайте новую базу данных, например, с именем new_database, если база данных уже существует, то этого делать не нужно:

mysql> CREATE DATABASE new_database;

Дальше закройте оболочку, нажав сочетание клавиш Ctrl+Q и импортируйте данные из файла командой:

mysql -u пользователь -p база_данных < data-dump.sql

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

Выводы

Теперь вы знаете как выполняется копирование базы данных MySQL, а также как восстановить скопированную информацию. Мы рассмотрели все возможные опции mysqldump чтобы вы могли настроить утилиту так, как вам нужно. Резервное копирование базы данных MySQL это очень важный момент и в определенной ситуации может сохранить много времени, поэтому обязательно настройте у себя на сервере!

Обнаружили ошибку в тексте? Сообщите мне об этом. Выделите текст с ошибкой и нажмите Ctrl+Enter.

Источник

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

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

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

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

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

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

mysqldump [options] > 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. Общий синтаксис команды выглядит следующим образом:

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

В следующем примере первая команда создаст базу данных с именем 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 в указанное время.

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

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

[client] user = dbuser password = dbpasswd

Добавьте следующее задание 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

Заключение

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

Источник