Mysql开启慢查询记录耗时SQL语句

简述

  网站数据达到一定的数量级,就会有页面卡,出现50x等各种问题,mysql服务器CPU居高不下等症状。因为sql缓慢导致php缓慢,导致超时。这个时候应该先从mysql入手。就像看病一样,要先找病根,记录慢查询日志,就是观察期,观察到迹象我们就可以对症下药了。

配置参数说明

  主要靠三个参数来配置,我们用通俗的话来解释。

  1. 开关(不多说,关闭就不记录了)
    slow_query_log
  2. log存储位置(记录下来,我得知道你在哪吧,一般将它存放到mysql数据目录,也就是和你创建的数据库在同级目录)
    slow_query_log_file
  3. 超时时间 (我得让你知道多慢我忍不了)
    long_query_time

设置方法

方法一:全局变量设置

  1. 将 slow_query_log 全局变量设置为“ON”状态
    mysql> set global slow_query_log='ON'; 
  2. 设置慢查询日志存放的位置(默认在 mysql data 目录下)
    mysql> set global slow_query_log_file='slow.log';
  3. 记录超时1秒的sql语句
    mysql> set global long_query_time=1;

    注意:无需重启即可生效,但重启会失效。set global 改的参数是暂时的,想重启不变请配合修改mysql配置文件。

- 阅读全文 -

Mysql错误 Index column size too large. The maximum column size is 767 bytes

  起因:准备上线一个phphub5的项目,本地是mysql5.7,数据库是没有问题的,线上环境5.5,版本较低,导入的时候出错,现在记录一下解决过程。

[Err] 1709 - Index column size too large. The maximum column size is 767 bytes.

错误原因是因为索引的字段太长,在 stack overflow 上找到了答案。查看原地址

原来SQL:

ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

修改之后SQL:

ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

解决方法:在 CREATE TABLE 语句后面加上 ROW_FORMAT=DYNAMIC 就可以了。

Mysql开启general_log记录

        开启general log会将所有的SQL语句记录下来。生产环境一般不会开启,因为log的量会非常庞大。但个别情况下可能会临时的开一会儿general log以供排障使用。 


* 如果是 mysql5.4以下版本

在my.ini 文件中 设置 log='你的log 日志路径'

在[mysqld]后面增加一行  然后重启mysql 生效

log=D:/wamp/www/mysql_bz.log

* 如果是 mysql5.5以上版本  一次性修改方法 重启后无效

SHOW VARIABLES LIKE '%general_log%'
SET GLOBAL general_log = 1
SET GLOBAL general_log_file = '你的log 日志路径'

长期有效修改方法在 my.ini 里面 [mysqld] 后面加上如下代码 没有 [mysqld] 自己加上

[mysqld]
general_log=ON
general_log_file=D:/wamp/logs/mysql_general.log  // 这里设置你 log日志路径

# log-raw=true  如果错误日志没记录 则开启这行,

参考地址:

http://dev.mysql.com/doc/refman/5.7/en/query-log.html

http://dev.mysql.com/doc/refman/5.7/en/password-logging.html

然后重启mysql 生效

错误的sql不会被成功解析,所以不会记录到general log中

如果需要记录所有的语句,包括那些错误的,请加 log-raw选项  log-raw=true


MySQL之Field‘***’doesn’t have a default value错误解决办法

  一个注册报错的问题,是因为字段没有默认值,我本地环境mysql5.5没有问题,外网演示环境是mysql5.6,通过查资料知道了原来是my.ini配置问题的问题。

解决方法:

打开my.ini,查找

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

修改为

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后重启MYSQL


STRICT_TRANS_TABLES模式:严格模式,进行数据的严格校验,错误数据不能插入,报error错误。 

在STRICT_TRANS_TABLES模式下,当我们插入数据时,mysql会严格的进行数据的校验,当发现插入列值未满足要求,直接报告error错误,保证了错误数据无法插入到数据库中。


MySql不停机添加丛库小记

  拖了很长时间才开始写这篇文章,本来打算上线之后第二天立刻开始记录的,一直有很多碎片事情要做。

  基础是已经有一主一丛,或一主多从,由于业务需要增加新的丛库来分担读的压力。查看如何配置主从复制

不停机平滑加丛库主要原理是: 

1、停掉当前的丛库同步 stop slave。做这一步之前需要需要代码层的改动,将这个从库从web业务脱离,建议访问量少时候操作。

2、查看丛库状态,show slave status  记下 Relay_Master_Log_file 和 Exec_Master_Log_Pos 

3、备份丛库,Myisam直接打包文件拷贝到另一台机器就可以了。Innodb的可以用xtrabackup备份,数据量少可以直接用Navicat数据传输,或者mysqldump导出。 

4、将数据库备份拷贝到待加丛库的机器,数据怎么弄过来方法很多,一定要保证拷贝过来的数据与上一个丛库的数据一致,数据表正常可用。 

5、编辑配置文件 my.cnf  修改 server-id (不要与之前的丛库相同) replicate-do-db(同步的库)  这两个参数

6、在新丛库设置主库的连接信息

change master to
master_host='192.168.0.xxx',
master_port=3306,
master_user='userdb_slave',     
master_password='xxxxxxxxxxxxxxxxx',
master_log_file='mysql-bin.000169',     # 上面记录的
master_log_pos=824721706;               # 上面记录的

7、重启数据库,start slave 开启同步。show slave status  

看这两个参数 Slave_IO_Running    Yes   Slave_SQL_Running   Yes  那就证明主从正常了

8、不要忘了把上一个丛库开启同步 start slave,查看上一个丛库和新加丛库是否正常同步可用,最后加入到web业务代码里面。