天天看点

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

原文地址:http://www.cnblogs.com/it-cen/p/5234345.html

MySQL用户授权 和 bin-log日志 详解和实战

看了上一篇博文的发布时间,到目前已经有三个月没更新博文了。这三个月经历了很多事情,包括工作、生活和感情等等。由于个人发展的原因,这个月准备换工作啦。在这段时间,我会把Web大型项目中所接触到的技术都总结出来,分享给各位支持我的博友。博客园是个好地方,在这里能学到很多东西,同时你也可以收获很多东西,就比如我本人,去年被两个比较大型的在线培训机构邀请当讲师(只接受了一家,当然由于太忙所以课程录制也不是特别多),另有一企业聘请我写一本技术相关的书籍(我拒了,没时间写)等等,这些都是我在博客园里得到的回报。所以还是要感谢博客园这个平台。

好了,废话不多说,直接进入今天的知识分享。这次博文我将分享下MySQL的用户授权和bin-log日志,这博文是为下一篇 MySQL主从复制技术博文做准备的,本博文并不是理论解释这些技术,而是通过实战去应用这些技术,因为,理论的东西,百度一下就一大堆,而实际如何去应用的,或者百度上比较少,所以,我分享的技术都是理论+实战,让博友阅读完本文后,就能自己动手去做实验,让自己更深入彻底地理解好这些较为高级的技术。

一、平台环境

centos6.5、MySQL5.6.22。首先当然是要有自己的环境,我是在虚拟机上去模拟实验的,因为MySQL要实现主从复制,所以需要两个服务器环境,所以在虚拟机中,你可以克隆一份一模一样的服务器,如下图:

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

我这里的服务器里已经提前安装好MySQL了,这里我就不演示去安装环境了。这里我已经正常进入两个服务器,然后启动了MySQL。(要是这些操作过程不懂,可以留言)

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

到此我们就把环境都准备好了。

二、MySQL用户授权

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

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

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

grant all privileges on *.* to [email protected] identified by '123456';

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

接着我们实际操作一遍,

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战
MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战
MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

 三、bin-log日志

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

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

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

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

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

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

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

1、开启big-log日志

打开MySQL的配置文件:/etc/my.cnf(我这里的MySQL是放在/etc下的),

在配置文件中添加此行代码: log-bin=mysql-bin,

然后重启下MySQL:service mysqld restart

进入数据库查看是否开启成功:mysql> show variables like '%log_bin%';

接着我实际演示一次:

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战
MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战
MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

2、bin-log日志的使用

首先我们来认识几个与bin-log相关的MySQL命令:

flush logs; 会多一个最新的bin-log日志

show master status; 查看最后一个bin-log日志的相关信息

reset master; 清空所有的bin-log日志

我们先看一下bin-log日志放在哪:

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 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); 其他的能看懂就自己看,不懂看错就跳过也没事。

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战
MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

流程如下:

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

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

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

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

具体如下:

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战
MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

这个时候,已经证明我们使用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-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

MySQL用户授权 和 bin-log日志 详解和实战(很详细) 原文地址:http://www.cnblogs.com/it-cen/p/5234345.html MySQL用户授权 和 bin-log日志 详解和实战

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

四、总结

本次博文对MySQL的用户授权 和 bin-log日志 这两个知识点进行了详细讲解和实操,对此为下一篇讲解MySQL主从复制技术做好的铺垫。希望想学到技术的朋友可以自己跟着博文动手实验一次,当然,高手可以忽略,也可以留言交流。

继续阅读