MySQL常用维护命令

原创飞天 2024-07-06

1810

登录数据库

# 本地登录
mysql -u用户名 -p'密码' -P 端口号
mysql -u用户名 -p'密码' -P 端口号 -S '/路径/socket文件名'
# 远程登录
mysql -h ip地址 -u用户名 -p'密码' -P 端口号
复制

查看数据库版本

select version();
复制

查看数据文件的位置

show global variables like 'datadir';
或者
grep -i datadir <配置文件>
示例：grep -i datadir /etc/my.cnf 
复制

查看错误日志文件的位置

show global variables like 'log_error';
或者
grep -i log_error <配置文件>
示例：grep -i log-error /etc/my.cnf 
复制

查看慢查询日志及慢查询时间

show global variables like '%slow_query%';
show global variables like 'long_query_time';
复制

查看建表语句

 show create table table_name;
复制

查看表状态

show table status like 'table_name'\G;
复制

查看非innodb的表

select TABLE_SCHEMA,TABLE_NAME from information_schema.tables where ENGINE!='innodb'and TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys');
复制

查看没有主键的表

select table_schema,table_name from information_schema.tables where (table_schema,table_name) not in( select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI') and table_schema not in ( 'sys','mysql','information_schema','performance_schema' );
复制

查看数据库中的分区

SELECT table_name,MAX(partition_name),MIN(partition_name) FROM information_schema.`PARTITIONS` WHERE partition_name IS NOT NULL GROUP BY table_name;
复制

查找数据库中的外键

select *
from information_schema.KEY_COLUMN_USAGE
where constraint_schema =’’ and REFERENCED_TABLE_SCHEMA is not null\G;

查看数据库大小

select 
table_schema as '数据库',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)',
sum(truncate(data_length/1024/1024, 2)+truncate(index_length/1024/1024, 2)) as '总容量(MB)' 
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

另一种：用内置函数sys.FORMAT_BYTES实现，该函数会根据大小，会自动显示为GB、MB或者KB。
select 
table_schema as '数据库',
FORMAT_BYTES(sum(data_length)) as '数据容量',
FORMAT_BYTES(sum(index_length)) as '索引容量',
FORMAT_BYTES(sum(data_length+index_length)) as '总容量' 
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;
复制

查看表大小

# 查看指定的数据库中每个表的记录数、占用空间大小
SELECT 
table_name AS '表名',
SUM(table_rows) AS '记录数',
SUM(TRUNCATE(data_length/1024/1024, 2)) AS '数据容量(MB)',
SUM(TRUNCATE(index_length/1024/1024, 2)) AS '索引容量(MB)',
SUM(TRUNCATE((data_length+index_length)/1024/1024, 2)) AS '总容量(MB)'
FROM information_schema.tables
WHERE table_schema='database_name'  #database_name是数据库名，根据实际情况替换
GROUP BY table_name
order by sum(data_length) desc, sum(index_length) desc;
复制

查看表属于哪个数据库

SELECT table_schema, table_name
  FROM information_schema.tables
 WHERE table_name ='table_name'; #table_name根据实际情况替换
复制

查看表上的索引

show index from table_name;
复制

查看Innodb引擎状态

show engine innodb status\G;
复制

查看用户的权限

show grants for 'user_name'@'ip';
复制

修改root密码

# 第一种情况：忘记root密码
1、配置文件中添加skip-grant-tables
2、重启mysql服务
systemctl restart mysqld
3、执行sql语句
flush privileges;
alter user 'root'@'localhost' identified by 'password';
4、配置文件去掉skip-grant-tables
5、重启mysql服务
systemctl restart mysqld

# 第二种情况：有root密码
执行sql语句：
1、alter user 'root'@'localhost' identified by 'password';
2、flush privileges;
复制

解析binlog文件

# 解析指定时间范围的binlog
mysqlbinlog --no-defaults -vv --base64-output=decode-rows --start-datetime='2024-07-05 11:00:00' --stop-datetime='2024-07-05 23:00:00'  binlog.000006 binlog.000007
# 解析指定 GTID 的事务（查看事务做了什么）
mysqlbinlog --no-defaults -vv --base64-output=decode-rows --include-gtids='de9ed572-394c-11ef-97f3-0050563d7276:3' binlog.000007
复制

修改binlog文件保留天数

# 8.0以下版本使用expire_logs_days参数设置binlog文件保留天数，默认为0，表示永不过期。
1、查看binlog文件保留天数
show variables like 'expire_logs_days';
2、修改为保留8天：
set global expire_logs_days=8; 

#8.0以上版本通过设置全局参数binlog_expire_logs_seconds修改binlog保留时间，默认是2592000秒，即30天
1、查看binlog文件保留时间
show variables like '%binlog_expire_logs_seconds%';
2、修改为保留3天：
set global binlog_expire_logs_seconds=259200; 
复制

手工清理binlog文件

PURGE { BINARY | MASTER } LOGS {
 TO 'log_name'
 | BEFORE datetime_expr
}
示例：
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2024-07-02 22:59:26';
复制

查看当前运行的所有线程（进程）

SHOW PROCESSLIST; 
这个命令对于监控和诊断数据库性能问题非常有用，因为它可以显示哪些查询正在执行，以及它们已经运行了多长时间。

执行 SHOW PROCESSLIST; 命令后可以看到的一些列的信息：
Id：线程的唯一标识符。
User：执行线程的MySQL用户。
Host：用户连接的主机名和端口号。
db：当前线程正在使用的数据库。
Command：线程正在执行的命令类型。
Time：该命令已经执行了多少秒。
State：线程当前的状态，例如 "Sleeping"、"Query" 等。
Info：执行的SQL语句（可能会被截断显示）。
复制

查看活动的线程（进程）

# 8.0以下
select * from information_schema.processlist where command<>'Sleep'; 
select * from information_schema.processlist where command <> 'Sleep' and id <> connection_id();

# 8.0以上建议用performance_schema.processlist替换information_schema.processlist，如下：
select * from performance_schema.processlist where command <> 'Sleep';
select * from performance_schema.processlist where command <> 'Sleep' and id <> connection_id();

复制

kill线程（进程）

第一种方法：
1、先执行show processlist命令
2、kill <show processlist命令>.id;
第二种方法
1、先执行：
8.0以下执行：
select * from information_schema.processlist ;
8.0及以上执行：
select * from performance_schema.processlist ;
2、kill线程
kill information_schema.processlist.id;
或者
kill performance_schema.processlist.id; 
复制

查看长时间运行的事务

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;

如果一个事务长时间未提交(处于ACTIVE状态)，虽然从events_transactions_current表中可以查询到未提交事务事件信息，但是并不能直观地看到事务是什么时间点开始的，可以借助于information_schema.innodb_trx表来进行辅助判断。
SELECT * FROM information_schema.INNODB_TRX;

复制

查看锁等待情况

# 8.0以下
select * from information_schema.innodb_locks\G;
select * from information_schema.innodb_lock_waits\G;
select * from sys.innodb_lock_waits\G;

# 8.0及以上
select * from performance_schema.data_locks\G;
select * from performance_schema.data_lock_waits\G;
select * from sys.innodb_lock_waits\G;
复制

查看元数据锁

select 
l.object_schema 数据库名,
l.object_type 对象类型, 
l.object_name 对象名称,
l.lock_type 锁类型,
l.lock_duration 持续类型,
l.lock_status 锁状态,
l.owner_thread_id 线程ID,
t.processlist_id 会话ID,
s.sql_text
from performance_schema.metadata_locks l
join performance_schema.threads t on t.thread_id=l.owner_thread_id
join performance_schema.events_statements_current s on s.thread_id=l.owner_thread_id
where l.object_schema='database_name'and l.object_name='table_name'; #database_name和table_name要根据实际情况替换
复制

查看执行次数最多的前10条SQL

SELECT QUERY_SAMPLE_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest ORDER BY COUNT_STAR DESC limit 10\G;

复制

查看平均响应时间最多的前10条SQL

SELECT QUERY_SAMPLE_TEXT,AVG_TIMER_WAIT FROM performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC limit 10;
# AVG_TIMER_WAIT列：表示执行这些SQL语句的平均等待时间（以微秒为单位）。
复制

查看排序行数最多的前10条SQL

SELECT QUERY_SAMPLE_TEXT,SUM_SORT_ROWS FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_SORT_ROWS DESC LIMIT 10;
# SUM_SORT_ROWS列：表示执行SQL语句时涉及的排序行数的总和。可以反映SQL语句执行过程中排序操作的规模。
复制

查看扫描行数最多的前10条SQL

select query_sample_text,sum_rows_examined from performance_schema.events_statements_summary_by_digest ORDER BY sum_rows_examined desc  LIMIT 10;
# sum_rows_examined：表示执行SQL语句时扫描的总行数。
复制

查看使用临时表最多的前10条SQL

SELECT QUERY_SAMPLE_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_CREATED_TMP_TABLES DESC LIMIT 10;
# SUM_CREATED_TMP_TABLES：表示执行SQL语句时创建的临时表的总数（包括内存临时表和磁盘临时表）
# SUM_CREATED_TMP_DISK_TABLES：表示执行SQL语句时创建的磁盘临时表的数量。

复制

物理IO最多的前10个文件

select file_name,event_name,sum_number_of_bytes_read,sum_number_of_bytes_write from performance_schema.file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC  LIMIT 10;
# sum_number_of_bytes_read：表示对文件进行读取操作时读取的字节总数。
# sum_number_of_bytes_write：表示对文件进行写入操作时写入的字节总数。
复制

逻辑IO等待时间最多的前10个表

select object_name,count_read,count_write,count_fetch,sum_timer_wait FROM performance_schema.table_io_waits_summary_by_table ORDER BY sum_timer_wait desc  LIMIT 10;
# object_name：表的名称。
count_read：表示该表上读取操作的计数。
count_write：表示该表上写入操作的计数。
count_fetch：表示从该表中fetch数据的计数（通常与读取操作类似，但可能包括缓存中的获取）。
sum_timer_wait：表示在该表上所有I/O操作的总等待时间（以微秒为单位）。
复制

查看从未使用过的索引

 select * from sys.schema_unused_indexes where object_schema not in ('performance_schema');

# 删除从未使用过的索引
ALTER TABLE table_name DROP INDEX index_name;
复制

查看冗余索引

select * from sys.schema_redundant_indexes;

# 删除冗余索引
ALTER TABLE table_name DROP INDEX index_name;
复制

查看从库的状态

show slave status\G;
show replica status\G;
复制

查看mgr状态

show group_replication status;
select * from performance_schema.replication_group_members;

复制

设置从库只读

set global read_only = 1;
set global super_read_only = 1;
复制

设置表名、列名大小写不敏感

#在my.cnf的[mysqld]设置
lower_case_table_names=1
复制

墨力计划 mysql

最后修改时间：2024-11-12 21:44:02

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

2人已赞赏

文章被以下合辑收录

MySql数据库（共24篇）

MySql数据库

神机军师

19天前

设置表名、列名大小写不敏感补充说明：在my.cnf的[mysqld]设置 lower_case_table_names=1 对MySQL 5.7以前版本可以任意设置后，重启均有效。 MySQL 8.0以后版本，如果没有在第一次启动数据库前设置lower_case_table_names=1，后续再设置是无效的，只能重装数据库。

19天前