天天看点
返回

mysqldump 备份 part 1

文章目录

      • mysqldump 备份
        • 认识备份的重要性及备份场景
          • 备份重要性
          • 备份场景
          • 涉及到的技术：
        • 利用 mysqldump 备份
          • mysqldump 是什么？
          • mysqldump 能做什么？
          • 常见用法 (详情请参考 [9.1 mysqldump 数据导出命令详解](http://note.youdao.com/noteshare?id=7d26b38d719c71faf701e36e3868d150&sub=5F8A5EEAB99547888A3B35AC62E8AE45)
            • 备份所有的数据库
            • 还原备份文件
          • 利用select into outfile + awk实现临时备份
        • 生产中 `MySQL` 备份恢复的形式
        • mysqldump 备份原理
          • mysqldump 关键参数含义
          • 观察 `mysqldump` 备份过程
          • mysqldump 最佳实践
            • mysqldump 备份从4点开始，6点结束，问备份出来的文件中的数据是4点时的数据还是6点时的数据？
          • mysqldump 重要参数
        • mydumper 介绍
          • mydumper备份过程
        • 思考
          • 如何从一个`mysqldump`文件中提取其中一个单表
            • 使用 `sed` (该方法速度十分缓慢)
          • 有一个需求，把 test 库下的表，都 dump 成单表存储，如：`t1.sql, t2.sql...` 如何实现
          • 如何从 mysqldump 备份出来的文件中恢复单表
          • 需掌握知识点
        • Thinking

mysqldump 备份

认识备份的重要性及备份场景

备份重要性

重要性：

  1. 备份是DBA的救命稻草
  2. 没有备份，就没有复活的机会
  3. 备份可以让数据回档到某一个时刻

误区：

  1. 备份占用太多资源，也不能促进生产
  2. 可以考虑利用备份恢复生产，提高应用的可用性
  3. 备份可以随便找个地方存放，丟了也无所谓
备份场景
  1. 功能开发环境配置类型库

  2. 测试环境

    性能监控相关的数据

  3. 生产环境

    几乎全备（每天一个全备 +

    binlog

     的备份）
  4. 其它
涉及到的技术：
  1. 备份工具

    mysqldump mydumper xtrabackup
  2. 备份存储归档
    • 商业软件
    • 开源的：ceph（1.2~1.5份）;HDFS（要求数据最少存储两份）
  3. 控制速度

利用 mysqldump 备份

mysqldump 是什么？

mysqldump

是一个逻辑备份工具, 复制原始的数据库对象定义和表数据产生一组可执行的

SQL

语句。 默认情况下, 生成

insert

语句，也能生成其它分隔符的输出或

XML

格式的文件。

mysqldump 能做什么？
  • 使用

    mysqldump

     命令创建

    SQL

     格式的转储文件来备份数据库。
  • 数据导出后做数据迁移，搭建主从。
常见用法 (详情请参考 9.1 mysqldump 数据导出命令详解

备份所有的数据库

  • 备整个库
  • 备份指定的表：

还原备份文件

  • 还原 dump 出来的备份

注意事项

需要一致性的话加上两个参数

--master-data=2 --single-transaction
利用select into outfile + awk实现临时备份 
select id, col1 from zst01.tb1 where id < 1000 into outfile /tmp/zst01_tb1.sql’;
update zst01.tb1 set col1='abc' where id < 1000;
cat zst01_tb1.sql |awk '{print "update zst01.tb1 set col1 ="$2", where id="$1";"}' >roll_zst01_tb1.sql
mysql zst01 <roll_zst01_tb1.sql

生产中

MySQL

 备份恢复的形式

  • 在线基于逻辑备份 
mysqldump & mydumper
mydumper 在 MySQL 5.7 中对 JSON 格式的数据备份不太友好
  • 在线物理备份 
xtrabackup
  • 增量备份 
mysql binary log

优点是数据可还原到任一时间点

mysqldump 备份原理

mysqldump 5.7版本，

--master-data=2 single-transaction

对非事务表的备份是不安全的。要获得非事务表的一致性快照需加上选项

--lock-tables

,该选项会对业务产生一定的影响(备份期间锁住所有表，无法写入数据)，而

--master-data=2 single-transaction

选项对业务的影响很小(1s左右)

mysqldump 关键参数含义

--single-transaction
在一个单独的事务中通过创建所有表的一致性快照来备份所有表，只对存储引擎支持多版本读（当前仅

InnoDB

 支持）的表有效；此备份不能够保证其他存储引擎表中数据的一致性，当一个

--single-transaction

 备份正在运行时，需确保备份是有效的（正确的表数据和日志位置），没有其他连接使用以下语句：

ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE

 ,因为一致性快照与此相互关联。选项

--lock-tables

 自动关闭

--master-data=2
该选项会将二进制日志文件名和位置追加到转储文件中。若

--master-data=1

 ，将以

CHANGE MASTER

 命令的形式输出，若

--master-data=2

 ，该命令将会被注释掉。除非同时指定选项

--single-transaction

 ,否则

--master-data

 选项将导致选项

--lock-all-tables

 默认打开，无论在哪种情况下，备份时肯定会更新日志。选项

--lock-tables

 自动关闭。
观察

mysqldump

 备份过程

mysqldump

备份通过

SQL

与数据库交互，最终产生一个备份，因此可在开启

general log

后，执行备份命令，查看

general log

可观察整个备份过程

  • 开启

    general log
  • 备份命令
  • 备份过程

mysqldump

备份原理示意图

time command(session 1)
t0 flush tables
t1 FTWRL
t2 set session ios_lation rr
t3 start trx
t4 show master status
t5 get gtid
t6 unlock tables
t7 savepoint sp
t8 rollback to savepoint sp
t9 unlock tables

general log

日志记录

# flush tables 操作关闭所有已打开的表，强制关闭所有正在使用的表，并刷新 prepare 语句缓存
# 该操作可以防止 mysqldump 时实例上没有发生以下几种情况: a. 表结构修改操作 b. 大事务 c. 较长时间的 select 查询。即有这些操作时，flsuh tables 会被阻塞，从而阻止备份数据
1.flush tables;
# FTWRL 关闭所有已经打开的表，并使用全局读锁来锁住所有数据库中的所有表，从而使实例处于只读状态，无法写入
2.flush tables with read lock;
#设置会话隔离级别为 RR，防止幻读、不可重复读
3.set session transaction isolation level repeatable read
#开启一致性快照事务
4.start transaction /*!40100 with consistent snapshot*/
#若是 GTID 环境,
5.SHOW VARIABLES LIKE 'gtid\_mode'
#获取当前已执行的 GTID
5.select @@global.gtid_executed
#获取 binlog 文件名及相关位置
6.show master status;
#释放 FTWRL LOCK, 释放后事务仍旧继续
7.unlock tables;
8.show databases;
9.show create database if not exists 'dbname';
#事务保存点，备份每张表的起始时间都是从同一个事务点开始的
10.savepoint sp;
11.show create table 'tb_xx';
12.select /!40100 $QL_NO_CACHE*/ * from tb_xx;
13.rollback to savepoint sp;
14....
15.rollback to savepoint sp; 
16.RELEASE SAVEPOINT sp;
#断开连接，原事务自动停止
17.quit
mysqldump 最佳实践

mysqldump

的缺点：

  1. 单进程 dump
  2. 所有结果保存在一个文件中，提取单个表比较困难

第一种方式

第二种方式

推荐

  • 数据压缩：

    pigz

     或者

    zstd

     替换

    gzip

  • mydumper

     替换

    mysqldump

mysqldump 备份从4点开始，6点结束，问备份出来的文件中的数据是4点时的数据还是6点时的数据？

答：4点时数据。准确时间应为实例加上

FTWRL LOCK

的时间 ，而该过程大约只花费 1s 左右的时间。

mysqldump 重要参数

若表中有触发器，存储过程，那么在备份时需要注意什么？

mysqldump 的Tips

一般有触发器和存储过程时，建议将每个部分单独备份一份,备份时也要加上这两个参数

--single-transaction --master-data=2

，减小对数据库的影响

  1. 表结构
  2. 数据
  3. 存储过程
  4. 触发器
  5. event

  • --triggers

     Dump triggers for each dumped table. (Defaults to on; use --skip-triggers to disable.)

  • -d --no-data

     No row information

  • -t --no-create-info

     Don’t write table creation info.

  • -n, --no-create-db

     Suppress the CREATE DATABASE … IF EXISTS statement that normally is output for each dumped database if --all-databases or --databases is given.

  • -R, --routines

     Dump stored routines (functions and procedures).

  • -E, --events

     Dump events.

  • -B, --databases

     Dump several databases. Note the difference in usage; in this case no tables are given. All name arguments are regarded as database names. ‘USE db_name;’ will be included in the output.

  • --tables

     Overrides option --databases (-B).

  • -w --where=name

     Dump only selected records. Quotes are mandatory.

应用案例：快速测试对一个上百

G

的库作字符集转码可能造成的影响

mysqldump --where="1=1 limit 1000" db1 >db1_1000.sql
相当于
Select * from tb where 1=1 limit 1OOO;

mydumper 介绍

mysqldump 是单线程进行数据库备份的工具，在数据量比较大时，备份速度下降十分严重，mydumper 支持并行备份，从而提高备份速度，缩小备份耗费时间。

参考文章

MySQL 多线程备份工具 Mydumper详解

项目地址

特性：

  • 并行，高性能的 MySQL 逻辑导入导出工具
  • 非常方便管理导出的文件（文件拆分成单表存储, metadata 信息)
  • 导入时支持正则表达式

mydumper

备份原理示意图

time master_thread worker_thread
t1 flush tables
t2 FTWRL
t3 set session ios_lation rr
t4 get gtid
t5 show master status
t6 set session ios_lation rr
t7 start trx
t8 unlock tables
mydumper备份过程
  1. 主线程

    FLUSH TABLES WITH READ LOCK

     ,施加全局只读锁，以阻止

    DML

     语句写入，保证数据的一致性
  2. 读取当前时间点的二逬制日志文件名和日志写入的位置并记录在

    metadata

     文件中，以供即使点恢复使用
  3. N个（线程数可以指定，默认是4) dump线程

    START TRANSACTION WITH CONSISTENT SNAPSHOT

     ;幵启读一致的事务

  4. dump non-InnoDB tables

     ,首先导出非事务引擎表
  5. 主线程

    UNLOCK TABLES

     ,非事务引擎表备份完后，释放全局只读锁 ;

  6. dump InnoDB tables

     ,基于事务导出

    InnoDB

     表
  7. 事务结束

思考

如何从一个

mysqldump

 文件中提取其中一个单表

使用

sed

(该方法速度十分缓慢)

  1. 使用 mydumper (文件拆分成单表存储)
有一个需求，把 test 库下的表，都 dump 成单表存储，如：

t1.sql, t2.sql...

 如何实现

使用

mydumper

备份数据

如何从 mysqldump 备份出来的文件中恢复单表

思路：

使用权限，给某用户只授权该表的权限，使用参数

-f

忽略报错导入数据

假设要恢复的表为

db1.t2
  1. 创建

    tb_recovery

     数据库, 用来存放要恢复的单表
  1. 创建用户

    'user_tb_recovery'@'%'

     ,并只对其赋予

    tb_recovery.t2

     表的所有权限 
create user 'user_tb_recovery'@'%' identified by 'user_tb_recovery';
grant all privileges on tb_recovery.t2 to 'user_tb_recovery'@'%'; 
flush privileges;
  1. 使用

    -f

     选项导入备份文件
  1. 若原表

    db1.t2

     还存在，则将其改名为

    db1.t2_bak

     ,待恢复确认无误后删除。
  1. 将步骤 3 恢复出来的表

    tb_recovery.t2

     移到

    db1

     库下
需掌握知识点
  1. 学会利用

    mysqldump

     怎么拿到一致性备份
  2. 学会利用

    mysqldump

     只备份需要的数据

    --where="add_time >= xxx and add_time <= xxx"
  3. (重点）知道

    mysqldump

     原理获取及解读，讲解出来

  4. mydumper

     下载编译一下，并行导出导入，动手做一下,知道

    mydumper

     原理获取及解读，讲解出来

Thinking

  1. zstd, pigz, gzip 压缩算法的比较，比较维度：1. 压缩耗费时间 2. 压缩比
    • zstd 和 pigz 可利用多核，gzip 只能用单核
mysql mysqldump 逻辑备份
上一篇: oracle delete释放表空间,Oracle delete数据后的释放表空间问题的解决 --转
下一篇: mysql恢复delete数据_mysql数据delete后的数据恢复

继续阅读