当前位置:Gxlcms > mysql > MySQL(四):备份恢复详解

MySQL(四):备份恢复详解

时间:2021-07-01 10:21:17 帮助过:39人阅读

一、备份的类型随着互联网的迅速发展;数据的重要性不需要多说;相信大家都知道。所以数据备份就显得格外重要了;并且选择一个合适的备份方法能提高性能。下面就

一、备份的类型

随着互联网的迅速发展;数据的重要性不需要多说;相信大家都知道。所以数据备份就显得格外重要了;并且选择一个合适的备份方法能提高性能。下面就备份类型来分类:

  • 根据备份时;数据库服务器是否在线:

  • 冷备:cold backup

    温备:warm backup

    热备:hot backup 基于事务的存储引擎(InnoDB)


  • 根据备份时的数据集:

  • 完全备份:full backup

    部分备份:partial backup


  • 根据备份时的接口(直接备份数据文件还是通过mysql服务器导出数据):

  • 物理备份(physical backup):直接复制(归档)数据文件的备份方式;

    1、不需要额外工具

    2、跨平台性能差

    3、表空间占据大


    逻辑备份(logical backup):把数据从库中提取出来保存为文本文件;

    1、可以使用编辑器处理数据文件

    2、可以使用批处理或网络恢复数据;可以更换数据引擎

    3、有助于避免数据损坏等

    缺陷:恢复慢;无法保证浮点数的精度;还原数据后需要重建索引,消耗时间和资源


  • 根据备份时数据变化(整个数据|变化的数据):

  • 1、完全备份:full backup

    2、增量备份:incremental backup

    3、差异备份:differential backup


    二、基本的备份工具

    1、mysqldump:逻辑备份工具

    对InnoDB热备、MyISAM温备、Aria温备

    备份和恢复过程较慢


    2、mysqldumper:多线程的mysqldump

    很难实现差异或增量备份;


    3、基于lvm-snapshot:

    接近于热备的工具;因为要先请求全局锁;而后创建快照;并在创建完成后释放全局锁

    cp、tar等工具进行物理备份:仅限于冷备

    备份和恢复速度较快;

    很难实现增量备份;并且请求全局需要等待一段时间;在繁忙的服务器上尤其如此。


    4、部分备份工具;不会备份关系定义;仅备份表中的数据;逻辑备份工具

    select clause into outfile '/path/to/somefile'

    load data infile '/path/to/somefile'


    5、Xtrabackup:由percona提供的开源备份工具

    InnoDB热备;增量备份

    MyISAM温备;不支持增量

    物理备份;速度快


    三、基于mydump进行备份

    #使用格式 [root@localhost ~]# mysqldump Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS] For more options, use mysqldump --help #详细可以--help查看;man也可以查看详细说明
  • 备份单个库

  • #备份单个库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | | ultrax | +--------------------+ 6 rows in set (0.00 sec) #假设备份上述中hellodb数据库 [root@localhost ~]# mysqldump -uroot -p --databases hellodb --lock-all-tables > /tmp/hdb.sql Enter password: [root@localhost ~]# # #删除、恢复 mysql> drop database hellodb; Query OK, 7 rows affected (0.20 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | ultrax | +--------------------+ 5 rows in set (0.00 sec) #恢复 mysql> source /tmp/hdb.sql Query OK, 0 rows affected (0.00 sec) ... mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | | ultrax | +--------------------+ 6 rows in set (0.00 sec) #测试恢复成功
  • 完全备份以及利用二进制日志进行时间点恢复

  • [root@localhost ~]# mysqldump -uroot -p --lock-all-tables --all-databases --flush-logs --master-data=2 --events >/tmp/all.sql Enter password: #--lock-all-tables 请求锁;不让其他用户此刻做修改 #--all-databases 备份所有数据库 #--flush-logs 滚动日志 #--master-data=2 记录二进制日志的位置 #--events 导出事件 [root@localhost ~]# vim /tmp/all.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107; #这行记录的就是二进制日志的位置;后续需要依赖进行时间点恢复 # # # #进行测试删除和恢复 mysql> create database soul; Query OK, 1 row affected (0.00 sec) mysql> use soul; Database changed mysql> create table t1 (ID int); Query OK, 0 rows affected (0.06 sec) mysql> insert into t1 values (1),(2),(3); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> drop database soul; Query OK, 1 row affected (0.05 sec) mysql> drop database hellodb; Query OK, 7 rows affected (0.00 sec) #利用备份恢复 mysql> set session sql_log_bin=0; #关闭记录二进制日志;因为恢复过程不需要记录 mysql> source /tmp/all.sql mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | | ultrax | +--------------------+ 6 rows in set (0.00 sec) #时间点之后的还没有 [root@localhost ~]# mysqlbinlog --start-position=107 /data/mydata/mysql-bin.000007 # at 547 #位置 #140324 1:04:00 server id 1 end_log_pos 628 Query thread_id=33 exec_time=0 error_code=0 SET TIMESTAMP=1395594240/*!*/; drop database soul #删除的库 /*!*/; # at 628 #位置 #140324 1:04:20 server id 1 end_log_pos 715 Query thread_id=33 exec_time=0 error_code=0 SET TIMESTAMP=1395594260/*!*/; drop database hellodb #删除的库 /*!*/; #能查看完全备份之后的操作;也能看到删除的操作;所以在此截取后来恢复 [root@localhost ~]# mysqlbinlog --start-position=107 --stop-position=547 /data/mydata/mysql-bin.000007 >/tmp/bin.sql mysql> source /tmp/bin.sql ... mysql> use soul; Database changed mysql> show tables -> ; +----------------+ | Tables_in_soul | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) mysql> select * from t1; +------+ | ID | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.15 sec) mysql> set session sql_log_bin=1; Query OK, 0 rows affected (0.00 sec) #在恢复的过程中;应该限制其他用户的任何操作;可以通过iptables来关闭端口;或其他策略 #备份除了数据文件还有二进制文件、配置文件等

    人气教程排行