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

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

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

一、平台环境

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

我这里的服务器里已经提前安装好MySQL了，这里我就不演示去安装环境了。这里我已经正常进入两个服务器，然后启动了MySQL。（要是这些操作过程不懂，可以留言）到此我们就把环境都准备好了。

二、MySQL用户授权

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

首先查看下主数据库服务器里的用户表，这里我们知道目前只有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可以访问，此用户可以访问主服务器里的所有数据库。

接着我们实际操作一遍，

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

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

三、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%';

接着我实际演示一次：

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

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日志，让其恢复到初始状态