首页 > 数据库 >MySQL 日常运维命令总结(一)

MySQL 日常运维命令总结(一)

时间：2024-11-19 13:17:23浏览次数：3  
标签：运维 SHOW mysql ceshi 日常 MySQL root localhost schema

一、连接数据库

使用 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;`
  1. 查看当前数据库中的活动会话（排除空闲 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;
  1. 数据库中各个库的大小合计：
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 ;
  1. 查看数据库中的 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';
  1. 索引统计信息：
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 ;
  1. 查询 MySQL 锁等待表的详细信息：
root@localhost:(none)>select * from sys.innodb_lock_waits\G;
  1. 查询长事务 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;
  1. 平均响应时间最长的 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;
  1. 排序次数最多的 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;
  1. 扫描记录数最多的 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;
  1. 使用临时表最多的 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');
  1. 查询冗余索引：
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;
  1. 查询非 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';
  1. 查看某表的创建语句：
root@localhost:ceshi>SHOW CREATE TABLE employees;

十、主从复制相关操作

  1. 查看从库状态信息（主从状态，延迟）：
root@localhost:ceshi>show slave status\G
  1. 在主节点上查看 master 状态：
root@localhost:ceshi>SHOW MASTER STATUS\G
  1. 在主节点上查看所有的 log 文件：
root@localhost:ceshi>SHOW MASTER LOGS;
  1. 在线清理 mysql-bin3306.000003 之前的日志：
root@localhost:ceshi>PURGE BINARY LOGS TO 'mysql-bin.000003';

十一、用户管理相关命令

  1. 创建 MySQL 用户，并设置密码：
root@localhost:mysql>CREATE USER 'zhh'@'%' IDENTIFIED BY '123123';
  1. 修改用户密码：
root@localhost:mysql>ALTER USER 'zhh'@'%' IDENTIFIED  BY '123456';
  1. 查看所有用户信息：
root@localhost:mysql>select user,host from mysql.user;
  1. 删除用户：
root@localhost:mysql>DROP USER 'zhh'@'%';
  1. 赋予zhh用户对所有数据库只有只读权限（SELECT）：
root@localhost:mysql>grant select on  *.* TO 'zhh'@'%';
root@localhost:mysql>FLUSH PRIVILEGES;
  1. 查看 zhh的权限：
root@localhost:mysql>SHOW GRANTS FOR 'zhh'@'%';

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

  1. 创建数据库（如果不存在）：
root@localhost:mysql>CREATE DATABASE IF NOT EXISTS ceshi;
  1. 查看所有数据库：
root@localhost:mysql>SHOW DATABASES;
  1. 查看数据库的建库语句：
root@localhost:mysql>SHOW CREATE DATABASE dbname;
  1. 查看所有数据库大小：
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;
  1. 查看某个库中的表大小：
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;
  1. 查询定义的 packet 大小：
root@localhost:ceshi>select @@max_allowed_packet;
  1. 查看当前 mysqld 的所有参数，包括默认值：
root@localhost:ceshi>SHOW VARIABLES;
  1. 查询当前 MySQL 实例的端口：
root@localhost:ceshi>SHOW VARIABLES LIKE 'port';
  1. 查询 MySQL 实例的 socket 文件路径：
root@localhost:ceshi>SHOW VARIABLES LIKE 'socket';
  1. 查看实例的数据路径：
root@localhost:ceshi>SHOW VARIABLES LIKE 'datadir';
  1. 查看是否开启了慢查询日志，以及慢日志的路径：
root@localhost:ceshi>SHOW VARIABLES LIKE 'slow_query_log%';
  1. 查看从服务器是否开启慢查询日志：
root@localhost:ceshi>SHOW VARIABLES LIKE 'log_slow_slave_statements';
  1. 查看慢查询时间：
root@localhost:ceshi>SHOW VARIABLES LIKE 'long_query_time';
  1. 在线开启慢日志：
root@localhost:ceshi>SET GLOBAL slow_query_log=1;
  1. 查看日志的输出格式：
root@localhost:ceshi>SHOW VARIABLES LIKE 'log_output';
  1. 查看日志的时间信息：
root@localhost:ceshi>SHOW VARIABLES LIKE 'log_timestamps';
  1. 查看是否开启 ‘将没有使用索引的 SQL 语句记录到慢查询日志中’ 的功能：
root@localhost:ceshi>SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
  1. 查询 log 文件大小：
root@localhost:ceshi>SHOW VARIABLES LIKE 'innodb_log_file_size';
  1. 查询页的大小：
root@localhost:ceshi>SHOW VARIABLES LIKE 'innodb_page_size';
  1. 查看缓冲池的大小：
root@localhost:ceshi>SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
  1. 在线调整 innodb_buffer_pool_size：
root@localhost:ceshi>SET GLOBAL innodb_buffer_pool_size=2*1024*1024*1024;
  1. 启动时加载 dump 的文件：
root@localhost:ceshi>SHOW VARIABLES LIKE 'innodb_buffer_pool_load_at_startup';
  1. 查看 dump 百分比：
root@localhost:ceshi>SHOW VARIABLES LIKE 'innodb_buffer_pool_dump_pct';
  1. 查看隔离级别：
root@localhost:ceshi>SHOW VARIABLES LIKE 'transaction_isolation';
  1. 是否将死锁信息打印到 err_log 中：
root@localhost:ceshi>SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';
  1. **master thread 每秒刷新 redo 的 buffer 到 logfile
root@localhost:ceshi>SHOW VARIABLES LIKE "%innodb_flush_log_at_timeout%";
  1. 查看 binlog 的类型：
root@localhost:ceshi>SHOW VARIABLES LIKE 'binlog_format';
  1. 查看 timeout 参数：
root@localhost:ceshi>SHOW VARIABLES LIKE "%timeout%"
  1. 查看最大连接数：
root@localhost:ceshi>SHOW VARIABLES LIKE 'max_connections';
  1. 查看 binlog 过期时间：
root@localhost:ceshi>show variables like 'expire_logs_days';
  1. 修改 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;

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

标签：运维,SHOW,mysql,ceshi,日常,MySQL,root,localhost,schema
From： https://blog.csdn.net/qq_36936192/article/details/143857448

相关文章

  • 软路由 + NAS 实现日常生活办公
    组网拓扑设备监控指标设备主要用途或部署服务1.OpenWrtWireGuardVPN组网从而实现内网穿透便于访问家庭局域网络；懂得都懂；运行一些docker小玩意。2.QNAPNASQuObjects对象存储服务器：Typora图床功能、Joplin笔记远程同步；PlexMediaServer：搭建个人的影音库；......
  • brew 安装的Mysql，查找my.cnf文件位置
    通过Homebrew安装的MySQL，默认情况下不会创建my.cnf文件，但你可以按照以下方式找到配置文件的路径或者创建一个自定义的my.cnf文件：查找默认配置文件位置1.查看MySQL默认使用的配置文件路径：你可以通过运行以下命令来查看MySQL会读取的配置文件路径顺序：mysql--help|......
  • MySQL基础知识(4)
    MySQL有哪些数据类型？数值类型整数TINYINT：1字节整数，范围-128到127（无符号0到255）SMALLINT：2字节整数，范围-32768到32767（无符号0到65535）MEDIUMINT：3字节整数，范围-8388608到8388607（无符号0到16777215）INT或INTEGER：4字节整数，范围-2147483648到2147483647（无符号0到4294967295）BIGINT：8......
  • MySQL基础知识（5）
    一、简介MySQL是一个流行的开源关系型数据库管理系统（RDBMS），它用于管理SQL（StructuredQueryLanguage）数据库。简单来说，MySQL帮助你存储、检索和管理数据。以下是一些关于MySQL的关键点：开源：MySQL是基于GPL（通用公共许可证）的开源软件，这意味着你可以免费使用它，并且可以查看和修改它的......
  • MySQL基础知识（6）
    MySQL遇到过死锁问题吗，你是如何解决的？排查死锁的步骤：查看死锁日志showengineinnodbstatus;找出死锁Sql；分析sql加锁情况；模拟死锁案发；分析死锁日志；分析死锁结果。数据库索引的原理，为什么要用B+树，为什么不用二叉树？可以从几个维度去看这个问题，查询是否够快，效率是否稳......
  • MySQL基础知识（7）
    数据库自增主键可能遇到什么问题？插入性能问题：在高并发的插入操作中，自增主键可能会成为性能瓶颈。因为每次插入新记录时，都需要获取一个新的自增ID，这个操作是串行的，无法并发执行。MySQL在生成自增ID时，需要确保ID的唯一性和递增性，这在高并发场景下可能会导致性能下降。主键耗尽问......
  • MySQL基础知识（8）
    MySQL中的日志系统包括哪些部分？它们各自的作用是什么？MySQL的日志系统主要包括以下几部分：错误日志（ErrorLog）：记录MySQL启动、运行或停止时的错误信息。查询日志（GeneralQueryLog）：记录MySQL服务器接收到的所有客户端连接和SQL查询信息。通常用于分析和审计。慢查询日志（SlowQuery......
  • 计算机毕业设计原创定制（免费送源码）Java+B/S+SSM+Web前端开发技术+IDEA+MySQL+Navicat
    摘 要信息化社会内需要与之针对性的信息获取途径，但是途径的扩展基本上为人们所努力的方向，由于站在的角度存在偏差，人们经常能够获得不同类型信息，这也是技术最为难以攻克的课题。针对有风小院等问题，对有风小院信息管理进行研究分析，然后开发设计出有风小院系统以解决问题。有......
  • 宝塔系统怎么安装apache和mysql
    在宝塔面板上安装Apache和MySQL相对简单，以下是具体步骤：登录宝塔面板：打开浏览器，输入宝塔面板的访问地址，通常是 http://你的服务器IP:8888。使用您的用户名和密码登录。进入软件商店：登录后，在首页会看到“软件商店”或“应用商店”的选项，点击进入。安装Apache：在软......
  • django mysqlclient orm
    要在Django中连接MySQL数据库，你需要确保你的环境中安装了mysqlclient这个Python库。以下是连接MySQL数据库的步骤：安装mysqlclient库。如果你使用的是pip，可以通过以下命令安装：pipinstallmysqlclient在你的Django项目的settings.py文件中，修改DATABASES设置以使用MySQL数据库。......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99