IT人生

  • 首页
  • 归档
  • kafka
  • Java
  • Spring
  • Golang
  • SQL
  • Spark
  • ElasticSearch
  • 关于

  • 搜索
Phoenix HBase Kudu ElasticSearch Spring 数据结构 操作系统 Kettle Azkaban Sqoop Hive Yarn Redis Mybatis Impala Cloudera 大数据 HDFS mycat shell Linux 架构 并发 mysql sql golang java 工具 spark kafka 人生

MySQL用户授权和bin-log日志

发表于 2016-11-03 | 分类于 数据库 | 0 | 阅读次数 1250

一. MySQL用户授权

由于我演示的是主从复制技术,所以我这里只讲解给从服务器设置授权用户,也就是只授权给从服务器连接到主服务器去。这里我的主服务器的IP是192.168.197.129,从服务器的IP的192.168.197.130。

1.1 首先查看下主数据库服务器里的用户表,这里我们知道目前只有root用户,

mysql_manage_1

接着我们要添加一个新用户,然后只能是从服务器去连接:

grant all privileges on . to kelly@192.168.197.130 identified by '123456';

这里我解释下这句命令行的意思,grant all指的是授权所有操作权限(增删改查),*.*指的是所有数据库,kelly指的是用户名,123456是密码,192.168.197.130指的是所要授权的远程IP地址。整一句的意思是,创建一个用户kelly密码为123456,然后将此用户授权给远程IP为192.168.197.130可以访问,此用户可以访问主服务器里的所有数据库。

1.2 接着我们实际操作一遍

mysql_manage_2 mysql_manage_3

1.3 然后我们在从服务器进行连接,再查看下是否 是主服务器的数据库:

mysql_manage_4 mysql_manage_5

到此,我们已经完成了给从服务器授权,大家跟着上面一步步操作进行测试,我相信会成功的,若有问题可以留言。

二. bin-log日志

bin-log日志的定义和作用我就直接百度过来了,也很好理解的。

基本定义:二进制日志,也成为二进制日志,记录对数据发生或潜在发生更改的SQL语句,并以二进制的形式保存在磁盘中;

作用:可以用来查看数据库的变更历史(具体的时间点所有的SQL操作)、数据库增量备份和恢复(增量备份和基于时间点的恢复)、MySQL的复制(主主数据库的复制、主从数据库的复制)

文件位置:默认存放位置为数据库文件所在目录下

文件的命名方式: 名称为hostname-bin.xxxxx (重启mysql一次将会自动生成一个新的binlog)

状态的查看:mysql> show variables like '%log_bin%';

以上这些都是百度复制过来的,我们看完后也很好理解了,bin-log日志也就是二进制日志,只要数据发生改变(增删改),都是记录到日志里。所以我们可以通过bin-log日志进行数据恢复。

2.1. 开启big-log日志

打开MySQL的配置文件:/etc/my.cnf(我这里的MySQL是放在/etc下的), 在配置文件中添加此行代码: log-bin=mysql-bin, 然后重启下MySQL:service mysqld restart 进入数据库查看是否开启成功:mysql> show variables like '%log_bin%'; 接着我实际演示一次:

mysql_manage_6

mysql_manage_7

mysql_manage_8

根据介绍流程操作是成功的,大家要自己动手测试下。

2.2. bin-log日志的使用

首先我们来认识几个与bin-log相关的MySQL命令: flush logs; 会多一个最新的bin-log日志 show master status; 查看最后一个bin-log日志的相关信息 reset master; 清空所有的bin-log日志 我们先看一下bin-log日志放在哪: mysql_manage_9 这个时候我们进入到MySQL里,查看一下最后一个bin-log日志信息

mysql_manage_10

然后刷新一下bin-log日志,再查看下最后一个bin-log日志,此时已经变成了000002这个版本了,以后的增删改操作就记录在这个最新的bin-log日志里

mysql_manage_11

我们可以看一下bin-log日志存放的目录,这里就多出了新版本的bin-log日志000002,以后的操作都是记录在这里面了

mysql_manage_12 接着我们创建一个数据库,然后创建一个表,再插入一些记录,然后再查看下日志信息有没有变化 mysql_manage_13 接着我们再看下bin-log信息,此时位置已经发生了变化,也就是说已经对刚才的操作进行了记录。

mysql_manage_14

接着我们打开二进制日志,看看里面就是记录了什么,我们需要知道的是,如果用vi打开二进制日志的乱码的,所以我们需要使用下面的命令进行打开:

/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002

我把打开的日志截图看看(截图太多了希望大家别介意,因为想让大家了解的更清楚),我用红色圈起来的都是我们需要注意的,end_log_pos大概的意思是这这个操作的最后位置在哪,例如:end_log_pos 400,在299-400之间是存了插入操作的记录的,insert into test1 values(1); 其他的能看懂就自己看,不懂看错就跳过也没事。

mysql_manage_15 mysql_manage_16

下面我们来做一个利用二进制恢复数据库数据的操作。

流程如下:

(1)先生成一个新的日志文件(000003);

(2)然后删除test1表的数据;

(3)然后利用前一个日志恢复数据(000002);

(4)最后查看是否恢复成功。

具体如下

mysql_manage_17

mysql_manage_18 接着用mysql-bin.000002进行数据恢复: mysql_manage_19

我们到数据库里查看下是否恢复成功了: mysql_manage_20 这个时候,已经证明我们使用bin-log日志进行数据恢复成功了。

在这里可能有博友就会问:“博主,你那实验在测试时是先刷新了个bin-log版本,假如我没刷新,然后删除语句就记录在了000002这个版本了,那么你怎么恢复啊?因为你使用000002进行恢复,也是会执行删除操作的记录的,这不是坑爹吗?”。

没错,假如有博友能想到这个了,那说明上面所讲的知识点基本上理解了,并有自己去思考。这里我们当然有办法在一个有删除记录的bin-log日志进行数据恢复,你有想过为啥会有end-log-pos这个位置记录吗?这就是我们解决以上问题的方法之一:定位恢复,即恢复你想恢复的数据。我打个比方,在一个bin-log日志里,insert操作的end-log-pos为250,而delete的end-log-pos为300。那么,我们可以将数据恢复到250这个位置,而之后的操作就不恢复了,这里我们先看一下mysqlbinlog的常用参数:

--stop-position = "100" --start-position = "50" 根据开始位置或者结束位置来恢复自己想恢复的参数

--stop-date= "2016-03-02 12:00:00" --start-date= "2016-03-02 11:55:00" 根据开始日期时间或者结束位置来恢复自己想恢复的参数

例如:

/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000001 --stop-position="450"|mysql -uroot -p

此句的意思就是将数据恢复到位置在450之前的就OK了,在450之后(假如后面还有操作记录)的就不需要进行恢复了。

下面我通过实验操作一次:

首先重置一下bin-log日志,让其恢复到初始状态

mysql_manage_21

接着插入几条数据,再删除些数据

mysql_manage_22 这个时候我们可以看一下mysql-bin.000001里的内容(这里我只截取一部分):

mysql_manage_23

由于我们知道删除的是大于2的数据,所以知道3和4被删除了,这里我们就恢复3和4(当然,假如误删了整个表,这里就恢复到删除操作之前的就可以了)执行如下命令: mysql_manage_24 接着我们进入数据库查看下是否进行恢复成功: mysql_manage_25

到此,bin-log日志的讲解已经告一段落

  • 本文作者: Randy
  • 本文链接: http://www.itrensheng.com/archives/mysql_manage_and_binlog
  • 版权声明: 本博客所有文章除特别声明外,均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处!
# Phoenix # HBase # Kudu # ElasticSearch # Spring # 数据结构 # 操作系统 # Kettle # Azkaban # Sqoop # Hive # Yarn # Redis # Mybatis # Impala # Cloudera # 大数据 # HDFS # mycat # shell # Linux # 架构 # 并发 # mysql # sql # golang # java # 工具 # spark # kafka # 人生
基于Centos7源码安装mysql-5.7.16-linux-glibc2.5-x86_64.tar.g
在Linux中for和cat遍历文件内容出现no space
  • 文章目录
  • 站点概览
Randy

Randy

技术可以暂时落后,但任何时候都要有上进的信念

80 日志
27 分类
31 标签
RSS
Github E-mail
Creative Commons
© 2021 备案号:沪ICP备19020689号-1
Randy的个人网站