MySQL Physical Backup tool xtrabackup installation configuration

MySQL physical Backup tool xtrabackup installation configuration

1????XtrabackupTools Introduction

Xtrabackup is a InnoDB to do physical data backup tools, Support Online hot backup (backup without affecting the data read and write), is a commercial backup tool InnoDB hotbackup a good alternative.

Xtrabackup has two main tools:xtrabackup,Innobackupex

(1)Xtrabackup can only back up InnoDB and XtraDB two data tables, instead of backing up MyISAM data tables.

(2)Innobackupex is used to back up non- InnoDB tables, and the Xtrabackup command is called to back up InnoDB tables, and MySQL server sends commands to rendezvous, such as read-lock, Fetch-point, and so on. Simply put,Innobackupex a layer of encapsulation on top of xtrabackup.

in general, we want to be able to back up MyISAM table, although we may not use the MyISAM table ourselves, but the system under the MySQL library represents MyISAM , so the backup is basically through Innobackupex command, another reason is that we may need to save the bit information.

2???XtrabackupBackup Principle

Xtrabackup based on the InnoDB crash-recovery function, it replicates the InnoDB data file, because the table is not locked, The replicated data is inconsistent, using crash-recovery when recovering , making the data recovery consistent.

InnoDB maintains a redo log, also known as the transaction log(transaction log), which contains all the changes to the InnoDB data. When InnoDB starts, it checks the data file and transaction log, and it does two things:

Xtrabackup a page-by -page copy of the InnoDB data at the time of backup, and without locking the table, Xtrabackup has another thread watching transaction log , once the log has changed, copy the changed log pages away. Why do you have to worry about copying away? Because the transaction log file is limited in size, when it is full, it starts from scratch, so the new data may overwrite the old data.

During the prepare process,xtrabackup uses the transaction log copied to the InnoDB data file that was backed up Crash recovery.

3??XtrabackupBackup Features

(1) Fast and reliable backup process

(2) The backup process does not interrupt a transaction that is executing

(3) ability to save disk space and traffic based on functions such as compression

(4) automatically implement backup inspection

(5) Fast Restore Speed

4? ?Xtrabackupinstallation

originally wanted to use the source code installation, but always constantly error, so had to use the Yum method installed

Open official website Yum Source Installation Method Description

Https://www.percona.com/doc/percona-xtrabackup/LATEST/installation/yum_repo.html

follow the steps to install the Yum source on the official website

# yum Install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

# ls/etc/yum.repos.d/

Centos-base.repo?????? Centos-fasttrack.repo? Centos-vault.repo? Percona-release.repo

Centos-debuginfo.repo? Centos-media.repo????? Epel.repo

Installing Xtrabackup

# yum List|grep Percona???? # test whether to install Yum source on successful website

# yum Install percona-xtrabackup-24

# xtrabackup-version??? # View version Information

Other versions can also be installed in RPM Package mode

https://www.percona.com/downloads/XtraBackup/LATEST/binary/redhat/6/x86_64/

5??XtrabackupCommon Parameters

--user=user???????????????????? Specifies that the backup user, if not specified, is the current system user

--PASSWORD=PASSWD????????????? Specify the backup user password

--port=port???????????????????? Specify the database port

--defaults-group=group-name???? use in multiple instances

--host=host??????????????????? ? Specifies the host for the backup, which can be a remote database server

--apply-log?? Application The Xtrabackup_logfile transaction log file in the backup-dir . In general, data cannot be used for recovery operations after the backup is complete, because the data that is backed up may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the data file. Therefore, the data file remains in an inconsistent state at this time. the primary role of Prep is to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing the committed transactions to the data file.

--apply-log-only?? This option enables backup in preparation (Prepare) , only redo is performed (Redo) phase, which is important for incremental backups.

--database????????????? ??????? Specify the database that needs to be backed up, separated by spaces between multiple databases

--defaults-file???????????????? Specify the configuration file for MySQL

--copy-back???????????????????? Copy the backup data back to the original location

--incremental?????????????????? incremental backup, followed by the path to the incremental backup

--incremental-basedir=directory Incremental backup Use the same directory that points to the last incremental backup

--incremental-dir=directory??? ? incremental backup is used to consolidate incremental backups to full volume to specify a full-standby path

--redo-only???????????????????? Merging incremental Backups

--rsync????? speed up local file transfers for the Non-innodb database engine. Not shared with--stream

--safe-slave-backup

--no-timestamp????????????????? The resulting backup file is not a timestamp directory.

6? ??XtrabackupBacking up data6.1???full-scale backup

See which libraries are available before the backup

mysql> show databases;

+--------------------+

| Database?????????? |

+--------------------+

| Information_schema |

| Binlog???????????? |

| Database1????????? |

| Mysql????????????? |

| Performance_schema |

| Wangning?????????? |

| Wordpress????????? |

+--------------------+

7 rows in Set (0.16 sec)

full backup data is stored under/data/backup/full , andInnobackupex automatically creates a folder + a folder named after the current system time

# Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF? --user=root--password=abc123--socket=/tmp/mysql.sock/data/backup/full

# ls/data/backup/full/

2017-12-11_09-10-45

Backup of the directory in the file description, xxx part of the backup built before the library

# ls/data/backup/full/2017-12-11_09-10-45/

Backup-my.cnf? Ibdata1???????????? wangning??????????????? Xtrabackup_checkpoints

Binlog???????? MySQL?????????????? WordPress?????????????? Xtrabackup_info

database1????? Performance_schema? Xtrabackup_binlog_info? Xtrabackup_logfile

(1) backup-my.cnf-- configuration option information used for backup command;

(2) ibdata1-- backup tablespace file;

(3) Xtrabackup executable files used in xtrabackup_binary--backup ;

(4) Xtrabackup_binlog_info--mysql The binary log file currently in use by the server and the location of the binary log event so far as the backup is made;

(5) redo log file for xtrabackup_logfile--backup.

(6) xtrabackup_checkpoints--the backup type (such as full or incremental), the backup state (such as whether it is already in the prepared state), and the LSN ( log sequence number ) range information;

# cat/data/backup/full/2017-12-11_09-10-45/xtrabackup_checkpoints

Backup_type = full-backuped????? # You can see that it's a full backup

FROM_LSN = 0????????????????????? # LSN logged , log offset

TO_LSN = 8096126

LAST_LSN = 8096126

Compact = 0

Recover_binlog_info = 0

6.2???recover fully-prepared data6.2.1??simulating database data corruption

Close the database and remove the data file

# Service Mysqld Stop

# Mv/application/mysql/data?/opt/???

# Mkdir/application/mysql/data??? # Create Data Catalog

# Chown-r Mysql.mysql/application/mysql/data

6.2.2???prepare a full data backup file

In general, this /data/backup/full/2017-12-11_09-10-45 Backup cannot be used for recovery because the backed up data may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the data file. The data file is in an inconsistent state at this time. Therefore, we are now going to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing the committed transactions to the data file.

# Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF? --user=root--password=abc123--socket=/tmp/mysql.sock--apply-log/data/backup/full/2017-12-11_09-10-45/

6.2.3?Start recovering data

# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=abc123--socket=/tmp/mysql.sock?--copy-back/data/ Backup/full/2017-12-11_09-10-45

171211 10:03:07 innobackupex:starting The copy-back operation

?

Important:please Check that the Copy-back run completes successfully.

?????????? At the end of a successful copy-back run Innobackupex

?????????? Prints "completed ok!".

?

Innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id:a467167cdd4)

Error:datadir must be specified.

When an error occurs when executing the above command, it is because the DataDir directory is not specified in the my.cnf file

Workaround:

[Mysqld]

DataDir =/application/mysql/data

start mysql error

# service Mysqld Start??????????????????????????????????????????????????? Starting MySQL. error! The server quit without updating PID file (/application/mysql/data/db01.pid).

Change the data directory owner to MySQL

# Chown-r mysql.mysql/application/mysql/data/

at this point , MySQL data has been successfully restored.

6.3?Incremental Backup

Incremental backups are performed on a full-scale backup basis

Incremental backup directory 1:/data/backup/increment1

Incremental backup directory 2:/data/backup/increment2

6.3.1? ??first incremental backup and recovery6.3.1.1? incremental backup for the first time

fully prepared and ready for all 6.1

# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=abc123--socket=/tmp/mysql.sock? --incremental/data/backup/increment1--incremental-basedir=/data/backup/full/2017-12-11_09-10-45

To generate a folder named after the system time

# ls/data/backup/increment1/

2017-12-11_10-51-27

--incremental-basedir refers to the directory where the full backup is located, and after this command is executed,the Innobackupex command is/data/backup/increment1 A new time-named directory is created in the directory to hold all the incremental backup data. Also, when incremental backups are performed after an incremental backup, their --incremental-basedir should point to the same directory as the last incremental backup. It is important to note that incremental backups can only be applied to InnoDB or XtraDB tables, and for MyISAM tables, a full backup is actually performed when performing an incremental backup.

6.3.1.2? first-time replenishment recovery

Prepare a complete data recovery file

fully prepared --apply-log

# Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF? --user=root--password=abc123--socket=/tmp/mysql.sock--apply-log? --redo-only/data/backup/Full/2017-12-11_09-10-45/

Add 1--apply-log

# Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF? --user=root--password=abc123--socket=/tmp/mysql.sock--apply-log? --redo-only/data/backup/full/2017-12-11_09-10-45/--incremental-dir=/data/backup/increment1/2017-12-11 _10-51-27

simulate database corruption, see 6.2.1

start recovery data, see 6.2.3

6.3.2??second incremental backup and recovery6.3.2.1? Second Incremental backup

The second incremental backup is based on the first incremental backup

# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=abc123--socket=/tmp/mysql.sock? --incremental/data/backup/increment2--incremental-basedir=/data/backup/increment1/2017-12-11_10-51-27

To generate a folder named after the system time

# ls/data/backup/increment2/

2017-12-11_10-58-55

6.3.2.2? Second Replenishment Recovery

Prepare a complete data recovery file

Add 2--apply-log

# Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF? --user=root--password=abc123--socket=/tmp/mysql.sock--apply-log? --redo-only/data/backup/full/2017-12-11_09-10-45/--incremental-dir=/data/backup/increment2/ 2017-12-11_10-58-55

simulate database corruption, see 6.2.1

start recovery data, see 6.2.3

6.4????more than three incremental backups

follow 6.3.2.2, etc.

MySQL Physical Backup tool xtrabackup installation configuration