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

XtraBackup backup tool

Three steps

1. Install the XtraBackup package. two。 Use XtraBackup to perform full and incremental backups. 3. Prepare the data recovery directory.

Need yum-y install perl-DBD-MySQL perl-Digest-MD5 and

Libev-4.15-1.el6.rf.x86_64.rpm these three packages

If these dependent packages are not installed, an error will be reported when you install percona-xtrabackup directly

The first two packages can be filtered with the following command

Yum list | grep perl | grep-I-E 'dbd | digest'

1. Installation

Yum-y install perl-DBD-MySQL perl-Digest-MD5

Rpm-ivh libev-4.15-1.el6.rf.x86_64.rpm

two。 Full backup

# innobackupex-- user root-- password password-- databases= "system Library list Storage Database" backup directory name-no-timestamp

When recovering data, the directory needs to be empty and does not need to be created

Mysql Sys Performance_schema, these three must be backed up, of course, and your other data.

If you back up a directory named / allbak

So there are two kinds of files in this directory.

Configuration information and backed up data

The following is a list of files to understand:

# xtrabackup_binlog_info backup information (full backup)

# xtrabackup_checkpoints log serial number range

# xtrabackup_logfile corresponds to sql command

# data size information generated by Ibdata1 sql

# backup-my.cnf.. page_size adjustable size can be adjusted for optimization in a production environment

(only INNODB supports incremental backup (supports transaction rollback)

Cd / var/lib/mysql # ib_logfile0 (sql Command) ib_logfile1

LSN (log serial number))

Fully recover data

Restore the log first and then the data

Rm-rf / var/lib/mysql (empty directory is required for data recovery and no need to be created)

Mkdir / var/lib/mysql

Innobackupex-- user root-- password 123456-- databases= "mysql sys performance_schema"-- apply-log / allbak # backup all without writing the library

Ls / var/lib/mysql

Innobackupex-user root-password 123456-databases= "mysql sys performance_schema"-copy-back / allbak

Ls / var/lib/mysql

Ls / var/lib/mysql-ld

Chown-R mysql:mysql / var/lib/mysql

Ls-l / var/lib/mysql

Systemctl restart mysqld

Cat / allbak/xtrabackup_checkpoints # check the backup type

Backup_type = full-prepared # has been restored

# #

-- incremental directory name

-- incremental-basedir= directory name

Incremental backup (remember! First a full backup and then an incremental backup)

Innobackupex-- user root-- password 123456-- databases= "system Library list Storage Database"-- incremental directory name (current)-- incremental-basedir= directory name (last backup)-- no-timestamp

Innobackupex-user root-password 123456

Databases= "mysql sys performance_schema"-- incremental / new1-- incremental-basedir=/allbak-- no-timestamp

Check the serial numbers of the two backups

Cat / allbak/xtrabackup_checkpoints

To_lsn = 2536507

.

Cat / new1/xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 2536507

To_lsn = 25371201

.

# #

-- incremental-dir= directory name

Incremental recovery

1. Restore log files

# innobackupex-user root-password abc123-databases= "system Library list Storage Database"-apply-log-redo-only / allbak (full recovery)

# innobackupex-- user root-- password abc123-- databases= "system Library list Storage Database"-- apply-log-- redo-only / allbak-- incremental-dir= directory (recovery increment)

# innobackupex-- user root-- password abc123-- databases= "system Library list Storage Database"-- apply-log-- redo-log / allbak-- incremental-dir= directory (recovery increment)

Cat / allbak/Xtraback_checkpositions

Lsn (record the scope of all log recoveries)

two。 Recover data

# innobackupex-- user root-- password abc123-- databases= "system Library list Storage Database"-- copy-back full backup directory name

# chown-R mysql:mysql / var/lib/mysql remember to add permissions or the service cannot be started

3. Restart the service

Systemctl restart mysqld

#

Beginners don't spray.

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.