Примеры работы с mysql в python при помощи модуля pymysql

Введение

В качестве примера я рассмотрю серверы с установленными там продуктами bitrix, работающими в bitrixenv. Особенностью будет то, что bitrix до сих пор использует не самую свежую версию mysql от percona — Percona Server for MySQL 5.7. Тем не менее, проблем с этим нет никаких. Версия будет поддерживаться минимум до октября 2023 года.

Для полных и инкрементных бэкапов я рассмотрю утилиту Percona XtraBackup, которая позволяет делать архивы баз данных на лету без блокировок таблиц. В моей статье будет использоваться версия 2.4, так как именно она поддерживает mysql 5.7. Это максимально доступная версия в репозиториях, которые устанавливает окружение bitrixenv.

Примеры в этой статье будут актуальны практически для всех версий Mysql и XtraBackup, так как в подходах и командах отличий почти нет

Важно знать, что последняя версия XtraBackup на момент написания статьи была 8.0 и она поддерживает популярный форк mysql — MariaDB только до версии 10.2 включительно, да и то с оговорками. Для более поздних версий mariadb рекомендуется использовать mariabackup

Это форк XtraBackup, который в использовании практически ничем не отличается от оригинала.

Сегодня я рассмотрю инкрементные бэкапы mysql только с помощью XtraBackup, а так же полные бэкапы в том числе с помощью mysqldump. MariaDB и Mariabackup рассматривать не буду. Принципиальных отличий между ними нет. Там все то же самое.

Возможные ошибки

В процессе восстановления мы можем столкнуться с разными ошибками. Рассмотрим их примеры.

MySQL server has gone away

Во время восстановления базы может выскочить ошибка:

at line xxx: MySQL server has gone away.

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

> SHOW VARIABLES LIKE ‘max_allowed_packet’;

Чтобы увеличить значение параметра, открываем конфигурационный файл my.cnf:

vi /etc/my.cnf

* в некоторых версиях СУБД конфиг может находится по пути /etc/my.cnf.d/server.cnf.

В разделе редактируем или добавляем:

…
max_allowed_packet = 512M

* значение для данного параметра не обязательно должно быть таким большим.

Перезапускаем mysql:

systemctl restart mariadb || systemctl restart mysql

Row size too large

Ошибка выскакивает после небольшого времени работы восстановления. Более полный текст выглядит, примерно, так:

ERROR 1118 (42000) at line 608: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

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

Решение:

Для решения проблемы мы можем добавить опцию innodb_strict_mode со значением . Данная опция регламентирует более строгий режим работы СУБД. Это грубое решение, которое позволит нам добиться результата, но мы можем выполнить настройку тонко — об этом можно прочитать на соответствующей странице блога mithrandir.ru.

Мы же сделаем все по-быстрому. Открываем конфигурационный файл СУБД — его местоположение зависит от версии и реализации, например:

vi /etc/mysql/mariadb.conf.d/50-server.cnf

* это пример расположения для базы MariaDB 10. Более точное расположение можно найти в файле /etc/my.cnf.

Приводим опцию innodb_strict_mode к виду:

…
innodb_strict_mode = 0

Перезапускаем сервис:

systemctl restart mariadb

* в данном примере мы перезапустили сервис для mariadb.

Logical vs Physical Backups

Logical backups consist of the SQL statements necessary to restore the data, such as CREATE DATABASE, CREATE TABLE and INSERT.

Physical backups are performed by copying the individual data files or directories.

The main differences are as follows:

logical backups are more flexible, as the data can be restored on other hardware configurations, MariaDB versions or even on another DBMS, while physical backups cannot be imported on significantly different hardware, a different DBMS, or potentially even a different MariaDB version.
logical backups can be performed at the level of database and table, while physical databases are the level of directories and files. In the MyISAM and InnoDB storage engines, each table has an equivalent set of files. (In versions prior to MariaDB 5.5, by default a number of InnoDB tables are stored in the same file, in which case it is not possible to backup by table. See .)
logical backups are larger in size than the equivalent physical backup.
logical backups takes more time to both backup and restore than the equivalent physical backup.
log files and configuration files are not part of a logical backup

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

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

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

mkdir /scripts

vi /scripts/mysql_backup.sh

#!/bin/bash
PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
destination=»/backup/mysql»
userDB=»backup»
passwordDB=»backup»
fdate=`date +%Y-%m-%d`
find $destination -type d $ -name «*-1» -o -name «*-?» $ -ctime +30 -exec rm -R {} \; 2>&1
find $destination -type d -name «*-*» -ctime +180 -exec rm -R {} \; 2>&1
mkdir $destination/$fdate 2>&1
for dbname in `echo show databases | mysql -u$userDB -p$passwordDB | grep -v Database`; do
case $dbname in
information_schema)
continue ;;
mysql)
continue ;;
performance_schema)
continue ;;
test)
continue ;;
*) mysqldump —databases —skip-comments -u$userDB -p$passwordDB $dbname | gzip > $destination/$fdate/$dbname.sql.gz ;;
esac
done;

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

chmod +x /scripts/mysql_backup.sh

Create MySQL Backup Script

Now, copy the following content in a script file (like: /backup/mysql-backup.sh) and save on your Linux system. Use this link to download script. After than change some configuration values in section “Update below values” in the script as per your environment.

Shell

#!/bin/bash

################################################################
##
## MySQL Database Backup Script
## Written By: Rahul Kumar
## URL: https://tecadmin.net/bash-script-mysql-database-backup/
## Last Update: Jan 05, 2019
##
################################################################

export PATH=/bin:/usr/bin:/usr/local/bin
TODAY=`date +»%d%b%Y»`

################################################################
################## Update below values ########################

DB_BACKUP_PATH=’/backup/dbbackup’
MYSQL_HOST=’localhost’
MYSQL_PORT=’3306′
MYSQL_USER=’root’
MYSQL_PASSWORD=’mysecret’
DATABASE_NAME=’mydb’
BACKUP_RETAIN_DAYS=30 ## Number of days to keep local backup copy

#################################################################

mkdir -p ${DB_BACKUP_PATH}/${TODAY}
echo «Backup started for database — ${DATABASE_NAME}»

mysqldump -h ${MYSQL_HOST} \
-P ${MYSQL_PORT} \
-u ${MYSQL_USER} \
-p${MYSQL_PASSWORD} \
${DATABASE_NAME} | gzip > ${DB_BACKUP_PATH}/${TODAY}/${DATABASE_NAME}-${TODAY}.sql.gz

if ; then
echo «Database backup successfully completed»
else
echo «Error found during backup»
exit 1
fi

##### Remove backups older than {BACKUP_RETAIN_DAYS} days #####

DBDELDATE=`date +»%d%b%Y» —date=»${BACKUP_RETAIN_DAYS} days ago»`

if ; then
cd ${DB_BACKUP_PATH}
if && ; then
rm -rf ${DBDELDATE}
fi
fi

### End of script ####

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

#!/bin/bash

################################################################
##
##   MySQL Database Backup Script
##   Written By: Rahul Kumar
##   URL: https://tecadmin.net/bash-script-mysql-database-backup/
##   Last Update: Jan 05, 2019
##
################################################################

export PATH=/bin/usr/bin/usr/local/bin

TODAY=`date+»%d%b%Y»`

################################################################
################## Update below values ########################

DB_BACKUP_PATH=’/backup/dbbackup’

MYSQL_HOST=’localhost’

MYSQL_PORT=’3306′

MYSQL_USER=’root’

MYSQL_PASSWORD=’mysecret’

DATABASE_NAME=’mydb’

BACKUP_RETAIN_DAYS=30## Number of days to keep local backup copy

#################################################################

mkdir-p${DB_BACKUP_PATH}/${TODAY}

echo»Backup started for database — ${DATABASE_NAME}»

mysqldump-h${MYSQL_HOST}\

-P${MYSQL_PORT}\

-u${MYSQL_USER}\

-p${MYSQL_PASSWORD}\

${DATABASE_NAME}|gzip>${DB_BACKUP_PATH}/${TODAY}/${DATABASE_NAME}-${TODAY}.sql.gz

if$?-eq;then

echo»Database backup successfully completed»

else

echo»Error found during backup»

exit1

fi

##### Remove backups older than {BACKUP_RETAIN_DAYS} days #####

DBDELDATE=`date+»%d%b%Y»—date=»${BACKUP_RETAIN_DAYS} days ago»`

if!-z${DB_BACKUP_PATH};then

cd${DB_BACKUP_PATH}

if!-z${DBDELDATE}&& ;then

rm-rf${DBDELDATE}

fi

### End of script ####

After creating or downloading script make sure to set execute permission to run properly.

chmod +x /backup/mysql-backup.sh

Установка Percona XtraBackup

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

# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Дальше ставим нужную нам версию программы. Самую последнюю 8.0:

# yum install percona-xtrabackup-80

или 2.4

# yum install percona-xtrabackup-24

Обращаю внимание, что если на сервере с установленным bitrixenv установить просто пакет xtrabackup, без указания версии, будет установлена версия 2.3, которая не работает с уставленным там же по дефолту сервером mysql 5.7. Устанавливаем в Debian/Ubuntu:

Устанавливаем в Debian/Ubuntu:

# wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
# dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb

И сам пакет:

# apt update && apt install percona-xtrabackup-80

Binary backup using Percona XtraBackup

Advantages:

UrBackup only transfers changed data
The complete database is not read during the more frequent backups (only differences)
Configuration option if indexes should be included in the backup
The database is not slowed down by a snapshot

Disadvantages:

You need to install a version of Percona XtraBackup matching your database
Might be slightly slower than the backup using snapshots
Currently only implemented and documented for Linux

Conclusion: Best method for high traffic and large MySQL/MariaDB instances.

How to setup (Linux):

Change to in and change other configuration options if necessary.

Setup the server by running

This will configure backups of the correct directories and create a virtual client for the incremental backups.

Test if XtraBackup works by running:

Then configure the backup interval of to be relatively small and the backup interval of the main client with the (virtual) full backup script
relatively big and schedule it via backup window at a time where it does not interfere with database usage.

How to restore:

Run the script

to restore your database server to an incremental or full database backup. The script will allow you to select the full/incremental backup you want to restore to, start/stop MySQL/MariaDB and then automatically apply first the full and then the incremental backups.

7.4. Как сделать импорт или экспорт базы данных через SSH? Работа с БД по SSH

Для импорта сначала загружаете дамп на сервер. Дамп должен быть в формате .sql
Далее подключаетесь на сервер по ssh и выполняете команду:

mysql -u пользователь_БД -p имя_БД < файл_дампа_БД

В случае неудачи всегда будет указанна ошибка из за которой импорт не удался или прервался.

Для экспорта существует утилита, которая позволяет сделать бэкап базы данных в традиционном SQL-формате – mysqldump. Общий вид в командной строке:

mysqldump –u пользователь_БД -p имя-БД > файл_дампа_БД

Также утилита имеет много опций и ключей, рассмотрим несколько основных полезных примеров по использованию mysqldump.

mysqldump -–all-databases -uUSER -pPASSWORD > /path/mysql-db-server.sql

Команда с опцией -–all-databases сохраняет все базы данных пользователя USER на MySQL-сервере.

mysql -uUSER -pPASSWORD < /path/mysql-db-server.sql

Восстанавливает все БД mysql пользователя USER. Если USER — root — то все БД.

mysqldump -uUSER -pPASSWORD DATABASE table1 table2 table3 > /path/ DATABASE_t1-t2-t3.sql

Сохраняет таблицы table1, table2, table3, из базы DATABASE в файле DATABASE_t1-t2-t3.sql.

mysqldump —no-data -uUSER -pPASSWORD DATABASE > /path/DATABASE-schema.sql

Указав опцию —no-data команда сохранит структуру таблиц (без данных) в файле DATABASE_schema.sql.

mysqldump —add-drop-table -uUSER -pPASSWORD DATABASE > /path/DATABASE.sql

Опция —add-drop-table добавит команду DROP TABLE (удаление таблицы) перед созданием таблиц.

mysqldump —databases -uUSER -pPASSWORD DATABASE > /path/DATABASE.sql

Опция —databases добавит команду CREATE DATABASE перед созданием базы данных. Это позволяет не создавать и не задавать базу данных при восстановлении.

mysqldump -uUSER –pPASSWORD –h192.168.0.1 DATABASE > /path/DATABASE.sql

Команда сделает бэкап базы DATABASE с сервера с ip-адресом 192.168.0.1

mysqldump —max_allowed_packet=8M -uUSER –pPASSWORD DATABASE > /path/DATABASE.sql

Опция —max_allowed_packet=8M принудительно изменит размер пакета считываемых данных в оперативную память размером в 8 мегабайт.

mysqldump —quick -uUSER –pPASSWORD DATABASE > /path/DATABASE.sql

Опция —quick заставляет команду записывать данные непосредственно на диск.

mysqldump —default-character-set=cp1251 -uUSER –pPASSWORD DATABASE > /path/DATABASE.sql

Принудительно указываем кодировку cp1251.

mysqldump -uUSER –pPASSWORD DATABASE | gzip -c /path/DATABASE.sql.gz

Этой последовательностью получаем архивированный бэкап с помощью утилиты gzip (для последующего восстановления необходимо будет предварительно извлечь из архива).

Заливаем архив бекапа в базу
gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE
или так
zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

Создаём новую базу данных
mysqladmin -u USER -pPASSWORD create NEWDATABASE

Для просмотра списка баз данных можно использовать команду:
mysqlshow -u USER -pPASSWORD

А так же можно посмотреть список таблиц базы:
mysqlshow -u USER -pPASSWORD DATABASE

где:

USER — это имя пользователя базы данных;

PASSWORD — пароль пользователя;

SERVER — это имя (или ip-адрес) сервера базы данных;

DataBase — наименование базы данных;

NameFile.sql — имя файла, в который будет помещен дамп базы данных.

Полная горячая резервная копия БД

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

В системе Ubuntu 14.04 файлы данных хранятся в каталоге /var/lib/mysql (который также называется datadir). По умолчанию доступ к этому каталогу заблокирован для всех, кроме пользователя mysql. XtraBackup требует доступа к этому каталогу для создания бэкапа, потому нужно передать созданному для XtraBackup пользователю соответствующие права.

Эти команды открывают группе mysql доступ ко всем каталогам datadir.

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

Создание резервной копии

Итак, теперь можно запустить резервное копирование. Чтобы скопировать работающую БД MySQL, используйте утилиту innobackupex.

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

Это создаст резервную копию БД в /data/backups/new_backup:

Также можно пропустить флаг –no-timestamp, чтобы программа XtraBackup создала каталог резервного копирования с текущей временной меткой:

Это создаст резервную копию БД в автоматически сгенерированном подкаталоге:

С флагом или без него, программа должна вернуть «innobackupex: completed OK!» в последней строке результата.

Подготовка резервной копии

Последнее, что нужно сделать, – это подготовить горячую копию БД к использованию; для этого нужно воспроизвести лог транзакций и внести все несовершённые транзакции в резервную копию. Подготовка резервной копии позволяет согласовать и уточнить данные.

Чтобы подготовить полученную копию (/data/backups/new_backup), нужно запустить команду:

В случае успешного выполнения команды программа вернёт «innobackupex: completed OK!».

Теперь резервную копию можно восстановить. Кроме того, если у вас есть система резервного копирования файлов (например, Bacula), эта резервная копия базы данных должна быть добавлена в backup selection.

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

Для восстановления резервной копии XtraBackup нужно остановить БД и очистить datadir.

Остановите сервис MySQL.

Переместите или удалите содержимое каталога данных (/var/lib/mysql). В этом примере данные будут просто временно перемещены:

Теперь можно восстановить резервную копию:

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

Теперь можно запустить MySQL:

Резервная копия БД полностью восстановлена.

Бэкап отдельной таблицы или базы

Не всегда нужны архивные копии всего mysql сервера. Иногда достаточно отдельной базы данных или даже таблицы. Xtrabackup позволяет это сделать. Архивируем только одну базу данных sitemanager.

Для того, чтобы этот способ бэкапа отдельной базы mysql работал, необходим параметр innodb_file_per_table в настройка сервера баз данных.

# xtrabackup --backup --databases "sitemanager" --target-dir=/root/backupdb/sitemanager

Восстановление отдельной базы mysql будет выглядеть так.

# xtrabackup --prepare --databases "sitemanager" --target-dir=/root/backupdb/sitemanager
# systemctl stop mysqld
# mkdir -p /var/lib/mysql.old/sitemanager
# mv /var/lib/mysql/sitemanager /var/lib/mysql.old
# mv /root/backupdb/sitemanager/sitemanager /var/lib/mysql
# chown -R mysql. /var/lib/mysql
# systemctl start mysql

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

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

Готовим бэкап к восстановлению:

# xtrabackup --prepare --export --target-dir=/root/backupdb/full

Идем в консоль mysql и выбираем там таблицу для восстановления. В моем примере это будет таблица b_user_access из базы sitemanager. Смотрим, заполнена ли таблица данными.

mysql> SELECT COUNT(*) FROM sitemanager.b_user_access;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

Делаем DISCARD этой таблицы.

mysql> ALTER TABLE sitemanager.b_user_access DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

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

# cp /root/backupdb/full/sitemanager/b_user_access.* /var/lib/mysql/sitemanager
# chown mysql. /var/lib/mysql/sitemanager/b_user_access.*

Возвращаемся в консоль mysql и импортируем данные.

mysql> ALTER TABLE sitemanager.b_user_access IMPORT TABLESPACE;
Query OK, 0 rows affected (0.03 sec)

Смотрим, что получилось.

> SELECT COUNT(*) FROM sitemanager.b_user_access;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

Данные восстановлены. Вернулись те же 6 строк, что и были до этого.

Как сделать резервную копию БД MySQL и MariaDB в Windows

Резервную копию можно создать в графическом веб-интерфейсе в phpMyAdmin. Если вы хотите сделать бэкап сразу всех баз данных, то перейдите на главную страницу phpMyAdmin, оттуда перейдите во вкладку Экспорт. Далее всё достаточно просто.

Аналогично при бэкапе отдельных баз данных: перейдите на страницу интересующей БД, а далее во вкладку Экспорт.

phpMyAdmin — это прослойка на PHP между СУБД и пользователем, по этой причине скорость создания дампа очень большой базы займёт больше времени, чем если создавать его напрямую через специальную программу от MySQL или MariaDB.

Если вы решили сделать бэкап базы данных MySQL из командной строки в Windows, то для этого понадобиться утилита mysqldump.exe, она поставляется вместе с MySQL и расположена в каталоге с установленной MySQL/MariaDB в папке bin. Например, если СУБД установлена в C:\Server\bin\mysql-8.0, то программа mysqldump.exe будет находиться в папке C:\Server\bin\mysql-8.0\bin\.

Для её использования откройте командную строку и перетащите туда программу. Программу можно использовать с разнообразными опциями.

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

mysqldump.exe -u root -p --all-databases > all-databases.sql

Кстати, файл нужно искать в той папке, которую вы видите в приглашении командной строки.

Для того, чтобы сделать резервную копию только одной базы данных (например, rsyslog):

mysqldump.exe -u root -p rsyslog > rsyslog.sql

Чтобы сделать резервную копию нескольких баз данных используйте опцию —databases, а после него через пробел перечислите желаемые для бэкапа базы данных:

mysqldump.exe -u root -p --databases rsyslog syslog > rsyslog_syslog.sql

Чтобы сделать резервную копию только одной таблицы (wp_posts) из базы данных (wordpress):

mysqldump.exe -u root -p wordpress wp_posts > wordpress_posts.sql

Для того, чтобы сделать резервную копию нескольких таблиц, перечислите их через пробел после названия БД:

mysqldump.exe -u root -p wordpress wp_posts wp_comments > wordpress_posts_comments.sql

Проверка дампа mysql

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

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

-- MySQL dump 10.13 Distrib 5.7.33-36, for Linux (x86_64)

У вас может отличаться версия сервера, но начало будет одинаковое — двойное тире и слово Mysql. В конце дампа будет следующая строка:

-- Dump completed on 2021-07-11 20:21:06

Эти две строки я и буду проверять в скрипте, который будет делать дамп в автоматическом режиме. Результат проверки бэкапа я буду записывать в лог файл, а заодно и вывод утилиты mysqldump, чтобы в случае какой-то ошибки, я смог его проанализировать.

#!/bin/bash

#ключи mysqldump
OPTS="-v --add-drop-database --add-locks --create-options --disable-keys --extended-insert --single-transaction --quick --set-charset --routines --events --triggers --comments --quote-names --order-by-primary --hex-blob"
#файл дампа
FNAME="/mnt/backup/`date +%Y-%m-%d_%H-%M`_dbname.sql"
#лог результатов проверки дампа
LOG="/var/log/mysql/backup.log"
#лог вывода mysqldump
MLOG="/var/log/mysql"
#формат даты
DATA=`date +%Y-%m-%d_%H-%M`

#делаем дамп базы и записываем вывод в лог
/usr/bin/mysqldump ${OPTS} --databases dbname -u'root' -p'password' > ${FNAME} 2>> ${MLOG}/${DATA}-mysqldump.log
#сжимаем лог
/usr/bin/gzip ${MLOG}/${DATA}-mysqldump.log

#проверяем первую и последнюю строки дампа
BEGIN=`head -n 1 ${FNAME} | grep ^'-- MySQL dump' | wc -l`
END=`tail -n 1 ${FNAME} | grep ^'-- Dump completed' | wc -l`

#если обе строки верны, то пишем в лог ОК и сжимаем дамп
if ;then
if ;then
    echo `date +%F_%H-%M` ${FNAME} is OK >> $LOG
    /usr/bin/gzip ${FNAME}
else
    echo `date +%F_%H-%M` ${FNAME} is corrupted >> $LOG
    /usr/bin/rm ${FNAME}
fi
else
    echo `date +%F_%H-%M` ${FNAME} is corrupted >> $LOG
    /usr/bin/rm ${FNAME}
fi
#если дамп не проходит проверки, удаляем его и пишем в лог corrupted

#удаляем бэкапы и логи старше суток.
find /mnt/backup -type f -mmin +1440 -exec rm -rf {} \;
find /var/log/mysql/*mysqldump* -type f -mmin +1440 -exec rm -rf {} \;

Я скрипт прокомментировал, так что добавить особо нечего. Делаем дамп, проверяем первую и последнюю строки. Результат проверки пишем в лог файл, за которым далее будем наблюдать с помощью Zabbix.

Что такое MariaDB Galera?

MariaDB Galera — это кластерная система для MariaDB типа master-master. Начиная с MariaDB 10.1 программное обеспечение Galera Server и MariaDB Server поставляются в одном пакете, так что вы получаете все необходимое программное обеспечение сразу. На данный момент MariaDB Galera может работать только с движками баз данных InnoDB и XtraDB. Из преимуществ использования репликации можно отметить добавление избыточности для базы данных сайта. Если одна из баз данных, даст сбой, то вы сразу же сможете переключиться на другой. Все сервера поддерживают синхронизированное состояние между собой и гарантируют отсутствие потерянных транзакций.

Основные возможности MariaDB Galera:

Репликация с постоянной синхронизацией;
Автоматическое объединение узлов;
Возможность подключения нескольких узлов master;
Поддержка записи на любой из узлов;
Прозрачная параллельная репликация;
Масштабируемость чтения и записи, минимальные задержки;
Давшие сбой ноды автоматически отключаются от кластера;
Нельзя блокировать доступ к таблицам.

Дальше перейдем ближе к настройке MariaDB Galera.

Backup using SQL dump

Advantages:

Easy to set-up
Enables migration to a different MySQL version on restore
Non-binary. For example you could manually delete some tables or modify data before restoring
Can be smaller because it does not include indexes

Disadvantages:

Restore time can be longer because MySQL/MariaDB has to rebuild indexes
Incremental backups can cause large transfers with UrBackup
The complete database (excl. indexes) has to be read during incremental backups

Conclusion: Use this backup method if you have a small database (e.g. 1GB) on Linux.

How to setup:

On Linux with the binary client: Change to in . Afterwards file backups will include the dump file of the database at .

How to restore:

Start with an empty database, download the SQL dump from the server and then apply the SQL dump with:

6: Резервное копирование ключа шифрования

И последнее, что нужно сделать – это создать резервную копию ключа шифрования (он находится в /backups/mysql/encryption_key).

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

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

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

Откройте текстовый файл на локальном компьютере и вставьте в него ключ. Если вам когда-нибудь понадобится восстановить резервные копии на другом сервере, скопируйте содержимое файла в файл /backups/mysql/encryption_key на новом компьютере, настройте систему, описанную в этом мануале, а затем восстановите все необходимое с помощью сценариев.

MySQLPercona XtraBackupUbuntuUbuntu 16.04

5: Автоматизация резервного копирования с помощью cron

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

Для начала создайте файл remote-backup-mysql в каталоге /etc/cron.hourly:

Вызовите скрипт remote-backup-mysql.sh как пользователь backup через команду systemd-cat, которая позволяет записывать вывод в journald:

Сохраните и закройте файл.

Включите новую задачу cron и отключите старую задачу:

Протестируйте удаленный бэкап:

Проверьте записи в логах:

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

1: Установка зависимостей

Для создания резервных копий и загрузки их в удаленное хранилище объектов мы будем использовать скрипты Python и Bash. Для работы вам понадобится библиотека Python boto3 (для взаимодействия с API хранилища объектов). Вы можете загрузить ее с помощью pip, менеджера пакетов Python.

Обновите индекс локальных пакетов, а затем установите pip для Python 3 из репозиториев Ubuntu по умолчанию, используя apt-get:

Поскольку Ubuntu поддерживает собственный жизненный цикл этого пакета, версия pip в репозиториях Ubuntu не синхронизируется с последними релизами. Однако мы можем обновиться до более новой версии pip, используя сам инструмент. Добавьте sudo для глобальной установки и включите флаг -H, чтобы установить для переменной $HOME значение, необходимое pip:

sudo -H pip3 install –upgrade pip

После этого можно установить boto3 вместе с модулем pytz, его мы будем использовать для точного сравнения времени в формате с учетом смещения, который возвращает API хранилища объектов:

Теперь у вас есть все модули Python, необходимые для взаимодействия с API хранилища объектов.

PyMySQL задействованные строки

Предназначенный только для чтения атрибут курсор показывает количество строк, которые были получены в результате последнего использования одного из операторов SELECT, UPDATE или INSERT.

Python

#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect(‘localhost’, ‘user17’,
‘s$cret’, ‘mydb’)

with con:

cur = con.cursor()
cur.execute(«SELECT * FROM cities WHERE id IN (1, 2, 3)»)

# rows = cur.fetchall()

# for row in rows:
# print(«{0} {1} {2}».format(row, row, row))

print(«The query affected {} rows».format(cur.rowcount))

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

#!/usr/bin/python3
# -*- coding: utf-8 -*-

importpymysql

con=pymysql.connect(‘localhost’,’user17′,

‘s$cret’,’mydb’)

withcon

cur=con.cursor()

cur.execute(«SELECT * FROM cities WHERE id IN (1, 2, 3)»)

# rows = cur.fetchall()

# for row in rows:

# print(«{0} {1} {2}».format(row, row, row))

print(«The query affected {} rows».format(cur.rowcount))

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

Python

print(«The query affected {} rows».format(cur.rowcount))

1	print(«The query affected {} rows».format(cur.rowcount))

Таким образом, составляется сообщение, в котором показывается количество задействованных строк.

Shell

$ ./affected_rows.py
The query affected 3 rows

1 2	$.affected_rows.py The query affected3rows

Это результат вывода.

В данной инструкции было показано, как использовать базу данных MySQL в Python при помощи модуля PyMySQL.

How to Use Script

This script is very easy to use, Download or copy this script on your local system and execute it with python. This script is capable to take multiple databases backup

Single Database Backup: If you want to use this script for taking single database backup, edit script as below. For example database name is mydb.

  DB_NAME = 'mydb'

Multiple Databases Backup: To take multiple databases backup, create an text file like /backup/dbnames.txt and add databases names one per line like below

# cat /backup/dbnames.txt
database1
mydb

And add this file to script like below.

  DB_NAME = '/backup/dbnames.txt'

Change Backup Location: You can change below variable to change the location of backup path.

  BACKUP_PATH = '/backup/dbbackup/'

Заключение

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

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

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

Онлайн курс по Linux

Если у вас есть желание научиться строить и поддерживать высокодоступные и надежные системы, рекомендую познакомиться с онлайн-курсом «Administrator Linux. Professional» в OTUS. Курс не для новичков, для поступления нужны базовые знания по сетям и установке Linux на виртуалку. Обучение длится 5 месяцев, после чего успешные выпускники курса смогут пройти собеседования у партнеров.

Что даст вам этот курс:

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

Проверьте себя на вступительном тесте и смотрите подробнее программу по .