MySQL备份

关于MySQL备份的4种方式，适合企业环境下实操！MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。

MySQL备份

• 为什么需要备份
  • 硬件故障
  • 软件故障
  • 自然灾害
  • 黑客攻击
  • 误操作 (占比最大)
  • 数据备份策略参考数值
• 数据的备份类型
  • 完全备份
    • 完全备份指的是备份整个数据集( 即整个数据库 )
  • 部分备份
    • 部分备份指的是备份部分数据集(例如: 只备份一个表)
    • 增量备份
      • 增量备份指的是备份自上一次备份以来(增量或完全)以来变化的数据; 特点: 节约空间、还原麻烦
    • 差异备份
      • 差异备份指的是备份自上一次完全备份以来变化的数据 特点: 浪费空间、还原比增量备份简单
• MySQL备份数据的方式
  • 热备份
    • 热备份指的是当数据库进行备份时, 数据库的读写操作均不是受影响
  • 温备份
    • 温备份指的是当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作
  • 冷备份
    • 冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线
  • 注意点
    • MySQL中进行不同方式的备份还要考虑存储引擎是否支持
    • MyISAM
      • 热备份
      • 温备份
      • 冷备份
    • InnoDB
      • 热备份
      • 温备份
      • 冷备份
  • 其他
    • 物理备份一般就是通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果 逻辑备份一般就是通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)
• 备份要考虑的问题
  • 我们要备份什么
    • 数据
    • 二进制日志, InnoDB事务日志
    • 代码(存储过程、存储函数、触发器、事件调度器)
    • 服务器配置文件
  • 备份工具
    • mysqldump
      • 逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备
    • cp, tar 等归档复制工具
      • 物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份
    • lvm2 snapshot
      • 几乎热备, 借助文件系统管理工具进行备份
    • mysqlhotcopy
      • 名不副实的的一个工具, 几乎冷备, 仅支持MyISAM存储引擎
    • xtrabackup
      • 一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份, 由percona提供
• 设计合适的备份策略
  • 直接cp,tar复制数据库文件
    • 如果数据量较小, 可以使用这种方式, 直接复制数据库文件
  • mysqldump+复制BIN LOGS
    • 如果数据量还行, 可以使用这种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果
  • lvm2快照+复制BIN LOGS
    • 如果数据量一般, 而又不过分影响业务运行, 可以使用这种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果
  • xtrabackup
    • 如果数据量很大, 而又不过分影响业务运行, 可以使用这种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份
• 实战演练
  • 准备环境
    • MySQL5.7
    • Centos7.6
    • 数据库：test; 三张表
  • 4种备份方式
    • cp备份
      • 进入到数据库
        • SHOW DATABASES; #查看当前的数据库 use test; #使用test数据库 SHOW TABLES; #查看当前库中的表
      • 向数据库施加读锁，关闭MySQL服务
        • FLUSH TABLES WITH READ LOCK; #向所有表施加读锁 ----------------------------------------- systemctl stop mysqld #关闭MySQL服务，其实上面的可以不用
      • 备份数据文件
        • mkdir /backup #创建文件夹存放备份数据库文件 cp -a /var/lib/mysql/* /backup #保留权限的拷贝源数据文件 ls /backup #查看目录下的文件
      • 模拟数据丢失并恢复
        • 删除数据 rm -rf /var/lib/mysql/* #删除数据库的所有文件 --------------------------------------------------------------------------------------------------------------------- 恢复数据 cp -a /backup/* /var/lib/mysql/ #将备份的数据文件拷贝回去 systemctl resart mysqld 重新连接数据并查看,show databases; show tables;
    • mysqldump+复制BINARY LOG备份
      • mysqldump是一个客户端的逻辑备份工具, 可以生成一个重新创建原始数据库和表的SQL语句, 可以支持所有的存储引擎, 对于InnoDB支持热备
      • 备份步骤
        • 通过mysqldump进行一次完全备份, 再修改表中的数据, 然后再通过binary log进行恢复 二进制日志需要在mysql配置文件（/etc/my.cnf）中添加 log_bin=on 开启，并且加上参数 server-id=1
      • 
      • 查看当前二进制文件的状态, 并记录下position的数字
        • mysql -u root -p #登录MySQL show master status #查看二进制文件状态，记录position的数字
        • 
      • 备份数据库到backup.sql文件
        • #备份数据库到backup.sql文件中 mysqldump -u root -p --all-databases --lock-all-tables >backup.sql 提示输入密码，即可在当前路径查看backup.sql文件
        • 
      • 创建一个数据库
        • create database test2;
        • 
      • 记录下现在的position
        • SHOW MASTER STATUS;
        • 
      • 备份二进制文件
        • cp /var/lib/mysql/on.000002 /root
        • 
      • 停止MySQL，删除所有的数据
        • systemctl stop mysqld #停止MySQL服务 rm -rf /var/lib/mysql/* #删除所有的数据文件 真实环境下，可能在服务器启动时删除，下次再测试下 如果此时再去启动MySQL，默认会初始化，密码会还原，需要去日志中查看密码登录MySQL
      • 启动MySQ，将二进制日志关闭
        • SET sql_log_bin=OFF;
      • 恢复数据
        • source backup.sql #恢复数据，所需时间根据数据库时间大小而定 该命令执行需要登录到MySQL中，并且在backup.sql目录下登录MySQL
      • 开启二进制日志，通过二进制日志增量恢复数据
        • #开启二进制日志 SET sql_log_bin=ON; #通过二进制日志增量恢复数据，执行该命令需要在shell终端下的on.000002文件目录下，即前面备份的/root目录下 mysqlbinlog --start-position=154 --stop-position=456 on.000002 | mysql -u root -p ----------------------------------------------------------------------------- #也可以指定开始和结束日期范围内恢复数据 mysqlbinlog --start-date="2021-06-24 16:30:00" --stop-date="2021-06-25 17:16:00" on.000002 |mysql -uroot -p ---------------------------------------------------------------------------- 重新登陆MySQL，输入：show databases; ,test2数据库已恢复
    • lvm2快照备份数据
      • 模拟的场景，首先需要硬盘设备为lvm分区。 然后将MySQL的datadir，即MySQL的数据目录设置为逻辑卷下挂载的目录 然后创建逻辑卷的快照卷，并做打包备份 最后模拟删除数据，删除卷组，快照卷都可。 最后只需要切换到数据库的data目录中，将备份的快照卷内容解压到该目录下即可
      • 部署lvm环境
        • 添加硬盘; 这里我们直接实现SCSI硬盘的热插拔, 首先在虚拟机中添加一块硬盘, 不重启
        • 虚拟机添加硬盘，进行分区，创建lvm逻辑卷，格式化后，进行挂载
          • ls /dev/sd*
          • fdisk /dev/sdb #分区
            • https://www.runoob.com/linux/linux-comm-fdisk.html 以上链接可查看fdisk各个参数的含义
          • 输入：n,表示新建分区
          • 之后看英文意思，不需要特殊设置，一路回车，最后分区完成，输入：w，保存分区
          • fdisk -l,查看设备是否分区
          • 创建逻辑卷
            • pvcreate /dev/sdb1 ；将设备创建为物理卷
            • vgcreate myvg /dev/sdb1；创建卷组
            • lvcreate -n mydata -L 5G myvg ；在该卷组下创建逻辑卷，并分配大小
            • mkfs.ext4 /dev/mapper/myvg-mydata #格式化
            • mkdir /lvm_data ；创建挂载目录
            • mount /dev/mapper/myvg-mydata /lvm_data #挂载到/lvm_data
          • 修改mysql配置文件的datadir，重启MySQL服务
            • vim /etc/my.cnf
            • datadir=/lvm_data
      • 创建快照卷并备份
        • FLUSH TABLES WITH READ LOCK; #锁定所有表
        • lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvg-mydata #创建快照卷
        • UNLOCK TABLES; #解锁所有表
        • mkdir /lvm_snap #创建文件夹
        • mount /dev/myvg/mydata-snap /lvm_snap/ #挂载snap
        • 进入/lvm_snap，会发现该目录下已经有了MySQL文件
        • tar cf /tmp/mysqlback.tar * #打包文件到/tmp/mysqlback.tar
          • 这个操作实际上是将lvm_snap下的MySQL文件打包备份
        • umount /lvm_snap/ #卸载snap
        • lvremove myvg mydata-snap #删除snap
        • rm -rf /lvm_data/*
      • 恢复数据
        • 登录到MySQL，密码可在/var/log/mysqld.log获取
        • cd /lvm_data/
        • rm -rf * #删除所有文件
        • tar xf /tmp/mysqlback.tar #解压备份数据库到此文件夹
        • 查看数据库，数据已经恢复
    • Xtrabackup备份
• 浮动主题