1.看阿里云的入网规则(正确)
允许 | 自定义 TCP | 3306/3306 | 地址段访问 | 0.0.0.0/0 |
2.mysql授权(正确)
先删用户,创建用户,授权用户,立即更新权限,查询用户
DROP USER 'root'@'%';
CREATE USER 'root'@'%' identified by 'root';
GRANT ALL ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;
select * from mysql.user;
CREATE USER 'root'@'%' identified by '123456'; 创建新用户,名为root,密码为123456,任何地方登陆
(只允许从本机登陆填‘localhost’ ,如果允许从远程登陆,则填 ‘%’)
GRANT 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;
3.wdcp 查linux防火墙
发现我把源端口也填了3306,改为不限就正常了。
比如开通80端口,在目标端口上填80即可,确定
宝塔面板后台设置 比如放行端口:[3306]
mysql常用运维例句
select * from file where memo='2018-03-31';
ALTER TABLE version ADD UNIQUE (MachineCode);
UPDATE file SET valid=0 where valid=1; --20170502更新有效状态
select * from file where filename like "%人民的名义%" and filename <>"人民的名义 - 周梅森.epub"
update file set valid=2 where filename like "%人民的名义%" and filename <>"人民的名义 - 周梅森.epub"
update sex.sex_dance set CS_SHits=CS_SHits+28345 ;
delete from sex.sex_dance where cs_id<6123 and cs_id>834;
taskkill /F /FI "WINDOWTITLE ne hr*"
DROP USER 'root'@'%';
CREATE USER 'root'@'%' identified by 'root' ;
GRANT ALL ON *.* TO 'root'@'%';
DROP USER 'wangpansou'@'%';
CREATE USER 'wangpansou'@'%' identified by 'xhrrj' ;
GRANT ALL ON wangpansou.* TO 'wangpansou'@'%';
select * from file where filename like '%百%';
select count(*) as 今日搜索次数 from usersearch where date like '2017-02-20%';
--删除地址重复,保留最小id
DELETE
FROM
file
WHERE
fileaddress IN (
select * from ((
SELECT
fileaddress
FROM
file
GROUP BY
fileaddress
HAVING
count(fileaddress) > 1
) a)
)
AND id NOT IN (
select * from ((
SELECT
min(id)
FROM
file
GROUP BY
fileaddress
HAVING
count(fileaddress) > 1
) b)
)
--是否有地址重复
select fileaddress from file group by fileaddress having (count(fileaddress)>1)
--是否有UK重复
select uk from avaiuk group by uk having (count(uk)>1)
select unix_timestamp(now())*1000
select FROM_UNIXTIME(1488073214);
select FROM_UNIXTIME(1488086459000/1000); //2017-02-21 13:45:47
INSERT INTO file (filename,fileaddress,uk,category,feed_time) VALUES ('【AASS】图片','https://pan.baidu.com/share/link?uk=2369047146&shareid=1131012816','2369047146','6',FROM_UNIXTIME(1156219870));
INSERT INTO file (filename,fileaddress,uk,category,feed_time) VALUES ('VID_20161120_172327.mp4','https://pan.baidu.com/share/link?uk=204673600&shareid=2282202158','204673600','1',
FROM_UNIXTIME(1479647701651))
select * from file where Id=16759;
select valid ,count(*) from file group by valid;
select * from file where valid =0 limit 10;
update file set valid=0 where valid =3;
select count(*) from file where uk=2569648469 ;
update avaiuk set fileflag=0 where fileflag=1;
update file set weight=1 where weight=14;
select DISTINCT * from file where fileaddress='https://pan.baidu.com/share/link?uk=3761695818&shareid=2513917462';
select Id,fileaddress from file where valid is NULL
select valid ,count(*) from file group by valid;
update file set valid=null ;
select valid ,count(*) from file where fileaddress like '%album_id%' group by valid;
select * from file where memo is not null
select uk from avaiuk group by uk having (count(uk)>1)
select version();
show variables like "%pro%";
set profiling = 1;
show processlist
show variables like '%long_query_time%';
show profiles;
select * from file where valid=1 and type='3'
select * from file where memo like 'http://www.6maa.com/%'
select * from file where filename like '%mp4%' and filename not like '%.%' and valid=1
select * from file where fileaddress ='http://www.javaapk.com/tools/flow/6251.html'
select * from file where valid=5 and memo not like '%微盘%' --5313
select * from file where valid<>5 and fileaddress like '%vdisk.weibo.com%'
select * from file where valid=4 and fileaddress like '%vdisk.weibo.com%' and memo<>'新浪微盘' --38770
--34325 memo='新浪微盘'
--4446 memo='2015-07-04'
explain select * from wangpansou.file where valid<>2 and Id>-1 and filename like '%英语' limit 20
select valid ,count(*) as cou from file group by valid order by valid
create INDEX search on wangpansou.file(valid,Id,filename);
select count(*) from file where filename like '%<em>%';
select * from file where filename like '%<em>%';
select * from file where valid=5
select * from file where valid=0 order by Id desc
select * from file where Id>12414350 and valid<>2 limit 2;
select * from file where valid<>2 and Id>=1
select * from file where valid=1 and Id<12406940 order by Id desc limit 20
select lastid as 最后Id,date as 最后更新日期 from flag limit 1
select * from file where memo='2018-06-07'
select valid ,count(*) as cou from file group by valid order by valid
select * from file where fileaddress="https://pan.baidu.com/s/1Dwci7XM29j3xQRjyGH5Z6g"
select Id from file order by Id desc limit 1
select * from file where memo='2018-05-08' order by Id desc;-- 156 12143732 12179718 35986
select * from sinaflag where url='http://vdisk.weibo.com/s/B_fR0Qc-SuSFH?category_id=0&parents_ref=B_fR0Qc-SuSsI,B_fR0Qc-SuSFo'
select distinct Id,q from usersearch order by Id desc limit 1000
select count(*) from file where fileaddress='http://'
Select * from dede_arctype where content like '%织梦58%'
select * from file where memo='新浪微盘' and valid=3 ;
check table file
repair table file
select * from file where memo like 'http://%'
select * from file where Id=16666182
select * from file where fileaddress like '%http://http://%';
update file set fileaddress=REPLACE(fileaddress,'http://http://','http://') where fileaddress like '%http://http://%';
update file set filename=REPLACE(filename,' - 百度网盘云资源 - 小白盘','') where filename like '% - 百度网盘云资源 - 小白盘%';
update file set memo=CONCAT('<a href=\'',memo,'\'>来源</a>') where memo like 'http://%' and Id=16666182;
delete from file where valid=6 and memo not like "%提取码%"
update file set memo='新浪微盘' where valid=5 and memo not like '%微盘%'
update file set valid=5 where fileaddress like '%vdisk.weibo.com%'
update file set type='3' where filename like '%mp4等'
update file set valid=1 where memo='新浪微盘' and valid=3 ;
update file set filename=REPLACE(filename,'</em>','') where filename like '%</em>%';
update file set memo=REPLACE(memo,' 大小: ','') where memo like '% 大小: ';
update file set valid=0 where memo like'%上传%' and valid=1;
update dede_arctype set content=REPLACE(content,'织梦58','小黄人软件') where content like '%织梦58%'
INSERT INTO list (filename,type,fileaddress,feed_time,valid) VALUES ('name','动漫','address','2018-09-15',4)