Ubuntu 20.04LTS + mariadb 10.5 (replication)

문자메시지 발송서비스 서버 구축을 위해서 mariadb (replication) 를 사용
1,000,000건/일 + @ 처리가 가능해야 한다

서버스펙

Xeon (16core)
samsung 860 pro 1Tb * 2 (RAID0)
32Gb memory

구성

L4 : Active – Standby
mariadb : Master – Slave

서버설치

http://mirror.kakao.com/ubuntu-releases/focal/

mariadb 설치

Download MariaDB Server
sudo apt-get install software-properties-common dirmngr apt-transport-https
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://mirror.yongbok.net/mariadb/repo/10.5/ubuntu focal main'

sudo apt update
sudo apt install mariadb-server

mysql 사용자 설정

mysql -uroot -p
# create user username@localhost identified by 'password';
create user my@'%' identified by '1234';
# grant select on database.table to username@localhost;
grant select on *.* to my@'%';
# grant all privileges on database.table to username@localhost;
grant all privileges on *.* to my@'%';
flush privileges;

replication

master

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

vim /etc/mysql/mariadb.conf.d/50-server.cnf

systemctl restart mariadb

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 Your MariaDB connection id is 37
 Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal-log mariadb.org binary distribution
 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 master status;
 +------------------+----------+--------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000001 |     2098 |              |                  |
 +------------------+----------+--------------+------------------+
 1 row in set (0.000 sec)
 MariaDB [(none)]>
show master status;

slave

[mysqld]
server-id = 2
relay_log=mysql-relay-bin
log_slave_updates = 1
read_only = 1

vim /etc/mysql/mariadb.conf.d/50-server.cnf

systemctl restart mariadb

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 Your MariaDB connection id is 36
 Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal mariadb.org binary distribution
 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)]> stop slave;
 MariaDB [(none)]> CHANGE MASTER TO
     -> MASTER_HOST='###.###.###.###',
     -> MASTER_PORT=3306,
     -> MASTER_USER='repl',
     -> MASTER_PASSWORD='1234',
     -> MASTER_LOG_FILE='mysql-bin.000001',
     -> MASTER_LOG_POS=2098;
 MariaDB [(none)]> start slave;
 MariaDB [(none)]> show slave status \G;
 * 1. row *
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: ###.###.###.###
                    Master_User: repl
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: mysql-bin.000001
            Read_Master_Log_Pos: 2098
                 Relay_Log_File: mysql-relay-bin.000002
                  Relay_Log_Pos: 906
          Relay_Master_Log_File: mysql-bin.000001
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
                Replicate_Do_DB:
            Replicate_Ignore_DB:
             Replicate_Do_Table:
         Replicate_Ignore_Table:
        Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
                     Last_Errno: 0
                     Last_Error:
                   Skip_Counter: 0
            Exec_Master_Log_Pos: 2098
                Relay_Log_Space: 1215
                Until_Condition: None
                 Until_Log_File:
                  Until_Log_Pos: 0
             Master_SSL_Allowed: No
             Master_SSL_CA_File:
             Master_SSL_CA_Path:
                Master_SSL_Cert:
              Master_SSL_Cipher:
                 Master_SSL_Key:
          Seconds_Behind_Master: 0
  Master_SSL_Verify_Server_Cert: No
                  Last_IO_Errno: 0
                  Last_IO_Error:
                 Last_SQL_Errno: 0
                 Last_SQL_Error:
    Replicate_Ignore_Server_Ids:
               Master_Server_Id: 1
                 Master_SSL_Crl:
             Master_SSL_Crlpath:
                     Using_Gtid: No
                    Gtid_IO_Pos:
        Replicate_Do_Domain_Ids:
    Replicate_Ignore_Domain_Ids:
                  Parallel_Mode: optimistic
                      SQL_Delay: 0
            SQL_Remaining_Delay: NULL
        Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Slave_DDL_Groups: 3
 Slave_Non_Transactional_Groups: 0
     Slave_Transactional_Groups: 0
 1 row in set (0.000 sec)
 ERROR: No query specified
 MariaDB [(none)]>
show slave status \G;

.htaccess 사용할 경우 apache server-status 접속

.htaccess 또는 rewirte 를 사용할 경우 https://localhost/server-status 주소에 접속하면 404페이지가 보일 경우가 있다.

RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php/$1 [L]
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_URI} !=/server-status
RewriteRule ^(.*)$ index.php/$1 [L]

index.php 로 가기전에 규칙을 하나더 추가해준다

Zabbix Agent 설치

zabbix 에이전트

운영체제를 선택하고 zabbix 서버버전을 확인해서 에이전트를 선택하면, 아래쪽에 다운로드 링크가 나타난다

서버에 에이전트 다운로드

cd /usr/local
mkdir zabbix-agent
cd zabbix-agent

wget  https://cdn.zabbix.com/zabbix/binaries/stable/5.0/5.0.10/zabbix_agent-5.0.10-linux-3.0-amd64-static.tar.gz

에이전트 설치

tar xfzp zabbix_agent-5.0.10-linux-3.0-amd64-static.tar.gz

zabbiz 유저추가

sudo useradd -r -s /bin/false zabbix

환경설정파일 복사

cd /usr/local/etc
ln -s /usr/local/zabbiz-agent/conf/zabbix_agentd.conf .

에이전트가 시작할 때 -t 옵션을 사용해서 설정파일을 지정하지 않으면, /usr/local/etc/zabbix_agentd.conf 파일을 찾는다. 때문에 해당위치에 파일을 링크로 걸어둔다. 직접 환경설정파일을 지정하려면 -t 옵션을 사용한다.

zabbiz_agentd.conf 설정파일 수정

# 로그파일위치를 변경
LogFile=/tmp/zabbix_agentd.log

# Zabbix 서버아이피로 변경
Server=127.0.0.1

에이전트 시작하기

cd /usr/local/zabbix-agent/sbin
./zabbix_agentd

우분투에서 시작스크립트에 등록

cd /etc/init.d
ln -s /usr/local/zabbix-agent/sbin/zabbix_agentd .

방화벽 설정

iptables -A INPUT -s {server ip} -p tcp -m tcp --dport 10050 -j ACCEPT
iptables -A INPUT -s {server ip} -p tcp -m tcp --dport 10051 -j ACCEPT
iptables -A INPUT -p tcp -m tcp --dport 10050 -j DROP
iptables -A INPUT -p tcp -m tcp --dport 10051 -j DROP

Dante – Socks5 Proxy

패키지 설치

# apt update && apt upgrade -y
# apt install dante-server dante-client

환경설정

logoutput: syslog stdout /var/log/dante.log
internal: eth0 port = 1080
external: eth1
clientmethod: none
socksmethod: none
user.privileged: proxy
user.unprivileged: nobody
user.libwrap: nobody
client pass {
        from: 0.0.0.0/0 to: 0.0.0.0/0
        log: error connect disconnect
}
socks pass {
        from: 0.0.0.0/0 to: 0.0.0.0/0
        log: connect error disconnect
}

/etc/danted.conf

프록시 서버는 eth0에서 LISTEN, 트래픽은 eth1으로 나가게 한다.
eth0와 eth1은 각각 공인아이피를 셋팅했다.

테스트

# curl -x socks5://{프록시아이피}:{프록시포트} https://www.google.com

socksify

route {
        from: 0.0.0.0/0 to: 0.0.0.0/0 via: {프록시아이피} port = {프록시포트}
        protocol: tcp udp
        proxyprotocol: socks_v4 socks_v5
        method: none
}

/etc/dante.conf

dante-client 패키지를 설치하면 socksify를 사용할 수 있다.
위에처럼 설정하고 아래 명령을 사용하는 방법도 있다.

이 경우에는 socksify를 사용하는 프로세스는 모두 지정한 프록시를 통해서 통신이 된다.

# socksify wget -O- -q https://google.com

Redsocks – Tcp to Proxy Redirector

서버로 들어오는 패킷을 socks5로 내보내기 위해서 redsocks를 활용했다.
설명은 우분투 18.04 기준으로 한다

Redsocks 홈페이지

https://github.com/darkk/redsocks

패키지

# apt update && apt -y upgrade
# apt install redsocks

패키지 매니저로 설치한다

환경설정파일

base {
    log_debug = on;
    log_info = on;
    log = "file:/var/log/redsocks.log";
    daemon = on;
    redirector = iptables;
}
redsocks {
    local_ip = 0.0.0.0;
    local_port = 12345;
    ip = {프록시서버 아이피};
    port = {프록시서버 포트};
    type = socks5;
}

/etc/redsocks.conf

# systemctl restart redsocks

redsocks 재시작

# iptables -t nat -A PREROUTING --in-interface ppp0 -p tcp -j REDIRECT --to-ports 12345

ppp0로 들어오는 패킷을 redsocks 포트인 12345로 리다이렉트 시켜준다

CentOS 7 – 오라클 18c XE 설치

패키지 업데이트

# yum update
# yum install wget

패키지 업데이트를 먼저한다

다운로드

# cd /usr/local/src
# wget https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
# wget https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-18c-1.0-1.x86_64.rpm

설치파일 다운로드는 아래 링크에서 한다.
https://www.oracle.com/database/technologies/xe-downloads.html

설치 진행

# yum -y localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
# yum -y localinstall oracle-database-xe-18c-1.0-1.x86_64.rpm

설치 사이즈가 커서 오래걸린다

초기설정

# /etc/init.d/oracle-xe-18c configure

Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
 Confirm the password:
 Configuring Oracle Listener.
 Listener configuration succeeded.
 Configuring Oracle Database XE.
 Enter SYS user password:                                                                                                           
 Enter SYSTEM user password:
 
 Enter PDBADMIN User Password:
 
 Prepare for db operation
 7% complete
 Copying database files
 29% complete
 Creating and starting Oracle instance
 30% complete
 31% complete
 34% complete
 38% complete
 41% complete
 43% complete
 Completing Database Creation
 47% complete
 50% complete
 Creating Pluggable Databases
 54% complete
 71% complete
 Executing Post Configuration Actions
 93% complete
 Running Custom Scripts
 100% complete
 Database creation complete. For details check the logfiles at:
  /opt/oracle/cfgtoollogs/dbca/XE.
 Database Information:
 Global Database Name:XE
 System Identifier(SID):XE
 Look at the log file "/opt/oracle/cfgtoollogs/dbca/XE/XE1.log" for further details.
 Connect to Oracle Database using one of the connect strings:
      Pluggable database: localhost.localdomain/XEPDB1
      Multitenant container database: localhost.localdomain
 Use https://localhost:5500/em to access Oracle Enterprise Manager for Oracle Database XE

비밀번호는 최소 1개의 대문자와 소문자, 숫자를 조합해서 8자이상 입력해야 한다.

환경설정 추가

# vi ~/.bash_profile

export ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_SID=XE
export ORAENV_ASK=NO
export PATH=$PATH:$ORACLE_HOME/bin

.bash_profile 파일 하단에 환경설정을 추가

리스너 확인

# lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 01-FEB-2021 11:02:03
 Copyright (c) 1991, 2018, Oracle.  All rights reserved.
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
 STATUS of the LISTENER
 Alias                     LISTENER
 Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
 Start Date                01-FEB-2021 11:01:04
 Uptime                    0 days 0 hr. 0 min. 58 sec
 Trace Level               off
 Security                  ON: Local OS Authentication
 SNMP                      OFF
 Default Service           XE
 Listener Parameter File   /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora
 Listener Log File         /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
 Listening Endpoints Summary…
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/product/18c/dbhomeXE/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW))
 Services Summary…
 Service "XE" has 1 instance(s).
   Instance "XE", status READY, has 1 handler(s) for this service…
 Service "XEXDB" has 1 instance(s).
   Instance "XE", status READY, has 1 handler(s) for this service…
 Service "ba3db2e4206012dde055000000000001" has 1 instance(s).
   Instance "XE", status READY, has 1 handler(s) for this service…
 Service "xepdb1" has 1 instance(s).
   Instance "XE", status READY, has 1 handler(s) for this service…
 The command completed successfully

listener.ora

[root@localhost admin]# cat listener.ora
 listener.ora Network Configuration File: /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora
 Generated by Oracle configuration tools.
 DEFAULT_SERVICE_LISTENER = XE
 LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     )
   )

tnsnames.ora

[root@localhost admin]# cat tnsnames.ora
 tnsnames.ora Network Configuration File: /opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora
 Generated by Oracle configuration tools.
 XE =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = XE)
     )
   )
 LISTENER_XE =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

포트번호가 바뀐경우가 있었는데, 그럴경우에 1521로 변경해서 저장하고 재시작 해준다

데이터베이스 접속확인

# sqlplus
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Feb 1 12:59:38 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Enter user-name: system
Enter password:
Last Successful login time: Mon Feb 01 2021 11:23:03 +09:00
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL>

데이터베이스 Start/Stop

# /etc/init.d/oracle-xe-18c start
# /etc/init.d/oracle-xe-18c stop

시스코 SNMP 설정하기

ACL에 허용할 아이피 추가

sys>en
sys# conf t
sys(config)# ip access-list standard 90
sys(config-ext-nacl)# permit host {허용할 아이피}
sys(config-ext-nacl)# exit
sys(config)# exit

ACL 설정한다.
여기서는 90번으로 추가했다.
허용할 아이피를 입력하면 그 외는 모두 차단된다.

ACL 확인

sys# show access-lists

Standard IP access list 90
     10 permit {허용할 아이피}

ACL 리스트를 확인한다

SNMP 커뮤니티 추가

sys# conf t
sys(config)# snmp-server community {커뮤니티이름} ro 90
sys(config)# exit

snmp 서버에 커뮤니티를 추가한다.

추가시 RO (read only) 권한으로 하며, 사용할 access-list 번호는 앞에서 추가한 90으로 한다.

SNMP 동작확인

sys# show snmp community

Community name: {커뮤니티이름}
 Community Index: {커뮤니티이름}
 Community SecurityName: {커뮤니티이름}
 storage-type: nonvolatile        active access-list: 90

snmp 정보를 확인해보면 커뮤니티와 access-list가 정상적인지 확인할 수 있다.