Linux Operations and Maintenance: Backup and Recovery of Mysql Database


Operations and maintenance engineers need to backup all kinds of data in their daily work, among which the backup of database data is one of the key points. In order to facilitate management, it is very important to choose which backup scheme.

Types of data backup

Full backup

Full backup refers to a complete copy of all data or applications at a certain point in time. In practical application, the whole system is backed up with a tape, including the system and all the data.


  • Backup data is comprehensive and most complete
  • Backup time is long when data is large
  • There will be a lot of duplicate information in backup data

Incremental backup

Incremental backup means that after a full backup or the last incremental backup, each subsequent backup only needs to backup files that have been added or modified compared with the previous one. This means that the object of the first incremental backup is the incremental and modified files generated by the full backup; the object of the second incremental backup is the incremental and modified files generated by the first incremental backup, and so on.


  • No duplicate backup data compared to full backup
  • The amount of backup data is not large and the backup recovery time is short.
  • Data recovery is cumbersome, it must have the last full backup and all incremental backup tapes, and it needs to reverse the recovery in the order of full backup to incremental backup.

Differential backup

Differential backup refers to those backups that add or modify files during the period from full backup to differential backup.


  • Short backup time, save disk space
  • Less tape needed for recovery and shorter recovery time

Data backup volume (large > small):
Full Backup > Differential Backup > Incremental Backup

Data recovery time (fast > slow):
Full Backup - > Differential Backup - > Incremental Backup

Mysql database backup tool

Mysqldump tool

Mysqldump is a MySQL logical backup tool, which exports objects (tables) in the database as SQL script files. It is a common backup method of MySQL backup, suitable for upgrading and migration between different versions of mysql, but when the database is large, the efficiency is not high.

Following are common uses of the mysqldump command:

# Back up a single database, such as db1
mysqldump -uroot -p123456 db1 > /backup/db1_`date +%F`.sql

# Back up all databases, -A parameter
mysqldump -uroot -p123456 -A > /backup/all_db.sql

# Back up the database of remote host, - H specifies IP address, - P (uppercase P) specifies port
mysqldump -uroot -p123456 -h192.168.30.4 -P3306 db1 > /backup/db1.sql

# Only backup table structure, no backup data, using - D parameter
mysqldump -uroot -p123456 -d db1 > /backup/db1.sql

# Back up data only, not table structure, using - t parameter
mysqldump -uroot -p123456 -t db1 > /backup/db1.sql 

# Back up multiple specified libraries, -B parameter
mysqldump -uroot -p123456 -B db1 db2 db3 > /backup/db123.sql

# Backup specified tables, such as stu tables in db1 Libraries
mysqldump -uroot -p123456 db1 stu > /backup/db1_stu.sql

# Back up multiple tables
Mysqldump-uroot-p123456 Library 1 Table 2 Table 3... > db_tables.sql

# Data recovery
mysqldump -uroot -p123456 dbname < xxx.sql
# Or
mysqldump -uroot -p123456 < xxx.sql

Other parameters:

parameter Explain
-R, –routines Backup stored procedure and function data
–triggers Backup trigger data
Master-data={1 or 2} Tell you the binlog location at the time after backup, if equal to 1, print it as the CHANGE MASTER command; if equal to 2, the command will be prefixed with comment symbols.
–single-transaction Hot standby for InnoDB engine

A complete backup statement (for InnoDB engine):

mysqldump -A -R --triggers --master-data=2 --single-transaction | gzip >/backup/all_$(date +%F).sql.gz

A complete backup statement for multi-engine mixing:

mysqldump -A -R --triggers --master-data=2 |gzip  >/backup/all_$(date +%F).sql.gz  

Backup script
Daily backup work can be done according to requirements. It is common to use shell script combined with task plan. Here is an example of MySQL database backup script.mysql_backup.shThe script is as follows:

Bakdir=/data/backup # Defines the backup file storage directory
D= `date +% F` # Named in date format

# Back up three databases bbs, blog, DB2
for db in bbs blog db2
    mysqldump -uroot -p123456 $db > $bakdir/$db\_$d.sql
cd $bakdir
# Compression of backup files
gzip *_$d.sql
# Delete files that have been backed up for more than a month
find ./ -name "*.gz" -mtime +30 |xargs rm

Add task plan:

[[email protected]_linux1 data]$ chmod +x /usr/local/sbin/
[[email protected]_linux1 data]$ crontab  -e
# Adding content
30 3 * * * /bin/bash /usr/local/sbin/ >/tmp/mysql_backup.log 2>/tmp/mysql_backup.log
# The mission plan consists of:
# The backup script is executed at 3:30 a.m. every day and the correct and error output is written into the mysql_backup.log file.

Xtrabackup backup tool

Xtrabackup is a percona open source free database hot backup software, which can backup InnoDB database and XtraDB storage engine database non-blocking, but also supports MyISAM tables and other parts of the server.

Mysqldump is good for exporting several G databases or tables, and it’s not slow. Once the data volume reaches tens of hundreds of gigabytes,
Whether it’s the pressure on the original library or the performance of the export, mysqldump is out of control. Percona-Xtrabackup backup tool,
MySQL online hot standby work is the best choice, can be carried outFull, incremental, single table backup and restore

Installation of xtrabackup

Environmental description:CentOS Linux release 7.5.1804 (Core) IP=

Install xtrabackup version 2.4:

# Add Yum source
wget -O /etc/yum.repos.d/epel.repo
# Install dependency packages
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
# Download RPM package
# Installation
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

There are four executable files after the installation of Xtrabackup, two of which are more important backup tools.innobackupexxtrabackup:

  1. .xtrabackupOnly InnoDB and XtraDB tables can be backed up (MyISAM backup is supported after version 2.4)
  2. .innobackupexIt is a Perl script encapsulating xtrabackup that supports both InnoDB and MyISAM backups, but requires a global read lock when backing up MyISAM

Full backup of innobackupex

useinnobackupexThe command for full backup is:

innobackupex --defaults-file=/etc/my.cnf  --host=  --port=3306 \
--user=root --password=123456  /data/backup/mysql


  • --defaults-fileSpecify the configuration file of Mysql to find the path of dataDir from the configuration file.
  • --hostSpecify IP, --portSpecify port;
  • --userDesignated users,--passwordSpecify user password;
  • /data/backup/mysqlIt is the directory where the backup files are located, using absolute paths.

The backup data is stored in a specified directory and a directory named after the current date and time is automatically generated, such as2019-06-01_15-05-31

[[email protected]_linux1 mysql]$ pwd
[[email protected]_linux1 mysql]$ ll
Total dosage 0
Drwxr-x - - 2 root 6 June 15:03 2019-06-01_15-03-22
Drwxr-x - - 6 root 188 June 15:05 2019-06-01_15-05-31
[[email protected]_linux1 mysql]#

About Backup Users

Using xtrabackup backup can directly use root users, but it is rare in production environment that does not conform to security rules, so a backup user can be created to backup data. This backup user has the rights of reload, lock tables, replication client, process, super and so on.

mysql  -uroot  -p123456
> grant reload,lock tables,replication client on *.* to 'backupuser'@'localhost'  identified  by  '123456';
> flush  privileges;

After creation, this backup user is used for subsequent backup work, and mysqldump can similarly use non-root users.

Recovery of full backup of innobackupex

Recovery of full backup of innobackupex:

# 1. Preparatory recovery:
innobackupex --apply-log /data/backup/mysql/2019-06-01_15-05-31 --user-memroy=2G

# 2. Stop MySQL/MariaDB and empty (or remove) the data in the dataDir directory.
/etc/init.d/mysql stop
mv /data/mysql /data/mysql_bak

# 3. Recovery
innobackupex --defaults-file=/etc/my.cnf  --copy-back  /data/backup/mysql/2019-06-01_15-05-31

# 4. Change permissions. This / data / MySQL is the dataDir directory in my. CNF
chown -R mysql:mysql /data/msyql

# 5. Start MySQL/MariaDB
/etc/init.d/mysql start

Incremental backup of innobackupex

innobackupex --host=  --port=3306 --user=bakuser --password=your_pass \
 --incremental /data/backup/mysql --incremental-basedir=/data/backup/mysql/last-backup-file


  • --incrementalRepresents that this backup is an incremental backup (if the last backup is a full backup, it can also be considered a differential backup here)
  • --incremental-basedirSpecify which backup (last increment or last full increment) this incremental backup is aimed at. Incremental backup can be done in one loop, such as a full backup on Monday, an incremental backup on Tuesday, and an incremental backup on Wednesday.

Recovery of incremental backup of innobackupex
Preparatory stage:
Step 1: Stop database services
Step 2: Delete or backup data in dataDir
Step 3: Prepare for full backup and recovery:

Innobackupex -- apply-log -- redo-only/PATH/TO/Full Backup Directory

Step 4: Prepare Incremental Backup Recovery 1:

Innobackupex -- apply-log -- redo-only/PATH/TO/full backup directory -- incremental-dir=/PATH/TO/first incremental backup directory

Step 5: Prepare Incremental Backup Recovery 2:

Innobackupex -- apply-log -- redo-only/PATH/TO/full backup directory -- incremental-dir=/PATH/TO/second incremental backup directory

Step N: Prepare the last incremental backup recovery:

Innobackupex -- apply-log/PATH/TO/full backup directory -- incremental-dir=/PATH/TO/last incremental backup directory

The last step in the preparatory phase is to prepare the full amount again:

Innobackupex -- apply-log/PATH/TO/full backup directory # (no redo-only for this time)

Recovery phase:

  1. Innobackupex -- copy-back/PATH/TO/full backup directory
  2. Change authority:chown -R mysql:mysql /PATH/TO/datadir
  3. Start MySQL/MariaDB

Explanation: – – redo-only: When preparing (application log) work, only redo operation will be done, only committed but not applied transactions will be redone, and uncommitted transactions will not be rolled back. The reason is that there is an incremental backup later, and the uncommitted may be submitted when the incremental backup is later. Note that the last incremental backup does not require this option.

Backup and recovery of Mariadb10.3.x and above

MariaDB 10.3.x and above will have problems with the Percona XtraBackup tool.
The reason may be that the redo log format of MariaDB version 10.3 or above is different from before.
The solution is to use mariabackup, an open source tool provided by MariaDB for physical online backup of InnoDB, Aria and MyISAM tables.
This tool is based on Percona’s XtraBackup (Version 2.3.8) solution:

Full backup:
mariabackup --defaults-file=/etc/my.cnf --backup --user=xxx --password=xxx --target-dir /data/backup/2019-06-01

Full recovery:
Stop the database
Empty dataDir
mariabackup --prepare --target-dir /data/backup/2019-06-01/ 
mariabackup --copy-back --target-dir /data/backup/2019-06-01/
Subordinate to the main or subordinate group

Incremental backup:
mariabackup --defaults-file=/etc/my.cnf --backup --user=xxx --password=xxx --target-dir /data/backup/2019-06-01
The first increment: mariabackup -- backup -- user = XXX -- password = XXX -- target-dir/data/backup/2019-06-01_inc1 -- incremental-basedir/data/backup/2019-06-01
The second increment: mariabackup -- backup -- user = XXX -- password = XXX -- target-dir/data/backup/2019-06-01_inc2 -- incremental-basedir/data/backup/2019-06-01_inc1

Incremental recovery:
Get ready first
mariabackup --prepare --target-dir /data/backup/2019-06-01 --apply-log-only
Prepare the first increment:
cd /data/backup
mariabackup --prepare --target-dir ./2019-06-01 --incremental-dir ./2019-02-21_inc1 --apply-log-only
Prepare the second increment:
mariabackup --prepare --target-dir .2019-06-01 --incremental-dir ./2019-02-21_inc2 --apply-log-only
mariabackup --copy-back  --target-dir ./2019-06-01

Reference material

  • Miserable Green Youth. Mysql Backup Recovery and xtrabackup Backup Backup.
  • The zhangguangzhi. innobackex tool backs up MySQL data.
  • Percona XtraBackup. Official Document.