MySQL备份

一、备份工作内容

1.备份设计策略

  • 备份周期
  • 备份工具
  • 备份方法(全备、增量)
  • 备份位置
企业常见备份策略

1. MDP定时全备+binlog备份(每天)
2. XBK定时全备+XBK增量(每天)+binlog备份(每天)
3. XBK定时全备+binlog备份(每天)

2.定期的恢复演练

3.快速故障恢复

4.数据迁移

二、备份工具

1.mysqldump(MDP)

  1. mysqldump是mysql自带的逻辑备份工具;主要备份的是SQL语句(CREATE DATABASE,CREATE TABLE;Insert语句);
  2. 特点
  • 优点:备份数据可读性较强,压缩比较高
  • 缺点:备份性能相对较差

2.xtrabackup(PBK、XBK)

  1. xtrabackup是Percona公司推出一款第三方物理备份工具;
  2. 特点
  • 优点:备份性能相对较好
  • 缺点:可读性弱,压缩比较低

三、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.sql

3.特殊功能参数

  • -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.sql

4.备份恢复模拟演练

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.sql

step2 模拟数据变化

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.rpm

step3 安装软件包

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 mysqld

2.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: data

3.全备恢复

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_logfile

step2 模拟故障

[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] Aborting

step3 备份的处理(保证数据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_id

step5 修改原数据文件权限

[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_id

step6 启动数据库

[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 mysqld

step7 验证

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 OK

step12 截取二进制日志

[root@db01 /data/mysql/3306/backup/XBK]# cat Inc/bak_inc_2/xtrabackup_binlog_info 
bin-log.000011  2539

step13 使用备份进行数据恢复

[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.sql

step14 验证

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)

附:思维导图

发表评论

您的电子邮箱地址不会被公开。