掌握MySQL恢复数据备份知识

本文主要给大家简单讲讲MySQL恢复数据备份知识，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望MySQL恢复数据备份知识这篇文章可以给大家带来一些实际帮助。

数据库备份恢复知识要点：

1.时间轴备份类型分类：

      完全备份：备份整个数据集

      增量备份：上一次完全备份，或上一次增量备份以后变化的数据的备份(还原麻烦，节省空间)

      差异备份：仅备份最近一次完全备份以来变化的数据（还原简单，空间消耗大）

2.什么是物理备份、逻辑备份：

      物理备份：直接复制数据文件进行备份(有可能占用更多的空间，备份速度快，做热备较难)

     逻辑备份：从数据库中导出数据“另存为”而进行的备份(从二进制转化为文本格式，有可能丢失精度，需要专门的协议客户端才能进行，和数据存储引擎无关、备份恢复时间较长、做热备容易)

3.应该备份什么？

     ① 数据
     ② 二进制日志、innodb的事务日志
     ③ 代码(存储过程、存储函数、触发器、事件调度器)
     ④ 云服务器的配置文件

4.备份工具：

     ① MySQLdump  #mysql自带逻辑备份工具
     ② cp、tar 物理备份工具，冷备
     ③ lvm2的快照：几乎热备（请求施加全局持锁），借助于文件系统管理工具进行备份
     ④ mysqlhotcopy ：几乎冷备，仅适用于myisam存储引擎

5.备份方案的选择：

    方案一：MySQLdump+复制binlog(时间较慢，支持远程备份)

                  MySQLdump：完全备份

                  复制binlog中指定时间范围的event：增量备份

    方案二：lvm2快照+复制binlog

                 lvm2快照：使用cp或tar等做物理备份：完全备份

                 复制binlog中指定时间范围的event：增量备份

    方案三：XtraBackup

                 由percona提供的支持对innodb做热备（物理备份）的工具

                 支持完全备份，增量备份

MySQLdump备份工具讲解：

---

1.什么是MySQLdump？

      mysql或mariadb自带客户端命令，逻辑备份工具。是图形化备份管理工具mydumper、phpmyadmin的备份调用工具。基于mysql客户端协议，适用于所有存储引擎，温备：完全备份、部分备份。对innodb支持热备(时间较长)适合备份较小的数据库(GB以下).

2.对常见存储引擎支持：

     InnoDB：热备或温备；

     MyISAM：温备；

3.备份机制：

    通过mysql协议连接至mysql云服务器。向mysql云服务器发起一个全量查询操作，把所有数据拿到本地以后，并且将读取到的数据保存在文件中从而完成备份。
    库：CREATE DATABASE
    表：CREATE TABLE
    数据：INSERT INTO

4.使用方法Usage:

     ① mysqldump [OPTIONS] database [tables]   # 备份单库，可以只备份其中的一部分表（部分备份,还原时需手动创建数据库）；

      ② mysqldump [OPTIONS]  -B/--databases [OPTIONS] DB1 [DB2 DB3...]  # 备份多库(推荐使用)；

      ③ mysqldump [OPTIONS]  -A/--all-databases [OPTIONS] # 备份所有库；

5. 初级备份实验示例：

    ① 备份：mysqldump -uroot -p -B test > test.sql

    ② 登录mysql删除test库：drop database  test；

    ③ 恢复：mysql -uroot -p <test.sql

MySQLdump使用进阶：

---

        实际生产使用中，需要考虑到备份时长、备份精度、备份过程对线上体验的影响、从完全备份后产生的数据恢复方法等，本节介绍MySQLdump针对主流存储引擎额外选项，对事务记录，以及演示生产使用MySQLdump。

MyISAM存储引擎选项：支持温备，备份时要锁定表（避免数据不一致）；

      -x, --lock-all-tables：锁定所有库的所有表，读锁；

       -l, --lock-tables：锁定指定库所有表；

InnoDB存储引擎：支持温备和热备；

     --single-transaction：创建一个事务，基于此快照执行备份；后续要跟崩溃后恢复操作

其它选项：

     -R, --routines：备份指定库的存储过程和存储函数；

     --triggers：备份指定库的触发器；

     -E, --events：备份指定数据库相关的所有event scheduler；

     --master-data[=#]

             1：默认（--master-data），记录为CHANGE MASTER TO语句，此语句不被注释（据说在change master to时不用指定master_log_file和master_log_pos即可）；

             2：记录为CHANGE MASTER TO语句，此语句被注释；

     -F / --flush-logs：锁定表完成后，即进行日志滚动操作；

    -d :后加数据库名表示只备份建库语句

    --compact：去掉注释

MySQLdump备份实验：

实验场景：两台数据库主机A,B。全局备份主机A数据后，又产生了新的数据（自己手动修改表信息），将主机A的完全备份文件、二进制日志文件发送给主机B完成备份。

实验准备：

        ① 两台主机安装MySQL或者mariadb-server

        ② 开启二进制日志功能(默认关闭)

             方法：vim /etc/my.cnf.d/server.cnf  #安装方式不同，路径也会不同，本文以yum安装讲解

                       在[server]或者[mysqld]下添加 ：log_bin=/路径/前缀  (mysql用户必须对日志路径具有rwx权限，本例log_bin=/app/test)

                       重启服务

       ③ 主机A数据示例：

select * from  shudian.mybook;

备份恢复流程：    

 1. 全局备份：

mysqldump -uroot -p --single-transaction --master-data=2 --flush-logs -B shudian >shudian.sql
--single-transaction：创建一个事务,在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。
--master-data=2:记录备份时二进制文件记录的位置，此语句被注释
--flush-logs：锁定表完成后，即进行日志滚动操作
--lock-all-tables,  -x 
提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
mysqldump  -uroot -p --host=localhost --all-databases --lock-all-tables
--lock-tables,  -l
开始导出前，锁定所有表。用READ  LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。
请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
mysqldump  -uroot -p --host=localhost --all-databases --lock-tables

2.修改主机A数据

insert into mybook values (5,'liangzi',30,412);
flush logs;  #刷新使生成新的binlog日志

修改后数据：

3.发送备份信息

① 全局备份文件

scp shudian.sql B主机IP：/app

② 查看全局备份文件看备份到哪个节点并记录：

grep " MASTER_LOG_POS" shudian.sql |head -1
-- CHANGE MASTER TO MASTER_LOG_FILE='test.000002', MASTER_LOG_POS=1545;

③ 备份节点后二进制日志

mysqlbinlog --start-position=1545 /app/test.000002 -D >incre.sql
#ROW模式导出mysqlbinlog --base64-output=decode-rows -v -d DBNAME mysqlbin.000001 >incre.sql
#mysqlbinlog -d -D ops test.000002 >ops_incre_bin.sql 
    --start-position= / -j起始位置
    -d #指定数据库
    -D / --disable-log #禁用恢复时生成二进制日志,生成的sql文件需要root权限才能执行

如误操作后需修改sql文件剔除相关操作的条目，例误删除某表要删除：

DROP TABLE `t_bind_card` 
;
# at 341
#180824 15:04:11 server id 1  end_log_pos 389 CRC32 0x2f0eec92 	Rotate to master-bin.000004  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC'  ;

发送二进制日志至主机B：

scp incre.sql B主机IP：/app

④ 主机B数据恢复

set sql_log_bin=0;临时关闭记录二进制日志
mysql <shudian.sql
mysql <incre.sql
set sql_log_bin=1;打开记录二进制日志

注意:二进制文件不应该与数据文件放在同一块磁盘，并应放置于具有冗余功能的磁盘上如ride10.

MySQL恢复数据备份知识就先给大家讲到这里，对于其它相关问题大家想要了解的可以持续关注我们的行业资讯。我们的板块内容每天都会捕捉一些行业新闻及专业知识分享给大家的。