В статье рассмотрим общие принципы выполнения резервного копирования СУБД MySQL или MariaDB. Также рассмотрим некоторые примеры часто используемых ключей и параметров резервирования.
Базовый синтаксис
Примеры
Пользователь для выполнения mysqldump
Скрипт
Использование phpMyAdmin
Исправление ошибок
Дополнительные материалы
Создание дампа выполняется из командной строки Linux или Microsoft с помощью утилиты mysqldump. Она идет в составе с пакетом mysql / mariadb (mysql-client) и может быть запущена как локально на сервере СУБД, так и с удаленного компьютера.
Общий синтаксис:
mysqldump [опции] > <в какой файл сделать дамп>
Пример базовой команды для резервирования базы:
mysqldump -v -h 127.0.0.1 -u root -p base > /tmp/dump.sql
* в данном примере мы создадим резервную копию базы base и поместим его в папку /tmp, назвав сам файл dump.sql. Подключение к базе на сервере 127.0.0.1 происходит от пользователя root. Это самый простой пример создания дампа MySQL.
Базовые параметры команды mysqldump:
| Параметр | Описание |
|---|---|
| -h | Адрес сервера, к которому нужно подключиться. |
| -u | Учетная запись, от которой выполняется резервное копирование. Необходимо, чтобы у пользователя были соответствующие права. |
| -p | Пароль учетной записи. Его можно ввести в команде, например -p12345 (для скрипта) или оставить -p (безопаснее). |
| -v | Подробный вывод в консоль. |
* полный перечень параметров смотрите в официальном руководстве .
Перейдем сразу к рассмотрению примеров.
export DBNAME=base
mysqldump -uroot -p ${DBNAME} | gzip > /tmp/${DBNAME}.sql.gz
* в данном примере мы сначала создали переменную DBNAME , в которую внесли значение с именем базы, которую необходимо забэкапить. После выполняем команду mysqldump , результат выполнения которой по конвееру отдаем архиватору gzip . В результате мы получит дамп по пути /tmp/<имя базы>. sql.gz
Или с подробным выводом информации на экран (дольше по времени):
mysqldump -v -uroot -p ${DBNAME} | gzip > /tmp/${DBNAME}.sql.gz
Если мы работает в консоли от пользователя root, который может входить в оболочку локального SQL-сервера без ввода пароль, команду можно сократить:
mysqldump ${DBNAME} | gzip > /tmp/${DBNAME}.sql.gz
Просто перечисляем имена баз через пробел и добавляем параметр -B
mysqldump -v -uroot -p -B base1 base2 base3 > /tmp/dump_multiply_bases.sql
Для этого ставим —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
* после восстановления этого дампа, необходимо в sql shell выполнить команду flush privileges ;
Выполняется при помощи ключа ignore-table:
mysqldump -v -uroot -p base —ignore-table=base.logs > /tmp/base.sql
Если нужно проигнорировать несколько таблиц, просто перечисляем их, добавляя опцию ignore-table:
mysqldump -v -uroot -p base —ignore-table=base.logs —ignore-table=base.actions > /tmp/base.sql
В наших примерах мы создаем дамп от пользователя root. Также можно задействовать любую учетную запись, у которой есть права на базу, дамп которой мы хотим сделать.
Если же нам нужна отдельная запись с минимальными правами, достаточными для резервного копирования, создаем ее SQL-командой:
> CREATE USER ‘backup’@’localhost’ IDENTIFIED BY ‘backup123’;
* в данном примере мы создадим пользователя backup с паролем backup123 .
Теперь дадим разрешение созданной учетной записи выполнять подключение и создание резервной копии:
> GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES, PROCESS, EXECUTE ON *.* TO backup@localhost;
Также мы можем создать локальный конфигурационный файл для пользователя системы, из под которого будет запускаться утилита mysqldump. В нем мы можем указать некоторые опции, чтобы не вводить их каждый раз.
Создаем файл:
vi ~/.my.cnf
[mysqldump]
host = 127.0.0.1
user=backup
password=»backup123″
Теперь можно вводить:
mysqldump base > /tmp/dump.sql
Для повседневных операций по резервному копированию MySQL рекомендуется написать скрипт и запускать его через cron.
Подробнее процесс описан в статье Пример скрипта для создания резервной копии MySQL (для Linux).
В качестве графического инструмента для работы с MySQL/MariaDB используется phpMyAdmin. Разберем, как с его помощью сделать экспорт данных.
В верхней части меню кликаем по Экспорт :
В разделе «Способ экспорта» ставим переключатель в положение Обычный :
* обычный режим откроет дополнительные опции для резервного экспорта данных.
Выбираем компрессию, например, zip :
И в нижней части окна нажимаем OK .
Начнется загрузка файла с резервной копией на компьютер.
Рассмотрим некоторые проблемы, с которыми столкнулся я.
Ошибка появляется во время выполнения резервного копирования. Более полный текст:
mysqldump: Error 1034: Incorrect key file for table ‘<table name>’; try to repair it when dumping table `<table name>` at row: xxxxxx
Причина: причин может быть несколько:
Решение: в зависимости от причины, решений будет несколько.
1. Проще всего сначала проверить место на диске. В конфигурационном файле СУБД (как правило, /etc/my.cnf) можно найти опцию tmpdir — она указывает на каталог, который используется под создание временных таблиц. Если опции нет, то используется путь /tmp.
Необходимо, чтобы для данного раздела было достаточно места. Проверить можно командой:
df -h
2. Если наша таблица повреждена, то пробуем ее востановить. Данный процесс зависит от типа таблицы, с которой возникла проблема.
а) Если тип MyISAM.
В командной оболочке SQL вводим:
> REPAIR TABLE <table name> USE_FRM;
После повторяем попытку создать резервную копию.
б) Если таблица типа INNODB.
Открываем конфигурационный файл СУБД:
vi /etc/my.cnf
В радел [mysqld] добавим опцию:
[mysqld]
…
innodb_force_recovery = 1
Перезапускаем сервер баз данных, например:
systemctl restart mysql
Пробуем сделать резервную копию. Если получим такую же ошибку, меняем значение innodb_force_recovery с 1 на 2:
[mysqld]
…
innodb_force_recovery = 2
И так по кругу, до значения 6, пока не получим положительный результат.
Параметр innodb_force_recovery может оказаться опасным, так как при его использовании возможны потери данных. Чем ниже значение, тем меньше рисков. Если пришлось поднять значение выше 2, то необходимо внимательно проверить наличие важной информации в базе.
Полный текст ошибки:
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `ХХХ` at row: 999
Причина: чаще всего, проблема из-за превышения лимита на объем передаваемых данных.
Решение: изменяем значение опции max_allowed_packet , которая указываем на максимальный размер пакета или сгенерированной строки. Также добавим опцию quick — она позволяет выводить информацию построчно, а не ожидать полной загрузки таблицы.
Итого, команда может быть:
mysqldump —quick —max_allowed_packet=512M -B base > /tmp/base.sql
* в примере мы создаем бэкап базы base , увеличив размер max_allowed_packet до 512 мегабайт.
** максимальное значение для max_allowed_packet может быть 1G.
Дополнительные инструкции по теме:
Zulip — программное обеспечение для реализации корпоративного чата. Разработан в 2012 году, в 2014 был…
Zookeeper — cервис-координатор, который позволяет обеспечить контроль синхронизации данных. Разработан на Java компанией Apache Software…
Zimbra — программное обеспечение для реализации почтового сервиса или, если сказать точнее, автоматизации совместной деятельности…
Zabbix — бесплатная система мониторинга. Позволяет отслеживать состояние сетевых узлов, компьютеров и серверов. Возможности: Поддержка…
YouTube — компания-владелец одноименного портала для просмотра и хранения видео. Чтобы пользоваться данным порталом достаточно…
Yota — провайдер, предоставляющий доступ к сети Интернет по беспроводной связи. Впервые, сервис начал работать…