备份和恢复

一条评论

备份和恢复

可以处理的崩溃类型

自动故障恢复 (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';
发表于：MySQL
2021-05-18
# MySQL# 备份和恢复# 逻辑备份
复制链接
英仔
版权声明：本站原创文章，由 英仔 2021-05-18发表，共计9467字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
mysqlimport-数据导入程序
Unicode 的支持
慢查询日志
MySQL 服务器启动程序
评论（一条评论）
英仔 博主
2021-05-18 20:40:32 回复

在时间点恢复部分，由于插件的 bug，部分内容没有正确显示，可以参考思维导图

WindowsWindowsFirefoxFirefox88.0