- Резервное копирование MySQL: инструкция
- Синтаксис команд
- Несколько примеров по созданию дампа MySQL
- Последующее архивирование
- Одновременная архивация двух и более баз
- Заархивировать все имеющиеся базы
- Резервировать только структуру БД
- Создать резервную копию указанной таблицы
- Перенос прав доступа к СУБД
- При архивации игнорировать указанную таблицу
- Резервирование под любой учетной записью
- Резервирование БД через phpMyAdmin
- Типовые проблемы
- Выводы
- Как сделать дамп базы MySQL / MariaDB
- Синтаксис и базовая команда
- Примеры создания дампа MySQL
- 1. С последующим архивированием
- 2. Для одновременно нескольких баз
- 3. Для всех баз одной командой
- 4. Резервирование только структуры базы
- 5. Создание копии определенной таблицы
- 6. Резервирование прав доступа на СУБД
- 7. Проигнорировать определенную таблицу
- Учетная запись
- Скрипт для резервного копирования
- Резервное копирование в phpMyAdmin
- Возможные проблемы
- Incorrect key file for table
- Читайте также
Резервное копирование MySQL: инструкция
В статье рассмотрим способы резервного копирования СУБД MySQL , приведем примеры по наиболее востребованным ключам и параметрам. В качестве тестовой платформы можно использовать облачные базы данных Timeweb Cloud.
Синтаксис команд
Создать дамп базы данных можно через командную строку с помощью утилиты mysqldump. Ее устанавливают вместе с пакетом MySQL (идет в стандартном комплекте). Возможности одинаковы на Linux и на Windows. Запуск приложения допускается как на локальном, так и на удаленном сервере СУБД. Общий синтаксис:
mysqldump -v -h127.0.0.1 -uroot base -p > /tmp/dump.sql
Здесь мы сделали резервный архив БД под названием base, разместили его в каталоге /tmp. Коннект осуществляется к локальной базе (IP 127.0.0.1) под аккаунтом root. Это был простейший вариант по созданию дампа для резервного копирования базы MySQL . Теперь перейдем к описанию базовых параметров утилиты mysqldump:
- -h – адрес хоста, куда требуется подключаться для создания дампа.
- -u – аккаунт, от имени которого будет выполнено резервирование (важно заранее убедиться в наличии соответствующих прав).
- -p – пароль используемого аккаунта. При работе через скрипт его вводят в формате p12345, в остальных случаях лучше оставить только символ -p, это безопаснее.
Мы перечислили только наиболее востребованные параметры. Если интересует их полный перечень для более глубокого изучения вопроса, он доступен на официальном сайте в документации по ссылке.
Несколько примеров по созданию дампа MySQL
Приведем варианты команд для нескольких частных случаев.
Последующее архивирование
DBNAME=base
mysqldump -uroot -p $ | gzip -c > /tmp/$.sql.gz
Мы сделали переменную DBNAME, где затем разместили значение «имя базы», подлежащей архивации. И только потом запустили утилиту mysqldump, чтобы по завершении передать очередь исполнения архиватору GZIP.
Итог: дамп, размещенный в каталоге /tmp/.sql.gz. Если пользователю надо, чтобы процесс отображался на экране, команда примет вид:
mysqldump -v -uroot -p $ | gzip -c > /tmp/$.sql.gz
Одновременная архивация двух и более баз
Задача простая, достаточно перечислить названия БД через пробел и использовать опцию -B. Вот так будет выглядеть команда:
mysqldump -v -uroot -p -B base1 base2 base3 > /tmp/dump_multiplе_bases.sql
Заархивировать все имеющиеся базы
Также можно запускать резервное копирование баз данных MySQL при помощи параметра —all-databases, указывая его вместо имени базы. Пример команды:
mysqldump -v -uroot -p --all-databases > /tmp/dump_all_bases.sql
Резервировать только структуру БД
Важно учитывать, что в таком режиме в архив будут сохранены таблицы без каких-либо данных. Режим выбирается при помощи параметра —no-data:
mysqldump -v -uroot -p --no-data base1 > /tmp/dump_base1_nodata.sql
Создать резервную копию указанной таблицы
В этом случае к имени базы добавляет наименование таблиц. Например, как в команде:
mysqldump -v -uroot -p base1 table1 table2 > /tmp/dump_base1_tables.sql
Перенос прав доступа к СУБД
Система способна выгрузить действующие учетки вместе с актуальными паролями. Такая функция нужна для экспорта СУБД на другой хост без риска утраты доступа к базе. Команда будет выглядеть так:
mysqldump -v -uroot -p mysql user > /tmp/mysql_user.sql
Чтобы обеспечить работоспособность после переноса при помощи такого дампа, нужно выполнить команду flush privileges (через SQL Shell).
При архивации игнорировать указанную таблицу
Подразумевается выполнение команды с ключом ignore-table:
mysqldump -v -uroot -p base --ignore-table=base.logs > /tmp/base.sql
Резервирование под любой учетной записью
Примеры, приведенные выше, были рассчитаны на подключение к БД при помощи пользователя root. На практике же процедуру выполняют и под другими учетками. Главное, чтобы у выбранного пользователя были права для доступа к базе, дамп которой планируется создать. Можно создать отдельный аккаунт с ограниченными правами для выполнения архивации. Выполним это командой SQL:
> GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES, PROCESS, EXECUTE ON *.* TO backup@localhost IDENTIFIED BY 'backup123';
Здесь мы создали учетку backup, задали пароль backup123. Прав у пользователя будет достаточно, чтобы подключиться к БД на локальном сервере. Чтобы не вводить вручную при каждом запуске одни и те же параметры, можно записать файл конфигурации:
vi ~/.my.cnf
[mysqldump]
host = 127.0.0.1
user=backup
password="backup123"
Теперь команда примет «облегченный» вид:
mysqldump base > /tmp/dump.sql
Резервирование БД через phpMyAdmin
Если работать в консоли неудобно, можно воспользоваться штатным графическим инструментом phpMyAdmin.
Зайдем в пункт «Экспорт» верхнего меню и в разделе «Способ экспорта» поставим переключатель в положение «Обычный». После этого откроется ряд дополнительных опций. Из них нас интересует способ компрессии, например ZIP. Остается кликнуть ОК – запустится загрузка дампа с выполнением резервной копии и ее сохранением на локальном компьютере.
Типовые проблемы
Иногда можно столкнуться с ошибкой Incorrect key file for table, которая выглядит так:
mysqldump: Error 1034: Incorrect key file for table ''; try to repair it when dumping table `` at row: xxxxxx
Причин может быть несколько. Например, обнаружена логическая ошибка таблицы в БД или закончилось место на накопителе, где система сохраняет временные данные. Соответственно, для решения проблемы нужны разные подходы. Так, первым делом желательно проверить объем места на диске. В файле конфигурации, размещенном по пути /etc/my.cnf, указана опция tmpdir, которая определяет папку, куда система сохраняет временные таблицы.
Если ее нет, тогда по умолчанию будет использован путь /tmp. Проверить, достаточно ли места для создания дампа, можно командой:
Иная ситуация, если повреждена таблица. Ее необходимо восстановить, но решение зависит от типа проблемного участка базы.
MyISAM. Действия пользователя следующие. В командной строке SQL вводим команду:
После нее повторяем попытку создания резервной копии.
INNODB. Откроем файл конфигурации СУБД:
Найдем раздел [mysqld] и в нем добавим опцию:
При повторе ошибки изменим значение innodb_force_recovery на 2. Если проблема все еще сохраняется, то на 3, и так вплоть до 6, пока резервирование не пройдет успешно. Важно учитывать, что применяемый параметр иногда приводит к потере данных. Если пришлось поднять его значение выше 2, стоит вручную проверить сохранность ценной информации после завершения резервирования.
Выводы
Команда mysqldump обладает широким функционалом, позволяющим резервировать базы в разной комбинации. При желании ее легко заменить удобным инструментом с графическим интерфейсом phpMyAdmin. Эксперименты желательно проводить на пустой БД, где нет ценной информации. Например, можно арендовать соответствующие ресурсы у Timeweb Cloud .
Как сделать дамп базы 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 ‘
Решение: в зависимости от причины, решений будет несколько.
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
Adblockdetector