우체국에서 최신 우편번호 DB 파일을 다운로드 받으면 텍스트 형태로 되어 있습니다.

이를 mysql 에 데이터로 밀어넣는 방법입니다.

https://www.epost.go.kr/search/zipcode/areacdAddressDown.jsp
https://www.epost.go.kr/search/areacd/zipcode_DB.zip
여기에서 파일을 다운로드 받아서 압축을 풀고 서버로 업로드를 합니다.

우편번호|
시도|
시도영문|
시군구|
시군구영문|
읍면|
읍면영문|
도로명코드|
도로명|
도로명영문|
지하여부|
건물번호본번|
건물번호부번|
건물관리번호|
다량배달처명|
시군구용건물명|
법정동코드|
법정동명|
리명|
행정동명|
산여부|
지번본번|
읍면동일련번호|
지번부번|
구우편번호|
우편번호일련번호

파일을 열어서 보면 위에 같은 형태로 되어 있는데 똑같은 구조로 테이블을 만듭니다.

CREATE TABLE zipcode (
 zipcode        VARCHAR(5) NULL,
 sido           VARCHAR(25) NULL,
 sido_en        VARCHAR(20) NULL,
 sigungu        VARCHAR(30) NULL,
 sigungu_en     VARCHAR(30) NULL,
 eupmyun        VARCHAR(20) NULL,
 eupmyun_en     VARCHAR(25) NULL,
 doro_code      VARCHAR(12) NULL,
 doro           VARCHAR(40) NULL,
 doro_en        VARCHAR(50) NULL,
 under_yn       VARCHAR(1) NULL,
 buildno1       VARCHAR(5) NULL,
 buildno2       VARCHAR(4) NULL,
 buildnum       VARCHAR(25) NULL,
 multiple       VARCHAR(1) NULL,
 buildname      VARCHAR(70) NULL,
 dong_code      VARCHAR(10) NULL,
 dong           VARCHAR(20) NULL,
 ri             VARCHAR(20) NULL,
 dong_hj        VARCHAR(30) NULL,
 mount_yn       VARCHAR(1) NULL,
 jibun1         VARCHAR(4) NULL,
 eupmyundong_no VARCHAR(2) NULL,
 jibun2         VARCHAR(4) NULL,
 zipcode_old    VARCHAR(7) NULL,
 zipcode_seq    VARCHAR(3) NULL,
 idx            INT(10)    UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

이제 DB Text 파일을 ANSI 에서 UTF-8 로 변환 합니다.

iconv -c -f cp949 -t utf-8 강원도.txt > 강원도1.txt
iconv -c -f cp949 -t utf-8 경기도.txt > 경기도1.txt
iconv -c -f cp949 -t utf-8 경상남도.txt > 경상남도1.txt
iconv -c -f cp949 -t utf-8 경상북도.txt > 경상북도1.txt
iconv -c -f cp949 -t utf-8 광주광역시.txt > 광주광역시1.txt
iconv -c -f cp949 -t utf-8 대구광역시.txt > 대구광역시1.txt
iconv -c -f cp949 -t utf-8 대전광역시.txt > 대전광역시1.txt
iconv -c -f cp949 -t utf-8 부산광역시.txt > 부산광역시1.txt
iconv -c -f cp949 -t utf-8 서울특별시.txt > 서울특별시1.txt
iconv -c -f cp949 -t utf-8 세종특별자치시.txt > 세종특별자치시1.txt
iconv -c -f cp949 -t utf-8 울산광역시.txt > 울산광역시1.txt
iconv -c -f cp949 -t utf-8 인천광역시.txt > 인천광역시1.txt
iconv -c -f cp949 -t utf-8 전라남도.txt > 전라남도1.txt
iconv -c -f cp949 -t utf-8 전라북도.txt > 전라북도1.txt
iconv -c -f cp949 -t utf-8 제주특별자치도.txt > 제주특별자치도1.txt
iconv -c -f cp949 -t utf-8 충청남도.txt > 충청남도1.txt
iconv -c -f cp949 -t utf-8 충청북도.txt > 충청북도1.txt

변환된 파일을 mysql load data 를 이용해서 데이터로 밀어넣습니다.

LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/강원도1.txt'         INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/경기도1.txt'         INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/경상남도1.txt'       INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/경상북도1.txt'       INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/광주광역시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/대구광역시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/대전광역시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/부산광역시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/서울특별시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/세종특별자치시1.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/울산광역시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/인천광역시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/전라남도1.txt'       INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/전라북도1.txt'       INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/제주특별자치도1.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/충청남도1.txt'       INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/충청북도1.txt'       INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;

생각보다 빨리 처리가 되는군요~

idx 컬럼 때문에 warning 이 발생하긴 하지만 idx 컬럼이 없으면 로딩시 느려집니다.

load data 뒤쪽에 SET idx = NULL 을 넣어도 워닝은 발생하네요~

INSERT INTO TABLE (컬럼명, ...) 이렇게 지정하면 되긴 하지만 저 많은 컬럼을 다 나열하자니~ ㅎ

 

블로그 이미지

iVPS 영은파더♥

가상서버호스팅 VPS 리눅스 서버관리 윈도우 IT

댓글을 달아 주세요

  • iVPS 영은파더♥ 2019.06.26 14:37 신고  댓글주소  수정/삭제  댓글쓰기

    LOAD DATA LOCAL INFILE '/강원도1.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
    Query OK, 343756 rows affected, 8 warnings (14.34 sec)
    Records: 343756 Deleted: 0 Skipped: 0 Warnings: 8

    MariaDB [(none)]> show warnings;
    +---------+------+----------------------------------------------------+
    | Level | Code | Message |
    +---------+------+----------------------------------------------------+
    | Warning | 1265 | Data truncated for column 'buildno2' at row 22660 |
    | Warning | 1265 | Data truncated for column 'buildno2' at row 71625 |
    | Warning | 1265 | Data truncated for column 'mount_yn' at row 172116 |
    | Warning | 1261 | Row 172116 doesn't contain data for all columns |
    | Warning | 1265 | Data truncated for column 'buildno1' at row 210646 |
    | Warning | 1265 | Data truncated for column 'buildno1' at row 210649 |
    | Warning | 1265 | Data truncated for column 'mount_yn' at row 256448 |
    | Warning | 1261 | Row 256448 doesn't contain data for all columns |
    +---------+------+----------------------------------------------------+
    8 rows in set (0.00 sec)

상위 버전에서 덤프 받은 DB 데이터를 하위 버전에 밀어 넣을려고 했더니 아래와 같은 에러가 나는군요~

 

ERROR 1273 (HY000) at line 31: Unknown collation: 'utf8mb4_unicode_520_ci'

 

ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

위와 같은 부분을 찾아서 변경하면 됩니다.

 

vi 에서 하는 방법

:%s/utf8mb4_unicode_520_ci/utf8_general_ci/g

:%s/utf8mb4/utf8/g

 

블로그 이미지

iVPS 영은파더♥

가상서버호스팅 VPS 리눅스 서버관리 윈도우 IT

Tag MySQL

댓글을 달아 주세요

[MySQL] DB hotcopy 백업 쉘스크립트



리플리케이션으로 데이터베이스를 동기화 하기전에 기존 데이터를 미리 백업 받아야 합니다.


mysqldump 명령어로 덤프를 하여도 되지만 innodb 가 아닌 myisam 이라면 핫카피가 됩니다.


myisam 테이블이란 가정하에 DB를 백업하는 쉘스크립트입니다.


#!/bin/sh


SERVER_HOST="root@example.com"

SERVER_PORT="22222"

SYNC_DIR_M="/var/lib/mysql/데이터베이스명/"

SYNC_DIR_S="/var/lib/mysql/데이터베이스명/"

# 1920KB/s = 15Mb/s

SYNC_SPD=1920


rsync -av --bwlimit=$SYNC_SPD -e "ssh -p$SERVER_PORT" $SERVER_HOST:$SYNC_DIR_M $SYNC_DIR_S


슬래이브 서버에서 실행하면 됩니다.


단점이 DB 운영중 rsync 시에는 실제 마스터서버의 데이터와 핫카피 한 슬래이브 서버의 데이터는 완전히 일치하지 않는다는 점입니다.



블로그 이미지

iVPS 영은파더♥

가상서버호스팅 VPS 리눅스 서버관리 윈도우 IT

Tag HotCopy, MySQL

댓글을 달아 주세요

[MySQL] Replication 마스터 동기화 쉘스크립트



리플리케이션 하다가 도중에 슬래이브 서버를 재시작 해야 하는 경우 마스터의 로그 파일의 포지션을 맞추는게 은근 짜증이 납니다.


DB를 멈춰 놓고 작업하면 느긋하게 해도 되지만 마스터 DB는 동작을 멈추면 안되는 경우엔 사용자가 없는 새벽시간을 이용해 작업하기도 그렇죠~


그래서 쉘스크립트로 마스터의 정보를 읽어와서 바로 CHANGE MSATER TO SQL을 실행하도록 해보았습니다.


#!/bin/sh


M_DB_HOST="MASTER서버"

M_DB_USER="리플리케이션유저아이디"

M_DB_PASS="리플리케이션유저비밀번호"

S_DB_USER="SLAVE루트아이디"

S_DB_PASS="SLAVE비밀번호"


MASTER_STATUS=(`mysql -h$M_DB_HOST -u$M_DB_USER -p$M_DB_PASS -e "SHOW MASTER STATUS" | grep -v "File"`)


SQL1="STOP SLAVE;"

SQL2="SET GLOBAL replicate_do_db = '동기화데이터베이스1,동기화데이터베이스2';"

SQL3="\

CHANGE MASTER TO \

MASTER_HOST='$M_DB_HOST', \

MASTER_USER='$M_DB_USER', \

MASTER_PASSWORD='$M_DB_PASS', \

MASTER_PORT=3306, \

MASTER_LOG_FILE='${MASTER_STATUS[0]}', \

MASTER_LOG_POS=${MASTER_STATUS[1]}; \

"

SQL4="START SLAVE;"


mysql -u$S_DB_USER -p$S_DB_PASS -e "$SQL1"

mysql -u$S_DB_USER -p$S_DB_PASS -e "$SQL2"

mysql -u$S_DB_USER -p$S_DB_PASS -e "$SQL3"

mysql -u$S_DB_USER -p$S_DB_PASS -e "$SQL4"


색깔이 있는 부분은 자신의 환경에 맞게 고쳐야 합니다.


응용하시면 됩니다.



mysql-replication.sh.txt


스크립트파일도 첨부하였습니다.


기존 데이터 백업은 http://blog.ivps.kr/607 여기를 참고하세요~




REPLICATION 유저로 SHOW MASTER STATUS 시 권한을 따로 주어야 합니다.


GRANT REPLICATION CLIENT ON *.* TO '유저아이디'@'%' IDENTIFIED BY '비밀번호';


블로그 이미지

iVPS 영은파더♥

가상서버호스팅 VPS 리눅스 서버관리 윈도우 IT

댓글을 달아 주세요

CentOS 7.x Postfix + Dovecot + RoundCube 설치 방법


하나의 서버에 여러개의 도메인을 운영할 때 시스템 계정이 아닌 DB에 가상 메일계정을 이용하는 방법입니다.

검색해서 설정했더니 잘 안되어서 여러곳의 정보를 취합해서 정리하였습니다.

참조 사이트 :

 https://www.linode.com/docs/email/postfix/email-with-postfix-dovecot-and-mariadb-on-centos-7/

 https://www.digitalocean.com/community/tutorials/how-to-configure-a-mail-server-using-postfix-dovecot-mysql-and-spamassassin

mariadb 및 아파치, php 는 설치되어 있다고 가정하고 진행합니다.


# yum install postfix dovecot dovecot-mysql roundcubemail

# systemctl enable postfix

# systemctl start postfix

# systemctl enable dovecot

# systemctl start dovecot

필요한 패키지를 설치합니다.


# useradd -u 5000 -d /home/vmail -s /sbin/nologin -m vmail

가상메일에서 사용될 계정을 만듭니다.


CREATE DATABASE webmail;

GRANT ALL PRIVILEGES ON webmail.* TO webmail@localhost IDENTIFIED BY '비밀번호';

가상메일에서 사용될 DB 계정을 만듭니다.


CREATE TABLE `webmail`.`virtual_domains` (

 `id` INT NOT NULL AUTO_INCREMENT,

 `name` VARCHAR(50) NOT NULL,

 PRIMARY KEY (`id`)

 ) DEFAULT CHARSET=utf8;


CREATE TABLE `webmail`.`virtual_users` (

 `id` INT NOT NULL AUTO_INCREMENT,

 `password` VARCHAR(106) NOT NULL,

 `email` VARCHAR(80) NOT NULL,

 PRIMARY KEY (`id`),

 UNIQUE KEY `email` (`email`)

 ) DEFAULT CHARSET=utf8;


CREATE TABLE `webmail`.`virtual_aliases` (

 `id` INT NOT NULL AUTO_INCREMENT,

 `source` varchar(100) NOT NULL,

 `destination` varchar(100) NOT NULL,

 PRIMARY KEY (`id`)

 ) DEFAULT CHARSET=utf8;


INSERT INTO `webmail`.`virtual_domains` (`id` ,`name`) VALUES

 ('1', 'aaa.com'),

 ('2', 'bbb.com'),

 ('3', 'example.com');


INSERT INTO `webmail`.`virtual_users` (`id`, `password`, `email`) VALUES

 ('1', ENCRYPT('비밀번호', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user1@aaa.com'),

 ('2', ENCRYPT('비밀번호', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user2@aaa.com'),

 ('3', ENCRYPT('비밀번호', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user1@bbb.com'),

 ('4', ENCRYPT('비밀번호', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user2@bbb.com'),

 ('5', ENCRYPT('비밀번호', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user1@example.com'),

 ('6', ENCRYPT('비밀번호', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user2@example.com');


INSERT INTO `webmail`.`virtual_aliases` (`id`, `source`, `destination`) VALUES

 ('1', 'alias@aaa.com', 'user1@aaa.com'),

 ('2', 'user2@example.com', 'user1@example.com');

가상메일에서 사용될 테이블과 샘플데이터를 만듭니다.

default_pass_scheme = CRYPT 일때는 ENCRYPT('비밀번호')


# vi /etc/postfix/mysql-virtual-alias-maps.cf

hosts = 127.0.0.1

dbname = webmail

user = webmail

password = 비밀번호

query = SELECT destination FROM virtual_aliases WHERE source='%s'


# vi /etc/postfix/mysql-virtual-users.cf

hosts = 127.0.0.1

dbname = webmail

user = webmail

password = 비밀번호

query = SELECT email FROM virtual_users WHERE email='%s'


# vi /etc/postfix/mysql-virtual-mailbox-domains.cf

hosts = 127.0.0.1

dbname = webmail

user = webmail

password = 비밀번호

query = SELECT name FROM virtual_domains WHERE name='%s'


# vi /etc/postfix/mysql-virtual-mailbox-maps.cf

hosts = 127.0.0.1

dbname = webmail

user = webmail

password = 비밀번호

query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM virtual_users WHERE email='%s'


# chgrp postfix /etc/postfix/mysql-virtual-*.cf

# chmod o= /etc/postfix/mysql-virtual-*.cf

Postfix 에서 가상메일 계정의 정보를 가져올 쿼리파일을 만듭니다.


# vi /etc/postfix/main.cf

myhostname = mail.example.com

mydestination = localhost

mynetworks = 127.0.0.0/8

inet_interfaces = all

message_size_limit = 30720000

virtual_alias_domains =

virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual-alias-maps.cf, mysql:/etc/postfix/mysql-virtual-users.cf

virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf

virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf

virtual_mailbox_base = /home/vmail

virtual_uid_maps = static:5000

virtual_gid_maps = static:5000

smtpd_sasl_type = dovecot

smtpd_sasl_path = private/auth

smtpd_sasl_auth_enable = yes

broken_sasl_auth_clients = yes

smtpd_sasl_authenticated_header = yes

smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination

smtpd_use_tls = yes

smtpd_tls_cert_file = /etc/pki/dovecot/certs/dovecot.pem

smtpd_tls_key_file = /etc/pki/dovecot/private/dovecot.pem

proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks

virtual_transport = dovecot

dovecot_destination_recipient_limit = 1

Postfix main.cf 파일을 설정합니다.

postconf -e 'mydestination = localhost' 형태의 명령어로도 수정이 가능합니다.

그리고 postconf -n 명령어로 환경설정을 볼 수도 있습니다.

회색 부분을 굳이 설정하지 않아도 됩니다. ( 보안 사용시 필요 )

proxy_read_maps 은 없어도 될 것 같습니다.


# vi /etc/postfix/master.cf

dovecot   unix  -       n       n       -       -       pipe

    flags=DRhu user=vmail:vmail argv=/usr/libexec/dovecot/deliver -f ${sender} -d ${recipient}


submission inet n       -       n       -       -       smtpd

  -o syslog_name=postfix/submission

  -o smtpd_tls_security_level=encrypt

  -o smtpd_sasl_auth_enable=yes

  -o smtpd_reject_unlisted_recipient=no

  #-o smtpd_client_restrictions=$mua_client_restrictions

  #-o smtpd_helo_restrictions=$mua_helo_restrictions

  #-o smtpd_sender_restrictions=$mua_sender_restrictions

  -o smtpd_recipient_restrictions=permit_sasl_authenticated,reject

  -o milter_macro_daemon_name=ORIGINATING

smtps     inet  n       -       n       -       -       smtpd

  -o syslog_name=postfix/smtps

  -o smtpd_tls_wrappermode=yes

  -o smtpd_sasl_auth_enable=yes

  -o smtpd_reject_unlisted_recipient=no

  #-o smtpd_client_restrictions=$mua_client_restrictions

  #-o smtpd_helo_restrictions=$mua_helo_restrictions

  #-o smtpd_sender_restrictions=$mua_sender_restrictions

  -o smtpd_recipient_restrictions=permit_sasl_authenticated,reject

  -o milter_macro_daemon_name=ORIGINATING

Postfix master.cf 파일을 설정합니다.

회색 부분을 설정하지 않아도 됩니다. ( 보안 사용시 필요 )


# systemctl restart postfix


# postmap -q alias@aaa.com mysql:/etc/postfix/mysql-virtual-alias-maps.cf

user1@aaa.com

# postmap -q user1@aaa.com mysql:/etc/postfix/mysql-virtual-users.cf

user1@aaa.com

# postmap -q aaa.com mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf

aaa.com

# postmap -q user1@aaa.com mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf

aaa.com/user1/

postfix 데몬을 재시작 하고 가상계정 정보를 잘 가져 오는지 확인합니다.


# mv /etc/dovecot/dovecot.conf /etc/dovecot/dovecot.conf.ori

# vi /etc/dovecot/dovecot.conf

protocols = imap pop3

listen = *

log_timestamp = "%Y-%m-%d %H:%M:%S "

mail_location = maildir:/home/vmail/%d/%n/Maildir


ssl_cert = </etc/pki/dovecot/certs/dovecot.pem

ssl_key = </etc/pki/dovecot/private/dovecot.pem


namespace {

    type = private

    separator = .

    prefix = INBOX.

    inbox = yes

}


service auth {

    unix_listener auth-master {

        mode = 0600

        user = vmail

    }

    unix_listener /var/spool/postfix/private/auth {

        mode = 0666

        user = postfix

        group = postfix

    }

    user = root

}


service auth-worker {

    user = root

}


protocol lda {

    log_path = /home/vmail/dovecot-deliver.log

    auth_socket_path = /var/run/dovecot/auth-master

    postmaster_address = postmaster@example.com

}


protocol pop3 {

    pop3_uidl_format = %08Xu%08Xv

}


passdb {

    driver = sql

    args = /etc/dovecot/dovecot-sql.conf.ext

}


userdb {

    driver = static

    args = uid=vmail gid=vmail home=/home/vmail/%d/%n allow_all_users=yes

}


# vi /etc/dovecot/dovecot-sql.conf.ext

driver = mysql

connect = host=127.0.0.1 dbname=webmail user=webmail password=비밀번호

default_pass_scheme = SHA512-CRYPT

password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';


# chown vmail:dovecot /etc/dovecot

# chown vmail:dovecot /etc/dovecot/dovecot.conf

# chown vmail:dovecot /etc/dovecot/dovecot-sql.conf.ext

# chmod o= /etc/dovecot/dovecot-sql.conf.ext

기존 /etc/dovecot/dovecot.conf 파일을 백업해 놓고 새로운 파일로 설정을 합니다.

그리고 계정계정 로그인 정보에 사용될 /etc/dovecot/dovecot-sql.conf.ext 쿼리파일을 만듭니다.

dovecot 데몬에서 접근이 가능하도록 소유권한을 변경합니다.


# systemctl restart postfix

# systemctl restart dovecot

# tail -n15 /var/log/maillog

이제 postfix, dovecot 데몬을 재시작하고 메일이 잘 보내지고 잘 받아지는지 테스트 하면 됩니다.

telnet localhost smtp, telnet localhost pop3 명령어를 이용해서 메일 테스트를 합니다.


# mysql -u webmail -p webmail < /usr/share/roundcubemail/SQL/mysql.initial.sql


# cp -p /etc/roundcubemail/config.inc.php.sample /etc/roundcubemail/config.inc.php

# vi /etc/roundcubemail/config.inc.php

$config['db_dsnw'] = 'mysql://webmail:비밀번호@localhost/webmail';

$config['product_name'] = 'Webmail'; // HTML Title 부분에 나오는 내용, 적당한 이름으로 변경


# vi /etc/httpd/conf.d/roundcubemail.conf

<Directory /usr/share/roundcubemail/>

    <IfModule mod_authz_core.c>

        # Apache 2.4

        Require local

        Require all granted

    </IfModule>

</Directory>

<Directory /usr/share/roundcubemail/installer/>

    <IfModule mod_authz_core.c>

        # Apache 2.4

        Require local

        Require ip 허용아이피

    </IfModule>

</Directory>

# systemctl restart httpd

RoundCube 메일 환경을 설정합니다.

http://webmail.example.com/roundcubemail 페이지를 호출하여 확인합니다.

로그인시 사용자명에는 이메일 주소를 입력하여야 합니다.


위의 내용은 아래 첨부파일을 다운 받으면 됩니다.

postfix-dovecot-roundcube.txt


블로그 이미지

iVPS 영은파더♥

가상서버호스팅 VPS 리눅스 서버관리 윈도우 IT

댓글을 달아 주세요

[MySQL] systemctl start mariadb 에러


조금 황당한 경우이긴 하지만 mysql 이 죽어있길래 재시작을 하니 에러가 나서 확인해봤습니다.

[root@vtr html]# systemctl status mariadb

● mariadb.service - MariaDB database server

   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)

   Active: failed (Result: exit-code) since Thu 2018-10-04 16:38:06 KST; 4min 23s ago

  Process: 22597 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=1/FAILURE)

  Process: 22596 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)

  Process: 22566 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)

 Main PID: 22596 (code=exited, status=0/SUCCESS)


Oct 04 16:38:04 vtr systemd[1]: Starting MariaDB database server...

Oct 04 16:38:04 vtr mariadb-prepare-db-dir[22566]: Database MariaDB is probab...

Oct 04 16:38:05 vtr mysqld_safe[22596]: 181004 16:38:05 mysqld_safe Logging ....

Oct 04 16:38:05 vtr mysqld_safe[22596]: 181004 16:38:05 mysqld_safe Starting...l

Oct 04 16:38:06 vtr systemd[1]: mariadb.service: control process exited, co...=1

Oct 04 16:38:06 vtr systemd[1]: Failed to start MariaDB database server.

Oct 04 16:38:06 vtr systemd[1]: Unit mariadb.service entered failed state.

Oct 04 16:38:06 vtr systemd[1]: mariadb.service failed.

Hint: Some lines were ellipsized, use -l to show in full.

운영중인 VPS 가 메모리가 너무 부족해서 생긴 현상인데 스왑메모리를 추가해서 해결하였습니다.

swap 늘이는 방법은 http://blog.ivps.kr/283 여기를 참고하세요~



블로그 이미지

iVPS 영은파더♥

가상서버호스팅 VPS 리눅스 서버관리 윈도우 IT

댓글을 달아 주세요

MySQL 대용량 테이블 삭제 테스트



3기가 넘어가는 테이블이라서 삭제하는데 시간이 얼마나 걸리는지 속도 테스트 한번 해보았습니다.


MySQL 대용량 테이블 삭제


테스트를 위해서 똑같은 Table 을 두 개로 복제하였습니다.



▶ delete 로 삭제시

MySQL 대용량 테이블 삭제

0.0111 초


▶ truncate 로 삭제시

MySQL 대용량 테이블 삭제

0.3008 초


▶ drop table 로 삭제시

0.3379 초

드랍 테이블은 시간이 조금 더 걸렸습니다.


예상과 달리 delete 명령어로 삭제하는게 더 빠르네요~


아무래도 조건절이 없어서 그런가 봅니다~


조건절이 있다면 시간은 아주 많이 걸릴 것 같네요~



블로그 이미지

iVPS 영은파더♥

가상서버호스팅 VPS 리눅스 서버관리 윈도우 IT

댓글을 달아 주세요

MySQL mysql vs mysqli vs pdo Benchmark Test



mysql, mariadb 모두 테스트를 해보았지만 결과는 거의 비슷하게 나왔습니다.


처리 속도 Benchmark 테스트는 아래의 PHP 소스 코드로 진행하였습니다.



▶ 벤치마크 테스트 소스


<?php

error_reporting(E_ALL);

ini_set('display_errors', TRUE);

ini_set('display_startup_errors', TRUE);

set_time_limit(0);


$db_addr = '127.0.0.1';

$db_user = 'user';

$db_pass = 'pass';

$table = 'test.benchmark_table';


benchmark($table, 100000);


function benchmark($table, $rows) {

  global $db_addr;

  global $db_user;

  global $db_pass;

  $table_innodb1 = $table."_innodb1";

  $table_innodb2 = $table."_innodb2";

  $table_innodb3 = $table."_innodb3";

  $table_myisam1 = $table."_myisam1";

  $table_myisam2 = $table."_myisam2";

  $table_myisam3 = $table."_myisam3";


  mysql_connect($db_addr, $db_user, $db_pass);

  mysql_query('drop table if exists '.$table_innodb1);

  mysql_query('drop table if exists '.$table_innodb2);

  mysql_query('drop table if exists '.$table_innodb3);

  mysql_query('drop table if exists '.$table_myisam1);

  mysql_query('drop table if exists '.$table_myisam2);

  mysql_query('drop table if exists '.$table_myisam3);

  mysql_query("CREATE TABLE $table_innodb1 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=INNODB");

  mysql_query("CREATE TABLE $table_innodb2 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=INNODB");

  mysql_query("CREATE TABLE $table_innodb3 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=INNODB");

  mysql_query("CREATE TABLE $table_myisam1 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=MyISAM");

  mysql_query("CREATE TABLE $table_myisam2 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=MyISAM");

  mysql_query("CREATE TABLE $table_myisam3 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=MyISAM");


  mysql_query("begin");

  for ($i=0; $i<$rows; $i++) {

    mysql_query("insert into $table_innodb1 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_innodb2 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_innodb3 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_myisam1 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_myisam2 values (0, 'benchmark test $i', rand() * $rows * 100)");

    mysql_query("insert into $table_myisam3 values (0, 'benchmark test $i', rand() * $rows * 100)");

  }

  mysql_query("commit");


  mysql_conn1($db_addr, $db_user, $db_pass, $table_innodb1);

  mysql_conn2($db_addr, $db_user, $db_pass, $table_innodb2);

  mysql_conn3($db_addr, $db_user, $db_pass, $table_innodb3);

  mysql_conn1($db_addr, $db_user, $db_pass, $table_myisam1);

  mysql_conn2($db_addr, $db_user, $db_pass, $table_myisam2);

  mysql_conn3($db_addr, $db_user, $db_pass, $table_myisam3);

}


function mysql_conn1($db_addr, $db_user, $db_pass, $table) {

  $i = 0;

  $start = get_time();

  $db = mysql_connect($db_addr, $db_user, $db_pass);

  $result = mysql_query('select * from '.$table, $db);

  while ($row = mysql_fetch_assoc($result)) { $i++; $test = $row; }

  mysql_close($db);

  echo 'rows : '.$i.' '.$table.' mysql '.round(get_time()-$start, 5)."<br>";

}


function mysql_conn2($db_addr, $db_user, $db_pass, $table) {

  $i = 0;

  $start = get_time();

  $db = new mysqli($db_addr, $db_user, $db_pass);

  $result = $db->query('select * from '.$table);

  while ($row = $result->fetch_assoc()) { $i++; $test = $row; }

  $db->close();

  echo 'rows : '.$i.' '.$table.' mysqli '.round(get_time()-$start, 5)."<br>";

}


function mysql_conn3($db_addr, $db_user, $db_pass, $table) {

  $i = 0;

  $start = get_time();

  $db = new PDO('mysql:host='.$db_addr, $db_user, $db_pass);

  foreach ($db->query('select * from '.$table) as $row) { $i++; $test = $row; }

  $db = null;

  echo 'rows : '.$i.' '.$table.' pdo '.round(get_time()-$start, 5)."<br>";

}


function get_time() {

  $t = explode(' ', microtime());

  return ((float)$t[0] + (float)$t[1]);

}

?>



▶ MySQL 5.1.73 결과


rows : 100000 redblue.benchmark_table_innodb1 mysql  0.39674
rows : 100000 redblue.benchmark_table_innodb2 mysqli 0.3815
rows : 100000 redblue.benchmark_table_innodb3 pdo    0.33571


rows : 100000 redblue.benchmark_table_myisam1 mysql  0.11391
rows : 100000 redblue.benchmark_table_myisam2 mysqli 0.11894
rows : 100000 redblue.benchmark_table_myisam3 pdo    0.12141



▶ MariaDB 5.5.52 결과


rows : 100000 test.benchmark_table_innodb1 mysql  0.2214
rows : 100000 test.benchmark_table_innodb2 mysqli 0.37862
rows : 100000 test.benchmark_table_innodb3 pdo    0.28196


rows : 100000 test.benchmark_table_myisam1 mysql  0.22379
rows : 100000 test.benchmark_table_myisam2 mysqli 0.19565
rows : 100000 test.benchmark_table_myisam3 pdo    0.28953


다른 분들이 테스트 한 결과에는 mysqli 가 좀 더 빠르던데 저는 이상하게 거의 비슷하게 결과가 나오는군요~


그나마 mariadb 에서는 효과가 있는 듯 합니다.


블로그 이미지

iVPS 영은파더♥

가상서버호스팅 VPS 리눅스 서버관리 윈도우 IT

댓글을 달아 주세요

MySQL 읽기전용 유저 생성



셀렉트만 가능한 유저를 생성하는 방법입니다.


다른 용도로도 사용을 하겠지만, 주 용도가 데이터 변경이 되면 안되는 Replication Slave 서버를 핸들링할때 사용합니다.


GRANT SELECT ON *.* to 'readuser'@'%' IDENTIFIED BY 'readonly'

*.* 대신에 접근가능한 database명을 지정해도 됩니다.


한번 테스트를 해보겠습니다. ( delete, update, insert )


# mysql -ureaduser -preadonly

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 58

Server version: 5.5.52-MariaDB MariaDB Server


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


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


MariaDB [(none)]> use testdb

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A


Database changed

MariaDB [testdb]> select * from test_table4;

+-----+-------+

| idx | title |

+-----+-------+

|   1 | 111   |

|   2 | 222   |

|   3 | 333   |

+-----+-------+

3 rows in set (0.00 sec)


MariaDB [testdb]> delete from test_table4 where idx = 3;

ERROR 1142 (42000): DELETE command denied to user 'readuser'@'localhost' for table 'test_table4'

MariaDB [testdb]> update test_table4 set title='444' where idx = 4;

ERROR 1142 (42000): UPDATE command denied to user 'readuser'@'localhost' for table 'test_table4'

MariaDB [testdb]> insert into test_table4 value (4,444);

ERROR 1142 (42000): INSERT command denied to user 'readuser'@'localhost' for table 'test_table4'


셀렉트는 가능하지만 삭제, 수정, 추가 기능은 에러가 뜨는 것을 확인할 수 있습니다.


블로그 이미지

iVPS 영은파더♥

가상서버호스팅 VPS 리눅스 서버관리 윈도우 IT

댓글을 달아 주세요

MySQL DB TABLE 다른 DATABASE로 빨리 옮기는 방법



데이터가 많은 테이블의 경우 mysqldump 로 덤프를 받아서 다른 database 로 옮기는 작업은 시간이 너무 오래 걸립니다.


동일한 서버에 database 명이 다른 곳에 table 을 손쉽게 옮기고 싶을 때 사용할 수 있습니다.


물론 테이블 복사도 가능하지만 copy 하는 시간 만큼은 시간이 소요가 됩니다.



▶ 테이블 옮기기 ( database1 -> database2 )


# mv /var/lib/mysql/database1/*.frm /var/lib/mysql/database2/

# mv /var/lib/mysql/database1/*.MYD /var/lib/mysql/database2/

# mv /var/lib/mysql/database1/*.MYI /var/lib/mysql/database2/



▶ 테이블 복사하기 ( database1 -> database2 )


# cp /var/lib/mysql/database1/*.frm /var/lib/mysql/database2/

# cp /var/lib/mysql/database1/*.MYD /var/lib/mysql/database2/

# cp /var/lib/mysql/database1/*.MYI /var/lib/mysql/database2/


database2 는 미리 생성이 되어 있어야 합니다.


mysql 을 재시작 할 필요도 없습니다.


블로그 이미지

iVPS 영은파더♥

가상서버호스팅 VPS 리눅스 서버관리 윈도우 IT

Tag MySQL

댓글을 달아 주세요