php的慢速日志引起的Mysql2013错误

Description:
————
If mysql query is longer as request_slowlog_timeout, connection breaks.

Test script:

<?php
// request_slowlog_timeout = 10s  (at /etc/php5/fpm/php-fpm.conf)

// $mysqli =
// ...
$query = "SELECT SLEEP (15)";

$res = $mysqli->query($query);
if (!$res) {
	echo $mysqli->error; // Error Code: 2013. Lost connection to MySQL server during query
	exit;
}

Expected result:
—————-
connection must be preserved and the request should be executed

https://bugs.php.net/bug.php?id=67087&edit=3

truncate和delete之间有什么区别

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。 DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。
TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。

TRUNCATE,DELETE,DROP放在一起比较:

操作 区别
TRUNCATE TABLE 删除内容、释放空间但不删除定义。
DELETE TABLE 删除内容不删除定义,不释放空间。
DROP TABLE 删除内容和定义,释放空间。

mysql 复制心跳

在 MySQL 主从复制时,有时候会碰到这样的故障:在 Slave 上 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes,Slave_SQL_Running_State 显示 Slave has read all relay log; waiting for the slave I/O thread to update it ,看起来状态都正常,但实际却滞后于主,Master_Log_File 和 Read_Master_Log_Pos 也不是实际主上最新的位置。一种可能是 Master 上的 binlog dump 线程挂了。但有时候,在 Master 上检查也是完全正常的。那 Slave 的延误又是怎么造成的呢?

在 MySQL 的复制协议里,由 Slave 发送一个 COM_BINLOG_DUMP 命令后,就完全由 Master 来推送数据,Master、Slave 之间不再需要交互。如果 Master 没有更新,也就不会有数据流,Slave 就不会收到任何数据包。但是如果由于某种原因造成 Master 无法把数据发送到 Slave ,比如发生过网络故障或其他原因导致 Master 上的 TCP 连接丢失,由于 TCP 协议的特性,Slave 没有机会得到通知,所以也没法知道收不到数据是因为 Master 本来就没有更新呢还是由于出了故障。

好在 MySQL 5.5 开始增加了一个复制心跳的功能。

stop slave;
change master to master_heartbeat_period = 10;
set global slave_net_timeout = 25;
start slave;

就会让 Master 在没有数据的时候,每 10 秒发送一个心跳包。这样 Slave 就能知道 Master 是不是还正常。slave_net_timeout 是设置在多久没收到数据后认为网络超时,之后 Slave 的 IO 线程会重新连接 Master 。结合这两个设置就可以避免由于网络问题导致的复制延误。master_heartbeat_period 单位是秒,可以是个带上小数,如 10.5。最高精度为 1 毫秒。

slave_net_timeout 的默认是 3600,也就是一小时。也就是说,在之前的情况下,Slave 要延误 1 小时后才会尝试重连。而在没有设置 master_heartbeat_period 时,将 slave_net_timeout 设得很短会造成 Master 没有数据更新时频繁重连。

很奇怪的是,当前的 master_heartbeat_period 值无法通过 show slave status 查看,而要使用 show status like ‘Slave_heartbeat_period’ 查看。此外,状态变量 Slave_last_heartbeat 表示最后一次收到心跳的时间,Slave_received_heartbeats 表示总共收到的心跳次数。

如:

mysql> show status like 'slave%';
+----------------------------+---------------------+
| Variable_name              | Value               |
+----------------------------+---------------------+
| Slave_heartbeat_period     | 5.000               |
| Slave_last_heartbeat       | 2014-05-08 11:48:57 |
| Slave_open_temp_tables     | 0                   |
| Slave_received_heartbeats  | 1645                |
| Slave_retried_transactions | 0                   |
| Slave_running              | ON                  |
+----------------------------+---------------------+
6 rows in set (0.00 sec)

原地址:http://xiezhenye.com/2014/05/mysql-%E5%A4%8D%E5%88%B6%E5%BF%83%E8%B7%B3.html

不重启mysql情况修改参数变量

地球人都知道,更新mysql配置my.cnf需要重启mysql才能生效,但是有些时候mysql在线上,不一定允许你重启,这时候应该怎么办呢?

看一个例子:

mysql> show variables like 'log_slave_updates';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | OFF   |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> set global log_slave_updates=1;
ERROR 1238 (HY000): Variable 'log_slave_updates' is a read only variable

看到了吧?报错了!

后来查了一下资料,发现有一个叫gdb的东西,感觉相当牛X,可以实现在线更改mysql参数,请看例子:

mysql> system gdb -p $(pidof mysqld) -ex "set opt_log_slave_updates=1" -batch
mysql> show variables like 'log_slave_updates';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | ON    |
+-------------------+-------+
1 row in set (0.00 sec)

但是在一些可重复的参数,不能直接用set更改,那这时候又要怎么办呢?老外给了一个解决方案:

mysql> show slave status \G
...
     Replicate_Do_DB: test
...
mysql> system gdb -p $(pidof mysqld)
          -ex 'call rpl_filter->add_do_db(strdup("hehehe"))' -batch
mysql> show slave status \G
...
      Replicate_Do_DB: test,hehehe
...

mysql超出最大连接数原因分析

遇到mysql超出最大连接数,相信不少人第一反应就是查看mysql进程,看有没有慢查询,当然这个做法是完全正确的!
但是很多时候真正的问题不在这里。
今天有遇到同样的问题,一味查看mysql进程和慢查询日志,无果。
后来老大提点了一下,查看一下nginx日志,发现有一两个访问执行时候比较长,然后使用top命令查看了一下服务器负载,惊了,居然超高!
最后发现原来有一台web分流主机挂了,导致另外几台web主机负载增高,从而导致了php-fpm的执行效率降低。
那么这跟mysql有什么关系呢?原因很简单,因为php执行时间过长,mysql连接迟迟未释放,就会导致连接数过多出现。
最后总结:其实很多时候,一个问题的根本原因并不是那么直接的呈现出来,需要自己去跟踪。
老大有一句很实用的话:遇到问题先查日志(mysql、php、nginx等)。

未知的恐惧——NULL

SELECT NULL =0, NULL =12345, NULL <>12345, NULL +12345, NULL || ‘abc’, NULL = NULL , NULL <> NULL , NULL AND TRUE , NULL AND FALSE , NULL OR FALSE , NULL OR TRUE , NOT (NULL);

如果这是一道面试题,估计不知道有多少程序员甚至是DBA会阵亡……

正确的答案是什么?(为了加深印象,建议复制SQL到mysql里去执行,看一下)

下面跟大家分析一下原因:

nullinmysql

那么在应用中如何避免NULL带来的一些困扰呢?

  • 把NULL当成一个特殊值,不等于空、0、FALSE,使用IS NULL/IS NOT NULL去检测
  • 声明NOT NULL列,给于默认值

解决MySQL server has gone away

主要可能是因为以下几种原因:

1、可能是发送的SQL语句太长,以致超过了max_allowed_packet的大小,如果是这种原因,你只要修改my.cnf,加大max_allowed_packet的值即可。

2、可能是因为某些原因导致超时,比如说程序中获取数据库连接时采用了Singleton的做法,虽然多次连接数据库,但其实使用的都是同一个连接,而且程序中某两次操作数据库的间隔时间超过了wait_timeout(SHOW STATUS能看到此设置),那么就可能出现问题。最简单的处理方式就是把wait_timeout改大,当然你也可以在程序里时不时顺手mysql_ping()一下,这样MySQL就知道它不是一个人在战斗。
继续阅读解决MySQL server has gone away

frm文件时的表结构恢复

了解MYSQL的都知道,在MYSQL中建立任何一张数据表,在其数据目录对应的数据库目录下都有对应表的.frm文件,.frm文件是用来保存每个数据表的元数据(meta)信息,包括表结构的定义等,.frm文件跟数据库存储引擎无关,也就是任何存储引擎的数据表都必须有.frm文件,命名方式为数据表名.frm,如user.frm. .frm文件可以用来在数据库崩溃时恢复表结构。

下面说说如何通过.frm文件恢复数据表结构。 继续阅读frm文件时的表结构恢复

mysql 1203错误分析与解决方案

一、问题
mysql常常报1203错误,超出max_user_connections限制;

二、分析
服务器有长短连接混用
max_connections和max_user_connections值同为2048;
SHOW STATUS LIKE ‘%connect%’查看到当前连接数约1100;

三、解决
# 更改max_user_connections
SET GLOBAL max_user_connections=0;

# 查看max_user_connections是否生效
SELECT @@max_user_connections;

# 查看当前服务器连接数
SHOW STATUS LIKE ‘%connect%’;

# 修改my.cnf
max_user_connections = 0

三、参考资料
https://bugs.launchpad.net/percona-server/+bug/893348
http://dev.mysql.com/doc/refman/5.5/en/news-5-5-27.html
If an account had a nonzero MAX_USER_CONNECTIONS value, that value was not always respected. (Bug #65104, Bug #14003080)