mysql 装载dump文件_07 : mysql备份恢复（1） - mysqldump命令使用

注意： mysql不同引擎备份方法不同。我们先来回忆一下和备份有关的知识点。

1、存储引擎

(1)InnoDB(默认使用引擎，也是企业常用的)

热备

独立表空间(每个表一个表空间)

redo：重做日志，用来前滚

undo：回滚日志，用来回滚(未提交的事务)

行级别锁，基于索引来实现的，GAP锁

支持事务。

(2)MyISAM

温备

三个文件构成

表级锁

2、二进制日志

(1)记录的是什么？

DDL、DCL这些种类语句，记录的就是操作语句

DML：他记录的已提交的事务日志，并支持多种格式记录(row、statement、mixed)

(2)事件

开始位置(at xxx)

结束为止(下一个at)

一个事务，有多个事件做成(begin到commit)

(3)截取二进制日志

1、分析二进制日志

找到要截取日志的开始位置(start-position)和结束位置(stop-position)

mysqlbinlog --base64-output=decode=rows -vvv

2、截取日志

mysqlbinlog -d test  --start-position=xxx --stop-position=xxx

-----------------------------------------------------------------------

MySLQL备份

1、备份是为了什么？

就是为了恢复数据库损坏

2、数据库损坏

物理损坏

高可用架构可以很好解决

逻辑损坏

只能用备份就解决

3、备份类型

冷备份

在数据库未工作时进行备份。数据一致性高，可靠性高。影响所有读写。

温备份

数据库不停止工作，只是在备份时对表进行加锁，不影响读，但是影响写操作。

热备份

数据库不停止工作，基于事务的特点进行数据库在线备份，读写影响小。受限于存储引擎类型(innoDB)。4、备份方式

逻辑备份(文本表示：SQL 语句)

物理备份(数据文件的二进制副本)

基于复制的备份

增量备份(刷新二进制日志)

5、备份工具

逻辑备份：

1、mysqldump

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

- 备份特点：备份的SQL

- 优势：可读性高、压缩比高

- 劣势：备份和恢复需要花费更多时间

-比较依赖于CPU和IO性能

2、mysqlbinlog

– 实现binlog备份的原生态命令

物理备份：

3、xtrabackup (第三方，需要单独安装配置)

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

- 备份特点：备份的是真正的数据文件

- 优势：备份恢复快

- 劣势：可读性差、压缩比低

- 主要是消耗IO

如何选择备份工具：

1: 100G以内，mysqldump大体上能够控制在60分钟左右，还是可以接受的

2: 100G以上，到TB级别，xtrabackup比较合适，备份恢复速度都比较快

大数据两级别，TB以上，该怎么选型？？？？？？

6、备份工具使用：

(1) mysqldump 命令备份：

数据量计算：

select sum(data_length+index_length)/1024/1024/1024 from information_schema.tables;

常用参数：

0> -u -p -h -P -S 和客户端有关的参数

1> -A 全库备份

mysqldump -uroot -p123 -A >/backup/full.sql

2> -B 备份1个或者多个单库

mysqldump -uroot -p123 -B world mysql>/backup/data.sql

mysqldump -uroot -p123 -B world >/backup/world.sql

3>单库或单表备份

备份单库(思考这里不加-B的区别)

mysqldump -uroot -p123 world >/backup/world1.sql

备份world 库下的city表

mysqldump -uroot -p123 world city >/backup/city.sql

** 思考并验证以下命令的区别：

mysqldump -uroot -p123 -B world >/backup/world.sql

与

mysqldump -uroot -p123 world >/backup/world1.sql

vimdiff wordpres.sql world1.sql

结论：

1、-B 在备份时，会添加create database和use语句

2、-B的备份恢复时，直接source

3、不加-B备份恢复时，需要事先创建好数据库，use进去进行source

4> -d 只备份表结构

mysqldump -uroot -p123 -A -d >/backup/aa.sql

5> 生产中必加的选项(1)

-R 备份存储过程和函数数据

--triggers 备份触发器数据

mysqldump -uroot -p123 -A -R --triggers >/backup/full.sql

<6> -F flush logs

在备份时，刷新一个新的二进制日志文件

(有几个数据库就会刷新出几个binlog文件 - 所以一般我们很少使用，使用下面的那个参数<7>)

mysqldump -uroot -p123 -A -R --triggers -F >/backup/full.sql

<7>生产中必加的选项(2) - 这样加-F 只会刷新出一个binlog出来。

--master-data=1 记录增加binlog日志文件名和 对应的位置点可执行的语句

--master-data=2 把记录的语句变成--注释，不会执行(默认使用这个)

mysqldump -uroot -p123456 -A   -R --triggers --master-data=2  --single-transaction  -F  |gzip >/tmp/alL_$(date +%F).sql.gz

功能：

1、在备份开始时刻，记录当时二进制日志的文件号，位置号，将来可以作为二进制日志的截取的起始位置

2、如果添加了此参数，会自动对备份的表加锁，对于加了--single-transaction参数的时候，

如果是innodb表则会实行“热备”

mysqldump -uroot -p123 -A -R --triggers --master-data=2>/backup/full.sql

<8>生产中必加的选项(3)

--single-transaction 主要的功能是，对innodb表实现“热备”(快照)

mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction >/backup/full.sql

mysqldump 热备的实现原理是什么？

1、热备的前提是，只能对innodb表才能实现的

2、热备的实现原理，基于时间点的的快照

3、我的理解是，数员工人数。。。。。

-----------------------------

<9> mysqldump企业备份实现

mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction |gzip >/backup/alL_$(date +%F).sql.gz

基于mysqldump备份策略？

0、数据量级在100G以内，时间控制在1小时以内比较合适。

1、每小时备份二进制日志，可以设定为远程备份。

2、备份和数据要存储在不同的设备上。

3、以上的操作都是由crontab。

备份策略1：每天全备，每小时binlog备份

00 23 * * * full.sh

00 * * * * binlog.sh

----------------

备份策略2：每周日备份全备，其它时间备份binlog

00 23 * * 7 full.sh (mysqldump 全备)

00 23 * * 1-6 binlog.sh (思路find 出来binlog文件，打包备份推送到远程)

----------------

从远程备份：

mysqldump -uroot -p123 -h 10.0.0.51 -A -R --triggers --master-data=2 --single-transaction |gzip >/backup/alL_$(date +%F).sql.gz

------------------------

<10> 利用备份恢复故障数据

故障案例基本背景：

1、mysql 5.6.34单节点数据库

2、数据量：30G

3、备份策略：mysqldump+mysqlbinlog

故障描述：

开发人员，周一上午10点，误删除生产库oss_base(计费、营帐、His、Boss)所有数据，线上核心应用Crash。

故障处理思路：

1、将业务进行断开，避免二次伤害

2、搭建一个备用库，恢复周日全备。

3、截取二进制日志，恢复到备用库。

4、测试备用库数据，可用性和完整性

5、恢复应用

方式一：应用直接割接到被用户，开启应用

方式二：将故障数据，倒回到元生产库，开其应用

项目结果：经过30分钟的处理，业务恢复正常。

故障模拟及恢复：

1、周日23:00全备

mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction |gzip >/backup/alL_$(date +%F).sql.gz

2、模拟周一数据变化

mysql> create database oss_base charset utf8;

mysql> use oss_base

mysql> create table oss_tab(id int , name varchar(20));

mysql> insert into oss_tab values(1,'a');

mysql> insert into oss_tab values(2,'b');

mysql> insert into oss_tab values(3,'c');

mysql> insert into oss_tab values(4,'d');

mysql> commit;

3、模拟故障

mysql> drop database oss_base;

4、恢复

(1)检查全备，找到二进制日志截取的起始位置

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=120;

(2)恢复全备

gunzip alL_2019-10-03.sql.gz

set sql_log_bin=0;

source /backup/alL_2019-10-03.sql

到此为止，已经将数据恢复到了昨天23点时间点。

(3)截取二进制日志

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000009 | 975 | | | |

+------------------+----------+--------------+------------------+-------------------+

mysql> show binlog events in 'mysql-bin.000009';

| mysql-bin.000009 | 871 | Query | 6 | 975 | drop database oss_base

mysqlbinlog --start-position=120 --stop-position=871 /data/mysql/mysql-bin.000009 >/backup/binlog.sql

(4)恢复二进制日志

mysql> set sql_log_bin=0;

mysql> source /backup/binlog.sql

mysql> show databases;

mysql> use oss_base;

mysql> select * from oss_tab;

_______________________________________________________

测试1： rm -rf /application/mysql/data/数据库删掉 恢复思路？

注意：二进制日志/data/mysql 别删了，生成中binlog文件是和数据文件分开放的，我们这里是放一起了

(1)误删除数据

rm -rf /application/mysql/data/*

(2)关闭数据库

pkill mysqld / kill -9 id / killall mysqld

(3) 初始化数据

/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data/

(4)恢复

(1)检查全备，找到二进制日志截取的起始位置

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=120;

(2)恢复全备

gunzip alL_2010-10-03.sql.gz

set sql_log_bin=0;

source /backup/alL_2019-10-03.sql

到此为止，已经将数据恢复到了昨天23点时间点。

(3)截取二进制日志

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000009 | 975 | | | |

+------------------+----------+--------------+------------------+-------------------+

mysql> show binlog events in 'mysql-bin.000009';

| mysql-bin.000009 | 871 | Query | 6 | 975 | drop database oss_base

mysqlbinlog --start-position=120 --stop-position=871 /data/mysql/mysql-bin.000009 >/backup/binlog.sql

(4)恢复二进制日志

mysql> set sql_log_bin=0;

mysql> source /backup/binlog.sql

mysql> show databases;

mysql> use oss_base;

mysql> select * from oss_tab;

--------------------------------------------------------------------------

思考，如果在drop之后又做了一下其他库的正常修改操作，该怎么恢复？

比如：

create database test;

use test;

create table tab(id int ,name varchar(20));

insert into tab values(1,'a'),(2,'b');

commit;

解决思路：

在binlog里面找到drop的那个位置点，一直到binlog的结束点，把这一段也导入到数据库里面就可以了。

______________________________________________