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

Reference: http://mingxiao.blog.51cto.com/8124243/1623634

A complete set

1. Command installation:

Yum install-y gnupgrpm-Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm(64 bit source) rpm-Uhv https://www.percona.com/redir/downloads/percona-release/percona-release-0.0-1.i386.rpm (32-bit source) yum install-y percona-xtrabackup

[root@aliyun-112] # xtrabackup-v

Xtrabackup version 2.3.3 based on MySQL server 5.6.24 Linux (x86x64) (revision id: 525ca7d)

2. Command function:

A tool for data backup of InnoDB, which supports online hot backup

3. Command parameters:

-- database=sundong backs up the hu database separately. If you don't add this parameter, you will back up the whole database.

-- stream = tar package format currently only supports tar and xbstream

2 > / var/backups/hu.log output information is written to the log

1 > / var/backups/hu.tar.gz is packaged and compressed and stored in this file

-- incremental-dir=/var/backups/plus/ incremental backup directory

The command of the copy-back option copies data, indexes, and logs from the backup directory to the initial location specified in the my.cnf document

The command of the apply-log option is to prepare to start the mysql service on a backup

-- port=PORT

4. Command example:

Backup and package compression (only hu database is backed up)

Innobackupex-- defaults-file=/home/DB/mysql/my.cnf-- user=root-- password=xxxxx-- database=hu-- stream=tar / var/backups/ 2 > / var/backups/hu.log | gzip 1 > / var/backups/hu.tar.gz

5 backup of the whole library. (back up all databases at once)

Innobackupex-defaults-file=/etc/my.cnf-user=root-password=cdqd / var/backups/

(root is the database user name, cdqd is the database password, / etc/my.cnf is the location of the mysql configuration file, and / var/backups/ is the backup file storage directory)

6 recovery

First stop the database, and then delete all database files in the database directory.

First write the log file, and then restore it, as follows. (note: 2010-04-17 July 22-19-30 is an automatically generated folder named after the backup time, which is selected on a case-by-case basis during recovery)

# innobackupex-- defaults-file=/etc/my.cnf-- apply-log / var/backups/2010-04-17 write 22-19-30 (log)

# innobackupex-- defaults-file=/etc/my.cnf-- copy-back / var/backups/2010-04-17 recovery 22-19-30 (recovery)

Then go to your backup directory to see if there are any backup files, and then you need to change the permissions (I did this step by cd to the original database directory)

# chown-R mysql:mysql *

Restart the database and see if the recovery is successful.

Second incremental backup and recovery

Here we do two incremental backups to make it clear.

Insert data for the student table and serve as the basis for incremental backups.

Mysql > use hu;mysql > create table student (`id` int (4), `name` char (40), `values`char (40)) mysql > INSERT INTO student (Name) VALUES ('XiaoHei3'), (' XiaoHei4')

Full backup:

Innobackupex-defaults-file=/etc/my.cnf-user=root-password=123456 / data/backup

This is generated after the backup is completed.

2015-03-23 folder 23-19-24 / folder, this is the directory of the full backup

The first incremental backup-- incremental-basedir= refers to the directory where the full backup is located. / backup is the directory of backup

INSERT INTO student (Name) VALUES ('XiaoHei1'), (' XiaoHei2')

[root@localhost /] #

Innobackupex-incremental / backup-incremental-basedir=/backup/2015-03-23-23-19-24 /

After the execution of this, a 2015-03-23-23-21-49 / will be generated, which can be specified for the second incremental backup.

Mysql >

INSERT INTO student (Name) VALUES ('XiaoHei5'), (' XiaoHei6')

The second incremental backup-- incremental-basedir= refers to the directory where the last incremental backup was located, in this case, the first, and so on.

[root@localhost /] # innobackupex-- incremental / backup-- incremental-basedir=/backup/2015-03-23-23-21-49 /

Step 3: prepare full and incremental backups

The incremental backup is complete, and the next step is to prepare the full backup and incremental backup.

Redo-only is followed by the directory where the full backup is located.

-- incremental-dir refers to the directory where the incremental backup resides. If there are multiple backups, they will be executed several times in order of time.

[root@localhost /] # innobackupex-- apply-log-- redo-only / backup/2015-03-23mm 23-19-24 / [root@localhost /] # innobackupex-- apply-log-- redo-only / backup/2015-03-23mm 23-19-24 /-- incremental-dir=/backup/2015-03-23mm 23-21-49 /-- incremental-dir points to the first incremental backup directory [root@localhost /] # innobackupex-- apply-log-- Redo-only / backup/2015-03-23-23-19-24 /-- incremental-dir=/backup/2015-03-23-23-22-44 /-- incremental-dir points to the second incremental backup directory

Step 4: simulate mysqld failure

[root@localhost /] # service mysqld stop

Shutting down MySQL. SUCCESS!

[root@localhost /] # rm-rf / mydata/data/*

Step 5: restore data

[root@localhost data] # innobackupex-- copy-back / backup/2015-03-23backup 23-19-24 / (directory of the first full backup)

Then go to the backup directory to see if any backup files exist.

Note: after data recovery, before restarting mysqld, you need to modify the permissions of the data directory. The default is root user, root group, mysql user and mysql group.

[root@localhost data] # chown-R mysql.mysql. / *

Service mysqld start

Mysql > use hu

Database changed

Mysql > select * from student

+-- +

| | ID | Name | Course | |

+-- +

| | 1 | xiaoming | English |

| | 2 | xiaohong | Kuihuabaodian |

| | 16 | XiaoHei1 |

| | 17 | XiaoHei2 |

| | 18 | XiaoHei3 |

| | 19 | XiaoHei4 |

| | 20 | XiaoHei5 |

| | 21 | XiaoHei6 |

+-- +

9 rows in set (0.00 sec)

You can see that all the data is intact, and the backup and restore operation is complete.

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.