数据库相关
1.用户权限管理
grant all privileges on . to ‘username’@‘ip’ identified by ‘password’;
GRANT SELECT, INSERT, UPDATE, DELETE,CREATE,ALTER （开发权限）
REVOKE privilege ON databasename.tablename FROM ‘username’@‘host’;（回收权限）
RENAME USER ‘user’@’%’ TO ‘dong’@’%’;（用户名重命名）
修改密码
update mysql.user set authentication_string=password(‘新密码’) where user=‘root’;
2.mysql processlist
批量kill指定连接 导出到sql文件 注释第一行
mysql -uroot -p -e “select concat(‘KILL ‘,id,’;’) from information_schema.processlist where user=‘root’;” > /tmp/a.sql
按运行时间查看进程
SELECT * FROM information_schema.processlist a WHERE a.COMMAND != ‘Sleep’ ORDER BY a.TIME DESC;
分析处于Sleep状态的连接分布情况
select substring_index(host,':', 1) as appip ,count(*) as count from information_schema.PROCESSLIST where COMMAND='Sleep' group by appip order by count desc ;
分析哪些DB访问的比较多
select DB ,count(*) as count from information_schema.PROCESSLIST where COMMAND='Sleep' group by DB order by count desc ;
3.slow log 清理
#查看原来慢日志存放的路径
show variables like ‘%slow_query_log_file%’;
#登陆服务器
shell > touch mysql-slow01.log
shell >chown mysql.mysql mysql-slow01.log
mysql> set global slow_query_log=0;
mysql> set global slow_query_log_file='/data/log/mysql-slow01.log'; # 文件要存在
mysql> set global slow_query_log=1;登陆服务器
shell > touch mysql-slow01.log
4.table信息统计相关
db中大于10G表
select TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS,ROUND((INDEX_LENGTH+DATA_FREE+DATA_LENGTH)/1024/1024/1024) as size_G from information_schema.tables where ROUND((INDEX_LENGTH+DATA_FREE+DATA_LENGTH)/1024/1024/1024) > 10 order by size_G desc ;
查询次数最多的sql
select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN from performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like 'select%' and DIGEST_TEXT not like '%SESSION%' and SCHEMA_NAME!='NULL' order by COUNT_STAR desc limit 10\G
写入次数最多的sql
select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN from performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like 'insert%' or DIGEST_TEXT like 'update%'or DIGEST_TEXT like 'delete%' or DIGEST_TEXT like 'replace%' order by COUNT_STAR desc limit 10\G
查看一个MySQL实例中哪个库的总访问量最大
select OBJECT_SCHEMA,sum(SUM_TIMER_WAIT) as all_time,sum(COUNT_STAR) as all_star,sum(COUNT_read) as all_read ,sum(COUNT_WRITE) as all_write,sum(COUNT_FETCH) as all_fetch,sum(COUNT_INSERT) as all_insert,sum(COUNT_UPDATE) as all_update,sum(COUNT_DELETE) as all_delete from performance_schema.table_io_waits_summary_by_table group by OBJECT_SCHEMA order by all_star desc limit 3
哪个库的write latency时间最大
select OBJECT_SCHEMA,sum(SUM_TIMER_WAIT) as all_time,sum(SUM_TIMER_READ) as all_read_time,sum(SUM_TIMER_WRITE) as all_write_time,sum(COUNT_STAR) as all_star,sum(COUNT_read) as all_read ,sum(COUNT_WRITE) as all_write,sum(COUNT_FETCH) as all_fetch,sum(COUNT_INSERT) as all_insert,sum(COUNT_UPDATE) as all_update,sum(COUNT_DELETE) as all_delete from performance_schema.table_io_waits_summary_by_table group by OBJECT_SCHEMA order by all_write_time desc limit 3;
|