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

1.1 full practice of full backup and recovery

#

1. Install the Xtrabackup backup command

Wget-O / etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo

Yum-y install perl perl-devel libaio libaio-develperl-Time-HiRes perl-DBD-MySQL

Wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

Yum-y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

Innobackupex *

2. Create a logs directory and configure permissions

Mkdir / application/mysql/logs-p

Chown-R / mysql.mysql / application/mysql/logs

3. Configuration / etc/my.cnf file

[client]

User=root

Password=oldboy123

[mysqld]

Basedir= / application/mysql/

Datadir= / application/mysql/data/

# binlog#

Log_bin = / application/mysql/logs/oldboy-bin

Expire_logs_days = 7

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]

Log-error = / application/mysql/logs/oldboy.err

Reboot takes effect after configuration is completed:

[root@db02 data] # / etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL... SUCCESS!

4. All be backed up

Mkdir / server/backup-p

Innobackupex-defaults-file=/etc/my.cnf--user=root-password=oldboy123--socket=/application/mysql-5.6.34/tmp/mysql.sock-no-timestamp/server/backup/full

5. Preparation before data recovery (merge xtrabackup_log_file and backup physical files)

Innobackupex-apply-log-use-memory=32M/server/backup/full/

Stop the library:

/ etc/init.d/mysqld stop

Lsof-I: 3306

Destroy the data:

Cd / application/mysql/

Mv data / opt/

Restore:

Cp-a / server/backup/full/ / application/mysql/data

Chown-R mysql.mysql / application/mysql/data

Start:

/ etc/init.d/mysqld start

Mysql-e "select * from oldboy.test"

The data was recovered successfully.

1.2 incremental backup and recovery:

Environmental preparation:

Du-sh / application/mysql/data/

Free-m

Cd / server/backup/

\ rm-rf *

1. Full backup (0: 00 on Monday)

Innobackupex-defaults-file=/etc/my.cnf--user=root-password=oldboy123--socket=/application/mysql-5.6.34/tmp/mysql.sock-no-timestamp/server/backup/base_full

2. The first incremental backup (0: 00 on Tuesday)

a. Prepare for

Use oldboy

Mysql > insert into test values (8) insert two data pieces

Query OK, 1 row affected (0.00 sec)

Mysql > insert into test values (9)

Query OK, 1 row affected (0.00 sec)

Select * from test

b. Increment

Innobackupex-defaults-file=/etc/my.cnf--user=root-password=oldboy123--socket=/application/mysql-5.6.34/tmp/mysql.sock-no-timestamp--incremental-basedir=/server/backup/base_full-incremental/server/backup/one_inc

2. The second increment

Mysql > use oldboy

Mysql > insert into test values (10)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into test values (11 minutes twohands inc2')

Query OK, 1 row affected (0.00 sec)

Innobackupex-defaults-file=/etc/my.cnf--user=root-password=oldboy123--socket=/application/mysql-5.6.34/tmp/mysql.sock-no-timestamp-parallel=3--incremental-basedir=/server/backup/one_inc/-incremental/server/backup/two_inc

Incremental recovery:

Innobackupex-apply-log-use-memory=32M--redo-only / server/backup/base_full/

Innobackupex-apply-log-use-memory=32M--redo-only-incremental-dir=/server/backup/one_inc / server/backup/base_full/

Innobackupex-apply-log-use-memory=32M--incremental-dir=/server/backup/two_inc / server/backup/base_full/ official data document

Recovery process:

[root@db02 backup] # / etc/init.d/mysqld stop

Shutting down MySQL.. SUCCESS!

[root@db02 backup] # cd / application/mysql

[root@db02 mysql] # mv data / tmp/

[root@db02 mysql] # / bin/cp-a/server/backup/base_full data

[root@db02 mysql] # chown-R mysql.mysql data

[root@db02 mysql] # / etc/init.d/mysqld start

Starting MySQL. SUCCESS!

[root@db02 mysql] # mysql-e "select * fromoldboy.test;"

+-+ +

| | id | name |

+-+ +

| | 1 | oldboy |

| | 2 | oldgirl |

| | 3 | inca |

| | 4 | zuma |

| | 5 | kaka |

| | 6 | bingbing |

| | 7 | xiaoting |

| | 8 | outman |

| | 9 | outgirl |

| | 10 | two_inc1 |

| | 11 | two_inc2 |

+-+ +

From this we can see that the incremental recovery data has been restored successfully!

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.