Use xtrabackup to implement mysql backup and xtrabackupmysql

Use xtrabackup to implement mysql backup and xtrabackupmysql

: Https://www.percona.com/downloads/XtraBackup/

Install xtrabackup

[root@node1 ~]# yum install percona-xtrabackup

Full backup

Node 1

Modify the configuration file and set it to a unique tablespace for each form. This item must be set during database installation.

[root@node1 ~]# vim /etc/my.cnf[mysqld]innodb_file_per_table=ON

Create Backup Directory

[root@node1 ~]# mkdir /backpus/

Backup

[root@node1 ~]# innobackupex --user=root /backpus/

Copy to node 2

[root@node1 ~]# scp -r /backpus/2016-07-13_20-27-04 192.168.1.114:/root/

Node 2

(Do not start mysql on node 2 after installation. After startup, it cannot be restored because an initialization file is generated)

[root@node2 ~]# yum install percona-xtrabackup

Move the backup file to the/backups directory

[root@node2 ~]# mkdir /backups/[root@node2 ~]# mv 2016-07-13_20-27-04/ /backups/

Sort backup files

[root@node2 ~]# innobackupex --apply-log /backups/2016-07-13_20-27-04/

Restore

[root@node2 ~]# innobackupex --copy-back /backups/2016-07-13_20-27-04/

Modify file permissions

[root@node2 ~]# chown -R mysql:mysql /var/lib/mysql/*

Incremental Backup

Modify data

[root@node1 ~]# mysqlMariaDB [(none)]> use hellodb;MariaDB [hellodb]> create table xxoo2 (id int);MariaDB [hellodb]> insert into xxoo2 values (1),(10),(83);

Perform Incremental backup for all previously backed up Files

[root@node1 ~]# innobackupex --incremental /backpus/ --incremental-basedir=/backpus/2016-07-13_20-27-04


Read-Only full backup to prepare for incremental and full merge

[root@node1 ~]# innobackupex --apply-log --redo-only /backpus/2016-07-13_20-27-04/


Merge incremental data to full

[root@node1 ~]# innobackupex --apply-log --redo-only /backpus/2016-07-13_20-27-04/ --incremental-dir=/backpus/2016-07-13_23-13-25/


View Incremental Backup Files

[root@node1 ~]# less /backpus/2016-07-13_23-13-25/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 1642047to_lsn = 1646912last_lsn = 1646912compact = 0

View full backup files

[root@node1 ~]# less /backpus/2016-07-13_20-27-04/xtrabackup_checkpointsbackup_type = full-preparedfrom_lsn = 0to_lsn = 1646912last_lsn = 1646912compact = 0

After that, if there are new Incremental backup files, you can continue to combine and restore the full backup files to restore them.

Note: For mysql access permissions, errors occur many times during the operation, which are caused by the owner and group permissions of the mysql database.