Полные резервные копии файлов (sql server)

Резервное копирование с помощью SSMS

С помощью этой задачи можно выполнять резервное копирование на URL-адрес с использованием учетных данных SQL Server.

Примечание

Для создания резервных копий моментальных снимков файлов SQL Server или перезаписи существующего набора носителей необходимо использовать Transact-SQL, Powershell или C#, а не задачу резервного копирования в SQL Server Management Studio.

Ниже приведены инструкции по внесению изменений в задачу «Резервное копирование базы данных» в SQL Server Management Studio для создания резервной копии в хранилище Azure.

1. В обозревателе объектов подключитесь к экземпляру компонента SQL Server Database Engine и разверните его.

2. Разверните узел Базы данных, щелкните правой кнопкой мыши нужную базу данных, укажите пункт Задачи и выберите Копировать базу данных.

3. На странице Общие в разделе Назначение в раскрывающемся списке Создать резервную копию на: доступен вариант URL-адрес . Параметр URL-адрес используется для создания резервной копии в хранилище Windows Azure. Нажмите кнопку Добавить , чтобы открыть диалоговое окно Выбор места расположения резервной копии .

   1. Контейнер хранилища Azure. Имя контейнера хранилища Microsoft Azure для хранения файлов резервной копии. Выберите существующий контейнер в раскрывающемся списке или введите контейнер вручную.

   2. Политика подписанных URL-адресов: введите подписанный URL-адрес для контейнера, указанного вручную. Это поле недоступно, если был выбран существующий контейнер.

   3. Файл резервной копии: Имя файла резервной копии.

   4. Создать контейнер: используется для регистрации существующего контейнера, который не имеет подписанного URL-адреса. См. раздел Соединение с подпиской Microsoft Azure.

Примечание

Добавить поддерживает несколько файлов резервных копий и контейнеров хранилища для одного набора носителей.

При выборе URL-адреса в качестве назначения некоторые параметры на странице Параметры носителя отключаются. Следующие разделы содержат дополнительные сведения о диалоговом окне «Резервное копирование базы данных»:

Ограничения на операции резервного копирования

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

Нельзя создать резервную копию данных, находящихся в режиме «вне сети»

Любая операция резервного копирования, которая явно или неявно ссылается на данные, находящиеся в режиме «вне сети», завершается неудачей. Ниже следуют некоторые наиболее распространенные примеры этого.

• Запрашивается создание полной резервной копии, но одна файловая группа в базе данных находится в режиме «вне сети». Операция завершается неудачно, так как в полное резервное копирование неявно включены все файловые группы.

  Чтобы создать резервную копию этой базы данных, можно воспользоваться созданием резервных копий файлов (или файловых групп) и задать только те файловые группы, которые находятся в режиме «в сети».

• Запрашивается частичное резервное копирование, но файловые группы, доступные для чтения и записи, находятся в режиме «вне сети». Операция завершается неудачей, потому что для частичного резервного копирования запрашиваются все файловые группы, доступные для чтения и записи.

• Запрашивается резервное копирование заданных файлов, но один из файлов находится в режиме «в сети». Операция завершается неудачей. Чтобы создать резервную копию файлов, находящихся в режиме «в сети», устраните из списка файлы, находящиеся в режиме «вне сети», и повторите операцию.

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

Ограничения параллелизма

SQL Server использует процесс резервного копирования в сети, что позволяет создавать резервную копию базы данных во время ее использования. Во время резервного копирования можно производить большинство операций. Например, во время создания резервной копии разрешены инструкции INSERT, UPDATE и DELETE. При попытке приступить к выполнению операции резервного копирования во время создания или удаления файла базы данных выполнение операции резервного копирования будет отложено до завершения создания или удаления либо до истечения времени ожидания.

Следующие операции запрещены во время создания резервной копии базы данных или журнала транзакций.

• Операции управления файлами, такие как инструкция ALTER DATABASE с параметром ADD FILE или с параметром REMOVE FILE.

• Операции сжатия базы данных или файла. Сюда же включены операции автоматического сжатия.

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

Если операция резервного копирования перекрывается операцией сжатия или управления файлами, то возникает конфликт. Независимо от того, какая из конфликтующих операций начата первой, вторая операция ждет истечения времени ожидания первой (оно зависит от параметров сеанса). Если разблокировка происходит до истечения времени ожидания, работа второй операции продолжается. Если разблокировки за этот период не происходит, вторая операция заканчивается неудачно.

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

В следующем примере sqlcmd подключается к локальному экземпляру SQL Server и создает полную резервную копию пользовательской базы данных .

При выполнении команды SQL Server запросит пароль. После ввода пароля оболочка возвратит результаты выполнения резервного копирования. Пример:

Резервное копирование журнала транзакций

Если база данных находится в модели полного восстановления, то можно также создать резервные копии журналов транзакций для получения более детальных вариантов восстановления. В следующем примере sqlcmd подключается к локальному экземпляру SQL Server и создает резервную копию журнала транзакций.

Бэкап и восстановление mysql с помощью mysqldump

Теперь просто для справки приведу примеры бэкапа и восстановления баз данных mysql с помощью mysqldump. Для небольших баз этого инструмента хватает за глаза и использовать что-то другое не имеет смысла. Преимущество xtrabackup в скорости работы и в возможности без проблем сделать инкрементный бэкап. Если он вам не нужен и база не большая, достаточно будет старого доброго mysqldump.

Бэкап всех баз mysql сервера с его помощью:

# /usr/bin/mysqldump -uroot -hlocalhost -p'password' --all-databases > /root/backupdb/all.sql

Можно сразу же сжимать его.

# /usr/bin/mysqldump -uroot -hlocalhost -p'password' --all-databases | /usr/bin/gzip -c > /root/backupdb/`date "+%Y-%m-%d"`sql.gz

Бэкап конкретной базы данных.

/usr/bin/mysqldump sitemanager -uroot -p'password' | /usr/bin/gzip -c > /root/backupdb/sitemanager_`date "+%Y-%m-%d"`sql.gz

Мне чаще всего мешают в дампе команды на создание базы данных — CREATE DATABASE, поэтому я их убираю ключом no-create-db.

/usr/bin/mysqldump --no-create-db sitemanager -uroot -p'password' | /usr/bin/gzip -c > /root/backupdb/sitemanager_`date "+%Y-%m-%d"`sql.gz

Для того, чтобы восстановить базу данных из дампа, можно воспользоваться следующими командами. Выполняются из консоли mysql.

# mysql -uroot -p
mysql> use sitemanager;
mysql> source /root/backupdb/sitemanager.sql;

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

mysql> use sitemanager;
mysql> \. /root/backupdb/sitemanager.sql

Вот простенький скрипт, который позволяет забэкапить все базы данных сервера, при этом бэкап каждой базы будет в отдельном файле. Это удобнее, чем все базы единым файлом. Оттуда потом трудно доставать конкретную базу.

#!/bin/bash

for i in `mysql -uroot -p'password' -e'show databases;' | grep -v information_schema | grep -v Database`; 
    do 
	/usr/bin/mysqldump -uroot -p'password' $i | /usr/bin/gzip -c > /root/backupdb/`date +%Y-%m-%d`-$i.sql.gz;
    done

Так же могу порекомендовать вот этот скрипт для бэкапа — https://github.com/adegtyarev/mysqlbackup. Описывать его не буду, по комментариям в скрипте понятен его функционал.

Если вам нужно из полного бэкапа mysql восстановить отдельную таблицу, то ее можно выделить из полного дампа через обычный awk примерно вот так.

# cat sitemanager.sql | /usr/bin/awk '/CREATE TABLE `b_catalog_discount`/,/UNLOCK TABLES/' > /tmp/b_catalog_discount.sql

Дальше через source можно восстановить данные из этого дампа отдельной таблицы.

Иногда бывает полезно сделать не просто полный backup базы данных, а разбить его сразу на таблицы. Тут поможет следующий скрипт.

#!/bin/bash

USER='root'
PASS='R(zDXcVUmI[zwx%aNBTN'

MYSQL="mysql --user=$USER --password=$PASS --skip-column-names";
DIR="/root/backupdb"

for s in mysql `$MYSQL -e "SHOW DATABASES"`;
    do
    mkdir $DIR/$s;
    for t in `$MYSQL -e "SHOW TABLES FROM $s"`;
	do
	    /usr/bin/mysqldump --user="$USER" --password="$PASS" --opt $s $t | /usr/bin/gzip -c > $DIR/$s/$t.sql.gz;
	done
    done

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

for s in mysql sitemanager;

Восстановить потом всю базу из такого потабличного бэкапа можно таким образом.

#!/bin/bash
#

DB=sitemanager;
USER='root'
PASS='R(zDXcVUmI[zwx%aNBTN'
DIR="/root/backupdb/sitemanager"

for s in `ls -1 $DIR`;
    do
    echo "--> $s restoring... ";
    zcat $DIR/$s | /usr/bin/mysql --user=$USER --password=$PASS $DB;
    done

При использовании пароля в открытом виде в mysql или mysqldump, в консоль постоянно сыпятся предупреждения.

mysql:  Using a password on the command line interface can be insecure.

Чтобы их не было, перенесите, как я показывал выше, пароль в отдельный файл ~/.my.cnf, а из скрипта уберите авторизацию вообще.

На этом, пожалуй, насчет бэкапа баз mysql сервера все. Поделился основными своими наработками.

Общие сведения о резервных копиях файлов

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

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

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

Примечание

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

Резервные копии файлов и простая модель восстановления

В простой модели восстановления резервные копии файлов для чтения и записи должны создаваться вместе. Это гарантирует восстановление базы данных до согласованного момента времени. Вместо того чтобы указывать каждый файл или файловую группу для чтения и записи, воспользуйтесь параметром READ_WRITE_FILEGROUPS. Этот параметр создает резервные копии всех файловых групп, доступных для чтения и записи, в базе данных. С помощью параметра READ_WRITE_FILEGROUPS создаются так называемой частичной резервной копией. Дополнительные сведения см. в разделе Частичные резервные копии (SQL Server).

Резервные копии файлов и модель полного восстановления

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

Восстановить базу данных лишь из файла и резервных копий журналов может оказаться сложно. Поэтому лучше выполнить полное резервное копирование базы данных, а затем начать резервное копирование журнала, чем сразу создавать резервную копию файлов. На следующем рисунке показана стратегия, согласно которой создается полная резервная копия базы данных (за время t1) вскоре после создания базы данных (за время t0). Эта первая резервная копия базы данных позволяет начать резервное копирование журнала транзакций. Резервное копирование журнала транзакций запланировано через определенные промежутки времени. Резервные копии файлов создаются через некоторый интервал времени, оптимально соответствующий требованиям предприятия. На данном рисунке показана каждая из четырех файловых групп, резервное копирование которых происходит одновременно. Порядок, в котором оно производится (группы A, C, B, A), отражает требования предприятия к базе данных.

Примечание

При использовании модели полного восстановления необходимо выполнить накат всех журналов транзакций во время восстановления резервной копии файла для записи и чтения, чтобы обеспечить согласованность состояния файла с остальной частью базы данных. Чтобы избежать необходимости наката большого количества резервных копий журналов транзакций, следует чаще создавать разностные резервные копии файлов. Дополнительные сведения см. в разделе Разностные резервные копии (SQL Server).

Как сделать бэкап базы MySQL

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

PhpMyadmin. Зайдите в панель управления базами данных на своем хостинге, на примере Макхост это раздел «Управление услугами» далее «Базы данных», где выбираете ту которую надо скопировать, и жмем перейти в «Администрирование баз».

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

Далее выбираете способ экспорта, лично я оставляю «Быстрый», но если вы хотите покопаться в настройках, например исключить какие-то таблицы, выбрать компрессию, кодировку и прочее, тогда ставьте «Обычный».

Указываете формат SQL и жмете кнопку «Ок», далее останется подождать пока копия базы MySQL закачается на компьютер.

Плагин. Но если вы хотите чтобы бэкапы базы данных создавались автоматически, тогда рекомендую присмотреться к плагину WordPress Database Backup, скачать его можно по этой ссылке.

После установки модуля и активации переходите к его настройкам в инструментах административной панели блога.

Теперь у вас появилась возможность в любой момент времени создать архив с копией базы данных, который можно сохранить на хостинга по указанному адресу, скачать на компьютер или отправить по электронной почте (Mail, Яндекс, Gmail и др.).

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

Я поставил периодичность 1 раз в день и завел для этого отдельных почтовый аккаунт, чтобы не захламлять рабочий адрес архивами копий баз блога. Как видите, нет ничего сложного!

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

Пока

Лучше потратить пару минут времени, чем несколько месяцев на восстановления проекта из веб-архивов. Пока

Резервное копирование частичных данных

Partial Backups Percona XtraBackup features partial backups, which means that you may backup only some specific tables ordatabases. The tables you back up must be in separate tablespaces, as a result of being created or altered after youenabled the innodb_file_per_table option on the server.

Физические файлы таблицы должны быть отдельными

Варианты использования резервной копии

Using the —include option The regular expression provided to this will be matched against the fully qualified table name, including the database name, in the form databasename.tablename.

For example,

$ innobackupex —include=’^mydatabasemytable’ /path/to/backup

Можно указать регулярное выражение

Using the —tables-file option The text file provided (the path) to this option can contain multiple table

names, one per line, in the databasename.tablename format.

For example,

$ echo «mydatabase.mytable» > /tmp/tables.txt

$ innobackupex —tables-file=/tmp/tables.txt /path/to/backup

Использовать файл, содержащий таблицу

Using the —databases option This option accepts either a space-separated list of the databases and tables to backup — in the databasename form — or a file containing the list at one element per line.

For example,

При подготовке необходимо добавить опцию —export

Preparing Partial Backups For preparing partial backups, the procedure is analogous to restoring individual tables

: apply the logs and use the —export option:

$ innobackupex —apply-log —export /path/to/partial/backup

Создание горячей резервной копии MySQL с одновременным копированием на удаленный сервер

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

В данной статье я расскажу как при помощи Percona Xtrabackup можно легко и быстро делать горячие резервные копии MySQL (горячие — значит без остановки MySQL) с одновременным перемещением копии на удаленный сервер, при этом на локальном сервере резервная копия не оседает и не занимает место, что очень удобно.

Исходные данные: Ubuntu 18.04 (bionic), Oracle MySQL 5.7.29Задача: Создать горячую (hotbackup) резервную копию MySQL и переместить ее на удаленный сервер, при этом не сохраняя ее на текущем сервере, чтобы не занимать драгоценное место.

Для начала нам нужно установить Percona Xtrabackup и набор утилит qpress, socat, nc на оба сервера. Если все утилиты у Вас уже установлены, то пропустите этот шаг.

1. Скачиваем и устанавливаем пакет percona-release_latest, включаем репозитарий percona tools (release) и устанавливаем набор полезных утилит от Percona Toolkit, устанавливаем Percona Xtrabackup и вспомогательные утилиты qpress, socat

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb && rm -f percona-release_latest.$(lsb_release -sc)_all.deb
percona-release enable-only tools release
apt-get update
apt-get install -y percona-toolkit percona-xtrabackup-24 qpress socat screen

2. На сервере приемнике (куда будем копировать резервную копию) создаем каталог, куда будет загружаться резервная копия и запускаем socat + xbstream. Все действия выполняем находясь под пользователем root. Перед запуском Вы должны проверить, что на сервере-приемнике достаточно места на файловой системе для приема бэкапа. Запускать socat желательно в screen чтобы в случае обрыва ssh сессии процесс приема не прервался.

mkdir /root/mysqlbackup
screen
socat -u tcp-listen:9999,reuseaddr stdout 2>/tmp/socat.log | xbstream -x -C /root/mysqlbackup

на что следует обратить внимание: а) Директива tcp-listen:9999 указывает, что мы будем принимать входящие подключения по протоколу TCP на порт 9999, не забудьте открыть порт на межсетевом экране; б) Принятый поток будет передаваться xbstream, а тот будет сохранять данные в каталог /root/mysqlbackup, проверьте чтобы на диске было достаточно места для данных. Сколько необходимо? Столько, сколько на источнике занимает каталог с данными MySQL, как правило это /var/lib/mysql, но он может меняться, смотрите на источнике в MySQL настройку datadir, она покажет каталог хранения данных

3. На источнике запускаем xtrabackup в формат потокового резервного копирования xbstream. Запускать xtrabackup желательно в screen чтобы в случае обрыва ssh сессии процесс создания и передачи резервной копии не прервался.

screen
ulimit -n 256000 && /usr/bin/xtrabackup --defaults-file=/etc/mysql/my.cnf --host=127.0.0.1 --port 3306 --user=root --password=mysqlrootpasswd --backup --no-backup-locks --no-lock --parallel=4 --stream=xbstream | socat - TCP4:172.16.0.1:9999

на что следует обратить внимание: а) Директива —defaults-file указывает местоположение файла конфигурации MySQL, это директива должна быть самой первой в списке; б) Директивы —host, —port, —user и —password указывают соответствующие опции подключения к работающему MySQL, это у Вас они будут своими, как минимум пароль пользователя root; в) Директива —no-backup-locks указывает, что не нужно делать блокировок при резервном копировании; г) Директива —no-lock указывает, что не нужно делать блокировок таблиц при резервном копировании; д) Директива —parallel указывает, во сколько потоков будет идти резервное копирование; е) Директива —stream указывает формат потокового резервного копирования; ж) Директива для socat — TCP4:172.16.0.1:9999 — указывают протокол, IP адрес и порт приемника, это адрес сервера из п.2;

После запуска xtrabackup начнется процесс создания горячей резервной копии и одновременной передачи её на сервер-приемник.

В следующей статье я расскажу как можно восстановить полностью рабочий экземпляр MySQL из созданное нами резервной копии.

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

Пример скрипта

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

Создаем каталог для скриптов и сам скрипт:

mkdir /scripts

vi /scripts/mysql_backup.sh

1. #!/bin/bash
2. PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
3. destination=»/backup/mysql»
4. userDB=»backup»
5. passwordDB=»backup»
6. fdate=`date +%Y-%m-%d`
7. find $destination -type d \( -name «*-1» -o -name «*-?» \) -ctime +30 -exec rm -R {} \; 2>&1
8. find $destination -type d -name «*-*» -ctime +180 -exec rm -R {} \; 2>&1
9. mkdir $destination/$fdate 2>&1
10. for dbname in `echo show databases | mysql -u$userDB -p$passwordDB | grep -v Database`; do
11.     case $dbname in
12.         information_schema)
13.             continue ;;
14.         mysql)
15.             continue ;;
16.         performance_schema)
17.             continue ;;
18.         test)
19.             continue ;;
20.         *) mysqldump —databases —skip-comments -u$userDB -p$passwordDB $dbname | gzip > $destination/$fdate/$dbname.sql.gz ;;
21.     esac
22. done;

Задаем права скрипту на выполнение:

chmod +x /scripts/mysql_backup.sh

Восстановление базы данных с помощью полной резервной копии

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

Дополнительные сведения см. в разделе Выполнение полного восстановления базы данных (простая модель восстановления) или Выполнение полного восстановления базы данных (модель полного восстановления).

Заключение

Решение задачи по бэкапу mysql баз, что я описал, не претендует на уникальность и 100% правильность. Это просто мой личный опыт. Никаких особых изысканий и поиска наилучшего решения не проводил. Просто сделал, как сделал, чем с вами и поделился. В моих задачах такой подход достаточен.

Еще раз напоминаю, что этот способ актуален для относительно небольших баз. Дампить объемные базы плохая идея, так как будет сильно проседать i/o дисков. Плюс тут нет возможности делать инкрементные бэкпы. Только полные, что, очевидно, не всегда удобно.

Для мониторинга бэкапов в целом, можете воспользоваться моей объемной статьей по теме — Мониторинг бэкапов с помощью zabbix.

Онлайн курсы по Mikrotik

Если у вас есть желание научиться работать с роутерами микротик и стать специалистом в этой области, рекомендую пройти курсы по программе, основанной на информации из официального курса MikroTik Certified Network Associate. Помимо официальной программы, в курсах будут лабораторные работы, в которых вы на практике сможете проверить и закрепить полученные знания. Все подробности на сайте .

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

• Знания, ориентированные на практику;
• Реальные ситуации и задачи;
• Лучшее из международных программ.