pymysql module을 사용하여 mysql 에 접속해 general log 를 조회하는 코드입니다.
모니터링 등을 위해 python 으로 mysql 에 접속해서 command 를 수행하는 코드 중 가장 기본이 되는 코드입니다.
code
import pymysql
conn=pymysql.connect(host='localhost',port=3310, user='root', password='test123',db='mysql')
c=conn.cursor()
sql='select * from general_log where command_type="Query"'
c.execute(sql)
rows=c.fetchall()
strOut = ''
strFormat = '%20s \t%30s \t%-20s\t%-20s\t%-20s\t%-20s\n'
strOut = strFormat % ('event_time', 'user_host', 'thread_id', 'server_id', 'command_type', 'agument')
strOut += '--------------------\t------------------------------\t--------------------\t--------------------\t--------------------\t--------------------'
print(strOut)
for i in rows:
i=list(map(str,i))
strOut=strFormat % (i[0][:19],i[1],i[2],i[3],i[4],i[5])
print(strOut, end='')
output
AL01542225:python nhn$ python test.py
event_time user_host thread_id server_id command_type agument
-------------------- ------------------------------ -------------------- -------------------- -------------------- --------------------
2019-10-10 04:37:24 root[root] @ localhost [] 8 64 Query b'select * from mysql.user'
2019-10-10 04:37:27 root[root] @ localhost [] 8 64 Query b'select * from mysql.user'
2019-10-10 04:37:53 root[root] @ localhost [] 8 64 Query b"CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' BY <secret>"
2019-10-10 04:38:02 root[root] @ localhost [] 8 64 Query b"grant all privileges on *.* to 'root'@'%'"
2019-10-10 04:38:05 root[root] @ localhost [] 8 64 Query b'flush privileges'