转载

数据库同步与认证问题

作者: tiankonguse | 更新日期: 2015-01-19 23:22:00

周一刚来到公司，收到一个消息，我的一个sphinx搜索服务搜出的数据延迟一个小时，于是查了查什么问题

前言

数据库相关的书籍几乎没有看过，现在掌握的相关知识都是看博客或者而平常敲代码实践中学习到的。

今天再记录一个实际项目中遇到的问题。

问题背景

我做过 sphinx 搜索程序相关的东西，很多人都在用我的搜索结果。

但是今天早上来后， hades 说数据在表里有，但是在搜索结果中没有。

简单的观察之后，发现数据不是没有，而是晚了一个小时多才可以搜索到。

于是查看 sphinx 索引中是否有这个数据，结果发现索引中没有，于是怀疑建索引的时候未搜到这个数据吧。

于是重建索引后依旧没有搜到结果。

于是我也去 DB 中是否有数据，看完之后，确实有的。

这个时候大概确定了这个一个问题： DB 中有数据， sphinx 建索引后没数据， sphinx 是从 DB 中读数据建索引的。

hades 提示我可能是内存不足的原因，而 sphinx 的配置有个 mem_limit= 32M 选项，修改之，发现还是不行。

然后我再看看另一个搜索程序，那个程序的数据是这个程序的10倍，用的也是 32M , 而且上周还正常，说明不是内存问题。

于是我在配置文件中一个选项一个选项的查找，什么问题都没有发现。

然后 hades 说找到问题了，主从 DB 的问题：之前看的是主库， sphinx 连接的是从库，经过观察，从库的数据比主库的数据延迟一个小时同步过来。

好吧，问题确定了，是 DB 的问题，于是修改 sphinx 尝试直接连接主库，结果发现有问题。

sql_connect: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

这个时候想起来当初问什么连接从库了：当时主库连接不上，由于历史原因，主库的密码使用旧的认证方式。

于是网上简单的搜索到几个解决方案：

服务器端升级，启用 secure_auth 选项
客户端连接的时候指定 --secure_auth=off
服务端更新对应的用户的密码，使用新认证加密

第一个方法对于我这个场景行不通。

sphinx 没有提供 mysql 选项的相关参数，但是由于 sphinx 使用 mysql-real-connect 方式连接，所以提供了一个指定 client_flag 的配置选项。

但是把官网的 client_flag 全部看完也没发现使用旧的认证的配置选项。于是第二个方法啊也不行。

我问 hades 真的不能更新用户密码吗？

他说不能，会影响其他人的。

那我说只好选择换 DB 了， sphinx 的 DB 不适用原来那个主库就可以了。

后来， hades 说找到办法了，新建一个用户，新用户使用新认证加密就可以了。

但是我有个疑惑：原先那个主库不是低版本数据库了，低版本怎么能识别高版本的加密方式呢？

但是后来新建一个用户，使用新认证加密，果然可以了。

然后看看原先的 DB 的 user 表，发现直接配置一个 ip 就可以了，意思是同一个用户可以指定ip使用何种认证。

好吧，到这里终于把所有事情都梳理完了，原来自己对数据库了解的太少了，这才是根本原因。

主从库问题

现在为了实现数据库备份，或者读写分离，负载均衡等等，往往会有多个相同的数据库。

但是为了方便同步数据，往往只有一个库可以写(称为主库)，其他库都只能读(称为从库)，这样只需要写主库一个数据库即可。

但是考虑到写一个数据库会给主库带来很大的压力，于是也支持可以写多个库， mysql 会自动帮我们同步最新的数据。

由于存在多个写的数据源，或者主库向从库同步数据，这里就可能出现一个问题：主从库数据不一致问题。

针对这个问题，还真没什么好的办法，只能手动检查。

比如我遇到的问题，就是主库向从库同步数据延迟了一个小时导致的，至于原因， hades 说是因为主库的负载过高，同步任务在负载闲暇的时候才同步。

之前我的印象中是要推荐读写分离的，但是今天的问题改变了我的看法。

hades 说对于同一个项目，需要保持数据同源的，不然会发生一个项目的数据不一致的问题，比如今天的这个。

主从库关于这里面的具体知识，我也不是太懂，以后慢慢学吧。

数据库认证问题

这里我了解到一个问题：之前对认证理解错了。

之前一直以为数据库版本低，使用的是旧的认证加密方式，旧的版本不支持新的认证加密方式。

然后认为我的这个主库使用的是旧的认证加密方式，而且使用了这么多年了，应该不支持新的认证加密方式的。

这也是为什么会给 hades 提出换 DB 不用那个mysql的原因，我以为它不支持新的加密方法。

不过刚才查了一下，那个数据库版本是 5.0, 版本虽然低了点，但还是比 4.1 高的。

解决了这个问题我还学到一个知识点：相同的用户名，可以针对不同的ip设置不同的密码，认证加密方式自己决定。

有了这个知识点，我那个问题可以直接解决了，只需要给原先的那个用户新指定一个 ip ，然后密码用新认证加密即可。

具体的，大家可以看看 mysql.user 这个表里面的内容吧。

关于新建账号

新建账号就比较简单了，直接新建，添加权限即可。

CREATE USER test IDENTIFIED BY 'test';   grant select on test.* to test;

也可以直接在指定 ip 上新建用户

CREATE USER test@127.0.0.1 IDENTIFIED BY 'test';

然后意识到大学的时候自己是多么的无知。

大学经常做小网站，然后会遇到这么一个问题：新建的数据库不能连接上。

后来我的解决方案是下面两个，缺一不可。

CREATE USER test@127.0.0.1 IDENTIFIED BY 'test';   CREATE USER test@localhost  IDENTIFIED BY 'test';

当然，当时新建用户时需要指定一个 ip，起初填的时候往往只会填上面其中一个, 不知道可以填 * 来代替所有，后来发现两个都填本地肯定能连接上，于是一直都是都填。

关于解决方案

假设我有一个 test 的用户，密码也是 test.

起初使用的是旧的认证加密方式，则我们查看mysql的用户表.

select * from mysql.user where User='test';  mysql> select Host,User,Password, LENGTH(Password) from  mysql.user where User="test"; +----------------+------+-------------------------------------------+------------------+ | Host           | User | Password                                  | LENGTH(Password) | +----------------+------+-------------------------------------------+------------------+ | localhost      | test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |               41 | | %              | test | 378b243e220ca493                          |               16 | +----------------+------+-------------------------------------------+------------------+ 2 rows in set (0.00 sec)

我们可以看到旧认证加密后的密码是 16 位长度，新认证加密后是40位长度，加上前面的那个星，长度是41.

我们想让某台机器使用新密码访问我们的数据库了，就新建一个与那台机器相关的用户即可。

例如

mysql> CREATE USER test@192.168.10.12 IDENTIFIED BY 'test';  Query OK, 0 rows affected (0.00 sec)  mysql> select Host,User,Password, LENGTH(Password) from  mysql.user where User="test"; +---------------+------+-------------------------------------------+------------------+ | Host          | User | Password                                  | LENGTH(Password) | +---------------+------+-------------------------------------------+------------------+ | 192.168.10.12 | test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |               41 | +---------------+------+-------------------------------------------+------------------+ 1 row in set (0.00 sec)

想让某台机器使用新的机密方式怎么办呢？

可以先增加用户，在设置密码为旧的密码即可。

mysql> CREATE USER test@192.168.10.13 IDENTIFIED BY 'test';  Query OK, 0 rows affected (0.00 sec)  mysql> select Host,User,Password, LENGTH(Password) from  mysql.user where User="test"; +---------------+------+-------------------------------------------+------------------+ | Host          | User | Password                                  | LENGTH(Password) | +---------------+------+-------------------------------------------+------------------+ | 192.168.10.12 | test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |               41 | +---------------+------+-------------------------------------------+------------------+ | 192.168.10.13 | test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |               41 | +---------------+------+-------------------------------------------+------------------+ 1 row in set (0.00 sec)   mysql> SET PASSWORD FOR 'test'@'192.168.10.13' = OLD_PASSWORD('test');  Query OK, 0 rows affected (0.00 sec)   mysql> select Host,User,Password, LENGTH(Password) from  mysql.user where User="test"; +---------------+------+-------------------------------------------+------------------+ | Host          | User | Password                                  | LENGTH(Password) | +---------------+------+-------------------------------------------+------------------+ | 192.168.10.12 | test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |               41 | | 192.168.10.13 | test | 378b243e220ca493                          |               16 | +---------------+------+-------------------------------------------+------------------+ 2 rows in set (0.00 sec)

有时候不想那么麻烦，可以直接向 mysql.user 插入数据即可, 这里就不多介绍了。