MySQL数据库备份

数据库备份分为 

• 物理备份
  • 对数据操作系统的物理文件（如数据文件、日志文件等）的备份
• 物理备份方法
  • 冷备份（脱机备份）：是在关闭数据库的时候进行的
  • 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
  • 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
• 逻辑备份：对数据库逻辑组件（如表等数据库对象）的备份

数据库的备份策略

• 完全备份：每次对数据库进行完整的备份
• 差异备份：备份自从上次完全备份之后被修改过的文件
• 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

 

常见备份方法

• 物理冷备

  • 备份时数据库处于关闭状态，字节打包数据库文件
  • 也就是将MySQL的数据目录（配置文件/etc/my.cnf可以查看数据根目录时哪个大概率是data）进行tar打包到备份目录（自定义的）
  • scp可以将文件上传到别的linux服务器上
  • scp 文件目录 别的服务器的ssh用户@别的服务器IP:要上传到的位置
  • 恢复是将备份的tar文件解压，将解压后的数据目录更改位置到要恢复的MySQL的数据目录上
  • 备份数据块，恢复时也是最简单的

• 专用备份工具mysqldump或mysqlhotcopy

  • mysqldump常用的逻辑备份工具
    • # 备份指定库中的部分表
      • # mysqldump [选项] 库名 [表名 1] [表名 2] … > /备份路径/备份文件名
      • 示例

        • mysqldump -uroot -p mysql user > mysql_user.sql

    • # 备份一个或多个完整的库（包括其中所有的表）
      • # mysqldump [选项] --databases 库名 1 [库名 2] … > /备份路径/备份文件名
      • 示例

        • mysqldump -uroot -p --databases mysql > mysql.sql

    • # 备份 MySQL 服务器中所有的库，完全备份
      • mysqldump [选项] --all-databases > /备份路径/备份文件名
      • -A 是 --all-databases的简写
      • 示例

        • mysqldump -uroot -p --all-databases > all-data.sql
          或
          mysqldump -uroot -p -A > all-data.sql

    • #恢复备份

      • mysql -uroot -p < 数据库备份文件
      • 示例

        • mysql -uroot -p < all-data.sql

  • MySQLhitcopy仅拥有备份MyISAM和ARCHIVE存储引擎的表

• 启用增量备份

  • 进行增量备份。需要刷新二进制文件（需要开启二进制文件）
    • 开启二进制日志文件
    • 示例

      • 在MySQL配置文件/ect/my.cnf添加[mysqld]下添加以下三行
        #启用了MySQL的二进制日志功能二进制文件名字
        log-bin=mysql-bin
        #二进制文件格式
        binlog_format = MIXED
        #设置MySQL服务器的唯一ID为1
        server-id=1

    • 手动增量备份

    • 示例

      • #刷新日志
        mysqladmin -uroot -p flush-logs
        日志文件会在/usr/local/mysql/数据目录/下一个定义的二进制名字的文件

    • 查看二进制日志

    • 示例

      • mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002（二进制日志文件路径）

      • 

  • 增量备份恢复

    • 文件全部恢复

    • 示例

      • #--no-defaults  不恢复配置文件内容
        mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -uroot -p

    • 基于偏移量恢复

    • 示例

      • 从哪里开始恢复，到哪里结束
        mysqlbinlog --no-defaults --start-position='偏移量' --stop-position='偏移量' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
        从文件开头进行恢复，到哪里结束
        mysqlbinlog --no-defaults --stop-position='偏移量' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
        从哪里进行恢复，到文件末尾结束
        mysqlbinlog --no-defaults --start-position='偏移量' /mysql_bak/mysql-bin.000002 | mysql -uroot -p

    • 基于日期恢复

    • 示例

      • 时间格式 年-月-日 时:分:秒
        从哪里开始恢复，到哪里结束
        mysqlbinlog --no-defaults --start-datetime='时间' --stop-datetime='时间' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
        从文件开头进行恢复，到哪里结束
        mysqlbinlog --no-defaults --stop-datetime='时间' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
        从哪里进行恢复，到文件末尾结束
        mysqlbinlog --no-defaults --start-datetime='时间' /mysql_bak/mysql-bin.000002 | mysql -uroot -p

  • 第三方工具
    • 免费的MySQL热备份软件Percona XtraBackup