Use Xtrabackup to implement Mysql backup _mysql

Download Address: https://www.percona.com/downloads/XtraBackup/

Install Xtrabackup

[Root@node1 ~]# Yum Install Percona-xtrabackup

Full backup

Node One

Modify the configuration file to set a separate table space for each table, which must be set when the database is installed

[Root@node1 ~]# vim/etc/my.cnf
[mysqld]
Innodb_file_per_table=on

Create a 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 two

(after the MySQL installation of Node II do not start, after startup due to the generation of initialization files can not be restored)

[Root@node2 ~]# Yum Install Percona-xtrabackup

Move backup files to the/backups directory

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

Organize the backup files

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

Restores

[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 backups

modifying data

[root@node1 ~]# MySQL
mariadb [(none)]> use Hellodb;
MARIADB [hellodb]> CREATE TABLE Xxoo2 (id int);
MARIADB [hellodb]> INSERT INTO Xxoo2 values (1), (10), (83);

Incremental backup of previously fully backed up files

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


Read-only for full backups, preparing for incremental and full consolidation

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


Merge increment 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 = incremental
from_lsn = 1642047
to_lsn = 1646912
last_lsn = 1646912
Compact = 0

View full backup files

[Root@node1 ~]# less/backpus/2016-07-13_20-27-04/xtrabackup_checkpoints
backup_type = full-prepared
from_ LSN = 0
to_lsn = 1646912
last_lsn = 1646912
Compact = 0

Then, if you have a new incremental backup file, you can continue to restore the full backup file when you merge the restore on the full backup file

Note: MySQL access rights, I operate in the process of many errors, are in the MySQL database and the permissions of the group has problems.