bedbmsguru

-- When will a SQL login password expire?  SELECT SL.name AS LoginName        ,LOGINPROPERTY (SL.name, 'PasswordLastSetTime') AS PasswordLastSetTime        ,LOGINPROPERTY (SL.name, 'DaysUntilExpiration') AS DaysUntilExpiration        ,DATEADD(dd, CONVERT(int, LOGINPROPERTY (SL.name, 'DaysUntilExpiration'))                   , CONVERT(datetime, LOGINPROPERTY (SL.name, 'PasswordLastSetTime'))) AS PasswordExpiration        ,SL.is_policy_checked AS IsPolicyChecked        ,LOGINPROPERTY (SL.name, 'IsExpired') AS IsExpired        ,LOGINPROPERTY (SL.name, 'IsMustChange') AS IsMustChange        ,LOGINPROPERTY (SL.name, 'IsLocked') AS IsLocked        ,LOGINPROPERTY (SL.name, 'LockoutTime') AS LockoutTime        ,LOGINPROPERTY (SL.name, 'BadPasswordCount') AS BadPasswordCount        ,LOGINPROPERTY (SL.name, 'BadPasswordTime') AS BadPasswordTime        ,LOGINPROPERTY (SL.name, 'HistoryLength') AS HistoryLength  FROM sys.sql_logins AS SL  WHERE is_expiration_checked = 1  ORDER BY LOGINPROPERTY (SL.name, 'PasswordLastSetTime') DESC

http://gallery.technet.microsoft.com/scriptcenter/When-will-a-SQL-login-d6fbb6df

SELECT CAST(event_Data AS xml) AS event_data FROM sys.fn_xe_file_target_read_file('R:\SqlStatements*.xel', NULL, NULL, NULL)

DROP TABLE IF EXISTS #myXeData

SELECT CAST(event_Data AS XML) AS StatementData
INTO #myXeData
FROM sys.fn_xe_file_target_read_file('R:\SqlStatements*.xel', NULL, NULL, NULL)

SELECT * FROM #myXeData

SELECT
StatementData.value('(event/@name)[1]', 'varchar(50)') AS event_name
,StatementData.value('(event/@timestamp)[1]', 'datetime2(0)') AS time_stamp
,StatementData.value('(event/action[@name="database_name"]/value)[1]','nvarchar(128)') AS database_name_
,StatementData.value('(event/data[@name="duration"]/value)[1]','bigint') / 1000 AS duration_ms
,StatementData.value('(event/data[@name="cpu_time"]/value)[1]','bigint') /1000 AS cpu_ms
,StatementData.value('(event/data[@name="physical_reads"]/value)[1]','bigint') AS physical_reads
,StatementData.value('(event/data[@name="logical_reads"]/value)[1]','bigint') AS logical_reads
,StatementData.value('(event/data[@name="row_count"]/value)[1]','bigint') AS row_count
,StatementData.value('(event/data[@name="statement"]/value)[1]','nvarchar(50)') AS statement_
FROM #myXeData AS evts
--ORDER BY time_stamp
ORDER BY duration_ms DESC

https://dba.stackexchange.com/questions/226704/how-to-view-xevent-event-file-data

1)상태확인

SHOW VARIABLES LIKE 'slow_query_%';

2)디렉토리 생성

mkdir /var/log/mysql

chown mysql:mysql /var/log/mysql

3) /etc/my.cnf설정

[mysqld]

slow_query_log = 1

slow_query_log_file = /var/log/mysql/mariadb-slow.log

long_query_time = 5

log_slow_rate_limit = 1

log_slow_verbosity = query_plan

log_slow_admin_statements

→ slow_query_log = 1(사용), 로그파일 위치는 /var/log/mysql/mariadb-slow.log

→ 수행시간이 5초 넘는 쿼리를 수집

4)DB 재시작

https://zetawiki.com/wiki/MySQL_%EC%8A%AC%EB%A1%9C%EC%9A%B0_%EC%BF%BC%EB%A6%AC_%EB%A1%9C%EA%B7%B8_%EC%84%A4%EC%A0%95