MySQL backup xtrabackup

MySQL backup xtrabackup (recommended for backup InnoDB)

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

Installing Xtrabackup

[email protected] ~]# Yum install Percona-xtrabackup

Full backup Node One

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

[Email protected] ~]# Vim/etc/my.cnf[mysqld]innodb_file_per_table=on

Create a backup directory

[Email protected] ~]# mkdir/backpus/

Backup

[Email protected] ~]# Innobackupex--user=root/backpus/

Copy to Node 2

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

Node two

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

[email protected] ~]# Yum install Percona-xtrabackup

Move the backup file to the/backups directory

[Email protected] ~]# Mkdir/backups/[[email protected] ~]# mv 2016-07-13_20-27-04//backups/

To organize your backup files

[Email protected] ~]# Innobackupex--apply-log/backups/2016-07-13_20-27-04/

Restores

[Email protected] ~]# Innobackupex--copy-back/backups/2016-07-13_20-27-04/

Modify file Permissions

[Email protected] ~]# chown-r mysql:mysql/var/lib/mysql/*

Incremental backup

modifying data

[[email protected] ~]# mysqlmariadb [(none)]> use Hellodb; MariaDB [hellodb]> CREATE TABLE Xxoo2 (id int); MariaDB [hellodb]> INSERT INTO Xxoo2 values (1), (10), (83);

Incremental backup of files that were previously fully backed up

[Email protected] ~]# Innobackupex--incremental/backpus/--incremental-basedir=/backpus/2016-07-13_20-27-04

Read-only for full backups, prepare for incremental and complete merges

[Email protected] ~]# Innobackupex--apply-log--redo-only/backpus/2016-07-13_20-27-04/

Merge increment to full

[Email protected] ~]# Innobackupex--apply-log--redo-only/backpus/2016-07-13_20-27-04/--incremental-dir=/backpus/ 2016-07-13_23-13-25/

viewing incremental backup files

[Email protected] ~]# less/backpus/2016-07-13_23-13-25/xtrabackup_checkpoints backup_type = INCREMENTALFROM_LSN = 1642047TO_LSN = 1646912LAST_LSN = 1646912compact = 0

View full backup files

[Email protected] ~]# Less/backpus/2016-07-13_20-27-04/xtrabackup_checkpointsbackup_type = FULL-PREPAREDFROM_LSN = 0TO_LSN = 1646912LAST_LSN = 1646912compact = 0

After that, 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 the process of multiple errors, are in the MySQL database and the owner of the group permissions arise.

This article is from the "Wind" blog, please be sure to keep this source http://xsllqs.blog.51cto.com/2308669/1827608

MySQL backup xtrabackup