Бэкап sql базы linux

Бэкап базы данных MySQL на сервере Ubuntu

MySQL – это популярная система управления базами данных (СУБД), использующая для управления данными язык запросов SQL. MySQL идеально подходит для хранения данных сайта или веб-приложения.

Резервное копирование (или бэкап) – очень важная для сохранности любых данных операция. Особенно это касается баз данных. Бэкап базы данных MySQL можно выполнить несколькими способами, о чём и пойдёт речь в этой статье.

Примечание: Для выполнения руководства использовался сервер Ubuntu 12.04 и MySQL 5.5, но более современные версии программного обеспечения будут работать подобным образом.

Бэкап базы данных MySQL при помощи mysqldump

Утилита mysqldump – один из самых простых и удобных способов создания резервной копии MySQL.

Для начала нужно экспортировать БД. Об экспортировании БД при помощи mysqldump можно прочесть здесь. Базовый синтаксис команды выглядит так:

mysqldump -u username -p database_to_backup > backup_name.sql

Восстановление БД

Чтобы восстановить дамп БД, созданный при помощи mysqldump, нужно просто перенаправить вывод в файл MySQL.

Для этого создайте пустую БД для хранения импортированных данных. Войдите в MySQL:

Создайте новую БД, чтобы переместить в неё данные из дампа, а затем закройте командную строку MySQL:

CREATE DATABASE database_name;
exit

Перенаправьте дамп-файл в файл БД:

mysql -u username -p database_name < backup_name.sql

Скопированные данные будут восстановлены в новой БД.

Копирование таблицы MySQL в текстовый файл

Также MySQL позволяет сохранять данные из таблицы прямо в текстовые файлы с помощью оператора select.

SELECT * INTO OUTFILE ‘table_backup_file’ FROM name_of_table;

Данный оператор сохранит данные из таблицы в файл на сервере MySQL. Имейте в виду: если файл с таким именем уже существует, операция не будет выполнена.

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

Утилита automysqlbackup

Утилита automysqlbackup доступна в стандартных репозиториях Ubuntu. Она позволяет выполнять бэкап БД автоматически на регулярной основе.

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

sudo apt-get install automysqlbackup

Главный конфигурационный файл утилиты находится в /etc/default/automysqlbackup; откройте его с правами администратора:

sudo nano /etc/default/automysqlbackup

Как видите, данный файл по умолчанию присваивает множество переменных из файла /etc/mysql/debian.cnf, который содержит данные для авторизации. Из этого файла automysqlbackup считывает пользователя, пароль и БД, резервные копии которых нужно создать.

Стандартное место хранения резервных копий – /var/lib/automysqlbackup. Найдите этот каталог и ознакомьтесь со структурой бэкапов:

ls /var/lib/automysqlbackup
daily monthly weekly

Каталог daily содержит подкаталог для каждой БД, в котором хранится сжатый sql дамп, полученный в результате последнего запуска команды:

ls -R /var/lib/automysqlbackup/dailey
.:
database_name information_schema performance_schema
./database_name:
database_name_2013-08-27_23h30m.Tuesday.sql.gz
./information_schema:
information_schema_2013-08-27_23h30m.Tuesday.sql.gz
./performance_schema:
performance_schema_2013-08-27_23h30m.Tuesday.sql.gz

Для настройки автоматического запуска резервного копирования система Ubuntu устанавливает вместе с этой программой демона cron.

Репликация баз данных

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

Репликация – это процесс зеркалирования данных с ведущего сервера на другие (тип master-slave) или с любого сервера связки на остальные серверы (тип master-master).

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

Чтобы устранить эту проблему, можно:

• Временно отключить репликацию
• Или временно сделать сервер резервного копирования доступным только для чтения.

Временное отключение репликации

Чтобы временно отключить репликацию на slave-сервере, введите:

mysqladmin -u user_name -p stop-slave

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

mysql -u user_name -p -e ‘STOP SLAVE SQL_THREAD;’

Остановив репликацию, можно выполнить бэкап при помощи любого из вышеперечисленных методов. При этом не нужно останавливать master-сервер.

После этого просто возобновите репликацию:

mysqladmin -u user_name -p start-slave

Настройка доступа к серверу резервного копирования

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

Это можно сделать как на сервере master, так и на slave.

Для начала откройте MySQL с правами root:

Запишите все кэшированные изменения на диск и настройте систему только для чтения с помощью команд:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

Выполните бэкап при помощи mysqldump.

После того как резервное копирование будет выполнено, верните систему в её обычное состояние:

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

Нерекомендуемые методы резервного копирования

Скрипт mysqlhotcopy

MySQL предоставляет perl-скрипт для быстрого резервного копирования по имени mysqlhotcopy. Этот инструмент позволяет очень быстро скопировать БД на локальной машине, но он имеет некоторые ограничения, из-за которых его лучше не использовать.

Во-первых, этот скрипт копирует только данные, хранящиеся при помощи механизмов MyISAM и Archive. Большинство пользователей не меняют механизмы хранения для своих БД, а MySQL, начиная с версии 5.5, по умолчанию использует механизм InnoDB. Следовательно, скрипт mysqlhotcopy не может скопировать такой тип данных.

Во-вторых, данные, скопированные при помощи этого скрипта, можно запустить только на той же машине, на которой хранится БД. То есть mysqlhotcopy не сможет скопировать данные с удалённого сервера.

Копирование файлов таблиц

Следующий метод, который не рекомендуется применять, – это простое копирование файлов таблиц MySQL.

Этот подход имеет те же недостатки, что и скрипт mysqlhotcopy.

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

Заключение

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

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

Источник

Как сделать дамп базы 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

Источник