MySQL常用维护命令 转发:https://www.modb.pro/db/1808708769895948288

登录数据库
# 本地登录
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_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表来进行辅助判断。
查看锁等待情况
# 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;

设置从库只读
set global read_only = 1;
set global super_read_only = 1;