企业官网建设流程全解析

查询mysql版本

执行指令

SELECTVERSION();

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

执行指令

showengineinnodbstatus

信息打印

===================================== 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

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

指定查询排序规则

执行指令

select*fromtestorderbyFIELD(type,1,5,4,2,3),iddesc

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

组连接

执行指令

SELECTname,GROUP_CONCAT(idORDERBYFIELD(type,1,5,4,2,3)SEPARATOR'-')fromtestGROUPBYnameORDERBYNULL;

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

更新关联表数据

执行指令

updatetest,lalasetlala.name=test.namewheretest.id=lala.id;或UPDATElala ulcINNERJOINtest uudONuud.device_id=ulc.device_snSETulc.tenant_id=uud.user_idwhereulc.tenant_id='';

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

null值排序

执行指令

SELECT*FROMtestORDERBYIF(ISNULL(name),0,1);

将name字段NULL值变更并排序

统计数据汇总

执行指令

SELECTcoalesce(type,'总量')type,SUM(total)astotalFROMtestGROUPBYtypeWITH ROLLUP;

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

设置某表中所有列的排序规则

执行指令

ALTERTABLEtestCONVERTTOCHARACTERSETutf8COLLATEutf8_unicode_ci;

将test表中所有字段设置成 utf8_unicode_ci 排序规则

查询近指定天数数据

执行指令

# 当天数据selectcount(1)fromalarm_orderwhereDATE_SUB(CURDATE(),INTERVAL0DAY)<=add_time# 7天内数据selectcount(1)fromalarm_orderwhereDATE_SUB(CURDATE(),INTERVAL7DAY)<=add_time

使用DATE_SUB函数查询近日数据

查询数据并输出序号

执行指令

set@i:=0;SELECT(@i:=@i+1)asnum,id,create_timeFROM`test`orderbycreate_timedesc

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

传参查询

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

执行指令

-- 要赋权的权限起始idSET@permission_start_id=4100;-- 要赋权的权限结束idSET@permission_end_id=4118;SELECTid,permission_typeFROMupms_operate_permissionWHEREidBETWEEN@permission_start_idAND@permission_end_id

传参permission_start_id 、permission_end_id ，并将参数作为条件进行筛选。

创建存储过程

可以将一大串sql脚本压缩成一个方法名，方便调用并可忽略sql脚本中的内部复杂逻辑。
示例中的分号要格外注意

创建指令

CREATEPROCEDURESimpleGreeting(INuser_nameVARCHAR(100))BEGINSELECTCONCAT('Hello, ',user_name,'!')ASgreeting;END

执行指令

CALLSimpleGreeting('John');

创建事件

开启能力

-- 若为OFF,则需开启SHOWVARIABLESLIKE'event_scheduler';-- 开启SETGLOBALevent_scheduler=1;SETGLOBALevent_scheduler=ON;

创建事件

-- 事件名称及权限设置CREATEDEFINER=`log_usr`@`%`EVENT my_daily_update-- 每五分钟执行一次ONSCHEDULE EVERY5MINUTE-- 事件的首次执行时间STARTS'2025-09-11 11:22:00'DOBEGIN-- 执行sql脚本CALLsync_status();END;

展示事件

SHOWEVENTS;

SELECT*FROMmysql.event;

删除事件

-- 根据事件名删除事件DROPEVENTIFEXISTSmy_daily_update;