数据备份与恢复

备份概述

一、备份方式

按照数据库服务状态分为：

冷备份：在备份时暂停数据库运行和服务，将整个数据库复制到备份设备中

热备份：在备份时不停止数据库的运行和服务

按照备份的数据分为：

物理备份：备份数据库服务器上存储的原始数据和文件，可以直接拷贝和恢复

逻辑备份：备份的是SQL语句，可用于不同版本和不同类型的MySQL数据库之间的数据迁移

二、备份策略

1、完全备份

• 完全备份所有的数据
• 易于管理

2、增量备份

• 每周进行一次完全备份
• 其余每天备份和上次备份的差异部分

3、差异备份

• 每周进行一次完全备份
• 其余每天备份和完全备份的差异部分

三、备份系统三要素

BW 一个工作周期内留给备份系统进行备份的时间长度

RPO 客户可以承受的最大数据丢失量

RTO 客户可以承受的最长停机时间

完全备份与恢复

一、物理备份及恢复

适合线下备份，不适合线上备份

1、备份操作

systemctl stop mysqld
cp -rp /var/lib/mysql /opt/mysql.bak
或
tar -zcf /opt/mysql.tar.gz /var/lib/mysql/*

2、恢复操作

systemctl stop mysqld
rm -rf /var/lib/mysql/*
cp -r /opt/mysql.bak/* /var/lib/mysql/
或
tar -zxf /opt/mysql.tar.gz -C /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

二、逻辑备份及恢复

1、完全备份

mysqldump -uroot -p密码 库名 > /目录/xxx.sql

库名表示方式：

库名 表名列表        备份1张表或多张表所有数据

-B 库名列表        备份1个库或多个库所有数据

--all-databases 或 -A        备份1台服务器所有数据

[root@mysql50 ~]# mkdir /bakdir
# 备份所有库 
[root@mysql50 ~]# mysqldump -uroot -p123456 -A > /bakdir/allbak.sql
# 备份1个库 
[root@mysql50 ~]# mysqldump -uroot -p123456 -B tarena > /bakdir/tarena.sql
# 备份多个库
root@mysql50 ~]# mysqldump -uroot -p123456  -B db3 db1 > /bakdir/db3_db1.sql 
# 备份多张表
[root@mysql50 ~]# mysqldump -uroot -p123456  tarena employees departments > /bakdir/tarena_employees_deparments.sql
# 备份1张表 
[root@mysql50 ~]# mysqldump -uroot -p123456 tarena salary > /bakdir/tarena_salary.sql[root@mysql50 ~]# ls /bakdir/
allbak.sql  tarena_salary.sql  tarena.sql  db3_db1.sql   tarena_employees_deparments.sql

2、完全恢复

1）命令行恢复

mysql -uroot -p密码 [库名] < /目录/xxx.sql

[root@mysql51 ~]# mysql -p123456mysql> select count(*) from tarena.salary;
mysql> delete from tarena.salary;
mysql> select count(*) from tarena.salary;[root@mysql50 ~]# scp /bakdir/tarena_salary.sql 192.168.88.51:/root
[root@mysql51 ~]# ls
mysql.tar.gz  tarena_salary.sql
[root@mysql51 ~]# mysql -uroot -p123456 tarena < /root/tarena_salary.sql 
[root@mysql51 ~]# mysql -p123456mysql> select count(*) from tarena.salary;

2）使用数据库里的 source 命令恢复数据

mysql> drop database tarena;
mysql> show databases;
mysql> source /bakdir/tarena.sql;
mysql> show databases;
mysql> drop table tarena.salary;
mysql> use tarena;
mysql> show tables;
mysql> source /bakdir/tarena_salary.sql;
mysql> show tables;

恢复库时库名可以省略

恢复表时需要库名

备份和恢复时，数据库服务必须是运行状态

Mysqldump 备份和恢复数据时会锁表，锁表期间无法对表做写访问，mysqldump适合备份数据量比较小的数据或在数据库服务器访问量少的时候备份。

3、备份缺点

1）物理备份缺点

• 跨平台性差
• 备份时间长、冗余备份、浪费存储空间

2）mysqldump备份缺点

• 效率较低、备份和还原速度慢、锁表
• 备份过程中，数据插入和更新操作被阻塞

增量备份与恢复

备份上次备份后 产生的数据

软件介绍

一款强大的在线热备份工具

备份过程中不锁库表，适合生产环境

由专业组织Percona提供（改进MySQL分支）

一、软件安装

# 免安装解压即可使用[root@mysql50 ~]# yum -y install perl-DBD-MySQL    # 安装依赖
[root@mysql50 ~]# tar -xf percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz
[root@mysql50 ~]# mv percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal /usr/local/percona
[root@mysql50 ~]# vim /etc/bashrc           # 配置初始配置文件 系统环境变量
export PATH=/usr/local/percona/bin:$PATH    # 添加此行
[root@mysql50 ~]# source /etc/bashrc        # 使配置生效
[root@mysql50 ~]# man xtrabackup 

二、增量备份

1、首次完全备份

xtrabackup --host=127.0.0.1 --user=用户名 --password=密码 --backup --target-dir=备份目录 --datadir=数据库目录

[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/allbak --datadir=/var/lib/mysql

2、增量备份

xtrabackup --host=127.0.0.1 --user=用户名 --password=密码 --backup --target-dir=备份目录 --incremental-basedir=上次备份目录 --data-dir=数据库目录

[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/new2 --incremental-basedir=/allbak --data-dir=/var/lib/mysql
...连接数据库插入数据...
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/new3 --incremental-basedir=/new2 --data-dir=/var/lib/mysql
...连接数据库插入数据...
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/new4 --incremental-basedir=/new3 --data-dir=/var/lib/mysql
...连接数据库插入数据...
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/new5 --incremental-basedir=/new4 --data-dir=/var/lib/mysql
...连接数据库插入数据...
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/new6 --incremental-basedir=/new5 --data-dir=/var/lib/mysql
...连接数据库插入数据...
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/new7 --incremental-basedir=/new6 --data-dir=/var/lib/mysql[root@mysql50 ~]# ls -d /allbak/
/allbak/
[root@mysql50 ~]# ls -d /new*
/new2  /new3  /new4  /new5  /new6  /new7

三、增量恢复 

1、准备恢复数据

定义合并数据的位置

[root@mysql50 ~]# scp -r /allbak root@192.168.88.51:/opt
[root@mysql50 ~]# scp -r /new* root@192.168.88.51:/opt
[root@mysql51 ~]# ls /opt/
allbak  new2  new3  new4  new5  new6  new7

[root@mysql51 ~]# cat /opt/allbak/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 23539361
last_lsn = 23539361
flushed_lsn = 23539361[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak# 验证合并
[root@mysql51 ~]# cat /opt/allbak/xtrabackup_checkpoints 
backup_type = log-applied    # 此行改变 可以合并数据了
from_lsn = 0
to_lsn = 23539361
last_lsn = 23539361
flushed_lsn = 23539361

2、合并数据

合并的顺序要和备份的顺序一致

[root@mysql51 ~]# cat /opt/allbak/xtrabackup_checkpoints    # 合并前查看数据范围
backup_type = log-applied
from_lsn = 0
to_lsn = 23541261
last_lsn = 23541261
flushed_lsn = 23541261[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak --incremental-dir=/opt/new2[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak --incremental-dir=/opt/new3
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak --incremental-dir=/opt/new4
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak --incremental-dir=/opt/new5
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak --incremental-dir=/opt/new6
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak --incremental-dir=/opt/new7[root@mysql51 ~]# cat /opt/new7/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23543141
to_lsn = 23543161
last_lsn = 23543171
flushed_lsn = 23543161[root@mysql51 ~]# cat /opt/allbak/xtrabackup_checkpoints    
backup_type = log-applied
from_lsn = 0
to_lsn = 23543161
last_lsn = 23543171
flushed_lsn = 23543161

3、拷贝数据

恢复数据

[root@mysql51 ~]# systemctl stop mysqld.service 
[root@mysql51 ~]# rm -rf /var/lib/mysql/*
[root@mysql51 ~]# xtrabackup --copy-back --target-dir=/opt/allbak
[root@mysql51 ~]# chown -R mysql:mysql /var/lib/mysql/
[root@mysql51 ~]# systemctl start mysqld.service 
[root@mysql51 ~]# mysql -uroot -p123456

四、恢复步骤

1、准备恢复数据

定义合并数据的位置

2、合并数据

合并的顺序要和备份的顺序一致

3、清空数据库目录

停止数据库服务 并清空数据库目录

4、拷贝数据

恢复数据

5、修改数据库目录所有者/所属组用户为mysql

6、重启数据库服务

差异备份与恢复

备份完全备份后 产生的新数据

一、差异备份

1、首次完全备份

xtrabackup --host=127.0.0.1 --user=用户名 --password=密码 --backup --target-dir=/备份目录 --datadir=/数据库目录

[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/fullbak --datadir=/var/lib/mysql
[root@mysql50 ~]# ls /fullbak/

2、差异备份

xtrabackup --host=127.0.0.1 --user=用户名 --password=密码 --backup --target-dir=/备份目录 --incremental-basedir=/完全备份目录 datadir=/数据库目录

[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/dir2 --incremental-basedir=/fullbak
[root@mysql50 ~]# ls /dir2/...连接数据库插入数据...[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/dir3 --incremental-basedir=/fullbak
[root@mysql50 ~]# ls /dir3/......

二、差异恢复

1、准备恢复数据

[root@mysql50 ~]# scp -r /fullbak root@192.168.88.51:/root
[root@mysql50 ~]# scp -r /dir2/ root@192.168.88.51:/root
[root@mysql50 ~]# scp -r /dir3/ root@192.168.88.51:/root

[root@mysql51 ~]# cat /root/fullbak/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 23544911
last_lsn = 23544911
flushed_lsn = 23544911[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/fullbak[root@mysql51 ~]# cat /root/fullbak/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 23544911
last_lsn = 23544911
flushed_lsn = 23544911

2、合并数据

[root@mysql51 ~]# cat /root/dir3/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23544911
to_lsn = 23548529
last_lsn = 23548539
flushed_lsn = 23548529[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/fullbak --incremental-dir=/root/dir3[root@mysql51 ~]# cat /root/fullbak/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 23548529
last_lsn = 23548539
flushed_lsn = 23548529

3、拷贝数据

[root@mysql51 ~]# systemctl stop mysqld.service 
[root@mysql51 ~]# rm -rf /var/lib/mysql/*
[root@mysql51 ~]# xtrabackup --copy-back --target-dir=/root/fullbak
[root@mysql51 ~]# chown -R mysql:mysql /var/lib/mysql
[root@mysql51 ~]# systemctl start mysqld.service 
[root@mysql51 ~]# mysql -uroot -p123456

三、恢复步骤

1、准备恢复数据

2、合并数据

3、清空数据目录

4、拷贝数据

5、修改数据库目录所有者/组用户为mysql

6、重启mysql服务

完全+差异

                                        数据量                        备份量
周一        完全备份           1G                              1G    

周二        差异备份           1G+2G                       2G

周三        差异备份           1G+2G+3G                5G

周四        差异备份           1G+2G+3G+4G         9G      

周五        差异备份          1G+2G+3G+4G+5G    14G

周六        差异备份       1G+2G+3G+4G+5G+6G        20G

周日        差异备份     1G+2G+3G+4G+5G+6G+7G    27G

完全+增量

                                        数据量                        备份量
周一        完全备份           1G                              1G    

周二        增量备份           1G+2G                       2G

周三        增量备份           1G+2G+3G                3G

周四        增量备份           1G+2G+3G+4G         4G      

周五        增量备份          1G+2G+3G+4G+5G    5G

周六        增量备份       1G+2G+3G+4G+5G+6G        6G

周日        增量备份     1G+2G+3G+4G+5G+6G+7G    7G

实时备份与恢复

启用mysql服务的binlog日志文件

binlog日志文件保存的是 在数据库服务里执行的数据库命令

除查询之外的所有命令（数据改变记，数据不改变不记）

binlog日志的使用

查看正在使用的binlog日志文件

mysql> show master status;

修改日志文件存放的位置和文件名

[root@mysql52 ~]# vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
......
log-bin=/mylog/hmy    # 添加此行[root@mysql52 ~]# mkdir /mylog
[root@mysql52 ~]# chown -R mysql:mysql /mylog/
[root@mysql52 ~]# getenforce    # 不能强制模式[root@mysql52 ~]# systemctl restart mysqld.service 
[root@mysql52 ~]# ls /mylog/
hmy.000001  hmy.indexmysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| hmy.000001 |      156 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

手动生成日志文件

[root@mysql52 ~]# mysql -e "show master status"[root@mysql52 ~]# systemctl restart mysqld.service [root@mysql52 ~]# systemctl stop mysqld.service 
[root@mysql52 ~]# systemctl start mysqld.service [root@mysql52 ~]# mysqldump -B db1 --flush-logs > /root/db1.sqlmysql> flush logs;

数据库服务里对日志管理的命令

mysql> show master status;    # 查看正在使用的日志
mysql> show binary logs;    # 查看已有的日志文件
mysql> purge master logs to "日志名";    # 删除编号之前的日志
mysql> reset master;    # 删除所有日志，重新创建日志mysql> show binlog events in "日志文件名";    # 查看日志文件内容
#
mysql> create database db10;
Query OK, 1 row affected (0.09 sec)mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| hmy.000001 |      341 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)mysql> show binlog events in "hmy.000001";
+------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name   | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
+------------+-----+----------------+-----------+-------------+--------------------------------------+
| hmy.000001 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.26, Binlog ver: 4    |
| hmy.000001 | 125 | Previous_gtids |         1 |         156 |                                      |
| hmy.000001 | 156 | Anonymous_Gtid |         1 |         233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| hmy.000001 | 233 | Query          |         1 |         341 | create database db10 /* xid=53 */    |
+------------+-----+----------------+-----------+-------------+--------------------------------------+
4 rows in set (0.00 sec)

使用日志恢复数据

把查看到的文件内容管道给连接mysql服务的命令执行

mysqlbinlog /目录名/binlog日志文件 | mysql -u用户名 -p密码

[root@mysql50 ~]# mysqldump -uroot -p123456 -B tarena --flush-logs > /root/tarena.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql50 ~]# ls /root/*.sql
/root/tarena.sql
[root@mysql50 ~]# mysql -uroot -p123456 -e 'show master status'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000021 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
[root@mysql50 ~]# mysql -uroot -p123456 -e 'insert into tarena.salary(employee_id,basic) values(2,10200)'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql50 ~]# mysql -uroot -p123456 -e 'show master status'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000021 |      443 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
[root@mysql50 ~]# 
[root@mysql51 ~]# mysql -uroot -p123456 -e 'drop database tarena'
[root@mysql51 ~]# scp 192.168.88.50:/root/tarena.sql /root/
[root@mysql51 ~]# mysql -uroot -p123456 < /root/tarena.sql 
[root@mysql51 ~]# mysql -uroot -p123456 -e 'select count(*) from tarena.salary'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|     8065 |
+----------+
[root@mysql50 ~]# mysql -uroot -p123456 -e 'select count(*) from tarena.salary'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|     8066 |
+----------+
[root@mysql50 ~]# scp /var/lib/mysql/binlog.000021 192.168.88.51:/root
[root@mysql51 ~]# mysqlbinlog /root/binlog.000021 
[root@mysql51 ~]# mysqlbinlog /root/binlog.000021 | mysql -uroot -p123456
[root@mysql51 ~]# mysql -uroot -p123456 -e 'select count(*) from tarena.salary'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|     8066 |
+----------+

可以通过执行日志文件里记录的命令恢复数据

mysqlbinlog /目录名/binlog日志文件 | mysql -u用户名 -p密码