mysql 5.7安装及使用binlog日志恢复数据库

mysql 5.7安装及使用binlog日志恢复数据库

我这里以mysql5.7为例

binlog日志介绍

是MySQL server维护的一组日志文件，用来记录对数据进行修改的信息。
MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句(除了数据查询语句select、show等)，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

主要应用场景有：

• MySQL主从复制
• 数据恢复

Binlog日志格式，支持三种格式类型

• STATEMENT：基于SQL语句的复制(statement-based replication, SBR)，语句级别，记录写操作的SQL语句
  • 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能。
  • 缺点：可能会造成数据不一致，比如now()函数，random()函数。比如update user set name = ‘张三’ where time < now() 。
• ROW：基于行的复制(row-based replication, RBR)，行级别，记录每次操作后行的变化。
  • 优点： 记录的是行变化，不会出现数据不一致
  • 缺点:以行的记录保存，会占用空间。
• MIXED：混合模式复制(mixed-based replication, MBR)
  • 从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。
  • 在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种

安装mysql5.7

//下载镜像源
wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum -y install mysql57-community-release-el7-11.noarch.rpm
//--nogpgcheck  (不校验数字签名)
yum -y install mysql-server  --nogpgcheck

//启动mysql
systemctl start mysqld.service
//获取初始密码
cat /var/log/mysqld.log| grep password

//登陆mysql
mysql -uroot -p
//修改密码
ALTER USER USER() IDENTIFIED BY '123456Admin@123';
//授权
grant all privileges on *.* to 'root'@'%' identified by '123456Admin@123' with grant option;
 //刷线权限
flush privileges;

//修改密码策略，把root密码改成更简单的密码
set global validate_password_policy=0;
set global validate_password_length=1;
flush privileges;

//修改root的密码为root
alter user 'root'@'localhost' identified by 'root';

//授权
grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
flush privileges;

//mysql安装好后，navicat连接不上报错
use mysql;
alter user 'root'@'%' identified with mysql_native_password by 'yourpassword';
flush privileges;

开启Binlog日志

通过配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件的 log-bin 选项：

#配置修改
//配置binlog日志路径以及日志文件名前缀、生成的日志格式为：bin-log.000001
log-bin=/var/lib/mysql/bin-log
//注意5.7以及更高版本需要配置本项、自定义，保证唯一性
server-id=1
//binlog格式，有3种statement,row,mixed,上面讲到了
binlog-format=mixed
//表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
sync-binlog=1
//此参数表示只记录制定数据库的二进制日志，下面配置的是只记录mydb1,mydb2,mydb3,mydb4着四个数据库的binlog日志，其他数据库的binlog日志不记录。
binlog_do_db=mydb1,mydb2,mydb3,mydb4
//此参数标示不记录指定的数据库的二进制日志，下面的配置是忽略MySQL自带的几个数据库的binlog日志。
binlog_ignore_db=mysql,sys,information_schema,performance_schema

查看binlog日志状态和内容

//查看日志状态
show variables like '%log_bin%';
//查看日志内容
show master status;
show binlog events in 'bin-log.000001';

//我们创建一个schema
create databases alex;
//再次查看binlog内容,呵呵，发现了吗，已经把schema创建的记录到log了
show binlog events in 'bin-log.000001';

通过Binlog恢复数据

我们进入刚才配置文件里写的log的路径查看binlog

根据 MySQL 官方文档的介绍，开启 binlog 之后，大概会有 1% 的性能损耗，不过这还是可以接受的，平时dump做好备份，尽量不要依赖binlog备份

mysqldump -uroot -p --flush-logs --lock-tables -B javaboy>/root/javaboy.bak.sql

-u、-p 这两个就不用说了。
–flush-logs：这个表示在导出之前先刷新 binlog，刷新 binlog 之后将会产生新的 binlog 文件，后续的操作都存在新的 binlog 中。
–lock-tables：这个表示开始导出前，锁定所有表。需要注意的是当导出多个数据库时，–lock-tables 分别为每个数据库锁定表，因此这个选项不能保证导出文件中的表在数据库之间的逻辑一致性，不同数据库表的导出状态可以完全不同。
-B：这个表示指定导出的数据库名称，如果使用 --all-databases 或者 -A 代替 -B 表示导出所有的数据库

首先，假设我们每星期三凌晨备份文件，星期四早上，来上班了，一顿操作后，小 X 今天跟领导吵架了很不爽，决定删除跑路，领导发现了大惊，当即要求立马恢复数据。这时候该你表现了
首先，我们有星期三凌晨的备份文件，先用那个文件进行数据恢复

mysql -uroot -proot javaboy < /root/javaboy.bak.sql

恢复之后，现在到星期三早上凌晨三点的数据有了,但是从星期三早上凌晨三点到星期四的数据现在没了
这个时候我们就要借助于 binlog 来恢复了。大家还记得，我们星期三凌晨三点执行备份的时候，用了一个参数叫做 --flush-logs，使用了该参数表示从备份那一刻起，新的 binlog 将产生在一个新的日志文件中，对于我们这里来说，新的 binlog 文件当然就是 javaboy_logbin.000002 了，我们去查看一下该文件

//找到了小X删库的那个操作
show binlog events in 'javaboy_logbin.000002';

可以看到，在 764-865 这个 Pos 中发生了删库跑路事件，那么我们只需要回放该文件将数据恢复到 764 这个位置即可。

mysqlbinlog /var/lib/mysql/javaboy_logbin.000002 --stop-position=764 --database=javaboy | mysql -uroot -p

–stop-position=764 表示恢复到 764 这个 Pos，不指定的话就把按整个文件恢复了，如果按当前文件恢复的话，由于这个 binlog 文件中有删除数据库的语句，那么就会导致执行完该 binlog 之后，javaboy 库又被删除了。
–database=javaboy 表示恢复 javaboy 这个库。
另外还有一个我们这里没用到的参数叫做 --start-position，这个表示起始的 Pos，不指定的话表示从头开始数据恢复。

数据恢复啦～

三种恢复方式

//通过Binlog恢复数据
/usr/bin/mysqlbinlog --database=hello /var/lib/mysql/bin-log-1.000001 | /usr/bin/mysql -uroot -p123456zy -v hello


//通过指定位置恢复数据
/usr/bin/mysqlbinlog --start-position=573 --stop-position=718 --database=hello /var/lib/mysql/bin-log-1.000001 | /usr/bin/mysql -uroot -p123456zy -v hello
--start-position=573 开始位置
--stop-position=718  结束位置


//通过指定时间恢复数据
/usr/bin/mysqlbinlog --start-datetime="2021-06-27 20:58:18" --stop-datetime="2021-06-27 20:58:35" --database=hello /var/lib/mysql/bin-log-1.000001 | /usr/bin/mysql -uroot -p123456zy -v hello

//查看开始和结束位置
show binlog events in 'bin-log-1.000001';

清理MySQL的binlog日志

MySQL的binlog是以二进制形式打印的日志，没设置自动删除的话，时间长了就会占用大量存储空间。删除MySQL的binlog日志有两种方法：自动删除和手动删除。

自动删除

• 永久生效,修改MySQL配置文件my.cnf，配置binlog的过期时间，重启生效。

  expire_logs_days=30

• 临时生效：即时生效，重启后失效。

  -- 设置过期时间为30天
  set global expire_logs_days=30;

手动删除

手动删除前需要先确认主从库当前在用的binlog文件。

主库：show master status;
从库：show slave status\G

假设当前在用的binlog文件为master-bin.000277，现需要删除master-bin.000277之前的所有binlog日志文件(不删master-bin.000277)：

PURGE MASTER LOGS TO 'master-bin.000277';

其它

查看binlog文件

show binary logs;