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

MySql, Microsoft SQL, MSSQL
Аватара пользователя
Артём Мамзиков
Admin
Сообщения: 523
Стаж: 3 года 7 месяцев
Откуда: Вологодская область
Поблагодарили: 23 раза
Контактная информация:

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

Сообщение Артём Мамзиков »

Бэкап / Backup Базы данных MySQL Zabbix
Будем делать на основе скрипта на Bash shell, при помощи утилиты MySQLDUMP
так же есть на подобии MySQLPUMP

Будет 2 варианта выгрузки базы INSERT и REPLACE полностью вся структура базы данных, данные таблиц кроме данных таблиц истории. Все это в один файл и в архив, в моем случае БД получается в районе 300МБ всего.
Так же файл сразу копируется на NAS и все проверяется шаблоном как прошло создание копии и на месте ли все копии.

Более подробно все описано в скрипте

Код: Выделить всё

#!/bin/bash

# Копия БД

# Переменные
# Хост, на котором расположен сервер MySQL
HOST=localhost
# Порт
PORT=3306
# Пользователь
USER=zabbix
# Пароль
PASSWORD=Zab
# Имя Базы Данных
DATABASE=zabbixdb

# Отправка состояния zabbix trapper
IPZBXSERVER=127.0.0.1
TRAPPORT=10051
HOSTHOST="ZABBIXSERVER"

function nas_copy_bk() {
# Подключаемся к NAS и делаем копию старые удаляем
# Создаем каталог mkdir
mkdir /mnt/ZabbixBK > /dev/null 2>&1
p=Пароль; l=Логин # Пароль Логин для подключения
PASSWD="$p" mount "//192.168.175.19/Volume_1/BACKFSCentr/ZabbixBK" "/mnt/ZabbixBK" -o username="$l",iocharset=utf8,file_mode=0666,dir_mode=0777
# Проверка что все с монтировалось
check=`cat /mnt/ZabbixBK/check`
if [ "$check" == "1" ];then

# Копируем в nas
cp -nr /root/ZabbixBK/* /mnt/ZabbixBK

# Удаляем файлы бэкапы и логи старше 3 суток.
find /mnt/ZabbixBK/ -type f -mtime +3 |sed -e '/'check'/d'| xargs rm -rfv
# Проверка 2х копий на NAS
bkday=$(ls -aF "/mnt/ZabbixBK"|grep -E `date +*-%Y.%m.%d.sql.gz`| wc -l)
if [[ $bkday == 2 ]]; then zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k structure-data-dump-day -o "1"; else zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k structure-data-dump-day -o "0"; fi

# Удаляем файлы бэкапы и логи старше 31 дня.
find /mnt/ZabbixBK/week -type f -mtime +31 | xargs rm -rfv
# Проверка копий каждый ПН (раз в неделю) на NAS
pn=`date +%u`
if [[ $pn == 1 ]]; then bkweek=$(ls -aF "/mnt/ZabbixBK/week"|grep -E `date +*-%Y.%m.%d.sql.gz`| wc -l)
if [[ $bkweek == 2 ]]; then zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k structure-data-dump-week -o "1"; else zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k structure-data-dump-week -o "0"; fi
fi

# Удаляем файлы бэкапы и логи старше 6 месяцев.
find /mnt/ZabbixBK/months -type f -mtime +183 | xargs rm -rfv
# Проверка копий на 6 месяцев на NAS
ms=`date +%d`
if [[ $ms == 01 ]]; then bkmonths=$(ls -aF "/mnt/ZabbixBK/months"|grep -E `date +*-%Y.%m.%d.sql.gz`| wc -l)
if [[ $bkmonths == 2 ]]; then zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k structure-data-dump-months -o "1"; else zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k structure-data-dump-months -o "0"; fi
fi

# От монтировать старую папку
umount /mnt/ZabbixBK

fi
}

# Представление (Дизайнер) CREATE VIEW
#mysql -u username INFORMATION_SCHEMA --skip-column-names --batch -e "select table_name from tables where table_type = 'VIEW' and table_schema = 'database'" | xargs mysqldump -u username database > views.sql


# ВЫГРУЗКА БД в INSERT - Создать (удалить все предыдущее)
INSERTDB=`date +/root/ZabbixBK/INSERT-$DATABASE-%Y.%m.%d.sql.gz`

echo "Структура дампа"
# -Q --quote-names оборачивает имена обратными кавычками
# -c --complete-insert делает полную вставку, включая имена колонок
# -e --extended-insert делает расширенную вставку. Итоговый файл получается меньше и делается он чуть быстрее
#mysqldump| Хост| Порт| Пользователь| Пароль| БД| Удалить БД перед Созданием БД| Удалить таблицу перед Перед ее созданием| Блокировка и Разблокировка таблиц| Отключение-включение ключей таблицы| Таблицы с сервера построчно за раз|  добавляет в дамп информацию о кодировках| Параметр создает дамп в виде одной транзакции| Временная зона| Двоичных столбцов с использованием шестнадцатеричной записи| Дамп хранимых подпрограмм (процедур и функций) из выгруженных баз данных| События| Триггеры| в дамп добавляются команды на создание таблиц| 	Добавить комментарии к файлу дампа| сортировка по пер.ключу или ун.индексу| Не сбрасывать содержимое таблицы| Cообщения об ошибках в файл| Сжать в архив
mysqldump -Q -c -e --host=${HOST} --port=${PORT} --user=${USER} --password=${PASSWORD} --databases ${DATABASE} --add-drop-database --add-drop-table --add-locks --disable-keys  --quick --set-charset --single-transaction --tz-utc --hex-blob --routines --events --triggers --create-options --comments --order-by-primary --no-data --log-error=/root/ZabbixBK/mysqldump_insert_error.log| gzip > "${INSERTDB}"

# Проверяем первую и последнюю строки Структуры дампа
BEGIN=`zcat "${INSERTDB}" | head -n 1 | grep ^'-- MySQL dump' | wc -l`
END=`zcat "${INSERTDB}" | tail -n 1 | grep ^'-- Dump completed' | wc -l`
let ANVERS=$BEGIN+$END
if [ "$ANVERS" == "2" ];then
    echo "OK-structure-dump-insert"
# Отправка состояния выполнения первого этапа
zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k structure-dump-insert -o "1"

# Игнорируемые таблицы
EXCLUDED_TABLES=(
acknowledges
alerts
auditlog
events
history
history_log
history_str
history_text
history_uint
housekeeper
problem
problem_tag
service_alarms
trends
trends_uint
)

IGNORED_TABLES_STRING=''
for TABLE in "${EXCLUDED_TABLES[@]}"
do :
   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done

echo "Дамп содержимого"
#mysqldump| Хост| Порт| Пользователь| Пароль| БД| Не добавлять удаление БД| Не добавлять удаление Таблиц|  Окружить таблицы LOCK TABLES и UNLOCK| Не использовать CREATE DATABASE| Не писать создание таблиц| Не сбрасывать триггеры| Строки построчно за раз| Информация о кодировках| Cоздает дамп в виде одной транзакции| Cортировка по пер.ключу или ун.индексу| Двоичных столбцов с использованием шестнадцатеричной записи| Игнорируемые таблицы| Cообщения об ошибках в файл| Сжать в архив
mysqldump -Q -c -e --host=${HOST} --port=${PORT} --user=${USER} --password=${PASSWORD} --databases ${DATABASE} --skip-add-drop-database --skip-add-drop-table --add-locks --no-create-db --no-create-info --skip-triggers --quick --set-charset --single-transaction --order-by-primary --hex-blob ${IGNORED_TABLES_STRING} --log-error=/root/ZabbixBK/mysqldump_insert_error.log | gzip >> "${INSERTDB}"

else
    echo "Not-structure-dump-insert"
# Отправка состояния выполнения первого этапа
zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k structure-dump-insert -o "0"
fi

# Проверяем первую и последнюю строки Структура + Содержимое дампа
BEGIN='';END='';ANVERS='' # Сброс пред. переменных
BEGIN=`zcat "${INSERTDB}" | head -n 1 | grep ^'-- MySQL dump' | wc -l`
END=`zcat "${INSERTDB}" | tail -n 1 | grep ^'-- Dump completed' | wc -l`
let ANVERS=$BEGIN+$END
if [ "$ANVERS" == "2" ];then
    echo "OK-data-dump-insert"
# Отправка состояния выполнения второго этапа
zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k data-dump-insert -o "1"
else
    echo "Not-data-dump-insert"
# Отправка состояния выполнения второго этапа
zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k data-dump-insert -o "0"
fi




# ВЫГРУЗКА БД в REPLACE - Создать, если уже существует заменить
REPLACE=`date +/root/ZabbixBK/REPLACE-$DATABASE-%Y.%m.%d.sql.gz`

echo "Структура дампа"
# -Q --quote-names оборачивает имена обратными кавычками
# -c --complete-insert делает полную вставку, включая имена колонок
# -e --extended-insert делает расширенную вставку. Итоговый файл получается меньше и делается он чуть быстрее
#mysqldump| Хост| Порт| Пользователь| Пароль| БД| Не добавлять удаление БД| Не добавлять удаление Таблиц| Не добавлять блокировки таблиц| Не отключать ключи| Параметр создает дамп в виде одной транзакций| временная зона| двоичных столбцов с использованием шестнадцатеричной записи| Дамп хранимых подпрограмм (процедур и функций) из выгруженных баз данных| События| Триггеры| Не сбрасывать содержимое таблицы| таблицы с сервера построчно за раз|  добавляет в дамп информацию о кодировках| в дамп добавляются команды на создание таблиц|  Добавить комментарии к файлу дампа| сортировка по пер.ключу или ун.индексу| Cообщения об ошибках в файл| Не создавать таблицу если уже есть| Не создавать события если уже есть| Не создавать процедуры если уже есть| Сжать в архив
mysqldump -Q -c -e --host=${HOST} --port=${PORT} --user=${USER} --password=${PASSWORD} --databases ${DATABASE} --skip-add-drop-database --skip-add-drop-table --skip-add-locks --skip-disable-keys --single-transaction --tz-utc --hex-blob --routines --events --triggers --no-data --quick  --set-charset --create-options --comments --order-by-primary --log-error=/root/ZabbixBK/mysqldump_replace_error.log| sed 's/^CREATE TABLE /CREATE TABLE IF NOT EXISTS /'| sed "s/^CREATE.* DEFINER=.* EVENT /&IF NOT EXISTS /"| sed "s/^CREATE DEFINER=.* PROCEDURE /&IF NOT EXISTS /"| gzip > "${REPLACE}"

# Проверяем первую и последнюю строки Структуры дампа
BEGIN='';END='';ANVERS='' # Сброс пред. переменных
BEGIN=`zcat "${REPLACE}" | head -n 1 | grep ^'-- MySQL dump' | wc -l`
END=`zcat "${REPLACE}" | tail -n 1 | grep ^'-- Dump completed' | wc -l`
let ANVERS=$BEGIN+$END
if [ "$ANVERS" == "2" ];then
    echo "OK-structure-dump-replace"
# Отправка состояния выполнения первого этапа
zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k structure-dump-replace -o "1"
# Игнорируемые таблицы
EXCLUDED_TABLES=(
acknowledges
alerts
auditlog
events
history
history_log
history_str
history_text
history_uint
housekeeper
problem
problem_tag
service_alarms
trends
trends_uint
)

IGNORED_TABLES_STRING=''
for TABLE in "${EXCLUDED_TABLES[@]}"
do :
   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done

echo "Дамп содержимого"
#mysqldump| Хост| Порт| Пользователь| Пароль| БД| Не пишите операторы CREATE TABLE, которые заново создают каждую выгруженную таблицу| Не добавлять удаление БД| Не добавлять удаление Таблиц|  Не добавлять замки| Не использовать CREATE DATABASE| Не писать создание таблиц| Не сбрасывать триггеры| Строки построчно за раз| Информация о кодировках| Cоздает дамп в виде одной транзакци| Cортировка по пер.ключу или ун.индексу| REPLACE вместо INSERT| двоичных столбцов с использованием шестнадцатеричной записи| Не сбрасывать данные таблицы| Cообщения об ошибках в файл| Сжатие в архив
mysqldump -Q -c -e --host=${HOST} --port=${PORT} --user=${USER} --password=${PASSWORD} --databases ${DATABASE} --no-create-info  --skip-add-drop-database --skip-add-drop-table --skip-add-locks --no-create-db --no-create-info --skip-triggers --quick --set-charset --single-transaction --order-by-primary --replace --hex-blob ${IGNORED_TABLES_STRING} --log-error=/root/ZabbixBK/mysqldump_replace_error.log| gzip >> "${REPLACE}"

else
    echo "Not-structure-dump-replace"
# Отправка состояния выполнения первого этапа
zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k structure-dump-replace -o "0"
fi

# Проверяем первую и последнюю строки Структура + Содержимое дампа
BEGIN='';END='';ANVERS='' # Сброс пред. переменных
BEGIN=`zcat "${REPLACE}" | head -n 1 | grep ^'-- MySQL dump' | wc -l`
END=`zcat "${REPLACE}" | tail -n 1 | grep ^'-- Dump completed' | wc -l`
let ANVERS=$BEGIN+$END
if [ "$ANVERS" == "2" ];then
    echo "OK-data-dump-replace"
# Отправка состояния выполнения первого этапа
zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k data-dump-replace -o "1"
# Удаляем файлы бэкапы и логи старше 3 суток.
find /root/ZabbixBK -type f -mtime +3 |sed -e '/'check'/d'| xargs rm -rfv

# Копия каждый ПН (раз в неделю)
pn=`date +%u`
if [[ $pn == 1 ]]; then cp -f /root/ZabbixBK/{`date +*-%Y.%m.%d.sql.gz`,*error.log} /root/ZabbixBK/week; fi
# Удаляем файлы бэкапы и логи старше 31 дня.
find /root/ZabbixBK/week -type f -mtime +31 | xargs rm -rfv

# Копия на 6 месяцев
ms=`date +%d`
if [[ $ms == 01 ]]; then cp -f /root/ZabbixBK/{`date +*-%Y.%m.%d.sql.gz`,*error.log} /root/ZabbixBK/months; fi
# Удаляем файлы бэкапы и логи старше 6 месяцев.
find /root/ZabbixBK/months -type f -mtime +183 | xargs rm -rfv

# Запускаем копирование в nas
nas_copy_bk
else
	echo "Not-data-dump-replace"
# Отправка состояния выполнения первого этапа
zabbix_sender -z $IPZBXSERVER -p $TRAPPORT -s "${HOSTHOST}" -k data-dump-replace -o "0"
fi


# Пример: Заливаем архив бэкапа в базу
#gunzip < `date +/root/$DATABASE.%Y.%m.%d.sql.gz` | mysql -u $USER -p$PASSWORD $DATABASE
# или так
#zcat `date +/root/$DATABASE.%Y.%m.%d.sql.gz` | mysql -u $USER -p$PASSWORD $DATABASE
Шаблон цепляем к самому ZABBIXSERVER
В шаблоне используется макрос текущей даты в середине поста как получить текущую дату
Скачать Скрипт + Шаблон
Backup-ZabbixDB.zip
Backup-ZabbixDB.zip
(7.07 КБ) 1 скачивание

Второй шаблон проверяет копии не посредственно на NAS Хранилище через Узел у которого есть доступ к nas в моем случае это Windows
Проверка-Копии-ZabbixDB-на-NAS.zip
Проверка-Копии-ZabbixDB-на-NAS.zip
(2.08 КБ) 0 скачиваний
Так же есть один момент необходима авторизация с узла на nas хранилище от Локального пользователя так как от него работает Zabbix агент
Создано действие:
Доступ к NAS от Локального пользователя
Имя триггера содержит NAS
Имя триггера содержит Файл Backup
Узел сети равно File server 192.168.175.48

Операции прописаны команды авторизации через заббикс агент на текущем узле
net use \\192.168.175.19\Volume_1 /user:Логин Пароль /persistent:yes
net use \\\\192.168.175.19\\Volume_1 /user:Логин Пароль /persistent:yes
количество слов: 1209
Партнёрская ссылка на Хостинг Beget https://beget.com/p937243

Вернуться в «Базы Данных/Databases»