精华内容
下载资源
问答
  • MySQL备份和恢复

    2019-11-30 15:29:49
    MySQL备份和恢复一.备份和恢复概述1.1 为何备份?1.2 备份什么?1.3 备份要考虑哪些因素?1.3.1 恢复点目标(RPO:recovery point objective)和恢复时间目标(RTO:recovery time objective)1.3.2 备份策略1.3.3 备份和...

    一.备份和恢复概述

    1.1 为何备份?

    • 灾难恢复
      当出现严重的硬件故障、或者某个无耻的软件错误损坏了关键数据,或者服务器抽风,突然数据无法读取;
      又或者黑客攻击、人员无操作等不幸发生时,此时如果没有备份数据,那就完蛋了。

    • 审计
      有时,有需要知道数据在过去的某个时间点是怎样的,例如:有人发现你的软件中的某个BUG,需要知道
      代码在过去干了些啥(总有些时候你的软件仅仅有版本控制是不够的)

    • 测试
      在实际产生的数据上测试往往要频繁的将最新的产品数据放到测试服务器上,一个非常方便的做法是周期性
      的使用最新的产品的数据备份恢复到测试服务器上。

    • 某些人总是改变想法
      你会惊讶于某些人删除了数据后反悔的速度有多快。

    1.2 备份什么?

    • 首要的是数据
      对于MySQL来说,日志即代表了数据,包括:二进制日志(binary log)和InnoDB的事务日志(transaction log)

    • 代码
      目前的数据库产品如MySQL,都会存有大量的代码,如触发器和存储过程;备份mysql数据库可以备份大
      部分触发器和存储过程,因为大部分的类似的代码都被存在系统数据库mysql中;但是没法独立的恢复
      某个表,因为该表的某些“数据”(如存储过程)实际上存放于mysql数据库中。

    下面展示了存储过程sp_testlog关联的数据存放在hellodb数据库的testlog表;而sp_testlog自身的
    定义则被存放在mysql数据库的proc表

    MariaDB [mysql]> USE mysql; SHOW TABLES;
    Database changed
    +---------------------------+
    | Tables_in_mysql           |
    +---------------------------+
    | columns_priv              |
    | db                        |
    | event                     |
    | func                      |
    | general_log               |
    | help_category             |
    | help_keyword              |
    | help_relation             |
    | help_topic                |
    | host                      |
    | ndb_binlog_index          |
    | plugin                    |
    | proc                      |   # 此表存放sp_testlog存储过程
    | procs_priv                |
    | proxies_priv              |
    | servers                   |
    | slow_log                  |
    | tables_priv               |
    | time_zone                 |
    | time_zone_leap_second     |
    | time_zone_name            |
    | time_zone_transition      |
    | time_zone_transition_type |
    | user                      |
    +---------------------------+
    24 rows in set (0.00 sec)
    
    MariaDB [mysql]> SHOW PROCEDURE STATUS\G
    *************************** 1. row ***************************
                      Db: hellodb       # sp_testlog存储过程关联的数据表
                    Name: sp_testlog    # sp_testlog存储过程
                    Type: PROCEDURE
                 Definer: root@localhost
                Modified: 2019-11-27 08:47:42
                 Created: 2019-11-27 08:47:42
           Security_type: DEFINER
                 Comment: 
    character_set_client: utf8
    collation_connection: utf8_general_ci
      Database Collation: utf8_general_ci
    1 row in set (0.00 sec)
    
    MariaDB [mysql]> USE hellodb; SHOW tables;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    +-------------------+
    | Tables_in_hellodb |
    +-------------------+
    | classes           |
    | coc               |
    | courses           |
    | scores            |
    | students          |
    | testlog           |           # sp_testlog存储过程关联的数据表
    +-------------------+
    6 rows in set (0.00 sec)
    
    
    MariaDB [mysql]> SELECT * FROM proc\G
    *************************** 1. row ***************************
                      db: hellodb
                    name: sp_testlog
                    type: PROCEDURE
           specific_name: sp_testlog
                language: SQL
         sql_data_access: CONTAINS_SQL
        is_deterministic: NO
           security_type: DEFINER
              param_list: 
                 returns: 
                    body:               # 存储过程定义
    begin;
    declare i int;
    set i = 1; 
    while i <= 100000 
    do  insert into testlog(name,age) values (concat('wang',i),i); 
    set i = i +1; 
    end while; 
    end
    ...
    1 row in set (0.00 sec)
    
    
    • 数据复制相关的配置文件
      如果涉及到主从复制的数据库备份和恢复(实际生产环境常见),那最好把涉及到的配置文件全部包括到
      备份计划中;如:二进制日志,中继日志,日志索引文件和.info文件等。

    • 服务器配置文件
      如果可能必须从严重的灾难恢复,需要配置一个全新的服务器,那最好连服务器配置文件备份。

    • 和服务器紧密相关的系统配置文件
      如涉及到备份计划的cron事务,用户和组配置,管理性脚本和sudo规则等。

    1.3 备份要考虑哪些因素?

    1.3.1 恢复点目标(RPO:recovery point objective)和恢复时间目标(RTO:recovery time objective)

    • 在没有严重后果的情况下容忍多少数据丢失?
    • 数据恢复时速度要多快才行?数据库不可访问时,什么时间范围内可以接受?用户能接受的时间长短?
    • 需要恢复什么?
      整个服务器?单个数据库?单张表?或者单个语句和事务?

    1.3.2 备份策略

    • 温备时加锁多久?

    • 备份产生的负载服务器在该时段是否能够承载?

    • 备份脚本的严格性?

    1.3.3 备份和恢复的挑战

    • 某个人可以计划,设计,实施备份;但是灾难恢复时未必是同一个人?

    • 执着于备份,未进行恢复测试和演练?

    1.4 备份的类型?

    1.4.1 完全备份

    完全备份:整个数据集,包括配置文件,全部数据库,全部日志。

    1.4.2 部分备份

    部分备份:只备份数据子集,如部分库或表或者某些SQL语句

    1.4.3 增量备份和差异备份

    增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快,还原复杂
    差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单
    在这里插入图片描述

    上:增量 下:差异

    1.4.4 冷备 温备 热备

    冷备:对数据库的读、写操作均不可进行,相当于停数据库;完全停业务;也叫离线备份
    温备:对数据库的读操作可执行,但写操作不可执行;影响业务
    热备:对数据库的读、写操作均可执行;不影响业务

    InnoDB:支持以上三种备份方式
    MyISAM:只支持冷备和温备,不支持热备

    1.4.5 物理备份

    • 直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快
    • 物理备份的优势:
      1.备份操作简单,直接拷贝需要备份的文件到备份服务器
      2.恢复物理备份亦简单,MyISAM的存储引擎直接放到相应的位置;InnoDB的存储引擎则需要停止MySQL
      服务和其他的简单步骤。
      3.InnoDB和MyISAM的数据库的物理备份可以跨平台、跨操作系统和跨MySQL版本兼容(逻辑备份亦可以)。
      4.恢复物理备份的时间极短,因为MySQL服务器不用执行任何SQL语句或者构建索引;相比逻辑备份来说,
      比较可怕的一点就是无法预估逻辑备份恢复的时间。
    • 物理备份的劣势:
      1.基于InnoDB数据库的物理备份文件往往远大于相对应的逻辑备份文件。InnoDB的表空间有很多未使用的
      磁盘空间,有部分空间用来实现其他功能而不是存储数据(如:插入缓存,回滚段空间等)
      2.由于文件名大小写敏感和不同的系统浮点数格式不一样等问题,物理备份也不是能够跨所有平台和系统。

    1.4.6 逻辑备份

    从数据库中“导出”数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度

    • 逻辑备份的优势:
      1.逻辑备份是普通的文本文件,可以编辑查看修改。如使用grep和sed等工具处理后再恢复,或者在不
      恢复的情况下查看数据。
      2.还原方便,简单的使用管道传给mysql或使用mysqlimport命令就可以。
      3.可以远程通过网络恢复数据。
      4.配合mysqldump等恢复工具,可以非常灵活。
      5.几乎与存储引擎无关的。可以备份InnoDB的数据表还原到使用MyISAM引擎的数据库中,几乎不要额外的工作。

    • 逻辑备份的缺陷:
      1.备份时,占用额外的服务器资源来产生备份文件。
      2.逻辑备份有时会比物理文件大很多,使用压缩又会消耗CPU资源。
      3.还原逻辑备份时需要MySQL解释执行SQL语句,转换为存储格式,并且重建索引;很慢。

    二.mysqldump备份工具

    2.1 mysqldump

    官网使用手册

    • mysqldump为mysql的客户端工具之一,用于实现逻辑备份功能;其通过产生可执行的SQL语句来备份一个
      或多个数据库;mysqldump也可以产生CSV或者XML格式的文件。由于每次备份其都会使用mysql协议连接到mysql
      服务器,所以有需要提供相关的认证信息,包括用户名、密码和主机(-uUSERNAME -p),本地连接mysql服务器默认
      主机为localhost。

    2.2 用法及选项

    mysqldump [OPTIONS] database [tables]
    mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
    mysqldump [OPTIONS] –A [OPTIONS]
    
    -A, --all-databases           #备份所有数据库,含create database
    -B, --databases db_name…      #指定备份的数据库,包括create database语句
    -E, --events                  #备份相关的所有event scheduler
    -R, --routines                #备份所有存储过程和自定义函数
    --triggers                    #备份表相关触发器,默认启用,用--skip-triggers,不备份触发器
    --default-character-set=utf8  #指定字符集
    --master-data[=#]             #此选项须启用二进制日志
                                     # 1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定#,默认为1
                                     # 2:记录为注释的CHANGE MASTER TO语句
                                     # 此选项会自动关闭--lock-tables功能,自动打开-x | --lock-all-tables功能(除非开启--single-transaction)
    -F, --flush-logs              #备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文
                                   # 件,配合-A 或 -B 选项时,会导致刷新多次数据库。建议在同一时刻执行转储和
                                   # 日志刷新,可通过和--single-transaction或-x,--master-data 一起使用实现,
                                   # 此时只刷新一次二进制日志
    --compact                    #去掉注释,适合调试,生产不使用
    -d, --no-data                #只备份表结构
    -t, --no-create-info         #只备份数据,不备份create table 
    -n,--no-create-db            #不备份create database,可被-A或-B覆盖
    --flush-privileges           #备份mysql或相关时需要使用
    -f, --force                  #忽略SQL错误,继续执行
    --hex-blob                   #使用十六进制符号转储二进制列,当有包括BINARY, VARBINARY,BLOB,BIT的数据类型的列时使用,避免乱码
    -q, --quick                  #不缓存查询,直接输出,加快备份速度
    

    2.3 备份示例

    • 分库备份并压缩
    法一:
    [root@centos7 ~]$ll /backup/
    total 0
    [root@centos7 ~]$mysql -uroot -p -e "SHOW DATABASES;" | sed -rn '/^(Database|information_schema|performance_schema)$/!s#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' | bash
    Enter password: 
    [root@centos7 ~]$ll /backup/
    total 156
    -rw-r--r-- 1 root root    513 Nov 30 13:05 db4.sql.gz
    -rw-r--r-- 1 root root    514 Nov 30 13:05 db5.sql.gz
    -rw-r--r-- 1 root root    514 Nov 30 13:05 db6.sql.gz
    -rw-r--r-- 1 root root 139585 Nov 30 13:05 mysql.sql.gz
    -rw-r--r-- 1 root root    514 Nov 30 13:05 test.sql.gz
    [root@centos7 ~]$
    
    法二:
    [root@centos7 ~]$rm -f /backup/*
    [root@centos7 ~]$ll /backup/
    total 0
    [root@centos7 ~]$for DB in `mysql -uroot -p -e "SHOW DATABASES;" | grep -Ev '^(Database|information_schema|performance_schema)$'`; do mysqldump -B $DB | gzip > /backup/$DB.sql.gz; done
    Enter password:
    [root@centos7 ~]$ll /backup/
    total 156
    -rw-r--r-- 1 root root    516 Nov 30 13:11 db4.sql.gz
    -rw-r--r-- 1 root root    516 Nov 30 13:11 db5.sql.gz
    -rw-r--r-- 1 root root    516 Nov 30 13:11 db6.sql.gz
    -rw-r--r-- 1 root root 139586 Nov 30 13:11 mysql.sql.gz
    -rw-r--r-- 1 root root    516 Nov 30 13:11 test.sql.gz
    
    
    • 使用完全备份和二进制日志,还原数据库最新状态
    # 1.完全备份
    [root@centos7 ~]$mysqldump -A -F --single-transaction --master-data=2 | gzip > /backup/base-`date +%F-%T`.sql.gz
    [root@centos7 ~]$ll /backup/
    -rw-r--r-- 1 root root 139761 Nov 30 13:15 base-2019-11-30-13:15:43.sql.gz
    [root@centos7 ~]$gunzip base-2019-11-30-13:15:43.sql.gz
    # 2.确定二进制日志大小位置
    [root@centos7 ~]$grep -nC 3 "\-\- CHANGE MASTER TO" /backup/base-2019-11-30-13\:15\:43.sql
    19--- Position to start replication or point-in-time recovery from
    20---
    21-
    22:-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000006', MASTER_LOG_POS=245;
    23-
    24---
    25--- Current Database: `db4`
    # 3.修改数据库
    [root@centos7 ~]$mysql -p
    Enter password:
    ...
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | db4                |
    | db5                |
    | db6                |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    7 rows in set (0.00 sec)
    
    MariaDB [(none)]> drop database db4;
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> drop database db6;
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | db5                |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    5 rows in set (0.00 sec)
    
    # 4 查看完全备份后数据库更改后的二进制日志位置,并根据第2步得到的日志位置信息备份更改后的数据
    MariaDB [(none)]> SHOW MASTER logs;
    +--------------------+-----------+
    | Log_name           | File_size |
    +--------------------+-----------+
    | mariadb-bin.000001 |       533 |
    | mariadb-bin.000002 |       551 |
    | mariadb-bin.000003 |       527 |
    | mariadb-bin.000004 |       326 |
    | mariadb-bin.000005 |       326 |
    | mariadb-bin.000006 |       326 |
    +--------------------+-----------+
    4 rows in set (0.00 sec)
    [root@centos7 ~]$mysqlbinlog mysql-bin.000006 --start-position=245 > /backup/inc.sql
    
    # 5.干掉数据库
    [root@centos7 ~]$ll /var/lib/mysql/
    aria_log.00000001         ib_logfile0               mariadb-bin.000003        mariadb-bin.index         mysql/                    relay-log.000012
    aria_log_control          ib_logfile1               mariadb-bin.000004        mariadb-relay-bin.000001  mysql.sock                relay-log.index
    db5/                      mariadb-bin.000001        mariadb-bin.000005        mariadb-relay-bin.index   performance_schema/       relay-log.info
    ibdata1                   mariadb-bin.000002        mariadb-bin.000006        master.info               relay-log.000011          test/
    [root@centos7 ~]$rm -rf /var/lib/mysql/
    [root@centos7 ~]$ll /var/lib/mysq/
    ls: cannot access /var/lib/mysq/: No such file or directory
    [root@centos7 ~]$ll /var/lib/mysql/
    total 0
    
    # 6.重新生成系统数据库
    mysql_install_db --user=mysql
    systemctl restart mariadb
    [root@centos7 ~]$mysql -p
    Enter password:
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    4 rows in set (0.00 sec)
    
    MariaDB [(none)]> set sql_log_bin=0;  # 关闭二进制日志
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> select @@sql_log_bin;
    +---------------+
    | @@sql_log_bin |
    +---------------+
    |             0 |
    +---------------+
    1 row in set (0.00 sec)
    
    MariaDB [(none)]> source /backup/base-2019-11-30-13:15:43.sql
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    ...
    
    # 7.数据库回到完全备份时状态
    MariaDB [test]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | db4                |
    | db5                |
    | db6                |
    | mysql              |
    | performance_schema |
    +--------------------+
    7 rows in set (0.00 sec)
    
    # 8.使用第4步得到的增量备份还原从完全备份后到数据库被干掉前的数据
    Bye
    [root@master ~]#mysql -p
    Enter password:
    ...
    MariaDB [test]> source /backup/inc.sql
    MariaDB [test]> SET sql_log_bin=1;
    MariaDB [(none)]> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | db5                |
    | mysql              |
    | performance_schema |
    +--------------------+
    5 rows in set (0.00 sec)
    

    2.4 基于MyISAM的MySQL使用mysqldump

    • MyISAM不支持事务,只能支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作
    -x,--lock-all-tables # 加全局读锁,锁定所有库的所有表,同时加--single-transaction或--lock-tables选项
                         会关闭此选项功能
                         # 注意:数据量大时,可能会导致长时间无法并发访问数据库
    -l,--lock-tables     # 对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,
    --skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
    # 注:以上选项对InnoDB表一样生效,实现温备,但不推荐使用
    

    2.5 基于InnoDB的数据库使用mysqldump

    • InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备,也可以实现温备但不建议用
    --single-transaction
    # 此选项Innodb中推荐使用,不适用MyISAM,此选项会开始备份前,先执行START TRANSACTION指令开启事务
    # 此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表
    (目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致。 在进行单事务转储时,要确保有效的转储
    文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句:ALTER TABLEDROP 
    TABLERENAME TABLETRUNCATE TABLE,此选项和--lock-tables(此选项隐含提交挂起的事务)选
    项是相互排斥,备份大型表时,建议将--single-transaction选项和--quick结合一起使用
    

    2.6 生产环境实战备份策略

    • InnoDB建议备份策略
    mysqldump –uroot -p –A –F –E –R  --single-transaction --master-data=1 --flush-privileges  --triggers --default-character-set=utf8 --hex-blob > ${BACKUP}/fullbak_${BACKUP_TIME}.sql
    
    • MyISAM建议备份策略
    mysqldump –uroot -p –A –F –E –R –x --master-data=1 --flush-privileges  --triggers  --default-character-set=utf8  --hex-blob > ${BACKUP}/fullbak_${BACKUP_TIME}.sql
    

    三.xtrabackup备份工具

    官网使用手册

    3.1 xtrabackup介绍

    • xtrabackup备份工具是percona公司提供的mysql数据库备份工具,惟一开源的能够
      对innodb和xtradb数据库进行热备的工具。

    • xtrabackup具有如下优点:

    1.备份还原过程快速、可靠
    2.备份过程不会打断正在执行的事务
    3.能够基于压缩等功能节约磁盘空间和流量
    4.自动实现备份检验
    5.开源,免费
    • 免费的Percona XtraBackup和收费的MySQL Enterprise backup对比
      借一步到官网

    • xtrabackup备份工具的文件组成—Xtrabackup2.2版之前包括4个可执行文件:

    innobackupex:     主备份程序:Perl 脚本
    xtrabackup:       主备份程序:C/C++,编译的二进制程序
    xbcrypt:          用于加解密备份
    xbstream:         支持并发写的流文件格式的备份工具
    
    • xtrabackup的新版变化
      xtrabackup版本升级到2.4后,相比之前的2.1有了比较大的变化:innobackupex功能全部集成到trabackup里面,只有一个
      binary程序,另外为了兼容考虑,innobackupex作为 xtrabackup 的软链接,即xtrabackup现在支持非Innodb表备份,
      并且 Innobackupex 在下一版本中移除,建议通过xtrabackup替换innobackupex

    • xtrabackup备份过程
      在这里插入图片描述

    • 备份后生成的文件:

      • 使用innobackupex备份时,其会调用xtrabackup备份所有的InnoDB表,复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、
        CSV和ARCHIVE表的相关文件,同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时,
        innobackupex还会在备份目录中创建如下文件:
      • (1)xtrabackup_info:文本文件,innobackupex工具执行时的相关信息,包括版本,备份选项,备份时长,备份LSN(log sequence number日志序列号),BINLOG的位置
      • (2)xtrabackup_checkpoints:文本文件,备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN范围信息,每个InnoDB页
        (通常为16k大小)都会包含一个日志序列号LSN。LSN是整个数据库系统的系统版本号,每个页面相关的LSN能够表明此页面最近是如何发生改变的
      • (3)xtrabackup_binlog_info:文本文件,MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置,可利用
        实现基于binlog的恢复
      • (4)backup-my.cnf:文本文件,备份命令用到的配置选项信息
      • (5)xtrabackup_logfile:备份生成的二进制日志文件

    3.2 xtrabackup使用

    • 使用xtrabackup工具备份和还原,大体分三步实现:
    1. 备份: 对数据库做完全或增量备份
      xtrabackup --backup --target-dir=/data/backups/
    2. 预准备: 还原前,先对备份的数据,整理至一个临时目录;整理后的已经是数据库可用数据
      xtrabackup --prepare --target-dir=/data/backups/rsync -avrP /data/backup/ /var/lib/mysql/
    3. 还原: 将整理好的数据,复制回数据库目录中
      xtrabackup --copy-back --target-dir=/data/backups/
      chown -R mysql:mysql /var/lib/mysql

    四.备份策略选择

    • 根据数据库的大小、是否写密集、或者是否需要时间点回复等方面决定使用的备份恢复策略

    • xtrabackup适用于大多数情况
      3.2 xtrabackup使用

    • 使用xtrabackup工具备份和还原,大体分三步实现:

    1. 备份: 对数据库做完全或增量备份
      xtrabackup --backup --target-dir=/data/backups/
    2. 预准备: 还原前,先对备份的数据,整理至一个临时目录;整理后的已经是数据库可用数据
      xtrabackup --prepare --target-dir=/data/backups/rsync -avrP /data/backup/ /var/lib/mysql/
    3. 还原: 将整理好的数据,复制回数据库目录中
      xtrabackup --copy-back --target-dir=/data/backups/
      chown -R mysql:mysql /var/lib/mysql

    四.备份策略选择

    • 根据数据库的大小、是否写密集、或者是否需要时间点回复等方面决定使用的备份恢复策略
    • xtrabackup适用于大多数情况
      在这里插入图片描述
    展开全文
  • Mysql备份和恢复

    2018-06-21 11:51:40
    Mysql备份和恢复 一:mysqldump常见备份和恢复方式 二:完全恢复 三:不完全恢复 (1) 基于时间点恢复 (2) 基于位置恢复 一:mysqldump常见备份和恢复方式 逻辑备份工具...
    Mysql备份和恢复

    一:mysqldump常见备份和恢复方式
    二:完全恢复
    三:不完全恢复
    (1) 基于时间点恢复
    (2) 基于位置恢复 

    一:mysqldump常见备份和恢复方式
    逻辑备份工具:mysqldump
    查看帮助信息:mysqldump --help 

    创建测试数据库
    mysql> create database test;
    mysql> create table emp(a int,b varchar(10));
    insert into emp values(1,'z1');
    insert into emp values(2,'z2');
    insert into emp values(3,'z3');
    mysql> create table dept(a int,b varchar(10));
    insert into dept values(1,'a1');
    insert into dept values(2,'a2');
    insert into dept values(3,'a3');

    ---1 备份所有数据库
    mysqldump -uroot -p --all-databases > D:\mysql\backup\all.sql
    ---2 备份数据库test 
    mysqldump -uroot -p test > D:\mysql\backup\test.sql 
    ---3 备份数据库test下的表emp
    mysqldump -uroot -p test emp > D:\mysql\backup\emp.sql 
    ---4 备份备份数据库test下的表emp和dept
    mysqldump -uroot -p test emp dept > D:\mysql\backup\emp_dept.sql
    ---5 备份数据库test下的所有表为逗号分割的文本,备份到D:\mysql\backup,需要提前设置好secure-file-priv参数;
    mysqldump -uroot -p -T D:\mysql\backup test --fields-terminated-by ','

    二:完全恢复

    ---16:52备份
    mysqldump -uroot -p -l -F test > test.sql 
    其中-l参数表示给所有表加读锁,-F表示生成一个新的日志文件,此时,test中emp表的数据如下:
    mysql> select * from emp order by a;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | z1   |
    |    2 | z2   |
    |    3 | z3   |
    |    4 | z4   |
    +------+------+
    4 rows in set (0.02 sec)

    ---16:57点插入新数据:
    insert into emp values(5,'z5');
    insert into emp values(6,'z6');
    mysql> select * from emp order by a;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | z1   |
    |    2 | z2   |
    |    3 | z3   |
    |    4 | z4   |
    |    5 | z5   |
    |    6 | z6   |
    +------+------+
    6 rows in set (0.00 sec)

    ---17点,数据库突然故障,数据无法访问。需要恢复备份:
    mysql> flush logs;  ---手动切一下日志,将下面数据库恢复产生的SQL写入到下一个日志组里;
    mysql -uroot -p test < test.sql 
    恢复后的数据如下:
    ---只恢复了备份时刻的数据
    mysql> select * from emp order by a;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | z1   |
    |    2 | z2   |
    |    3 | z3   |
    |    4 | z4   |
    +------+------+
    4 rows in set (0.00 sec)
    ---使用mysqlbinlog恢复自mysqldump备份以来的BINLOG
    mysql> show master status;
    ---file mysql-bin.000005
    D:\mysql\mysql-5.7.22-winx64\bin>mysqlbinlog D:\mysql\mysql-5.7.22-winx64\data\mysql-bin.000004|mysql -u root -p test
    Enter password: ***
    ---查询完全恢复的数据如下:
    mysql> use test
    Database changed
    mysql> select * from emp;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | z1   |
    |    2 | z2   |
    |    3 | z3   |
    |    4 | z4   |
    |    5 | z5   |
    |    6 | z6   |
    +------+------+
    6 rows in set (0.00 sec)

    三:不完全恢复
    (1) 基于时间点恢复
    (2) 基于位置恢复 

    (1) 基于时间点恢复
    ---不完全恢复 
    恢复到无操作之前的状态,然后跳过误操作语句,在恢复后面执行的语句,完成我们的恢复
    ---备份 
    ---9:50 
    mysqldump -uroot -p -l -F test > test0621.sql 
    其中-l参数表示给所有表加读锁,-F表示生成一个新的日志文件,此时,test中dept表的数据如下:
    mysql> use test
    Database changed
    mysql> select * from dept;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | a1   |
    |    2 | a2   |
    |    3 | a3   |
    +------+------+
    3 rows in set (0.00 sec)

    ---9:56
    mysql>
    insert into dept values(4,'a4');
    insert into dept values(5,'a5');
    insert into dept values(6,'a6');
    mysql> select * from dept;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | a1   |
    |    2 | a2   |
    |    3 | a3   |
    |    4 | a4   |
    |    5 | a5   |
    |    6 | a6   |
    +------+------+
    6 rows in set (0.00 sec)

    ---10:00
    mysql> delete from dept;
    Query OK, 6 rows affected (0.09 sec)

    mysql> select * from dept;
    Empty set (0.00 sec)

    ---10:03
    mysql> create table t1(a int,b varchar(10));
    insert into t1 values(1,'c1');
    insert into t1 values(2,'z2');
    insert into t1 values(3,'z3');
    mysql> select * from t1;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | c1   |
    |    2 | z2   |
    |    3 | z3   |
    +------+------+
    3 rows in set (0.00 sec)

    mysql> flush logs;  ---手动切一下日志,将下面数据库恢复产生的SQL写入到下一个日志组里;
    mysql> show binary logs;
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000001 |       177 |
    | mysql-bin.000002 |       201 |
    | mysql-bin.000003 |      5599 |
    | mysql-bin.000004 |       721 |
    | mysql-bin.000005 |      2876 |
    | mysql-bin.000006 |       201 |
    | mysql-bin.000007 |      2237 |
    | mysql-bin.000008 |       154 |
    +------------------+-----------+
    8 rows in set (0.00 sec)

    mysql -uroot -p test < test0621.sql 
    mysql> select * from dept;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | a1   |
    |    2 | a2   |
    |    3 | a3   |
    +------+------+
    3 rows in set (0.00 sec)

    mysql> select * from t1;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | c1   |
    |    2 | z2   |
    |    3 | z3   |
    +------+------+
    3 rows in set (0.00 sec)

    如果上午10点发送误操作,可以用以下语句用备份和BINLOG将数据恢复到故障前:
    mysqlbinlog --stop-datetime="2018-06-21 09:57:10" D:\mysql\mysql-5.7.22-winx64\data\mysql-bin.000007| mysql -u root -p test
    跳过故障时的时间点,继续执行后面的BINLOG,完成恢复。
    mysqlbinlog --start-datetime="2018-06-21 10:02:00" D:\mysql\mysql-5.7.22-winx64\data\mysql-bin.000007| mysql -u root -p test
    Enter password: ***
    ERROR 1050 (42S01) at line 26: Table 't1' already exists

    mysql> select * from dept;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | a1   |
    |    2 | a2   |
    |    3 | a3   |
    |    4 | a4   |
    |    5 | a5   |
    |    6 | a6   |
    +------+------+
    6 rows in set (0.00 sec)

    (2) 基于位置恢复 
    和基于时间点的恢复类似,但是更精确,因为同一个时间点可能有很多条SQL语句同时执行。恢复的操作步骤如下。
    mysql> create table t2(a int,b varchar(10));
    insert into t2 values(1,'c1');
    insert into t2 values(2,'z2');
    insert into t2 values(3,'z3');
    mysql> select * from t2;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | c1   |
    |    2 | z2   |
    |    3 | z3   |
    +------+------+
    3 rows in set (0.00 sec)

    ---10:32
    mysqldump -uroot -p -l -F test > test0621a.sql

    ---10:44
    insert into t2 values(4,'a4');
    insert into t2 values(5,'a5');
    insert into t2 values(6,'a6');

    mysql> select * from t2;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | c1   |
    |    2 | z2   |
    |    3 | z3   |
    |    4 | a4   |
    |    5 | a5   |
    |    6 | a6   |
    +------+------+
    6 rows in set (0.00 sec)

    ---10:47
    mysql> delete from t2;
    Query OK, 6 rows affected (0.13 sec)

    mysql> select * from t2;
    Empty set (0.00 sec)

    ---10:50
    insert into t1 values(4,'c4');
    insert into t1 values(5,'c5');
    insert into t1 values(6,'c6');

    mysql> flush logs;  ---手动切一下日志,将下面数据库恢复产生的SQL写入到下一个日志组里;
    Query OK, 0 rows affected (0.21 sec)

    mysql> show binary logs;
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000001 |       177 |
    | mysql-bin.000002 |       201 |
    | mysql-bin.000003 |      5599 |
    | mysql-bin.000004 |       721 |
    | mysql-bin.000005 |      2876 |
    | mysql-bin.000006 |       201 |
    | mysql-bin.000007 |      2237 |
    | mysql-bin.000008 |      4131 |
    | mysql-bin.000009 |      2054 |
    | mysql-bin.000010 |       154 |
    +------------------+-----------+
    10 rows in set (0.00 sec)

    mysql -uroot -p test < test0621a.sql 
    mysql> use test
    Database changed

    mysql> select * from t2;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | c1   |
    |    2 | z2   |
    |    3 | z3   |
    +------+------+
    3 rows in set (0.00 sec)

    mysql> select * from t1;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | c1   |
    |    2 | z2   |
    |    3 | z3   |
    +------+------+
    3 rows in set (0.00 sec)
     
    mysql> show binlog events in 'mysql-bin.000009';
    +------------------+------+----------------+-----------+-------------+---------------------------------------+
    | Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
    +------------------+------+----------------+-----------+-------------+---------------------------------------+
    | mysql-bin.000009 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.22-log, Binlog ver: 4 |
    ......
    | mysql-bin.000009 | 1068 | Table_map      |         1 |        1116 | table_id: 114 (test.t2)               |
    | mysql-bin.000009 | 1116 | Delete_rows    |         1 |        1199 | table_id: 114 flags: STMT_END_F       |
    ......

    mysqlbinlog --start-datetime="2018-06-21 10:01:00" --stop-datetime="2018-06-21 10:55:00" D:\mysql\mysql-5.7.22-winx64\data\mysql-bin.000009 > sql_resore.sql 
    ---sql_resore.sql
    # at 1116
    #180621 10:47:56 server id 1  end_log_pos 1199 CRC32 0xa6883df9 Delete_rows: table id 114 flags: STMT_END_F

    BINLOG '
    3BErWxMBAAAAMAAAAFwEAAAAAHIAAAAAAAEABHRlc3QAAnQyAAIDDwIeAAOaVcAI
    3BErWyABAAAAUwAAAK8EAAAAAHIAAAAAAAEAAgAC//wBAAAAAmMx/AIAAAACejL8AwAAAAJ6M/wE
    AAAAAmE0/AUAAAACYTX8BgAAAAJhNvk9iKY=
    '/*!*/;
    # at 1199
    #180621 10:47:56 server id 1  end_log_pos 1230 CRC32 0x9e871466 Xid = 264
    COMMIT/*!*/;

    找出误删除语句前后位置,分别是1116到1199;

    恢复
    mysqlbinlog --stop-position="1116" D:\mysql\mysql-5.7.22-winx64\data\mysql-bin.000009 |mysql -u root -p test 
    mysqlbinlog --start-position="1199" D:\mysql\mysql-5.7.22-winx64\data\mysql-bin.000009 |mysql -u root -p test 

    mysql> use test
    Database changed
    mysql> select * from t1;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | c1   |
    |    2 | z2   |
    |    3 | z3   |
    |    4 | c4   |
    |    5 | c5   |
    |    6 | c6   |
    +------+------+
    6 rows in set (0.00 sec)

    mysql> select * from t2;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | c1   |
    |    2 | z2   |
    |    3 | z3   |
    |    4 | a4   |
    |    5 | a5   |
    |    6 | a6   |
    +------+------+
    6 rows in set (0.00 sec)

    ------参考《深入浅出mysql》

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2156430/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/29785807/viewspace-2156430/

    展开全文
  • MySQL 备份和恢复

    2017-06-14 14:21:09
    摘要: MySQL备份和恢复机制,以及如何维护数据表,包括最主要的两种表类型:MyISAM 和 Innodb MySQL 备份和恢复 本文讨论 MySQL备份和恢复机制,以及如何维护数据表,包括最主要的两种表类型:MyISAM 和 ...
    阅读原文请点击:[url]http://click.aliyun.com/m/23216/[/url]
    摘要: MySQL 的备份和恢复机制,以及如何维护数据表,包括最主要的两种表类型:MyISAM 和 Innodb

    MySQL 备份和恢复
    本文讨论 MySQL 的备份和恢复机制,以及如何维护数据表,包括最主要的两种表类型:MyISAM 和 Innodb

    目前 MySQL 支持的免费备份工具有:mysqldump、mysqlhotcopy,还可以用 SQL 语法进行备份:BACKUP TABLE 或者 SELECT INTO OUTFILE,又或者备份二进制日志(binlog),还可以是直接拷贝数据文件和相关的配置文件。MyISAM 表是保存成文件的形式,因此相对比较容易备份,上面提到的几种方法都可以使用。Innodb 所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件,或者是独立的表空间文件),相对来说比较不好备份,免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump。

    mysqldump
    备份

    mysqldump 是采用SQL级别的备份机制,它将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之间升级时相对比较合适,这也是最常用的备份方法。
    现在来讲一下 mysqldump 的一些主要参数:

    --compatible=name
    它告诉 mysqldump,导出的数据将和哪种数据库或哪个旧版本的 MySQL 服务器相兼容。值可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等,要使用几个值,用逗号将它们隔开。当然了,它并不保证能完全兼容,而是尽量兼容。

    --complete-insert,-c
    导出的数据采用包含字段名的完整 INSERT 方式,也就是把所有的值都写在一行。这么做能提高插入效率,但是可能会受到 max_allowed_packet 参数的影响而导致插入失败。因此,需要谨慎使用该参数,至少我不推荐。

    --default-character-set=charset
    指定导出数据时采用何种字符集,如果数据表不是采用默认的 latin1 字符集的话,那么导出时必须指定该选项,否则再次导入数据后将产生乱码问题。

    --disable-keys
    告诉 mysqldump 在 INSERT 语句的开头和结尾增加/*!40000 ALTER TABLE table DISABLE KEYS */; 和 *!40000 ALTER TABLE table ENABLE KEYS */;语句,这能大大提高插入语句的速度,因为它是在插入完所有数据后才重建索引的。该选项只适合 MyISAM 表。

    --extended-insert = true|false
    默认情况下,mysqldump 开启 --complete-insert模式,因此不想用它的的话,就使用本选项,设定它的值为 false 即可。

    --hex-blob
    使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 BINARY、VARBINARY、BLOB。

    --lock-all-tables,-x
    在开始导出之前,提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭 --single-transaction 和 --lock-tables 选项。

    --lock-tables
    它和 --lock-all-tables 类似,不过是锁定当前导出的数据表,而不是一下子锁定全部库下的表。本选项只适用于 MyISAM 表,如果是 Innodb 表可以用 --single-transaction 选项。

    --no-create-info,-t
    只导出数据,而不添加 CREATE TABLE 语句。

    --no-data,-d
    不导出任何数据,只导出数据库表结构。

    --opt
    这只是一个快捷选项,等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 选项。本选项能让 mysqldump 很快的导出数据,并且导出的数据能很快导回。该选项默认开启,但可以用 --skip-opt 禁用。注意,如果运行 mysqldump 没有指定 --quick 或 --opt 选项,则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。

    --quick,-q
    该选项在导出大表时很有用,它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。

    --routines,-R
    导出存储过程以及自定义函数。

    --single-transaction
    该选项在导出数据之前提交一个 BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表,例如 InnoDB 和 BDB。
    本选项和 --lock-tables 选项是互斥的,因为 LOCK TABLES 会使任何挂起的事务隐含提交。
    要想导出大表的话,应结合使用 --quick 选项。

    --triggers
    同时导出触发器。该选项默认启用,用 --skip-triggers 禁用它。

    其他参数详情请参考手册,我通常使用以下 SQL 来备份 MyISAM 表:

    /usr/local/mysql/bin/mysqldump -uyejr -pyejr \
    --default-character-set=utf8 --opt --extended-insert=false \
    --triggers -R --hex-blob -x db_name > db_name.sql
    使用以下 SQL 来备份 Innodb 表:

    /usr/local/mysql/bin/mysqldump -uyejr -pyejr \
    --default-character-set=utf8 --opt --extended-insert=false \
    --triggers -R --hex-blob --single-transaction db_name > db_name.sql```
    另外,如果想要实现在线备份,还可以使用 --master-data 参数来实现,如下:
    /usr/local/mysql/bin/mysqldump -uyejr -pyejr \
    --default-character-set=utf8 --opt --master-data=1 \
    --single-transaction --flush-logs db_name > db_name.sql

    它只是在一开始的瞬间请求锁表,然后就刷新binlog了,而后在导出的文件中加入CHANGE MASTER 语句来指定当前备份的binlog位置,如果要把这个文件恢复到slave里去,就可以采用这种方法来做。

    注意:`--extended-insert `需要根据实际情况决定是否启用或关闭 ,会对数据恢复速度产生较大影响。


    #### 还原

    用 **mysqldump** 备份出来的文件是一个可以直接倒入的 SQL 脚本,有两种方法可以将数据导入。

    直接用 mysql 客户端
    例如:
    `/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql`

    用 SOURCE 语法
    其实这不是标准的 SQL 语法,而是 mysql 客户端提供的功能,例如:
    `SOURCE /tmp/db_name.sql;`

    这里需要指定文件的绝对路径,并且必须是 mysqld 运行用户(例如 nobody)有权限读取的文件。

    ### mysqlhotcopy
    #### 备份
    mysqlhotcopy 是一个 PERL 程序,最初由Tim Bunce编写。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径,但它只能运行在数据库文件(包括数据表定义文件、数据文件、索引文件)所在的机器上。mysqlhotcopy 只能用于备份 MyISAM,并且只能运行在 类Unix 和 NetWare 系统上。

    mysqlhotcopy 支持一次性拷贝多个数据库,同时还支持正则表达。以下是几个例子:

    root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr \
    db_name /tmp (把数据库目录 db_name 拷贝到 /tmp 下)
    root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr \
    db_name_1 ... db_name_n /tmp
    root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr \
    db_name./regex/ /tmp

    更详细的使用方法请查看手册,或者调用下面的命令来查看 `mysqlhotcopy` 的帮助:

    `perldoc /usr/local/mysql/bin/mysqlhotcopy`
    注意,想要使用 mysqlhotcopy,必须要有 `SELECT、RELOAD`(要执行 FLUSH TABLES) 权限,并且还必须要能够有读取 datadir/db_name 目录的权限。

    #### 还原

    mysqlhotcopy 备份出来的是整个数据库目录,使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是 /usr/local/mysql/data/)目录下即可,同时要注意权限的问题,如下例:

    root#cp -rf db_name /usr/local/mysql/data/
    root#chown -R nobody:nobody /usr/local/mysql/data/ (将 db_name 目录的属主改成 mysqld 运行用户)
    阅读原文请点击:[url]http://click.aliyun.com/m/23216/[/url]
    展开全文
  • mysql备份和恢复

    2021-04-13 18:58:27
    MySQL备份恢复 日志 分类 错误日志 通用查询日志 二进制日志 慢查询日志 开启日志 vim /etc/my.cnf [mysqld] ##错误日志,用来记录当MySQL启动、停止或运行时发生的错误信息,默认已开启 #指定错误日志的保存位置...

    MySQL备份恢复

    日志

    分类

    错误日志
    通用查询日志
    二进制日志
    慢查询日志

    开启日志

    vim /etc/my.cnf
    [mysqld]
    ##错误日志,用来记录当MySQL启动、停止或运行时发生的错误信息,默认已开启
    #指定错误日志的保存位置和文件名
    log-error=/usr/local/mysql/data/mysql_error.log
    
    ##通用查询日志,用来记录MySQL的所有连接和语句,默认是关闭的
    general_log=ON
    general_log_file=/usr/local/mysql/data/mysql_general.log
    
    ##二进制日志(binlog),用来记录所有更新了数据或者已经潜在更新了数据的语句,记录了数据的更改,可用于数据恢复,默认已开启
    log-bin=mysql-bin
    #也可以 log_bin=mysql-bin
    
    ##慢查询日志,用来记录所有执行时间超过long_query_time秒的语句,可以找到哪些查询语句执行时间长,以便于优化,默认是关闭的
    slow_query_log=ON
    slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
    long_query_time=5  #设置超过5秒执行的语句被记录,缺省时为10秒
    
    systemctl restart mysqld.service 
    
    

    在这里插入图片描述

    验证查询日志是否开启

    在这里插入图片描述

    查看二进制日志是否开启

    在这里插入图片描述

    查看慢查询日志相关功能

    在这里插入图片描述
    在这里插入图片描述

    补充

    在这里插入图片描述

    备份

    在这里插入图片描述
    在这里插入图片描述
    备份可以分为物理备份和逻辑备份
    在这里插入图片描述
    备份又可以从备份策略分为:
    在这里插入图片描述

    常见方法

    在这里插入图片描述

    MySQL完全备份

    在这里插入图片描述
    恢复
    在这里插入图片描述
    在这里插入图片描述

    mysqldump备份与恢复

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    MySQL 完全恢复

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    MySQL 增量备份

    1、开启二进制日志功能
    vim /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin
    server-id = 1
    binlog_format = MIXED				#指定二进制日志(binlog)的记录格式为 MIXED
    
    #二进制日志(binlog)有3种不同的记录格式:STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式),默认格式是STATEMENT
    
    systemctl restart mysqld.service
    ls -l /usr/local/mysql/data/mysql-bin.*
    
    

    在这里插入图片描述
    在这里插入图片描述

    每周对数据库或表进行完全备份

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    每天进行增量备份操作,生成新的二进制日志文件

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    查看二进制日志文件的内容

    在这里插入图片描述

    MySQL 增量恢复

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    断点恢复

    mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000006
    
    #部分二进制文件的内容
    ......
    BEGIN
    /*!*/;
    ##-------------解释:at xxx 表示位置点------------------------------------------------
    # at 587
    ##--------------解释:开头210206 15:45:53表示时间,其他的现在用不到-----------------------------------
    #210412  7:54:21 server id 1  end_log_pos 734 CRC32 0x46f74eb6 	Query	thread_id=25	exec_time=0	error_code=0
    ##--------------解释:这里是执行的操作语句---------------------
    SET TIMESTAMP=1618228461/*!*/;<------------建立时间戳
    insert into class values ('6','srs6','男','00006','666666','城南')<-------向表中插入数据
    /*!*/;
    # at 734
    #210412  7:54:21 server id 1  end_log_pos 765 CRC32 0x8c58b8d9 	Xid = 411
    COMMIT/*!*/;
    # at 765
    #210412  7:56:51 server id 1  end_log_pos 830 CRC32 0x546d845a 	Anonymous_GTID	last_committed=2	sequence_number=3
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 830
    #210412  7:56:51 server id 1  end_log_pos 913 CRC32 0xdf882827 	Query	thread_id=25	exec_time=0	error_code=0
    SET TIMESTAMP=1618228611/*!*/;
    
    ##-------------------------------插入第二个数据--------------------------
    BEGIN
    /*!*/;
    # at 913
    #210412  7:56:51 server id 1  end_log_pos 1060 CRC32 0xd56daa4d 	Query	thread_id=25	exec_time=0	error_code=0
    SET TIMESTAMP=1618228611/*!*/;
    insert into class values ('7','srs7','男','00007','777777','城南')
    /*!*/;
    # at 1060
    #210412  7:56:51 server id 1  end_log_pos 1091 CRC32 0x42ca40ae 	Xid = 412
    COMMIT/*!*/;
    # at 1091
    #210412  7:57:39 server id 1  end_log_pos 1138 CRC32 0x777f989b 	Rotate to mysql-bin.000007  pos: 4
    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    DELIMITER ;
    .......
    
    

    在这里插入图片描述

    1)基于位置恢复
    仅恢复到位置点为“913”之前的数据,即不恢复“id=7”的数据
    #模拟数据丢失
    mysql -uroot -p school < /opt/school_class_2021-04-12.sql
    mysql -uroot -p -e "select * from school.class;"
    #到位置点913停止恢复数据
    mysqlbinlog --no-defaults --stop-position='913' /opt/mysql-bin.000006 | mysql -uroot -p
    #查看class表的数据
    mysql -uroot -p -e "select * from school.class;"
    
    仅恢复“id=7”的数据,跳过“id=6”的数据
    #模拟数据丢失
    mysql -uroot -p school < /opt/school_class_2021-04-12.sql
    mysql -uroot -p -e "select * from school.class;"
    #从位置点913开始恢复数据
    mysqlbinlog --no-defaults --start-position='913' /opt/mysql-bin.000006 | mysql -uroot -p
    #查看class表的数据
    mysql -uroot -p -e "select * from school.class;"
    
    

    在这里插入图片描述
    在这里插入图片描述

    2)基于时间点恢复
    仅恢复到210412  7:56:51之前的数据,即不恢复“id=7”的数据
    #模拟数据丢失
    mysql -uroot -p school < /opt/school_class_2021-04-12.sql
    mysql -uroot -p -e "select * from school.class;"
    #到21-04-12  7:56:51截止恢复数据
    mysqlbinlog --no-defaults --stop-datetime='21-04-12  7:56:51' /opt/mysql-bin.000006 | mysql -uroot -p
    #查看class表的数据
    mysql -uroot -p -e "select * from school.class;"
    
    仅恢复“id=7”的数据,跳过“id=6”的数据恢复
    #模拟数据丢失
    mysql -uroot -p school < /opt/school_class_2021-04-12.sql
    mysql -uroot -p -e "select * from school.class;"
    #从21-04-12  7:56:51开始恢复数据
    mysqlbinlog --no-defaults--start-datetime='21-04-12  7:56:51' /opt/mysql-bin.000006 | mysql -uroot -p
    #查看class表的数据
    mysql -uroot -p -e "select * from school.class;"
    
    

    在这里插入图片描述
    在这里插入图片描述

    展开全文
  • Mysql 备份和恢复

    2012-12-07 17:34:50
    Mysql 备份和恢复 备份整个数据库 [root@localhost bin]# ./mysqldump db1 > /tmp/e.txt 恢复数据库 方式1: mysql> create database db1; [root@localhost bin]# ./mysql db1 方式2: mysql> ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 5,850
精华内容 2,340
关键字:

mysql备份和恢复

mysql 订阅