月度归档:2017年05月

mysql mark

#mysql8添加用户

create user 'mysqlexporter1'@'%' identified by 'mysqlexporter1';
grant ALL on *.* to 'mysqlexporter1'@'%';
flush privileges;

#mysql查找不连续的id

#查找不连续的ID 

select id from (select id from s_users order by id asc) t where not exists (select 1 from s_users where id=t.id-1)

select fromid from (select fromid from ethusdt_trades order by fromid asc) t where not exists (select 1 from ethusdt_trades where fromid=t.fromid-1)

#mysql5.7用户授权

GRANT ALL ON apps_hney_exchange.* TO 'apps_hney_main_oper'@'10.1.5.%';

#检验授权是否添加成功
use mysql;
select * from db\G;

sync_binlog =  N
N>0    每向二进制日志文件写入N条SQL或N个事务后,则把二进制日志文件的数据刷新到磁盘上; 
N=0    不主动刷新二进制日志文件的数据到磁盘上,而是由操作系统决定(默认为30s); 

因此可建议获得更高性能

innodb_flush_log_at_trx_commit=0/2 
sync_binlog=500

#innodb_flush_logs_at_trx_commit=N

1 最安全 最慢
0 最快  mysql挂掉(损失1S数据)  linux挂掉    (损失1S数据)
2 中间  mysql挂掉(不丢数据)     linux挂掉  (损失1S数据)

#update 子查询 limit

update vi_works du,(select voteid from public_vote ORDER by num DESC limit 0,2) b set du.state=2 where du.id=b.voteid and du.state=1;



update vi_works du,(select * from vi_works ORDER by public_vote DESC limit 0,2) b set du.state=2 where du.id=b.id and du.state=1;