Shulou(Shulou.com)06/01 Report--

This article mainly tells you how to achieve full incremental backup and recovery of MySQL database. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on how to achieve full incremental backup and recovery of Percona in MySQL database can bring you some practical help.

Percona's innobackupex has the following characteristics:

(1) the backup process is fast and reliable

(2) the backup process will not interrupt the transaction in progress.

(3) it can save disk space and traffic based on compression and other functions.

(4) automatic backup verification

(5) the reduction speed is fast.

Combined with scheduled tasks and scripts, this paper uses the innobackupex command of Percona to implement the following backup strategy:

(1) make a full backup of the database at 23:00 every Monday, and the name of the script is allbbsdbbak.sh.

(2) make incremental backups of the database from Tuesday to Sunday 23:30 every week. The name of the script is newbbsdbdata.sh.

Second, the command introduction:

1. Principle: at the beginning of the backup, a background detection process will be started to detect the changes of mysql redo in real time. Once a new log is found, the log will be recorded in the background log file xtraback_log immediately, then copy the data file of InnoDB (system tablespace file ibdatax), execute flush tables with readlock after the copy is finished, and then copy .fri, .MYI, .MYD and other files, and finally execute unlock tables and stop xtrabackup_log.

2. Rely on software:

Using the innobackupex command of percona software, you need to install four software packages: perl-DBD-mysql, perl-Digest-MD5, libev and percona-xtrabackup.

3. Command format:

The innobackupex command has many parameters, and the common parameters used for data backup are in the following format (for more information, please see help 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 name used by user backup

-- password backs up the user's password

If the port is 3306, you don't have to write it.

-- the name of the subdirectory stored in the backup file without date naming by no-timestamp

-- database "library name"-single library, "library 1 library 2"-multiple libraries, "library. table"-single table

4. Restore:

(1) rm-rf / var/lib/mysql

Mkdir / var/lib/mysql

(2) restore log

# innobackupex-user root-password 123456-databases "mysql sys performance_schema db1"-apply-log / allback

(3) restore 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 / / the directory for incremental backup, which is automatically created during backup. If you create it in advance, you need to leave it empty below.

-- 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) restore 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) when innobackupex recovers data, the / var/lib/mysql directory is required to be empty, so it is generally necessary to back up the three system libraries (mysql, sys, perfomance_schema).

(2) the / allbak directory can be created automatically during backup. If it is created in advance, it needs to be empty below.

(3) this command can only implement incremental backups using InnoDB engine libraries, while other storage engines can only make full backups.

III. Backup script

1. Full backup script

2. Incremental backup script

MySQL database how to achieve Percona full incremental backup and recovery will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.