Xtrabackup of MySQL Backup tool

1. Backup Xtrabackup tool installation (only data can be backed up and table structure cannot be backed up)
http://tongcheng.blog.51cto.com/6214144/1562538

2.xtrabackup Basic Parameters

--defaults-file--The default configuration file for the database

--target-dir--Directory where backup files are stored

--backup--Back up to the--target-dir directory

--prepare-Preparation for backup files before recovery

--use-memory-How much memory is allocated for recovery

--throttle--Limit the number of IO used

--databases--Backing up the specified database

--compress--Compress backup

--incremental-basedir--The directory of incremental backups

3. Full database backup

[Email protected] ~]# mysql-u root-p-E "select * from Tong.t1"--view data in the database
Enter Password:
+------+
| Q |
+------+
| 9 |
| 8 |
+------+

[Email protected] ~]# xtrabackup--default-file=/etc/my.cnf--backup--datadir=/usr/local/mysql-5.6.22/data-- Target-dir=/opt/all/--Back up all libraries
[Email protected] ~]# ll/opt/all/
Total 12300
-rw-r-----. 1 root root 12582912 Apr 15:35 ibdata1
DRWX------. 2 root root 4096 Apr 15:35 Tong
-rw-r-----. 1 root root Apr 15:35 xtrabackup_checkpoints
-rw-r-----. 1 root root 2560 Apr 15:35 xtrabackup_logfile
[Email protected] ~]#

4. Recover after deleting data
[Email protected] ~]# mysql-u root-p-E "delete from Tong.t1"--delete data
Enter Password:
[Email protected] ~]# mysql-u root-p-E "select * from Tong.t1"--see if the data still exists
Enter Password:
[Email protected] ~]# Cd/usr/local/mysql-5.6.22/data/tong
[Email protected] tong]# cp-a/opt/all/tong/*. --Overwrite the backed up data with the current data file
Cp:overwrite './t1.ibd '? Y
Cp:overwrite './t.ibd '? Y
[[email protected] tong]# chown Mysql:mysql *--Modify Permissions
[email protected] tong]# LL
Total 220
-rw-r--r--. 1 mysql mysql Apr 14:37 db.opt
-rw-r--r--. 1 mysql mysql 8554 Apr 14:37 t1.frm
-rw-r-----. 1 mysql mysql 98304 Apr 15:35 t1.ibd
-rw-r--r--. 1 mysql mysql 8554 Apr 14:37 t.frm
-rw-r-----. 1 mysql mysql 98304 Apr 15:35 t.ibd
[Email protected] tong]#/etc/init.d/mysqld Restart--Restart service
Shutting down MySQL. success!
Starting MySQL. success!
[Email protected] data]# mysql-u root-p-E "select * from Tong.t1"
Enter Password:
+------+
| Q |
+------+
| 9 |
|          8 | --the data is back.
+------+
[Email protected] data]#

5. Incremental backup

[Email protected] data]# mysql-u root-p-E "insert into TONG.T1 values (7), (6), (5)"
Enter Password:
[Email protected] data]# mysql-u root-p-E "select * from Tong.t1"
Enter Password:
+------+
| Q |
+------+
| 9 |
| 8 |
| 7 |
| 6 |
| 5 |
+------+
[Email protected] tong]# xtrabackup--default-file=/etc/my.cnf--backup--datadir=/usr/local/mysql-5.6.22/data-- target-dir=/opt/incr/--incremental-basedir=/opt/all/
[Email protected] tong]# ll/opt/incr/
Total 176
-rw-r-----. 1 root root 163840 Apr 15:47 Ibdata1.delta
-rw-r-----. 1 root root (APR) 15:47 Ibdata1.meta
DRWX------. 2 root root 4096 Apr 15:47 Tong
-rw-r-----. 1 root root, Apr 15:47 xtrabackup_checkpoints
-rw-r-----. 1 root root 2560 Apr 15:47 xtrabackup_logfile
[Email protected] tong]#

6. Recover data after deleting data

[Email protected] data]# mysql-u root-p-E "delete from Tong.t1"
Enter Password:
[Email protected] data]# mysql-u root-p-E "select * from Tong.t1"
Enter Password:
[Email protected] data]# xtrabackup--default-file=/etc/my.cnf--prepare--target-dir=/opt/all/

[Email protected] data]# xtrabackup--default-file=/etc/my.cnf--prepare--target-dir=/opt/all/--incremental-dir=/ opt/incr/

[Email protected] data]# cd/opt/all/tong/
[Email protected] tong]# Cp-a */usr/local/mysql-5.6.22/data/tong
Cp:overwrite '/usr/local/mysql-5.6.22/data/tong/t1.ibd '? Y
Cp:overwrite '/usr/local/mysql-5.6.22/data/tong/t.ibd '? Y
[Email protected] tong]# Cd/usr/local/mysql-5.6.22/data/tong
[Email protected] tong]# chown Mysql:mysql *
[Email protected] tong]#/etc/init.d/mysqld restart
Shutting down MySQL. success!
Starting MySQL. success!
[Email protected] tong]# mysql-u root-p-E "select * from Tong.t1"
Enter Password:
+------+
| Q |
+------+
| 9 |
| 8 |
| 7 |
| 6 |
| 5 |
+------+
[Email protected] tong]#

This article is from the "Days Together" blog, please be sure to keep this source http://tongcheng.blog.51cto.com/6214144/1638022

Xtrabackup of MySQL Backup tool