连接信息
use information_schema;
select * from information_schema. ` PROCESSLIST` where info is not null ;
注释查询
select table_name, column_name, column_comment from information_schema. columns
where table_schema = 'dbA' and COLUMN_NAME like '%columnName%' ;
select table_name 表名, TABLE_COMMENT 表注释 from INFORMATION_SCHEMA. TABLES
Where table_schema = 'dbA' AND table_name = "tableA" ;
select table_name, column_name, column_comment from information_schema. columns
where table_schema = 'dbA' and column_comment like '%注释%' ;
数据库配置
show global status like 'Thread%' ;
show variables like '%connect%' ;
变量
set @uname = '张三' ;
UPDATE table p SET p. user_name= @uname WHERE p. no = '1234' ;
慢查询
use mysql;
show global variables like '%log_output%' ;
set global log_output = 'TABLE' ;
set global log_queries_not_using_indexes= off ; 慢查询中不写入无索引查询的日志。
show variables like "%slow%" ;
set global slow_query_log = ON ;
set long_query_time= 1 ; 开启慢查询,超过1 秒的记录入日志
mysqldumpslow - s t - t 2 / tmp/ mysqlslow. log 查看慢查询日志
(
- s, 是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙;
- t, 是top n的意思,即为返回前面多少条的数据;
- g, 后边可以写一个正则匹配模式,大小写不敏感的;
)
普通日志
show global variables like '%general%' ;
set global general_log = ON ;
SELECT argument from general_log
where argument not like '%SELECT%' AND ( argument like '%UPDATE%' OR argument like '%INSERT%' ) order by event_time desc ;
密码重置
MYSQL密码重置:
mysqld_safe
mysql - u root mysql
update user set Password = PASSWORD( 'newPassword' ) where User = 'root' ;
flush privileges ;