MySQL DBA在日常运维中会使用到一系列的命令来管理数据库和SQL运维查询。
总归纳如下：
1. **登录和用户管理**：
- `mysql -uroot`：在Windows系统下，打开cmd并用root用户登录MySQL。
- `mysql -uroot -p`：如果root用户设置了密码，需要加上-p选项进行登录。
2. **查看数据库信息**：
- `show databases;`：列出所有数据库。
- `use mysql;`：选择mysql数据库（用于查看用户、权限等）。
3. **表的操作**：
- `show tables;`：列出当前数据库下的所有表。
- `desc table_name;`：查看表的结构。
4. **数据的导入和导出**：
- `mysqldump -u username -p db_name > db_backup.sql`：导出数据库到文件。
- `mysql -u username -p db_name < db_backup.sql`：从文件导入数据库数据。
5. **备份与恢复**：
- `--single-transaction`、`--master-data=2`、`--flush-logs`：优化锁和得到一致性备份的命令组合。
6. **远程连接**：
- `mysql -h host -P port -u user -p`：远程连接到MySQL服务器。
7. **性能监控和优化**：
- `SHOW PROCESSLIST;`：查看当前MySQL服务器上的进程。
- `EXPLAIN query;`：分析查询语句的执行计划。
8. **安全管理**：
- `GRANT privileges ON db.table TO 'user'@'host';`：给用户授权。
- `REVOKE privileges ON db.table FROM 'user'@'host';`：撤销用户的权限。
9. **系统变量和状态**：
- `SHOW VARIABLES;`：查看MySQL服务器的系统变量。
- `SHOW STATUS;`：查看服务器状态信息。
10. **日志管理**：
- `SHOW BINARY LOGS;`：查看二进制日志文件列表。
- `SHOW ERROR LOG;`：查看错误日志。
11. **服务管理**：
- `service mysql start`：启动MySQL服务。
- `service mysql stop`：停止MySQL服务。
12. 查看当前数据库中的活动会话（排除掉空闲Sleep状态的会话）
```sql
SELECT * FROM information_schema.processlist WHERE command <> 'Sleep';
--8.0以后版本建议使用performance_schema:
SELECT * FROM performance_schema.processlist WHERE command <> 'Sleep';
--排除掉自己的会话连接
SELECT * FROM information_schema.processlist WHERE command <> 'Sleep' AND id <> CONNECTION_ID();
SELECT * FROM performance_schema.processlist WHERE command <> 'Sleep' AND id <> CONNECTION_ID();
--也可以通过其他条件来排查掉自己不想要的会话信息：如user in 或者 db in ,host等查询条件来过滤。
```
13. 查看数据库的总大小
```sql
SELECT ROUND(SUM(data_length+index_length)/1024/1024/1024,2) AS 'DBSIZE_GB' FROM information_schema.tables;
```
14. 查看数据库中各个库的大小合计
```sql
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;
```
15. 查看数据库中的TOP 30大表信息
```sql
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 30;
```
16. 查看表和索引的统计信息：
```sql
--表统计信息：
SELECT * FROM mysql.innodb_table_stats WHERE database_name='db_name' AND table_name='table_name';
--索引统计信息：
SELECT * FROM mysql.innodb_index_stats WHERE database_name='' AND table_name='' AND index_name='idx_name';
```
17. 查询锁等待时持续间大于20秒的SQL信息
```sql
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;
```
18. 查询MySQL锁等待表的详细信息
```sql
-- sys库锁等待表：
SELECT * FROM sys.innodb_lock_waits\G
```
19. 查询长事务SQL
```sql
--长事务（包含未关闭的事务）
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;
```
20. 平均响应时间最长的TOP 10 SQL
```sql
--平均响应时间TOP 10 SQL
SELECT QUERY_SAMPLE_TEXT, AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC limit 10;
```
21. 排序次数最多的TOP 10 SQL
```sql
--排序此时最多TOP 10 SQL
SELECT QUERY_SAMPLE_TEXT, SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY SUM_SORT_ROWS DESC LIMIT 10;
```
22. 扫描记录数最多的 TOP 10 SQL
```sql
--扫描行最多的 TOP 10 SQL
SELECT QUERY_SAMPLE_TEXT, SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 10;
```
23. 使用临时表最多的TOP 10 SQL
```sql
--使用临时表最多的TOP 10 SQL
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;
```
24. 查询从未使用过的索引
```sql
--从未使用过的索引：未使用索引建议直接删除，多余索引如不使用会影响增删改性能，且索引占用磁盘空间。
SELECT * FROM schema_unused_indexes WHERE object_schema NOT IN ('performance_schema');
```
25. 查询数据库中没有主键的表
```sql
--查询所有无主键表：
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;
```
