我的mysql常用运维例句 远程连接权限不够mysql 报10038错误的解决 阿里云 linux ECS wdcp等 删除、创建、授权、查询用户

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)