Rupid MySQL Backup
Data backups
We perform data backups everyday at 2:00 am and restore its data on the other database to ensure that data backup and restore is being done perfectly. We perform mysql backup and restore it in Devops server and expose its metric to the grafana about the back up and restore details. So that we can make alerting on it. All this operations are being done everyday with the help of jenkins pipelines.
Stage view of Jenkins pipeline for taking mysql full backup and restore
Backup panel visualisation in grafana
Grafana > Dashboards > rupid-prod-mysql-monitoring > MySql backup
0: MySQL backup successful and Database is consistent
1: MySQL backup is inconsistent
2: Backup creation failed on MySQL server
3: Failed to Copy backup to Backup server
4: Importing backup failed on Backup server
Incremental Backup
Using Percona XtraBackup
-
Incremental backups work because each InnoDB page contains a log sequence number, or LSN. The LSN is the system version number for the entire database. Each page’s LSN shows how recently it was changed.
-
The algorithm reads the data pages and checks the page LSN of the last incremental Backup and take the data from last LSN to present LSN.
We can see these LSN details in .xtrabackup_checkpoints file
Sample contents of .xtrabackup_checkpoints file of incremental backup folder
backup_type = incremental
from_lsn = 1626007`
to_lsn = 4124244
last_lsn = 4124244
compact = 0
recover_binlog_info = 1
- we maintain 31 days old full backup files in rupid devops server
Creating Incremental Backup
xtrabackup --backup --target-dir=${backupDir}/inc_${currentDate} --incremental-basedir=${backupDir}/inc_${lastDate} -u ${db.user} -p${db.password}
The inc_${lastDate}/ contains the incremental backup files one of those is, .xtrabackup_checkpoints file. The xtrabackup checks the last_lsn value, the xtrabackup checks that last lsn value before taking incremental backup and take the incremental backup from that last Log Sequence Number.
Preparing Incremental Backup
xtrabackup --prepare --apply-log-only --target-dir=${remoteBackupDir}/base_${lastDate} --incremental-dir=${remoteBackupDir}/inc_${currentDate}/
In the process of preparing the incemental backup, it checks the last lsn of last prepared full backup(base data which have already prepared, if not it is the base directory) and prepares the data and merge the differences between the founded log lsn of previous prepared backup and present taken backup.
The next step is to replace all data directory files of mysql-server with the prepared backup folder i.e folder which got incremental changes are merged.
Directory structure of Rupid Demo server backup
-
/data/backups/mysql/rupid-demo/inc : Contains all incremental backup related files
-
inc/compressed : Contains all full backup archives with the file names base_dd-mm-yy.tar.gz
-
inc/inc-dd_mm_yy : : Contains all incremental files with specified dates.
Example flow of Incremental Backup Process
Today: 17-05-2024
- inc_17-05-24/ incremental backup directory taken from demo server and SCP to rupid-devops server to restore
- inc_17-05-24/ directory merged with base_16-05-24/ directory after checking the xtrabackup_checkpoints file in base_16-05-24/
- All incremental changes will be reflected in the base_16-05-24/ directory
- We will take the archive of base_16-05-24/ directory for future purposes i.e base_16-05-24.tar.gz
- Replace all files with data folder of mysql with base_16-05-24/
- Rename base_16-05-24/ as base_17-05-24/ as it assumed it have data at the start of the day 17
- Delete all files older than 31 days in inc/ folder for efficient storage purposes
- Next day the same process will go continue and the base_17-05-24// directory is the base for next incremental backups.
Backup and Restore Set Up
- The rupid-devops server hosts two MySQL servers, one for production databases and another for demo databases.
- The production MySQL server contains the rupid-prod database, while the demo MySQL server manages the rupid-demo database.
- Both servers are backed up and restored regularly on the rupid-devops virtual machine.
- This setup ensures separation of production and demo environments, facilitating efficient database management and maintenance.
Risk identification
We configured the backup metric in a way that it can send email alerts when backup value is other than 0. So that we can alert on the issue and fix it as soon as possible.