mysql数据库备份

物理备份

    cp    -rp    /var/lib/mysql/数据库    备份目录/文件名

    tar    -zcvf    xxx.tar.gz    /var/lib/mysql/数据库/*

恢复操作

    cp    -rp    备份目录/文件名    /var/lib/mysql/

    tar    -zxvf    xxx.tar.gz    -C    /var/lib/mysql/数据库名/

逻辑备份（需要验证用户授权）

    mysqldump    -uroot    -p密码    库名    >    路径/xxx.sql

    mysql    -uroot    -p密码    库名    <    路径/xxx.sql

库名表示方式

    -A或--all-databases            所有库

    -数据库名                          单个库

    -数据库名    表名               单张表

    -B   数据库1    数据库2      多个库

例：mysqldump    -uroot    -p    --all-databases    >    alldb.sql

Binlog日志概述

1）采用binlog日志的好处

    记录除查询之外的所有SQL命令

    可用于数据恢复

    配置mysql主从同步的必要条件

 

2）启用binlog

    vim    /etc/my.cnf    

    添加：log_bin[=dir/name]

             server_id=数字            //每个数据库服务器的id都需要不一样

             max_binlog_size=大小（带单位）

             binlog_formart="mixed"        //binlog的记录格式

    binlog记录格式:

        statement:每一条修改数据的sql命令都会记录在binlog日志中（记录修改命令）

        row:不记录sql语句上下文相关信息，仅保存哪条记录被修改（记录修改内容）

        mixed：以上两种格式的混合使用

    systemctl    start    mysqld

3）手动生成新的日志文件（先清空生成一个新的）

    -mysql>flush    logs;

    -mysqldump    --flush-logs

    -mysql    -uroot    -p密码    -e    'flush    logs'

4）删除早于指定版本的binlog日志

    mysql>purge    master    logs    to    "binlog文件名"

5）删除所有binlog日志，重建新日志

    mysql>reset    master

6）分析binlog日志（使用mysqlbinlog工具）

    mysqlbinlog    选项    binlog日志文件名(master1.000001)

    常用选项

    --start-datetime="yyyy-mm-dd    hh:mm:ss"

    --stop-datetime="yyyy-mm-dd    hh:mm:ss"    //通过时间戳选择

    --start-position=数字    --stop-position=数字    //通过偏移量选择

利用binlog数据恢复

    思路：使用mysqlbinlog提取历史sql操作

              通过管道交给mysql命令执行

    cd    /var/lib/mysql

    mysqlbinlog    master1.000001    |    mysql    -uroot    -p123456

XtraBackup工具

    特点：备份过程中不锁库表，适合生产环境

    主要组件：

            xtrabackup:C程序，支持InnoDB/XtraDB

            innobackupex:以perl脚本封装xtrabackup,还支持myisam

    安装xtrabackup

innobackup备份/恢复

    物理备份的缺点：跨平台性差，备份时间长，浪费存储空间

    mysqldump缺点：效率较低，备份过程中数据插入和更新操作会被挂起

innobackupex基本选项

    --host                               //主机名

    --user                               //用户名

    --port                               //端口号

    --password                        //密码

    --databases                       //数据库名

    --no-timestamp                  //不使用时间戳命名

    --databases="库名"            //单个库

    --databases="库1  库2"       //多个库

    --databases="库.表"           //单个表

    --redo-only                        //日志合并

    --apply-log                        //准备还原（回滚日志）

        **在数据恢复的时候使用

    --copy-back                       //恢复数据

    --incremental    目录名        //增量备份

    --incremental-basedir=目录名    //增量备份时，指定上一次备份数据存储的目录名

    --incremental-dir=目录名    //准备恢复数据时，指定增量备份数据存储的目录名

    --export                            //导出表信息

    import                              //导入表空间

innobackupex应用

   1.完全备份

    1）将数据库完全备份到/backup

    innobackupex    --user    root    --password    123456    /backup    --no-timestamp

    2）准备恢复数据

    innobackupex    --user    root    --password    123456    --apply-log    /backup

    rm    -rf    /var/lib/mysql/*        //恢复时要求空的库目录

    3）恢复数据

    innobackupex    --user    root    --password    123456    --copy-back    /backup

    4）将数据库目录的属主更改为mysql

    chown    -R    mysql:mysql    /var/lib/mysql

    systemctl    restart    mysqld

    2.增量备份与恢复（必须要有一次备份，通常是完全备份）

    1）完全备份某库

    innobackupex    --user    root    --password    123456    --database="库名"    /fullback    --no-timestamp

    2）第一次增量备份

    innobackupex    --user    root    --password    123456    --incremental    /new1    --incremental-basedir="/fullback"    --no-timestamp    

    3）第二次增量备份

    innobackupex    --user    root    --password    123456    --incremental    /new2    --incremental-basedir="/new1"    --no-timestamp

    4）清空数据库目录

    rm    -rf    /var/lib/mysql/*

    5）恢复准备

    innobackupex    --user    root    --password    123456    --databases="库名列表"    --apply-log    --redo-only    /fullback

    6）恢复增量1

    innobackupex    --user    root    --password    123456    --database="库名列表"    --apply-log    --redo-only    /fullback    --incremental-dir="/new1"

    7） 恢复增量2

    innobackupex    --user    root    --password    123456    --database="库名列表"    --apply-log    --redo-only    /fullback    --incremental-dir="/new2"

    8）恢复数据

    innobackupex    --user    root    --password    123456    --database="库名列表"    --copy-back    /fullback

    9）将恢复的数据库属主更改

    chown    -R    mysql:mysql    /var/lib/mysql/

    systemctl    restart    mysqld

3.在完全备份文件中恢复单个表

    1）导出表信息

    innobackupex    --user    root    --password    123456    --database="gamedb"    /allbak    --no-timestamp

    mysql>drop    table    gamedb.a;        //将表删除

    innobackupex    --user    root    --password    123456    --database="gamedb"    --apply-log    --export    /allbak                //导出表信息

    mysql>create    table    gamedb.a(id    int);    //创建表

    mysql>alter    table    gamedb.a    discard    tablespace;        //删除表空间

    cp    /allbak/gamedb.a/a.{ibd,cfg,exp}    /var/lib/mysql/gamedb    //拷贝表信息文件

    chown    mysql:mysql    /var/lib/mysql/bbsdb/a.*            //修改所有者

    mysql>alter    table    gamedb.a    import    tablespace        //导入表空间，恢复表空间​

    select    *    from    gamedb.a;        //查看原表数据，恢复表成功