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