Как сделать дамп базы mysql / mariadb

Введение

Важно уметь импортировать и экспортировать ваши базы данных. Вы можете использовать дампы в качестве резервных копий, на случай аварий или для перенесения на другой сервер.. Кстати, дамп – это от английского dump, что буквально переводится как «сбрасывать», «сваливать» и может означать результат этого действия – куча

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

Кстати, дамп – это от английского dump, что буквально переводится как «сбрасывать», «сваливать» и может означать результат этого действия – куча. Дамп в компьютерной терминологии означает данные/база данных целиком, которую «сбросили», например, на диск.

Работа с базами данных MySQL и MariaDB проста. Эта инструкция покажет вам, как экспортировать базы данных, а также как импортировать их из файла дампа в MySQL и MariaDB.

Настройка прав доступа

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

> GRANT ALL PRIVILEGES ON newdb.* TO dbuser@localhost IDENTIFIED BY ‘password’ WITH GRANT OPTION;

* где newdb.* — наша база и все ее таблицы; dbuser@localhost — имя учетной записи, которая будет подключаться с локального сервера; password — придуманный нами пароль.** В данном примере, учетной записи будут предоставлены полные права (ALL PRIVILEGES). Подробнее о правах в MySQL читайте статью Как создать пользователя MySQL и дать ему права.

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

> SELECT db, host, user FROM mysql.db WHERE db=’newdb’;

* в данном примере мы выведем учетные записи, которым был дан прямой доступ к созданной нами базе. В данном списке не будут отражены пользователи с глобальными правами (например, root).

Поменять пароль пользователю можно одной из команд (в зависимости от версии СУБД):

> SET PASSWORD FOR ‘dbuser’@’localhost’ = PASSWORD(‘new_password’);

> ALTER USER ‘dbuser’@’localhost’ IDENTIFIED BY ‘new_password’;

> UPDATE mysql.user SET Password=PASSWORD(‘new_password’) WHERE USER=’dbuser’ AND Host=’localhost’;

* все 3 команды меняют пароль для пользователя dbuser@localhost на новый — new_password.

При необходимости, удалить пользователя можно командами:

> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘dbuser’@’localhost’;

> DROP USER ‘dbuser’@’localhost’;

* первая команда отнимает все привилегии, выданные пользователю. Вторая удаляет самого пользователя.

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

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

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.

Generate the backup of a single database

For example, you want to generate the backup of the single database, run the following command. The command will generate the backup of the “sakila” database with structure and data in the sakila_20200424.sql file.

1	mysqldump-uroot-psakila>C\MySQLBackup\sakila_20200424.sql

When you run this command, it prompts for the password. Provide the appropriate password. See the following image:

Once backup generated successfully, let us open the backup file to view the content of the backup file. Open the backup location and double-click on the “sakila_20200424.sql” file.

As you can see in the above image, the backup file contains the various T-SQL statements that can be used to re-create the objects.

How to create a physical MySQL database backup

Ultimately, any database is stored in a file or set of files. If you save these files to another location, you can later use them to restore the data from a particular time period.

Unlike logical backups, physical backups are created much faster, because it is only a matter of copying files. The restoration is also fast, for the same reason.

However, physical backups have two important disadvantages:

1. Backups are portable only to other machines that have identical or similar hardware characteristics.
2. The solution for creating hot backups can be too expensive for small businesses, as the only Windows tool that allows you to create a physical backup without stopping the server is MySQL Enterprise Backup.

Manual creation of a MySQL Server backup by copying data files

The easiest way to create a backup is to simply copy the contents of a MySQL data directory.

To manually create a MySQL Server backup based on files, follow the steps below:

1. Find MySQL Server data directory. To do this, run the following command:

select @@datadir;

The easiest way to do this is through MySQL Workbench.

1. Find and stop the MySQL Server service
   • Press win+R
   • Enter services.msc
   • Find the MySQL Server service in the list of services
   • Right-click on it and select Stop

1. Copy or pack the contents of a MySQL Server data directory to another location
2. Start MySQL Server by clicking Start in the context menu of the service.

Creating a MySQL Server backup by copying data files using a batch script

All actions described in the previous section can be performed using one batch script.

First, set values for the variable values ,, and then run the script. It will stop the service, copy the contents of the data folder to a new subdirectory, and then start the MySQL Service.

set mysql_data_dir=C:\ProgramData\MySQL\MySQL Server 8.0\Data
set backup_dir=D:\Temp
set mysql_service_name=MySQL80

NET STOP %mysql_service_name%
set mysql_backup_folder=%backup_dir%\mysql-backup-%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%-%time::=.% 
mkdir %mysql_backup_folder%
xcopy /e /k /h /i "%mysql_data_dir%" "%mysql_backup_folder%"
NET START %mysql_service_name%

Database restoration from files

Follow the steps below to restore your data:

1. Stop MySQL Service
2. Empty MySQL Server data directory completely
3. Copy the saved data to the data directory
4. Start the service

Using mysqlbackup utility

The main disadvantage of the previous method is the need to stop MySQL Server. And although copying itself should take little time, even a short shutdown of the server may be unacceptable.

For a windows server, Oracle Corporation has developed a MySQL Enterprise backup product that allows you to create hot physical backups.

This solution includes a console utility – mysqlbackup, which allows creating physical backups. It is as easy to use as mysqldump.

The following command creates a backup file in the directory d:\Temp\EnterpriseBackup.

mysqlbackup --user=root --password --backup-image=backup.mbi \ --backup-dir=D:\Temp\EnterpriseBackup backup-to-image

–backup-image  – Backup file name

–backup-dir –  Directory in which backup will be created

To restore data from backups, you will need to perform preparatory steps, namely to stop the MySQL Service and clear the MySQL Server data directory. Then you need to use the same utility to restore data but with different parameters.

mysqlbackup --datadir=C:\ProgramData\MySQL\MySQL Server 8.0\Data\ --backup-image=backup.mbi --backup-dir=D:\Temp\EnterpriseBackup copy-back-and-apply-log

–datadir – MySQL Server data directory

–backup-image – Backup file name

–backup-dir – The directory containing the backup file.

The mysqlbackup utility supports on-the-fly compression, partial backup, incremental backup, and many other features. Details are available here.

The main drawback of this solution is the need to purchase MySQL Enterprise, which can be expensive for small and medium-sized businesses.

Использование распространенных инструментов

Используйте распространенные инструменты и служебные программы, такие как MySQL Workbench или mysqldump, для удаленного подключения и восстановления данных в Базу данных Azure для MariaDB. Используйте эти инструменты на своем клиентском компьютере, подключенном к Интернету, чтобы подключиться к Базе данных Azure для MariaDB. В целях безопасности рекомендуется использовать подключение с шифрованием SSL. Дополнительные сведения см. в статье Настройка SSL-подключения в Базе данных Azure для MariaDB. При переносе данных в Базу данных Azure для MariaDB не нужно перемещать файлы дампа в особое облачное расположение.

Бэкап и восстановление 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 сервера все. Поделился основными своими наработками.

Восстановление из бэкапа

Давай­те теперь вос­ста­но­вим дан­ные из сде­лан­но­го бэка­па. Если это тот же сер­вер, то все очень про­сто. Нам доста­точ­но под­го­то­вить бэкап с помо­щью клю­ча prepare, как я пока­зал ранее и заме­нить содер­жи­мое рабо­чей дирек­то­рии mysql на то, что хра­нит­ся в архиве.

YAML

# systemctl stop mysqld && rm -rf /var/lib/mysql/*
# xtrabackup —copy-back —target-dir=/root/backupdb/full
# chown -R mysql:mysql /var/lib/mysql
# systemctl start mysqld

1 2 3 4

# systemctl stop mysqld && rm -rf /var/lib/mysql/* # xtrabackup —copy-back —target-dir=/root/backupdb/full # chown -R mysql:mysql /var/lib/mysql # systemctl start mysqld

Раз­би­ра­ем, что я сделал.

1. Оста­но­вил mysql сер­вер и уда­лил все из ее рабо­чей директории.
2. Вос­ста­но­вил дан­ные из архив­ной копии xtrabackup. По фак­ту он про­сто ско­пи­ро­вал дан­ные в рабо­чую дирек­то­рию mysql сервера.
3. Назна­чил поль­зо­ва­те­ля mysql вла­дель­цем рабо­чей дирек­то­рии и все­го ее содержимого.
4. Запу­стил mysql сер­вер с вос­ста­нов­лен­ны­ми данными.

После запус­ка mysql сер­ве­ра про­ве­ряй­те лог /var/log/mysql/error.log на пред­мет оши­бок. Если уви­ди­те там такие ошибки:

YAML

InnoDB: Page log sequence number 17744745 is in the future! Current system log sequence number 9160744.

1	InnoDB: Page [page id: space=14,pagenumber=4logsequencenumber17744745isinthefuture!Currentsystemlogsequencenumber9160744.

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

Если буде­те вос­ста­нав­ли­вать базу на дру­гой сер­вер, то после­до­ва­тель­ность дей­ствий будет сле­ду­ю­щая. Под­клю­ча­ем репо­зи­то­рий percona.

YAML

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

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

Ста­вим mysql server и xtrabackup нуж­ной версии.

YAML

# yum install Percona-Server-server-57 percona-xtrabackup-24

1	# yum install Percona-Server-server-57 percona-xtrabackup-24

Копи­ру­ем на новый сер­вер архив сер­ве­ра баз данных.

YAML

# scp [email protected]:/root/backupdb/full.tar.gz ~

1	# scp [email protected]:/root/backupdb/full.tar.gz ~

Рас­па­ко­вы­ва­ем его:

YAML

# tar xzvf full.tar.gz

1	# tar xzvf full.tar.gz

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

YAML

# xtrabackup —copy-back —target-dir=~/full
# chown -R mysql:mysql /var/lib/mysql
# systemctl start mysqld

1 2 3

# xtrabackup —copy-back —target-dir=~/full # chown -R mysql:mysql /var/lib/mysql # systemctl start mysqld

Захо­дим в кон­соль mysql и про­ве­ря­ем спи­сок баз и пользователей.

YAML

# mysql -u root -p
mysql> show databases;
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

1 2 3

# mysql -u root -p mysql>showdatabases; mysql>SELECTuser,authentication_string,plugin,hostFROMmysql.user;

Все на месте, как и на исход­ном сервере.

Это я пока­зал вос­ста­нов­ле­ние из пол­но­го бэка­па. Теперь пока­зы­ваю, как вос­ста­но­вить­ся из инкре­мент­ной копии. Для нача­ла нам надо под­го­то­вить пол­ную копию для нака­ты­ва­ния на нее инкремента.

YAML

# xtrabackup —prepare —apply-log-only —target-dir=/root/backupdb/full

1	# xtrabackup —prepare —apply-log-only —target-dir=/root/backupdb/full

Теперь добав­ля­ем туда дан­ные из инкре­мент­но­го бэкапа.

YAML

# xtrabackup —prepare —apply-log-only —target-dir=/root/backupdb/full —incremental-dir=/root/backupdb/inc1

1	# xtrabackup —prepare —apply-log-only —target-dir=/root/backupdb/full —incremental-dir=/root/backupdb/inc1

И так для всех осталь­ных инкре­мент­ных копий, если у вас из них выстро­е­на цепоч­ка. Кон­тро­ли­ро­вать состо­я­ние пол­но­го архи­ва и сопо­став­лять с инкре­мен­та­ми мож­но по содер­жи­мо­му фай­лов xtrabackup_checkpoints. После того, как вос­ста­но­ви­ли все инкре­мент­ные архи­вы, на послед­нем из них не нуж­но исполь­зо­вать ключ apply-log-only. Так же он не нужен, если у вас толь­ко одна инкре­мент­ная копия. Завер­ша­ю­щий этап под­го­тов­ки пол­ной копии дол­жен быть без него.

После того, как вос­ста­но­ви­ли всю цепоч­ку инкре­мен­тов, с архи­вом мож­но рабо­тать как с обыч­ным пол­ным бэкапом.

Управление пользователями

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

useradd — создает на сервере новую учетную запись. По сути, нового пользователя. Синтаксис: useradd имя будущей учетной записи. Имя можно указать любое на свой вкус. Потом останется лишь добавить для нового аккаунта пароль.

passwd — задает пароль для учетной записи. Работает вкупе с предыдущей командой. То есть сразу после создания аккаунта, пишем: passwd имя новой учетной записи. После этого система попросит придумать и указать пароль для новой учетной записи.

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

userdel — удаляет выбранную учетную запись. Синтаксис: userdel имя учетной записи, которую нужно стереть

usermod — вносит изменения в характеристики существующих учетных записей, лишает их контроля или вовсе приостанавливает работу. Делает все, что не связано с созданием и удалением аккаунтов. Используется только вместе с дополнительными опциями:

• -с — добавляет комментарий к аккаунту (можно вписать любой текст по желанию, чтобы запомнить для чего нужен выбранный пользователь).
• -d — меняет расположение домашней директории выбранной учетной записи.
• -e — указывает время, которое будет существовать аккаунт (после этого сработает автоматический userdel).
• -g — меняет группу, к которой принадлежит аккаунт.
• -G — привязывает аккаунт к выбранной группе.
• -L — блокирует пользователя.
• -m — перемещает контент из домашней папки пользователя в другую папку.
• -p — устанавливает незашифрованный пароль (лучше так не делать).
• -s — задает конкретную оболочку для нового аккаунта на усмотрение администратора компьютера.
• -U — снимает блокировку с выбранной учетной записи.

Использование Transact-SQL

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

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

Базовая структура синтаксиса Transact-SQL для полного резервного копирования базы данных:

BACKUP DATABASE database TO backup_device ] ;

Параметр	Описание
database	База данных для резервного копирования.
backup_device	Указывает список от 1 до 64 устройств резервного копирования, используемых для создания резервной копии. Можно указать как физическое устройство резервного копирования, так и соответствующее логическое устройство, если оно уже определено. Для указания физического устройства резервного копирования используйте параметр DISK или TAPE. { DISK | TAPE } = physical_backup_device_name Дополнительные сведения см. в разделе Устройства резервного копирования (SQL Server).
WITH with_options	Используется для указания одного или нескольких параметров, o. Сведения о некоторых основных параметрах см. в пункте 2.

При необходимости укажите один параметр WITH или несколько. Здесь описываются некоторые основные параметры WITH. Сведения о всех параметрах WITH см. в разделе BACKUP (Transact-SQL).

Основные параметры WITH резервного набора данных:

• { COMPRESSION | NO_COMPRESSION } : Только в версии SQL Server 2008 Enterprise и выше указано, выполняется ли команда backup compression для этой резервной копии, переопределяя значение по умолчанию на уровне сервера.
• ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY) : Только для SQL Server 2014 и выше укажите используемый алгоритм шифрования, а также сертификат или асимметричный ключ для шифрования.
• DESCRIPTION = { ‘ text ‘ | @ text_variable }: Задает произвольное текстовое описание резервного набора данных. В этой строке может содержаться до 255 символов.
• NAME = { имя_резервного_набора_данных | @ переменная_резервного_набора_данных } : Указывает имя резервного набора данных. Длина имени не может превышать 128 символов. Если имя не указано, оно остается пустым.

По умолчанию команда добавляет резервную копию в существующий набор носителей, сохраняя существующие резервные наборы данных. Чтобы явно задать значение, используйте параметр . Сведения о присоединении к существующим резервным наборам данных см. в разделе Наборы носителей, семейства носителей и резервные наборы данных (SQL Server).

Чтобы отформатировать носитель резервной копии, используйте параметр FORMAT:

FORMAT

Используйте предложение FORMAT при первом обращении к носителю или при необходимости перезаписать все существующие данные. При необходимости назначьте новому носителю имя и описание.

Важно!

Будьте предельно осторожны, используя предложение FORMAT инструкции , так как оно удаляет все резервные копии, сохраненные ранее на носителе резервных копий.

A. Резервное копирование на дисковое устройство

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

Б. Резервное копирование на ленточное устройство

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

В. Резервное копирование на логическое ленточное устройство

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

Создание резервного файла

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

Необходимо указать следующие параметры:

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

: пароль для базы данных (обратите внимание, что между -p и паролем нет пробела);

: имя вашей базы данных;

: имя файла резервной копии базы данных;

: параметр mysqldump.

Например, чтобы создать резервную копию базы данных с именем testdb на сервере MariaDB с именем пользователя testuser и без пароля и сохранить ее в файл testdb_backup.sql, используйте приведенную ниже команду. Команда создает резервную копию базы данных в файле с именем , который содержит все инструкции SQL, необходимые для повторного создания базы данных.

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

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

Дефрагментация

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

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

Get-MailboxDatabase -Status | ft Name, DatabaseSize, AvailableNewMailboxSpace

Пример ответа:

Name        DatabaseSize    AvailableNewMailboxSpace
—-        ————    ————————
Base1       686.4 GB        286.4 MB
Base2       170 GB          69.42 GB

* где DatabaseSize — текущий размер базы; AvailableNewMailboxSpace — пространство, которое можно освободить при дефрагментации.

Саму оптимизацию можно выполнить двумя способами:

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

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

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

Если используется база на основе группы DAG, сначала необходимо .

Операция дефрагментации выполняется из Exchange Management Shell с применением утилиты eseutil.

Сначала переходим в каталог хранения базы данных, например:

cd C:\Program Files\Microsoft\Exchange Server\V14\Mailbox\Base1

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

Dismount-Database Base1

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

Запускаем дефрагментацию:

eseutil /d Base1.edb /t \\share\base1_tmp.edb

* где опция d — имя файла базы; t — путь до временного файла на момент дефрагментации, если его не указать, временный файл будет создан в каталоге с основным файлом и, в таком случае, нужно убедиться, что на диске достаточно свободного места (110% от размер дефрагментируемого файла).

После завершения операции, снова подключаем базу:

Mount-Database Base1

Полный бэкап Mysql сервера

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

# xtrabackup --backup --user=root --password='R(zDXcVUmI[zwx%aNBTN' --target-dir=/root/backupdb/full

backup	инициируем процедуру бэкапа
user=root	пользователь mysql
password=’R(zDXcVUmI[zwx%aNBTN’	пароль пользователя, взятый в одинарные кавычки
target-dir=/root/backupdb/full	директория для создания полного бэкапа mysql

В дальнейших примерах я не буду указывать пользователя и пароль, чтобы упростить команды. Эти данные я указал в файле ~/.my.cnf.

user=root
password='R(zDXcVUmI[zwx%aNBTN'

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

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

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

# xtrabackup --backup --compress --target-dir=/root/backupdb/full

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

# xtrabackup --decompress --target-dir=/root/backupdb/full

Для того, чтобы команда decompress отработала без ошибки:

sh: qpress: command not found
cat: write error: Broken pipe
Error: decrypt and decompress thread 0 failed.

Необходимо установить пакет qpress.

# yum install qpress

Он есть в репозитории percona. После этого распаковка пройдет штатно.

Лично я не вижу большого смысла использовать ключи compress и decompress. Можно сделать полный бэкап, подготовить его, а потом сжать тем же gzip.

# tar -czvf /root/backupdb/full.tar.gz -C /root/backupdb full

На выходе получите тот же архив, только сжат лучше и нет необходимости ставить дополнительный софт. Gzip и tar обычно есть во всех дистрибутивах. К тому же архив в виде единого файла проще и быстрее передать на сервер бэкапов и там хранить.

В завершении раздела про полный backup, предлагаю простенький скрипт для автоматизации процесса через cron — mysql-full-backup.sh.

#!/bin/bash

DATA=`date +%Y-%m-%d`

mkdir -p /root/backupdb/$DATA
xtrabackup --backup --target-dir=/root/backupdb/$DATA/full
xtrabackup --prepare --target-dir=/root/backupdb/$DATA/full
tar -czvf /root/backupdb/$DATA/full.tar.gz -C /root/backupdb/$DATA full
rm -rf /root/backupdb/$DATA/full

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

Заключение

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

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

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

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

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

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

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

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