[Mysql/MariaDB] Client 접속 없이 쿼리 실행 (--execute=statement)

1. Client 접속하여 쿼리 실행

# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 33164
Server version: 10.5.9-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show status like '%CONNECT%';
+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Aborted_connects                              | 7     |
| Aborted_connects_preauth                      | 0     |
| Connection_errors_accept                      | 0     |
| Connection_errors_internal                    | 0     |
| Connection_errors_max_connections             | 0     |
| Connection_errors_peer_address                | 0     |
| Connection_errors_select                      | 0     |
| Connection_errors_tcpwrap                     | 0     |
| Connections                                   | 33168 |
| Max_used_connections                          | 167   |
| Performance_schema_session_connect_attrs_lost | 0     |
| Slave_connections                             | 0     |
| Slaves_connected                              | 0     |
| Ssl_client_connects                           | 0     |
| Ssl_connect_renegotiates                      | 0     |
| Ssl_finished_connects                         | 0     |
| Threads_connected                             | 45    |
| wsrep_connected                               | OFF   |
+-----------------------------------------------+-------+
18 rows in set (0.001 sec)

 

2. Client 접속 없이 쿼리 실행

[옵션]

--execute=statement, -e statement

: 명령문을 실행하고 종료합니다.

 

Ex)

# mysql -u root -p'비밀번호' -e "실행 쿼리;"

# mysql -u root -p'비밀번호' DB명 -e "실행 쿼리;"

★ -p 옵션 사용 시 '비밀번호'와 공백이 없어야 합니다. 

# mysql -u root -p'yourPassword' -e "show status like '%CONNECT%';"
+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Aborted_connects                              | 7     |
| Aborted_connects_preauth                      | 0     |
| Connection_errors_accept                      | 0     |
| Connection_errors_internal                    | 0     |
| Connection_errors_max_connections             | 0     |
| Connection_errors_peer_address                | 0     |
| Connection_errors_select                      | 0     |
| Connection_errors_tcpwrap                     | 0     |
| Connections                                   | 33194 |
| Max_used_connections                          | 167   |
| Performance_schema_session_connect_attrs_lost | 0     |
| Slave_connections                             | 0     |
| Slaves_connected                              | 0     |
| Ssl_client_connects                           | 0     |
| Ssl_connect_renegotiates                      | 0     |
| Ssl_finished_connects                         | 0     |
| Threads_connected                             | 46    |
| wsrep_connected                               | OFF   |
+-----------------------------------------------+-------+

# mysql -uroot -p'qhdks!@#' mysql -e "SELECT host, user, password FROM user;"
+-----------------------+-------------+-------------------------------------------+
| Host                  | User        | Password                                  |
+-----------------------+-------------+-------------------------------------------+
| localhost             | mariadb.sys |                                           |
| localhost             | root        | *1BE5C08FBB4947BF17FFC9887569E8F2839B3341 |
| localhost             | mysql       | invalid                                   |
| localhost             |             |                                           |
| localhost.localdomain |             |                                           |
| %                     | root        | *1BE5C08FBB4947BF17FFC9887569E8F2839B3341 |
+-----------------------+-------------+-------------------------------------------+

# mysql -uroot -p'qhdks!@#' -e "SELECT host, user, password FROM mysql.user;"
+-----------------------+-------------+-------------------------------------------+
| Host                  | User        | Password                                  |
+-----------------------+-------------+-------------------------------------------+
| localhost             | mariadb.sys |                                           |
| localhost             | root        | *1BE5C08FBB4947BF17FFC9887569E8F2839B3341 |
| localhost             | mysql       | invalid                                   |
| localhost             |             |                                           |
| localhost.localdomain |             |                                           |
| %                     | root        | *1BE5C08FBB4947BF17FFC9887569E8F2839B3341 |
+-----------------------+-------------+-------------------------------------------+

 

3. Client 접속 없이 쿼리 실행 응용

• -e 옵션을 사용하여 MySQL Command-Line Client를 사용하지 않아도 되므로 쉘 스크립트 작성 시 유용하게 사용 가능합니다.
• Ex) 5초마다 DB processlist 결과 로깅

 

[스크립트 생성]

[스크립트 파일 생성]
# touch connnect_db_log.sh

[실행 권한 부여]
# chmod +x connnect_db_log.sh

[쉘 스크립트 작성]
# vi connnect_db_log.sh

 

[쉘 스크립트 내용]

#!/bin/bash

function connnectCheck() {
        echo -n -e '\n' | mysql -u root -p'비밀번호' -e "show processlist;" >> $today
}

while true; do
        today=$(date "+%Y-%m-%d").txt #날짜로 파일명 생성
        today_format=$(date) #로그를 위한 데이트 형태

        echo -n -e '\n['$today_format']\n' >> $today
        connnectCheck
        sleep 5 #5초 주기
done

 

[스크립트 실행]

[실행]
# nohup sh connnect_db_log.sh 1> /dev/null 2>&1 &

[중지: 실행자의 세션이 끊기지 않은 경우]
# jobs
[1]+  Running                 nohup sh connnect_db_log.sh > /dev/null 2>&1 &

# kill %1

[중지: 실행자의 세션이 끊겼거나 다른 사용자를 이용해 중지할 경우]
# ps -ef | grep connnect_db_log.sh | grep -v grep
root     12129  9060  0 03:10 pts/1    00:00:00 sh connnect_db_log.sh

# kill 12129