二进制日志文件

二进制日志(bin_log)
二进制日志文件是用来记录所有用户对数据库的操作,即记录用户对数据库操作的sql语句。当数据库发生意外时,可以通过该日志查看到用户对数据库的操作,在和数据库备份配合使用,可以将损失最小化。
mysql启用和查看二进制文件

  • 二进制日志的开启
    二进制日志的开启由 sql_log_bin 和 log_bin 一起来决定。sql_log_bin 决定了二进制日志是否开启;log_bin 决定了二进制日志的存储路径。 只有当sql_log_bin=on 并且在/etc/my.cnf 中指定了log_bin 的路径,二进制日志记录才会开启。如下图所示:
    数据库的备份与恢复数据库的备份与恢复
  • 二进制日志的查看
    show binary logs 查看二进制日志
    show master logs  查看所有的二进制日志
    show master status  查看当前的二进制日志

    数据库的备份与恢复

  • 二进制日志的预览和导出
    mysqlbinlog [ --start-position= 位置  --stop-positon=位置 ]  mysql-log.000001  预览指定位置的二进制日志记录
    mysqlbinlog [ --start-position= 位置  --stop-positon=位置 ] mysql-log.000001 >  a.sql  导出指定位置的二进制日志记录到 a.sql 中

基与LVM的热备

lvm的热备是通过利用lvm快照进行备份,即为了保持系统的一致性,我们先做一个快照冻结当前系统状态,这样快照里面的内容可暂时保持不变,系统本身继续运行,通过备份快照来实现不中断服务的的备份。
以下通过具体操作来展示LVM热备的过程:

  1. 环境准备(创建基于lvm的数据库)
    我们需要搭建好数据库,在这里我们把数据库做在逻辑卷上,并且指定了数据库中的数据的存储路径以及开启二进制日志记录。 还有准备了备份目录(生产环境中,备份与数据是分开的),如下:
    准备一个新的分区/dev/sda6
    pvcreate /dev/sda6
    vgcreate vgdata  /dev/sda6
    lvcreate -L 5G -n lvdata vgdata
    lvcreate -L 3G -n lvlogbin vgdata
    mkfs.xfs /dev/vgdata/lvdata
    mkfs.xfs /dev/vgdata/lvlogbin
    mkdir -pv /mysql/{data,logbin}
    mount /dev/vgdata/lvdata /mysql/data
    mount /dev/vgdata/lvlogbin /mysql/logbin
    chown -R mysql.mysql /mysql
    vim /etc/my.cnf
    datadir=/mysql/data
    log_bin=/mysql/logbin
    systemctl start mariadb.service
    mysql < hellodb_innodb.sql

    就绪环境如下:
    数据库的备份与恢复

  2. 备份
    锁定表
    flush tables with read lock;

    数据库的备份与恢复
    切换日志

    flush logs;

    数据库的备份与恢复
    查看当前二进制日志位置并记录

    show master status;

    数据库的备份与恢复
    创建lvm快照 (实际场景中,创建快照的过程在锁表之后应迅速完成)

    lvcreate -n lvdata_snap -L 1G  -s -p r  /dev/vgdata/lvdata

    数据库的备份与恢复
    解锁

    unlock tables;

    数据库的备份与恢复

  3. 增加新数据
    向表中随意更改一些数据,来模拟现实中新产生的还未来得及备份的新数据,这里我们向hellodb库中的students表中添加一行新数据
    insert into hellodb.students values (26,'lishuyang',23,'m',null,null);

    数据库的备份与恢复
    数据库的备份与恢复
    注:这条新增的数据只存在于日志中,并不存在于备份中

  4. 挂载lvm快照
    lvcreate -n lvdata_snap -L 1G -s -p r /dev/vgdata/lvdata

    数据库的备份与恢复数据库的备份与恢复
    将快照数据拷贝到备份目录中

    cp -a /mysql/lvmbackup/*  /mysql/backup/
  5. 卸载并删除快照
    umount /mysql/lvmbackup
    lvremove /dev/vgdata/lvdata_snap

    直到这一步,我们的备份就完全完成了,备份数据存放在了/mysql/back中

  6. 模拟崩溃
    在这里我们直接进行比较暴力的破坏,直接删除库数据
    rm /mysql/data/* -rf
    ls /mysql/data/

    数据库的备份与恢复
    可以看到我们的数据库丢失了全部数据

  7. 恢复
    恢复备份数据
    cp -a /mysql/back/*  /mysql/data/
    ls data/

    数据库的备份与恢复
    此时,我们已经将备份过数据完全恢复到数据库中了,但是未被备份的新数据不在其中,我们后来新加的一条数据,现在的数据库中是没有的
    数据库的备份与恢复数据库的备份与恢复数据库的备份与恢复
    接下来我们需要把最新的数据恢复,就要用到我们的二进制日志的记录,由上面的记录我们知道,新数据在日志mysql-bin.000005 245之后的记录中,所以我们需要将日志mysql-bin.000005 245之后的日志记录全部恢复
    首先我们需要关闭远程客户访问:

    vim /etc/my.cnf
    skip_networking
    systemctl restart mariadb.service
    或
    iptables -A INPUT -p tcp --dport 3306 -j DROP

    查看全部日志记录

    ls /mysql/logbin

    数据库的备份与恢复
    恢复二进制日志记录的未备份的数据,

    mysqlbinlog --start-position=245 /mysql/logbin.000005 > /root/bin.sql
    mysql <  /root/bin.sql

    查看数据库
    数据库的备份与恢复
    可以看到我们后来的新数据也恢复回来,这样我们整个基于lvm的热备与恢复就完成了。


mysqldump的逻辑备份

mysqldump备份数据库相比较lvm快照备份要更加简单,方便,而且可以灵活的备份某个表,指定库,当然也可以备份整个库。
备份命令:

mysqldump [OPTIONS] database [tables]    备份指定的表
mysqldump [OPTIONS] -B DB1 [DB2 DB3.....]    备份指定的库
mysqldump [OPTIONS] -A [OPTIONS]    备份所有的库

在这里,我们最基本需要了解五个选项
-A --all-databases 备份所有的数据库,含create database
-B --database db_name...... 指定备份的数据库,含create database语句
--F --flush-logs 备份前滚动日志,锁表完成后,执行flush logs命令,生成新的二进制日志文件,配合-A或-B 选项时,会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新,可通知和--single-transaction 或-x,--master-data 一起使用实现,此时只刷新一次日志
--master-data[=1/2] 此选项须启用二进制日志 1:所备份的数据之前加一条CHANGE MASTER TO语句,非注释,不指定#,默认为1
--single-transaction 通过在单个事务中转储所有表来创建一致的快照。仅适用于存储在支持多版本控制的存储引擎中的表;转储不保证与其他存储引擎保持一致。在进行事务转储时,要确保有效的转储文件(正确的表内容和二进制日志的位置),没有其他连接应该使用以下语句:ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE
综合以上选项,备份所有数据库操作:

musqldump -A -F --single-transaction --master-data=2 > /mysql/backup/fullbak_$(date +%F_%T)

数据库的备份与恢复
备份数据的还原

mysql < /mysql/backup/fullbak_2018-10-11_+20\:00\:20

未备份的数据同上面的还原一样,利用二进制日志记录进行还原。