mysql常用命令

1.导出sql

mysqldump -u$root2 -p$pwd2 -h$ip2 --opt $db2 amc_advertise_idea_relation > $sqlfile
#忽略注释压缩
mysqldump -u$user -p$pwd -h$host --opt db --skip-comments | gzip > file
#附redis备份
redis-dump -u :password@127.0.0.1:6379 |gzip > file

2.导入sql

mysql -u$root3 -p$pwd3 -h$ip3 --default-character-set=utf8 $db3 < $sqlfile

3.用户受权

grant all  on jeecnDB.* to jeecn@'localhost' identified by '密码';
#部分权限
grant select,update  on jeecnDB.* to jeecn@'localhost' identified by '密码';
#已经存在用户
grant all  on jeecnDB.* to jeecn@'localhost'；

4.刷新系统权限表

一般用户权限修改后需要执行

flush privileges;

5.查看mysql连接

show full processlist;
#等同于
select * from information_schema.processlist limit 10

6.bin log

/usr/local/mysql/bin/mysqlbinlog -v --base64-output=DECODE-ROWS --set-charset=gbk --start-datetime='2017-08-04 10:00:00' -d xyk2_db mysql-bin.000012 |grep inser

7.关闭mysql所有连接

for i in $(mysql -uroot -p'pwd' -Bse "show processlist" | awk '{print $1}');do mysql -uroot -p'pwd' -e "kill $i";done

8.时间分类统计

SELECT (FROM_UNIXTIME(use_date,'%H:00')) AS h,count(1) as n from evente_new_ticket where `t_state` = '2' AND `org_id` = '100160' AND `product_id` = '901189' AND `product_sub_id` = '708806' and use_date >= 1565941347  GROUP BY h ;