How to Create MySQL Full and Incremental Backup Step by Step Tutorial

How to Create MySQL Full and Incremental Backup Step by Step Tutorial | dbatutorial
In this article we are going to discuss how to take mysql full bakup using mysqldump as well as we will learn how to create mysql incremental backup using binary logs. How to run mysql incremental backup using binary log steps are given below.

Step 1: Check whether binary log is enabled or disabled using below command. 

mysql> show variables like '%log_bin%';

If the value of log_bin is ON, it means binary log already enable so go to the step 2. If log_bin value is OFF it means binary log is disabled so enable binary log that steps are as follow.

i. Find the mysql configuration (my.cnf) file. It location in linux server is /etc/my.cnf.
ii. Edit the file my.cnf and change under the [mysqld] section and add the following lines.
vi /etc/my.cnf
# log_bin=/u01/mysql/mysql/binarylog 
# max_binlog_size=100M

iii. Now Restart MySQL server using below command
[root@srv binaylogs]# systemctl stop mysqld.service  -- Stop mysql server
[root@srv binaylogs]# systemctl start mysqld.service  -- Start mysql server

iv. Now check the binary log status that showed log-bin= ON;

Check binary logs: 
mysql>show binary logs;

On-demand binary log switch using below command
mysql> flush binary logs;

Step 2: An incremental backup should start with a full backup. 

The very first backup must be a full backup. You need to create a full backup using mysqldump. Run the following command to take full backup.

[root@srv dbbackup]# mysqldump -u root -p --flush-logs --delete-source-logs --all-databases > /u01/dbbackup/MySQL_FullBKP_$(date +%d-%m-%Y_%H-%M-%S).sql
Enter password:

--flush-logs: initialize writing a new binary log file
-- delete-source-logs: delete old binary log files

Step 3: Backup binary log files:
After you have modified your data in MySQL database since the last full backup, you can use the cp command to copy binary log files, but copying the active binary log file (to which data is being written right now) may cause some problems. Therefore, you need to run the FLUSH BINARY LOGS command, which will start writing to a new binary log file. In this case, all the accumulated binary log files can be safely copied. After copying the binary log files, they must be deleted so that during the next copying they do not duplicate the already backed up data. The PURGE BINARY LOGS command will help with this. Below is a small script that performs these actions and also puts binary log files in the archive. you can follow the steps below to run an incremental backup.

a) 3.1: Create user for backup purpose and provide required permission

mysql> CREATE USER 'backup'@'localhost' IDENTIFIED BY 'Bkp@1234'; 
mysql> GRANT ALL PRIVILEGES ON * . * TO 'backup'@'localhost';
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

b) 3.2: Create backup user as password less login that steps are given below

# vi /etc/my.cnf

user=backup -- user name
password=password -- Password  
user=backup -- user name
password=password -- Password
After changing my.cnf file values restart the database using following command

[root@srvdb ~]# systemctl stop mysqld.service
[root@srvdb ~]# systemctl start mysqld.service

c) 3.3: Binary log file backup script is given below 

@ Incremental backup script as follow

cd /u01/dbbackup
#path to directory with binary log files
binlogs_path=/u01/mysql/mysql/ #path to backup storage directory
backup_folder=/u01/dbbackup #start writing to new binary log file
mysql -E --execute='FLUSH BINARY LOGS;' mysql
#get list of binary log files
binlogs=$(mysql -E --execute='SHOW BINARY LOGS;' mysql | grep Log_name | sed -e 's/Log_name://g' -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//')
#get list of binary log for backup (all but the last one)
binlogs_without_Last=`echo "${binlogs}" | head -n -1`
#get the last active binary log file (which you do not have to copy)
binlog_Last=`echo "${binlogs}" | tail -n -1`
#form full path to binary log files
binlogs_fullPath=`echo "${binlogs_without_Last}" | xargs -I % echo $binlogs_path%`
#compress binary logs into archive
zip $backup_folder/$(date +%d-%m-%Y_%H-%M-%S).zip $binlogs_fullPath
#delete saved binary log files
#echo $binlog_Last | xargs -I % mysql -E --execute='PURGE BINARY LOGS TO "%";' mysql

# [root@srv dbbackup]# sh adding: u01/mysql/mysql/binaylogs.000043 (deflated 50%)
adding: u01/mysql/mysql/binaylogs.000044 (deflated 42%)


Post a Comment