MYSQL常用命令（不定时更新）

她又在丛中笑

已于 2024-11-11 15:07:12 修改

阅读量140

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

于 2023-08-02 09:13:14 首次发布

本文链接：https://blog.youkuaiyun.com/m0_63164811/article/details/132054647

版权

MySQL 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一系列MySQL常用命令，包括查看最后一次死锁信息、解析binlog文件、指定查询排序规则等。通过这些命令，可以有效地管理和维护MySQL数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询mysql版本

执行指令

SELECT VERSION();

查看数据库最后一次死锁信息

执行指令

show engine innodb status

信息打印


=====================================
2023-08-02 09:11:38 0x7f85b78b7700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 17 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 19667 srv_active, 0 srv_shutdown, 1792016 srv_idle
srv_master_thread log flush and writes: 1811683
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 45468
OS WAIT ARRAY INFO: signal count 45132
RW-shared spins 0, rounds 91187, OS waits 44756
RW-excl spins 0, rounds 14291, OS waits 271
RW-sx spins 270, rounds 4974, OS waits 47
Spin rounds per wait: 91187.00 RW-shared, 14291.00 RW-excl, 18.42 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-08-01 17:48:44 0x7f85b7885700
*** (1) TRANSACTION:
TRANSACTION 2007585, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 78679, OS thread handle 140212270978816, query id 39909442 10.10.156.33 uni updating
update dms_device set configured_channel_cnt = (
        select count(*) from dms_device_video_channel where device_id = '34020000001180035001' and channel_state != 2
    ) where device_id = '34020000001180035001'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 170 page no 51 n bits 536 index device_id of table `jvs_tdms`.`dms_device` trx id 2007585 lock_mode X locks rec but not gap waiting
Record lock, heap no 469 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 20; hex 3334303230303030303031313830303335303031; asc 34020000001180035001;;
 1: len 8; hex 000000000003a8dd; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 2007584, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
10 lock struct(s), heap size 1136, 60 row lock(s), undo log entries 14
MySQL thread id 78787, OS thread handle 140212286543616, query id 39909481 10.10.156.33 uni update
INSERT INTO `dms_device_video_channel`(
     `channel_id`,
    
    
      `device_id`,
    
    
      `lookup_key`,
    
    
      `channel_name`,
    
    
      `channel_state`,
    
    
      `channel_ability` 
  )
  VALUES (
     7,
    
    
      '34020000001180035001',
    
    
      '34020000001180035001-7',
    
    
      '',
    
    
      0,
    
    
      'ptz,localstorage,talk' 
  )
  ON DUPLICATE KEY UPDATE
   channel_name = '',
    
    
      channel_state = 0,
    
    
      channel_ability = 'ptz,localstorage,talk'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 170 page no 51 n bits 536 index device_id of table `jvs_tdms`.`dms_device` trx id 2007584 lock_mode X
Record lock, heap no 469 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 20; hex 3334303230303030303031313830303335303031; asc 34020000001180035001;;
 1: len 8; hex 000000000003a8dd; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 171 page no 77 n bits 584 index device_id of table `jvs_tdms`.`dms_device_video_channel` trx id 2007584 lock_mode X waiting
Record lock, heap no 487 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3334303230303030303031313830303335303031; asc 34020000001180035001;;
 1: len 2; hex 0007; asc   ;;
 2: len 8; hex 80000000001fdfb1; asc         ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 2034630
Purge done for trx's n:o < 2018283 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421692223573632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223566336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223565424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223564512, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223563600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223569984, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223569072, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223571808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223568160, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223567248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223570896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223574544, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223580016, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223602816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223600992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223584576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223581840, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223578192, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223576368, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421692223572720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1634 OS file reads, 445227 OS file writes, 301690 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.47 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 158, seg size 160, 27 merges
merged operations:
 insert 69, delete mark 13, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1106363, node heap has 4 buffer(s)
Hash table size 1106363, node heap has 3 buffer(s)
Hash table size 1106363, node heap has 6 buffer(s)
Hash table size 1106363, node heap has 5 buffer(s)
Hash table size 1106363, node heap has 6 buffer(s)
Hash table size 1106363, node heap has 2 buffer(s)
Hash table size 1106363, node heap has 3 buffer(s)
Hash table size 1106363, node heap has 4 buffer(s)
0.94 hash searches/s, 11.76 non-hash searches/s
---
LOG
---
Log sequence number 215368442
Log flushed up to   215368442
Pages flushed up to 215368442
Last checkpoint at  215368433
0 pending log flushes, 0 pending chkp writes
148531 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4397727744
Dictionary memory allocated 1202385
Buffer pool size   262128
Free buffers       260350
Database pages     1745
Old database pages 0
Modified db pages  3
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1485, created 260, written 230608
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1745, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   65536
Free buffers       65034
Database pages     493
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 411, created 82, written 74029
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 493, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   65528
Free buffers       65231
Database pages     290
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 269, created 21, written 21159
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 290, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   65536
Free buffers       65021
Database pages     508
Old database pages 0
Modified db pages  3
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 420, created 88, written 64584
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 508, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   65528
Free buffers       65064
Database pages     454
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 385, created 69, written 70836
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 454, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=25616, Main thread ID=140212709340928, state: sleeping
Number of rows inserted 31191, updated 29923, deleted 10620, read 28849248
33.88 inserts/s, 0.00 updates/s, 0.00 deletes/s, 56.35 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

解析binlog二进制文件

执行命令

/usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows -v /optbinlog.001821 >> /opt/20231220.txt

在安装有mysql的linux服务器中，执行
mysqlbinlog所在路径 --base64-output=decode-rows -v binlog二进制文件路径 >> 解析完之后导出文件路径
–base64-output=decode-rows -v 用于解析二进制文件中的类base64字符串为sql脚本

指定查询排序规则

执行指令

select * from test order by FIELD(type,1,5,4,2,3), id desc

查询结果按照test表先按照type字段1,5,4,2,3顺序排列，然后id倒序排列

组连接

执行指令

SELECT name, GROUP_CONCAT(id ORDER BY FIELD(type,1,5,4,2,3)  SEPARATOR '-') 
from test GROUP BY name ORDER BY NULL;

查询test表，以name分组，查询name对应的id列表，并指定type字段以1,5,4,2,3排序，且使用-连接输出

更新关联表数据

执行指令

update test, lala set lala.name = test.name 
where test.id = lala.id;
或
UPDATE
    lala ulc
  INNER JOIN test uud
        ON uud.device_id = ulc.device_sn
SET
    ulc.tenant_id = uud.user_id
where ulc.tenant_id = '';

两表关联，以test表字段内容更新lala表字段数据

null值排序

执行指令

SELECT * FROM test ORDER BY  IF(ISNULL(name), 0, 1);

将name字段NULL值变更并排序

统计数据汇总

执行指令

SELECT coalesce(type, '总量') type, SUM(total) as total 
FROM test GROUP BY type WITH ROLLUP;

将各个type的total字段进行统计并进行汇总，获取总量

查询近指定天数数据

执行指令

# 当天数据
select count(1) from alarm_order  where DATE_SUB(CURDATE(), INTERVAL 0 DAY) <= add_time
# 7天内数据
select count(1) from alarm_order  where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= add_time

使用DATE_SUB函数查询近日数据

查询数据并输出序号

执行指令

set @i := 0;
SELECT
	(@i:=@i+1) as num ,id,create_time
FROM
	`test`
order by create_time desc

以 create_time 倒序排列查询数据，并添加序号列

传参查询

这种写法优点在于，传其他参数的时候，只需要修改开始的参数对应值即可，不用在sql里面找参数进行修改。

执行指令

-- 要赋权的权限起始id
SET @permission_start_id = 4100;
-- 要赋权的权限结束id
SET @permission_end_id = 4118;
SELECT
	id,
	permission_type 
FROM
	upms_operate_permission 
WHERE
	id BETWEEN @permission_start_id AND @permission_end_id