반응형
한땀 한땀 적어보자.....
Mariadb는 5.5.37과 10.0.10 버전 부터는 Default로 Audit plugin이 내장 되어 있다.
별도의 PKG를 설치 할 필요 없이 설정이 가능하다.
환경
- OS : Centos 7.7_1908
- DB : MariaDB 10.3.22
DB에 접속하여 Plugin경로가 있는지 확인한다.
(여기서는 DB접속 방법에 대해서는 생략한다. 궁금할 경우 DB설치부분은 참고 한다.)
2020/01/29 - [DB/Mysql] - [DATABASE] MariaDB 10.3.xx Install - 바이너리 설치
MariaDB [(none)]> SHOW VARIABLES LIKE 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.001 sec)현재 설치 되어 있는 plugin을 확인한다. (아직까지 audit plugin이 설치 되어 있는건 아니다.)
MariaDB [(none)]> show plugins;
+-------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| wsrep | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CLIENT_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INDEX_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| TABLE_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| USER_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| SQL_SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_MUTEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_SEMAPHORE_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| INNODB_TABLESPACES_SCRUBBING | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| user_variables | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-------------------------------+----------+--------------------+---------+---------+
53 rows in set (0.001 sec)
Plugin이 설치 되어 있지 않기 때문에 audit 관련 설정도 보이 않는다.
MariaDB [(none)]> show global variables like "server_audit%";
Empty set (0.001 sec)
이제 audit plugin을 설치 하자!!!
설치는 간단하다!!!
MariaDB [(none)]> install plugin server_audit soname 'server_audit.so';
Query OK, 0 rows affected (0.002 sec)
설치가 완료 되면 Audit 관련 설정 상태를 확인 할 수 있다.
Check point - 1 !!!
설치가 완료 되면 plugin table에 등록이 되는지 확인한다.
MariaDB [(none)]> select * from mysql.plugin;
+--------------+-----------------+
| name | dl |
+--------------+-----------------+
| server_audit | server_audit.so |
+--------------+-----------------+
1 row in set (0.000 sec)Check point - 2 !!!
Plugin list에서 "server_audit"이 있는지 확인한다.
MariaDB [(none)]> show plugins;
+-------------------------------+----------+--------------------+-----------------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+--------------------+-----------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| wsrep | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CLIENT_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INDEX_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| TABLE_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| USER_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| SQL_SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_MUTEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_SEMAPHORE_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| INNODB_TABLESPACES_SCRUBBING | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| user_variables | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SERVER_AUDIT | ACTIVE | AUDIT | server_audit.so | GPL |
+-------------------------------+----------+--------------------+-----------------+---------+
54 rows in set (0.001 sec)Check point - 3 !!!
Audit 관련 설정 config가 있는지 확인한다.
MariaDB [(none)]> show global variables like "server_audit%";
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| server_audit_events | |
| server_audit_excl_users | |
| server_audit_file_path | server_audit.log |
| server_audit_file_rotate_now | OFF |
| server_audit_file_rotate_size | 1000000 |
| server_audit_file_rotations | 9 |
| server_audit_incl_users | |
| server_audit_logging | OFF |
| server_audit_mode | 0 |
| server_audit_output_type | file |
| server_audit_query_log_limit | 1024 |
| server_audit_syslog_facility | LOG_USER |
| server_audit_syslog_ident | mysql-server_auditing |
| server_audit_syslog_info | |
| server_audit_syslog_priority | LOG_INFO |
+-------------------------------+-----------------------+
15 rows in set (0.001 sec)
my.cnf에 Audit설정을 추가하여 Mysql 재부팅시에도 반영되도록 한다.
# vi /usr/local/mysql/my.cnf
~(생략)
[mysqld]
~(생략)
server_audit_output_type = syslog
server_audit_syslog_ident = mysql
server_audit_excl_users = 'root'
server_audit_logging = 1
~(생략)
이히 mysql를 재시작하여 설정이 정상적으로 반영이 되었는지 확인한다.
[root@TEST-DB support-files]# /usr/local/mysql/bin/mysqladmin -uroot -p shutdown
Enter password:
[1]+ Done /usr/local/mysql/bin/mysqld_safe --user=mysql (wd: /usr/local/mysql)
(wd now: /usr/local/mysql/support-files)
[root@TEST-DB support-files]#
[root@TEST-DB support-files]#
[root@TEST-DB support-files]# ps -elf | grep mysql
0 S root 11850 10883 0 80 0 - 29219 - 17:21 pts/2 00:00:00 grep --color=auto mysql
[root@TEST-DB support-files]# /usr/local/mysql/bin/mysqld_safe --user=mysql &
[1] 11851
[root@TEST-DB support-files]# 200130 17:22:12 mysqld_safe Logging to '/db/log/mariadb.log'.
200130 17:22:12 mysqld_safe Starting mysqld daemon with databases from /db/data
[root@TEST-DB support-files]# ps -elf | grep mysql
4 S root 11851 10883 1 80 0 - 28330 do_wai 17:22 pts/2 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql
4 S mysql 12308 11851 23 80 0 - 1406197 poll_s 17:22 pts/2 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/db/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/db/log/mariadb.log --open-files-limit=8192 --pid-file=/db/log/mariadb.pid --socket=/tmp/mysql.sock --port=3306
0 R root 12351 10883 0 80 0 - 29219 - 17:22 pts/2 00:00:00 grep --color=auto mysql
[root@TEST-DB support-files]#
설정이 정상적으로 되었는지 확인한다.
[root@TEST-DB support-files]# /usr/local/mysql/bin/mysql -uroot -p
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.3.22-MariaDB-log 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 global variables like "server_audit%";
+-------------------------------+------------------+
| Variable_name | Value |
+-------------------------------+------------------+
| server_audit_events | |
| server_audit_excl_users | root |
| server_audit_file_path | server_audit.log |
| server_audit_file_rotate_now | OFF |
| server_audit_file_rotate_size | 1000000 |
| server_audit_file_rotations | 9 |
| server_audit_incl_users | |
| server_audit_logging | ON |
| server_audit_mode | 0 |
| server_audit_output_type | syslog |
| server_audit_query_log_limit | 1024 |
| server_audit_syslog_facility | LOG_USER |
| server_audit_syslog_ident | mysql |
| server_audit_syslog_info | |
| server_audit_syslog_priority | LOG_INFO |
+-------------------------------+------------------+
15 rows in set (0.001 sec)
MariaDB [(none)]>
추가적으로 my.cnf에 반영하기 전에 실시간으로 아래 쿼리명령어로도 설정을 변경 할 수 있다.
## 변경전 config 정보
MariaDB [(none)]> show global variables like "server_audit%";
+-------------------------------+------------------+
| Variable_name | Value |
+-------------------------------+------------------+
| server_audit_events | |
| server_audit_excl_users | root |
| server_audit_file_path | server_audit.log |
| server_audit_file_rotate_now | OFF |
| server_audit_file_rotate_size | 1000000 |
| server_audit_file_rotations | 9 |
| server_audit_incl_users | |
| server_audit_logging | OFF |
| server_audit_mode | 0 |
| server_audit_output_type | syslog |
| server_audit_query_log_limit | 1024 |
| server_audit_syslog_facility | LOG_USER |
| server_audit_syslog_ident | mysql |
| server_audit_syslog_info | |
| server_audit_syslog_priority | LOG_INFO |
+-------------------------------+------------------+
15 rows in set (0.001 sec)
## 쿼리로 설정 변경
MariaDB [(none)]> set global server_audit_logging=ON;
Query OK, 0 rows affected (0.000 sec)
## 변경 후 확인
MariaDB [(none)]> show global variables like "server_audit%";
+-------------------------------+------------------+
| Variable_name | Value |
+-------------------------------+------------------+
| server_audit_events | |
| server_audit_excl_users | root |
| server_audit_file_path | server_audit.log |
| server_audit_file_rotate_now | OFF |
| server_audit_file_rotate_size | 1000000 |
| server_audit_file_rotations | 9 |
| server_audit_incl_users | |
| server_audit_logging | ON |
| server_audit_mode | 0 |
| server_audit_output_type | syslog |
| server_audit_query_log_limit | 1024 |
| server_audit_syslog_facility | LOG_USER |
| server_audit_syslog_ident | mysql |
| server_audit_syslog_info | |
| server_audit_syslog_priority | LOG_INFO |
+-------------------------------+------------------+
15 rows in set (0.001 sec)
MariaDB [(none)]>
실시간 반영은 아래와 같이 쿼리로 할수 있으나 DB가 재기동 된 후에는 해당 설정이 사라지니 꼭 my.cnf에 등록을 해야 한다.
MariaDB [(none)]> set global server_audit_output_type=file;
MariaDB [(none)]> set global server_audit_file_path='/db/log/server_audit.log';
MariaDB [(none)]> set global server_audit_events='connect,query,query_ddl';
MariaDB [(none)]> set global server_audit_logging=on;
MariaDB [(none)]> show global variables like "server_audit%";
+-------------------------------+---------------------------+
| Variable_name | Value |
+-------------------------------+---------------------------+
| server_audit_events | |
| server_audit_excl_users | root |
| server_audit_file_path | /db/log/server_audits.log |
| server_audit_file_rotate_now | OFF |
| server_audit_file_rotate_size | 1000000 |
| server_audit_file_rotations | 9 |
| server_audit_incl_users | |
| server_audit_logging | ON |
| server_audit_mode | 0 |
| server_audit_output_type | file |
| server_audit_query_log_limit | 1024 |
| server_audit_syslog_facility | LOG_USER |
| server_audit_syslog_ident | mysql |
| server_audit_syslog_info | |
| server_audit_syslog_priority | LOG_INFO |
+-------------------------------+---------------------------+설치한 audit을 삭제하고자 할 경우 아래와 같이 명령어를 실행한다.
MariaDB [(none)]> uninstall soname "server_audit.so";
Query OK, 0 rows affected, 1 warning (0.000 sec)
### 삭제 확인
MariaDB [(none)]> show global variables like '%audit%';
Empty set (0.002 sec)반응형
'DB > Mysql' 카테고리의 다른 글
| [Mysql/MariaDB] "mysql: [Warning] Using a password on the command line interface can be insecure." 발생 시 (0) | 2020.09.11 |
|---|---|
| [Mysql/MariaDb ] innodb frm파일에서 table schema 복구 (0) | 2020.07.23 |
| [DATABASE] MariaDB 10.3.xx Install - 바이너리 설치 (0) | 2020.01.29 |
| [Mysql/Maria DB] root password 변경 방법 (0) | 2019.10.31 |
| [MariaDB] Database root 계정 이름 변경 (0) | 2019.10.31 |