Каждая база данных нуждается в резервном копировании. Если вы делаете его вручную, то пора переходить к автоматическому варианту с использованием простого решения на PHP. C помощью данного скрипта вы сможете сохранять отдельную таблицу или всю базу целиком.
Как использовать
Самый лучший способ - скопировать код и разместить его в папке с названием ‘database_backups ’ как показано ниже на своем веб сервере. Затем при любом вызове файла backup.php будет создавать резервную копию и сохранять ее в туже папку. Останется только скачать копию в другое место для надежного хранения и использования в случае необходимости.
Используем CRON
Cron - планировщик задач, который позволяет вам запускать определенные скрипты в заданное время. Например, можно делать резервную копию базы данных каждую ночь, когда трафик достаточно низок. Если ваш хостинг разрешает использовать CRON, то для запуска резервного копирования можно использовать следующую команду, которую нужно занести в таблицу crontab .
Wget -O /dev/null http://ваш_сайт.com/database_backup/backup.php
Код скрипта
В данной статье рассмотрим несколько практических примеров резервирования восстановления баз с помощью 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. Также вы сможете перенести данные на другой сервер.
Как сделать бэкап через командную строку (используя mysqldump)
Если у вас есть доступ к серверу через консоль (SSH) вы можете быстро делать бэкапы и разварачивать их обратно. Эта позволяет быстро создавать дамп базы данных (дамп - это текстовый вариант БД) и восстанавливать их обратно, быстрее чем через phpmyadmin, adminer и прочее. Дамп базы будет состоять из SQL-команд для создания копии вашей БД. Вот команда создания дампа:
$ mysqldump --opt -u -p >
- Имя пользователя БД (возможно root)
- пароль к вашему пользователю, можно писать слитно если он без каких-то особых знаков и пробелов, например -proot, -ppassword
- имя вашей БД
- имя файлу куда будет сохранен бэкап
[--opt]
- опции к команде mysqldump, можно пропустить и не писать
Допустим у вас есть база данных Drupal, а имя пользователя root с паролем password и имя файла будет backup.sql, тогда команда будет такая:
$ mysqldump -u root -ppassword DrupalDB > backup.sql
Можно опустить пароль или пароль содержит пробелы и другие особые знаки (#!,-_), то тогда вам придется вводить пароль отдельно и команда будет такая:
$ mysqldump -u root -p DrupalDB > backup.sql
Вы можете также бэкапить отдельные таблицы, для этого нужно перечислять таблицы через пробел, например nodes users:
$ mysqldump -u root -p DrupalDB nodes users > backup.sql
Помимо того чтобы бэкапить отдельные таблицы, можно бэкапить сразу несколько БД, для этого нужно указать параметр --databases это позволит через пробел указать нужные БД:
$ mysqldump -u root -p --databases DrupalDB Drupal7 Drupal8 > backup.sql
Если вы хотите перенести сервер MySQL полностью, то вы можете скопировать все данные с помощью параметра --all-databases:
$ mysqldump -u root -p --all-databases > alldb_backup.sql
Команда mysqldump имеет также несколько полезных параметров:
--add-drop-table - позволяет удалять таблицы перед разварачиваем этого бекапа (то есть в дамп будет добавлены SQL-запросы DROP TABLE перед CREATE TABLE той же таблицы).
--no-data - позволяет копировать только структуру БД без данных, например полезно когда вы копируете таблицы кеша, которые в друпале могут быть с сотнями тысяч ненужных нам записей.
--add-lock - позволяет добавить LOCK TABLES и UNLOCK TABLES. Если размер БД большой то таблицы могут быть залочены на продолжительное время.
Как сделать бэкап MySQL БД со сжатием
Пожалуй наиболее подходящий варинт, потому что сжать удается в 10-20 раз и бэкапы из больших баз данных становится вполне небольших размеров. Для сжатия мы будем пользоваться командой gzip:
$ mysqldump -u root -p DrupalDB | gzip -9 > backup.sql.gz
Если вы хотите разархивировать файл (не восстановить БД, а просто разархивировать), то воспользуйтесь этой командой:
$ gunzip backup.sql.gz
Как восстановить БД MySQL из бэкапа
Для восстановления БД из дампа, вам нужна чистая БД, можете удалить таблицы с помощью adminer"а или phpmyadmin"а. Если вы использовали параметр --add-drop-table, то таблицы сами удаляться и зальются и ничего удалять прежде не нужно. Вот команда чтобы восстановить БД из дампа:
$ mysql -u root -p DrupalDB < backup.sql
Если у вас был сжатый бэкап, то воспользуйтесь этой командой:
Gunzip < backup.sql.gz | mysql -u root -p DrupalDB
Бэкап и восстановление через Adminer (замена PhpMyAdmin)
Adminer - это замена PhpMyAdmin. Он такой же по функционалу, только сделан в виде одного небольшого файла, что весьма удобно:
Просто копируем файл в корень нашего сайта и обращаемся к нему через браузер:
http://имя_вашего_сайта/adminer-4.2.1.php (можете переименовать в adminer.php для удобства):
Теперь нажимаем Экспорт (Dump) и выгружаем данные. Причем мы можем не выгружать ненужные нам данные кэша:
Для загрузки дампа обратно используйте вкладку Импорт (Import):
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 < 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
Запустите программу:
sudo 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 в связке с любым из перечисленных выше методов.
Репликация — это процесс зеркалирования данных с ведущего сервера на другие () или с любого сервера связки на остальные серверы ().
Но поскольку репликация постоянно фиксирует изменения динамической системы, она может пострадать от резервного копирования, потому что при этом фиксируется определенный момент времени.
Чтобы устранить эту проблему, можно:
- Временно отключить репликацию
- Или временно сделать сервер резервного копирования доступным только для чтения.
Временное отключение репликации
Чтобы временно отключить репликацию на 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:
mysql -u root -p
Запишите все кэшированные изменения на диск и настройте систему только для чтения с помощью команд:
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 не сможет скопировать данные с удалённого сервера.
Копирование файлов таблиц
Этот подход имеет те же недостатки, что и скрипт mysqlhotcopy.
Конечно, использовать эту технику с механизмами хранения, которые хранят свои данные в файлах, довольно разумно; однако InnoDB, механизм хранения MySQL по умолчанию, нельзя скопировать таким образом.
Заключение
Как видите, MySQL предоставляет самые различные методы копирования данных. Все они имеют свои преимущества и недостатки, некоторые из них проще, некоторые — более широкого применения.
В целом, метод резервного копирования зависит от индивидуальных потребностей и ресурсов сервера, а также от производственной среды. Какой бы метод вы выбрали, обязательно проверьте резервные копии и попробуйте восстановить данные.
Tags: ,Для этого в командной строке вашего 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.
