Percona Full-scale incremental backup and recovery of MySQL database

Tag: ATI display art supports ASE stroke float amp Password

First, overview:

     mysqldump is a tool for MySQL database backup provided by Mysql-commiunity, but can only be used for full-scale backups, if you want to implement incremental backups based on Binlog log implementations.

      This article describes how to do full backup, incremental backup, and recovery using the commands provided by the third-party software Percona Innobackupex. percona-xtrabackup mainly has two tools, one of which is Xtrabackup, dedicated to backing up InnoDB tables, and the other is Innobackupex, which is the former seal A Perl script that supports backup InnoDB and MyISAM. In the physical Backup tool for MySQL,Percona is probably the most popular and powerful tool.

PerconaOfInnobackupexHas the following characteristics:

(1)The backup process is fast and reliable;

        (2) The backup process does not interrupt the transaction being performed;

        (3) can save disk space and traffic based on functions such as compression;

        (4) Automatically implement backup inspection;

        (5) Fast restore speed.

     This article combines scheduled tasks and scripts, using the Percona Innobackupex command to implement the following backup strategy:

         (1 ) Make a full backup of the database at 123 points per week, with the script name allbbsdbbak.sh.
(2) weekly from Tuesday to Sunday 23:30 make an incremental backup of the database with the script name newbbsdbdata.sh.

Second, the command introduction:

1, the principle: the beginning of the backup will start a background detection process, real-time detection of MySQL redo changes, once found a new log write, immediately log into the background log file Xtraback_log, After copying the InnoDB data file (System tablespace file Ibdatax), execute flush tables with Readlock after copying, and then copy. Fri,. MYI,. MyD and other files, and finally execute unlock tables and stop Xtrabackup_log.

2. Dependent software:

Using the Percona softwareInnobackupexcommand, need to installPerl-dbd-mysql、PERL-DIGEST-MD5、Libev、Percona-xtrabackupFour packages.

3. Command format:

The parameters of the Innobackupex command are very numerous and the commonly used parameter formats for data backup are as follows (for specific parameters refer to the Help information or this article: https://www.cnblogs.com/waynechou/p/xtrabackup_backup.html):

# Innobackupex [--user=name] [--password=word] [--port=port] [--no-timestamp] [--databases=list]/backupdir

--user user name used for backup
--password Backup User's password

--port if it's 3306, you don't have to write.

--no-timestamp name of a subdirectory stored with a backup file without a date
--database "Library name"-Single Library, "Library 1 Library 2"-Multiple libraries, "libraries. Tables"-Single table
4. Recovery:
(1) Rm-rf/var/lib/mysql
Mkdir/var/lib/mysql
(2) Recovery log
# Innobackupex--user root--password 123456--databases "MySQL sys performance_schema DB1"--apply-log/allback
(3) Recovering data
# Innobackupex--user root--password 123456--databases "MySQL sys performance_schema DB1"--copy-back/allback
(4) Chown-r Mysql:mysql/var/lib/mysql
(5) Restart database service
Systemctl Restart Mysqld
(6) Log in to the restored database
# mysql-uroot-p123456
5. Incremental Backup:
#innobackupex--user root--password 123456--databases= "System Library List Storage Database"--incremental directory name--incremental-basedir= directory name-- No-timestamp
--incremental Directory Name//incremental backup of the directory, when the backup is automatically created, if created well in advance will need the directory below is empty
--incremental-basedir= Directory Name//directory of the last backup
6. Incremental recovery:
#innobackupex--user root--password 123456--databases= "System Library List Storage Database"--incremental-dir= directory name--no-timestamp
--incremental-dir= Directory Name//backup Data directory for incremental recovery
--redo-only//
(1) Rm-rf/var/lib/mysql
Mkdir/var/lib/mysql
(2) Restore log files
# Innobackupex--user root--password 123456--apply-log--redo-only/allback
# Innobackupex--user root--password 123456--apply-log--redo-only/allback--incremental-dir=/new1
# Innobackupex--user root--password 123456--apply-log--redo-only/allback--incremental-dir=/new2
(3) Recovering data
# Innobackupex--user root--password 123456--copy-back/allback
(4) Chown-r Mysql:mysql/var/lib/mysql
(5) Restart database service
Systemctl Restart Mysqld
(6) Log in to the restored database
# mysql-uroot-p123456

Note: (1) Innobackupex recovery data requires the/var/lib/mysql directory is empty, it is generally required to three system libraries (MySQL, sys, Perfomance_schema) to be backed up.
(2) The/allbak directory can be created automatically when the backup is made, if it is created in advance, the directory below is empty.
(3) This command only implements incremental backups using the InnoDB engine library, and only a full-scale backup of the other storage engines

Third, backup script

1, full-scale backup script

2. Incremental backup Script

3. Scheduled Tasks

Percona Full-scale incremental backup and recovery of MySQL database