将cacti数据库配为非3306端口

最近部署cacti来monitor一批mysql的服务器, 由于某些原因, 我需要将cacti的数据库放置在一个在3307端口监听的instance上.

但是发现修改config.php中的port不起作用, cacti依然选择去连接3306端口的数据库.

一番研究下, 发现cacti使用adodb来连接mysql, 并且默认使用的driver是php5-mysql,  使用NewADOConnection('mysql')的方式来连接mysql数据库.
而这个driver就我查到的看来是不支持非3306端口的连接. 并且cacti中连接mysql部分的函数的写法也是不支持其他端口, 因为在函数参数中hard code了3306.(不知道为何这么操作)

最终做了两处修改:
1. 修改[cacti_path]/lib/database.php, 使其能够接收port参数
       function db_connect_real($host, $user, $pass, $db_name, $db_type, $port = "3306", $db_ssl = false, $retries = 20) {
       global $cnn_id;

       $i = 0;
       $dsn = "$db_type://$user:$pass@$host/$db_name?persist&port=$port";
2. 修改[cacti_path]/include/config.path, 改为使用mysqli来连接mysql数据库
$database_type = "mysqli";

这样就可以自由修改config.php中的port了.

修改log-bin路径

最近升级一台mysql, 因为原来这个mysql上的二进制日志文件跟数据文件是放在一起的, 感觉不是太好, 所以想改一下路径.

没料到把日志文移到新的路径下面并在配置文件中修改好, 但改完之后, 就是起不来, 老是报文件找不到.

/usr/local/mysql/bin/mysqld: File './mysql-bin.000014' not found (Errcode: 2)

111020  2:17:23 [ERROR] Failed to open log (file './mysql-bin.000014', errno 2)

111020  2:17:23 [ERROR] Could not open log file

111020  2:17:23 [ERROR] Can't init tc log

111020  2:17:23 [ERROR] Aborting

但是权限都是没问题的.

很郁闷的改回去, 先让其跑起来再说.

今天又想起来这个事情, 所以又折腾了一番.

原来关键在mysql-bin.index这个文件, mysql启动的时候会根据这个文件找最后一个二进制日志文件, 但是原来二进制日志文件与数据文件放在一起的时候, 这个文件里面存的是相对路径.

并且是相对于数据文件的相对路径(我前面也关注过这个index文件, 但以为这个相对路径是相对于index文件的相对路径), 所以修改到其他路径以后, 必须要将index文件中的路径改为绝对路径, 或者是改为新的相对路径, 要记住, 这个相对基础是数据文件的路径.

利用mk-query-digest查看general log中所有操作某表的sql

mk-query-digest --type genlog --filter '$event->{arg} =~ m/order_main/' --limit 100% mysql_log.log > table3.log

第一个mysql case

上周碰到一个case, 原同事的朋友打电话给我, 说他们的mysql数据库挂了, 有什么办法可以恢复, 找到哪些文件可以恢复.

当时在外面, 问他有没有备份, 说没有, 只有9月份他自己的一个dump备份.

他说他现在看到有frm文件以及opt文件, 我就告诉他, 找到ibdata, iblogfile文件, 以及服务器的配置文件, 类似my.cnf之类的.

半个小时之后, 再电话过来, 说都找到了, 但是ibdata文件比较奇怪, 在原来server上显示10个G, 但是拷贝到另外一个server上, 几秒钟就拷贝完了, 而且拷贝过去显示size只有几百M.

跟他确认一下拷贝过程没问题之后, 基本确认应该是ibdata文件损坏. 问他有没有类似mysql-bin之类的文件, 说没有, 发过来的my.ini里面也没有看到有启动二进制日志.

QQ详谈, mysql数据库是放在服务商那的, 某天应用不能访问了, 服务商说是他们那网络异常了, 半个小时后恢复, 但是恢复之后数据就没有了. 控制台查看数据库, 只看到表, 但是表里面都没有数据.

根据他前面说的, 基本确定是ibdata文件损坏, 但体系统他说应该是服务商那边硬件或者OS故障导致, 断网不会出现这种问题.

他电话过去确认, 果然, 断过一次电, 重新启动之后ibdata文件就只有几百M了, 检查磁盘说有坏道. 另外, 服务商OS是windows 2003的.

很遗憾的告诉他, 我这边没有办法了, 只能请磁盘恢复的人看看能否找回!

1. 没有固定的备份策略, 最近的备份还是9月份的, 而且还是他自己dump出来的

2. 很关键的, 没有启动二进制日志.

关于表join顺序的一次tuning

昨天收到一个sql, 很慢, 基本上跑不出来, 将其他session全都block住了, 只能kill掉.
SELECT DISTINCT a.order_id, a.item_id, a.update_date, a.operation, a.reason, a.comments, d.status_date, c.first_name, c.last_name, f.email
FROM po.item_update_history a
JOIN po.order_service b
JOIN po.vendor c ON a.order_id = - b.order_id
AND a.item_id = - b.item_id
AND b.vendor = c.user_id
LEFT JOIN po.order_status d ON a.order_id = d.order_id
AND d.status = "Supply Confirmed"
STRAIGHT_JOIN po.order_main f ON abs( f.order_id ) = abs( a.order_id )
WHERE a.operation
IN (
"Canceled", "Deleted"
)
AND c.department = "Antibody"
AND a.update_date
BETWEEN "2011-02-01 00:00:00"
AND "2011-02-28 23:59:59"
AND 1 =1
几个相关表的信息如下:
item_update_history   58132rows
order_service    133550rows   index:order_id
vendor 413rows  index:user_id
order_status  531835rows  index:order_id
order_main 130394  index:order_id

一番折腾之后, 在item_update_history表上添加了index(order_id, item_id)
然后速度依然没有反应, 感觉abs函数会影响性能, 于是将abs部分改成ON (f.order_id = a.order_id or f.order_id = - a.order_id)
速度上去了, 于是提交给RD那边改,  但是具体原理不是特别明白, 因为我并不确定不用函数能够对性能造成这么大的影响, 因为不用函数最多是能够走索引, 但是初步看explain结果, 并没有走order_main上面的索引.

今天仔细check. 发现两段sql的explain结果如下:

也就是说表join的顺序变掉了, 并且最后join order_main表的时候只是可能能够走到索引.
但是关键的应该是表join顺序, 第二段将order_main表放到最后join, 也就是使用条件过滤掉了大多数数据以后再join order_main表的, 而第一段是一开始就join,  这样两个大表在一起join, 速度自然慢了.
为了验证这一点, 我将第一段sql join order_main的部分改为straight_join, explain结果显示表join顺序与第二段的join顺序一样,  然后我跑了下, 虽然没有第二段速度快, 但也是很快就出来结果了, 大概是因为第二段sql中extra部分中描述的索引的影响.

一次MySQL的升级

有一台Ubuntu上已经默认安装mysql5.1.49, 路径为默认路径, 并且有一个数据库在运行, 为master.

另外, 原来的数据库innodb数据文件设置不合理, 需要重新设置.

我需要将这太mysql升级到5.5.16.

升级步骤如下

1. 在这台server上二进制安装5.5.16, 在路径/usr/local/mysql下面

2. 将master的备份(mysqlbackup完成)apply到5.5.16, 并5.5.16的run mysql_upgrade

3. 使用mysqldump将数据库完整dump为sql文件

4. 停5.5.16, 删除所有数据以及log, 只保留mysql库(如果不保留mysql库, 因为view/procedure中有definer引用, 后面导回数据的时候会报错), 修正innodb数据文件配置为合适的配置

5. 将前面dump的sql文件导回5.5.16

6. 将5.5.16设为master的slave, 并启动slave, 保持数据一致

因为master是一个production环境, 所以需要申请停机时间, 申请了20分钟进行切换.

1. 在master上flush logs with read lock; 并查看主从数据是否一致

2. 主从一致, shutdown master, shutdown slave

3. 将master的data, log, cnf文件备份

4. 删除原有mysql5.1.49的安装, apt-get remove mysql-client, apt-get remove mysql-server, apt-get remove mysql-common(实际证明, 只有最后一项起作用, 因为实际的包名不是mysql-client, mysql-server)

5. 删除/etc/mysql, /etc/init/mysql.conf

6. mv /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

7. update-rc.d -f mysql defaults (重设mysql服务自动启动)

8. 将5.5.16的my.cnf文件移到/etc下, 并编辑, 修正端口以及server id

9. 删除原5.5.16下面的salve相关信息

10. 在my.cnf中添加skip-networking

11. 尝试service mysql start, 查看启动是否正常,  如果正常则再使用socket方式登录, 查看相关的variables值是否正常, 如果有问题则修正

12. 所有都正常以后, 将my.cnf文件中skip-networking去掉, 重新启动mysql服务, 并通知user可以重新使用

MySQL Replication的两个错误

昨天重做production的replication的时候发现有以下两种操作会引起slave端错误:

1. 在master端执行一个很长的update, 然后发现写法问题导致太慢, 于是cancel掉(在SQLYOG端cancel)

2. 直接将另外一个server的myisam表文件copy到数据库目录下, 然后在update/insert语句中使用这个文件

MySQL产生随机密码

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`fun_generate_rand` $$

CREATE

    FUNCTION `test`.`fun_generate_rand`(p_length TINYINT)

    RETURNS VARCHAR(100)

    BEGIN

       DECLARE l_i TINYINT DEFAULT 0;

       DECLARE l_type TINYINT DEFAULT 1;

       DECLARE l_result VARCHAR(100) DEFAULT '';

       

       WHILE l_i < p_length DO

          SET l_type = CEIL(RAND() * 4);

          IF l_type = 1 THEN

             SET l_result = CONCAT(l_result, CHAR(97+CEIL(RAND()*25)));

          END IF;

          

          IF l_type = 2 THEN

             SET l_result = CONCAT(l_result, CHAR(65+CEIL(RAND()*25)));

          END IF;

          

          IF l_type = 3 THEN

             SET l_result = CONCAT(l_result, CHAR(48+CEIL(RAND()*9)));

          END IF;

          

          IF l_type = 4 THEN

             SET l_result = CONCAT(l_result, CHAR(35+CEIL(RAND()*3)));

          END IF;

          

          SET l_i = l_i + 1;

       END WHILE;

       

       RETURN l_result;

    END$$

DELIMITER ;