MySQL数据库(十五)_备份与恢复

By | 2019年 11月 23日
0 Comment

备份和恢复

  1. 为什么要备份
    灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景
  2. 备份注意要点
    能容忍最多丢失多少数据
    恢复数据需要在多长时间内完成
    需要恢复哪些数据

一定要做还原测试，用于测试备份的可用性， 还原演练

  1. 备份时需要考虑的因素
    温备的持锁多久
    备份产生的负载
    备份过程的时长
    恢复过程的时长
  2. 备份什么
    数据
    二进制日志、InnoDB的事务日志
    程序代码（存储过程、函数、触发器、事件调度器）
    服务器的配置文件

备份类型：

  1. 按不同类型分：
    • 冷备：读写操作均不可进行，数据库停止状态
    • 温备：读操作可执行；但写操作不可执行
    • 热备：读写操作均可执行，数据库运行状态
      MyISAM：温备，不支持热备
      InnoDB：都支持
  2. 按备份的内容来分：
    • 完全备份：整个数据集
    • 部分备份：只备份数据子集，如部分库或表
    • 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
    • 差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
      注意：二进制日志文件不应该与数据文件放在同一磁盘
  3. 按备份后的内容来分：
    • 物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
    • 逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

备份工具

  1. cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
  2. LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
  3. mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
  4. xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
  5. MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
  6. mysqlbackup：热备份， MySQL Enterprise Edition组件
  7. mysqlhotcopy：几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

基于LVM的备份

  1. 请求锁定所有表
    mysql> FLUSH TABLES WITH READ LOCK;
  2. 记录二进制日志文件及事件位置
    mysql> FLUSH LOGS;
    mysql> SHOW MASTER STATUS;
    mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE
  3. 创建快照
    lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
  4. 释放锁
    mysql> UNLOCK TABLES;
  5. 挂载快照卷，执行数据备份
  6. 备份完成后，删除快照卷
  7. 制定好策略，通过原卷备份二进制日志

逻辑备份工具：mysqldump, mydumper, phpMyAdmin
Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件

mysqldump工具

mysqldump参考： https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

客户端命令，通过mysql协议连接至mysql服务器进行备份

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
mysqldump [OPTIONS] –A [OPTIONS]
  • mysqldump常见选项：

mysqldump [options] [db_name [tbl_name ...]]

-A   #备份所有数据库，含create database
-B db_name…   #指定备份的数据库，包括create database语句
-E   #备份相关的所有event scheduler
-R   #备份所有存储过程和自定义函数
--triggers   #备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8    #指定字符集
--master-data[=#]  #此选项须启用二进制日志
    "1"：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
    "2"：记录为注释的CHANGE MASTER TO语句
    此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）

-F   #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次日志
--compact   #去掉注释，适合调试，生产不使用
-d    #只备份表结构
-t    #只备份数据,不备份create table
-n    #不备份create database，可被-A或-B覆盖
--flush-privileges  #备份mysql或相关时需要使用
-f          #忽略SQL错误，继续执行
--hex-blob  #使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q    #不缓存查询，直接输出，加快备份速度
  • MyISAM备份选项：

支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
锁定方法如下：

-x,--lock-all-tables    #加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能
#注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables    #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用
  • InnoDB备份选项： 支持热备，可用温备但不建议用
--single-transaction
#此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE
#此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥
#备份大型表时，建议将--single-transaction选项和--quick结合一起使用

mysqldump的示例

  1. 备份所有数据库
[centos]$ mysqldump -A > dump.sql
  1. 指定备份的数据库
#指定数据库备份
[centos]$ mysqldump -B hellodb > /data/hellodb.sql
  1. 对架构进行备份
#其实备份的内容就是表结构和数据，使用的SQL语句表示
[centos]$ mysqldump --single-transaction test > test_backup.sql
  1. 还原数据库
mysql db_name < dump.sql
  1. 还原二进制日志binlog.00001
mysqlbinlog binlog.00001 | mysql -u root -p test

#同时恢复多个
mysqlbinlog binlog.0000[1-10] | mysql -u root -p test

#还可以这样还原
mysqlbinlog binlog.00001 > /tmp/test.sql
mysqlbinlog binlog.00002 >> /tmp/test.sql
mysql -u root -p -e "source /tmp/test.sql"
#可以用--start-position和--stop-position来指定偏移量
  1. 出错：unknown variable...
[centos]$ mysqldump -A > all_mysql.sql
mysqldump: unknown variable 'datadir=/data/mysql/'

解决：把配置文件下所有[mysqldump]不相关的配置都删除了，如果不清楚

[mysqldump]
...

生产环境实战备份策略

  1. InnoDB建议备份策略
mysqldump –uroot –A –F –E –R --single-transaction 
--master-data=1 --flush-privileges --triggers 
--default-character-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql
  1. MyISAM建议备份策略
mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注