屏幕有输出: mysqld_safe Logging to '/data/cm3307/mysqllog/cm3307_error.log'.mysqld_safe日志记录到 /data/cm3307/mysqllog/cm3307_error.log
案例处理
那就先检查下日志文件,发现文件是空的,权限也正确都是mysql
1 2 3 4 5 6 7 8 9
[root@liups data]# cat /data/cm3307/mysqllog/cm3307_error.log [root@liups data]# ls -l /data/cm3307/mysqllog/cm3307_error.log -rw-r--r-- 1 mysql mysql 0 Mar 2 09:22 /data/cm3307/mysqllog/cm3307_error.log [root@liups data]# ls -al ./cm3307 total 16 drwxr-xr-x 4 mysql mysql 4096 Mar 2 09:23 . drwxr-xr-x 5 root root 4096 Mar 2 09:22 .. drwxr-xr-x 2 mysql mysql 4096 Mar 2 09:23 mysqldata drwxr-xr-x 2 mysql mysql 4096 Mar 2 09:22 mysqllog
在这里我又特意启动的时候指定了日志文件的位置进行启动,去掉后台启动,这样启动日志文件仍然为空。
1 2 3 4 5 6 7 8 9 10 11 12 13 14
[root@liups cm3307]# /usr/local/cm3307/bin/mysqld_safe --defaults-file=/etc/cm3307.cnf --user=mysql --log-error=/tmp/cnwww.err Logging to '/tmp/cnwww.err'. 2024-02-29T07:37:21.201831Z mysqld_safe Starting mysqld daemon with databases from /data/cm3307/mysqldata 2024-02-29T07:37:21.401062Z mysqld_safe mysqld from pid file /data/cm3307/mysqldata/cm3307.pid ended [root@liups cm3307]# touch /tmp/cnwww.err [root@liups cm3307]# chown mysql:mysql /tmp/cnwww.err [root@liups cm3307]# chmod 777 /tmp/cnwww.err [root@liups cm3307]# /usr/local/cm3307/bin/mysqld_safe --defaults-file=/etc/cm3307.cnf --user=mysql --log-error=/tmp/cnwww.err 2024-02-29T07:38:07.319574Z mysqld_safe Logging to '/tmp/cnwww.err'. 2024-02-29T07:38:07.342685Z mysqld_safe Starting mysqld daemon with databases from /data/cm3307/mysqldata 2024-02-29T07:38:07.539972Z mysqld_safe mysqld from pid file /data/cm3307/mysqldata/cm3307.pid ended [root@liups cm3307]# cat /tmp/cnwww.err [root@liups cm3307]# ll /tmp/cnwww.err -rwxrwxrwx 1 mysql mysql 0 Feb 29 15:37 /tmp/cnwww.err
[root@liups:/root]# /data/cm3307/mysql/bin/mysqld --defaults-file=/etc/cm3307.cnf --user=mysql 2024-03-01T10:10:26.689364+08:00 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path. 2024-03-01T10:10:26.689450+08:00 0 [Note] /data/cm3307/mysql/bin/mysqld (mysqld 5.7.36-log) starting as process 12867 ... 2024-03-01T10:10:26.691139+08:00 0 [ERROR] COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'latin1' 2024-03-01T10:10:26.691161+08:00 0 [ERROR] Aborting
[root@liups:/root]# vi /data/cm3307/mysql/bin/mysqld_safe [root@liups:/root]# /data/cm3307/mysql/bin/mysqld_safe --defaults-file=/etc/cm3307.cnf --user=mysql 57 2024-03-02T13:10:59.571550Z mysqld_safe Logging to '/data/cm3307/mysqllog/cm3307_error.log'. nohup: ignoring input and appending output to ‘nohup.out’ 2024-03-02T13:10:59.591554Z mysqld_safe Starting mysqld daemon with databases from /data/cm3307/mysqldata nohup: appending output to ‘nohup.out’ 2024-03-02T13:10:59.774138Z mysqld_safe mysqld from pid file /data/cm3307/mysqldata/cm3307.pid ended
很明显多了 nohup: ignoring input and appending output to ‘nohup.out’ 也就是 nohup 启动 mysqld的时候日志默认是写到nohup.out,我检查当前目录下的nohup.out
1 2 3 4 5 6 7
2024-03-02T21:10:59.759723+08:00 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path. 2024-03-02T21:10:59.759809+08:00 0 [Note] /data1/canarymysql3307/mysql/bin/mysqld (mysqld 5.7.36-log) starting as process 24043 ... 2024-03-02T21:10:59.761453+08:00 0 [ERROR] COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'latin1' 2024-03-02T21:10:59.761481+08:00 0 [ERROR] Aborting
set lines 200 col event for a55 SELECT inst_id,EVENT, SUM(DECODE(WAIT_TIME, 0, 0, 1)) "Prev", SUM(DECODE(WAIT_TIME, 0, 1, 0)) "Curr", COUNT(*) "Tot" , sum(SECONDS_IN_WAIT) SECONDS_IN_WAIT FROM GV$SESSION_WAIT WHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client','gcs remote message') AND event NOTLIKE'%idle%' AND event NOTLIKE'%Idle%' AND event NOTLIKE'%Streams AQ%' GROUPBY inst_id,EVENT ORDERBY1,5desc;
根据等待事件查会话
1 2 3 4 5 6 7 8
set line 199 col username format a14 col event format a35 col module format a20 col spid format a8 col machine format a15 col B_SESS for a10 SELECT/*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE event='&event_name'AND s.paddr = p.addr orderby6;
根据用户查会话
1 2 3 4 5 6 7 8
set line 199 col username format a14 col event format a35 col module format a20 col spid format a8 col machine format a15
SELECT/*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE s.username='&user_name'AND s.paddr = p.addr orderby6
根据SQL_ID查会话
1 2 3 4 5 6 7
set line 199 col username format a14 col event format a35 col module format a20 col spid format a8 col machine format a15 SELECT/*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE s.sql_id='&sql_id'AND s.paddr = p.addr orderby6
根据会话ID查会话详情
1 2 3 4 5 6 7
set line 199 col username format a14 col event format a35 col module format a20 col spid format a8 col machine format a15 SELECT s.sid, s.serial#, spid, event, sql_id, PREV_SQL_ID, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, module,blocking_session b_sess,logon_time FROM v$session s, v$process p WHERE sid ='&sid'AND s.paddr = p.addr;
查询阻塞会话
查询会话的对象信息
1 2 3
selectcount(*),blocking_session from v$session where blocking_session isnotnullgroupby blocking_session; col OBJECT_NAME for a30 select owner,object_name,subobject_name,object_type from dba_objects where object_id=&oid;
1 2 3 4 5
set line 199 col event format a35
## 杀某个SID会话 SELECT/*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sid='&sid'AND s.paddr = p.addr orderby1;