MySQL Incremental Backup

MySQL Incremental Backup

Сообщение ALEXX » 28 май 2015, 09:04

MySQL Incremental Backup - Point In Time Backup and Recovery of InnoDB and MyIsam Databases.


Doing incremental backups is an important requirement for large production databases. Without a safe incremental backup, you can not tell yourself that you have a reliable production database. Because you must have enough data in order to recover your database in emergency cases. After some search on Internet, I could not find any tool that can do a complete incremental backup for MyISAM and InnodB in a mixed environment were applications use both database engines simultaneously (maybe I am not an expert searcher on Google and Internet). So I decided to write this one, but to avoid wasting time and benefit from other open-source solutions, I preferred to add this feature to -automysqlbackup- script that is the best script for full backup in simplicity and widespread use.

Mechanism


We use the Post- and Pre feature of automysqlbackup to do an incremental backup. Before starting a full backup, mysql-backup-pre executes a query to lock the whole database during backup process because we have to freeze the binlog to avoid any change while backup is running. The binlog name and position may not change during backup. The binary log position is very crucial in the subsequent incremental backup process and will be used as a starting point to begin the next incremental backup. After finishing the full backup, mysql-backup-post removes the database lock.

Lock Query:
Код: выделить все
FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)

Find Lock Queries:
Код: выделить все
mysql -u[username] -p[pass] -e "show processlist" | grep "SELECT SLEEP(86400)" | awk '{print $1}'


Requirements


root privileges to install package and update mysql.conf
mysql-community-client package
installation automysqlbackup and mysql-incremental

Installation


Install mysql-community-client package for your distro.
Note: after the MySQL installation you must have the 'mysqlshow' command.

Install automysqlbackup:
download the package from https://sourceforge.net/projects/automysqlbackup/
tar -xzf [PathYouSavedTarFile] -C /tmp/
cd /tmp/
./install.sh


During installation of automysqlbackup, you will be asked about path of automysqlbackup.conf and its binary, you can leave defaults without any change.

Код: выделить все
rm /etc/automysqlbackup/myserver.conf


Install the mysql-incremental: Download the package from https://sourceforge.net/projects/mysqli ... talbackup/

Код: выделить все
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


Update the automysqlbackup.conf:
Find below parameters, uncomment and change them:
Код: выделить все
CONFIG_mysql_dump_username='Mysql user name. It must has privileges to get Lock'
   CONFIG_mysql_dump_password='Password'
   CONFIG_backup_dir='The backup directory you want to store full and incremental backup'
   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"


Update my.cnf:


Edit the MySQL configuration file:

Код: выделить все
nano /etc/mysql/my.cnf


1- BinLog Format

Due to some limitation on STATEMENT format, my recommendation is to set ROW based format. For more information please see the 'troubleshoot' section in this howto. You can check the type of binary log format by executing "select @@binlog_format;" query. To modify logbin format , you must add binlog_format = ROW to mysql.conf or my.cnf .

2- binlog_do_db

You must specify the databases that you intend to have the related changes in the binary log. Please note if you do not specify any database, any change on any database will be logged into binary log. In this case, if you chose STATEMENT format, maybe you have some trouble when restoring from incremental backup and binlog files. You can add databases to this option:

Код: выделить все
binlog_do_db = DATABASENAME1
binlog_do_db = DATABASENAME2


3- expire_logs_days

To have binary log files for a longer time, you can increase this parameter to a higher value. My recommendation is 60 days. So you must add or change it to "expire_logs_days = 60".

4- log-bin

The directory where the binary logs will be stored. In old MySQL versions, mysql-incremenetal might not be able to find the correct path. So if you get an error about this after executing mysql-incremental, you must update mysql-incremental script and set the binary log path.

5- log_slave_updates

If you are setting up mysql-incremental backup on a slave server, you must enable this option. Normally, a slave does not log updates to its own binary log as they were received from a master server. This option tells the slave to log the updates performed by its SQL threads to its own binary log. http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_log-slave-updates

Run automysqlbackup


Run automysqlbackup manually to have at least one full backup from your specified databases.

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


After executing the command successfully, check the /[BackupDirInAutomysqlbackup]/status/backup_info file for the newly added information about the daily backup. For error details, check /var/log/Backup_Post_Pre_log . The backup file will be stored in the directory /[BackupDirInAutomysqlbackup]/daily/[DatabaseName]/ .

Run mysql-incremental


Run mysql-incremental manually now to have at least one hourly backup.

Код: выделить все
mysql-incremental


In case of an error, the details are logged in the file "/var/log/Backup_Incremental_Log" . The incremental backup files will be stored in the directory /[BackupDirInAutomysqlbackup]/IncrementalBackup/ .

Edit the root crontab


You can schedule mysql-incremental for more than one hour. You can find the total time of full backup from backup_status and then based on that value you set an accurate schedule time. Of course mysql-incremental backup does have a mechanism to find any running full backup before start, so there is no concern about conflict between incremental and full backup.

Код: выделить все
crontab -e

Код: выделить все
5 00 * * * root /usr/local/bin/automysqlbackup
25 *  * * * root  /etc/automysqlbackup/mysql-incremental


Restore Database


In order to restore up to a specific time (point in time recovery), first you must restore one full daily backup and then restore sequentially related incremental backup files. To clarify more, here is the steps to recover testDB database. In sample scenario we intend to recover our data up to 2015-5-01 at 2 AM. we have set /backup as our main backup dir and testDB as our target database:

Код: выделить все
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


Important notes and Troubleshooting


MySQL supports different formats for the binary log. Some Mysql versions use 'statement-based' as binlog format that this type of binlog does have some limitations that we must pay close attention to it when we intent to use it in incremental backup procedure. When mysql is set to statement-base format, it does not able to filter correctly based on databases. If you set 'USE or \u' to change database and then update another database which is not included in binlog-do-db, the statement will be logged in binlog file that it is not desirable state! and will expose some issue when restoring based on specific database and also if you change to another database that is not included in binlog-do-db, and update a database which is included in binlog-do-db, the statement will not logged to binlog file. our purpose from adding databases to binlog-do-db is to filter based on database,but it does not work as expected. If USE or \u is not executed before running queries, mysqlbinlog can not extract 'update queries' related to one database. We will explain more this issue with below scenarioes:

Код: выделить все
databases:
 - binlog
     - person (table)
  - binlog2
     - person (table)

 binlog-do-db=binlog2 (it is supposed only change of this database are logged to binlog file)
--------Scenario 1---------
\u binlog2
insert into person (data) values ('17') ---> loged in binlog  *desired state*
insert into binlog.person (data) values ('25'); ---> logged in binlog (target database is 'binlog' ) *undesired state*
--------Scenario 2---------
\u binlog
insert into person (data) values ('17') ---> is not logged in binlog  *desired state*
insert into binlog2.person (data) values ('25'); ---> is not logged in binlog (target database is 'binlog2' ) *undesired state* because the binlog2 database
is begin changed, so we want to have this change,but it will not logged in logbin file
--------Scenario 3---------
if you just connect to database without any USE or \u statement, all of updates on any databases will be logged, but mysqlbinlog can not able to filter
based on specific database, so that is not desirable state for our purpose in incremental backup. Using USE or \u before executing update queries, is very
important. Because mysqlbinlog finds update queries based on USE statement in binlog file.


Work around for the mentioned issue


1) By defining users on databases in a way that each user only has access to one database to update (application user) and when connection to database, the name of database must be specified. Of course most of applications do have a config file that the credentials and name of database are set in it, so in that case you will not have a cross-access on databases and there will not be concern on using "\USE or \u".

2) If you use row-based binlog format, so all of mentioned issue will be gone. in other words,row-based format is much more proper method for binlog. https://dev.mysql.com/doc/refman/5.1/en ... y-log.html

Log Files


I did try to log everything in a log file so you can find enough information in the logs:

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


The file "backup_info" contains the detailed info about the backup and when the backup finished (Times are in Unix Time format). It contains the binlog name and position of the timepoint the backup started, the type of backup, number of backups since the last full backup and the duration of the backup.

Sample backup_info:

Код: выделить все
1431043501,mysql-bin.000026,120,Daily,2015-05-08,0,24
1431044701,mysql-bin.000026,120,Hourly,2015-05-08,1,1


Here are description of the different values:

Код: выделить все
1th) 1431043501 : indicates the time when the backup has been finished. You can run date --date @1431043501 command on the server the backup has been done to view it in human readable format.
 2th) Mysql-bin.000026 : indicates the binary log name that backup up to this file has been done.
 3th) 120 : indicates the position of binlog  that backup up to this position in binary log has been done.
 4th) Daily/Hourly: indicates type of backup. Daily does mean the full backup by automysqlbackup script and Hourly is done by mysql-incremental script.
 5th) 2015-05-08: The date that backup has been done. This date will be used in creating directory for incremental backup and also as a base for restore hourly backups. In restoring procedure, first a full backup is restored and then sequentially other incremental backup are restored.
 6th) 0 : indicates number of backups from previous full backup. 0 does mean the backup is full and others mean hourly. This number is very important in restoring procedure.
 7th) 24: The backup duration in second.


Источник

Друзья, очень прошу перевести это на наш родной. Очень нужная вещь.
Аватар пользователя
ALEXX
Администратор
Администратор
 
Автор темы
Сообщений: 1152
Фото: 35
Стаж: 3 года 9 месяцев 2 дня
Откуда: Королёв
Благодарил (а): 337 раз.
Поблагодарили: 187 раз.

MySQL Incremental Backup

Спонсор

Спонсор
 

Re: MySQL Incremental Backup

Сообщение Шпак Дмитрий » 28 май 2015, 09:22

что то как то костыляво :)
Шпак Дмитрий
Местный говорун
Местный говорун
 
Сообщений: 589
Стаж: 3 года 8 месяцев 4 дня
Откуда: Russian_federation, какой то
Благодарил (а): 20 раз.
Поблагодарили: 145 раз.

Re: MySQL Incremental Backup

Сообщение ALEXX » 28 май 2015, 09:44

Хоть что то появилось, а то полные бэкапы за сутки иногда не есть хорошо.
Аватар пользователя
ALEXX
Администратор
Администратор
 
Автор темы
Сообщений: 1152
Фото: 35
Стаж: 3 года 9 месяцев 2 дня
Откуда: Королёв
Благодарил (а): 337 раз.
Поблагодарили: 187 раз.

Re: MySQL Incremental Backup

Сообщение Шпак Дмитрий » 28 май 2015, 09:48

hotmax писал(а):

Хоть что то появилось, а то полные бэкапы за сутки иногда не есть хорошо.

Я со слейва всегда делаю, вроде нет проблем. А с малых баз и с основной базы бекап сделать не накладно.
Шпак Дмитрий
Местный говорун
Местный говорун
 
Сообщений: 589
Стаж: 3 года 8 месяцев 4 дня
Откуда: Russian_federation, какой то
Благодарил (а): 20 раз.
Поблагодарили: 145 раз.

Re: MySQL Incremental Backup

Сообщение ALEXX » 28 май 2015, 09:56

Когда наш форум вырастет до серьезного ресурса ( более 300 человек одновременно онлайн ) инкрементное копирование будет ой как кстати...
И да, делать будем его с мастера, а со слейва будем делать суточные.
Аватар пользователя
ALEXX
Администратор
Администратор
 
Автор темы
Сообщений: 1152
Фото: 35
Стаж: 3 года 9 месяцев 2 дня
Откуда: Королёв
Благодарил (а): 337 раз.
Поблагодарили: 187 раз.

Re: MySQL Incremental Backup

Сообщение Шпак Дмитрий » 28 май 2015, 10:00

hotmax писал(а):

Когда наш форум вырастет до серьезного ресурса ( более 300 человек одновременно онлайн ) инкрементное копирование будет ой как кстати...
И да, делать будем его с мастера, а со слейва будем делать суточные.


Не понял я ни чего, что ты будишь делать с мастера, а что со слейва?
Шпак Дмитрий
Местный говорун
Местный говорун
 
Сообщений: 589
Стаж: 3 года 8 месяцев 4 дня
Откуда: Russian_federation, какой то
Благодарил (а): 20 раз.
Поблагодарили: 145 раз.

Re: MySQL Incremental Backup

Сообщение ALEXX » 28 май 2015, 11:22

Правильно, с мастера смысла нет бэкапить... Со слейва значит будем делать.
Аватар пользователя
ALEXX
Администратор
Администратор
 
Автор темы
Сообщений: 1152
Фото: 35
Стаж: 3 года 9 месяцев 2 дня
Откуда: Королёв
Благодарил (а): 337 раз.
Поблагодарили: 187 раз.

Re: MySQL Incremental Backup

Сообщение Шпак Дмитрий » 28 май 2015, 11:28

hotmax писал(а):

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

Вот и я про то же :) И все эти энкрименты мне не понятны. ТОлько окно бинарных логов сделать побольш
Шпак Дмитрий
Местный говорун
Местный говорун
 
Сообщений: 589
Стаж: 3 года 8 месяцев 4 дня
Откуда: Russian_federation, какой то
Благодарил (а): 20 раз.
Поблагодарили: 145 раз.

Re: MySQL Incremental Backup

Сообщение tupoll » 28 май 2015, 16:29

Мускул,мастер,слэйв чё-то жаргоны при клинописи некстати.На русском куча описаний -ищите на грамотных дистрах.
Правильный линукс-BSD.
Аватар пользователя
tupoll
Уважаемый участник
Уважаемый участник
 
Сообщений: 237
Фото: 3
Стаж: 3 года 8 месяцев 4 дня
Откуда: Vladivostok
Благодарил (а): 31 раз.
Поблагодарили: 44 раз.

Re: MySQL Incremental Backup

Сообщение Oleg65 » 28 май 2015, 22:30

tupoll писал(а):

На русском куча описаний -ищите на грамотных дистрах.
Ну да, то же удивился немного: зачем переводить? Да и вообще... :)
Аватар пользователя
Oleg65
Местный говорун
Местный говорун
 
Сообщений: 856
Стаж: 3 года 8 месяцев 5 дней
Откуда: г.Коломна Моск.обл.
Благодарил (а): 72 раз.
Поблагодарили: 209 раз.

След.

Вернуться в Серверы

Кто сейчас на форуме

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 1

cron