[ php ] csv 파일 테이블로 import 하기 ( LOAD DATA INFILE )





디렉토리에 들어있는 csv 파일들을 DB에 저장할 일이 생겼다.

파일 개수가 많아 php로 파일 리스트를 읽어 테이블들로 일괄 저장하려고 한다.

 

mysqlimport도 생각해봤는데 LOAD DATA INFILE 문 처리속도가 더 빠르다고 한다.

 

일단 DB에 접속하자.

 

$conn = mysqli_connect('localhost', 'userid', 'password','dbname');
if (mysqli_connect_errno($conn)) 
     echo "데이터베이스 연결 실패: " . mysqli_connect_error();
else
 echo "connection success";

 

csv파일을 import할 테이블도 생성하자.

기존 csv파일에 없는 idx필드를 생성해 primary key값으로 설정하고 자동 증가 옵션을 주었다.

 

$sql = "CREATE TABLE `테이블이름` (
     `idx` INT(11) NOT NULL AUTO_INCREMENT,
     PRIMARY KEY(idx),
     `날짜` INT(8) NOT NULL,
     `가격` INT(5) NOT NULL,
     ...

`시간` INT(4) NOT NULL 
 )";

 

if(mysqli_query($conn, $sql))

    echo "Table created successfully"; 

else
    echo "Error creating table:".mysqli_error($conn);
  

 

LOAD DATA INFILE 구문 규칙은 아래와 같다.

 

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [CHARACTER SET charset_name] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...]

[각주:1]

 

기본적으로 csv 파일 이름과 테이블 이름만 입력해도 사용 가능하다.

이럴 경우 나머지 옵션들은 기본 옵션으로 실행되게 된다.

 

파일 이름은 경로와 함께 나타낼 수 있으며 상대 경로 절대 경로 다 사용 가능하다.

기본적으로 현재 데이터베이스가 있는 디렉토리가 기본 경로가 된다.

csv파일이 데이터베이스와 다른 경로에 있기 때문에 절대 경로로 나타낸다.

필드가 , 로 구분되어 있기 때문에 FIELDS TERMINATED BY ',' 옵션을 추가했다.

 

참고로 기본 필드 값은 아래와 같다.

 

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

 

 

응용프로그램에서 생성되는 많은 csv파일이 아래와 같은 포맷을 사용한다고 하니 참고하자.

 

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

 

 

csv파일의 첫 라인에는 필드 이름이 있었기 때문에 IGNORE 1 LINES"; 옵션도 추가했다.

IGNORE N LINES 옵션은 파일 N번째 라인부터 테이블로 저장하게 한다.

 

만약 테이블의 필드와 csv파일의 필드가 정확히 일치하면 그냥 아래와 같이 적어주면 된다.

 

$sql = "LOAD DATA INFILE '/var/lib/mysql-files/파일이름.csv'
     INTO TABLE `테이블이름`
     FIELDS TERMINATED BY ','
     IGNORE 1 LINES";
  

 

간혹 파일이 존재하는데 파일이 없다는 에러가 나올 수 있다. 



mysql> LOAD DATA INFILE '/home/changelist.csv' INTO TABLE changelist

    ->   FIELDS TERMINATED BY '\t'

    ->   IGNORE 1 LINES;

ERROR 29 (HY000): File '/home/changelist.csv' not found (Errcode: 13)



그럴 경우는 주로 권한의 문제로 파일이 있는 폴더나 파일 소유자를 mysql로 변경하면 해결 된다. 


chown mysql:mysql changelist.csv


 

그런데 나는 인덱스 필드를 추가했기 때문에 ( ) 옵션으로 필드들 이름을 하나하나 적어주기로 한다(...)

여기서 필드들 이름 순서는 파일에 나타나는 필드  순서와 동일해야 한다.


$sql = "LOAD DATA INFILE '/var/www/html/파일이름.csv'
     INTO TABLE `테이블이름`
     FIELDS TERMINATED BY ','
     IGNORE 1 LINES"

     (`날짜`,`가격`,... ,`시간`)";

 

if(mysqli_query($conn, $sql))
    echo "LOAD DATA INFILE successfully"; 
else
    echo "Error LOAD DATA INFILE:".mysqli_error($conn);

 

$conn->close();

 


필드 명까지 다 입력하고 php 파일을 실행하자 테이블이 생성되고 csv 파일이 테이블로 잘 변환된 것을 확인할 수 있었다 : )

 

만약 테이블에 null 값을 입력해야 한다면 (그리고 csv 파일에는 빈 입력값으로 들어있다면) 

빈 입력값을 \N으로 입력해야 한다



MySQL manual says:


When reading data with LOAD DATA INFILE, empty or missing columns are updated with ''. If you want a NULL value in a column, you should use \N in the data file. The literal word “NULL” may also be used under some circumstances.

So you need to replace the blanks with \N like this:


예시: 

1,2,3,4,5

1,2,3,\N,5

1,2,3



 

 

  1. 출처:http://dev.mysql.com/doc/refman/5.1/en/load-data.html [본문으로]
  • 2017.03.30 13:40 ADDR 수정/삭제 답글

    비밀댓글입니다

    • adana 2017.03.30 21:28 신고 수정/삭제

      네 이메일주소 알려주세요

[ php ] mysql / mysqli - select / insert / update 문 사용 예시

 

 

1. DB 연결하기


$conn = mysqli_connect('localhost', 'username', 'password','dbname');
if (mysqli_connect_errno($conn))
{
  echo "데이터베이스 연결 실패: " . mysqli_connect_error();
}
else
{

// to do something

}

 


2. select 문 사용


mysql_query 결과로 영향받은 row수를 알기 위해서는 mysqli_affected_rows 함수를 사용한다. 

- select 결과를 associative array로 반환받기 

$check_query = "SELECT * from ".$table." where code='".$code."' and period='".$period."'";

 $result=mysqli_query($conn,$check_query);

if(mysqli_affected_rows($conn) > 0)
{

  

 // mysqli_fetch_assoc 함수 

 // Fetch a result row as a numeric array and as an associative array:


 while ($row = mysqli_fetch_assoc($result))
 {
            $id = $row['id']."\t";
            $name = $row['name']."\t";
            $news .= $row['news'];

  }
                                 

}

mysqli_free_result($result);


 참고: http://www.w3schools.com/php/func_mysqli_fetch_array.asp

- sum 함수를 사용한 select 문 예시 


 $sum_query = "select Sum(`scores`) as value_sum from tablename";
        $result = $conn->query($sum_query);
        $row = mysqli_fetch_assoc($result);
        $sum_up_short = $row['value_sum'];



참고: http://www.tizag.com/mysqlTutorial/mysqlsum.php

3. insert 문 사용

$query = "INSERT INTO ".$table."(code, name_kor,name_eng, market, type, period,`".$field."`) VALUES ('".$code."','".$name_kor."','".$name_eng."','".$market."','".$type."','".$period."','".$value."');";

   if(mysqli_query($conn, $query))
       echo "insert successfully";
      else
       echo "Error:".$query."mesage:".mysqli_error($conn);  

3. update 문 사용

 $query = "UPDATE ".$tablename." SET `field0`='".$value0."' ,`field1`='".$value1."' ,`field2`='".$value2."' ,`".$field3."`='".$value3."' WHERE 날짜='".$time."' AND 종목코드='".$stockcode."'";
    
    if(mysqli_query($conn, $query))
       echo "update successfully";
      else
       echo "Error:".$query."mesage:".mysqli_error($conn);  
       

 

 4. DB 연결 닫기 

$conn->close();



 

리눅스와 mysql의 max_open_file 사이즈 변경하기







환경: 리눅스 CentOS 


새 서버를 받아 max open file 관련하여 설정을 하여 기록을 남긴다. 

mysql에서는 보통 max open file 기본 값으로 1024이 설정되어 있는데 

값이 낮아 mysql 작업 속도를 느리게 하는 원인이 되기도 하니 적절하게 값을 조정해보도록 하자. 



1.mysql의 max open file 설정 확인


일단 mysql 의 현재 max open file설정부터 확인해보자.

ps afx | grep mysql 로 해당 프로세스의 id를 알아내고 프로세스의 설정값을 확인해본다. 

1024(soft), 4096(hard) 값이 설정되어 있다.  



shell> ps afx | grep mysql

48320 pts/3    S+     0:00                      \_ grep mysql

21919 pts/0    S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql

22021 pts/0    Sl     0:03  \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock


shell> cat /proc/22021/limits

Limit                     Soft Limit           Hard Limit           Units

Max cpu time              unlimited            unlimited            seconds

Max file size             unlimited            unlimited            bytes

Max data size             unlimited            unlimited            bytes

Max stack size            10485760             unlimited            bytes

Max core file size        0                    unlimited            bytes

Max resident set          unlimited            unlimited            bytes

Max processes             30122                30122                processes

Max open files            1024                 4096                 files

Max locked memory         65536                65536                bytes

Max address space         unlimited            unlimited            bytes

Max file locks            unlimited            unlimited            locks

Max pending signals       30122                30122                signals

Max msgqueue size         819200               819200               bytes

Max nice priority         0                    0

Max realtime priority     0                    0

Max realtime timeout      unlimited            unlimited            us

[root@quick141-176 ~]#


mysql>  show variables like 'max_connections';

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

| Variable_name   | Value |

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

| max_connections | 151   |

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

1 row in set (0.00 sec)




또는 mysql 에서 직접 open_files_limit 변수값을 확인할 수도 있다. 



mysql> show global variables like 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)




현재 기본 값은 너무 작으니 값을 증가해야 겠다. 
그리고 mysql 의 max open file값은 시스템이나 사용자에게 기존에 할당된 값 이상으로 변경할 수 없으니 

mysql max open file 값을 변경하기 이전에 관련 설정들을 확인 및 변경해보자. 


 

2. OS의 max open file 설정 확인 및 변경 


일단 서버의 기본 max open file 설정부터 확인해보자. 

file-max 값은 리눅스에서 한 번에 운용할 수 있는 파일 수를 의미하며, 

보통 4MB 메모리당 256개의 파일을 운용할 수 있다고 한다.

대략 1G -> 65536개,  2G -> 131072 개 이다. 

 



shell> cat /proc/sys/fs/file-max

382386

 



이미 충분한 값이 설정되어 있는 것 같다. 

만약 위에 나온 값을 변경하고 싶으면 터미널에서 아래와 같이 입력하면 된다.  

그러나 재부팅시 이 값은 유지가 되지 않으니 유의하자. 


shell> echo 382386 > /proc/sys/fs/file-max



재부팅시에도 이 값을 계속 유지하려면 

/etc/sysctl.conf 파일에서 아래와 같이 file-max값 설정을 추가한다. 



# increase max open file

fs.file-max = 382386



그리고 sysctl -p 를 입력하여 설정값이 즉각 변경하도록 한다. 

이 값은 서버가 재시작을 해도 계속 유지된다.




shell> sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
error: "net.bridge.bridge-nf-call-ip6tables" is an unknown key
error: "net.bridge.bridge-nf-call-iptables" is an unknown key
error: "net.bridge.bridge-nf-call-arptables" is an unknown key
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.file-max = 382386







3. 사용자별 max open file 설정 확인 및 변경 



사용자별 프로세스(task)의 max open file 설정을 확인하려면 터미널에서 ulimit -a를 입력한다.




shell> ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 30122
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 30122
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited



open file 값이 기본값인 1024으로 설정되어 있어 64000으로 변경하려고 한다. 

ulimit의 값을 변경하는 방법은 두가지가 있다. 


터미널에서 아래와 같이 ulimit -n 옵션으로 변경할 수 있다. 

현재 로그인되어 있는 사용자에 한해 변경이 되며 로그아웃시에는 설정한 값이 유지가 되지 않으니 유의한다.  



shell> ulimit -n64000



다시 ulimit -a를 입력하여 해당 값이 제대로 변경된 것을 확인하였다. 




shell>  ulimit -a

core file size          (blocks, -c) 0

data seg size           (kbytes, -d) unlimited

scheduling priority             (-e) 0

file size               (blocks, -f) unlimited

pending signals                 (-i) 30122

max locked memory       (kbytes, -l) 64

max memory size         (kbytes, -m) unlimited

open files                      (-n) 64000

pipe size            (512 bytes, -p) 8

POSIX message queues     (bytes, -q) 819200

real-time priority              (-r) 0

stack size              (kbytes, -s) 10240

cpu time               (seconds, -t) unlimited

max user processes              (-u) 30122

virtual memory          (kbytes, -v) unlimited

file locks                      (-x) unlimited



ulimit -n의 변경값은 일시적이기 때문에 

이 값을 계속 유지하고 싶으면 /etc/security/limits.conf 파일의 내용을 수정해야 한다. 

nofile은 해당 사용자(그룹)이 오픈할 수 있는 최대 파일 개수를 의미한다.

/etc/security/limits.conf 값은 새로운 session 부터 영향을 끼치기 때문에

파일 설정을 변경하고 재로그인을하거나 새로운 세션으로 다시 접속하면

 해당 설정이 변경된 것을 확인할 수 있다.


참고로 root user 는 * wildcard 에 속하지 않으니 

root user   는 따로 설정해줘야한다.


# 전체 user에게 같은 설정을 하려면 * 을 사용한다


* hard nofile 64000

* soft nofile 64000

root hard nofile 64000

root soft nofile 64000



# 특정 user (예: mysql)에기만 설정을 하려면


mysql hard nofile 64000

mysql soft nofile 64000




만약 설정을 변경하였는데 ulimit -a    해도 동일한 결과가 나온다면

/etc/pam.d/su 의 아래 라인을 코멘트 아웃해야한다. 

limits.conf   는  pam_limits.so가 사용하는 설정값이라 

/etc/pam.d/su에서 사용되도록 설정되었는지 확인해야한다.



# Sets up user limits, please uncomment and read /etc/security/limits.conf

# to enable this functionality.

# (Replaces the use of /etc/limits in old login)

session    required   pam_limits.so








4. mysql max open file 설정 변경 

 

자 드디어 mysql max open file 설정을 변경할 차례가 왔다. 

/etc/my.cnf 파일에 아래와 같이 open-file-limit 옵션을 수정하자. 



[mysqld_safe]

open-files-limit=64000




그리고 mysqld 서비스를 재시작한다. 



shell> service mysqld restart
mysqld 를 정지 중:                                         [  OK  ]
mysqld (을)를 시작 중:                                     [  OK  ]




마지막으로 수정된 값을 확인하고 설정을 마무리한다. 



mysql> show global variables like 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 64000 |
+------------------+-------+
1 row in set (0.00 sec)






참고사이트:

http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit

http://blog.endpoint.com/2013/12/increasing-mysql-55-maxconnections-on.html

https://dev.mysql.com/doc/refman/5.0/en/linux-postinstallation.html

http://jikime.tistory.com/44






  • adana 2015.10.25 13:51 신고 ADDR 수정/삭제 답글

    https://dev.mysql.com/doc/refman/5.0/en/linux-postinstallation.html

c#에서 mysql (MySqlCommand) 사용하기 예시

 



c#에서 MySQL을 엑세스하기 위해서는 일반적으로 MySQL을 위한 ADO.NET 드라이버인 MySQL Connector/Net을 사용하는데, 이 MySQL Connector/Net은 MySQL 웹사이트에서 무료 다운 받아 설치할 수 있다. 드라이버를 설치한 후에 C# 프로젝트에서 MySql.Data.dll를 참조한 후 MySql.Data.MySqlClient 네임스페이스를 참조하면, MySQL버젼의 ADO.NET 클래스들 (예: MySqlConnection, MySqlCommand, MySqlDataReader 등)을 이용할 수 있다. 


1. mysql connectin을 만든다. connection이 정상적으로 열리지 않을 경우 에러 메세지 출력

 

 

            MySqlConnection conn = new MySqlConnection("SERVER=server ip or address(ex:kugancity.tistory.com);DATABASE=dbname;UID=user_id;PWD=password;");

            try
            {
                // 데이터베이스를 open 한다. 
                conn.Open();
                Console.WriteLine(string.Format("connection is '{0}'", conn.State));

             }
            catch (Exception ex)
            {
                // 연결되지 못했거나, 오류가 발생하면 출력한다. 
                MessageBox.Show("can not open connection!");
                string line = string.Format("{0},", ex);
                MessageBox.Show(line);
            }

 

2. MySqlCommand 클래스 사용하여 데이터베이스 operation 작업하기 


MysqlCommand object를 사용하여 데이터베이스 operation을 수행할 수 있습니다. 

MysqlCommand object에서 자주 사용하는 세 가지 함수는 아래와 같습니다. 

   

    ExecuteReader - 데이터를 받아오는 쿼리문에 사용합니다. 데이터는 MySqlDataReader 라는 클래스의 객체로 리턴됩니다. 

    ExecuteNonQuery - 데이터 삽입/삭제 시 사용합니다. 

    ExecuteScalar - 하나의 값이 리턴되는 쿼리문에 사용합니다. 



아래는 각 함수를 사용하는 예시들 입니다. 


- 테이블 데이터 받아오기  (ExecuteReader 함수 사용)


   MySqlDataReader 클래스는 SQL Server와 연결을 유지한 상태에서 한번에 한 레코드(One Row)씩 데이타를 가져오는데 사용됩니다.      MySqlCommand.ExecuteReader()로부터 리턴되는 MySqlDataReader 객체는 (파일의 BOF와 같이) 첫 Row 이전에 포인터를 위치시키기 때문에 개발자는 MySqlDataReader의 Read()메서드를 써서 처음 Row로 이동해 주어야 합니다. MySqlDataReader는 하나의 Connection에 하나만 Open되어 있어야 하며, 사용이 끝나면 Close() 메서드를 호출하여 닫아 줍니다. 


 

          

try

        {

            string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent='Oceania'";

            MySqlCommand cmd = new MySqlCommand(sql, conn);

            MySqlDataReader rdr = cmd.ExecuteReader();


            while (rdr.Read())

            {

                Console.WriteLine(rdr[0]+" -- "+rdr[1]);

            }

            rdr.Close();

        }

        catch (Exception ex)

        {

            Console.WriteLine(ex.ToString());

        }


 

만약 이미 DataReader가 하나의 connection에 열려있는데 이 datareader instance나 connection을 다시 사용하려고 하면 아래와 같은 에러가 발생하니 유의하자. 


there is already an open datareader associated with this connection which must be closed first


이 에러가 발생하는 원인과 해결방법은 다른 포스팅에서 자세하게 설명을 해 놓았습니다. 



- 특정 테이블의 전체 row 개수 구하기. (ExecuteScalar 함수 사용)

 

            MySqlCommand cmd = new MySqlCommand("select count(*)  from table_name;", conn);
            try
            {
                index = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
            }
            catch (Exception ex)
            {
                // 연결되지 못했거나, 오류가 발생하면 출력한다. 
                Console.WriteLine("{0} Exception caught.", ex);
            }

 

 

-  insert 문 수행 (ExecuteNonQuery 함수 사용)

 

                MySqlCommand cmd_insert = new MySqlCommand(total_line, conn);
                try
                {
                    cmd_insert.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    // 연결되지 못했거나, 오류가 발생하면 출력한다. 
                     string line = string.Format("{0},", ex);
                    MessageBox.Show(line);
                 }

 

3. connection 닫기

 

 

 // 데이터베이스를 close 한다.  
 conn.Close();
 Console.WriteLine(string.Format("connection is '{0}'", conn.State));

 


참고: http://www.csharpstudy.com/Practical/Prac-mysql.aspx

https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-sql-command.html

https://blogs.msdn.microsoft.com/spike/2009/08/20/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-closed-first-explained/

http://www.csharpstudy.com/Data/SQL-command.aspx



  • 2018.10.01 14:50 ADDR 수정/삭제 답글

    비밀댓글입니다

ubuntu에서 방화벽 설정 (UFW)



Ubuntu에서 기본적으로 제공하는 방화벽은 UFW(Ubuntu Firewall) 이다. 

iptables을 보다 쉽게 사용하도록 개발된 툴로 사용법도 좀 더 간편하다. 



일단 ufw status를 사용하여 활성화 여부를 체크한다. 

기본적으로 비활성화 되어 있기 때문에 ufw enable를 사용하여 ufw를 활성화한다. 

ufw를 활성화하면 방화벽이 작동하기 떄문에 모든 포트가 자동으로 막히게 된다. 

방화벽 설정을 하지 않고 그대로 종료하면 다음에 ssh 접근을 못할 수도 있으니 유의하자. 




# ufw status

Status: inactive


# ufw enable

Command may disrupt existing ssh connections. Proceed with operation (y|n)? y

Firewall is active and enabled on system startup







ufw default deny/allow를 입력하여 기본적인 방화벽 방침을 입력한다. 

ufw logging on을 입력하여 로그를 남기도록 변경한다. 





# ufw default deny
Default incoming policy changed to 'deny'
(be sure to update your rules accordingly)


# ufw logging on
Logging enabled





이제 사용하는 포트들을 열어보자. 

ufw allow의 기본적인 문법은 아래와 같다. 


ufw allow <port>/<optional: protocol>


ftp. ssh, http, mysql port를 차례대로 열어준다. 

그리고 ufw status numbered를 사용하여 입력한 룰들을 확인해본다. 




# ufw allow 21
Rule added
Rule added (v6)
# ufw allow 22
Rule added
Rule added (v6)
# ufw allow 80
Rule added
Rule added (v6)
# ufw allow 3306
Rule added
Rule added (v6)



# ufw status numbered
Status: active

     To                         Action      From
     --                         ------      ----
[ 1] 21                         ALLOW IN    Anywhere
[ 2] 22                         ALLOW IN    Anywhere
[ 3] 80                         ALLOW IN    Anywhere
[ 4] 3306                       ALLOW IN    Anywhere
[ 5] 21 (v6)                    ALLOW IN    Anywhere (v6)
[ 6] 22 (v6)                    ALLOW IN    Anywhere (v6)
[ 7] 80 (v6)                    ALLOW IN    Anywhere (v6)
[ 8] 3306 (v6)                  ALLOW IN    Anywhere (v6)




참고로 룰을 삭제하고 싶을 경우에는 ufw delete rule_number 를 사용하면 된다. 



# ufw delete 8

Deleting:

 allow 3306

Proceed with operation (y|n)? y

Rule deleted (v6)



참고: http://squall.kr/363



리눅스에 kibana 4.1.2 설치하기






elasticsearch도 설치했으니 이제 키바나 4.1.2을 설치하려고 한다. 

키바나 4.1 버전은 elasticsearch 1.4.4 를 포함한 이 후 버전에서만 사용할 수 있으니 참고하자. 



키바나 다운로드 사이트 바로가기 





운영체제에 맞게 LINUX 64-BIT 나 32-BIT를 클릭한다. 

연결된 다운로드 사이트에서 나오는 url을 wget으로 받은후 압축을 푼다.



# wget https://download.elastic.co/kibana/kibana/kibana-4.1.2-linux-x64.tar.gz

--2015-10-15 18:10:30--  https://download.elastic.co/kibana/kibana/kibana-4.1.2-linux-x64.tar.gz

Resolving download.elastic.co... 23.21.139.229, 50.16.192.199, 184.73.241.51, ...

Connecting to download.elastic.co|23.21.139.229|:443... connected.

HTTP request sent, awaiting response... 200 OK

Length: 11787239 (11M) [application/octet-stream]

Saving to: `kibana-4.1.2-linux-x64.tar.gz'


100%[==========================================================================>] 11,787,239  2.38M/s   in 5.6s


#tar zxfv kibana-4.1.2-linux-x64.tar.gz






./conf/kibana.yml을 열어 필요하면 설정을 수정한다. 

elasticsearch의 hostname과 port 및 키바나의 port 번호를 여기서 설정할 수 있다. 



# Kibana is served by a back end server. This controls which port to use.

port: 5601


# The host to bind the server to.

host: "0.0.0.0"


# The Elasticsearch instance to use for all your queries.

elasticsearch_url: "http://localhost:9200"





bin/kibana를 실행한다. 



# ./kibana

{"name":"Kibana","hostname":"q..","pid":17380,"level":30,"msg":"No existing kibana index found","time":"2015-10-15T10:24:46.640Z","v":0}

{"name":"Kibana","hostname":"q..","pid":17380,"level":30,"msg":"Listening on 0.0.0.0:5601","time":"2015-10-15T10:24:46.645Z","v":0}




이렇게 실행할 경우 터미널 창이 종료되면 kibana실행도 종료가 된다. 

ssh 세션이 끝나도 남아있도록 하려면 아래와 같이 실행하도록 한다. 



sh kibana-4.1.2-linux-x64/bin/kibana >> /var/log/kibana.log(or /dev/null) &




마지막으로 웹브라우저에서 http://yourhost:5601(또는 입력한 키바나 포트번호)를 입력하여 

아래와 같은 설정화면이 뜨는지 확인해본다.  




다음 포스팅에서는 키바나 설정을 해보겠다. 







elasticsearch 시작하기 - bulk index 로 색인하기

 

 

기본 사용법

 

D:\es\elasticsearch-1.4.2>curl -s -XPOST "http://localhost:9200/_bulk" --data-binary @bulkdata.json

 

--data-binary 옵션뒤에 색인할 bulk file을 지정한다.

bulk 색인중에는 bulk file이 다 메모리에 로드되기 때문에

적은 사이즈 (20~30 M)로 나눠서 색인을 하도록 색인 스크립트를 작성하였다.

 

 

Linux -command 


D:\es\elasticsearch-1.4.2>curl -s -XPOST "http://localhost:9200/_bulk" --data-binary @bulkdata.json


 

 

리눅스에서는 주소에 ' 나 "를 붙여도 잘 되지만 윈도우에서는 에러가 나거나 작동 안함


윈도우 - command line


D:\es\elasticsearch-1.6.0\bin>curl -s -XPOST "localhost:9200/_bulk" --data-binary @bulkdata.json

{"error":"ElasticsearchParseException[Failed to derive xcontent from org.elastic

search.common.bytes.BytesArray@1]","status":400}


D:\es\elasticsearch-1.6.0\bin>curl -s -XPOST 'localhost:9200/_bulk' --data-binary @bulkdata.json

작동안함


D:\es\elasticsearch-1.6.0\bin>curl -s -XPOST localhost:9200/_bulk --data-binary@bulkdata.json

제대로 동작


 

 

bulk file (예시)

{"index":{"_index":"indexname","_type":"typename","_id":"1"}}
{"id":"981146","날짜":"2014-01-01",... ,"총주식수":null}

 

- doc이후의 마지막 라인에 빈 라인이 있지 않으면 no_requests added 에러가 나니 조심할 것. (참고:

http://stackoverflow.com/questions/22996337/elasticsearch-no-requests-added-bulk-api-error)

 

{ "error": "ActionRequestValidationException[Validation Failed: 1: no requests added;]", "status": 500 }

 

 

리눅스 전체 bulk index 스크립트

 

 

#!/bin/sh


today=`date '+%Y%m%d'`
jsondir=/home/json/$today
bulkdir=/home/bulk/$today
mkdir ${jsondir}
mkdir ${bulkdir}

// table에서 bulk file을 생성하는 스크립트
php extract_bulk_from_table.php > ${jsondir}/bulkdata.json
split_bulk.sh 200000 ${jsondir}/bulkdata.json
curl -XDELETE localhost:9200/indexname

// mapping 설정
curl -s -XPOST 'http://localhost:9200/indexname' --data-binary @mapping_unit.json

for entry in $bulkdir/*
do
        echo "$entry"
        curl -s -XPOST 'http://localhost:9200/_bulk' --data-binary @${entry}
done


 

extract_bulk_from_table.php

 

<?php

ini_set('memory_limit', '-1');
header("Content-Type: text/html; charset=UTF-8");
$conn = mysqli_connect('localhost', 'id', 'passwd','dbname');
if (mysqli_connect_errno($conn))
{
        echo "데이터베이스 연결 실패: " . mysqli_connect_error();
}
 

  $check_query = "select * from tablename";

  $result = $conn->query($check_query);

  $total_line = "";
  while($row = mysqli_fetch_assoc($result)) {

                   $type = $row['종목코드'];
                   $total_line = "{\"index\":{\"_index\":\"indexname\",\"_type\":\"".$type."\",\"_id\":\"".$id++."\"}}\n";
                   $total_line .= json_encode($row, JSON_UNESCAPED_UNICODE );
                   $total_line .= "\n";
                   echo $total_line;
                }


 

?>

 

 

mapping_unit.json

 

{
    "settings" : {
      "analysis": {
          "filter": {
            "my_shingle_filter": {
               "type": "shingle",
               "output_unigrams": false
            }
          },
          "analyzer" : {
            "my_shingle_analyzer": {
               "type": "custom",
               "tokenizer" : "standard",
               "filter": [
                  "my_shingle_filter"
               ]
            }
          }
      }
    },
    "mappings" : {
      "_default_" : {
        "properties" : {
          "4일종가합" : {
            "type" : "string"
          },

          "종목" : {
            "type" : "string",
            "index" : "not_analyzed"
          },
          ....  
          "현재가" : {
            "type" : "string"
          }
        }
      }
}
}

 

 


elasticsearch - mapping 확인하기






엘라스틱 검색에서 현재 mapping을 확인하고 변경하는 방법을 정리해보았다. 


현재 mapping 확인하기


- web browser에서 확인하기 


http://(localhost or ip):(port)/indexname/_mapping/typename/


예시 ) 


http://localhost:9200/test/_mapping/companyname1/


- linux 에서 확인하기 


curl -XGET 'http://(localhost or ip):(port)/indexname/_mapping/typename?pretty'


예시) 

curl -XGET 'http://125.456.789.01:9200/test/_mapping?pretty'


- 리눅스에서 현재 mapping을 json 파일로 저장하기 

curl -XGET 'http://125.456.789.01:9200/test/_mapping/companyname1?pretty' > current_mapping.json


예시) mapping 저장 결과 


{

  "test" : {

    "mappings" : {

      "companyname1" : {

        "properties" : {

          " 분기경상이익" : {

            "type" : "string"

          }, 

        ...

        "news_line" : {

            "type" : "string"

          },

    }
}





참고: 


https://www.elastic.co/guide/en/elasticsearch/reference/current/analysis-cjk-bigram-tokenfilter.html


https://www.elastic.co/guide/en/elasticsearch/guide/current/mapping-intro.html



shell script로 이메일 보내기



뉴스 업데이트 결과를 메일로 보내는 쉘 스크립트를 작성하였다. 

스크립트를 실행하기 전에 mail 프로그램이 깔려있어야 한다. 



#!/bin/bash

subject="today update summary"

email="user@naver.com"

filename="/tmp/message.txt"

updated_cnt=`grep -c "there is new" /home/log/sort_date.log`

echo "updated news count:${updated_cnt}" > ${filename}


#메일 발송하기

mail -s "$subject" "$email" < $filename





참고로 메일설치 삽질과정도 기록해둔다. 


apt-get install mailutils



위 명령어를 입력하면 아래 설정창이 나온다. 

이 중에서 하나를 선택해야 한다. 


SMTP를 사용해서 메일을 보내고 받으려고 하면 

Internet Site   를 선택하면 된다. 







기존 설정이 없던 상태에서 No configuration 을 선택했더니 아래와 같은 에러가 발생하였다. 

참고로 /var/log/mail.err 에서 메일 관련 에러 메세지를 확인할 수 있다. 





#echo "Test" | mail -s "Test" username@naver.com

mail: cannot send message: Process exited with a non-zero status



/var/log# ll *mail*

-rw-r----- 1 syslog adm 214 12월 25 16:33 mail.err

-rw-r----- 1 syslog adm 214 12월 25 16:33 mail.log



vi mail.err

postfix/sendmail[11425]: fatal: open /etc/postfix/main.cf: No such file or directory

postfix/sendmail[11453]: fatal: open /etc/postfix/main.cf: No such file or directory






검색해보니 메일 보내기 설정이 제대로 되어있지 않아서 그런것이라고 해서 

sudo dpkg-reconfigure postfix

로 다시 위 설정창을 띄운 후 Internet site로 설정을 변경했다. 


참고: http://jonsview.com/how-to-setup-email-services-on-ubuntu-using-postfix-tlssasl-and-dovecot



General type of mail configuration? Internet Site
System mail name? server1.example.com
Root and postmaster mail recipient? Leave blank
Other destinations to accept mail for? server1.example.com, example.com, localhost.example.com, localhost
Force synchronous updates on mail queue? No
Local networks? Leave default (127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128)
Mailbox size limit (bytes)? 0
Local address extension character? Leave default (+)
Internet protocols to use? ipv4 (most likely)



설정 변경후 정상적으로 메일 프로그램이 작동하는 것을 확인하였다. 




리눅스(centos)에 phpmyadmin 설치하기



이미 APM 설치가 되어있는 서버에 phpmyadmin를 추가로 설치하였다.


서버: CentOS 




shell> yum install epel-release

shell> yum update

shell> yum install phpmyadmin





보통 로컬서버에서 접속하지 않으면 아래와 같은 접속 권한이 없다는 오류가 나온다. 



Forbidden


You don't have permission to access /phpmyadmin/ on this server.




그럴 경우 phpmyadmin의 설정파일에서 phpmyadmin에 접근할 수 있는 ip를 추가하도록 하자. 

또는 보안에는 약해지겠지만 ip 제한 정책을 아예 없앨 수 도 있다. 

아래는 phpmyadmin의 기본 설정파일 내용이다. 


vi /etc/httpd/conf.d/phpMyAdmin.conf





Alias /phpMyAdmin /usr/share/phpMyAdmin

Alias /phpmyadmin /usr/share/phpMyAdmin


<Directory /usr/share/phpMyAdmin/>

   AddDefaultCharset UTF-8


   <IfModule mod_authz_core.c>

     # Apache 2.4

     <RequireAny>

       Require ip 127.0.0.1

       Require ip ::1

     </RequireAny>

   </IfModule>

   <IfModule !mod_authz_core.c>

     # Apache 2.2

     Order Deny,Allow

     Deny from All

     Allow from 127.0.0.1

     Allow from ::1

   </IfModule>

</Directory>





예시 1 ) 223.170.188.0/24 대역대의 ip전체 접속 허용 



Alias /phpMyAdmin /usr/share/phpMyAdmin

Alias /phpmyadmin /usr/share/phpMyAdmin


<Directory /usr/share/phpMyAdmin/>

   AddDefaultCharset UTF-8


   <IfModule mod_authz_core.c>

     # Apache 2.4

     <RequireAny>

       Require ip 127.0.0.1, 223.170.188.0/24

       Require ip ::1

     </RequireAny>

   </IfModule>

   <IfModule !mod_authz_core.c>

     # Apache 2.2

     Order Deny,Allow

     Deny from All

     Allow from 127.0.0.1, 223.170.188.0/24

     Allow from ::1

   </IfModule>

</Directory>




예시 2 ) ip 접속 제한 없앰



<Directory /usr/share/phpMyAdmin/>

   AddDefaultCharset UTF-8


   <IfModule mod_authz_core.c>

     # Apache 2.4

     <RequireAny>

       Require all granted

       Require ip 127.0.0.1

       Require ip ::1

     </RequireAny>

   </IfModule>

   <IfModule !mod_authz_core.c>

     # Apache 2.2

     Order Allow,Deny

     Allow from All

     Allow from 127.0.0.1

     Allow from ::1

   </IfModule>

</Directory>




마지막으로 설정파일에서 기본 alias를 설정한다. 

alias는 phpmyadmin 접속주소인데 보통 http://yourdomain/phpmyadmin 이 기본 phpmyadmin의 접속 주소가 된다. 


기본 설정은 아래와 같지만  자신만이 알수 있는 alias로 변경하는것이 보안에 좋다. 

Alias /phpMyAdmin /usr/share/phpMyAdmin

Alias /phpmyadmin /usr/share/phpMyAdmin 



참고로 예전 서버에 남아있던 해킹시도 로그 기록들이다. 

phpmyadmin에 대한 다양한 주소로 접속을 시도하는 것을 확인할 수 있다. 

여기에서 언급되는 주소를 피해서 alias를 설정하는 것이 좋을 것 같다. 




[Tue Jul 07 14:09:28 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/admin

[Tue Jul 07 14:09:31 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/administrator

[Tue Jul 07 14:09:31 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/database

[Tue Jul 07 14:09:35 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/db

[Tue Jul 07 14:09:37 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/dbadmin

[Tue Jul 07 14:09:37 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/myadmin

[Tue Jul 07 14:09:37 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/myadminphp

[Tue Jul 07 14:09:38 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/mysql-admin

[Tue Jul 07 14:09:39 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/mysql

[Tue Jul 07 14:09:41 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/mysql

[Tue Jul 07 14:09:42 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/mysqladmin

[Tue Jul 07 14:09:42 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/mysqlmanager

[Tue Jul 07 14:09:43 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/php-my-admin

[Tue Jul 07 14:09:43 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/php-myadmin

[Tue Jul 07 14:09:43 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpMyAdmin-2

[Tue Jul 07 14:09:44 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpMyAdmin-3

[Tue Jul 07 14:09:44 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpMyAdmin-4

[Tue Jul 07 14:09:52 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpMyAdmin2

[Tue Jul 07 14:09:52 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpMyAdmin3

[Tue Jul 07 14:09:53 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpMyAdmin4

[Tue Jul 07 14:09:53 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpMyadmin

[Tue Jul 07 14:09:53 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpmanager

[Tue Jul 07 14:09:54 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpmy-admin

[Tue Jul 07 14:09:54 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpmy

[Tue Jul 07 14:09:55 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpmyAdmin

[Tue Jul 07 14:09:55 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpmyadmin

[Tue Jul 07 14:09:55 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpmyadmin1

[Tue Jul 07 14:09:56 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpmyadmin2

[Tue Jul 07 14:09:56 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpmyadmin3

[Tue Jul 07 14:09:57 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phpmyadmin4

[Tue Jul 07 14:09:57 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/phppma

[Tue Jul 07 14:09:57 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/pma

[Tue Jul 07 14:09:58 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/pma2011

[Tue Jul 07 14:09:58 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/pma2012

[Tue Jul 07 14:09:58 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/pma2013

[Tue Jul 07 14:09:59 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/pma2014

[Tue Jul 07 14:09:59 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/pma2015

[Tue Jul 07 14:10:00 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/program

[Tue Jul 07 14:10:00 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/shopdb

[Tue Jul 07 14:10:00 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/sql

[Tue Jul 07 14:10:01 2015] [error] [client 54.173.63.20] File does not exist: /var/www/html/sql


..

[Thu Jun 25 15:16:23 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.10.0.0

[Thu Jun 25 15:16:24 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.10.0.1

[Thu Jun 25 15:16:24 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.10.0.2

[Thu Jun 25 15:16:25 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.10.0

[Thu Jun 25 15:16:25 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.10.1.0

[Thu Jun 25 15:16:26 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.10.2.0

[Thu Jun 25 15:16:26 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.11.0.0

[Thu Jun 25 15:16:27 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.11.1-all-languages

[Thu Jun 25 15:16:27 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.11.1.0

[Thu Jun 25 15:16:28 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.11.1.1

[Thu Jun 25 15:16:28 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.11.1.2

[Thu Jun 25 15:16:29 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.6.1-pl2

[Thu Jun 25 15:16:29 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.6.1-pl3

[Thu Jun 25 15:16:30 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.6.4-pl3

[Thu Jun 25 15:16:30 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.6.4-pl4

[Thu Jun 25 15:16:31 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.6.4-rc1

[Thu Jun 25 15:16:31 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.6.5

[Thu Jun 25 15:16:31 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.6.6

[Thu Jun 25 15:16:32 2015] [error] [client 104.243.24.211] File does not exist: /var/www/html/phpMyAdmin-2.6.9





마지막으로 httpd 재시작하면 변경된 설정이 반영 된다. 


shell> service httpd restart

httpd 를 정지 중:                                          [  OK  ]

httpd (을)를 시작 중:                                      [  OK  ]