实战教学:MySQL用户授权和bin-log日志

一. MySQL用户授权

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

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

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

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 接着我们实际操作一遍

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

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

二. 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%’; 接着我实际演示一次:

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

2.2. bin-log日志的使用

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

这个时候我们进入到MySQL里,查看一下最后一个bin-log日志信息

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

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


接着我们创建一个数据库,然后创建一个表,再插入一些记录,然后再查看下日志信息有没有变化

接着我们再看下bin-log信息,此时位置已经发生了变化,也就是说已经对刚才的操作进行了记录。

接着我们打开二进制日志,看看里面就是记录了什么,我们需要知道的是,如果用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); 其他的能看懂就自己看,不懂看错就跳过也没事。

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

流程如下:

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

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

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

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

具体如下

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

我们到数据库里查看下是否恢复成功了:

这个时候,已经证明我们使用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-bin.000001里的内容(这里我只截取一部分):

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

接着我们进入数据库查看下是否进行恢复成功:

未经允许不得转载:大自然的搬运工 » 实战教学:MySQL用户授权和bin-log日志

赞 (0)

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址