Practice of full backup and incremental backup
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!
