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

Mysql physical backup tool Xtrabackup installation configuration

1 introduction to the Xtrabackup tool

Xtrabackup is a tool for physical data backup of InnoDB, supports online hot backup (backup does not affect data read and write), and is a good substitute for commercial backup tool InnoDB Hotbackup.

Xtrabackup has two main tools: xtrabackup and innobackupex

(1) xtrabackup can only back up InnoDB and XtraDB data tables, but not MyISAM data tables.

(2) innobackupex is used to back up non-InnoDB tables. At the same time, the xtrabackup command is called to back up the InnoDB table, and the mysql server sends commands for rendezvous, such as adding read locks, obtaining sites, and so on. To put it simply, innobackupex has a layer of encapsulation on top of xtrabackup.

In general, we want to back up the 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 carried out through the innobackupex command; another reason is that we may need to save site information.

2 principle of XtraBackup backup

XtraBackup is based on the crash-recovery function of InnoDB, which replicates the data file of InnoDB. Because the table is not locked, the copied data is inconsistent. Crash-recovery is used during recovery to make the data recovery consistent.

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

When XtraBackup backs up, it copies InnoDB data page by page and does not lock the table. At the same time, XtraBackup has another thread monitoring transaction log, and once the log changes, it copies the changed log pages away. Why are you in a hurry to copy? Because the size of the transaction log file is limited, when it is full, it will start all over again, so the new data may overwrite the old data.

During the prepare process, XtraBackup uses the copied transaction log to crash recovery the backed up InnoDB data file.

3 characteristics of XtraBackup backup

(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) Fast reduction speed.

4 XtraBackup installation

I wanted to install it with source code, but I always reported errors, so I had to use yum method to install it.

Open the official website yum source installation method instructions

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

Follow the steps to install the yum source of 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

Install xtrabackup

# yum list | grep percona # tests whether the yum source of the official website has been successfully installed

# yum install percona-xtrabackup-24

# xtrabackup-version # View version information

You can also choose other versions to install with rpm package.

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

5 parameters commonly used in XtraBackup

-- user=USER specifies the backup user, or the current system user if not specified

-- password=PASSWD specifies the backup user password

-port=PORT specifies the database port

-- defaults-group=GROUP-NAME is used in multiple instances

-- host=HOST specifies the host of the backup, which can be a remote database server

-- apply-log applies the xtrabackup_logfile transaction log file in BACKUP-DIR. In general, after the backup is complete, the data cannot be used for restore operations because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time. The main role of "preparation" is to make the data file consistent by rolling back uncommitted transactions and synchronizing committed transactions to the data file.

-- apply-log-only this option causes only the redo phase to be performed when preparing a backup (prepare), which is important for incremental backups.

-- database specifies the database to be backed up, and multiple databases are separated by spaces

-- defaults-file specifies the configuration file of mysql

-- copy-back copies the backup data back to the original location

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

-- incremental-basedir=DIRECTORY incremental backups using the directory that points to the last incremental backup

-- incremental-dir=DIRECTORY incremental backup restore is used to merge incremental backups to the full, and to specify the full path

-redo-only merges incremental backups

-- rsync speeds up local file transfer, which is suitable for non-InnoDB database engine. Not shared with-- stream

-- safe-slave-backup

Backup files generated by no-timestamp do not take timestamps as directories.

6 XtraBackup backup data 6.1 full backup

Check what 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)

The full backup data is stored under / data/backup/full, and innobackupex 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

The files in the backed-up directory indicate that the * * part is the library built before the backup.

# 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 commands

(2) ibdata1-the backed up tablespace file

(3) xtrabackup_binary-the executable file of xtrabackup used in backup

(4) xtrabackup_binlog_info-the location of the binary log files currently in use by the mysql server and the binary log events up to the moment of backup

(5) xtrabackup_logfile-the backed up redo log file.

(6) xtrabackup_checkpoints-backup type (such as full or incremental), backup status (such as prepared status) and LSN (log serial number) scope information

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

Backup_type = full-backuped # you can see that it is a full backup

From_lsn = 0 # record LSN, log offset

To_lsn = 8096126

Last_lsn = 8096126

Compact = 0

Recover_binlog_info = 0

6.2 recover complete data 6.2.1 simulate database data corruption

Close the database and remove the data file

# service mysqld stop

# mv / application/mysql/data / opt/

# mkdir / application/mysql/data # create a data directory

# chown-R mysql.mysql / application/mysql/data

6.2.2 prepare a complete data backup file

In general, this / data/backup/full/2017-12-11 backup 09-10-45 backup cannot be used for recovery because the backed up data may contain transactions that have not been committed or transactions that have been committed but have not been synchronized to the data file, when the data file is in an inconsistent state. Therefore, we are now trying to make the data file consistent by rolling back uncommitted transactions and synchronizing 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 September 09-10-45 /

6.2.3 start data recovery

# innobackupex-- defaults-file=/etc/my.cnf-- user=root-- password=abc123-- socket=/tmp/mysql.sock-- copy-back / data/backup/full/2017-12-11 September 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 (x86x64) (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

Solution:

[mysqld]

Datadir = / application/mysql/data

Error occurred when starting mysql

# service mysqld start Starting MySQL. ERROR! The server quit without updating PID file (/ application/mysql/data/db01.pid).

Just change the data directory owner to mysql.

# chown-R mysql.mysql / application/mysql/data/

At this time, the data of mysql has been successfully restored.

6.3 incremental backup

Incremental backup is based on full backup.

Incremental backup directory 1:/data/backup/increment1

Incremental backup directory 2:/data/backup/increment2

6.3.1 first incremental backup and recovery 6.3.1.1 first incremental backup

Prepare it first and then add it to it. See 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-1111 09-10-45

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. After the execution of this command, the innobackupex command creates a new time-named directory in the / data/backup/increment1 directory to hold all incremental backup data. In addition, when you perform an incremental backup again after an incremental backup, its-- incremental-basedir should point to the directory where the last incremental backup was located. It is important to note that incremental backups can only be applied to InnoDB or XtraDB tables, and for MyISAM tables, incremental backups are actually performed as full backups.

6.3.1.2 recovery of the first additional equipment

Prepare a complete data recovery file

Complete-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 September 09-10-45 /

Additional 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 September 09-10-45 /-incremental-dir=/data/backup/increment1/2017-12-11 million 10-51-27

Simulate database corruption, see 6.2.1 for details

Start to recover data, see 6.2.3 for details

6.3.2 second incremental backup and recovery 6.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 million 10-51-27

Generate a folder named after the system time

# ls / data/backup/increment2/

2017-12-11 10-58-55

6.3.2.2 recovery of the second additional equipment

Prepare a complete data recovery file

Additional 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 September 09-10-45 /-incremental-dir=/data/backup/increment2/2017-12-11 million 10-58-55

Simulate database corruption, see 6.2.1 for details

Start to recover data, see 6.2.3 for details

6.4 more than three incremental backups

Follow 6.3.2.2 and so on

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.