TOC

MySQL 管理语句

MySQL 语句:

  1. DDL
  2. DML
  3. 事件和锁
  4. Replication
  5. Prepared
  6. Compound
  7. 数据库管理
  8. 工具

用户管理

角色 (用户模板)

create role stuff@localhost;
... 分配权限,修改属性
select host, user as role from mysql.user where authentication_string = '';
  • CREATE ROLE [IF NOT EXISTS] role [, role ] ...
    锁定,密码为空
  • DROP ROLE
  • SET ROLE
  • SET DEFAULT ROLE

用户

select host, user, plugin, left(authentication_string, 5) as pass, account_locked from mysql.user;
select distinct user from mysql.user;

select current_user(), current_role();
  • CREATE USER
  • DROP USER
  • ALTER USER
  • RENAME USER

密码

  • SET PASSWORD

权限

  • GRANT
  • REVOKE

资源组管理

  • CREATE RESOURCE GROUP
  • DROP RESOURCE GROUP
  • ALTER RESOURCE GROUP
  • SET RESOURCE GROUP

表维护

  • ANALYZE TABLE
  • CHECK TABLE
  • CHECKSUM TABLE
  • OPTIMIZE TABLE
  • REPAIR TABLE

组件,插件,Loadable Function

  • CREATE FUNCTION
  • DROP FUNCTION

  • INSTALL COMPONENT

  • UNINSTALL COMPONENT

  • INSTALL PLUGIN

  • UNINSTALL PLUGIN

CLONE

SET

  • SET 设置变量
  • SET CHARACTER SET
  • SET NAMES

SHOW

  • SHOW BINARY LOGS binlog
  • SHOW BINLOG EVENTS binlog
  • SHOW CHARACTER SET 字符集
  • SHOW COLLATION 排序规则
  • SHOW COLUMNS 查看表结构

    SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]
    
  • SHOW CREATE DATABASE

  • SHOW CREATE EVENT
  • SHOW CREATE FUNCTION
  • SHOW CREATE PROCEDURE
  • SHOW CREATE TABLE
  • SHOW CREATE TRIGGER
  • SHOW CREATE USER
  • SHOW CREATE VIEW

  • SHOW DATABASES 数据库

  • SHOW ENGINE 查看指定引擎的状态

    部分引擎会提供一些状态信息,比如 INNODB, PERFORMANCE_SCHEMA

  • SHOW ENGINES 存储引擎

  • SHOW ERRORS
  • SHOW EVENTS
  • SHOW FUNCTION CODE
  • SHOW FUNCTION STATUS
  • SHOW GRANTS 用户权限
  • SHOW INDEX 查看指定表的索引
  • SHOW MASTER STATUS
  • SHOW OPEN TABLES
  • SHOW PLUGINS 插件
  • SHOW PRIVILEGES
  • SHOW PROCEDURE CODE
  • SHOW PROCEDURE STATUS
  • SHOW PROCESSLIST
  • SHOW PROFILE
  • SHOW PROFILES
  • SHOW RELAYLOG EVENTS
  • SHOW REPLICAS
  • SHOW SLAVE HOSTS | SHOW REPLICAS
  • SHOW REPLICA STATUS
  • SHOW SLAVE | REPLICA STATUS
  • SHOW STATUS
  • SHOW TABLE STATUS
  • SHOW TABLES
  • SHOW TRIGGERS
  • SHOW VARIABLES
  • SHOW WARNINGS

其他

  • BINLOG
  • CACHE INDEX
  • FLUSH
  • KILL
  • LOAD INDEX INTO CACHE
  • RESET
  • RESET PERSIST
  • RESTART
  • SHUTDOWN