mysql之备份恢复

一、为什么要备份?

    灾难恢复

    需求改变

    测试


二、事先考虑的问题

    可以容忍丢失多长时间的数据?

    恢复要在多长时间内完成?

    是否需要持续提供服务?

    需要恢复什么,整个数据库服务器?单个数据库?一个或多个表?某个语句?


三、备份类型

    根据是否需要数据库离线分为:

      冷备:cold backup,关闭mysql服务,或不允许读写请求

      温备:warm backup,备份的同时仅支持读请求

      热备:hot backup,备份的同时,业务功能不受影响,需要工具和数据库引擎支持

    根据要备份的数据范围可分为:

      完全备份:full backup,备份全部数据集,服务器、数据库等根据关注点觉定

      增量备份:incremental backup,上次完全备份或增量备份以来改变了的数据;

      差异备份:differential backup,上次完全备份以来改变了的数据

    根据备份数据或是文件,可以分为:

      物理备份:直接备份数据文件

      逻辑备份:备份表中数据和库代码等


四、备份对象:

    数据:

    配置文件:经常备份/etc目录

    代码:存储过程、存储函数、触发器等;

    OS相关的配置文件:

    二进制日志:


    注:备份完成后在测试机上测试备份内容

    mysql> CHECK TABLES


五、备份工具:

    mysqldump:

      逻辑备份工具

      InnoDB支持热备、MyISAM仅温备

      备份和恢复的速度较慢

    mydumper:

      多线程备份工具

    lvm-snapshot:

      接近于热备的工具

      物理备份

      备份和恢复较快

    SELECT INTO OUTFILE  通常备份单表

      LOAD DATA INFILE   使用LOAD加载恢复数据

      逻辑备份工具

    ibbackup:

    Xtrabackup:开源版ibbackup

      物理备份工具

      支持InnoDB热备、MyISAM温备

      速度快

    mysqlhotcopy:冷备工具


六、恢复操作:

    1.停止MySQL服务

    2.记录服务的配置和文件权限

    3.复制备份文件至数据目录

    4.按需调整配置

    5.按需调整文件权限

    6.尝试启动服务

    7.装载逻辑备份

    8.检查和重放二进制日志

    9.确定数据还原正常完成

    10.以完全权限重启服务器


例一:使用SELECT INTO OUTFILE备份恢复单表

  mysql> SELECT * FROM test1 INTO OUTFILE ‘/tmp/sql1.backup‘

  mysql> DELETE FROM TABLE 

  myslq> LOAD DATA INFILE ‘/tmp/sql1.backup‘ INTO TABLE test1


例二:mysqldump的MySQL客户端工具使用

  mysqldump可以备份整个服务器,单个或部分数据库,单个或部分表,表中某些行,存储过程,存储函   数,触发器

  能自动记录备份时的二进制日志文件及相应position


    -uusername  -hhostname  -ppassword

    -B, --databases dbname 备份指定数据库

    -x,--lock-all-tables 锁定所有表

    -l,--lock-tables 锁定单张表

    --master-data=[0|1|2]  0不启用,1启用,2记录正在访问的二进制日志并记录访问点

    --single-transaction  基于此选项能实现InnoDB热备,不需要同时使用--lock-all-tables;

    -A,--all-databases;备份整个服务器上的所有库


  mysql> FLUSH TABLES WITH READ LOCK;请求关闭所有表

  mysql> SHOW ENGINE INNODB STATUS; 查看INNODB状态,确保不再有写入

  [root@localhost ~]# mysqldump -uroot -pmysql --databases TESTDB --lock-all-tables >         /tmp/backupdb.sql

  mysql> DROP DATABESE TESTDB;

  mysql> source /tmp/backupdb.sql

  mysql> UNLOCK TABLES; 释放锁

  注:恢复时注意当前所在库,如果原库不存在请新建并use.



  myslqdump热备-->   完全备份 + 增量备份 + 二进制 

  # mysqldump -uroot -pmypass --single-transaction --master-data=2 --all-databases > 

    /backup/`date +%F`.sql     完全备份

  # mysqlbinlog --start-position=4579 --stop-position=4868 mysql-bin.000021 > 

    /backup/`date +%F_%H`.sql   增量备份

  # mysqlbinlog --start-position=4868 --stop-position=5062 mysql-bin.000021 > 

    /tmp/backup.sql   最近一次增量备份之后的数据从二进制文件导出

 备份恢复需要mysql服务器离线,

    mysql> SET sql_log_bin=0; 停止二进制日志

    mysql> flush logs;  滚动日志

    mysql> source /backup/filename.sql   完全备份文件恢复

    mysql> source /backup/filename.sql   增量备份文件恢复

    mysql> source /backup/filename.sql   二进制文件恢复

  注:也可以使用mysql -uroot -pmypass < /backup/filename.sql 输入重定向恢复




例三:使用二进制日志操作

  mysql> SHOW {BINARY| MASTER} LOGS; 查看二进制和主服务器日志

  mysql> SHOW MASTER STATUS;查看当前正在使用的二进制日志及当前所处位置

  mysql> SHOW BINLOG EVENTS IN ‘mysql-bin.000021‘ FROM 3321(指定查看位置); 查看日志内容

  mysql> FLUSH LOGS; 滚动日志,可以理解为建立并使用新的二进制日志

  mysql> PURGE BINARY LOGS TO ‘mysql-bin.000010‘;清除10以前的日志(不建议清除或备份后再清     除)

  误操作恢复:

  mysql> drop database test;

  [root@charce data]# mysqlbinlog mysql-bin.000021 查看二进制日志的操作位置

  [root@charce data]# mysqlbinlog --stop-position=3447 mysql-bin.000021 > /tmp/a.sql

 mysql> source /tmp/a.sql;

  mysql> SHOW DATABASES;

  mysql> SHOW TABLE test;

  注:在恢复的过程中如果继续有二进制日志写入,则需FLUSH LOGS之后再恢复。




本文出自 “滴不尽相思血泪抛红豆” 博客,请务必保留此出处http://beijgh.blog.51cto.com/8272564/1545243

mysql之备份恢复

上一篇:PHPCMS v9修改默认后台登录地址


下一篇:Handlebars.js循环中索引(@index)使用技巧(访问父级索引)