MySQL 상태 확인

IT노트/MYSQL 2015. 2. 25. 21:01

1.데이터베이스의 설정 상태 보기 (status)

ⓐ 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show status;
+----------------------------+------------+
| Variable_name              | Value      |
+----------------------------+------------+
| Aborted_clients            | 1838       |
| Aborted_connects           | 10970      |
| Binlog_cache_disk_use      | 0          |
.
.
.
| Threads_running            | 1          |
| Uptime                     | 5805967    |
| Uptime_since_flush_status  | 5805967    |
+----------------------------+------------+
226 rows in set (0.00 sec)

ⓑ

1
2
3
4
$ mysqladmin -uroot -p status
Enter password:
Uptime: 5806309  Threads: 4  Questions: 71514928  Slow queries: 588  Opens: 381732 
Flush tables: 1  Open tables: 64  Queries per second avg: 12.316

2. 데이터베이스의 환경변수와 값보기 (variables)

ⓐ

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show variables;
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| auto_increment_increment        | 1                                   |
| auto_increment_offset           | 1                                   |
| autocommit                      | ON                                  |
.
.
.
| version_compile_os              | pc-linux-gnu                        |
| wait_timeout                    | 28800                               |
| warning_count                   | 0                                   |
+---------------------------------+-------------------------------------+
235 rows in set (0.00 sec)

ⓑ

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$ mysqladmin -uroot -p variables
Enter password:
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| auto_increment_increment        | 1                                   |
| auto_increment_offset           | 1                                   |
| autocommit                      | ON                                  |
.
.
.
| version_compile_os              | pc-linux-gnu                        |
| wait_timeout                    | 28800                               |
| warning_count                   | 0                                   |
+---------------------------------+-------------------------------------+

3. 현재 데이터베이스에 연결된 프로세스들 보기 (processlist)

1
2
3
4
5
6
7
8
9
$ mysqladmin -uroot -p processlist
Enter password:
+---------+------+----------------------+------+---------+-------+-------+------------------+
| Id      | User | Host                 | db   | Command | Time  | State | Info             |
+---------+------+----------------------+------+---------+-------+-------+------------------+
| 1738937 | test | 192.168.111.11:50533 | test | Sleep   | 9860  |       |                  |
| 1738938 | test | 192.168.111.11:50534 |      | Sleep   | 11535 |       |                  |
| 1744741 | root | localhost            |      | Query   | 0     |       | show processlist |
+---------+------+----------------------+------+---------+-------+-------+------------------+

▶ 간단하게 명령어를 체크하여 점검에 유용하게 쓰이거나 값을 조정하여 최적의 상태로 만든다.


'IT노트 > MYSQL' 카테고리의 다른 글

[MySQL] .sql 파일 실행  (0) 2015.03.03
mysqladmin flush-hosts -uroot -p  (0) 2015.03.01
mysql grant user 생성 및 권한 설정  (0) 2015.02.25
MySQLDUMP 사용법과 옵션들  (0) 2015.02.22
계측쿼리에 사용함수 hierarchy_connect_by_parent_eq_prior_id;  (0) 2015.02.20
Posted by wychoi
,

티스토리툴바