目录:
0.方案描述
1.数据备份
2.数据还原
0.场景介绍
xtrabackup备份场景.jpg
1.备份
1.1完全备份
[root@node09 ~]# xtrabackup --backup --target-dir=/backup/base
[root@node09 backup]# pwd
/backup
[root@node09 backup]# ls base/ -l
total 18460
-rw-r----- 1 root root 431 Jul 25 21:22 backup-my.cnf
drwxr-x--- 2 root root 146 Jul 25 21:22 hellodb
-rw-r----- 1 root root 18874368 Jul 25 21:22 ibdata1
drwxr-x--- 2 root root 4096 Jul 25 21:22 mysql
drwxr-x--- 2 root root 4096 Jul 25 21:22 performance_schema
drwxr-x--- 2 root root 20 Jul 25 21:22 test
-rw-r----- 1 root root 22 Jul 25 21:22 xtrabackup_binlog_info
-rw-r----- 1 root root 113 Jul 25 21:22 xtrabackup_checkpoints
-rw-r----- 1 root root 468 Jul 25 21:22 xtrabackup_info
-rw-r----- 1 root root 2560 Jul 25 21:22 xtrabackup_logfile
1.2模拟数据修改
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> insert teachers (name,age) values('stone',18);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | stone | 18 | NULL |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
MariaDB [hellodb]> insert teachers (name,age) values('centos',20);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | stone | 18 | NULL |
| 6 | centos | 20 | NULL |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
1.3第一次增量备份
[root@node09 ~]# xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
[root@node09 backup]# pwd
/backup
[root@node09 backup]# ll
total 0
drwxr-x--- 6 root root 217 Jul 25 21:22 base
drwxr-x--- 6 root root 243 Jul 25 21:39 inc1
[root@node09 backup]# du -sh /backup/*
20M /backup/base
1.6M /backup/inc1
1.4再次修改数据
[root@node09 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> insert teachers (name,age) values('redhat',30);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | stone | 18 | NULL |
| 6 | centos | 20 | NULL |
| 7 | redhat | 30 | NULL |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)
1.5第二次增量备份
[root@node09 ~]# xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
[root@node09 backup]# ll
total 0
drwxr-x--- 6 root root 217 Jul 25 21:22 base
drwxr-x--- 6 root root 243 Jul 25 21:39 inc1
drwxr-x--- 6 root root 243 Jul 25 21:42 inc2
[root@node09 backup]# du -sh /backup/*
20M /backup/base
1.6M /backup/inc1
1.5M /backup/inc2
1.6数据拷贝至还原结点node10
[root@node09 ~]# scp -r /backup/ 192.168.177.139:/
[root@node09 ~]# ls /backup
base inc1 inc2
2.数据还原
2.1准备还原文件
[root@node10 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base
[root@node10 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --inc
remental-dir=/backup/inc1
[root@node10 ~]# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
2.2数据还原
[root@node10 ~]# systemctl stop mariadb
[root@node10 ~]# ll /data/mysql/
total 0
[root@node10 ~]# xtrabackup --copy-back --target-dir=/backup/base
[root@node10 ~]# ll /data/mysql/
total 40980
drwxr-x--- 2 root root 146 Jul 18 20:43 hellodb
-rw-r----- 1 root root 18874368 Jul 18 20:43 ibdata1
-rw-r----- 1 root root 5242880 Jul 18 20:43 ib_logfile0
-rw-r----- 1 root root 5242880 Jul 18 20:43 ib_logfile1
-rw-r----- 1 root root 12582912 Jul 18 20:43 ibtmp1
drwxr-x--- 2 root root 4096 Jul 18 20:43 mysql
drwxr-x--- 2 root root 4096 Jul 18 20:43 performance_schema
drwxr-x--- 2 root root 20 Jul 18 20:43 test
-rw-r----- 1 root root 35 Jul 18 20:43 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 509 Jul 18 20:43 xtrabackup_info
-rw-r----- 1 root root 1 Jul 18 20:43 xtrabackup_master_key_id
[root@node10 ~]# du -sh /data/mysql/
42M /data/mysql/
[root@node10 ~]# chown -R mysql.mysql /data/mysql
[root@node10 ~]# systemctl start mariadb
2.3验证还原结果
[root@node10 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | stone | 18 | NULL |
| 6 | centos | 20 | NULL |
| 7 | redhat | 30 | NULL |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)
网友评论