[Mysql/MariaDB] DB 관리전용 유틸리티 (mysqladmin)

1. mysqladmin

• mysqladmin 은 관리 작업을 수행하기 위한 클라이언트입니다.
• 서버의 구성 및 현재 상태를 확인하고 데이터베이스를 생성 및 삭제하는 등의 작업을 수행할 수 있습니다.

 

[형식]

# mysqladmin [options] command [command-arg] [command [command-arg]] ...

 

2. mysqladmin 사용 예시

CREATE & DROP DB

// DB 생성
# mysqladmin -u root -p'계정 비밀번호' create test_db

// 확인
# mysql -u root -p'계정 비밀번호' -e 'show databases';
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test_db            |
+--------------------+

// DB 삭제
# mysqladmin -u root -p'qhdks!@#' drop test_db
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'test_db' database [y/N] y
Database "test_db" dropped

# mysql -u root -p'계정 비밀번호' -e 'show databases';
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

 

상태

// 버전 확인
# mysqladmin -u root -p'계정 비밀번호' version
mysqladmin  Ver 9.1 Distrib 10.4.24-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version          10.4.24-MariaDB
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 9 min 1 sec

Threads: 6  Questions: 16  Slow queries: 0  Opens: 18  
Flush tables: 1  Open tables: 11  Queries per second avg: 0.029

// 서버 상태(요약)
# mysqladmin -u root -p'계정 비밀번호' status 
Uptime: 566  Threads: 6  Questions: 17  Slow queries: 0  Opens: 18  
Flush tables: 1  Open tables: 11  Queries per second avg: 0.030

// show status 
# mysqladmin -u root -p'계정 비밀번호' extended-status
+--------------------------------------------------------------+--------------------------------------------------+
| Variable_name                                                | Value                                            |
+--------------------------------------------------------------+--------------------------------------------------+
| Aborted_clients                                              | 0                                                |
| Aborted_connects                                             | 0                                                |
| ...                                                          |                                                  |
+--------------------------------------------------------------+--------------------------------------------------+

# mysqladmin -u root -p'계정 비밀번호' extended-status | grep 'Threads'
| Threads_cached                                               | 0                                                |
| Threads_connected                                            | 1                                                |
| Threads_created                                              | 1                                                |
| Threads_running                                              | 6                                                |

// 상태 변수를 지웁니다. (초기화)
# mysqladmin -u root -p'계정 비밀번호' flush-status 

// show variables
# mysqladmin -u root -p'계정 비밀번호' variables
+--------------------------------------------------------------+--------------------------------------------------+
| Variable_name                                                | Value                                            |
+--------------------------------------------------------------+--------------------------------------------------+
| alter_algorithm                                              | DEFAULT                                          |
| aria_block_size                                              | 8192                                             |
| ...                                                          |                                                  |
+--------------------------------------------------------------+--------------------------------------------------+

// 서버 상태 확인
# mysqladmin -u root -p'계정 비밀번호' ping
mysqld is alive

 

프로세스(스레드)

// threads list
# mysqladmin -u root -p'qhdks!@#' processlist
+----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
| Id | User        | Host      | db | Command | Time | State                    | Info             | Progress |
+----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
| 1  | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 2  | system user |           |    | Daemon  |      | InnoDB purge coordinator |                  | 0.000    |
| 3  | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 4  | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 5  | system user |           |    | Daemon  |      | InnoDB shutdown handler  |                  | 0.000    |
| 27 | root        | localhost |    | Query   | 0    | Init                     | show processlist | 0.000    |
+----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+

// thread kill
# mysqladmin -u root -p'qhdks!@#' kill 522100

// thread kill 여러개 kill할 경우 ,로 구분하고 공백 X
# mysqladmin -u root -p'qhdks!@#' kill 523986,523987

 

 

비밀번호 변경

# mysqladmin -u root -p'기존 비밀번호' password 새 비밀번호