数据库的备份与恢复 mysqldump+binlog方式

实验说明：

（1）mysqldump：逻辑备份工具，适用于所有存储引擎，可用于温备，能实现完全备份，部分备份；对InnoDB存储引擎支持热备；

        binlog：二进制日志

        二进制日志也叫作变更日志，主要用于记录修改数据或有可能引起数据改变的mysql语句，并且记录了语句发生时间、执行时长、操作的数据等等。所以说通过二进制日志可以查询mysql数据库中进行了哪些变化。一般大小体积上限为1G

（2）本次实验使用备份方式:完全备份+增量备份。

（3）musqldump命令的语法格式

mysqldump [OPTIONS] database [tables]：备份单个库，或库指定的一个或多个表

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：备份一个或多个库

mysqldump [OPTIONS] --all-databases [OPTIONS]：备份所有库

    其他选项

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

        -l, --lock-tables：锁定备份的表

        --single-transaction：启动一个大的单一事务实现备份

        -C, --compress：压缩传输

        -E, --events：备份指定库的事件调度器

        -R, --routines：备份存储过程和存储函数

        --triggers：备份触发器

        --master-data={0|1|2}

            0：不记录

            1：记录CHANGE MASTER TO语句；此语句未被注释

            2：记录为注释语句

            -F，--flush-logs：锁定表之后执行flush logs命令

1、安装数据库

    centos6：  yum  install mysql

    centos7：  yum  install mariadb

2、开启二进制日志

      在配置文件修改

        vim  /etc/my.cnf

        [mysqld]   

         log-bin=bin-log  添加改行，采用相对路径，真实存储路径为/var/lib/mysql/下

3、启动服务

        systemctl start mariadb

        ls /var/lib/mysql

        会看到bin-log.000001 文件

        每次重启mysql服务或运行mysql> flush logs;都会生成一个新的二进制日志文件，这些日志文件的number会不断地递增。除了生成上述的文件外还会生成一个为filename.index（bin-log.index）的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

4、登入数据库并创建需要备份的数据库

        mysql -uroot -p

   （1）建立数据库

        create database mariadb  

   （2）建立表

        use mariadb

        create table table1(id int ,name char(20));

        show tables；查看是否创建表成功

   （3）插入数据

           insert into table1  values (1,'one');

            insert into table1  values (2,'two');

            insert into table1  values (3,'three');

           insert into table1  values (4,'for'); 

            查看是否插入数据到表中

            select * from table1

5、进行完整备份

    （1）创建要备份的目录

            mkdir /backup

      （2）备份

       mysqldump --databases mariadb --lock-all-tables --lock-tables  --flush-log --master-data=2 >/backup/`date +%F-%T`-all.sql    

      （3） 查看是否备份成功

            ls  /backup

6、向表中插入数据，进行增量备份

     （1）查看 当前使用的二进制文件及所处位置

            show master status； 

    （2）插入数据

        insert into table1 values (5,five);

7、增量备份

    （1）查看二进制日志

        mysqlbinlog /var/lib/mysql/bin-log.000002

        选择备份的位置

    插入数据前，即完全备份的位置在245

    插入数据后，位置在418

   说明：整个事务一般以BEGIN开头到COMMIT结束，注意一定要看清，很容易出错。

（2）进行增量备份

        mysqlbinlog --start-position=245 --stop-position=418 /var/lib/mysql/bin-log.000002 > /backup/`date +%F-%T`-add.sql

    ls /backup

 8、继续插入数据，在没备份的情况下删除数据库，模拟误操作

     inset into table1 values （6，'six'）;

     drop  database mariadb;

9、不要慌张

10、备份未备份的操作

    由于最后我们没有备份就删除了数据库，所以我们首先需要保护最后的二进制日志，查看删除操作之前的position值

    mysqlbinlog /var/lib/mysql/bin-log.000002

   所以备份位置从结束位置在643

     mysqlbinlog  --start-position=445  --stop-position=643 /var/lib/mysql/bin-log.000002 > /backup/`date +%F-%T`-add.sql

    一定不要备份误删除的位置，不然恢复后，又被删除，就恢复不了。

11、恢复数据库

    mysql < /backup/2017-11-16-14\:24\:31-all.sql 先恢复完全备份

    mysql < /backup/2017-11-16-14\:53\:33-add.sql  恢复第一个增量备份

    mysql < /backup/2017-11-16-15\:13\:23-add.sql  恢复最后备份

12、登录数据库查看

   数据恢复成功。数据位置为完全正确。

本文转自 hawapple 51CTO博客，原文链接:http://blog.51cto.com/guanm/1982422