MySQL常用命令总结

show table status like '%tablename%';

./mysqladmin -uroot -hlocalhost --socket=/data/mysql_3306/tmp/mysql.sock -p password

ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password BY 'password';

select concat('KILL ',id,';')from information_schema.processlist where user='21xmt_user';

mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000201

drop user 'xxx'; 只删除 'xxx'@'%' 账户

alter table xxx drop primary key,add primary key(task_id, aaa);

select table_name , table_rows from inforation_schema.tables where table_name='xxx';

SELECT
 CONCAT(
   'show grants for \'',
   user,
   '\'@\'',
   Host,
   '\';'
 ) AS ShowGrants
FROM
 mysql.`user`
WHERE
 `User` NOT IN (
   'root',
   'mysql.session',
   'mysql.sys'
 );

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/%';

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

show engine innodb status\G；

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();

配置文件添加skip-grant-tables

alter user 'root'@'localhost' identified by 'password';

flush privileges;

配置文件去掉skip-grant-tables

systemctl restart mysqld;

alter table xxx modify id bigint auto_increment;

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

alter table tablename engine innodb;
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...;

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';

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;

begin；
要执行的sql；
commit/rollback；

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;

SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');