一、连接数据库

使用 root 用户和指定密码连接本地 MySQL 数据库

root@localhost:(none)> `mysql -uroot -p'password'`

指定主机和端口连接 MySQL 数据库

root@localhost:(none)> `mysql -uroot -p'password' -h 127.0.0.1 -P 3306`

使用指定的套接字文件连接 MySQL 数据库

root@localhost:(none)> `mysql -uroot -p'password' -S /tmp/mysql.sock`

二、查看会话相关信息

1. 查看当前数据库中的会话状态：

root@localhost:(none)>`show processlist;`

2. 查看当前数据库中的活动会话（排除空闲 Sleep 状态的会话）：

root@localhost:(none)>select * from performance_schema.processlist where command <> 'Sleep';

三、查看数据库大小信息

1. 数据库总大小：

root@localhost:(none)>select round(sum(data_length+index_length)/1024/1024/1024,2) as 'DBSIZE_GB' from information_schema.tables;

2. 数据库中各个库的大小合计：

root@localhost:(none)>select table_schema,round(sum(data_length+index_length)/1024/1024/1024,3) as 'SIZE_GB' from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') group by table_schema ;

3. 查看数据库中的 TOP 10 大表信息：

root@localhost:(none)>select table_schema,table_name,round((data_length+index_length)/1024/1024,2) as 'SIZE_MB',table_rows,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') order by 3 desc limit 10 ;

四、查看表和索引统计信息

1. 表统计信息：

root@localhost:(none)>select * from mysql.innodb_table_stats where database_name='ceshi' and table_name='employees';

2. 索引统计信息：

root@localhost:(none)>select * from mysql.innodb_index_stats where database_name='ceshi' and table_name='employees' and index_name='idx_name';

五、查询锁相关信息

1. 查询锁等待时持续间大于 20 秒的 SQL 信息：

root@localhost:(none)>SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
       NOW(),
       TRX_STARTED,
       TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME,
       USER,
       HOST,
       DB,
       TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN sys.innodb_lock_waits lw ON trx.trx_mysql_thread_id=lw.waiting_pid
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id!= connection_id()
  AND TO_SECONDS(now())-TO_SECONDS(trx_started) >= 20 ;

2. 查询 MySQL 锁等待表的详细信息：

root@localhost:(none)>select * from sys.innodb_lock_waits\G;

3. 查询长事务 SQL：

root@localhost:(none)>SELECT thr.processlist_id AS mysql_thread_id,
       concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
       Command,
       FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
       current_statement as `latest_statement`
  FROM performance_schema.events_transactions_current trx
  INNER JOIN performance_schema.threads thr USING (thread_id)
  LEFT JOIN sys.processlist p ON p.thd_id=thread_id
 WHERE thr.processlist_id IS NOT NULL 
   AND PROCESSLIST_USER IS NOT NULL 
   AND trx.state = 'ACTIVE'
 GROUP BY thread_id, timer_wait 
 ORDER BY TIMER_WAIT DESC LIMIT 10;

六、查看 DDL 执行进度

1. 先检查相关配置：
   • use performance_schema;
   • select * from setup_instruments where name like 'stage/innodb/alter%';
   • select * from setup_consumers where name like '%stages%';
   • 若上述查询结果为 NO，则进行配置：
     • update set_instrucments set enabled = 'YES' where name like 'stage/innodb/alter%';
     • update set_consumers set enabled = 'YES' where name like '%stages%';
2. 查询 DDL 执行的进度：

root@localhost:(none)>select stmt.sql_text,
       stage.event_name,
       concat(work_completed, '/', work_estimated) as progress,
       concat(round(100 * work_completed / work_estimated, 2), ' %') as processing_pct,
       sys.format_time(stage.timer_wait) as time_costs,
       concat(round((stage.timer_end - stmt.timer_start) / 1e12 *
                    (work_estimated - work_completed) / work_completed,
                    2),
              ' s') as remaining_seconds
  from performance_schema.events_stages_current     stage,
       performance_schema.events_statements_current stmt
 where stage.thread_id = stmt.thread_id
   and stage.nesting_event_id = stmt.event_id\G

七、查看 SQL 执行情况统计

1. 执行次数最多的 TOP 10 SQL：

root@localhost:(none)>SELECT QUERY_SAMPLE_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC LIMIT 10;

2. 平均响应时间最长的 TOP 10 SQL：

root@localhost:(none)>SELECT QUERY_SAMPLE_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC limit 10;

3. 排序次数最多的 TOP 10 SQL：

root@localhost:performance_schema>SELECT QUERY_SAMPLE_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY SUM_SORT_ROWS DESC LIMIT 10;

4. 扫描记录数最多的 TOP 10 SQL：

root@localhost:performance_schema>SELECT QUERY_SAMPLE_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 10;

5. 使用临时表最多的 TOP 10 SQL：

root@localhost:performance_schema>SELECT QUERY_SAMPLE_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY SUM_CREATED_TMP_TABLES DESC LIMIT 10;

八、查看索引使用情况

1. 查询从未使用过的索引：

root@localhost:ceshi>select * from schema_unused_indexes where object_schema not in ('performance_schema');

2. 查询冗余索引：

root@localhost:ceshi>select * from schema_redundant_indexes;

九、查看表结构相关信息

1. 查询数据库中没有主键的表：

root@localhost:ceshi>SELECT A.table_schema, A.table_name 
  FROM information_schema.tables AS A 
       LEFT JOIN (SELECT table_schema, table_name FROM information_schema.statistics WHERE index_name = 'PRIMARY') AS B 
        ON A.table_schema = B.table_schema AND A.table_name = B.table_name 
       WHERE A.table_schema NOT IN ('information_schema', 'mysql','performance_schema', 'sys') 
       AND A.table_type='BASE TABLE' 
       AND B.table_name IS NULL;

2. 查询非 InnoDB 表：

root@localhost:(none)>SELECT table_schema,table_name,engine FROM information_schema.tables where table_schema not in ('mysql','sys','information_schema','performance_schema') and engine!='InnoDB';

3. 查看某表的创建语句：

root@localhost:ceshi>SHOW CREATE TABLE employees;

十、主从复制相关操作

1. 查看从库状态信息（主从状态，延迟）：

root@localhost:ceshi>show slave status\G

2. 在主节点上查看 master 状态：

root@localhost:ceshi>SHOW MASTER STATUS\G

3. 在主节点上查看所有的 log 文件：

root@localhost:ceshi>SHOW MASTER LOGS;

4. 在线清理 mysql-bin3306.000003 之前的日志：

root@localhost:ceshi>PURGE BINARY LOGS TO 'mysql-bin.000003';

十一、用户管理相关命令

1. 创建 MySQL 用户，并设置密码：

root@localhost:mysql>CREATE USER 'zhh'@'%' IDENTIFIED BY '123123';

2. 修改用户密码：

root@localhost:mysql>ALTER USER 'zhh'@'%' IDENTIFIED  BY '123456';

3. 查看所有用户信息：

root@localhost:mysql>select user,host from mysql.user;

4. 删除用户：

root@localhost:mysql>DROP USER 'zhh'@'%';

5. 赋予zhh用户对所有数据库只有只读权限（SELECT）：

root@localhost:mysql>grant select on  *.* TO 'zhh'@'%';
root@localhost:mysql>FLUSH PRIVILEGES;

6. 查看 zhh的权限：

root@localhost:mysql>SHOW GRANTS FOR 'zhh'@'%'; 

十二、数据库管理相关命令

1. 创建数据库（如果不存在）：

root@localhost:mysql>CREATE DATABASE IF NOT EXISTS ceshi;

2. 查看所有数据库：

root@localhost:mysql>SHOW DATABASES;

3. 查看数据库的建库语句：

root@localhost:mysql>SHOW CREATE DATABASE dbname;

5. 查看所有数据库大小：

root@localhost:mysql>SELECT
	table_schema AS '数据库',
	SUM(table_rows) AS '记录数',
	SUM(TRUNCATE(data_length/1024/1024/1024, 2)) AS '数据容量(GB)',
	SUM(TRUNCATE(index_length/1024/1024/1024, 2)) AS '索引容量(GB)'
FROM
	information_schema.tables
GROUP BY
	table_schema
ORDER BY
	SUM(data_length) DESC,
	SUM(index_length) DESC;

6. 查看某个库中的表大小：

root@localhost:mysql>SELECT 
    TABLE_NAME, 
    CONCAT(ROUND(DATA_LENGTH / 1024 / 1024, 2), 'MB') AS data_size, 
    CONCAT(ROUND(INDEX_LENGTH / 1024 / 1024, 2), 'MB') AS index_size, 
    CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2), 'MB') AS total_size 
FROM 
    information_schema.TABLES 
WHERE 
    TABLE_SCHEMA = 'ceshi' 
    AND TABLE_NAME = 'employees';

十三、查看 MySQL 实例信息

1. 查询当前 MySQL 版本：

root@localhost:mysql>SELECT @@VERSION;

2. 查询定义的 packet 大小：

root@localhost:ceshi>select @@max_allowed_packet;

3. 查看当前 mysqld 的所有参数，包括默认值：

root@localhost:ceshi>SHOW VARIABLES;

4. 查询当前 MySQL 实例的端口：

root@localhost:ceshi>SHOW VARIABLES LIKE 'port';

5. 查询 MySQL 实例的 socket 文件路径：

root@localhost:ceshi>SHOW VARIABLES LIKE 'socket';

6. 查看实例的数据路径：

root@localhost:ceshi>SHOW VARIABLES LIKE 'datadir';

7. 查看是否开启了慢查询日志，以及慢日志的路径：

root@localhost:ceshi>SHOW VARIABLES LIKE 'slow_query_log%';

8. 查看从服务器是否开启慢查询日志：

root@localhost:ceshi>SHOW VARIABLES LIKE 'log_slow_slave_statements';

9. 查看慢查询时间：

root@localhost:ceshi>SHOW VARIABLES LIKE 'long_query_time';

10. 在线开启慢日志：

root@localhost:ceshi>SET GLOBAL slow_query_log=1;

11. 查看日志的输出格式：

root@localhost:ceshi>SHOW VARIABLES LIKE 'log_output';

12. 查看日志的时间信息：

root@localhost:ceshi>SHOW VARIABLES LIKE 'log_timestamps';

13. 查看是否开启 ‘将没有使用索引的 SQL 语句记录到慢查询日志中’ 的功能：

root@localhost:ceshi>SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

14. 查询 log 文件大小：

root@localhost:ceshi>SHOW VARIABLES LIKE 'innodb_log_file_size';

15. 查询页的大小：

root@localhost:ceshi>SHOW VARIABLES LIKE 'innodb_page_size';

16. 查看缓冲池的大小：

root@localhost:ceshi>SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

17. 在线调整 innodb_buffer_pool_size：

root@localhost:ceshi>SET GLOBAL innodb_buffer_pool_size=2*1024*1024*1024;

18. 启动时加载 dump 的文件：

root@localhost:ceshi>SHOW VARIABLES LIKE 'innodb_buffer_pool_load_at_startup';

19. 查看 dump 百分比：

root@localhost:ceshi>SHOW VARIABLES LIKE 'innodb_buffer_pool_dump_pct';

20. 查看隔离级别：

root@localhost:ceshi>SHOW VARIABLES LIKE 'transaction_isolation';

21. 是否将死锁信息打印到 err_log 中：

root@localhost:ceshi>SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

22. **master thread 每秒刷新 redo 的 buffer 到 logfile

root@localhost:ceshi>SHOW VARIABLES LIKE "%innodb_flush_log_at_timeout%";

23. 查看 binlog 的类型：

root@localhost:ceshi>SHOW VARIABLES LIKE 'binlog_format';

24. 查看 timeout 参数：

root@localhost:ceshi>SHOW VARIABLES LIKE "%timeout%"

25. 查看最大连接数：

root@localhost:ceshi>SHOW VARIABLES LIKE 'max_connections';

26. 查看 binlog 过期时间：

root@localhost:ceshi>show variables like 'expire_logs_days';

27. 修改 binlog 过期时间：

root@localhost:ceshi>set global expire_logs_days=7;

十四、其他常用命令

1. 显示表结构和列结构的命令：DESC dbname.tablename;
2. 显示正在执行的线程：SHOW PROCESSLIST;
3. 查看 buffer pool 的状态：SHOW ENGINE INNODB STATUS\G;
4. 查看表的索引情况：SHOW INDEX FROM tablename;
5. 查看锁的信息（在数据库 sys 下执行）：SELECT * FROM sys.innodb_lock_waits;
6. 查看指定 binlog 中的内容：show binlog events in 'mysql-bin.000008';
7. 刷新日志，并产生一个新的日志文件：flush binary logs;

欢迎关注公众号《小周的数据库进阶之路》，更多精彩知识和干货尽在其中。