В данной статье рассмотрим несколько практических примеров резервирования восстановления баз с помощью mysqldump . Утилита mysqldump – это эффективный инструмент для создания резервной копии базы данных MySQL . Он позволяет создать *.sql файл с совокупностью (дампом) всех таблиц и данных основной базы данных (источника).

С целью резервирования баз данных MyISAM, лучше использовать инструмент mysqlhotcopy , который мы обязательно опишем в следующих статьях, так как с ними он работает быстрее и эффективнее.

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

Базовыми командами для создания резервной копии и восстановления базы данных MySQL с помощью mysqldump есть:

В данных командах:

-u – параметр, который указывает логин, с помощью которого в данном случае осуществляется подключение к базе данных;

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

[имя_базы] – имя базы данных, резервную копию которой необходимо создать;

[название_файла_резервной_копии_базы].sql – пользователь может указать любое удобное название файла резервной копии базы данных. Если указать название файла как в предоставленном примере, то резервная копия базы будет создана в папке из которой запускалась команда, а именно:
C:\Program Files\MySQL\MySQL Server 5.7\bin

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

# mysqldump -uroot -pqwerty my_db › C:\Users\Valery\Documents\MySQL_Backup\my_db-dump1.sql
# mysql -uroot -pqwerty my_db ‹ C:\Users\Valery\Documents\MySQL_Backup\my_db-dump1.sql

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

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

# mysqldump –u [пользователь] –p [пароль_пользователя] [имя_базы] › [название_файла_резервной_копии_базы].sql

Например:

# mysqldump -uroot -pqwerty my_db > my_db-dump1.sql

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

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

Для этого введите команду show databases (в Workbench)

или # mysqlshow –uroot -p (в консоли).

Если необходимо одновременно создать резервную копию нескольких баз данных (например, my_db и test ), то для этого необходимо выполнить такую команду:

# mysqldump -uroot -pqwerty –databases my_db test › my_db_test_backup.sql

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

Если есть необходимость создать бэкап всех баз данных вашего профайла MySQL, то это можно сделать с помощью параметра –all-databases .

# mysqldump -uroot -pqwerty –all-databases › all-databases_backup.sql

Резервная копия отдельной таблицы

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

# mysqldump -uroot -p my_db wp_commentmeta › table_ my_db-wp_commentmeta.sql

Примечание . Чтобы просмотреть список таблиц базы, введите команду:
#mysqlshow –uroot –p my_db

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

Восстановить базу данных 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

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

head -n 5 data-dump.sql

Но если во время создания копии возникнут какие-либо ошибки, они будут выведены на экран и вы сразу о них узнаете. Более сложный вариант, это выполнить резервное копирование mysql с другого хоста, если у вас есть к нему доступ:

mysqldump -h хост -P порт -u имя_пользователя -p имя_базы > data-dump.sql

Копирование таблицы mysql может быть выполнено простым добавлением имени таблицы в конец строки:

mysqldump -u имя_пользователя -p имя_базы имя_таблицы > data-dump.sql

Также, чтобы выполнять автоматическое резервное копирование базы mysql может понадобиться сразу задать пароль, для этого указывайте его сразу после опции -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 сервере с правами суперпльзователя:

mysql -u root -p

Затем создайте новую базу данных, например, с именем new_database, если база данных уже существует, то этого делать не нужно:

mysql> CREATE DATABASE new_database;

mysql -u пользователь -p база_данных < data-dump.sql

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

Выводы

Теперь вы знаете как выполняется копирование базы данных mysql, а также как восстановить скопированную информацию. Мы рассмотрели все возможные опции mysqldump чтобы вы могли настроить утилиту так, как вам нужно. Резервное копирование базы данных mysql это очень важный момент и в определенной ситуации может сохранить много времени, поэтому обязательно настройте у себя на сервере!

Для этого в командной строке вашего shell выполним:

# mysqldump -u root -p -h localhost -x -F base >

где флаги обозначают:
-u
-h
-p – указывает на то, что для входа в MySQL привилегированному пользователю (root) требуется ввести пароль;
-x – блокируем все таблицы бэкапируемой базы данных от изменений на время работы выгрузки дампа;
-F – сбрасываем на диск журнал регистрации сервера MySQL прежде, чем запустить дамп;
base – имя бекапируемой базы данных MySQL;
/var/backups/base_`date +%Y-%m-%d%n`.sql – путь и имя файла дампа бэкапируемой базы данных MySQL, где `date +%Y-%m-%d%n` припишет к имени файла дату его создания.

В результате работы это команды мы получим файл дампа необходимой нам базы base, который будет выглядеть как:

# ls -Al /var/backups/ | grep base -rw-r--r-- 1 root wheel 2738944 13 апр 10:03 base_2010-04-13.sql

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

# mysql -u root -h localhost -p

где флаги обозначают:
-u – указываем привилегированного пользователя на базы MySQL (в данном примере это root);
-h – указываем имя хоста, на котором работает MySQL (в данном примере это localhost);
-p – указывает на то, что для входа в MySQL привилегированному пользователю (root) требуется ввести пароль.

В приглашении оболочки MySQL введем команду создания новой базы данных:

Mysql> CREATE DATABASE base;

Для просмотра результата дадим команду, которая отобразит существующие базы:

Mysql> SHOW DATABASES;

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

Mysql> USE base; Database changed mysql> source /var/backups/base_2010-04-13.sql;

Все, мы создали новую базу данных и загрузили в нее архивную копию. Выйдем из оболочки MySQL:

Mysql> exit Bye

Существует еще один способ восстановления из архива. Для этого откроем необходимый нам файл бэкапа и внесем в самое его начало следующие строки:

# nano -w /var/backups/base_2010-04-13.sql CREATE DATABASE base; USE base;

Сохраним внесенные изменения, сделанные в nano, нажав CTRL +x , и подтвердим, нажав по запросу y . Теперь в командной строке вашего shell выполните команду:

# mysql -u root -h localhost -p < /var/backups/base_2010-04-13.sql

введите пароль привилегированного пользователя root, чтобы импортировать бекап в прописанную нами вновь созданную базу base.

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

• mysqdump (Командная утилита)
• phpmyadmin (Веб интерфейс для работы с бд)
• Sypex Dumper (Скрипт)

mysqldumpПодключившись по SSH или имея локальный доступ через терминал, можно сделать бекап базы данных используя командную утилиту mysqldump которая входит в комплект установленного MySQL пакета. Рассмотрим синтаксис данной команды.
mysqldump -u -p > – имя пользователя который имеет доступ к базе данных.
– пароль пользователя.
– имя базы данных.
– имя файла, дампа на выходе.
Пример как получить дамп базы данных mydb_forum на MySQL сервере:

mysqldump -uadmin -pSuperPassword mydb_forum > mydb_forum.sql

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

mysql -uadmin -pSuperPassword mydb_forum < mydb_forum.sql

Что бы сделать дамп всех БД которые находятся у вас на MySQL сервере воспользуйтесь след. примером:

mysqldump -uAdmin -pMyPass --all-databases > all_databases.sql

Бекап структуры базы данных

mysqldump --no-data --databases my_db1 my_db2 my_db3 > structurebackup.sql

Cжатие данных на ходу

mysqldump -uAdmin -pSuperPass mydb | bzip2 -c > backup.sql.bz2 mysqldump -uAdmin -pSuperPass mydb | gzip -c > backup.sql.gz

PHPMyAdminPHPMyAdmin это легкий веб-интерфейс для работы с MySQL базами данных. Вы можете его использовать на сервере или домашнем ПК если у вас установлен и настроен PHP. Также это хороший вариант когда у вас нет доступа к серверу через telnet/ssh. Скачать последнюю версию
пакета PHPMyAdmin можно сдесь .
Настройки PHPMyAdmin
Прежде чем приступить к бекапу базы данных используя PHPMyAdmin я опишу как можно его настроить. После распаковки и установки пакета PHPMyAdmin нам нужно отредактировать файл
config.inc.php
Указываем полный URL к месту где находится phpmyadmin

$cfg [ "PmaAbsoluteUri" ] = "http://myexample.com/phpmyadmin/" ;

Настройки phpmyadmin для работы с базой данных

$cfg [ "Servers" ] [ $i ] [ "host" ] = "235.104.1.5" ; $cfg [ "Servers" ] [ $i ] [ "port" ] = "3306" ; $cfg [ "Servers" ] [ $i ] [ "connect_type" ] = "tcp" ; $cfg [ "Servers" ] [ $i ] [ "auth_type" ] = "config" ; $cfg [ "Servers" ] [ $i ] [ "user" ] = "admin" ; $cfg [ "Servers" ] [ $i ] [ "password" ] = "FvAk41P" ;

Бекап MySQL базы данных используя PHPMyAdmin.
Что бы сделать бекап базы данных MySQL используя PHPMyAdmin войдите в него (напр. http://localhost/phpmyadmin/), слева выберите базу данных которая вас интересует, и кликните на вкладку Export, вы попадете примерно на такую страницу:

Как видите тут есть разные настройки, возможность выбрать тип будущего дампа базы данных, архивация дампа и т.п. Жмем на кнопку “Go” что бы приступить к бекапу БД, выскакивает окошко с просьбой указать место на вашем ПК куда будет сохранен дамп базы данных, указываем и наслаждаемся процессом резервного копирования БД. PHPMyAdmin. Ввостановление базы данных
Процедура довольно проста, заходим в phpmyadmin, кликаем на Import, видим форму, используем ее для выбора локального файл-дампа базы данных. При клике на “Go” база данных восстановится.Sypex DumperТакже очень интересный на мой взгляд, бесплатный скрипт – “Sypex Dumper” написанный на PHP.
Любой начинающий пользователь, или администратор который не хочет заморачиватся с разными консольными утилитами и многофункциональными веб интерфейсами может выбрать для себя удобный, простой и вполне работоспособный скрипт Sypex Dumper.

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

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

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

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

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

# mkdir /home/captain/backup && cd /home/captain/backup

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

# mysqldump -u root -proot -h 127.0.0.1 --all-databases | gzip >

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

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

# ls -lah

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

# vim /etc/crontab

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

# crontab -e # Автоматический бэкап баз Mysql в Linux 00 00 * * * /usr/bin/mysqldump -u root -proot -h 127.0.0.1 --all-databases | gzip > /home/captain/backup/my_database_backup_`date +%mm%dd%yy`.sql.gz

На этом, я завершаю свою тему «Автоматический бэкап баз Mysql в Linux» на моем сайте https://сайт