当前位置:首页 > Mysql > 正文内容

MySQL的备份和恢复

5年前 (2019-09-08)Mysql506

备份的类型


冷备份:

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


温备份:

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


热备份:

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


备份的方式


逻辑备份:


基于SQL语句的备份


1:binlog

2:into outfile

mysql> select * from world.city into outfile '/tmp/world_city.data';

3:mysqldump

4:replication


物理备份:


基于数据文件的备份

1:Xtrabackup(percona公司)


备份策略


1:全量备份 full

2:增量备份 increamental


备份工具


1:mysqldump(逻辑)

mysql原生自带很好用的逻辑备份工具


2:mysqlbinlog(逻辑)

实现binlog备份的原生态命令


3:xtrabackup(物理)

precona公司开发的性能很高的物理备份工具


备份工具使用


mysqldump的备份


mysqldump常用参数

1:连接服务端参数(基本参数):-u -p -h -P -S


2:-A, --all-databases:全库备份

[root@db01 ~]# mysqldump -uroot -poldboy123 -A > /backup/full.sql


3:不加参数:单库、单表多表备份

[root@db01 ~]# mysqldump -uroot -poldboy123 db1 > /backup/db1.sql
[root@mysql-db01 backup]# mysqldump -uroot -poldboy123 world city > /backup/city.sql


4:-B:指定库备份

[root@db01 ~]# mysqldump -uroot -p123 -B db1 > /backup/db1.sql
[root@db01 ~]# mysqldump -uroot -p123 -B db1 db2 > /backup/db1_db2.sql


5:-F:flush logs在备份时自动刷新binlog

[root@db01 backup]# mysqldump -uroot -p123 -A -R –triggers -F > /backup/full_2.sql


6:--master-data=2:备份时加入change master语句0没有1不注释2注释

[root@db01 backup]# mysqldump -uroot -p123 --master-data=2 >/backup/full.sql

7:-d:仅表结构

8:-t:仅数据


备份额外扩展项


1:-R, --routines:备份存储过程和函数数据


2:--triggers:备份触发器数据

[root@db01 backup]# mysqldump -uroot -p123 -A -R --triggers > /backup/full_2.sql


mysqldump特殊参数


1:-x:锁表备份(myisam温备份:

2:--single-transaction:快照备份

[root@db01 backup]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction>/backup/full.sql


gzip:压缩备份

[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction|gzip>/backup/full.sql.gz


mysqldump的恢复


#先不记录二进制日志
mysql> set sql_log_bin=0;

#库内恢复操作
mysql> source /backup/full.sql

#库外恢复操作
[root@db01 ~]# mysql -uroot -p123 < /backup/full.sql

注意:

1:mysqldump在备份和恢复时都需要MySQL实例启动为前提

2:一般数据量级100G以内,大约15-30分钟可以恢复(PB、EB就需要考虑别的方式:

3:mysqldump是以覆盖的形式恢复数据的


企业故障恢复案例


背景:

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


备份策略:

每天23:00,计划任务调用mysqldump执行全备脚本


故障时间点:

上午10点开发人员误删除一个核心业务表,如何恢复?


思路:

1:停业务避免数据的二次伤害

2:找一个临时的库,恢复前一天的全备

3:截取前一天23:00到第二天10点误删除之间的binlog,恢复到临时库

4:测试可用性和完整性

5:开启业务前的两种方式

    a.直接使用临时库顶替原生产库,前端应用割接到新库

    b.将误删除的表单独导出,然后导入到原生产环境

6:开启业务


故障模拟演练:


准备数据:


#刷新binlog使内容更清晰
mysql> flush logs;

#查看当前使用的binlog
mysql> show master status;

#创建backup库
mysql> create database backup;

#进入backup库
mysql> use backup

#创建full表
mysql> create table full select * from world.city;

#创建full_1表
mysql> create table full_1 select * from world.city;

#查看表
mysql> show tables;


全备:

[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz


模拟数据变化:

#进入backup库
mysql> use backup

#创建new表
mysql> create table new select * from mysql.user;

#创建new_1表
mysql> create table new_1 select * from world.country;

#查看表
mysql> show tables;

#查看full表中所有数据
mysql> select * from full;

#把full表中所有的countrycode都改成CHN
mysql> update full set countrycode='CHN' where 1=1;

#提交
mysql> commit;

#删除id大于200的数据
mysql> delete from full where id>200;

#提交
mysql> commit;


模拟故障:

#删除new表
mysql> drop table new;

#查看表
mysql> show tables;


恢复过程:


1:准备临时数据库

[root@db02 ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &


2:拷贝数据到新库上

[root@db02 ~]# scp /backup/full_2018-08-16.sql.gz root@10.0.0.52:/tmp


3:解压全备数据文件

#进入tmp目录
[root@db02 ~]# cd /tmp/
#解压全备数据文件
[root@db02 tmp]# gzip -d full_2018-08-16.sql.gz


4:截取二进制

#查看全备的位置点(起始位置点:
[root@db02 tmp]# head -50 full_2018-08-16.sql |grep -i 'change master to'

#找到drop语句执行的位置点(结束位置点:
mysql> show binlog events in 'mysql-bin.000017';

#截取二进制
[root@db01 tmp]#mysqlbinlog -uroot -p123 --start-position=268002 --stop-position=671148 /application/mysql/data/mysql-bin.000017 > /tmp/inc.sql

#发送增量数据到新库
[root@db01 tmp]# scp /tmp/inc.sql root@10.0.0.52:/tmp


在新库内恢复数据

#不记录二进制日志
mysql> set sql_log_bin=0;

#恢复全备数据
mysql> source /tmp/full_2018-08-16.sql

#进入backup库
mysql> use backup

# 查看表
mysql> show tables;

#恢复增量数据
mysql> source /tmp/inc.sql

#查看表
mysql> show tables;


5:将故障表导出并恢复到生产

#导出new表
[root@db02 ~]# mysqldump -uroot -p123 -S /data/3307/mysql.sock backup new > /tmp/new.sql

#发送到生产库
[root@db02 ~]# scp /tmp/new.sql root@10.0.0.51:/tmp/

#进入backup库
mysql> use backup

#在生产库恢复数据
mysql> source /tmp/new.sql

#查看表
mysql> show tables;


物理备份(Xtrabackup)


Xtrabackup安装


#下载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


备份方式(物理备份)


1:对于非innodb表(比如myisam:是直接锁表cp数据文件,属于一种温备。

2:对于innodb的表(支持事务:,不锁表,cp数据页最终以数据文件方式保存下来,并且把redo和undo一并备走,属于热备方式。

3:备份时读取配置文件/etc/my.cnf


全量备份


#全备
[root@db01 data]# innobackupex --user=root --password=123 /backup

#避免时间戳,自定义路径名
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full

#查看备份路径中的内容
[root@db01 backup]# ll /backup/full

#记录binlog文件名和binlog的位置点
-rw-r-----  1 root root       21 Aug 16 06:23 xtrabackup_binlog_info 

#备份时刻,立即将已经commit过的内存中的数据页刷新到磁盘
#备份时刻有可能会有其他数据写入,已备走的数据文件就不会再发生变化了

#在备份过程中,备份软件会一直监控着redo和undo,一旦有变化会将日志一并备走
-rw-r-----  1 root root      117 Aug 16 06:23 xtrabackup_checkpoints

#备份汇总信息
-rw-r-----  1 root root      485 Aug 16 06:23 xtrabackup_info

#备份的redo文件
-rw-r-----  1 root root     2560 Aug 16 06:23 xtrabackup_logfile

全备的恢复


准备备份

将redo进行重做,已提交的写到数据文件,未提交的使用undo回滚,模拟CSR的过程

[root@db01 full]# innobackupex --user=root --password=123 --apply-log /backup/full


恢复备份


前提1:被恢复的目录是空的

前提2:被恢复的数据库的实例是关闭的


#停库
[root@db01 full]# /etc/init.d/mysqld stop

#进入mysql目录
[root@db01 full]# cd /application/mysql

#删除data目录(在生产中可以备份一下:
[root@db01 mysql]# rm -fr data/

#拷贝数据
[root@db01 mysql]# innobackupex --copy-back /backup/ful

#授权
[root@db01 mysql]# chown -R mysql.mysql /application/mysql/data/

#启动MySQL
[root@db01 mysql]# /etc/init.d/mysqld start


增量备份及恢复


备份方式

1:基于上一次备份进行增量

2:增量备份无法单独恢复,必须基于全备进行恢复

3:所有增量必须要按顺序合并到全备当中


#不使用之前的全备,执行一次全备
[root@mysql-db01 ~]#  innobackupex --user=root --password=123 --no-timestamp /backup/full


模拟数据变化

mysql> create database inc1;
mysql> use inc1
mysql> create table inc1_tab(id int);
mysql> insert into inc1_tab values(1),(2),(3);
mysql> commit;
mysql> select * from inc1_tab;


第一次增量备份

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full/ /backup/inc1

参数说明:

--incremental:开启增量备份功能

--incremental-basedir:上一次备份的路径


再次模拟数据变化

mysql> create database inc2;
mysql> use inc2
mysql> create table inc2_tab(id int);
mysql> insert into inc2_tab values(1),(2),(3);
mysql> commit;


第二次增量备份

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1/ /backup/inc2


增量恢复


#破坏数据
[root@db01 ~]# rm -fr /application/mysql/data/


准备备份

1:full+inc1+inc2

2:需要将inc1和inc2按顺序合并到full中

3:分步骤进行--apply-log


第一步:在全备中apply-log时,只应用redo,不应用undo

[root@db01 ~]# innobackupex --apply-log --redo-only /backup/full/


第二步:合并inc1合并到full中,并且apply-log,只应用redo,不应用undo

[root@db01 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1/ /backup/full/


第三步:合并inc2合并到full中,redo和undo都应用

[root@db01 ~]# innobackupex --apply-log --incremental-dir=/backup/inc2/ /backup/full/


第四步:整体full执行apply-log,redo和undo都应用

[root@db01 mysql]# innobackupex --apply-log /backup/full/
copy-back
[root@db01 ~]# innobackupex --copy-back /backup/full/
[root@db01 ~]# chown -R mysql.mysql /application/mysql/data/
[root@db01 ~]# /etc/init.d/mysqld start


“MySQL的备份和恢复” 的相关文章

MySQL简介及安装

MySQL安装1. rpm、yum安装安装方便、安装速度快,无法定制2. 二进制不需要安装,解压即可使用,不能定制功能3. 编译安装可定制,安装慢,步骤复杂#定制功能:存储引擎、字符集、压缩#定制安装位置、数据存放位置、文件位置(socket)#下载5.6.36包 [root@db02 ...

MySQL程序结构

MySQL程序结构

mysql是一个C/S服务结构1、mysql自带的客户端程序(/application/mysql/bin)mysqlmysqladminmysqldump2、应用程连接MySQL方式TCP/IP的连接方式套接字连接方式MySQL服务器构成1.MySQL的后台进程+线程+预分配的内存结构。2.MyS...

MySQL用户、权限简单管理

MySQL管理MySQL用户的作用:1、登陆MySQL数据库2、管理数据库对象MySQL用户管理:1、创建用户:create user2、删除用户:delete user drop user3、修改用户:update用户的定义:1、username@’主机域’2、主机域:可以理解为是MySQL登陆的...

mysql数据库SQL语句及字符集定义

mysql数据库SQL语句及字符集定义

SQL简单语句用于数据库的连接管理:mysql命令行管理工具:mysqladmin备份数据库和表的内容:mysqldumpMySQL接口自带的命令\h 或 help 或?    查看帮助 \G   ...

MySQL索引管理及执行计划

MySQL索引管理及执行计划

一.索引介绍 1.索引1)索引就好比一本书的目录,它能让你更快的找到自己想要的内容。2)让获取的数据更有目的性,从而提高数据库检索数据的性能。2.索引类型1.BTREE:B+树索引2.HASH:HASH索引3.FULLTEXT:全文索引4.RTREE:R树索引3.索引管理索引建立在表的列上...

MySQL的存储引擎(二)Innodb核心特性——事务

MySQL的存储引擎(二)Innodb核心特性——事务

Innodb核心特性——事务1.什么是事务主要针对DML语句(update,delete,insert)一组数据操作执行步骤,这些步骤被视为一个工作单元:1)用于对多个语句进行分组2)可以在多个客户机并发访问同一个表中的数据时使用所有步骤都成功或都失败1)如果所有步骤正常,则执行2)如果步骤出现错误...