MySQL - 모니터링3

서버의 통계정보를 확인하기(SHOW STATUS)

MySQL서버내의 통계정보를 확인하려면 다음과 같이 한다.

shell$ mysqadmin extened-status

mysql>SHOW STATUS;

mysql>SELECT * FROM information_schema.GLOBAL_STATUS;

mysql>SELECT * FROM information_schema.SESSION_STATUS;

information_schema를 SELECT하는 경우는 Variable_name은 전부 대문자로 표현된다.

- Variable_name 내용

• Aborted_clients: 클라이언트가 정상적인 방법으로 통신을 끊지않은 수
• Aborted_connects: MySQL서버에 접속에 실패한 수
• Binlog_cache_disk_use: mysqld는 트랜잭션개시할 때에 binlog_cache_size 메모리를 확보하고 그곳에 트랜잭션 문을 일단 써놓는다.(COMMIT, ROLLBACK실행전) 만약 캐쉬에 실행문을 전부 기록하지 못할 경우는 문장은 디스크에 쓰여진다. Binlog_cache_disk_use는 이 디스크에 문장을 보존하게 된 트랜잭션 수. binlog_cache_size의 설정이 충분히 큰 경우는 이 수가 적어지게 된다.
• Binlog_cache_use: mysqld는 트랜잭션개시할 때에 binlog_cache_size 메모리를 확보하고 그곳에 트랜잭션 문을 일단 써놓는다.(COMMIT, ROLLBACK실행전) Binlog_cache_use는 이 캐쉬를 사용한 트랜잭션 수
• Bytes_received: 모든 클라이언트에서 받은 바이트수
• Bytes_sent: 모든 클라이언트에 보낸 바이트수
• Com_xxx: 각각 xxx문이 얼마나 실행되었나를 나타낸다. Com_stmt_xxx는 Prepared Statement관련 문이 있다는 것을 나타낸다. Com_stmt_xxx는 예를 들어 Prepared statement인수 에러로 실행되지 못했던 경우에도 계산된다. 즉 Prepared Statement의 리퀘스트 수라고 말할 수 있다.
• Compression: 클라이언트 서버사이에 통신을 zlib(gzip)압축을 하고 있으면 ON
• Connections: MySQL서버에 접속을 시도한 수. 성공, 실패하고는 관계없음.
• Created_tmp_disk_tables: 쿼리 실행중에 디스크에 작성된 임시 테이블 수. tmp_table_size보다 큰 영역이 필요한 경우에 디스크에 작성된다. 임시테이블을 메모리에서 전부 처리하고 싶다면 tmp_table_size을 늘린다.
• Created_tmp_files:mysqld가 작성한 임시 파일 수
• Created_tmp_tables: 쿼리 실행중에 메모리내에 작성된 임시 테이블 수. 메모리 상에 작성된 임시 테이블의 최대 사이즈는 tmp_table_size로 결정된다.
• Delayed_errors: INSERT DELAYED로 쓰여진 레코드중에 에러가 난 것들의 수(중복 키 에러 등 )
• Delayed_insert_threads: 사용된 INSERT DELAYED 핸들러 스레드수
• Delayed_writes: INSERT DELAYED로 쓰여진 레코드 수
• Flush_commands: FLUSH문의 실행횟수
• Handler_commit: 내부 커밋 실행횟수
• Handler_delete: 테이블에서 삭제된 레코드 수
• Handler_discover: 테이블에 대해서 NDB클러스터에 질의하는 것을 discovery라고 부른다. 얼마나 NDB클러스터에 질의해서 테이블이 찾아졌는지를 나타냄.
• Handler_prepare: two phase commit의 prepare 구문의 횟수
• Handler_read_first: 테이블의 최초의 행이 읽어들여진 횟수
• Handler_read_key
• Handler_read_next
• Handler_read_prev
• Handler_read_rnd
• Handler_read_rnd_next
• Handler_rollback: 롤백을 요구한 수
• Handler_savepoint:SAVEPOINT 요청 수
• Handler_savepoint_rollback
• Handler_update
• Handler_write
• Innodb_buffer_pool_pages_data:데이터를 포함한 페이지 (dirty, clean) 수
• Innodb_buffer_pool_pages_dirty
• Innodb_buffer_pool_pages_flushed
• Innodb_buffer_pool_pages_free
• Innodb_buffer_pool_pages_latched: Innodb버퍼풀 안에 latched페이지 수. 현재 읽고 쓰여지고 있는 페이지는 플러쉬나 삭제가 되지않는데 이 것을 말한다.
• Innodb_buffer_pool_pages_misc
• Innodb_buffer_pool_pages_total
• Innodb_buffer_pool_read_ahead_rnd
• Innodb_buffer_pool_read_ahead_seq
• Innodb_buffer_pool_read_requests
• Innodb_buffer_pool_reads
• Innodb_buffer_pool_wait_free
• Innodb_buffer_pool_write_requests
• Innodb_data_fsyncs
• Innodb_data_pending_fsyncs
• Innodb_data_pending_reads
• Innodb_data_pending_writes
• Innodb_data_read: 지금까지 데이터를 읽어들인 바이트의 합계
• Innodb_data_reads: 데이터 취득 횟수
• Innodb_data_writes: 데이터 작성 횟수
• Innodb_data_written:지금까지 데이터를 쓴 바이트의 합계

[계속]

MySQL - 모니터링2

자식 쓰레드 확인(SHOW PROCESSLIST / KILL)

현재 접속중인 클라이언트(자식 쓰레드)를 KILL문이나 mysqladmin kill 을 사용해서 강제종료시킬 수 있다.

기동중에 있는 자식 쓰레드를 확인하기 위해서는 다음과 같은 명령어를 사용할 수 있다.

SHOW PROCESSLIST

또는

SELECT * FROM information_schema.PROCESSLIST

또는

mysqladmin processlist

실행하기 위해서는 PROCESS권한이 필요한다.

실행결과에 표시되는 내용은 다음과 같다.

• Id: 쓰레드 ID이다. MySQL서버가 작성한 클라이언트용 쓰레드에 고유 번호가 할당된다. KILL문을 사용해서 쓰레드를 죽일 경우에 이 번호를 지정한다.
• User, Host: 접속유저, 클라이언트 호스트를 나타낸다.
• db:현재 데이터베이스를 나타낸다.
• Command:실행되고 있는 명령어 종류를 나타낸다. 다음과 같은 것이 있다. Binlog Dump, Change user, Close stmt, Connect Connect Out, Create DB, Daemon, Debug, Delayed insert, Drop DB, End, Error, Execute, Fetch, Field List, Init DB, Kill, Long Data, Ping, Prepare, Processlist, Query, Quit, Refresh, Register Slave, Reset stmt, Set option, Shutdown, Sleep, Statistics, Table Dump, Time
• Time: 명령어의 실행시간(sec.)이다.
• State:상태의 설명이다. 다음과 같은 것이 있다. After create, allocating local table, Analyzing, Changing master, Checking master version, Checking table, cleansing up, closing tables, Connecting to master, converting HEAP to MyISAM, copy to tmp table, Copying to group table, Copying to tmp table, Copying to tmp table on disk, Creating delayed handler, Creating sort index, creating table, Creating table from master dump, Creating tmp table, deleting from main table, deleting from reference tables, discard_or_import_tablespace, Execution of init_command, Finished reading one binlog; switching to next binlog, Flushing tables, freeing items, FULLTEXT initialization, got handler lock, got old table, Has read all relay log; waiting for the slave I/O thread to update it, Has sent all binlog to slave; waiting for binlog to be updated, init, insert, Killed, Killing slave, Locked, logging slow query, login, Making temp file, Opening master dump table, Opening table, Opening tables, preparing, Purging old relay logs, query end, Queueing master event to the relay log, Reading event from the relay log, Reading from net, Reading master dump table data, Rebuilding the index on master dump table, Reconnecting after a failed binlog dump request, Reconnecting after a failed master event read, Registering slave on master, Removing duplicates, removing tmp table, rename, rename result table, Reopen tables, Repair by sorting, Repair with keycache, Requesting binlog dump, reschedule, Searching rows for update, Sending binlog event to slave, Sending data, setup, Sorting for group, Sorting for order, Sorting result, starting slave, statistics, storing row into queue, System lock, Table lock, unauthenticated user, update, Updating, updating main table, updating reference tables, upgrading lock, User lock, waiting for delay_list, Waiting for event from ndbcluster, waiting for handler insert, waiting for handler lock, waiting for handler open, Waiting for INSERT, Waiting master to send event, Waiting for master update, Waiting for the slave SQL thread to free enough relay log space, Waiting for slave mutex on exit, Waiting for table, Waiting for tables, Waiting for the next event in relay log, Waiting on cond, Waiting to finalize termination, Waiting to reconnect after a failed binlog dump request, Waiting to reconnect after a failed master event read, Writing to net
• Info: 쓰레드가 실행되고 있는 문장을 나타낸다. NULL인 경우는 문장을 실행하고 있지 않다는 뜻이다.

MySQL - 모니터링1

데이터베이스 목록을 표시

SHOW DATABASES
또는
SELECT * FROM information_schema.SCHEMATA [WHERE...]

테이블 목록을 표시

SHOW TABLES
또는
SHOW TABLE STATUS
또는
SELECT * FROM information_schema.TABLES [WHERE ...]

테이블정의를 표시

DESC 테이블명
DESCRIBE 테이블명
EXPLAIN 테이블명

테이블정보를 표시

SHOW CREATE TABLE 테이블명

컬럼정보를 표시

SHOW COLUMNS FROM 테이블명
SHOW FULL COLUMNS FROM 테이블명
SELECT * FROM information_schema.COLUMNS [WHERE ...]


인덱스정보를 표시

SHOW INDEX FROM 테이블명
SELECT * FROM information_schema.STATISTICS [WHERE ...]

MySQL 보안 - stone with SSL 2

stone작성

우선 OpenSSL대응의 stone을 작성한다. 소스를 풀고 안에 있는 Makefile을 편집한다.

다음 부분을 환경에 맞춰서 편집한다.

Makefile

SSL= /usr/local/ssl

SSL_FLAGS= -DUSE_SSL -I$(SSL)/include

SSL_LIBS= -L$(SSL)/lib -lssl -lcrypto

Linux의 경우, glibc-2.3.2에서는 EPOLLONESHOT이 정의되어 있지않기때문에 컴파일 에러가 난다.

Makefile안의 linux: 타켓에 적혀있는 -DUSE_EPOLL을 생략한다.

준비가 끝났으면 make를 실행한다.

make의 실행

shell$ make linux-ssl

생성된 stone을 원하는 디렉토리에 인스톨하면 된다.

stone실행

stone의 사용방법은 소스와 같이 있는 README.txt에 자세하게 적혀있다.

stone의 기본적인 구문은 다음과 같다.

stone 리모트호스트:리모트포트 로컬포트

stone은 Local포트에서 대기 , 수신한 것을 리모트 포트에 릴레이 전송하게 된다.

＞클라이언트에서 실행하는 mysql

mysql -h 127.0.0.1 -P 3308

＞클라이언트에서 실행하는 stone

stone remote.server.name:3307 3308

＞서버에서 실행하는 stone

stone localhost:3306 3307

이것으로 패킷 리피트의 골격이 완성되었다.

그러나 SSL의 통신은 되지 않는다. 여기에서 SSL용 옵션을 지정한다.

전송포트, 대기포트의 뒤에 /ssl을 붙이게 되어 SSL통신을 하게 된다.

＞클라이언트에서 실행하는 stone

stone remote.server.name:3307/ssl 3308

＞서버에서 실행하는 stone

stone localhost:3306 3307/ssl

또, 개인 키나 CERTIFICATE 파일을 지정하고 픈 경우는 다음처럼 조작한다.

＞클라이언트에서 실행하는 stone

stone -q key=key.pem -q cert=cert.pem remote.server.name:3307/ssl 3308

＞서버에서 실행하는 stone

stone -z key=key.pem -z cert=cert.pem localhost:3306 3307/ssl

-z옵션은 SSL서버용 옵션이다.

stone이 서버로 동작할 때 사용한다. -q옵션은 SSL클라이언트로 stone이 동작할 때 사용한다.

MySQL의 SSL대응의 때와 마찬가지로 이 옵션을 사용하면 된다.

key=는 개인키, cert=는 CERTIFICATE파일로 자기가 서명한 것이던지 제3자 인증국이 싸인한 파일을 지정한다. 인증국이 서명한 경우 인증국의 CERTIFICATE파일이 필요로 하게된다.

그 경우는 -{z|q} CApath=, -{z|q}CAfile= 둘중에 하나를 사용하면 된다.

CApath=의 경우는 해쉬를 생성해서 파일명을 만들어야 한다.

MySQL 보안 - stone with SSL

stone은 심플한 패킷 리피터로 TCP나 UDP를 릴레이 하는 것이 가능하다.

작고 가볍기 때문에 간단한 벽에 구멍을 내거나 패킷 내용 취득등에 많이 사용되고 있다.

OpenSSL를 같이 쓸 수 있기때문에 SSL통신을 할 수 있다.

MySQL클라이언트나 서버를 SSL통신을 시킬려면 stone은 1개(또는 0개)여도 충분하지만 여기에서는 MySQL클라이언트와 서버 둘다 SSL가 설치되지 않았다고 가정하고 이야기한다.

stone사용예

[Local] ------------------------------------[Remoe]
mysql-> 3308(stone) ==(암호화)==> 3307(stone)->3306(mysqld)

MySQL 보안 - SSH port forwarding

SSH를 사용해서 통신경로를 암호화하는 방법도 있다.

단지, MySQL서버가 동작하고 있는 운영체제에 계정을 가지고 있을 필요가 있다.

[클라이언트]                                  [서버]

mysql-> 3307(ssh) ==(암호화)==> (sshd)->3306(mysqld)

클라이언트로 mysql명령어를 사용한다고 가정하자.

자기가 사용하고 있는 로컬 컴퓨터의 TCP/IP 의 3307번 포트를 ssh가 처리할 수 있도록 한다.

Local의 ssh는 포트 3307로 받아들인 내용을 리모트 컴퓨터의 sshd에 송신, remote의 포트 번호 3306으로 전송하도록 한다.

이 방식을 실현하기 위해서는 -L옵션을 사용해서 클라이언트 머신에서 다음과 같이 ssh를 실행한다.

ssh -L 3307:localhost:3306 remote-server

3307

local 장비에서의 포트 번호이다. mysql명령어나 ssh명령어를 실행하려고 하는 컴퓨터에서의 포트를 말한다.

localhost:3306

remote장비에서 볼 때 어느 호스트, 어느 포트인가 지정한다. 위 표기는 같은 장비 3306를 포워딩하게 된다.

Local에서 조작은 실제로 다음과 같이 될 것이다.

shell$ ssh -L 3307:localhost:3306 mycom@remote.server.name &

shell$ mysql -h 127.0.0.1 -P 3307

mysql명령어에는 -h 127.0.0.1로 TCP/IP를 이용한 접속을 지정하고 -P 3307로 포트 번호를 지정한다.

TCP/IP포트번호 1024이하는 root계정이 아니면 사용할 수 없으므로 주의한다.

MySQL서버(mysqld)에 접속하는 것은 MySQL서버와 같은 컴퓨터에서 동작하고 있는 sshd임으로 mysqld에서 보면 클라이언트는 '사용자명'@'127.0.0.1'이 된다. 따라서 GRANT에 주의해야한다.

MySQL 보안 - TCP Wrapper

MySQL에 TCP Wrapper가 되게 하려면 configure옵션에 --with-libwrap을 지정한다.

shell$ ./configure --with-libwrap=/usr

디렉토리가 지정된 경우, 지정된 디렉토리 밑에 TCP Wrapper 헤더파일과 라이브러리가 필요하다.

컴파일과 인스톨이 끝난 뒤는 다른 TCP Wrapper설정과 마찬가지로 hosts.allow와 hosts.deny파일을 편집해야한다.

hosts.{allow,deny}의 최초 컬럼은 프로그램이름이다. 보통은 mysqld이지만 만약 바이너리 파일의 이름을 변경했을 경우에는 그것으로 해야한다.

hosts.deny파일의 편집예

mysqld: .my.domain

mysqld-5.1: 192.168.0.0/255.255.255.240

libwrap를 추가하면 클라이언트가 접속할 때마다 서버는 TCP Wrapper를 이용해서 클라이언트의 접속의 허가, 거부를 수행한다.

MySQL 보안 - SSL통신7

SSL통신을 필수로 하기

어떤 유저의 통신에는 반드시 SSL을 사용하는 경우는 GRANT문에 REQUIRE를 지정한다.

mysql>GRANT .... TO 'mycom'@'192.168.0.1' REQUIRE SSL;

또한 어떤 유저는 반드시 올바른 인증을 받지 않으면 안되는 경우에는 GRANT문에 REQUIRE X509를 지정한다.

mysql>GRANT .... TO 'mycom'@'192.168.0.1' REQUIRE X509;


Subject고정

어떤 사용자의 Subject를 고정하고픈 경우는 GRANT문에 SUBJECT를 추가한다.
클라이언트 키의 Subject가 다른 경우, 서버는 접속을 거부한다.

mysql>GRANT .... TO 'mycom'@'192.168.0.1' REQUIRE SUBJECT '/C=KR/L=Basel/O=MYCOM/OU=DB Comms/CN=whatever'

키의 Subject를 표시하고 싶은 경우에는 다음과 같이 한다.

shell$ openssl x509 -text -in client-cert.pem


Issuer의 고정

어떤 유저의 Issuer를 고정하고픈 경우는 GRANT문에 ISSUER를 추가한다.
클라이언트 키의 Issuer가 다른 경우는 서버는 접속을 거부한다.

mysql>GRANT .... TO 'mycom'@'192.168.0.1' REQUIRE ISSUER '/C=KR/O=MYCOM/OU=Certificate Authority/CN=whatever';

Cipher의 고정

어떤 유저의 암호화방식을 고정하고픈 경우는 GRANT문에 CIPHER를 추가한다.

mysql> GRANT .... TO 'mycom'@'192.168.0.1' REQUIRE CIPHER 'DHE-RSA-AES256-SHA:AES128-SHA';

Cipher 리스트는 복수의 암호화를 「:」로 구분짓는다. 특별한 지정으로 「ALL」 하고 「-」이 있다.
예를 들어 , 다음처럼 한 경우 모든 암호화 방식에서 DHE-RSA-AES256-SHA:를 제거한 암호화 방식을 채용한다.
CIPHER 'ALL:DHE-RSA-AES256-SHA'

지원되는 Cipher확인하려면 다음과 같은 조작을 한다.

shell$ openssl ciphers