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

The purpose of this paper is to give force directly and simply.

1. Configure the network yum source (ensure that virtual machines are connected to the Internet)

Rm-rf / etc/yum.repos.d/*

Wget-P / home/backup/ http://mirrors.163.com/.help/CentOS6-Base-163.repo

Mv CentOS6-Base-163.repo / etc/yum.repos.d/CentOS-Base.repo

Cd / etc/yum.repos.d/

Mv CentOS6-Base-163.repo CentOS6-Base.repo

Yum clean all

Yum makecache

2. Install Xtrabackup tools.

Wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

Tar zxvf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz-C / usr/src/

Cd / usr/src/

Cd percona-xtrabackup-2.4.4-Linux-x86_64/

Cp bin/* / usr/bin/

Cd / usr/src/

Wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm

Yum install-y perl*

Rpm-ivh percona-toolkit-2.2.19-1.noarch.rpm

3. Full backup + twice incremental backup + binary log backup.

Mkdir-pv / opt/mysqlbackup/ {full,inc,binlog}

Mysql-uroot-p123456

Mysql > createdatabase xingjun

Mysql > createtable shengong (id int (10), name varchar (20))

Mysql > insertinto shengong values (1)

Mysql > select* from shengong

Mysql > exit

Complete:

Innobackupex--user=root-password=123456 / opt/mysqlbackup/full/

Add data again:

Mysql-uroot-p123456

Mysql > usexingjun

Mysql > insertinto shengong values (2)

Mysql > select* from shengong

Mysql > exit

First incremental backup:

Innobackupex--user=root-- password='123456'-- incremental / opt/mysqlbackup/inc/--incremental-basedir=/opt/mysqlbackup/full/2017-07-09 / 10-50-00 /

Note: 2017-07-09 10-50-00 this document is complete.

Mysql-uroot-p123456

Mysql > usexingjun

Mysql > insertinto shengong values (3)

Mysql > select* from shengong

Mysql > exit

Second incremental backup:

Innobackupex--user=root-- password='123456'-- incremental / opt/mysqlbackup/inc/--incremental-basedir=/opt/mysqlbackup/inc/2017-07-09 / 10-50-46 /

Note: 2017-07-09 10-50-46 this file is the first incremental backup.

Mysql-uroot-p123456

Mysql > usexingjun

Mysql > insertinto shengong values (4)

Mysql > select* from shengong

Mysql > exit

Binary file backup:

Cp/usr/local/mysql/data/mysql-bin.000001 / opt/mysqlbackup/binlog/

4. The simulation data is lost.

Servicemysqld stop

Rm-rf / usr/local/mysql/data/*

5. Dangdang, the key point is coming, and recovering the data.

Prepare before recovery:

Innobackupex--apply-log-- redo-only / opt/mysqlbackup/full/2017-07-09 / 10-50-00 /

Note: 2017-07-09 10-50-00 this is complete.

Innobackupex--apply-log-- redo-only / opt/mysqlbackup/full/2017-07-09 / 10-50-00/--incremental-dir=/opt/mysqlbackup/inc/2017-07-09 / 10-50-46 /

Note: 2017-07-09 10-50-46 this is the first incremental backup.

Innobackupex--apply-log-- redo-only / opt/mysqlbackup/full/2017-07-09 / 10-50-00/--incremental-dir=/opt/mysqlbackup/inc/2017-07-09 / 10-54-58 /

Note: 2017-07-09 10-54-58 this is the second incremental backup.

Innobackupex--apply-log / opt/mysqlbackup/full/2017-07-09 / 10-50-00 /

Note: 2017-07-09 10-50-00 this is complete.

Start recovering data:

Innobackupex--copy-back / opt/mysqlbackup/full/2017-07-09 / 10-50-00 /

Note: 2017-07-09 10-50-00 this is complete.

6. Verification.

Chown-R mysql:mysql / usr/local/mysql/data/

Mysql-uroot-p123456

Mysql > usexingjun

Mysql > select* from shengong

Mysql > exit

After checking that there are three pieces of data in the database, complete + two incremental backups are completed.

7. Recover the final data through the binary log.

Mysqlbinlog/usr/local//mysql/data/mysql-bin.000001 > / opt/mysqlbackup/binlog/ `date +% F`.sql

View the binary log information and position information for the second incremental backup:

Cat/opt/mysqlbackup/inc/2017-07-09 10-54-58/xtrabackup_info

Note: the line message in xtrabackup_info is: binlog_pos = filename'mysql-bin.000001', position '1040'

Vi/opt/mysqlbackup/binlog/2017-07-09.sql

Delete from # at # # to # at 1040, including # at 1040.

Note: the following is the 2017-07-09.sql file information.

# at 205

# 17070910:55:53 server id 1 end_log_pos 320 CRC32 0x07b5d487 Query thread_id=2exec_time=1380 error_code=0

Use`xingjun`

SETTIMESTAMP=1499612153

Insertinto shengong values (10d`)

Then save the exit.

Start recovery:

Mysql-uroot-p123456

Mysql > set sql_log_bin=0

Mysql > usexingjun

Mysql > source/opt/mysqlbackup/2017-07-09.sql

Mysql > select* from shengong

Mysql > set sql_log_bin=1

Mysql > exit

After checking that there are four pieces of data in the database, complete + two incremental backups + binary log backups are completed.

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.