MySQL DBA 日常运维命令总结:
MySQL DBA在日常运维中会使用到一系列的命令来管理数据库,确保数据库的稳定性和性能。以下是一些常用的MySQL DBA运维命令的总结归纳:
1. 连接MySQL数据库
mysql -uroot -p'password' mysql -uroot -p'password' -h 127.0.0.1 -P 3306 mysql -uroot -p'password' -S /path/to/mysql.sock
复制
2. 查看当前数据库中的会话状态
show processlist;
复制
3. 查看当前数据库中的活动会话(排除掉空闲Sleep状态的会话)
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等查询条件来过滤。
复制
4. 查看数据库的总大小
--数据库总大小 select round(sum(data_length+index_length)/1024/1024/1024,2) as 'DBSIZE_GB' from information_schema.tables;
复制
5. 查看数据库中各个库的大小合计
--数据库大小信息: 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 ;
复制
6. 查看数据库中的TOP 30大表信息
--Top 30大表信息: 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 ;
复制
7. 查看表和索引的统计信息:
--表统计信息: 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';
复制
8. 查询锁等待时持续间大于20秒的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 ;
复制
9. 查询MySQL锁等待表的详细信息
-- sys库锁等待表: select * from sys.innodb_lock_waits\G
复制
10. 查询长事务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;
复制
11. 查看当前DDL执行的进度
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%'; -- 查询DDL执行的进度: 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
复制
12. 执行次数最多的TOP 10 SQL
--执行次数前10 SQL SELECT QUERY_SAMPLE_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC LIMIT 10;
复制
13. 平均响应时间最长的TOP 10 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;
复制
14. 排序次数最多的TOP 10 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;
复制
15. 扫描记录数最多的 TOP 10 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;
复制
16. 使用临时表最多的TOP 10 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;
复制
17. 查询从未使用过的索引
--从未使用过的索引:未使用索引建议直接删除,多余索引如不使用会影响增删改性能,且索引占用磁盘空间。 select * from schema_unused_indexes where object_schema not in ('performance_schema');
复制
18. 查询冗余索引
--冗余索引建议删除 select * from schema_redundant_indexes;
复制
19. 查询数据库中没有主键的表
--查询所有无主键表: 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;
复制
20. 查询非InnoDB表
--非innodb表 SELECT table_schema,table_name,engine FROM information_schema.tables where table_schema not in ('mysql','sys','information_schema','performance_schema') and engine!='InnoDB';
复制
21. 查询从库状态信息(主从状态,延迟)
--主从状态: (Slave_IO_Running和Slave_SQL_Running 都为YES 且Seconds_Behind_Master 为0) show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.120.184.82 Master_User: repuser Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.001026 Read_Master_Log_Pos: 182832 Relay_Log_File: mysql-relay-bin.002069 Relay_Log_Pos: 183005 Relay_Master_Log_File: mysql-bin.001026 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 182832 Relay_Log_Space: 183299 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 330682 Master_UUID: d2ba61a0-5b46-11ee-b627-005056b51543 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: d2ba61a0-5b46-11ee-b627-005056b51543:12832492-14068377 Executed_Gtid_Set: a5bf5226-5b48-11ee-ae63-005056b53ab2:1, d2ba61a0-5b46-11ee-b627-005056b51543:1-14068377 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
复制
22. 查看慢日志信息:是否开启及慢日志的位置
--确认慢日志是否开启:slow_query_log为ON 及慢日志位置:/data/mysql8036/3306/logs/slow.log root@localhost:sys 04:14:14 >show global variables like 'slow%'; +---------------------+------------------------------------+ | Variable_name | Value | +---------------------+------------------------------------+ | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /data/mysql8036/3306/logs/slow.log | +---------------------+------------------------------------+ 3 rows in set (0.00 sec) --确认慢日志记录的时间阈值: root@localhost:sys 04:14:16 >show global variables like 'long%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ 1 row in set (0.00 sec)
复制
总结
以上这些命令是MySQL DBA在日常工作中经常使用的,可以帮助DBA监控数据库状态、管理用户权限、优化查询性能、查找关键信息等。在执行这些操作时,DBA需要根据实际情况和数据库的配置来做适当调整。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。
文章被以下合辑收录
评论
目录