MySQL and MongoDB DBA

HandlerSocket is well known as a MySQL NoSQL plugin which provides user a much better performance without losing SQL features as some other NoSQL database do.
But honestly speaking, not all the fancy software would have a lot of users. Most of the time people just forward posts and blogs without trying to use the amazing thing first.
The problem HandlerSocket has is that we can merely find any blogs talking about how to tune or troubleshoot it. Based on my little experience of HandlerSocket, today I would like to share some problems we had encountered. And this post will be updated according to the future usage.

1. MySQL error code 167: Failed to set row auto increment value
This error is due to a HandlerSocket bug. If you can understand Chinese, please read this post written by Ruohui Huang.
The solution here is very easy: set innodb_autoinc_lock_mode to 0.

2. HNDSOCK failed to lock tables
If you try to use HandlerSocket API to update a read-only InnoDB table, this message will appear in the error log.

3. DDL timeout
It is very hard to do DDL on HandlerSocket enabled tables beacause it keeps tables opened for reuse. Even HandlerSocket in Percona server would close tables when traffics become small, there is definitely no parameter to control this behaviour. If you are suffering from this DDL problem, maybe you can try HandlerSocket in Spider server. There is a parameter named handlersocket_close_table_interval, hope it’s helpful to you.

mysqldump是非常重要的MySQL备份工具。然而在长年累月的使用过程中，TAOBAO多次出现了因mysqldump意外终止而导致备份失败的情况。
以下是我们经常遇到的问题：

1、Lost connection to MySQL server at ‘reading initial communication packet’：
这个主要是因为DNS不稳定导致的。如果做了网络隔离，MySQL处于一个相对安全的网络环境，那么开启skip-name-resolve选项将会最大程度避免这个问题。

2、Lost connection to MySQL server at ‘reading authorization packet’：
从MySQL获取一个可用的连接是多次握手的结果。在多次握手的过程中，网络波动会导致握手失败。增加connect_timeout可以解决这个问题；然而增加connect_timeout并不能防止网络故障的发生，反而会引起MySQL线程占用。最好的解决办法是让mysqldump重新发起连接请求。

3、Lost connection to MySQL server during query：
这个问题具备随机性，而淘宝MySQL的应用场景决定了我们无法多次备份数据以便重现问题。
然而我们注意到这个问题一般会在两种情况下会发生。一种是mysqldump **** | gzip ****；另外一种是mysqldump **** > /nfs-file
注意，不管是gzip还是nfs都有一种特点，那就是它们影响了mysqldump的速度。从这个角度思考，是不是mysqldump从MySQL接受数据包的速度不够快导致Lost connection to MySQL server during query错误呢？
(more…)

在高压力的情况下，把MySQL直接kill掉重启，发现MySQL挂了。

查看了下error log，里面出现了如下的提示：
091101 13:16:37 InnoDB: Page checksum 3686608226, prior-to-4.0.14-form checksum 745408106
InnoDB: stored checksum 2630870012, prior-to-4.0.14-form stored checksum 774850316
InnoDB: Page lsn 320 3109670381, low 4 bytes of lsn at page end 3189967116
InnoDB: Page number (if stored to page already) 8905727,
(more…)

一、没有正确赋权
有时候真的非常抓狂，明明赋给某用户相关的权限了，他死活就是不能从远程登录。哈哈，别急，这里有几个常犯的小错误。第一个可能，没有用root帐号登录MySQL。MySQL安装完之后会产生空用户，而且这个用户是默认用户。如果使用不带参数的mysql命令直接登录本地服务器，往往当前用户就是毫无权限的空用户（不信可以用select user()看一下）。这时候虽然grant命令会返回成功的提示，但是实际上新用户屁权限都没得到。第二个可能，你用来赋权的用户，虽然有相关权限，但是它的帐号信息不在内存里。这种情况多发生于用source读取sql后。此时只要执行flush privileges就好啦！第三个可能，grant语句里面的数据库名称写错了。MySQL有个非常龌龊的恶习，它不会检查grant里面的数据库是否真的存在。有时候手抖写错了一个字母，查了半天才能查出来。
(more…)