Резервное копирование · 8 min read · Dec 10, 2025

Инкрементное резервное копирование MySQL - Резервное копирование и восстановление InnoDB и MyIsam баз данных в определенный момент времени

Инкрементное резервное копирование является важным требованием для крупных производственных баз данных. Без безопасного инкрементного резервного копирования вы не можете уверенно сказать, что у вас есть надежная производственная база данных. Потому что у вас должно быть достаточно данных для восстановления вашей базы данных в экстренных случаях. После некоторых поисков в Интернете я не смог найти ни одного инструмента, который мог бы выполнить полное инкрементное резервное копирование для MyISAM и InnodB в смешанной среде, где приложения одновременно используют оба движка баз данных (возможно, я не эксперт в поиске в Google и Интернете). Поэтому я решил написать это, но чтобы избежать потери времени и воспользоваться другими решениями с открытым исходным кодом, я предпочел добавить эту функцию в скрипт -automysqlbackup-, который является лучшим скриптом для полного резервного копирования по простоте и широкому использованию.

Механизм

Мы используем функцию Post- и Pre- скрипта automysqlbackup для выполнения инкрементного резервного копирования. Перед началом полного резервного копирования mysql-backup-pre выполняет запрос для блокировки всей базы данных во время процесса резервного копирования, потому что мы должны заморозить binlog, чтобы избежать любых изменений во время выполнения резервного копирования. Имя и позиция binlog не могут изменяться во время резервного копирования. Позиция двоичного лога имеет решающее значение в последующем процессе инкрементного резервного копирования и будет использоваться в качестве отправной точки для начала следующего инкрементного резервного копирования. После завершения полного резервного копирования mysql-backup-post снимает блокировку базы данных.

Запрос блокировки: FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)

Найти запросы блокировки: mysql -u[username] -p[pass] -e “show processlist” | grep “SELECT SLEEP(86400)” | awk ‘{print $1}’

Требования

  • права root для установки пакета и обновления mysql.conf
  • пакет mysql-community-client
  • установка automysqlbackup и mysql-incremental

Установка

Установите пакет mysql-community-client для вашего дистрибутива.

Примечание: после установки MySQL у вас должна быть команда ‘mysqlshow’.

Установите automysqlbackup:

скачайте пакет с https://sourceforge.net/projects/automysqlbackup/
tar -xzf [PathYouSavedTarFile] -C /tmp/
cd /tmp/
./install.sh

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

rm /etc/automysqlbackup/myserver.conf

Установите mysql-incremental: Скачайте пакет с https://sourceforge.net/projects/mysqlincrementalbackup/

cd /tmp  
wget http://downloads.sourceforge.net/project/mysqlincrementalbackup/mysql-incremental.tar.gz  
tar xfz mysql-incremental.tar.gz
cp mysql-incremental /etc/automysqlbackup/
chmod 755 /etc/automysqlbackup/mysql-incremental
cp mysql-backup-post /etc/automysqlbackup/
chmod 755 /etc/automysqlbackup/mysql-backup-post
cp mysql-backup-pre /etc/automysqlbackup/
chmod 755 /etc/automysqlbackup/mysql-backup-pre

Обновите automysqlbackup.conf:

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

        CONFIG_mysql_dump_username='Имя пользователя Mysql. Он должен иметь права для получения блокировки'
    CONFIG_mysql_dump_password='Пароль'
    CONFIG_backup_dir='Каталог резервного копирования, в котором вы хотите хранить полное и инкрементное резервное копирование'
    CONFIG_db_names=('databaseName1' 'databaseName2' )
    CONFIG_db_month_names=('databaseName1' 'databaseName2' )
    CONFIG_mysql_dump_master_data=2
    CONFIG_prebackup="/etc/automysqlbackup/mysql-backup-pre"
    CONFIG_postbackup="/etc/automysqlbackup/mysql-backup-post"

Обновите my.cnf:

Отредактируйте файл конфигурации MySQL:

nano /etc/mysql/my.cnf

1- Формат BinLog

Из-за некоторых ограничений формата STATEMENT, я рекомендую установить формат на основе ROW. Для получения дополнительной информации, пожалуйста, смотрите раздел ‘устранение неполадок’ в этом руководстве. Вы можете проверить тип формата двоичного лога, выполнив запрос “ select @@binlog_format; “. Чтобы изменить формат logbin, вы должны добавить binlog_format = ROW в mysql.conf или my.cnf.

2- binlog_do_db

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

binlog_do_db = DATABASENAME1
binlog_do_db = DATABASENAME2

3- expire_logs_days

Чтобы иметь файлы двоичного лога в течение более длительного времени, вы можете увеличить этот параметр до более высокого значения. Моя рекомендация - 60 дней. Поэтому вы должны добавить или изменить его на “ expire_logs_days = 60 “.

4- log-bin

Каталог, в котором будут храниться двоичные логи. В старых версиях MySQL mysql-incremenetal может не найти правильный путь. Поэтому, если вы получите ошибку об этом после выполнения mysql-incremental, вам необходимо обновить скрипт mysql-incremental и установить путь к двоичному логу.

5- log_slave_updates

Если вы настраиваете инкрементное резервное копирование mysql на сервере-реплике, вы должны включить эту опцию. Обычно реплика не записывает обновления в свой собственный двоичный лог, так как они были получены от главного сервера. Эта опция говорит реплике записывать обновления, выполненные ее SQL-потоками, в свой собственный двоичный лог. http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_log-slave-updates

Запустите automysqlbackup

Запустите automysqlbackup вручную, чтобы получить хотя бы одно полное резервное копирование из указанных вами баз данных.

automysqlbackup

После успешного выполнения команды проверьте файл /[BackupDirInAutomysqlbackup]/status/backup_info на наличие новой информации о ежедневном резервном копировании. Для получения деталей об ошибках проверьте /var/log/Backup_Post_Pre_log. Файл резервного копирования будет храниться в каталоге /[BackupDirInAutomysqlbackup]/daily/[DatabaseName]/.

Запустите mysql-incremental

Теперь запустите mysql-incremental вручную, чтобы получить хотя бы одно инкрементное резервное копирование.

mysql-incremental

В случае ошибки детали записываются в файл “ /var/log/Backup_Incremental_Log “. Инкрементные файлы резервного копирования будут храниться в каталоге /[BackupDirInAutomysqlbackup]/IncrementalBackup/.

Отредактируйте crontab для root

Вы можете запланировать mysql-incremental на более чем один час. Вы можете узнать общее время полного резервного копирования из backup_status, а затем на основе этого значения установить точное время расписания. Конечно, резервное копирование mysql-incremental имеет механизм для поиска любого выполняемого полного резервного копирования перед началом, поэтому нет причин для беспокойства о конфликте между инкрементным и полным резервным копированием.

crontab -e
5 00 * * * root /usr/local/bin/automysqlbackup
25 *  * * * root  /etc/automysqlbackup/mysql-incremental

Восстановление базы данных

Чтобы восстановить данные до определенного времени (восстановление в определенный момент времени), сначала вы должны восстановить одно полное ежедневное резервное копирование, а затем последовательно восстановить связанные инкрементные файлы резервного копирования. Чтобы прояснить, вот шаги для восстановления базы данных testDB. В примере сценария мы намерены восстановить наши данные до 2015-5-01 в 2 часа ночи. Мы установили /backup в качестве нашего основного каталога резервного копирования и testDB в качестве целевой базы данных:

1- mysql -u root -p DatabaseName < /backup/daily/testDB/daily_DatabaseName_2015-05-16_00h05m_Saturday.sql.gz
2- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_00h25m.1
3- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_01h25m.2
4- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_02h25m.3

Важные заметки и устранение неполадок

MySQL поддерживает различные форматы для двоичного лога. Некоторые версии Mysql используют ‘statement-based’ в качестве формата binlog, который имеет некоторые ограничения, на которые мы должны обратить особое внимание, когда намереваемся использовать его в процедуре инкрементного резервного копирования. Когда mysql установлен в формат на основе операторов, он не может правильно фильтровать на основе баз данных. Если вы установите ‘USE или \u’, чтобы изменить базу данных, а затем обновите другую базу данных, которая не включена в binlog-do-db, оператор будет записан в файл binlog, что является нежелательным состоянием! и вызовет некоторые проблемы при восстановлении на основе конкретной базы данных, а также если вы измените на другую базу данных, которая не включена в binlog-do-db, и обновите базу данных, которая включена в binlog-do-db, оператор не будет записан в файл binlog. Наша цель добавления баз данных в binlog-do-db заключается в фильтрации на основе базы данных, но это не работает так, как ожидалось. Если USE или \u не выполняется перед выполнением запросов, mysqlbinlog не может извлечь ‘обновляющие запросы’, относящиеся к одной базе данных. Мы объясним эту проблему более подробно с помощью следующих сценариев:

базы данных: 
 - binlog
     - person (таблица) 
  - binlog2
     - person (таблица)

 binlog-do-db=binlog2 (предполагается, что только изменения этой базы данных записываются в файл binlog)
--------Сценарий 1---------
\u binlog2
insert into person (data) values ('17') ---> записано в binlog  *желательное состояние*
insert into binlog.person (data) values ('25'); ---> записано в binlog (целевой базой данных является 'binlog' ) *нежелательное состояние*
--------Сценарий 2---------
\u binlog
insert into person (data) values ('17') ---> не записано в binlog  *желательное состояние*
insert into binlog2.person (data) values ('25'); ---> не записано в binlog (целевой базой данных является 'binlog2' ) *нежелательное состояние*, потому что база данных binlog2
изменяется, поэтому мы хотим иметь это изменение, но оно не будет записано в файл logbin
--------Сценарий 3---------
если вы просто подключаетесь к базе данных без каких-либо операторов USE или \u, все обновления в любых базах данных будут записаны, но mysqlbinlog не сможет фильтровать
на основе конкретной базы данных, так что это нежелательное состояние для нашей цели в инкрементном резервном копировании. Использование USE или \u перед выполнением обновляющих запросов очень важно. Потому что mysqlbinlog находит обновляющие запросы на основе оператора USE в файле binlog.

Обходные пути для упомянутой проблемы

  1. Определив пользователей на базах данных таким образом, чтобы каждый пользователь имел доступ только к одной базе данных для обновления (пользователь приложения), и при подключении к базе данных должно быть указано имя базы данных. Конечно, у большинства приложений есть файл конфигурации, в котором указаны учетные данные и имя базы данных, поэтому в этом случае у вас не будет перекрестного доступа к базам данных, и не будет причин для беспокойства о использовании “\USE или \u”.

  2. Если вы используете формат binlog на основе строк, то все упомянутые проблемы исчезнут. Другими словами, формат на основе строк является гораздо более подходящим методом для binlog. https://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html

Файлы журналов

Я пытался записать все в файл журнала, чтобы вы могли найти достаточно информации в журналах:

/var/log/Backup_Post_Pre_log
/var/log/Backup_Incremental_Log
/[SpecifiedBackupDirInAutomysqlbackup.conf]/status/backup_info

Файл “ backup_info “ содержит подробную информацию о резервном копировании и о том, когда резервное копирование завершилось (время указано в формате Unix Time). Он содержит имя и позицию binlog в момент времени, когда началось резервное копирование, тип резервного копирования, количество резервных копий с момента последнего полного резервного копирования и продолжительность резервного копирования.

Пример backup_info:

1431043501,mysql-bin.000026,120,Daily,2015-05-08,0,24
1431044701,mysql-bin.000026,120,Hourly,2015-05-08,1,1

Вот описание различных значений:

 1th) 1431043501 : указывает время, когда резервное копирование было завершено. Вы можете выполнить команду date --date @1431043501 на сервере, на котором было выполнено резервное копирование, чтобы просмотреть его в читаемом формате.
 2th) Mysql-bin.000026 : указывает имя двоичного лога, до которого было выполнено резервное копирование.
 3th) 120 : указывает позицию binlog, до которой было выполнено резервное копирование в двоичном логе.
 4th) Daily/Hourly: указывает тип резервного копирования. Daily означает полное резервное копирование с помощью скрипта automysqlbackup, а Hourly выполняется с помощью скрипта mysql-incremental.
 5th) 2015-05-08: Дата, когда было выполнено резервное копирование. Эта дата будет использоваться для создания каталога для инкрементного резервного копирования, а также в качестве основы для восстановления почасовых резервных копий. В процессе восстановления сначала восстанавливается полное резервное копирование, а затем последовательно восстанавливаются другие инкрементные резервные копии.
 6th) 0 : указывает количество резервных копий с предыдущего полного резервного копирования. 0 означает, что резервное копирование полное, а другие означают почасовые. Это число имеет большое значение в процессе восстановления.
 7th) 24: Продолжительность резервного копирования в секундах.

Сообщение об ошибке

Вы можете сообщить об ошибках или дать свои предложения и отзывы на https://sourceforge.net/projects/mysqlincrementalbackup.

Share: X/Twitter LinkedIn
#Резервное копирование

Get new posts in your inbox

No spam. Unsubscribe anytime.