Резервное копирование mysql linux

Резервное копирование 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 / MariaDB

Обновлено: 12.02.2023 Опубликовано: 15.07.2016

В статье рассмотрим общие принципы выполнения резервного копирования СУБД MySQL или MariaDB. Также рассмотрим некоторые примеры часто используемых ключей и параметров резервирования.

Синтаксис и базовая команда

Создание дампа выполняется из командной строки Linux или Microsoft с помощью утилиты mysqldump. Она идет в составе с пакетом mysql/mariadb (mysql-client) и может быть запущена как локально на сервере СУБД, так и с удаленного компьютера. Общий синтаксис:

* в данном примере мы создадим резервную копию базы base и поместим его в папку /tmp, назвав сам файл dump.sql. Подключение к базе на сервере 127.0.0.1 происходит от пользователя root. Это самый простой пример создания дампа MySQL. Базовые параметры команды mysqldump:

Параметр	Описание
-h	Адрес сервера, к которому нужно подключиться.
-u	Учетная запись, от которой выполняется резервное копирование. Необходимо, чтобы у пользователя были соответствующие права.
-p	Пароль учетной записи. Его можно ввести в команде, например -p12345 (для скрипта) или оставить -p (безопаснее).

* полный перечень параметров смотрите в официальном руководстве.

Примеры создания дампа MySQL

1. С последующим архивированием

* в данном примере мы сначала создали переменную DBNAME, в которую внесли значение с именем базы, которую необходимо забэкапить. После выполняем команду mysqldump, результат выполнения которой по конвееру отдаем архиватору gzip. В результате мы получит дамп по пути /tmp/.sql.gz Или с подробным выводом информации на экран (дольше по времени):

2. Для одновременно нескольких баз

3. Для всех баз одной командой

4. Резервирование только структуры базы

Для уточнения, это создание копии только самих таблиц без данных. Делается добавлением параметра —no-data

5. Создание копии определенной таблицы

6. Резервирование прав доступа на СУБД

Позволяет выгрузить все учетные записи с паролями. Удобно для переноса СУБД на новый сервер без потери доступа к нему.

* после восстановления этого дампа, необходимо в sql shell выполнить команду flush privileges;

7. Проигнорировать определенную таблицу

Учетная запись

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

* в данном примере мы создадим пользователя backup с паролем backup123. Теперь дадим разрешение созданной учетной записи выполнять подключение и создание резервной копии:

> GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES, PROCESS, EXECUTE ON *.* TO backup@localhost;

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

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

Для повседневных операций по резервному копированию MySQL рекомендуется написать скрипт и запускать его через cron. Подробнее процесс описан в статье Пример скрипта для создания резервной копии MySQL (для Linux).

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

В качестве графического инструмента для работы с MySQL/MariaDB используется phpMyAdmin. Разберем, как с его помощью сделать экспорт данных. В верхней части меню кликаем по Экспорт: В разделе «Способ экспорта» ставим переключатель в положение Обычный: * обычный режим откроет дополнительные опции для резервного экспорта данных. Выбираем компрессию, например, zip: И в нижней части окна нажимаем OK. Начнется загрузка файла с резервной копией на компьютер.

Возможные проблемы

Incorrect key file for table

mysqldump: Error 1034: Incorrect key file for table ‘

‘; try to repair it when dumping table ` ` at row: xxxxxx

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

1. Проще всего сначала проверить место на диске. В конфигурационном файле СУБД (как правило, /etc/my.cnf) можно найти опцию tmpdir — она указывает на каталог, который используется под создание временных таблиц. Если опции нет, то используется путь /tmp.

Необходимо, чтобы для данного раздела было достаточно места. Проверить можно командой:

2. Если наша таблица повреждена, то пробуем ее востановить. Данный процесс зависит от типа таблицы, с которой возникла проблема.

В командной оболочке SQL вводим:

После повторяем попытку создать резервную копию.

б) Если таблица типа INNODB.

Открываем конфигурационный файл СУБД:

В радел [mysqld] добавим опцию:

[mysqld]
.
innodb_force_recovery = 1

Перезапускаем сервер баз данных, например:

Пробуем сделать резервную копию. Если получим такую же ошибку, меняем значение innodb_force_recovery с 1 на 2:

[mysqld]
.
innodb_force_recovery = 2

И так по кругу, до значения 6, пока не получим положительный результат.

Параметр innodb_force_recovery может оказаться опасным, так как при его использовании возможны потери данных. Чем ниже значение, тем меньше рисков. Если пришлось поднять значение выше 2, то необходимо внимательно проверить наличие важной информации в базе.

Читайте также

Для восстановления базы читайте инструкцию Как восстановить базу MySQL

Источник