mysql数据备份

数据备份分类

• 数据库的备份类型

  • 完全备份：对整个数据库的数据进行备份
  • 部分备份：对部分数据进行备份（可以是一张表也可以是多张表）
    • 增量备份：是以上一次备份为基础来备份变更数据的，节约空间
    • 差异备份：是以第一次完全备份的基础来备份变更备份的，浪费空间

• 数据库备份的方式

  • 逻辑备份：直接生成sql语句保存起来，在恢复数据的时候执行备份的sql语句来实现数据的恢复
  • 物理备份：直接拷贝相关的物理数据
  • 区别：逻辑备份效率低，恢复数据效率低，但是逻辑备份节约空间；物理备份浪费空间，但是相对逻辑备份而言效率比较高

• 数据库备份的场景

  • 热备份：备份时，数据库的读写不会受到影响
  • 温备份：备份时，数据库的读操作可以进行，但是写操作不能执行
  • 冷备份：备份时，不能进行任何操作

逻辑备份与恢复mysqldump

• mysqldump使用语法

  mysqldump -u用户 -p -h主机 数据库 表名 > 路径

• 实例

  # 备份单库
  mysqldump -uroot -p -h127.0.0.1 --databases test | gzip > /mysql_data_back/test.sql.gz
  
  # 备份单库单表
  mysqldump -uroot -p -h127.0.0.1 test test | gzip > /mysql_data_back/test.sql.gz
  
  # 备份多库
  mysqldump -uroot -p -h127.0.0.1 --databases test sys | gzip > /mysql_data_back/test.sql.gz
  

• 数据恢复

  mysql -uroot -p -h127.0.0.1 < /mysql_data_back/test.sql
  

物理备份

• 查询数据库源文件路径

  • 方式一：show variables like ‘datadir’;
  • 方式二：配置文件中查看vim /etc/my.cnf

• MyISAM表源文件

  • db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则
  • .frm：记录表结构信息
  • .MYD：记录表数据
  • .MYI：记录索引

• InnoDB表源文件：InnoDB有着共享表空间跟独立表空间的概念

  • db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则
  • .frm：记录表结构信息
  • .ibd：独立表空间，记录这个表的数据和索引
  • ibdata1：共享表空间，记录表的数据和索引（和数据库同级别存放）

物理备份：直接将源文件拷贝，工作中不常用，注意不要拷贝缺失

二进制备份mysqlbinlog （结合mysqldump使用）

• 简介

  • 二进制日志就是记录着mysql数据库中的一些写入性操作，比如增删改，不包括查询
  • 实现增量备份
  • 开启二进制日志会有1%性能消耗

• 查看二进制日志是否开启

  show variables like 'log_bin%';
  

• 开启二进制日志：vim /etc/my.cnf

  [mysqld]
  log-bin=/data/mydata/log_bin/mysql_bin
  server-id=1
  

• binlog相关操作

  -- 查看所有的binlog日志列表
  show master logs;
  
  -- 刷新二进制日志（重新生成新的日志）
  flush logs;
  
  -- 清空二进制日志文件
  reset master;
  

• 使用mysqldump备份数据时，加上-F可以重新生成一个新的二进制日志

  mysqldump -uroot -p -h127.0.0.1 test test -F > /mysql_data_back/test.sql
  

二进制恢复mysqlbinlog

• 查看二进制日志：mysqlbinlog mysql_bin.000002

• 二进制日志文件导出成普通文件

  mysqlbinlog --base64-output=DECODE-ROWS -v mysql_bin.000002 > mysqlbin.sql
  

• 找出要恢复的位置

  # 找出关键字的行数
  mysqlbinlog  --no-defaults mysql_bin.000002 | cat -n | grep -iw 'drop'
  
  # 打印相关内容
  mysqlbinlog --no-defaults mysql_bin.000002 | cat -n | sed -n '100,114p'
  

• 恢复数据

  # 先恢复之前全量备份的数据
  mysql -uroot -p -h127.0.0.1 test < /mysql_data_back/test.sql
  # 恢复增量备份的数据：注意position是文件中的# at 1275
  mysqlbinlog --no-defaults --set-charset=utf8 --stop-position="1275" /data/mydata/log_bin/mysql_bin.000002 | mysql -uroot -p