mysql 데이터베이스 백업 스크립트




mysqldump를 사용하여 데이터베이스를 매일 백업하는 쉘스크립트를 작성하였다. 



mysqldump 사용법 

   mysqldump는 DB에서 원하는 DB만, table만 백업 할 수 있고, 전체 DB를 백업 할 수 있다.


  (1) 전체 백업

    mysqldump -u계정이름 -p -A > all.sql


  (2) 특정 DB 백업

    mysqldump -u계정이름 -p 특정DB명 > 특정DB명.sql


  (3) 특정 DB에 특정 테이블 백업

    mysqldump -u계정이름 -p 특정DB명 특정table명 > 특정DB명.특정table.sql


  복원법

     mysql -u [userId] -p [password] [DB명] < 특정DB(특정table).sql


- DB 백업 스크립트 작성  

# vi /root/dbbackup.sh


mysqldump를 이용하여 디비나 테이블을 백업하고 백업한 파일은 gzip을 이용하여 압축하여 백업 폴더에 저장한다. 


- db 계정정보를 스트립트 안에 명시하는 경우 

  (스크립트에 명시적으로 계정정보를 입력하는 것은 보안상 위험 있음. 사용 예시로만 참고할 것


#!/bin/bash

DB_BACKUP="/home/dbbackup/"

DB_USER="username"

DB_PASSWD="passwd"

db="dbname"

table="tablename"


# Remove backups older than 3 days

find $DB_BACKUP -ctime +3 -exec rm -f {} \;




# 데이터베이스를 모두 백업할경우 

mysqldump --user=$DB_USER --password=$DB_PASSWD -A | gzip > "$DB_BACKUP/mysqldump-$db-$(date +%Y-%m-%d).gz";




# 데이터베이스를 백업할경우 

mysqldump --user=$DB_USER --password=$DB_PASSWD $db | gzip > "$DB_BACKUP/mysqldump-$db-$(date +%Y-%m-%d).gz";



# 데이터베이스의 특정 테이블을 백업할경우 

mysqldump --user=$DB_USER --password=$DB_PASSWD $db $table | gzip > "$DB_BACKUP/mysqldump-$db-$table-$(date +%Y-%m-%d).gz";






- db 계정정보를 my.cnt에 명시하는 경우 


my.cnt 




[mysqldump]


user=사용자이름

password=암호




스크립트 


#!/bin/bash

DB_BACKUP="/home/dbbackup/"

db="dbname"

table="tablename"


# Remove backups older than 3 days

find $DB_BACKUP -ctime +3 -exec rm -f {} \;




# 데이터베이스를 모두 백업할경우 

mysqldump  -A | gzip > "$DB_BACKUP/mysqldump-$db-$(date +%Y-%m-%d).gz";




# 데이터베이스를 백업할경우 

mysqldump  $db | gzip > "$DB_BACKUP/mysqldump-$db-$(date +%Y-%m-%d).gz";



# 데이터베이스의 특정 테이블을 백업할경우 

mysqldump  $db $table | gzip > "$DB_BACKUP/mysqldump-$db-$table-$(date +%Y-%m-%d).gz";









/root/dbbackup.sh 를 실행하여 정상적으로 스크립트가 작동하는지 확인한다. 



정상적으로 스크립트가 실행이 되면 보안을 위해 root만 수정하고 실행할수 있도록 권한 설정 

 chmod 700 /root/dbbackup.sh


- 크론택에 작업 등록 

#crontab -e



00 04 * * * /root/dbbackup.sh




매일 새벽 4시마다 디비 백업을 하도록 크론택을 등록하고 작업 완료. 



my.cnf 위치 찾기

 

 

 

리눅스에서 mysql 설정파일 (my.cnf) 위치가 기억이 안날경우

아래 명령어를 입력해서 나온 파일 위치 순서대로 찾아보면 된다.

 


# mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

 

 

 

Waiting for table metadata lock 대처방법



간혹 테이블을 수정하려고 할 때 이상하게 오래 걸려서 show processlist를 쳐보면 

작업의 status가 Wating for table metadata lock 일 경우가 있습니다. 


말 그대로 현재 metadata 가 lock이 걸려있는 상태라서 작업이 수행되지 못하고 있는 상태인데 

이것을 해결하기 위해서는 lock이 걸려있는 process를 강제로 종료하면 됩니다. 


일단 mysql command 창에서 show full processlist를 쳐서 전체 프로세스를 확인하고 

현재 오래 걸리고 있는 process id 이전에 있는 프로세스들 중 의심이 가는 프로세스를 kill process id 를 쳐서 종료시킵니다. 


아래와 같이 기존 프로세스를 종료시켰더니 곧바로 변경사항이 수정되었습니다. 






mysql> SHOW FULL PROCESSLIST;
+--------+------------+-----------------+------------+---------+------+---------------------------------+-----------------------------------------------------------------------------------------------+
| Id     | User       | Host            | db         | Command | Time | State                           | Info                                                                                          |
+--------+------------+-----------------+------------+---------+------+---------------------------------+-----------------------------------------------------------------------------------------------+
| 200609 | lla***       | localhost | realestate | Sleep   | 4729 |                                 | NULL                                                                                          |
| 200657 | r***       | localhost       | realestate | Query   |    0 | starting                        | SHOW FULL PROCESSLIST                                                                         |
| 200667 | p**** | localhost       | NULL       | Sleep   | 1255 |                                 | NULL                                                                                          |
| 200668 | r***      | localhost       | realestate | Query   | 1255 | Waiting for table metadata lock | ALTER TABLE `Table` CHANGE `column1` `column1` DECIMAL(10,2) NULL DEFAULT NULL |
+--------+------------+-----------------+------------+---------+------+---------------------------------+-----------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> kill 200609;
Query OK, 0 rows affected (0.00 sec)



sed로 " 안의 , 제거하기




load data infile로 csv 파일 내용을 mysql 테이블로 import했는데 

warning 개수가 넘 많다 -_-;; 






LOAD DATA INFILE '/var/lib/mysql-files/sample.csv' INTO TABLE sample

FIELDS TERMINATED BY ','

IGNORE 1 LINES;


Query OK, 12147957 rows affected, 65535 warnings (1 min 44.92 sec)





show warnings; 로 경고 메세지를 확인하니 아래와 같이 테이블의 input columns 개수보다 

칼럼 개수가 많다고 한다. 





 Warning | 1265 | Data truncated for column '전용면적' at row 3758                             |

| Warning | 1262 | Row 3758 was truncated; it contained more data than there were input columns |

| Warning | 1265 | Data truncated for column '전용면적' at row 3759                             |

| Warning | 1262 | Row 3759 was truncated; it contained more data than there were input columns |

| Warning | 1265 | Data truncated for column '전용면적' at row 3760                             |

| Warning | 1262 | Row 3760 was truncated; it contained more data than there were input columns |

| Warning | 1265 | Data truncated for column '전용면적' at row 3761                             |





귀찮지만 데이터 확인 ㄱㄱ

, 를 기준으로 필드를 분리하는데 건물 이름에 , 가 들어가 있어서 필드 개수가 더 많아진 것임 ㅇㅇ




2016,01,4211011200,강원 춘천 춘천로64번길 21-0,강원,춘천,효자,,0,688,6,,"엘피스빌(가,다)(구한전사택)",60.69,4,48000000

2016,01,4211011200,강원 춘천 춘천로64번길 21-0,강원,춘천,효자,,0,688,6,,"엘피스빌(가,다)(구한전사택)",60.69,3,49000000

2016,01,4211011200,강원 춘천 춘천로64번길 21-0,강원,춘천,효자,,0,688,6,,"엘피스빌(가,다)(구한전사택)",60.69,4,48000000




sed 로 " 안의 , 를 제거하였다. 




sed -e 's/\(".*\),\(.*"\)/\1 \2/g' sample.csv &> sample2.csv



-e 옵션은 정규식 옵션이고 

's/A/B/g' 는 A 패턴을 B 패턴으로 바꾸겠다는 것이다. 

()는 정규식 서브 패턴으로  A패턴에서 나오는 순서대로 B패턴에서 \1, \2 , 로 표시한다. 

참고로 \은 escape character이다.


위 식이 의미하는 것은 ,를 기준으로 앞의 "를 서브패턴1로 보고 

뒤 "를 서브패턴2으로 보아서 

최종적으로 " 문자열, 문자열 " 에서 ,를 제거하는 것이다. 


아래는 아래 정규식을 적용한 결과 파일이다. 





2016,01,4211011200,강원 춘천 춘천로64번길 21-0,강원,춘천,효자,,0,688,6,,"엘피스빌(가 다)(구한전사택)",51.81,1,40000000

2016,01,4211011200,강원 춘천 춘천로64번길 21-0,강원,춘천,효자,,0,688,6,,"엘피스빌(가 다)(구한전사택)",77.11,3,62000000

2016,01,4211011200,강원 춘천 춘천로64번길 21-0,강원,춘천,효자,,0,688,6,,"엘피스빌(가 다)(구한전사택)",77.11,4,60000000




이제 정상적으로 경고 없이 파일 내용이 테이블로 로드되었다. 






 



  • 2017.10.25 14:05 ADDR 수정/삭제 답글

    비밀댓글입니다

[ 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 신고 수정/삭제

      네 이메일주소 알려주세요

mysql int 타입 최대/최소 값




매번 테이블 만들때마다 검색하기 귀찮아서 

걍 블로그 저장용 




MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT. 

As an extension to the standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. 

The following table shows the required storage and range for each integer type.













참조: http://dev.mysql.com/doc/refman/5.5/en/integer-types.html

https://zetawiki.com/wiki/MySQL_%EC%9E%90%EB%A3%8C%ED%98%95




mysql 두 개의 테이블 데이터 사용하기 예시들





두 개의 테이블에서 칼럼들 읽어오기 (inner join) 




SELECT t1.*, t2.*

FROM table1 t1, table2 t2

WHERE t1.id = t2.t1_id




다른 테이블 정보로 칼럼 데이터 업데이트 하기 





UPDATE table1 t1, table2 t2

SET t1.field_to_change = t2.field_with_data

WHERE t1.field1 = t2.field2;







다른 테이블 필드 값을 추가하기 






INSERT INTO tbl_temp2 (fld_id)

  SELECT tbl_temp1.fld_order_id

  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;






특정 문자열을 select 문과 같이 사용할 경우에는 select 문의 필드명에서 그냥 특정 문자열을 입력하면 된다. 





INSERT INTO db2.b (x,y,z) SELECT x,y,4 FROM db1.a



테이블 2에 있는 데이터가 테이블 1에 없을 경우에만 (NOT EXISTS) 테이블 2 정보를 테이블 1로 입력하기.



insert into t1 ( `c1`, `c2`, `c3`, `c4`, `c5`) SELECT `c1`, `c2`, `c3`, `c4`, `c5` FROM t2   WHERE NOT EXISTS (SELECT * FROM t2                     WHERE t1.c1 =t2.`c2`);



테이블 2에 없는 데이트들만 테이블 1에서 선택하기. 


SELECT a.* FROM #test1 a WHERE NOT EXISTS (SELECT * FROM #test2 b WHERE a.type = b.type); 




처음에 위 mysql문에서 아래와 같이 사용하였더니 

SELECT * FROM #test1 a WHERE NOT EXISTS (SELECT * FROM #test2 b WHERE a.type = b.type); 

Unknown column in order clause 에러가 나왔다. 

order 문도 없는데 에러가 나와서 당황했는데 역시 스택오버플로에서 답을 찾았다 ㅠㅠ
결론만 적으면 phpmyadmin에서 첫번째 칼럼에 자동적으로 order by를 지정한다고 한다. 


The reason is that phpMyAdmin is adding an ORDER BY to your query for the first column in the results grid. Because of the alias, the code that does this fails.

This issue reproduces on phpMyAdmin 4.0.6. I don't know the status on the latest 4.2.5


참고: https://stackoverflow.com/questions/17312297/unknown-column-in-order-clause/24534013#24534013?newreg=74111d804c0445868c139e34ecdf4fa7




테이블 2에도 있는 데이터에만 정보를 테이블 1에 업데이트 하기. 



update table2 set `holiday`=1 where exists 

(select * from table1 where table1.date = table2.date)




[ 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

mysql 로그 확인 및 저장하기




1.   .mysql_history 파일을 확인


홈디텍토리에 있는 .mysql_history 파일에는 

사용자가 사용했던 query들이 아래와 같이 저장되어 있어 확인이 가능하다. 

로그인된 사용자 별로 확인이 가능하고 전체 sql command 확인은 불가능하다. 





show global status like 'open_%';

show variables like 'max_connections';

show status like 'Max_used_connections';

show global variables like 'open%';





2. mysql 로그 저장하도록 설정하기 


mysql server를 재시작하지 않아도 설정이 변경 가능하다. 

다만 서버를 재시작할 경우 같은 설정을 다시 해줘야한다. 


참고로 대용량의 쿼리 작업을 할 경우 로그가 저장되도록 설정되어 있으면 

속도가 느려질 수도 있으니 주의해야한다. 








# 로그 설정을 확인한다.

show variables where Variable_name in ('version', 'log', 'general_log');



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

| Variable_name | Value                   |

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

| general_log   | OFF                     |

| log           | OFF                     |

| version       | 5.5.46-0ubuntu0.14.04.2 |

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

3 rows in set (0.35 sec)



# OFF로 되어있는 general_log 설정을 1로 설정하여 로그를 저장하도록 변경한다. 

mysql> set global general_log = 1;

Query OK, 0 rows affected (0.00 sec)


mysql> show variables where Variable_name in ('version', 'log', 'general_log');

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

| Variable_name | Value                   |

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

| general_log   | ON                      |

| log           | ON                      |

| version       | 5.5.46-0ubuntu0.14.04.2 |

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

3 rows in set (0.01 sec)


# 저장한 로그를  mysql DB general_log 테이블에 저장하게 한다. 

mysql>  SET GLOBAL log_output = 'TABLE';



# slow log도 같이 설정해둔다. 

mysql> SET GLOBAL slow_query_log = 'ON';

Query OK, 0 rows affected (0.00 sec)





이렇게 저장된 로그는 아래와 같이 general_log 테이블에서 확인이 가능하다. 







<추가>



최근에 mysql 대용량의 쿼리 작업을 하다 반응 속도가 넘 느려서 

혹시 저번 로그 설정해둔 것 때문인가 하고 

관련 테이블 사이즈를 확인해보았다. 



mysql> SELECT TABLE_NAME AS "Tables",

    ->                      round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"

    -> FROM information_schema.TABLES

    -> WHERE table_schema = "mysql"

    -> ORDER BY (data_length + index_length) DESC;

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

| Tables                    | Size in MB |

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

| help_topic                |       0.48 |

| help_keyword              |       0.12 |

| help_relation             |       0.03 |

| db                        |       0.01 |

| proxies_priv              |       0.01 |

| proc                      |       0.00 |

| help_category             |       0.00 |

| tables_priv               |       0.00 |

| columns_priv              |       0.00 |

| procs_priv                |       0.00 |

| user                      |       0.00 |

| user.tmp                  |       0.00 |

| host                      |       0.00 |

| event                     |       0.00 |

| servers                   |       0.00 |

| plugin                    |       0.00 |

| ndb_binlog_index          |       0.00 |

| time_zone_transition_type |       0.00 |

| time_zone_transition      |       0.00 |

| time_zone_name            |       0.00 |

| func                      |       0.00 |

| time_zone_leap_second     |       0.00 |

| time_zone                 |       0.00 |

| slow_log                  |       0.00 |

| general_log               |       0.00 |

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

25 rows in set (0.10 sec)





???? ????????????


테이블 사이즈가 0으로 나와서 확인해보니 

sql에서 로그 csv파일로 접근만 해서 읽어오는 것이라고 한다. 

즉 파일로 가야 제대로 로그 사이즈를 확인할 수 있는것이다. 




The general_log table by default uses the CSV engine, which is literally just a full-blown CSV file on your drive, but can be 


accessed via SQL. This means its size limit is the size limit of files on your file system.


(참고: http://stackoverflow.com/questions/9343001/is-my-mysql-general-log-table-getting-too-big)


그래서 로그가 있는 디렉토리로 갔더니 로그 파일 사이즈만 11기가 OTL 

query문 하나하나마다 이 파일에 저장이 되고 있으니 

아무래도 이녀석이 범인(아니 내가;)인것 같다 ㅠㅠ





/var/lib/mysql/mysql# ll -h *log*

-rw-rw---- 1 mysql mysql   35  4월 15 17:06 general_log.CSM

-rw-rw---- 1 mysql mysql  11G  5월  9 15:27 general_log.CSV

-rw-rw---- 1 mysql mysql 8.6K 11월  3  2015 general_log.frm

-rw-rw---- 1 mysql mysql 8.6K 11월  3  2015 ndb_binlog_index.frm

-rw-rw---- 1 mysql mysql    0 11월  3  2015 ndb_binlog_index.MYD

-rw-rw---- 1 mysql mysql 1.0K 11월  3  2015 ndb_binlog_index.MYI

-rw-rw---- 1 mysql mysql   35  4월 16 01:01 slow_log.CSM

-rw-rw---- 1 mysql mysql 127K  5월  9 15:21 slow_log.CSV

-rw-rw---- 1 mysql mysql 8.8K 11월  3  2015 slow_log.frm

root@stock:/var/lib/mysql/mysql#



일단 general log 설정을 off로 변경하고 관련 파일은 내용을 지웠다. 



mysql> set global general_log = 0;

Query OK, 0 rows affected (0.00 sec)



mysql> show variables where Variable_name in ('version', 'log', 'general_log');

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

| Variable_name | Value                   |

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

| general_log   | OFF                     |

| log           | OFF                     |

| version       | 5.5.46-0ubuntu0.14.04.2 |

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

3 rows in set (0.01 sec)







Unless you're troubleshooting something, in my opinion there is no reason to keep the general log turned on. I would only turn it on if I were troubleshooting an issue, and then I'd turn it off when I was finished. I would suppose that it would improve performance with it off, especially if it's currently writing to a 20 GB logfile.


After you've turned off the general log, just delete that log from /var/lib/mysql



검색하다 찾은 troubleshooting을 하는 것이 아니면     
general log를 켜지 말라는 조언이 꽤 와닿았다. 




(참고: https://forums.cpanel.net/threads/large-general-log-file-how-to-clean-up-safely-and-stop-it-reoccuring.278392/) 






참고: http://www.koreaidc.com/bbs/set_view.php?b_name=idcpds&w_no=184