MySQL Backup with Incremental Levels


The script is written to take backup of MySQL Database using Percona Xtrabackup. The innobackupex tool is a Perl script that acts as a wrapper for the xtrabackup C program. It allows us to take backup of Innodb without any locking and also takes backup of other engines like MyISAM with minimum locking. To read more about innobackupex, read the following links.

Innobackupex Script
How Innobackupex Works?

You can download full script here.

Innobackupex provides 2 types of backups:-
  • Full: Which will take the full backup of the given database from start to end.
  • Incremental: It will only take the backup of changed blocks since last base backup or LSN(Log Sequence Number).

The script will take setting from the variables defined and take backups accordingly. I have currently set it up as below.

Every Sunday, it will take full backup of the DB provided. Every day at 0000 hours, it will take an incremental backup since last full backup (Figure 1). It is going to take the backup since last full backup, so its size will be increasing every day.






 If cron job is setup to run after every 3 hours, it will take the incremental backup since last incremental backup as shown in figure below (Figure 2).



Precautions:

  • Do not setup cron job to run this script more than once in an hour, otherwise it will take multiple full backups in one hour which is unnecessary.

Assumptions: 



First of all, we need to setup variables which will control the script.


Now get the required date formats from today's date.

debug_info function is used for printing out debugging information. You can enable/disable printing debug information by setting DEBUG variable as (1/0). If debug is enabled in settings, it will print all the info on screen and write into log file. If disabled, would not print on screen.


We need to check if we are able to connect to the DB, before starting backup script. For security purpose, I am not going to use db credentials in the script. Instead, I am taking credentials from config file. Conf file path can be setup in settings. Format of the conf file should be as below.


Using the config file, I will create a function to check if I can connect the DB.



We need to setup directories to create our DB backups and logs. Every db backup directory will contain the name from the current datetime with format as YYYY-MM-DD_HH_MI_SS. Basic directory structure is as shown in figure (Figure 3).



Will create full backup if called without checking anything for that date and time. Backup will be created in compressed format.

incr_backup function will create an incremental backup based on the two parameters. first parameter is to define where to take the backup based on the type of backup and second parameter will give the path of the base db backup.



This function will setup the initial type of backup based on settings of dates and hour


find_base_and_target function does a lot of work. It will be called in case of daily incremental backup and does the following tasks.
  • Checks if there is any backup for current date in daily folder.
  • If no backup for current date is found, it will get the path of the last full backup and use it as base directory and set target path accordingly.
  • If no backup for current date is found, and nothing found in full backups, it will initiate full backup.
  • If it found a backup for current date, it will go inside it and checks if there is any incremental backup is done based on it inside incr directory.
  • If no incremental backup found inside current date's backup, it will create an incremental backup using current date's backup as base.
  • If incremental backups found inside incr directory, it will use the latest backup from those as base for new incremental backup.
  • It will setup target directory according to the decisions taken for the type of the backup.

Decider function will take input for backup_type function and calls the appropriate functions to initiate backup process.


cleanup_older function is a cleanup function which will delete the old obsolete database backups. It works on the setting of variable backup_copies. It checks how many copies of the full backup you want to keep at any time. It will first delete all the full backups which are not required. After that it will delete all the incremental backups which are orphan i.e. all backups whose base backup is deleted.


We are finished with our setup code and functions. Now we need to call the required functions one by one to make our script work.



Comments

Post a Comment

Popular posts from this blog

MySQL 5.7 cluster with Galera on RedHat 7.4

Galera Cluster is a master-master replication, this means it keeps same data on all the nodes. For this purpose it transfers data continuously between nodes (IST or SST). There are 2 methods with which you can transfer the data. Using Percona XtraBackup or rsync. We are going to use rsync.                         yum install rsync We need to setup SELinux in permissive mode for required ports so that nodes can communicate. For this purpose we are going to use semanage . We can install this by given command.        yum install policycoreutils-python Also you firewall should not stop communication between the nodes, you can use telnet utility to check if nodes can communicate with each other on specific ports. If its not already installed, you can install it.               ...
Read more

Restore single database from full Xtrabackup backup

Percona Xtrabackup does not have any direct way to import single database. I have found a script to restore single database written by Phil Buescher . I have made few changes in the script. There are few perquisite to run the script. You can download the script from here . You need to use mysql-utility mysqlfrm to get the structure of the table from backup. But mysqlfrm works only for innodb tables. Prerequisite:     1. Table should be innodb engine.     2. innodb_file_per_table option should be enabled on the source server        before taking the backup.     3. If you have any view inside your backup directory, script will fail. It will        print the name of the file where it failed. You need to go inside backup directory and delete all the files related to those views. For restoring DB we need to have all the following backups. I am referring to the directory structure creat...
Read more

MySQL default Password

Recently, I was setting up MySQL Galera cluster on redhat 7.4. I installed MySQL 5.7 and Galera cluster on the system according to the instructions provided in galera support page ( Galera Cluster setup ). I have made a blog post for installation and setup here . But when I started the mysql, I was not able to login as I did not setup any password for mysql root account. When I tried logging in without password, I got access denied. After looking for the answers on internet, I found out that MySQL creates a temporary password and write it in log files. It is clrearly mentioned in mysql documentation here . All I needed to do to find this password is to run the following command.      grep 'temporary password' /var/log/mysqld.log Problem solved !
Read more