最近整理笔记，分享一篇常用命令给大家。值得收藏。hah....
1、查看表状态
代码语言：javascript复制show table status like '%tablename%';
2、改密码
方法一：
代码语言：javascript复制./mysqladmin -uroot -hlocalhost --socket=/data/mysql_3306/tmp/mysql.sock -p password
方法二：
代码语言：javascript复制ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password BY 'password';
3、杀特定用户链接
代码语言：javascript复制select concat('KILL ',id,';')from information_schema.processlist where user='21xmt_user';
4、binlog解析
https://cloud.tencent.com/developer/article/1925495
代码语言：javascript复制mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000201
5、删除用户
代码语言：javascript复制drop user 'xxx'; 只删除 'xxx'@'%' 账户
6、修改主键（小表，大表用pt-osc等工具）
代码语言：javascript复制alter table xxx drop primary key,add primary key(task_id, aaa);
7、查看表大小
代码语言：javascript复制select table_name , table_rows from inforation_schema.tables where table_name='xxx';
8、权限查询
代码语言：javascript复制SELECT
CONCAT(
'show grants for '',
user,
''@'',
Host,
'';'
) AS ShowGrants
FROM
mysql.`user`
WHERE
`User` NOT IN (
'root',
'mysql.session',
'mysql.sys'
);
9、PS库内存使用
代码语言：javascript复制To control memory instrumentation state at server startup, use lines like these in your my.cnf file:
Enable:
[mysqld]performance-schema-instrument='memory/%=ON'Disable:
[mysqld]performance-schema-instrument='memory/%=OFF'To control memory instrumentation state at runtime, update the ENABLED column of the relevant instruments in the setup_instruments table:
Enable:
UPDATE performance_schema.setup_instrumentsSET ENABLED = 'YES'WHERE NAME LIKE 'memory/%';Disable:
UPDATE performance_schema.setup_instrumentsSET ENABLED = 'NO'WHERE NAME LIKE 'memory/%';
10、trace
代码语言：javascript复制SET SESSION OPTIMIZER_TRACE="enabled=on"; # enable tracing
<statement to trace>; # like SELECT, EXPLAIN SELECT, UPDATE, DELETE...
SELECT * FROM information_schema.OPTIMIZER_TRACE;
[ repeat last two steps at will ]
SET SESSION OPTIMIZER_TRACE="enabled=off"; # disable tracing
11、innodb状态查看
代码语言：javascript复制show engine innodb statusG；
12、event 操作
13、造测试数据
代码语言：javascript复制create table t1(id int primary key, a int, b int, index(a));
drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i <= 1000000)do
insert into t1 values(i, 1000001 - i, i);
set i = i 1;
end while;
end;;
delimiter ;
call idata();
14、修改root密码
代码语言：javascript复制配置文件添加skip-grant-tables
alter user 'root'@'localhost' identified by 'password';
flush privileges;
配置文件去掉skip-grant-tables
systemctl restart mysqld;
15、添加自增属性
代码语言：javascript复制alter table xxx modify id bigint auto_increment;
15、rename库名 (所有表操作即可完成库名重命名)
代码语言：javascript复制RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
16、表碎片整理
代码语言：javascript复制alter table tablename engine innodb;
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...;
17、查看表的最近更新时间
代码语言：javascript复制SELECT
`TABLE_NAME`, `CREATE_TIME`, `UPDATE_TIME`
FROM
`information_schema`.`TABLES`
WHERE
`information_schema`.`TABLES`.`TABLE_SCHEMA` = 'ob1_dg_log'
AND
`information_schema`.`TABLES`.`TABLE_NAME` = 'w_logbattle';
18、创建索引时间查询
代码语言：javascript复制SELECT trx_id, trx_started, (NOW() - trx_started) trx_duration_seconds, id processlist_id, user, IF(LEFT(HOST, (LOCATE(':', host) - 1)) = '', host, LEFT(HOST, (LOCATE(':', host) - 1))) host, command, time, REPLACE(SUBSTRING(info,1,25),'n','') info_25 FROM information_schema.innodb_trx JOIN information_schema.processlist ON innodb_trx.trx_mysql_thread_id = processlist.id WHERE (NOW() - trx_started) > 60 ORDER BY trx_started;
19、mysql 终端中操作启用事务（DML操作记得加）
代码语言：javascript复制begin；
要执行的sql；
commit/rollback；
20、修改表的字符集
代码语言：javascript复制ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;
21、查看用户自定义视图
存储过程、triggers查询方法类似，自己找相关表查询即可。
代码语言：javascript复制SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');
