第十五章 MySQL备份与恢复

一、数据的备份

1.备份的原因

1.数据重要
2.备份就是为了恢复
3.减少公司的损失

2.备份的类型

1.冷备份:先停止数据库的访问，然后备份

这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。

2.温备份:不停止数据库的访问，不阻止数据的读取，阻止数据修改的同时然后备份

这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。

3.热备份:不停止数据库的访问，不锁表，不阻止数据的读取和写入的同时进行备份

这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问

3.备份的形式

1）逻辑备份

#把数据导出成sql文件的形式进行备份
1.binlog 的方式
2.into outfile
	1）配置数据库
		[root@db01 ~]# vim /etc/my.cnf
		[mysqld]
		secure-file-priv=/tmp
	2）数据库执行
		mysql> select * from world.city into outfile '/tmp/world_city.data';
	3）查看导出文件
3.mysqldump
4.replication (数据库主从，属于逻辑备份，并没有是真正的做到备份)

2）物理备份

1.直接备份data目录
	1）将数据目录data打包
	2）将data打包文件推送到另一台数据库服务器（两台机器必须使用相同安装方式）
	3）到新机器解压data目录到数据库的数据目录进行替换
	4）登录数据库查看
	
2.Xtrabackup

3）备份的类型

1.全备：全部数据都进行备份
	1）比较浪费磁盘空间
	2）恢复数据方便

2.增备：对新增加的数据进行备份
	1）相对于全备比较节省磁盘空间
	2）恢复数据需要合并多次数据

3.差异备份：针对上一次全备进行备份
	1）也会出现数据的重复，比较浪费磁盘空间
	2）恢复数据，只需要与全备合并进行恢复即可

二、mysqldump 备份命令

1.mysql客户端

1.mysql
2.mysqladmin
3.mysqldump

2.mysqldump命令参数

0）语法

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

1）不使用任何参数的时候

1.导出指定库
[root@db01 ~]# mysqldump -uroot -p world > /tmp/world.sql

2.导出指定库下面的指定表
[root@db01 ~]# mysqldump -uroot -p world city > /tmp/city.sql

3.导出指定库下面的多个表（第一个参数必须是库，后面的其他参数都只能是表名）
[root@db01 ~]# mysqldump -uroot -p world city country > /tmp/city2.sql

2）加参数

1. -A 等于 --all-databases，导出所有库
	[root@db01 ~]# mysqldump -uroot -p -A > /tmp/full.sql

2. -B 指定库备份，只能指定库的名字
	[root@db01 ~]# mysqldump -uroot -p -B world row events > /tmp/1.sql
	
3. -F 备份数据的同时刷新binlog
	[root@db01 ~]# mysqldump -uroot -p -F world city > /tmp/city2.sql
	
4. --master-data=2 备份时进行打点，记录备份时的binlog位置点（必须开启binlog才能使用）
	[root@db01 ~]# mysqldump -uroot -p123 -B world --master-data=2 > /tmp/world.sql
		--master-data=0		不打点不记录位置
		--master-data=2		打点并注释（单纯的进行备份时）
		--master-data=1		打点不注释（主从数据库扩展从库时）

5. --single-transaction 快照备份

6. -d 仅表结构
7. -t 仅数据
8. -R --routines 备份存储过程和函数数据
9. --triggers 备份触发器数据

#备份的完整命令
[root@db01 ~]# mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sql

3）扩展

1. gzip 压缩备份
	[root@db01 ~]# mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.tar.gz
	#压缩恢复数据时
	[root@db01 ~]# zcat /tmp/full.tar.gz | mysql -uroot -p

2. 备份同时加上时间戳
	[root@db01 ~]# mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction > /tmp/full$(date +%F).sql

三、企业故障恢复案例

1.背景

1.正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。

2.备份策略：每天23：00，计划任务调用mysqldump执行全备脚本

3.故障时间点：上午10点开发人员误删除一个核心业务表，如何恢复

2.数据库恢复思路

1.停止业务，避免数据的二次伤害
2.找一个临时的库，恢复前一天的全备
3.截取前一天23：00到第二天10点误删除之间的binlog，恢复到临时库
4.测试可用性和完整性
5.开启业务前的两种方式
	1）直接使用临时库顶替原生产库，前端应用割接到新库（核心业务表数据量多的时候）
	2）将误删除的表单独导出，然后导入到原生产环境（核心业务表数据量少的时候）
6.开启业务

3.故障模拟

1）准备数据

#建库
mysql> create database backup;
#切换库
mysql> use backup;
#建表
mysql> create table back111(id int);
#建表
mysql> create table back222 select * from world.city;

2）进行23:00全备

#定时任务备份
[root@db01 ~]# mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction > /tmp/backup$(date +%F).sql

3）模拟23：00备份后到早上10点的操作

#切换库
mysql> use backup
#建表
mysql> create table back333 select * from world.city;
#修改数据
mysql> update back222 set countrycode='CHN' where 1=1;

4）模拟10:00开发人员删除核心表

#删除核心表
mysql> drop table back111;

4.恢复数据

1）停止业务

[root@db01 ~]# systemctl stop mysqld

2）准备新的数据库

3）找出binlog对应23:00到10点的位置点

1.找到起始位置点
	[root@db01 ~]# less /tmp/backup2020-11-04.sql
	#第22行
	-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=959935;
	
2.找到结束位置点
	[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv --start-position=959935 /usr/local/mysql/data/mysql-bin.000013 > 1.txt
	[root@db01 ~]# vim 1.txt
	# at 1339403
	... ...
	DROP TABLE `back111` /* generated by server */
	
3.截取binlog
	[root@db01 ~]# mysqlbinlog --start-position=959935 --stop-position=1339403 /usr/local/mysql/data/mysql-bin.000013 > /tmp/huifu.sql

4）将全备和恢复的sql推送到新库

[root@db01 ~]# scp /tmp/backup2020-11-04.sql 172.16.1.52:/tmp
[root@db01 ~]# scp /tmp/huifu.sql  172.16.1.52:/tmp

5）将sql导入新的数据库

[root@db02 ~]# mysql < /tmp/backup2020-11-04.sql 
[root@db02 ~]# mysql < /tmp/huifu.sql
#尽量使用source的方式恢复

6）确认数据

mysql> use backup
mysql> select * from back333;

四、Xtrabackup 物理备份

1.上传或者下载Xtrabackup工具

1.上传
[root@db01 ~]# rz percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

#或者

2.下载
#下载epel源
wget -O /etc/yum.repos.d/epel.repo  https://mirrors.aliyun.com/repo/epel-6.repo
#安装依赖
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
#下载Xtrabackup
wget httpss://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

2.安装

[root@db01 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

[root@db01 ~]# xtrabackup		#旧版本的命令
[root@db01 ~]# innobackupex		#新版本的命令

3.xtrabackup特性

1.对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
2.对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
3.备份时读取配置文件/etc/my.cnf

4.xtrabackup全量备份

1.创建备份的目录
	[root@db01 ~]# mkdir /backup
	
2.全备
	[root@db01 ~]# innobackupex --user=root --password=123 /backup/full/
	
3.查看全备的文件
	[root@db01 ~]# ll /backup/full/
	总用量 0
	drwxr-x--- 9 root root 271 11月  4 17:50 2020-11-04_17-50-18
	drwxr-x--- 9 root root 271 11月  4 17:51 2020-11-04_17-51-34
	
4.去除时间戳进行备份
	[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full/

5.再次查看备份文件
	[root@db01 ~]# ll /backup/full/
    [root@db01 /backup/full]# vim xtrabackup_checkpoints   #备份的信息
	backup_type = full-backuped
	from_lsn = 0
	to_lsn = 2330991066
	
	xtrabackup_binlog_info		#binlog的信息
    xtrabackup_info				#备份工具的信息
    xtrabackup_logfile			#binlog

5.使用xtrabackup进行数据恢复

1.删库
mysql> drop database test;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

2.停止数据库
[root@db01 ~]# systemctl stop mysqld

3.将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚，模拟CSR的过程
[root@db01 ~]# innobackupex --user=root --password=123 --apply-log /backup/full

4.恢复数据
[root@db01 ~]# cd /usr/local/mysql
[root@db01 /usr/local/mysql]# mv data databack
#方式一：
[root@db01 /usr/local/mysql]# cp -r /backup/full ./data
[root@db01 /usr/local/mysql]# chown -R mysql.mysql data
#方式二：
[root@db01 /usr/local/mysql]# innobackupex --copy-back /backup/full/
[root@db01 /usr/local/mysql]# chown -R mysql.mysql data

posted @ 2020-11-09 18:35 年少纵马且长歌阅读(191) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

青青子衿悠悠我心

当你的才华还撑不起你的野心的时候，你就应该静下心来学习

第十五章 MySQL备份与恢复

一、数据的备份

1.备份的原因

2.备份的类型

3.备份的形式

1）逻辑备份

2）物理备份

3）备份的类型

二、mysqldump 备份命令

1.mysql客户端

2.mysqldump命令参数

0）语法

1）不使用任何参数的时候

2）加参数

3）扩展

三、企业故障恢复案例

1.背景

2.数据库恢复思路

3.故障模拟

1）准备数据

2）进行23:00全备

3）模拟23：00备份后到早上10点的操作

4）模拟10:00开发人员删除核心表

4.恢复数据

1）停止业务

2）准备新的数据库

3）找出binlog对应23:00到10点的位置点

4）将全备和恢复的sql推送到新库

5）将sql导入新的数据库

6）确认数据

四、Xtrabackup 物理备份

1.上传或者下载Xtrabackup工具

2.安装

3.xtrabackup特性

4.xtrabackup全量备份

5.使用xtrabackup进行数据恢复

公告

青青子衿悠悠我心

当你的才华还撑不起你的野心的时候，你就应该静下心来学习

第十五章 MySQL备份与恢复

一、数据的备份

1.备份的原因

2.备份的类型

3.备份的形式

1）逻辑备份

2）物理备份

3）备份的类型

二、mysqldump 备份命令

1.mysql客户端

2.mysqldump命令参数

0）语法

1）不使用任何参数的时候

2） 加参数

3）扩展

三、企业故障恢复案例

1.背景

2.数据库恢复思路

3.故障模拟

1）准备数据

2）进行23:00全备

3）模拟23：00备份后到早上10点的操作

4）模拟10:00开发人员删除核心表

4.恢复数据

1）停止业务

2）准备新的数据库

3）找出binlog对应23:00到10点的位置点

4）将全备和恢复的sql推送到新库

5）将sql导入新的数据库

6）确认数据

四、Xtrabackup 物理备份

1.上传或者下载Xtrabackup工具

2.安装

3.xtrabackup特性

4.xtrabackup全量备份

5.使用xtrabackup进行数据恢复

公告

2）加参数