一、备份工作内容
1.备份设计策略
- 备份周期
- 备份工具
- 备份方法(全备、增量)
- 备份位置
|
企业常见备份策略
1. MDP定时全备+binlog备份(每天) |
2.定期的恢复演练
3.快速故障恢复
4.数据迁移
二、备份工具
1.mysqldump(MDP)
- mysqldump是mysql自带的逻辑备份工具；主要备份的是SQL语句(CREATE DATABASE,CREATE TABLE;Insert语句)；
- 特点
- 优点：备份数据可读性较强，压缩比较高
- 缺点：备份性能相对较差
2.xtrabackup(PBK、XBK)
- xtrabackup是Percona公司推出一款第三方物理备份工具；
- 特点
- 优点：备份性能相对较好
- 缺点：可读性弱，压缩比较低
三、mysqldump应用
| mysqldump是MySQL自带的一个客户端备份工具，可以实现远程备份； |
1.连接参数
-
-u用户 指定用户
-
-p密码 指定密码
-
-S socket文件 指定登录所用的socket文件
-
-h IP地址 指定登录数据库主机的IP地址
-
-P 端口号 指定目标主机的数据库登录端口号
-
2.备份专用参数
-
-A(--all-database) #实现mysql全库备份
[root@db01 ~]# mysqldump -uroot -p123 -A >/data/mysql/3306/backup/MDP/All_bak_`date +%F`.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@db01 ~]# ls /data/mysql/3306/backup/MDP
All_bak_2019-08-30.sql- -B 库名1 库名2 库名3 ... #单库或多库备份
[root@db01 ~]# mysqldump -uroot -p123 -B Linux test >/data/mysql/3306/backup/MDP/Linux\&test_bak_`date +%F`.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@db01 ~]# ll /data/mysql/3306/backup/MDP/
total 98252
-rw-r--r-- 1 root root 50825098 Aug 30 19:08 All_bak_2019-08-30.sql
-rw-r--r-- 1 root root 49782113 Aug 30 19:12 Linux&test_bak_2019-08-30.sql
[root@db01 ~]# mysql -uroot -p123 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| Linux |
| binlog |
| mysql |
| performance_schema |
| sys |
| test |
| world |
+--------------------+- 单表或多表备份
| mysqldump -u用户名 -p密码 库名 表名1 表名2 表名3 ... |
[root@db01 ~]# mysqldump -uroot -p123 world city country >/data/mysql/3306/backup/MDP/world.city\&country.sql 2>/dev/null
[root@db01 ~]# ll /data/mysql/3306/backup/MDP/
total 98464
-rw-r--r-- 1 root root 50825098 Aug 30 19:08 All_bak_2019-08-30.sql
-rw-r--r-- 1 root root 49782113 Aug 30 19:12 Linux&test_bak_2019-08-30.sql
-rw-r--r-- 1 root root 216900 Aug 30 19:16 world.city&country.sql3.特殊功能参数
- -R 备份MySQL存储过程及函数
- -E 备份MySQL事件
- -triggers 备份MySQL触发器
- -F 备份时立即刷新二进制日志文件(刷新日志文件的个数与数据库个数相关)
- --master-data=2 以注释的方式自动记录binlog位置点到备份文件(22行)中；(该参数在备份时会自动锁表)
|
该参数配合 --single-transaction，可实现InnoDB表的热备 备份的操作不会记录到二进制日志文件中 |
- --single-transaction 备份InnoDB表时，实现快照备份；(备份时不会阻塞其他事务操作)
- --max-allowed-packet=容量 容量 设定最多会话级别可接受数据量(服务端默认值4M，当前最大版本为1G)
#标准全备
[root@db01 ~]# mysqldump -uroot -p123 -A -R -E --triggers --master-data=2 --single-transaction >/data/mysql/3306/backup/MDP/Full_backup_`date +%F_%T`.sql 2>/dev/null
[root@db01 ~]# ls /data/mysql/3306/backup/MDP/
All_bak_2019-08-30.sql Linux&test_bak_2019-08-30.sql
Full_backup_2019-08-30_19:20:45.sql world.city&country.sql4.备份恢复模拟演练
step0 模拟原始数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| mysql |
| performance_schema |
| sys |
| test |
| world |
+--------------------+
7 rows in set (0.00 sec)
mysql> create database Linux;
Query OK, 1 row affected (0.00 sec)
mysql> use Linux;
Database changed
mysql> create table ver(id tinyint primary key auto_increment,version varchar(10) not null default '7',factory varchar(10))charset utf8mb4;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into ver(version,factory) values('Cent OS 6','redhat'),('SUSE OS','Linux AG'),('Solaris','Oracle');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from ver;
+----+-----------+----------+
| id | version | factory |
+----+-----------+----------+
| 1 | Cent OS 6 | redhat |
| 2 | SUSE OS | Linux AG |
| 3 | Solaris | Oracle |
+----+-----------+----------+
3 rows in set (0.00 sec)step1 模拟全备
[root@db01 ~]# mysqldump -uroot -p123 -A -R -E --triggers --master-data=2 --single-transaction >/data/mysql/3306/backup/MDP/Full_bak_`date +%F`.sql 2>/dev/null
[root@db01 ~]# ll /data/mysql/3306/backup/MDP/
total 197736
-rw-r--r-- 1 root root 50825098 Aug 30 19:08 All_bak_2019-08-30.sql
-rw-r--r-- 1 root root 50825714 Aug 30 19:20 Full_backup_2019-08-30_19:20:45.sql
-rw-r--r-- 1 root root 50826659 Aug 30 19:32 Full_bak_2019-08-30.sql
-rw-r--r-- 1 root root 49782113 Aug 30 19:12 Linux&test_bak_2019-08-30.sql
-rw-r--r-- 1 root root 216900 Aug 30 19:16 world.city&country.sqlstep2 模拟数据变化
mysql> insert into ver(version,factory) values('Cent OS 7','redhat'),('RHEL','redhat'),('Ubuntu','Debian');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from ver;
+----+-----------+----------+
| id | version | factory |
+----+-----------+----------+
| 1 | Cent OS 6 | redhat |
| 2 | SUSE OS | Linux AG |
| 3 | Solaris | Oracle |
| 4 | Cent OS 7 | redhat |
| 5 | RHEL | redhat |
| 6 | Ubuntu | Debian |
+----+-----------+----------+
6 rows in set (0.00 sec)step3 模拟数据库故障
mysql> drop database Linux;
Query OK, 1 row affected (0.00 sec)
mysql> drop database world;
Query OK, 4 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)step4 模拟数据恢复(临时关闭恢复窗口二进制日志记录set sql_log_bin=0)
[root@db01 ~]# head -n 22 /data/mysql/3306/backup/MDP/Full_bak_2019-08-30.sql | tail -n 1
-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000003', MASTER_LOG_POS=588;
mysql> show binlog events in 'bin-log.000003';
+----------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| bin-log.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| bin-log.000003 | 123 | Previous_gtids | 6 | 154 | |
| bin-log.000003 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000003 | 219 | Query | 6 | 316 | create database Linux |
| bin-log.000003 | 316 | Anonymous_Gtid | 6 | 381 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000003 | 381 | Query | 6 | 588 | use `Linux`; create table ver(id tinyint primary key auto_increment,version varchar(10) not null default '7',factory varchar(10))charset utf8mb4 |
| bin-log.000003 | 588 | Anonymous_Gtid | 6 | 653 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000003 | 653 | Query | 6 | 726 | BEGIN |
| bin-log.000003 | 726 | Table_map | 6 | 779 | table_id: 108 (Linux.ver) |
| bin-log.000003 | 779 | Write_rows | 6 | 869 | table_id: 108 flags: STMT_END_F |
| bin-log.000003 | 869 | Table_map | 6 | 922 | table_id: 110 (Linux.ver) |
| bin-log.000003 | 922 | Write_rows | 6 | 1006 | table_id: 110 flags: STMT_END_F |
| bin-log.000003 | 1006 | Xid | 6 | 1037 | COMMIT /* xid=12 */ |
| bin-log.000003 | 1037 | Anonymous_Gtid | 6 | 1102 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000003 | 1102 | Query | 6 | 1197 | drop database Linux |
| bin-log.000003 | 1197 | Anonymous_Gtid | 6 | 1262 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000003 | 1262 | Query | 6 | 1357 | drop database world |
+----------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
17 rows in set (0.00 sec)
[root@db01 /data/mysql/3306/backup/MDP]# mysqlbinlog --start-position=588 --stop-position=1102 /data/mysql/3306/log/bin-log.000003 >>Full_bak_2019-08-30.sql
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /data/mysql/3306/backup/MDP/Full_bak_2019-08-30.sql
......step5 结果验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Linux |
| binlog |
| mysql |
| performance_schema |
| sys |
| test |
| world |
+--------------------+
8 rows in set (0.00 sec)
mysql> select * from ver;
+----+-----------+----------+
| id | version | factory |
+----+-----------+----------+
| 1 | Cent OS 6 | redhat |
| 2 | SUSE OS | Linux AG |
| 3 | Solaris | Oracle |
| 4 | Cent OS 7 | redhat |
| 5 | RHEL | redhat |
| 6 | Ubuntu | Debian |
+----+-----------+----------+
6 rows in set (0.00 sec)四、xtrabackup应用
| xtrabackup是Percona公司推出一款第三方MySQL数据库物理备份工具；工作模式类似于复制数据文件。对于InnoDB表，可以实现热备功能。 |
1.xtrabackup安装流程
step1 安装依赖软件包
|
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev |
[root@db01 ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
--2019-08-30 21:12:45-- http://mirrors.aliyun.com/repo/epel-7.repo
Resolving mirrors.aliyun.com (mirrors.aliyun.com)... 219.238.20.101, 219.238.20.88, 219.238.20.83, ...
Connecting to mirrors.aliyun.com (mirrors.aliyun.com)|219.238.20.101|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 664 [application/octet-stream]
Saving to: ‘/etc/yum.repos.d/epel.repo’
100%[=====================================================================>] 664 --.-K/s in 0s
2019-08-30 21:12:46 (175 MB/s) - ‘/etc/yum.repos.d/epel.repo’ saved [664/664]
[root@db01 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
.....
Updated:
perl.x86_64 4:5.16.3-294.el7_6
Dependency Updated:
perl-libs.x86_64 4:5.16.3-294.el7_6
Complete!step2 下载软件安装包
xtrabackup官方下载地址
| wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm |
[root@db01 ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
--2019-08-30 21:29:33-- https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
Resolving www.percona.com (www.percona.com)... 74.121.199.234
Connecting to www.percona.com (www.percona.com)|74.121.199.234|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 7829340 (7.5M) [application/x-redhat-package-manager]
Saving to: ‘percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm’
85% [===========================================================> ] 6,726,709 14.6KB/s in 9m 6s
2019-08-30 21:38:41 (12.0 KB/s) - Connection closed at byte 6726709. Retrying.
--2019-08-30 21:38:42-- (try: 2) https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
Connecting to www.percona.com (www.percona.com)|74.121.199.234|:443... connected.
HTTP request sent, awaiting response... 206 Partial Content
Length: 7829340 (7.5M), 1102631 (1.1M) remaining [application/x-redhat-package-manager]
Saving to: ‘percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm’
100%[++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=========>] 7,829,340 12.2KB/s in 81s
2019-08-30 21:40:05 (13.3 KB/s) - ‘percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm’ saved [7829340/7829340]
[root@db01 ~]# ll
total 7652
-rw-------. 1 root root 1753 May 11 15:54 anaconda-ks.cfg
-rw-r--r-- 1 root root 7829340 Jun 14 2018 percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpmstep3 安装软件包
| yum install -y percona-xtrabackup* |
[root@db01 ~]# yum install -y percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
Loaded plugins: fastestmirror
Examining percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm: percona-xtrabackup-24-2.4.12-1.el7.x86_64
Marking percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm to be installed
......
Installed:
percona-xtrabackup-24.x86_64 0:2.4.12-1.el7
Dependency Installed:
perl-Digest.noarch 0:1.17-245.el7 perl-Digest-MD5.x86_64 0:2.52-3.el7
Complete!step4 配置软件
编辑MySQL配置文件
|
#my.cnf [client] socket=/tmp/mysql.sock |
[root@db01 ~]# vim /data/mysql/3306/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/log/error.log
log_bin=/data/mysql/3306/log/bin-log
binlog_format=row
slow_query_log=ON
slow_query_log_file=/data/mysql/3306/log/slow.log
long_query_time=0.1
log_queries_not_using_indexes=ON
innodb_flush_log_at_trx_commit=1
innodb_buffer_pool_size=1G
innodb_flush_method=O_DIRECT
autocommit=0
transaction_isolation=READ-COMMITTED
[mysql]
socket=/tmp/mysql.sock
[client]
socket=/tmp/mysql.sock
[root@db01 ~]# systemctl restart mysqld2.xtrabackup全备应用
| innobackupex --user=用户名 --password=密码 备份路径 |
| 可选参数 --no-timestamp 备份时不创建时间戳文件夹 |
[root@db01 ~]# innobackupex --defaults-file=/data/mysql/3306/my.cnf --user=root --password=123 /data/mysql/3306/backup/XBK/Full/cbk_full
......
xtrabackup: Transaction log of lsn (1574278598) to (1574278607) was copied.
190830 21:54:52 completed OK!
[root@db01 ~]# ll /data/mysql/3306/backup/XBK/Full/cbk_full/
total 0
drwxr-x--- 9 root root 275 Aug 30 21:54 2019-08-30_21-54-49
[root@db01 ~]# ll /data/mysql/3306/backup/XBK/Full/cbk_full/2019-08-30_21-54-49/
total 77936
-rw-r----- 1 root root 487 Aug 30 21:54 backup-my.cnf
drwxr-x--- 2 root root 114 Aug 30 21:54 binlog
-rw-r----- 1 root root 62165 Aug 30 21:54 ib_buffer_pool
-rw-r----- 1 root root 79691776 Aug 30 21:54 ibdata1
drwxr-x--- 2 root root 50 Aug 30 21:54 Linux
drwxr-x--- 2 root root 4096 Aug 30 21:54 mysql
drwxr-x--- 2 root root 8192 Aug 30 21:54 performance_schema
drwxr-x--- 2 root root 8192 Aug 30 21:54 sys
drwxr-x--- 2 root root 68 Aug 30 21:54 test
drwxr-x--- 2 root root 182 Aug 30 21:54 world
-rw-r----- 1 root root 19 Aug 30 21:54 xtrabackup_binlog_info
-rw-r----- 1 root root 119 Aug 30 21:54 xtrabackup_checkpoints
-rw-r----- 1 root root 540 Aug 30 21:54 xtrabackup_info
-rw-r----- 1 root root 2560 Aug 30 21:54 xtrabackup_logfile- xtrabackup_binlog_info :记录备份时二进制日志文件的position
[root@db01 ~]# cat /data/mysql/3306/backup/XBK/Full/cbk_full/2019-08-30_21-54-49/xtrabackup_binlog_info
bin-log.000004 154- xtrabackup_checkpoints :记录备份类型及相关LSN号
[root@db01 ~]# cat /data/mysql/3306/backup/XBK/Full/cbk_full/2019-08-30_21-54-49/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1574278598
last_lsn = 1574278607
compact = 0
recover_binlog_info = 0|
backup_type = full-backuped：备份类型-全备 from_lsn:LSN起始号码，全备的from_lsn一定为0； to_lsn:数据脏页写回磁盘的LSN号码； last_lsn:备份结束时redo事务日志的LSN号码 默认情况下，last_lsn与to_lsn差值为9； |
- xtrabackup_info:备份信息(记录备份参数及相关工具)
[root@db01 ~]# cat /data/mysql/3306/backup/XBK/Full/cbk_full/2019-08-30_21-54-49/xtrabackup_info
uuid = bd82855a-cb2d-11e9-aa1b-000c2938d306
name =
tool_name = innobackupex
tool_command = --defaults-file=/data/mysql/3306/my.cnf --user=root --password=... /data/mysql/3306/backup/XBK/Full/cbk_full
tool_version = 2.4.12
ibbackup_version = 2.4.12
server_version = 5.7.26-log
start_time = 2019-08-30 21:54:49
end_time = 2019-08-30 21:54:52
lock_time = 0
binlog_pos = filename 'bin-log.000004', position '154'
innodb_from_lsn = 0
innodb_to_lsn = 1574278598
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N- xtrabackup_logfile:备份过程内数据变化日志
[root@db01 ~]# file /data/mysql/3306/backup/XBK/Full/cbk_full/2019-08-30_21-54-49/xtrabackup_logfile
/data/mysql/3306/backup/XBK/Full/cbk_full/2019-08-30_21-54-49/xtrabackup_logfile: data3.全备恢复
step0 模拟原始数据
mysql> create database xbk;
Query OK, 1 row affected (0.00 sec)
mysql> use xbk;
Database changed
mysql> create table t1 (id tinyint primary key auto_increment, k1 int)charset utf8mb4;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1(k1) values(111),(222),(333);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+----+------+
| id | k1 |
+----+------+
| 1 | 111 |
| 2 | 222 |
| 3 | 333 |
+----+------+
3 rows in set (0.00 sec)step1 全备
[root@db01 ~]# innobackupex --defaults-file=/data/mysql/3306/my.cnf --user=root --password=123 --no-timestamp /data/mysql/3306/backup/XBK/Full_bak
......
xtrabackup: Transaction log of lsn (1574291095) to (1574291104) was copied.
190830 22:15:21 completed OK!
[root@db01 ~]# ll /data/mysql/3306/backup/XBK/Full_bak/
total 77936
-rw-r----- 1 root root 487 Aug 30 22:15 backup-my.cnf
-rw-r----- 1 root root 62165 Aug 30 22:15 ib_buffer_pool
-rw-r----- 1 root root 79691776 Aug 30 22:15 ibdata1
drwxr-x--- 2 root root 50 Aug 30 22:15 Linux
drwxr-x--- 2 root root 4096 Aug 30 22:15 mysql
drwxr-x--- 2 root root 8192 Aug 30 22:15 performance_schema
drwxr-x--- 2 root root 8192 Aug 30 22:15 sys
drwxr-x--- 2 root root 68 Aug 30 22:15 test
drwxr-x--- 2 root root 182 Aug 30 22:15 world
drwxr-x--- 2 root root 48 Aug 30 22:15 xbk
-rw-r----- 1 root root 19 Aug 30 22:15 xtrabackup_binlog_info
-rw-r----- 1 root root 119 Aug 30 22:15 xtrabackup_checkpoints
-rw-r----- 1 root root 550 Aug 30 22:15 xtrabackup_info
-rw-r----- 1 root root 2560 Aug 30 22:15 xtrabackup_logfilestep2 模拟故障
[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# cd /data/mysql/3306/data/
[root@db01 /data/mysql/3306/data]# ls
auto.cnf ibdata1 ib_logfile1 mysql sys world
ib_buffer_pool ib_logfile0 Linux performance_schema test xbk
[root@db01 /data/mysql/3306/data]# rm -rf *
[root@db01 /data/mysql/3306/data]# ll
total 0
[root@db01 /data/mysql/3306/data]# systemctl start mysqld
[root@db01 /data/mysql/3306/data]# grep '\[ERROR\]' /data/mysql/3306/log/error.log |tail -4
2019-08-30T14:17:53.195634Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2019-08-30T14:17:53.200295Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2019-08-30T14:17:53.200307Z 0 [ERROR] Fatal error: Failed to initialize ACL/grant/time zones structures or failed to remove temporary table files.
2019-08-30T14:17:53.200346Z 0 [ERROR] Abortingstep3 备份的处理(保证数据LSN与redo日志LSN一致)
工作原理：用工具redo的前滚与undo的回滚
- --apply-log 备份路径
| innobackupex --apply-log 备份路径 |
[root@db01 /data/mysql/3306/data]# innobackupex --apply-log ../backup/XBK/Full_bak/
......
190830 22:21:21 completed OK!step4 恢复数据
[root@db01 /data/mysql/3306/data]# \cp -a ../backup/XBK/Full_bak/* ./
[root@db01 /data/mysql/3306/data]# ll
total 196728
-rw-r----- 1 mysql mysql 56 Aug 30 22:17 auto.cnf
-rw-r----- 1 root root 487 Aug 30 22:15 backup-my.cnf
-rw-r----- 1 root root 62165 Aug 30 22:15 ib_buffer_pool
-rw-r----- 1 root root 79691776 Aug 30 22:21 ibdata1
-rw-r----- 1 root root 50331648 Aug 30 22:21 ib_logfile0
-rw-r----- 1 root root 50331648 Aug 30 22:21 ib_logfile1
-rw-r----- 1 root root 12582912 Aug 30 22:21 ibtmp1
drwxr-x--- 2 root root 50 Aug 30 22:15 Linux
drwxr-x--- 2 root root 4096 Aug 30 22:15 mysql
drwxr-x--- 2 root root 8192 Aug 30 22:15 performance_schema
drwxr-x--- 2 root root 8192 Aug 30 22:15 sys
drwxr-x--- 2 root root 68 Aug 30 22:15 test
drwxr-x--- 2 root root 182 Aug 30 22:15 world
drwxr-x--- 2 root root 48 Aug 30 22:15 xbk
-rw-r----- 1 root root 19 Aug 30 22:15 xtrabackup_binlog_info
-rw-r--r-- 1 root root 20 Aug 30 22:21 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 119 Aug 30 22:21 xtrabackup_checkpoints
-rw-r----- 1 root root 550 Aug 30 22:15 xtrabackup_info
-rw-r----- 1 root root 8388608 Aug 30 22:21 xtrabackup_logfile
-rw-r--r-- 1 root root 1 Aug 30 22:21 xtrabackup_master_key_idstep5 修改原数据文件权限
[root@db01 /data/mysql/3306/data]# chown -R mysql. *
[root@db01 /data/mysql/3306/data]# ll
total 196728
-rw-r----- 1 mysql mysql 56 Aug 30 22:17 auto.cnf
-rw-r----- 1 mysql mysql 487 Aug 30 22:15 backup-my.cnf
-rw-r----- 1 mysql mysql 62165 Aug 30 22:15 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Aug 30 22:21 ibdata1
-rw-r----- 1 mysql mysql 50331648 Aug 30 22:21 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 30 22:21 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Aug 30 22:21 ibtmp1
drwxr-x--- 2 mysql mysql 50 Aug 30 22:15 Linux
drwxr-x--- 2 mysql mysql 4096 Aug 30 22:15 mysql
drwxr-x--- 2 mysql mysql 8192 Aug 30 22:15 performance_schema
drwxr-x--- 2 mysql mysql 8192 Aug 30 22:15 sys
drwxr-x--- 2 mysql mysql 68 Aug 30 22:15 test
drwxr-x--- 2 mysql mysql 182 Aug 30 22:15 world
drwxr-x--- 2 mysql mysql 48 Aug 30 22:15 xbk
-rw-r----- 1 mysql mysql 19 Aug 30 22:15 xtrabackup_binlog_info
-rw-r--r-- 1 mysql mysql 20 Aug 30 22:21 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 119 Aug 30 22:21 xtrabackup_checkpoints
-rw-r----- 1 mysql mysql 550 Aug 30 22:15 xtrabackup_info
-rw-r----- 1 mysql mysql 8388608 Aug 30 22:21 xtrabackup_logfile
-rw-r--r-- 1 mysql mysql 1 Aug 30 22:21 xtrabackup_master_key_idstep6 启动数据库
[root@db01 /data/mysql/3306/data]# systemctl start mysqld
[root@db01 /data/mysql/3306/data]# ls /tmp/
backup_dir mysql.sock percona-version-check vmware-root_6122-994292192
backup.sql mysql.sock.lock t100w.sql
[root@db01 /data/mysql/3306/data]# ps -ef | grep mysqld
mysql 27233 1 0 16:35 ? 00:00:08 /application/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf
mysql 31457 1 0 22:29 ? 00:00:00 /application/mysql/bin/mysqld --defaults-file=/data/mysql/3306/my.cnf
root 31505 31136 0 22:30 pts/1 00:00:00 grep --color=auto mysqldstep7 验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Linux |
| mysql |
| performance_schema |
| sys |
| test |
| world |
| xbk |
+--------------------+
8 rows in set (0.00 sec)
mysql> use xbk;
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
mysql> select * from t1;
+----+------+
| id | k1 |
+----+------+
| 1 | 111 |
| 2 | 222 |
| 3 | 333 |
+----+------+
3 rows in set (0.00 sec)4.xtrabackup增量备份应用与数据恢复
| innobackupex --user=用户名 --password=密码 --incremental --incremental-basedir=备份基路径 备份路径 #xtrabackup增量备份 |
| 增量备份无法单独完成数据恢复，必须依靠全备才能实现数据恢复。 |
|
innobackupex --apply-log --redo-only 全备路径 #处理全备 innobackupex --apply-log --redo-only --incremental-dir=增量路径 全备路径 #处理非末次增量备份 innobackupex --apply-log 备份路径 #处理末次增量备份 innobackupex --copy-back 全备路径 |
[root@db01 ~]# innobackupex --help
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
Open source backup tool for InnoDB and XtraDB
......
-?, --help This option displays a help screen and exits.
--apply-log Prepare a backup in BACKUP-DIR by applying the
transaction log file named "xtrabackup_logfile" located
in the same directory. Also, create new transaction logs.
The InnoDB configuration is read from the file
"backup-my.cnf".
--redo-only This option should be used when preparing the base full
backup and when merging all incrementals except the last
one. This forces xtrabackup to skip the "rollback" phase
and do a "redo" only. This is necessary if the backup
will have incremental changes applied to it later. See
the xtrabackup documentation for details.
--copy-back Copy all the files in a previously made backup from the
backup directory to their original locations.
--incremental This option tells xtrabackup to create an incremental
backup, rather than a full one. It is passed to the
xtrabackup child process. When this option is specified,
either --incremental-lsn or --incremental-basedir can
also be given. If neither option is given, option
--incremental-basedir is passed to xtrabackup by default,
set to the first timestamped backup directory in the
backup base directory.
--no-timestamp This option prevents creation of a time-stamped
subdirectory of the BACKUP-ROOT-DIR given on the command
line. When it is specified, the backup is done in
BACKUP-ROOT-DIR instead.
-u, --user=name This option specifies the MySQL username used when
connecting to the server, if that's not the current user.
The option accepts a string argument. See mysql --help
for details.
-H, --host=name This option specifies the host to use when connecting to
the database server with TCP/IP. The option accepts a
string argument. See mysql --help for details.
-P, --port=# This option specifies the port to use when connecting to
the database server with TCP/IP. The option accepts a
string argument. See mysql --help for details.
-p, --password=name This option specifies the password to use when connecting
to the database. It accepts a string argument. See mysql
--help for details.
-S, --socket=name This option specifies the socket to use when connecting
to the local database server with a UNIX domain socket.
The option accepts a string argument. See mysql --help
for details.
--incremental-basedir=name
This option specifies the directory containing the full
backup that is the base dataset for the incremental
backup. The option accepts a string argument. It is used
with the --incremental option.step0 模拟原始数据
mysql> use xbk;
Database changed
mysql> show tables;
+---------------+
| Tables_in_xbk |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
mysql> create table t2(id int primary key auto_increment,num int not null default 0, description varchar(100) not null default 'NA' )engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t2(num) values(111),(222),(333);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2;
+----+-----+-------------+
| id | num | description |
+----+-----+-------------+
| 1 | 111 | NA |
| 2 | 222 | NA |
| 3 | 333 | NA |
+----+-----+-------------+
3 rows in set (0.00 sec)step1 模拟全备
[root@db01 /data/mysql/3306/backup/XBK]# innobackupex --defaults-file=/data/mysql/3306/my.cnf --user=root --password=123 --no-timestamp ./Full/bak_`date +%F`
......
xtrabackup: Transaction log of lsn (1574344841) to (1574344850) was copied.
190902 09:33:41 completed OK!step2 模拟数据增量
mysql> insert into t2(num) values(121),(122),(123);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2;
+----+-----+-------------+
| id | num | description |
+----+-----+-------------+
| 1 | 111 | NA |
| 2 | 222 | NA |
| 3 | 333 | NA |
| 4 | 121 | NA |
| 5 | 122 | NA |
| 6 | 123 | NA |
+----+-----+-------------+
6 rows in set (0.00 sec)step3 模拟增量备份
[root@db01 /data/mysql/3306/backup/XBK]# innobackupex --defaults-file=/data/mysql/3306/my.cnf --user=root --password=123 --no-timestamp --incremental --incremental-basedir=./Full/bak_2019-09-02 ./Inc/bak_inc_1
......
xtrabackup: Transaction log of lsn (1574346783) to (1574346792) was copied.
190902 09:36:04 completed OK!step4 模拟数据增量
mysql> insert into t2(num) values(101),(202),(303);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2;
+----+-----+-------------+
| id | num | description |
+----+-----+-------------+
| 1 | 111 | NA |
| 2 | 222 | NA |
| 3 | 333 | NA |
| 4 | 121 | NA |
| 5 | 122 | NA |
| 6 | 123 | NA |
| 7 | 101 | NA |
| 8 | 202 | NA |
| 9 | 303 | NA |
+----+-----+-------------+
9 rows in set (0.00 sec)step5 模拟增量备份
[root@db01 /data/mysql/3306/backup/XBK]# innobackupex --defaults-file=/data/mysql/3306/my.cnf --user=root --password=123 --no-timestamp --incremental --incremental-basedir=./Inc/bak_inc_1 ./Inc/bak_inc_2
......
xtrabackup: Transaction log of lsn (1574348690) to (1574348699) was copied.
190902 09:39:03 completed OK!step6 模拟数据变化
mysql> insert into t2(num) values(100),(200),(300);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t2;
+----+-----+-------------+
| id | num | description |
+----+-----+-------------+
| 1 | 111 | NA |
| 2 | 222 | NA |
| 3 | 333 | NA |
| 4 | 121 | NA |
| 5 | 122 | NA |
| 6 | 123 | NA |
| 7 | 101 | NA |
| 8 | 202 | NA |
| 9 | 303 | NA |
| 10 | 100 | NA |
| 11 | 200 | NA |
| 12 | 300 | NA |
+----+-----+-------------+
12 rows in set (0.00 sec)step7 模拟数据库故障
[root@db01 /data/mysql/3306/backup/XBK]# pkill mysqld;
[root@db01 /data/mysql/3306/backup/XBK]# ps -ef | grep mysqld
root 36952 36707 0 09:43 pts/4 00:00:00 grep --color=auto mysqld
[root@db01 /data/mysql/3306/backup/XBK]# cd ../../data/
[root@db01 /data/mysql/3306/data]# grep '\[ERROR\]' ../log/error.log | tail -2
2019-09-02T01:46:44.388226Z 0 [ERROR] Fatal error: Failed to initialize ACL/grant/time zones structures or failed to remove temporary table files.
2019-09-02T01:46:44.388283Z 0 [ERROR] Aborting
[root@db01 /data/mysql/3306/data]# ps -ef | grep mysqld
root 37014 36707 0 09:48 pts/4 00:00:00 grep --color=auto mysqld数据恢复
step8 处理原始全备
[root@db01 /data/mysql/3306/backup/XBK]# innobackupex --apply-log --redo-only Full/bak_2019-09-02/
......
InnoDB: Number of pools: 1
190902 09:58:25 completed OK!step9 合并第一次增量备份到全备，并处理备份
[root@db01 /data/mysql/3306/backup/XBK]# innobackupex --apply-log --redo-only --incremental-dir=Inc/bak_inc_1/ Full/bak_2019-09-02/
......
190902 10:01:46 [00] ...done
190902 10:01:46 completed OK!step10 合并第二次增量备份到全备，并处理备份
[root@db01 /data/mysql/3306/backup/XBK]# innobackupex --apply-log --incremental-dir=Inc/bak_inc_2/ Full/bak_2019-09-02/
......
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1574349864
190902 10:03:21 completed OK!step11 整理备份
[root@db01 /data/mysql/3306/backup/XBK]# innobackupex --apply-log Full/bak_2019-09-02/
......
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1574349902
190902 10:05:08 completed OKstep12 截取二进制日志
[root@db01 /data/mysql/3306/backup/XBK]# cat Inc/bak_inc_2/xtrabackup_binlog_info
bin-log.000011 2539step13 使用备份进行数据恢复
[root@db01 /data/mysql/3306/backup/XBK]# rm -rf /data/mysql/3306/data/*
[root@db01 /data/mysql/3306/backup/XBK]# ls /data/mysql/3306/data/
[root@db01 /data/mysql/3306/backup/XBK]# innobackupex --defaults-file=/data/mysql/3306/my.cnf --copy-back Full/bak_2019-09-02/
......
190902 11:01:10 completed OK!
[root@db01 /data/mysql/3306/backup/XBK]# ll /data/mysql/3306/data/
total 188460
-rw-r----- 1 mysql mysql 1998 Sep 2 11:01 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Sep 2 11:01 ibdata1
-rw-r----- 1 mysql mysql 50331648 Sep 2 11:01 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Sep 2 11:01 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Sep 2 11:01 ibtmp1
drwxr-x--- 2 mysql mysql 50 Sep 2 11:01 Linux
drwxr-x--- 2 mysql mysql 4096 Sep 2 11:01 mysql
drwxr-x--- 2 mysql mysql 8192 Sep 2 11:01 performance_schema
drwxr-x--- 2 mysql mysql 8192 Sep 2 11:01 sys
drwxr-x--- 2 mysql mysql 68 Sep 2 11:01 test
drwxr-x--- 2 mysql mysql 182 Sep 2 11:01 world
drwxr-x--- 2 mysql mysql 76 Sep 2 11:01 xbk
-rw-r----- 1 mysql mysql 20 Sep 2 11:01 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 591 Sep 2 11:01 xtrabackup_info
-rw-r----- 1 mysql mysql 1 Sep 2 11:01 xtrabackup_master_key_id
[root@db01 /data/mysql/3306/backup/XBK]# systemctl restart mysqld;
[root@db01 /data/mysql/3306/backup/XBK]# ps -ef | grep mysqld
mysql 37395 1 3 11:04 ? 00:00:00 /application/mysql/bin/mysqld --defaults-file=/data/mysql/3306/my.cnf
root 37430 36707 0 11:05 pts/4 00:00:00 grep --color=auto mysqld
[root@db01 /data/mysql/3306/backup/XBK]# cd /tmp/
[root@db01 /tmp]# cat /data/mysql/3306/backup/XBK/Inc/bak_inc_2/xtrabackup_binlog_info
bin-log.000011 2539
[root@db01 /tmp]# mysqlbinlog --start-position 2539 /data/mysql/3306/log/bin-log.000011 >bin_log.sql
[root@db01 /tmp]# ll bin_log.sql
-rw-r--r-- 1 root root 2175 Sep 2 13:08 bin_log.sql
[root@db01 /tmp]# mysql -uroot -p123 <bin_log.sqlstep14 验证
mysql> use xbk;
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
mysql> show tables;
+---------------+
| Tables_in_xbk |
+---------------+
| t1 |
| t2 |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from t2;
+----+-----+-------------+
| id | num | description |
+----+-----+-------------+
| 1 | 111 | NA |
| 2 | 222 | NA |
| 3 | 333 | NA |
| 4 | 121 | NA |
| 5 | 122 | NA |
| 6 | 123 | NA |
| 7 | 101 | NA |
| 8 | 202 | NA |
| 9 | 303 | NA |
| 10 | 100 | NA |
| 11 | 200 | NA |
| 12 | 300 | NA |
+----+-----+-------------+
12 rows in set (0.00 sec)附：思维导图
