mysql备份、恢复之mysqldump

  mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等

 mysqldump 语法 : 

  默认配置读取路径:  /etc/mysql/my.cnf     /etc/my.cnf    ~/.my.cnf  

   Usage: mysqldump [OPTIONS] database [tables] 

   OR   mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] 

   OR   mysqldump [OPTIONS] --all-databases [OPTIONS]

常用参数 :  
    -u, --user=name         指定用户 
    -h, --host=name         指定主机 
    -p, --password[=name]      指定密码 
    -A, --all-databases       指定所有库,-A与--all-databases不同时使用 
    -B, --databases         只指定--databases表示备份单个数据库;多数据库之间使用空格隔开;
                     如果不指定--databases表示备份整个数据库;只指定dbname表示备份单张表；多表之间使用空格隔开
    --master-data[=#]         0 表示关闭,默认值为 0 
                     1 表示开头打印CHANGE MASTER命令信息,在复制场景中有用 
                     2 表示添加注释信息,记录当前所处二进制日志及所处位置 
    --single-transaction       如果全部为 InnoDB引擎表,则不需要使用 --lock-all-tables;
                     基于此选项能实现热备InnoDB表;会启动单个大事务,对全局服务影响可能是持久的
    --lock-all-tables         执行备份前请求锁定表,一般只锁定当前要备份的数据库及表 
    --add-drop-database        在恢复之前先删除已经存在的对应数据库 
    --add-drop-table         在恢复之前先删除已经存在的对应表 
    -C, --compress          将数据先压缩后通过网络传输 
    -E, --events           备份数据同时，备份事件调度器代码 
    -R, --routines          备份数据同时，备份存储过程和存储函数 
    -d, --no-data          表示只备份表结构不备份数据,复制表结构、空库至其它服务器时有用.其它任何时候不可用  
    --opt               同时启动各种高级选项

 备份方式 : 

    全量+增量+二进制日志 

      前提需要在配置中添加参数 : 

        default-storage-engine = InnoDB       设置默认存储引擎为InnoDB 

        innodb_file_per_table=1            设置每表单个表空间 

        log-bin=mysql-bin                启用二进制功能

备份过程 : 
    全量备份 : 
      使用InnoDB引擎,备份使用--single-transaction选项不需要锁定表即实现热备;使用非InnoDB引擎,备份需要使用--lock-all-tables选项进行请求锁定所有表后开始备份
 
      mysqldump -uroot -pmypass --single-transaction --all-databases --master-data=2 > /backup/mysql_fullbak.sql
 
    增量备份 : 
      mysql>SHOW MASTER STATUS;              查看当前处于哪个日志文件及位置 
         less  /backup/mysql_fullbak.sql     查看从哪个位置开始 
      mysqlbinlog --start-position=4579 --stop-position=4868 mysql-bin.000015 > /backup/increment.sql  指定开始及结束位置导出成增量备份
      
    二进制日志备份: 
      mysql>INSERT INTO test (Name,Age) VALUES (‘tom‘,18);       增量备份后,插入一条数据
      mysql>DROP DATABASE hello;                                 此时删除数据库
      mysql>SHOW MASTER STATUS;                           查看当前处于哪个日志文件及位置
      mysqlbinlog --start-position=4868 mysql-bin.000015  查看数据库崩溃前二进制日志开始位置,结束位置必须是数据库删除之前位置
      mysqlbinlog --start-position=4868 --stop-position=5062 mysql-bin.000015 > /tmp/hello.sql 将二进制日志启动时位置至崩溃前位置数据导出

恢复过程 : 
    前提 : 清空数据目录下所有文件,重新初始化数据库 
      mysql>SET GLOBAL sql_log_bin=0;      恢复时需要临时关闭二进制日志,如不关闭,恢复数据将记录进二进制日志增加日志管理难度
      mysql>FLUSH LOGS;                    手动对二进制日志进行一次滚动
      mysql < /backup/mysql_fullbak.sql    恢复全量备份
      mysql < /backup/increment.sql        恢复增量备份
      mysql < /tmp/hello.sql               恢复崩溃前导出二进制数据
      mysql>SET GLOBAL sql_log_bin=1;      恢复完成开启二进制日志

  恢复后需要使用 mysqlcheck -uroot -pmypass -c -all-databases 检查所有表是否存在问题,所有恢复完成后请记得对数据库做一次全量备份

  至此,使用mysqldump备份恢复完成,如有错误请大家指教！

本文出自 “Why no !” 博客，请务必保留此出处http://mydove.blog.51cto.com/8487472/1790176

原文：http://mydove.blog.51cto.com/8487472/1790176