备份和恢复

备份和恢复

可以处理的崩溃类型

自动故障恢复 (CSR)

  • 操作系统崩溃
  • 电源故障

只能使用备份进行恢复 (磁盘数据不可读)

  • 文件系统崩溃
  • 硬件故障

备份工具

  • mysqldump (MDP)

  • percona Xtrabackup (XBK/PBK)

  • MySQL Enterprise Backup (MEB)

  • mysqlbinlog

备份和恢复类型

物理备份 VS 逻辑备份

  • 物理备份

    • 适合大型备份
    • 备份速度比逻辑备份快
      • 因为只涉及文件复制,不涉及转换
    • 备份粒度
      • 从整个数据目录到单个文件,根据存储引擎不同,有可能到表
    • 备份时需要停库或锁定

    • MEMRORY 表无法使用物理备份,因为数据不在磁盘上
    • 备份工具

      • XtraBackup
  • 逻辑备份
    • 适合小型备份
    • 备份比物理备份慢
      • 因为必须访问数据库信息并转换逻辑格式
    • 备份粒度
      • 服务器级别-数据库级-表级
    • 高度可移植性

    • 可以在线备份
    • 适合于任何存储引擎 (包括 MEMORY)
    • 备份工具

      • mysqldump
      • binlog (flush logs + cp/scp)

在线备份 VS 离线备份

  • 在线备份
    • 特点
      • 对其他客户端的干扰较小
      • 必须注意施加适当的锁定,保证完整性
      • 还原数据时必须防止客户端访问数据
    • 类型
      • 热备 (InnoDB)
        • 对业务影响最小
      • 温备 (MyISAM)
        • 长时间锁表
  • 离线备份 (冷备)
    • 客户端可能会受到不利影响,因为备份期间服务器不可用
      • 通常是从副本服务器上获取
    • 备份过程更简单,因为不可能受到客户端活动的干扰

本地备份 VS 远程备份

  • mysqldump

    • 本地或远程启动,输出到客户端
  • SELECT …INTO OUTFILE
    • 本地或远程启动,输出到服务器端
  • 物理备份
    • 本地启动,本地或远程输出

快照备份

  • MySQL 本身不提供获取文件系统快照的功能。它可以通过 Veritas,LVM 或 ZFS 等第三方解决方案获得

全备 VS 增量

  • 增量备份需要启动二进制日志

完全恢复 VS 时间点恢复

  • 时间点恢复基于二进制日志

备份方式

使用 MySQL 企业版进行热备

利用 mysqldump 进行备份

  • –single-transaction
    • 不锁定表的在线备份

通过拷贝表文件进行备份

  • 通过复制文件来备份表

  • 为了保证一致性,需要停止服务器,或锁定 + 刷新表

    • 刷新 + 读锁
      FLUSH TABLES tbl_list WITH READ LOCK;
      
      • 刷新是为了将脏数据写入磁盘
      • 读锁使其他客户端可以继续查询

进行分隔文本文件备份 (导出数据)

  • 导出数据
    • SELECT * INTO OUTFILE ‘file_name’ FROM tbl_name
    • mysqldump –tab
  • 重新加载带分隔符的数据文件
    • load data
    • mysqlimport
      • mysqlimport 是 load data 的接口

启用二进制日志做增量备份

  • 从上次全备或增量备份以来新的增量备份,实际就是这些二进制日志
    • 先 flush logs 刷新到磁盘,然后备份所有的二进制日志,这些二进制日志就是增量备份

利用副本进行备份

  • 可以避免主节点的性能问题
  • 需要备份副本库的源信息、中继日志信息存储库
    • 还有 load data 目录中的文件
    • 系统变量
      • slave_load_tmpdir
        • load data 的导出目录

恢复损坏的表 (MyISAM 表)

  • REPAIR TABLE
  • myisamchk -r

利用文件系统的快照进行备份

备份策略

必须启用二进制日志,且二进制日志要放到安全介质上。

定期进行全备

  • mysqldump

定期进行增量备份

  • flush logs
  • mysqladmin flush-logs

全备和增量备份配合使用

可以提高备份效率。

  • 刷新 (轮换) 二进制日志

    刷新日志,会关闭当前二进制日志,并手动开启一个新的日志文件。

    • flush logs
    • mysqladmin flush-logs
  • 举例说明

    星期日,全备

    星期一,fush logs,生成 mysql-bin.000002,那么星期日到星期一之间的增量备份就是 mysql-bin.000001

    星期二,flush logs,生成 mysql-bin.000003,那么星期一到星期二之间的增量备份就是 mysql-bin.000002

  • 释放二进制日志

    • 全备时删除二进制日志
      shell> mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > backup_sunday_1_PM.sql
      

      Note: 复制结构中,从节点这样删除很危险,因为有可能服务器有尚未处理的二进制日志。

    • 手动删除二进制日志

      PURGE BINARY LOGS
      
    • 设置过期时间
      [mysqld]
      binlog_expire_logs_seconds = 604800  # 7天
      

检查备份可用性

根据定时任务 crontab -l ,查看备份脚本,确定备份路径和日志。

  • 检查备份日志
  • 检查备份文件大小
  • 检查备份文件内容
  • 定期做恢复演练

mysqldump

mysqldump 开始时,先对所有表获取全局读锁,然后读取二进制日志坐标并释放锁。

mysqldump 生成的 .sql 文件包含一组 SQL INSERT 语句,可在重新加载转储的表。

在 MySQL 客户端程序中会详细讲解 mysqldump 的使用方法。

备份 (在线逻辑备份)

  • 常用选项

    这里只是部分选项。

    • 筛选选项
      • 基础筛选选项
        • –all-databases, -A
          • 备份所有库

            在 8.0 版本之前,–routines 和 –events 包含的存储过程、函数、事件都保存在 mysql.proc 和 mysql.event 表中,所以备份所有库时,不需要单独指定这两个选项。

            但是,8.0 版本删除了这两个 mysql 表,相应的对象存储在数据字典中,所以需要单独指定这两个选项。

        • 导出文件中包含 create database 和 use 语句

      • –databases, -B

        • 备份某些库,此选项必须在命令行使用
          • 导出文件中包含 create database 和 use 语句
          • 备份单库时,可忽略此选项
            shell> mysqldump test > dump.sql
            shell> mysqldump --databases test > dump.sql
          
          • 如果没有 –databases,导出文件中不包含 create database 或 use 语句
            • 重新加载导出文件时,必须指定一个默认库名 (use 语句)
            • 重新加载时,可以指定与原库不同的库名
            • 如果要加载的库不存在,必须创建
            • –add-drop-database 选项不起作用,因为导出文件中不包含 create database 语句
      • –events, -E
        • 输出事件。包含 CREATE EVENT 语句。
      • –routines, -R
        • 输出存储例程 (存储过程和函数) ,输出中包含 CREATE PROCEDURE 和 CREATE FUNCTION 语句
      • –triggers
        • 输出触发器。默认已启用,可以使用 –skip-triggers 禁用此功能。
    • 表筛选选项
      • –no-data, -d
        • 不备份表数据,只记录表结构
          • 可以通过此选项导出数据库中的所有表结构,用于备份,或者开发时做影响评估
      • –tables
          mysqldump -uroot -p -S /tmp/mysql.sock -B test --tables t1 t2 > /tmp/test_t1_t2.sql
        
        • 指定要备份的表,此选项必须在命令行使用。选项后面的所有名称都将视为表名称,会覆盖 –databases 或 -B 选项
    • –ignore-*
      • –ignore-error=error[,error]…
        • 忽略指定的错误。多个选项值之间用逗号分隔
          • –force 是忽略所有错误,优先级更高。
      • –ignore-table=db_name.tbl_name
        • 备份时忽略的表或视图。必须同时指定库名和表名。
          • 如果指定多个表,要多次使用此选项。
    • –where=’where_condition’, -w ‘where_condition’
      • 指定 WHERE 条件
          --where="user='jimf'"
          -w"userid>1"
          -w"userid<1"
        
    • DDL 选项
      • –add-drop-database
        • 重建库前删除原有的库
    • 事务选项
      • –single-transaction
        • 保证数据一致性。
      • 指定此选项,对于 InnoDB 的表,进行一致性快照备份,不会锁表 (MVCC)

      • –flush-logs

        • 在备份之前刷新日志 (便于找二进制日志的起点)
    • 主从复制的选项
      • –master-data[=#]
        • 作用
          • 记录备份时刻二进制日志的位置和文件名
        • 锁表
          - 不加 --single-transaction,会自动锁表 (--lock-all-tables) ,温备
          
          • 加 –single-tranction,InnoDB 不锁表 (快照备份)
        • 变量值
          [root@yingzai backup]# diff 1_student.sql 2_student.sql 
          30c30
           -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=591;
          68c68
          
          • 1:内容中二进制日志信息不加注释
        • 2:内容中二进制日志信息加注释

    • 格式选项

      • –tab=dir_name
        • 为每个导出表生成两个输出文件
          shell> mysqldump --tab=/tmp db1
          
          • tbl_name.txt
            • 以 tab 作为分隔符,无引号包含列值的数据文件。存储在服务器端
          • 服务器端使用 select … into outfile 写入文件
            - 需要 FILE 权限
            
            • 如果 .txt 文件已存在,则报错
          • tbl_name.sql
            • 建表语句。存储在客户端
        • 不带 –tab 写入标准输出

      • –tab 只适合本地导出

      • 其他分隔符

        以逗号分隔列,以双引号包含列值,以\r\n分隔行

        shell> mysqldump --tab=/tmp --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1
        

        Note: 如果使用了特殊格式导出,加载时必须指定相同的格式,才能解释文件内容。

        • –fields-terminated-by=str
          • 列分隔符,默认为 tab
        • –fields-enclosed-by=char
          • 包含列值的字符,默认无
        • –fields-optionally-enclosed-by=char
          • 包含非数字列值的字符,默认无
        • –fields-escaped-by=char
          • 转义字符,默认不转义
        • –lines-terminated-by=str
          • 行分隔符,默认为换行符
    • 分别导出表结构和数据
      shell> mysqldump --no-data test > dump-defs.sql
      shell> mysqldump --no-create-info test > dump-data.sql
      shell> mysqldump --no-data --routines --events test > dump-defs.sql
      
      • –no-data
        • 不导出数据
      • –no-create-info
        • 不导出定义语句
    • 扩展选项
      • –set-gtid-purged=value
        • 在基于 GTID 模式的复制结构中,控制 SET @@GLOBAL.gtid_purged 语句和 SET @@ SESSION.sql_log_bin = 0 语句。如果启用了 GTID 模式,则默认启用此选项。
      • 对于 5.6、5.7 版本,如果 dump 文件中包含系统表,则不建议在启用 GTID 时加载 dump 文件
        • 选项值
          • AUTO
            • (默认) 在启用 GTID 的服务器上自动启用此选项
          • OFF
            • 禁用。
          • 禁用后,dump 文件中不会出现 GTID 信息。

          • ON

            • 开启。如果设置了此选项,但是服务器未启用 GTID ,则报错。
              --
              -- GTID state at the beginning of the backup 
              --
              
              SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '32e96078-8ec2-11eb-b651-000c290007bf:1-72,
              aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-13';
              
              
          • COMMENTED

          • 以注释的形式记录 SET @@GLOBAL.gtid_purged 语句,正常记录 SET @@ SESSION.sql_log_bin = 0 语句。

              --
            -- GTID state at the beginning of the backup 
              --
            
              /* SET @@GLOBAL.GTID_PURGED='+32e96078-8ec2-11eb-b651-000c290007bf:1-72,
              aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-13';*/
            
            
      • –max-allowed-packet=#
        • 控制备份时传输数据包的大小,如 –max_allowed_packet=128M
  • 语法
    mysqldump [OPTIONS] database [tables]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    
    • 示例
      shell> mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql
      shell> mysqldump --single-transaction --flush-logs --master-data=2  --all-databases > backup_sunday_1_PM.sql
      

恢复

  • 恢复全备
    shell> mysql  mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql
    shell> mysqlbinlog gbichot2-bin.000009 ... | mysql
    
  • 重新加载 SQL 格式的备份
    • 如果是 –databases 导出文件
      shell> mysql  source dump.sql
      
      • 包含 create database 语句,可以直接导入
    • 如果未使用 –databases 导出文件
      shell> mysqladmin create db1
      shell> mysql db1  CREATE DATABASE IF NOT EXISTS db1;
      mysql> USE db1;
      mysql> source dump.sql
      
      • 先建库,然后导入。因为备份中没有 use 语句
  • 重新加载定界文本格式备份
    • 加载 –tab 导出的文件

      –tab 导出的文件,加载时要先加载 .sql 文件创建表结构,然后加载 .txt 文件导入数据。

      • mysqlimport
        shell> mysql db1  mysqlimport db1 t1.txt
        
      • load data
        mysql> USE db1;
        mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;
        

        Note: 这两种加载数据的方法,前提都是先创建表结构。

    • 如果导出时有指定格式

      • mysqlimport
        shell> mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt
        
      • load data
        mysql> USE db1;
        mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1 FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
        
  • 时间点恢复
    • 使用全部的二进制日志
      • 查看二进制日志文件列表
        mysql> show binary logs;
        
      • 查看当前二进制日志文件
        mysql> show master status;
        
      • 查看二进制日志内容
        shell> mysqlbinlog binlog_files > tmpfile
        
      • 应用二进制日志事件

        “`shell
        shell> mysqlbinlog binlog_files | mysql -u root -p
        or
        shell> mysql -u root -p mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
        shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!
        or
        shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
        or
        shell> mysqlbinlog binlog.000001 > /tmp/statements.sql
        shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
        shell> mysql -u root -p -e "source /tmp/statements.sql"
        “`

      • 如果开启了 GTID

          shell> mysqlbinlog --skip-gtids binlog.000001 >  /tmp/dump.sql
          shell> mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
          shell> mysql -u root -p -e "source /tmp/dump.sql"
        
        • –skip-gtids
    • 截取部分二进制日志
      • 查找事件时间
        shell> mysqlbinlog   --start-datetime="2020-05-27 12:59:00" --stop-datetime="2020-05-27 13:06:00" \
          --verbose /var/lib/mysql/bin.123456 | grep -C 12 "DROP TABLE"
        
        • 确定日志位置

        • 选项

          这两个选项只能用来查找日志,不建议用于应用日志,丢失事件的风险很高。

          • –start-datetime
          • –stop-datetime
      • 查看事件列表
        mysql> show binlog events in 'mysql-bin.000003';
        +------------------+------+----------------+-----------+-------------+------------------------------------------------------------+
        | Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                       |
        +------------------+------+----------------+-----------+-------------+------------------------------------------------------------+
        | mysql-bin.000003 |    4 | Format_desc    |         6 |         123 | Server ver: 5.7.31-log, Binlog ver: 4                      |
        | mysql-bin.000003 |  123 | Previous_gtids |         6 |         154 |                                                            |
        | mysql-bin.000003 |  154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
        | mysql-bin.000003 |  219 | Query          |         6 |         291 | BEGIN                                                      |
        | mysql-bin.000003 |  291 | Table_map      |         6 |         339 | table_id: 109 (test.t1)                                    |
        | mysql-bin.000003 |  339 | Write_rows     |         6 |         383 | table_id: 109 flags: STMT_END_F                            |
        | mysql-bin.000003 |  383 | Xid            |         6 |         414 | COMMIT /* xid=19 */                                        |
        | mysql-bin.000003 |  414 | Anonymous_Gtid |         6 |         479 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
        | mysql-bin.000003 |  479 | Query          |         6 |         551 | BEGIN                                                      |
        | mysql-bin.000003 |  551 | Table_map      |         6 |         599 | table_id: 109 (test.t1)                                    |
        | mysql-bin.000003 |  599 | Write_rows     |         6 |         644 | table_id: 109 flags: STMT_END_F                            |
        | mysql-bin.000003 |  644 | Xid            |         6 |         675 | COMMIT /* xid=24 */                                        |
        | mysql-bin.000003 |  675 | Anonymous_Gtid |         6 |         740 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
        | mysql-bin.000003 |  740 | Query          |         6 |         855 | use `test`; create table t2(id int, name varchar(20))      |
        | mysql-bin.000003 |  855 | Anonymous_Gtid |         6 |         920 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
        | mysql-bin.000003 |  920 | Query          |         6 |        1056 | create database zabbix charset utf8mb4 collate utf8mb4_bin |
        | mysql-bin.000003 | 1056 | Anonymous_Gtid |         6 |        1121 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
        | mysql-bin.000003 | 1121 | Query          |         6 |        1210 | drop database zabbix                                       |
        +------------------+------+----------------+-----------+-------------+------------------------------------------------------------+
        
      • 截取事件并恢复
        shell> mysqlbinlog --start-position=1006 --stop-position=1868 /var/lib/mysql/bin.123456 | mysql -u root -p
        shell> mysqlbinlog --start-position=1985 /var/lib/mysql/bin.123456 | mysql -u root -p
        
        • 恢复删表前的事件
        • 跳过删表事件,继续恢复

数据导出

主要用于异构迁移,配合 concat 使用,可以批量生成脚本。

  • 选项
    • –secure-file-priv[=dir_name]
      • 允许的导入/导出目录

      • 有效值

        • dir_name
          • 目录名,只允许在这个目录中导入/导出
        • 空字符串
          • 如果不设置,表示允许导入/导出
        • NULL
          • (默认) 表示不允许导入/导出
            mysql> select * from t1 into outfile '/tmp/t1.sql';
            ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
            mysql> select @@secure_file_priv;
            +--------------------+
            | @@secure_file_priv |
            +--------------------+
            | NULL               |
            +--------------------+
            
  • 语法
    select 语句 into outfile 'dir_name';
    
    • 示例
      mysql> select @@secure_file_priv;
      mysql> select * from t1 into outfile '/tmp/t1.sql';
      

1条评论

发表评论

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