Mysql Commercial Mysqlbackup:How to take Differential or Incremental Backup and resotre using mysqlbackup utility in mysql 8.0.37 enterprise edition
Mysql Commercial Mysqlbackup:How to take Differential or Incremental Backup and resotre using mysqlbackup utility in mysql 8.0.37 enterprise edition
Incremental vs Differential Backup:-
The main difference between incremental and differential backups is how they save space and time by storing only changed files. However, the effectiveness of incremental backups is very different from differential backups.
Before we dive into the differences between Incremental and Differential backups let’s review Full Backups.
Are you still using Full Backups?
In its simplest form, a full backup is the starting point for both Incremental and Differential backup strategies. A full backup, of course, backs up all the data on a partition or disk by copying all disk sectors with data to the backup image file.
A full backup is time-consuming and requires a lot of space, it’s usually used as part of a backup plan with long intervals, like once a week or a month. During this time, if anything goes wrong, a lot of data can be lost. That’s when these two backup strategies come in hand.
An average partition or disk only contains a small number of changes per day, or even per week. Therefore, it makes sense only to backup data that has changed on a daily basis. This is the basis of smart backup strategies.
Incremental vs Differential Backup – How do they compare?
Differential and incremental backups are different backup strategies with the same purpose: optimize backup time and space. Differential backups only back up the files that have changed since the previous full backup, while incremental backups do the same, they back up the files that have changed since the previous incremental or full backup.
What is Differential Backup?
Differential backup strategy backs up files and folders that have changed since the last full backup, on a daily basis. They are much quicker than full backups since less data is being backed up.
One of the benefits of this strategy over incremental backup is that you only need the last full backup and last differential backup to restore data, making the restoration process much faster. However, the amount of space consumed by backed up data will grow with each differential backup until the next full backup.
Even though differential backups are more flexible than full backups, they still present too many obstacles for routine use, particularly as the next full backup gets closer
What is Incremental Backup?
Unlike differential backups, incremental backup copies changed files since the last backup of any type, which can be a full backup or an incremental backup. When incremental backups are performed, the shorter the time interval between backups, the less data needs to be backed up.
It’s the strategy with the most space efficiency between the three: full, differential, and incremental.
In spite of incremental backups giving greater flexibility and granularity (time between backups), they have a reputation for taking longer to restore since they must be constructed from the last full backup and all subsequent incremental backups. .
Step To take Incremental Backup and resotre:-
Step.1.First We are going to take full backup and after that some changes and taking Incremental Backup.
Taking full backup using below command:-
mysqlbackup --user=db_user --password --host='xx.xx.xx.xx' --backup-image=/Enterprise_Bakup/my.mbi --backup-dir=/Enterprise_Bakup/Enterprise_Bakup-tmp backup-to-image
Step.2. Now we are going to login mysql server and create some table and insert record.
Above screen shot we have create one table name t1 and insert some record in t1 table.
step.3.After some changes now we are going to take Incremental backup .using given below command:-
mysqlbackup --user=db_user --password --host=xx.xx.xx.xx --incremental --incremental-base=dir:/Enterprise_Bakup/Enterprise_Bakup-tmp/ --backup-dir=/Enterprise_Bakup/incr-image-dir --backup-image=incremental_image1.bi backup-to-image
Step.4.Now we are going to validate backup position.
full backup
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 21151232
Last Checkpoint LSN : 21151679
End LSN : 21157905
-------------------------------------------------------------
now you can also go to the backup director and validate this.
first we are going to full backup directory and check start lsn and end lsn and checkpoint.
first we are going to Incremental backup directory and check start lsn and end lsn and checkpoint.
Now you can also look into below snapshot when end of the END LSN than inremental backup start START LSN position.
Step.5.Now we are going to login mysql and also check star lsn and end lsn and checkpoint also.using below command
select start_lsn,end_lsn from mysql.backup_history where backup_format='IMAGE' and backup_type='INCREMENTAL';
Step.6.Now we are going to rename mysql data directory and login to mysql .
step.7.Now are going to stop mysql service.
Step.8.Now we are going to validate full backup using given below command.
step.9.Now we are going to resotre full backup using below command
mysqlbackup --user=db_user --backup-image=/Enterprise_Bakup/my.mbi --backup-dir=/Enterprise_Bakup/resore-tmp --datadir=/var/lib/mysql copy-back-and-apply-log
step.9.Now we are going to resotre Incremental backup using below command:-
mysqlbackup --user=db_user --backup-image=/Enterprise_Bakup/incr-image-dir/incremental_image1.bi --backup-dir=/Enterprise_Bakup/resore-inc --datadir=/var/lib/mysql --incremental copy-back-and-apply-log
Step.10. Finally we have successfully restore full backup and incremental backup and now er are going to change permission for mysql data directory .
Step.11.After that we need to change rename file backup-auto.cnf to auto.cnf.
step.12.Finally we are going to restart mysql service and login to mysql.
Now we have successfull take full backup and also take incrementall backup and finally we have restore full backup and incremental backup and restart mysql service successfull.
Comments
Post a Comment