Как скопировать базу данных mysql linux

Как сделать дамп базы MySQL / MariaDB

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

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

Создание дампа выполняется из командной строки Linux или Microsoft. Общий синтаксис:

mysqldump [опции] -p >

Пример базовой команды для резервирования базы:

mysqldump -v -uroot -p base > /tmp/dump.sql

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

Базовые параметры команды mysqldump:

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

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

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

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

mysqldump -uroot -p $ | gzip > /tmp/$.sql.gz

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

Или с подробным выводом информации на экран (дольше по времени):

mysqldump -v -uroot -p $ | gzip > /tmp/$.sql.gz

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

Просто перечисляем имена баз через пробел и добавляем параметр -B

mysqldump -v -uroot -p -B base1 base2 base3 > /tmp/dump_multiply_bases.sql

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

Для этого ставим —all-databases, вместо имен баз

mysqldump -v -uroot -p —all-databases > /tmp/dump_all_bases.sql

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

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

mysqldump -v -uroot -p —no-data base1 > /tmp/dump_base1_nodata.sql

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

Для этого после базы через пробел перечисляем названия таблиц

mysqldump -v -uroot -p base1 table1 table2 > /tmp/dump_base1_tables.sql

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

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

mysqldump -v -uroot -p mysql user > /tmp/mysql_user.sql

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

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

Выполняется при помощи ключа ignore-table:

mysqldump -v -uroot -p base —ignore-table=base.logs > /tmp/base.sql

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

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

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

В качестве графического инструмента для работы с MySQL/MariaDB используется phpMyAdmin. Разберем, как с его помощью сделать экспорт данных.

В верхней части меню кликаем по Экспорт:

В разделе «Способ экспорта» ставим переключатель в положение Обычный:

* обычный режим откроет дополнительные опции для резервного экспорта данных.

Выбираем компрессию, например, zip:

И в нижней части окна нажимаем OK.

Начнется загрузка файла с резервной копией на компьютер.

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

Источник

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

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

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

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

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

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

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

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

• options — Параметры mysqldump
• file.sql — файл дампа (резервной копии)

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

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

Наиболее распространенный вариант использования инструмента mysqldump — резервное копирование одной базы данных.

Например, чтобы создать резервную копию базы database_name с именем database_name используя пользовательский root и сохранить ее в файл с именем database_name.sql вы должны выполнить следующую команду:

Вам будет предложено ввести пароль root. После успешной аутентификации начнется процесс дампа. В зависимости от размера базы данных процесс может занять некоторое время.

Если вы вошли в систему как тот же пользователь, которого используете для экспорта, и этот пользователь не требует пароля, вы можете опустить параметры -u и -p :

Резервное копирование нескольких баз данных MySQL

Для резервного копирования нескольких баз данных MySQL с помощью одной команды вам нужно использовать параметр —database за которым следует список баз данных, которые вы хотите —database . Каждое имя базы данных должно быть разделено пробелом.

Приведенная выше команда создаст файл дампа, содержащий обе базы данных.

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

Используйте параметр —all-databases для резервного копирования всех баз данных MySQL:

Как и в предыдущем примере, приведенная выше команда создаст один файл дампа, содержащий все базы данных.

Резервное копирование всех баз данных MySQL в отдельные файлы

Утилита mysqldump не предоставляет возможности резервного копирования всех баз данных в отдельные файлы, но мы легко достигаем этого с помощью простого цикла FOR bash :

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

Создание сжатой резервной копии базы данных MySQL

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

Создать резервную копию с отметкой времени

Если вы хотите хранить более одной резервной копии в одном месте, вы можете добавить текущую дату к имени файла резервной копии:

Приведенная выше команда создаст файл в следующем формате database_name-20180617.sql

Восстановление дампа MySQL

Вы можете восстановить дамп MySQL с помощью инструмента mysql . Общий синтаксис команды следующий:

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

В следующем примере первая команда создаст базу данных с именем database_name а затем импортирует в нее дамп database_name.sql :

Восстановление единой базы данных MySQL из полного дампа MySQL

Если вы создали резервную копию всех своих баз данных с помощью параметра -all-databases и хотите восстановить одну базу данных из файла резервной копии, содержащего несколько баз данных, используйте параметр —one-database как показано ниже:

Экспорт и импорт базы данных MySQL одной командой

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

Приведенная выше команда передаст вывод клиенту mysql на удаленном хосте и импортирует его в базу данных с именем remote_database_name . Перед запуском команды убедитесь, что база данных уже существует на удаленном сервере.

Автоматизируйте резервное копирование с помощью Cron

Автоматизировать процесс резервного копирования баз данных так же просто, как создать задание cron, которое будет запускать команду mysqldump в указанное время.

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

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

Скопируйте и вставьте следующий текст в файл .my.cnf.

Не забудьте заменить dbuser и dbpasswd на пользователя базы данных и пароль пользователя.

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

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

Откройте ваш пользовательский файл crontab:

Добавьте следующее задание cron, которое будет создавать резервную копию базы данных с именем mydb каждый день в 3 часа ночи:

Не забудьте заменить username своим фактическим именем пользователя. Мы также избегаем знаков процента ( % ), потому что они имеют особое значение в crontab.

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

Конечно, вам нужно настроить команду в соответствии с местоположением резервной копии и именами файлов. Чтобы узнать больше о команде find, ознакомьтесь с нашим руководством по поиску файлов в Linux с помощью командной строки .

Выводы

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

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

Вы также можете проверить руководство о том, как сбросить пароль root MySQL, если вы его забыли.

Если у вас есть какие-либо вопросы или отзывы, не стесняйтесь оставлять комментарии.

Источник

linux-notes.org

Автоматический бэкап баз Mysql в Linux

Хочу рассказать в своей статье «Автоматический бэкап баз Mysql в Linux» как я делал автоматический бекап моих баз данных в Mysql. Очень полезная информация, особенно если этого не знаешь, а нужно делать бекап.

1. Установка и настройка Mysql ( надеюсь у вас уже все настроено и готова к работе, как же имеются БД для создания бекапа), я приведу информацию в своих предыдущих темах, может кому то и нужно будет:

Есть еще много тем, просто поищите на сайте, если есть необходимость.

2. Создаем собственно бекап всех БД.

Для начала создадим папку, там будут лежать все наши бекапы, для этого:

Командой создаем бекап:

PS: Ребята, только не путаем знаки (` -тильда, которая похожа на обычную кавычку ‘). У метя там прописаны именно тильды.

Смотрим чтобы уже был созданный файл, убеждаемся что все норм:

Это бывает не всегда правильно! Так так нужно всегда думать, а не сделал ли я бекап? Нужно добавить эту команду в crontab для автоматического бекапа данных mysql, делается это вот так:

Этот вариант, подходит, но есть но, он выполняется от пользователя root. Желательно этого не делать. Правильно будет следующим образом:

Я добавил в кронтаб, задание которое будет выполнятся каждый день в 00.00. Если нужно прописать другое время, то прочитайте:

Источник

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

Что такое MySQL?

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 -u username -p

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

CREATE DATABASE database_name;
exit

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

mysql -u username -p database_name

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

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

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

Источник