(─━┘_└━─)/ 머리만1톤(Head1Ton)

mysql DB에서 replication을 이용할 경우 bin-log를 활성화 시켜놔야 하는데

default 상태에서는 무한정 bin-log가 커지게 된다.

어느정도 운영한 DB 시스템의 <hostname>.bin  파일을 보면 많이 커져 있을 수 있다.

이때는 bin-log 크기를 줄이기 위한 옵션을 설정해 주면 된다.

/etc/my.cnf 파일 수정

binlog_cache_size      =  1M      # binlog cache 사이즈 
max_binlog_size        =  500M    # bin로그 maximum 사이즈 
expire_logs_days        = 3       # 로그 만료기간

로그 만료기간 수정법2 : mysql 접속후 수정하기

mysql> show variables;  

에서 나오는 expire_logs_days 항목을 확인하고

mysql> set global expire_logs_days=3;

과 같이 수정해준다.

바이너리 로그 리스트 확인

mysql> show binary logs;
mysql> show master logs;

바이너리 로그 삭제

mysql> purge master logs to 'mysql-bin.000010';

지정된 바이너리 로그 이하의 파일이 삭제됨.
mysql-bin.000001~0000009 까지 삭제됨.

mysqldumpslow -r -s c slow-query.log > parsed_slowquery.log

쿼리 실행 횟수(-s c)를 역순(-r) 

"mysqldumpslow -r -s c" 

쿼리의 잠금 시간

"mysqldumpslow -r -s l"(테이블 수준의 잠금만 해당)

쿼리로 조회한 레코드(row sent)

"mysqldumpslow -r -s r"

mysql 5.0 이상 버전에서 (5.0 미만에 메뉴얼이 없는 것으 보아)

timestamp 데이터 타입에 디폴트 값을 넣을 수 있다.

구문은 column_name timestamp default current_timestamp 이다.

timestamp 데이터 타입은 다른 타입과 다르게 기본이 not null 제약조건을 갖는다.

따라서 null 값을 허용하려면 column_name timestamp null default current_timestamp 처럼 null 을 명시해주어야 한다.

제약 조건이 not null 임에도 불구하고 insert 할 때 null 값을 넣을 수가 있다.

이 때에는 자동으로 default 값으로 들어간다.

또한 on update current_timestamp 구문을 추가로 사용할 수 있다. 

만약 row 가 update 된다면 자동으로 현재 시간으로 timestamp를 갱신해주는 구문이다.

mysql 5.6 이상 버전에서는 datetime 데이터 타입이도 default, on update 구문을 사용할 수 있게 되었다.

MySQL이 설치된 폴더의 'BIN' 로 이동하세요.

파일의 형태로 백업이 됩니다.

1. 백업 

MySQLDump -u사용자 -p암호 DB이름 > DB백업.sql

ex) mysqldump -uroot -p1234 test_db > test_db.sql -- ('test_db' 전체 내용을 백업)

2. 테이블 백업

MySQLDump -u사용자 -p암호 DB이름 TB이름 > TB백업.sql

ex) mysqldump -uroot -p1234 test_db test_table > test_table.sql -- ('test_db' 의 'test_table' 내용을 백업)

3. 데이터 백업

MySQLDump -u사용자 -p암호 DB이름 TB이름 -w "조건" > 데이터백업.sql

ex) mysqldump -uroot -p1234 test_db test_table -w "name='홍길동' > test_table.sql 

 -- ('test_db' 의 'test_table' 에서 'name' 이 홍길동인 데이터만 백업) 

4. 복원 (데이터 등록)

MySQL -u사용자 -p암호 DB이름 < DB백업.sql -- DB가 존재 하지 않는 경우 미리 생성 후 진행 하여야 함.

MySQL -u사용자 -p암호 DB이름 TB이름 < TB백업.sql

MySQL -u사용자 -p암호 DB이름 TB이름 < 데이터백업.sql

주의) 복원 명령 시 기존의 테이블은 제거 된 후 재생성 하여 복원 됩니다. (기존 데이터는 삭제됨) 

TIP) 파일명 자동 생성 명령을 위한 명령문 

ex) mysqldump -uroot -p1234 test_db > test_db_%date%.sql -- ('test_db_2013-04-02.sql' 형태로 생성됨.)   

아래와 같이 "backup.bat" batch 파일을 만들면 내가 원하는 형태의 날짜및시간을 표현 할 수 있다. 

----------------------------------------------------------------------------------

@echo off  

SET now=%date:~0,4% -- 2013-04-02 (0번째 부터 4개)
SET now=%now%%date:~5,2% -- 2013-04-02 (5번째 부터 2개)
SET now=%now%%date:~8,2% -- 2013-04-02
  

SET now=%now%_%time:~0,2% -- 14:10:10.10
SET now=%now%%time:~3,2% -- 14:10:10.10
SET now=%now%%time:~6,2% -- 14:10:10.10

mysqldump -uroot -p1234 %1 > %1_%now%.sql  -- %1 : 배치 파일 실행 시 포함 한 DB명

-----------------------------------------------------------------------------------

backup.bat test_db < 샐행  -- ('test_db_20130402_141010.sql' 형태로 생성됨.)   

[mysql row format]
    fixed : varchar로 지정해도 내부적으로 char로 처리
    dynamic : AVG_ROW_LENGTH(힌트 개념), MAX_ROW(크기 넘어가면 안됨)
    compressed : fixed나 dynamic을 압축해서 저장할수 있음. (read only)

1.mysql 쿼리 최적화.

    1.dynamic row는 AVG_ROW_LENGTH, MAX_ROW로 10%정도의 성능향상 기대할수 있음.

    2.pack keys : 인덱스 압축 -> 읽기는 향상, 쓰기는 저하.
    3.checksum : 복구시 안정성 향상
    4.procedure analyse() : 칼럼 타입 분석해서 추천.(데이타가 이미많이 들어간 상태에서 사용해볼만함.)

2.optimizer
    1.analyze table : 최신의 정보를 optimizer가 사용할수 있도록 통계정보 분석, 저장.
                        - 실행시간동안 read lock 걸림.
    2.optimize table : 조각모음. (delete가 많이 일어났을경우 실행하는게 좋음)- dynamic의경우 사용.
    3.explain
             : type 값이 아래와 같을경우 튜닝 필요
                 range.
                 index.
                 all.
    4.show_query_status -> last_query_cost 의 값으로 대략 튜닝해야할지 결정.

3.최적화
    1.select sql_buffer_result : 쿼리 결과가 오래걸릴 경우. lock걸리는것을 방지하기 위해
                                       테이블을 복사한후, 바로 lock을 풀어버림. 결과는 따로 작업후 돌려짐.
    2.select sql_big_result : 미리 결과가 클것을 알려서 using filesort, using temporary 두 작업을 한꺼번에 처리하면서, 시간을 줄이게된다.
    3.select straight_join : 테이블들의 조인순서 정하는 시간을 줄여줌.
    4.select * from table (force/use/ignore) index : optimizer의 최적화 이외의 설계를 적용할때.
                               force : 강제, use:가능하면 사용.
    5.여러개 insert시 multi-row insert를 사용해라!
                       

출처: http://mysqlblog.fivefarmers.com/2012/05/29/overlooked-mysql-5-6-new-features-timestamp-and-datetime-improvements/

MySQL 5.6 은 현재 개발중에 있습니다. 버전이 올라가면 기능이 향상되는데, TIMESTAMP 와 DATETIME 의 데이터 타입(Data Type)을 사용하는 컬럼에 경우 Update, Insert 시에 몇가지 기능이 향상되었습니다.

mysql> CREATE TABLE test_date (
    -> a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> b INT,
    -> created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -> updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.03 sec)

여기서 주목해야 할 것은 'created TIMESTAMP DEFAULT CURRENT_TIMESTAMP'와 'updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP' 입니다.

mysql> desc test_date;
+---------+------------------+------+-----+---------------------+-----------------------------+
| Field   | Type             | Null | Key | Default             | Extra                       |
+---------+------------------+------+-----+---------------------+-----------------------------+
| a       | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| b       | int(11)          | YES  |     | NULL                |                             |
| created | timestamp        | NO   |     | CURRENT_TIMESTAMP   |                             |
| updated | timestamp        | NO   |     | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
+---------+------------------+------+-----+---------------------+-----------------------------+
4 rows in set (0.03 sec)

Default와 Extra 컬럼에 내용을 자세히 보십시오.

여기서 Insert 를 다음과 같이 합니다.

mysql> INSERT INTO test_date SET b=1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_date;
+---+------+---------------------+---------------------+
| a | b    | created             | updated             |
+---+------+---------------------+---------------------+
| 1 |    1 | 2012-06-12 17:07:20 | 0000-00-00 00:00:00 |
+---+------+---------------------+---------------------+
1 row in set (0.00 sec)

'created' 컬럼에 자동으로 시간이 등록됩니다. 과거에는 다음과 같이 해줬어야 했습니다.

mysql> INSERT INTO test_date SET b=1, created=now();

'now()' 함수를 사용해서 했어야 했지만 mysql 5.6 에서 TIMESTAMP 타입을 이용해서 컬럼을 정의하면 더 손쉽게 자동으로 처리를 해줍니다.

다음과 같이 Update 를 해봅니다.

mysql> UPDATE test_date SET b = b+1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM test_date;
+---+------+---------------------+---------------------+
| a | b    | created             | updated             |
+---+------+---------------------+---------------------+
| 1 |    2 | 2012-06-12 17:07:20 | 2012-06-12 17:11:48 |
+---+------+---------------------+---------------------+
1 row in set (0.00 sec)

update 필드에 값이 자동으로 시간이 업데이트 됩니다. DATETIME 도 이와 같은데 다른점은 값이 없을 경우 'NULL'이 됩니다. 

mysql> CREATE TABLE test_date2 (
    -> a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> b INT,
    -> created DATETIME DEFAULT CURRENT_TIMESTAMP,
    -> updated DATETIME ON UPDATE CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.00 sec)

select date_format(now(), '%Y-%m-%d %H:%i:%s')

 이런 식의 날자 변환의 경우

2010-11-10 오후 11:23:19  - > 2010-11-10 23:23:19    로  정상 변환이 되지만

아래의 테스트 경우

SELECT DATE_FORMAT('2010-10-05 오후 9:29:07','%Y-%m-%d %H:%i:%s')

2010-11-10 오후 11:23:19  - > 2010-11-10 00:00:00   로 변환 된다.. 왜일까?????

 이와 같은 텍스트를 임의로 입력하여 변환하는 경우 오라클 처럼 sysdate 데이터형으로 더미쿼리가 변환하여 테스트 되는것이 아니다.

mysql 에서는 저런 형식으로 입력하고 테스트하면 datetime 형식으로 자동 변환하여 포멧을 변경하는것이 아니다..

그냥 스트링을 저형식으로 변경하다보니

당연히 결과로 날짜 검색이라던가 할수가 없다...;;; 물론 테스트상이지만...

실제 테스트할 테이블의 컬럼데이터형이 datetime 형식이라면 그 테이블에 now() 와 컬럼명으로 테스트 하는 것이 좋다.

또한 날짜의 차이.. timediff 함수를 사용하여

TIMEDIFF(DATE1, DATE2)    ==========    DATE1에서 DATE2를 뺀 나머지 시간.

TIMEDIFF( date_format(now(), '%Y-%m-%d %H:%i:%s'), date_format(chag_dt, '%Y-%m-%d %H:%i:%s') )

2010-11-12 00:41:39  -  2010-10-22 16:47:22      =      20.07:54:17    의 결과는 잘 나오는 편이다... (?)

잘나오는 편이라고 한 이유를 설명하겠다..

(아니 디비가 정확하게 나와야지 잘나오는 편은 뭐야 하며 짜증을 내며 발견한 것이다)

너무 큰 날짜 차이를 검색 하게 되면 TIMEDIFF 의 결과는 항상 34.22:59:59 를 넘지 못한다!!!!!!!!!!!!!

날짜가 34 일을 넘지 않으면 상관없다 치지만... 기분나쁘지 않은가..-_-

그래서 사용한 것이

아 여기서 주의점은 앞에서 DATE1, DATE2 순으로 넣었다면 이번엔 반대로 넣어야한다

                                     DATE2, DATE1
SELECT TIMESTAMPDIFF(YEAR,'1010-11-11 10:12:01','2011-12-12 11:11:20')

SELECT TIMESTAMPDIFF(MONTH,'1010-11-11 22:12:01','2011-12-12 23:11:20')        

SELECT TIMESTAMPDIFF(DAY,'1010-11-11 22:12:01','2011-12-12 23:11:20')/365       

SELECT TIMESTAMPDIFF(MINUTE,'1010-11-11 오후 10:12:01','2011-12-12 오후 11:11:20')   /60/24   

SELECT TIMESTAMPDIFF(SECOND,'1010-11-11 22:12:01','2011-12-12 23:11:20')/60

SELECT TIMESTAMPDIFF(SECOND,'1010-11-11 오후 10:12:01','2011-12-12 오후 11:11:20')

timestampdiff 는 이상하게 string형도 잘 계산하는듯 하며

1001 년까지 나눗셈으로 계산해봤는데도 거의(?이제 DB를 못믿겠따) 데이터가 맞는듯하다

-----------------------------------------------------------------------------------------

아 착오가 있었다.. 이방법으로 입력하여도 STRING 형으로 인식하는듯 하다

SELECT TIMESTAMPDIFF(SECOND,'1010-11-11 22:12:01','2011-12-12 23:11:20')             =31591213159

SELECT TIMESTAMPDIFF(SECOND,'1010-11-11 오후 10:12:01','2011-12-12 오후 11:11:20')   =31591209600

SELECT TIMESTAMPDIFF(SECOND,'1010-11-11 22:12:01','2011-12-12 오후 11:11:20')        =31591129679

각자 다른결과가 나왔다.. DATETIME 필드를 이용하여 테스트하자 -_-

-----------------------------------------------------------------------------------------

좋은 예는

TIMESTAMPDIFF( SECOND, (date_format(chag_dt, '%Y-%m-%d %H:%i:%s')) ,(date_format(now(), '%Y-%m-%d %H:%i:%s')) )

이런 일들을 격으면서 드는 생각은 DB에서 제공해주는 함수도

날짜 계산에 정확한 결과를 엊지 못함에 실망감을 느끼며, function 을 새로 만들어야하나.. 하는 생각이든다..

누가 만들어놓은게 있으면 난찾아쓰는게 좋은데.. 사서고생하고 싶지않아서이다..

아무튼 프로젝트별로 항상 date type로 별것 아니라고 생각하는데 고생하는 경우가 많은것 같아서

정말 데이터 타입또는 날짜비교 function 등이 필요할지도 모르겠다.. mysql은 믿을수가 없으니..

1. 목적

  특정 프로토콜의 경우 서버 or DB의 시간을 기준으로 돌아가야 할 경우가 있다. 이럴 경우 경과 시간을 체크해야 하는데 그때 MySQL에서 사용을 할수 있는 함수이다.

2. 원형 

  TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

  uint : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

  datetime_expr1 : 원본 datetime

  datetime_expr2 : 비교 datetime

3. 마무리

  MSSQL에서는 datediff를 사용하면되는데 MySQL에서는 안되서 찾아봤다. 다음에 언제 또 필요할지 모르니 우선은 블로그에 담아 둔다.

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

 출처 : MySQL 5.0 Reference Manual

심플하면서도... 안 쓰다 쓸 때 한 번에 기억을 재생시켜주기에는 충분한 예제

CREATE DEFINER=`root`@`localhost` PROCEDURE `foo`.`usp_cursor_example`(
  IN name_in VARCHAR(255)
)
READS SQL DATA
BEGIN

  /*
    All 'DECLARE' statements must come first
  */

  -- Declare '_val' variables to read in each record from the cursor
  DECLARE name_val VARCHAR(255);
  DECLARE status_update_val VARCHAR(255);

  -- Declare variables used just for cursor and loop control
  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;

  -- Declare the cursor
  DECLARE friends_cur CURSOR FOR
    SELECT
        name
      , status_update
    FROM foo.friend_status
    WHERE name = name_in;

  -- Declare 'handlers' for exceptions
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

  /*
    Now the programming logic
  */

  -- 'open' the cursor and capture the number of rows returned
  -- (the 'select' gets invoked when the cursor is 'opened')
  OPEN friends_cur;
  select FOUND_ROWS() into num_rows;

  the_loop: LOOP

    FETCH  friends_cur
    INTO   name_val
    ,      status_update_val;

    -- break out of the loop if
      -- 1) there were no records, or
      -- 2) we've processed them all
    IF no_more_rows THEN
        CLOSE friends_cur;
        LEAVE the_loop;
    END IF;

    -- the equivalent of a 'print statement' in a stored procedure
    -- it simply displays output for each loop
    select name_val, status_update_val;

    -- count the number of times looped
    SET loop_cntr = loop_cntr + 1;

  END LOOP the_loop;

  -- 'print' the output so we can see they are the same
  select num_rows, loop_cntr;

END
DELIMITER ;
[출처] MySQL - 저장 프로시저(Stored Procedure), 커서(Cursor) 사용 예제|작성자 아리

delimiter $$

DROP FUNCTION IF EXISTS dbname.string_print$$

CREATE FUNCTION dbname.string_print( str VARCHAR(20) ) RETURNS VARCHAR(20)

     BEGIN

          DECLARE copy_str VARCHAR(20);

          SET copy_str = str;

          RETURN copy_str;

     END $$

delimiter ;

SELECT dbname.string_print('Hello world');

delimiter $$

DROP PROCEDURE IF EXISTS dbname.string_print$$

CREATE PROCEDURE dbname.string_print( str VARCHAR(20) )

     BEGIN

          DECLARE copy_str VARCHAR(20);

          SET copy_str = str;

          SELECT copy_str;

     END $$

delimiter ;

CALL dbname.string_print('Hello world');

이번엔 예외처리...

delimiter $$

DROP PROCEDURE IF EXISTS db_name.procedure_name$$

CREATE PROCEDURE db_name.procedure_name(IN num INT, IN ch VARCHAR(2))

BEGIN

     DECLARE err INT DEFAULT '0'; 
     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  SET err = -1; 
    
     START TRANSACTION;

     INSERT INTO table_name VALUES (num, ch);     // 성공
     INSERT INTO table_name VALUES (ch, num);     // 실패
    
     IF err < 0 THEN 
          ROLLBACK; 
     ELSE 
          COMMIT; 

     END IF;

END $$

delimiter ;

      : IN -> input, OUT -> output <가독성을 위한 표시 기능>

      : 예외 핸들링을 위한 변수 선언. 쿼리 실패시 err값은 '-1'

이번엔 루프문을...

delimiter $$

DROP PROCEDURE IF EXISTS db_name.procedure_name$$

CREATE PROCEDURE db_name.procedure_name(IN ch VARCHAR(2))

BEGIN

     DECLARE val INT DEFAULT '0'; 
    

     WHILE val < 5 DO
          INSERT INTO table_name VALUES (val, ch);

          SET val = val + 1;

     END WHILE;
    
END $$

delimiter ;

이번엔 커서를...

delimiter $$

DROP PROCEDURE IF EXISTS proc_name$$

CREATE PROCEDURE proc_name()
BEGIN 

DECLARE cur_state INT DEFAULT '0'; 
DECLARE copy_column1 INT DEFAULT '0'; 
DECLARE copy_column2 INT DEFAULT '0'; 
DECLARE result_count INT DEFAULT '0';

DECLARE cur CURSOR FOR SELECT column1, column2 FROM table1;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cur_state = 1;

OPEN cur;

REPEAT

FETCH cur INTO copy_column1, copy_column2; →커서 이동 및 결과 매칭

IF NOT cur_state THEN →커서 상태 체크

UPDATE ...;
SET result_count = result_count + 1;

END IF;

UNTIL cur_state END REPEAT;

CLOSE cur;

IF result_count > 0 THEN

SELECT result_count;

ELSE

SELECT 0;

END IF;

END $$

delimiter ;

      :

      :

      :

      :

이번엔 SELECT 결과를 변수에...

delimiter $$

DROP PROCEDURE IF EXISTS proc_name$$

CREATE PROCEDURE proc_name()
BEGIN 

DECLARE result INT DEFAULT '0'; 

SELECT column1 INTO result FROM table1 WHERE ...;

SELECT reslut;

END $$

delimiter ;

※ MySQL에선 'result = column1'이 안된다.

이 함수는 expr1 과 expr2 이 같으면 0 을 반환하고, expr1 이 expr2 보다 크면 1 를 반환합니다. 반대로 expr1 이 expr2 보다 작으면 -1 를 반환합니다.

예제(ex #1

※ INSERT XXX  ON DUPLICATE KEY UPDATE 는 MySQL 4.1.0 부터 지원되며 이하버전에서는 지원되지 않습니다.

  CONCAT 

CONCAT은 정해준 구분자를 기준으로 필드를 묶어주는 함수이다. 

사용 방법은 다음과 같다. 

SELECT CONCAT(`필드1`,'구분자',`필드2`,'구분자',`필드3`) FROM 테이블명;

CONCAT에 대해서 알아본 이유는 바로 이것때문이었다. 

데이터 베이스에 다음과 같은 사전이 저장되어 있다. 

keyword는 하나의 제품을 의미하고 syn은 그 제품명에 대한 동의어들이다. 

따라서 내가 원한것은 하나의 키워드 들과 그 동의어들을 나열해서 출력하는 것이었는데 

문제는 각 keyword 들에 대한 동의어의 수가 다르다는 것이었다. 

그러다 발견한 것이 GROUP_CONCAT이다. 

  GROUP_CONCAT 

GROUP_CONCAT은 GROUPING된 레코드들을 원하는 구분자를 이용하여 문자열로 만들 수 있다. 

다음과 같이 사용한다. 

SELECT GROUP_CONCAT( 필드명 SEPARATOR "구분자") FROM 테이블명 

GROUP BY 그룹할 필드명; 

만약 다른 테이블 2개에서 동일한 내용을 갖는 필드가 있다면 그 두 테이블을 이용하겠지만, 나는 하나의 테이블을 

이용해야 했다. 따라서 임시테이블을 생성하여 GROUP_CONCAT을 사용하였다. 

(phpMyAdmin 출력화면)

여기서, 하나의 keyword에 대해서 검색하고 싶다면 다음과 같이 하면 된다. 

만약 GROUP_CONCAT 안에 중복이 존재할 경우, 

이렇게 되어 버리는데....

이렇게 하면 

ALTER 문에 대해서 좀 끄적여 놓으려 한다. 

우선 ALTER문은 이미 존재하는 테이블의 구조나 형식등을 바꾸기 위해 사용한다. 

따라서, 칼럼의 구조나 형식을 변경하기 위해 ALTER명령을 사용하게 된다. 

ALTER 명령어는 다음과 같다. 

  1. 테이블 형식 변경 

- Engine 사용 : ALTER TABLE 테이블명 ENGINE=형식; 

- type 사용 : ALTER TABLE 테이블명 TYPE=형식; 

  2. 테이블 이름 변경 

- ALTER TABLE 테이블명 RENAME 바꿀이름

- RENAME TABLE 테이블명 TO 바꿀이름

  3. 칼럼 추가 

- 마지막에 추가 : ALTER TABLE 테이블명 ADD COLUMN 칼럼이름 칼럼타입 

- 지정 칼럼 뒤에 : ALTER TABLE 테이블명 ADD COLUMN 칼럼이름 칼럼타입 AFTER 칼럼이름

- 제일 앞에 : ALTER TABLE 테이블명 ADD COLUMN 칼럼이름 칼럼타입 FIRST

  4. 칼럼 삭제 

- ALTER TABLE 테이블명 DROP COLUMN 칼럼이름

  5. 칼럼 변경 

- ALTER TABLE 테이블명 MODIFY 컬럼이름 새컬럼타입

- ALTER TABLE 테이블명 CHANGE 컬럼이름 새컬럼이름 새컬럼타입

  6. 인덱스에 새항목 추가 

- ALTER TABLE 테이블명 ADD INDEX(컬럼이름)

  7. 인덱스 삭제 

- ALTER TABLE 테이블명 DROP INDEX 컬럼이름

- DROP INDEX 인덱스이름 ON 테이블명 

  7. 기본키(Primary Key) 지정하기 

- ALTER TABLE 테이블명 ADD PRIMARY KEY(칼럼이름) 컬럼이름

  7. 기본키 삭제

- ALTER TABLE 테이블명 DROP PRIMARY KEY

MySQL이 설치된 폴더의 'BIN' 로 이동하세요.

파일의 형태로 백업이 됩니다.

1. 백업 

MySQLDump -u사용자 -p암호 DB이름 > DB백업.sql

ex) mysqldump -uroot -p1234 test_db > test_db.sql -- ('test_db' 전체 내용을 백업)

2. 테이블 백업

MySQLDump -u사용자 -p암호 DB이름 TB이름 > TB백업.sql

ex) mysqldump -uroot -p1234 test_db test_table > test_table.sql -- ('test_db' 의 'test_table' 내용을 백업)

3. 데이터 백업

MySQLDump -u사용자 -p암호 DB이름 TB이름 -w "조건" > 데이터백업.sql

ex) mysqldump -uroot -p1234 test_db test_table -w "name='홍길동' > test_table.sql 

 -- ('test_db' 의 'test_table' 에서 'name' 이 홍길동인 데이터만 백업) 

4. 복원 (데이터 등록)

MySQL -u사용자 -p암호 DB이름 < DB백업.sql -- DB가 존재 하지 않는 경우 미리 생성 후 진행 하여야 함.

MySQL -u사용자 -p암호 DB이름 TB이름 < TB백업.sql

MySQL -u사용자 -p암호 DB이름 TB이름 < 데이터백업.sql

주의) 복원 명령 시 기존의 테이블은 제거 된 후 재생성 하여 복원 됩니다. (기존 데이터는 삭제됨) 

TIP) 파일명 자동 생성 명령을 위한 명령문 

ex) mysqldump -uroot -p1234 test_db > test_db_%date%.sql -- ('test_db_2013-04-02.sql' 형태로 생성됨.)   

아래와 같이 "backup.bat" batch 파일을 만들면 내가 원하는 형태의 날짜및시간을 표현 할 수 있다. 

----------------------------------------------------------------------------------

@echo off  

SET now=%date:~0,4% -- 2013-04-02 (0번째 부터 4개)
SET now=%now%%date:~5,2% -- 2013-04-02 (5번째 부터 2개)
SET now=%now%%date:~8,2% -- 2013-04-02
  

SET now=%now%_%time:~0,2% -- 14:10:10.10
SET now=%now%%time:~3,2% -- 14:10:10.10
SET now=%now%%time:~6,2% -- 14:10:10.10

mysqldump -uroot -p1234 %1 > %1_%now%.sql  -- %1 : 배치 파일 실행 시 포함 한 DB명

-----------------------------------------------------------------------------------

backup.bat test_db < 샐행  -- ('test_db_20130402_141010.sql' 형태로 생성됨.)   

* 구조 복사

Create Table new_table like old_table

  특징 : 기존 테이블의 설정 그대로 복사 된다.

    참고 ==> 큐브리드의 경우 복사하고자 하는 기존 테이블에 'Primary Key' 또는 'auto_increment' 가 설정 되어 있으면 복사 할 수 없음.

    응용 ==> Create Table IF NOT EXISTS new_table like old_table (new_table 이 없으면 복사)

* 구조와 데이터 복사

Create Table new_table ( select * from old_table )

  특징 : 테이블의 구조와 함께 데이터도 함께 복사가 된다.

     주의 ==> 큐브리드의 경우와 같이 기존 테이블에 'Primary Key' 또는 'auto_increment' 가 설정 되어 있으면

       해당 설정은 적용 되지 않고 값만 복사 됨.

* 데이터 복사

Insert Into destination_table ( select * form source_table)

  참고 ==> 대상 테이블의 컬럼 중에 자동 증가 값 설정 이 된 컬럼이 있을 경우 해당 컬럼에 데이터 입력시 중복된 데이터가 있으면 오류 발생.

  응용 ==> Insert Into destination_table (column_a, column_b) (select a, b from source_table) 원하는 테이블만 복사가 가능하다.

 데이터베이스에서도 프로그램 처리가 가능하다니.. 데이터베이스 공부 좀 해야겠다는.. 

## LOOP 문

DELIMITER $$
DROP PROCEDURE IF EXISTS loop_test $$
CREATE PROCEDURE loop_test(IN var INT)
BEGIN
    DECLARE nCnt INT DEFAULT 0;
    DECLARE nTot INT DEFAULT 0; 
    loop_xxxx:LOOP 
       IF (var <= nCnt) THEN
          LEAVE loop_xxxx; -- loop 탈출 조건 (탈출 조건을 사용하기 위해서는 루프의 이름이 지정되어야 함.) 
       END IF;  
       SET nCnt = nCnt + 1;
       SET nTot = nTot + nCnt; 
    END LOOP;
 
    SELECT CONCAT(CONCAT(CONCAT('1 부터 ', var), ' 까지의 합은 '), nTot) AS total; -- 결과 값 출력
 
END $$
DELIMITER ;

#########################

Call loo_test(10); 

total                            
-----------------------
1 부터 10 까지의 합은 55   

## WHILE 문

DELIMITER $$
DROP PROCEDURE IF EXISTS while_test $$
CREATE PROCEDURE while_test(IN var INT)
BEGIN
    DECLARE nCnt INT DEFAULT 0;
    DECLARE nTot INT DEFAULT 0; 
    while_xxxx:WHILE (nCnt < var) DO
        SET nCnt = nCnt + 1;        
        IF ((nCnt % 2) = 1) THEN
            ITERATE while_xxxx; -- 아래쪽 명령은 수행하지 않고 반복문 시작 지점으로 이동(C 언어의 'continue') 
        END IF; 
        SET nTot = nTot + nCnt;  
    END WHILE;
 
    SELECT CONCAT(CONCAT(CONCAT('1 부터 ', var), ' 까지의 짝수의 합은 '), nTot) AS total;
 
END $$
DELIMITER ;

#########################

Call while_test(10);

total                                      
------------------------------
1 부터 10 까지의 짝수의 합은 30 

## REPEAT ~ UNTIL 문
DELIMITER $$
DROP PROCEDURE IF EXISTS repeat_test $$
CREATE PROCEDURE repeat_test(IN var INT)
BEGIN
    DECLARE nCnt INT DEFAULT 0;
    DECLARE nTot INT DEFAULT 0; 
    repeat_xxxx:REPEAT
        SET nCnt = nCnt + 1;      
        SET nTot = nTot + nCnt;        
        IF (nCnt >= 5) THEN
            LEAVE repeat_xxxx; -- 반복문 탈출
        END IF; 
 
        UNTIL nCnt >= var -- 주의 조건이 참이면 반복문 완료 (문장완료를 알리는 ';' 없음)
    END REPEAT;
 
    SELECT CONCAT(CONCAT(CONCAT('1 부터 ', nCnt), ' 까지의 합은 '), nTot) AS total;
 
END $$
DELIMITER ;

#########################

Call repeat_test(10);

total                           
---------------------- 

1 부터 5 까지의 합은 15   

OS 계정 추가

다음과 같이 dba 그룹을 추가하고 그 밑에 mysql 계정을 추가합니다.

1
2
3

groupadd -g 600 mysql
useradd -g 600 -u 605 mysql

Linux 설정 변경

세션 Limit 를 설정합니다.

1
2
3
4
5
6

vi /etc/security/limits.conf
##하단 내용 추가
root            soft    nproc  8192
root            hard    nproc  16384
root            soft    nofile 8192
root            hard    nofile 65536

OS에서 limits.conf 파일을 읽어들이도록 설정합니다. 없으면 생성합니다.

1
2
3

vi /etc/pam.d/login
## 하단 내용 추가
session    required     pam_limits.so

/etc/profile 에 다음 내용을 추가하여 login 시 적용되도록 합니다.

1
2
3
4
5
6
7
8
9
10

vi /etc/profile
##
if [ $USER = "mysql" ]; then
if [ $SHELL = "/bin/dba" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

MySQL 데이터 저장 디렉토리를 생성합니다.

1
2
3
4

mkdir -p /head1ton/database/data
mkdir -p /head1ton/tmp
mkdir -p /head1ton/iblog
mkdir -p /head1ton/binlog

MySQL 설치 파일 다운로드

하단 실행 시 x86_64 가 있으면 64비트이고, i686 이 있으면 32비트입니다.

1
2
3

## OS 버전 확인 ##
uname -a
Linux ..중략.. EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

이제 MySQL Download 의 “Linux – Generic” 탭에서 자신의 OS에 맞는 MySQL Server 받으세요. 현재 Release되는 주 버전은 MySQL 5.5.x이나, 여기서는 MySQL 5.1.57 64비트 버전으로 설명드리겠습니다.
굴욕적이지만, 한국보다는 일본 mirror서버에서 받는 것이 빠르다는..-_-;;

1
2
3

cd /usr/local/
## 설치 파일 다운로드
wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.19-linux2.6-x86_64.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.37-linux2.6-x86_64.tar.gz

MySQL 기본 설정

시스템에 따라 데이터 파일과 같은 일부 변수 값이 달라질 수 있으니, 자신의 시스템에 맞게 수정해서 사용하세요.

1

vi /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
# generic configuration options
port = 3306
socket = /tmp/mysql.sock

back_log = 100
max_connections = 500
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
join_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
bulk_insert_buffer_size = 16M
thread_cache_size = 128
thread_concurrency = 16
query_cache_type = 0
default_storage_engine = innodb
thread_stack = 192K
lower_case_table_names = 1
max_heap_table_size = 128M
tmp_table_size = 128M
local_infile = 0
max_prepared_stmt_count = 256K
event_scheduler = ON
log_bin_trust_function_creators = 1
secure_auth = 1
skip_external_locking
skip_symbolic_links
#skip_name_resolve

## config server and data path
basedir = /usr/local/mysql
datadir = /head1ton/database/data
tmpdir = /head1ton/tmp
log_bin = /head1ton/binlog
relay_log = /head1ton/binlog
innodb_data_home_dir = /head1ton/database/data
innodb_log_group_home_dir = /head1ton/iblog

## config character set
##utf8
character_set_client_handshake = FALSE
character_set_server = utf8
collation_server = utf8_general_ci
init_connect = "SET collation_connection = utf8_general_ci"
init_connect = "SET NAMES utf8"

## bin log
binlog_format = row
binlog_cache_size = 4M

## Replication related settings
server_id = 1
expire_logs_days = 7
slave_net_timeout = 60
log_slave_updates
#read_only

## MyISAM Specific options
key_buffer_size = 32M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 16M
myisam_repair_threads = 1
myisam_recover = FORCE,BACKUP

# *** INNODB Specific options ***
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1G

innodb_data_file_path = ibdata1:1G:autoextend
innodb_file_per_table = 1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 120
innodb_support_xa = 0
innodb_file_io_threads = 8

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no_auto_rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive_timeout

[mysqld_safe]
open_files_limit = 8192

MySQL Server 설치

1
2
3
4
5
6
7
8
9
10
11
12
13

## 압축 해제
cd /usr/local
tar xzvf mysql-5.5.19-linux2.6-x86_64.tar.gz
## 관리를 위한 심볼릭 링크 생성
ln -s mysql-5.5.19-linux2.6-x86_64 mysql
## 설치 파일 권한 변경
chown -R mysql.mysql /usr/local/mysql*
## 시작 스크립트 복사
cp mysql/support-files/mysql.server /etc/init.d/mysqld
## 관련 파일 권한 설정
chown mysql.mysql /head1ton/*
chown mysql.mysql /etc/my.cnf
chown mysql.mysql /usr/local/mysql*

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

su - mysql
cat >> ~/.bash_profile
## 하단 내용 입력
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin:.
export ADMIN_PWD="ROOT 패스워드"
 
alias ll="ls -al --color=auto"
alias mydba="mysql -uroot -p$ADMIN_PWD"
alias mymaster="mysql -uroot -p$ADMIN_PWD -e'show master status;'"
alias myslave="mysql -uroot -p$ADMIN_PWD -e'show slave status\G'"
alias mh="cd $MYSQL_HOME"
alias md="cd /head1ton/database/data"
alias mt="cd /head1ton/tmp"
alias mb="cd /head1ton/binlog"
alias mi="cd /head1ton/database/data"
alias dp="cd /head1ton/database/data"

 
## 환경 변수 적용
. ~/.bash_profile

MySQL Server 구동

1
2
3
4
5

cd /usr/local/mysql
## 기본 데이터베이스 설치
./scripts/mysql_install_db --datadir=/head1ton/database/data --defaults-file=/etc/my.cnf --user=mysql
## MySQL 데몬 Startup
/etc/init.d/mysqld start

MySQL 보안 설정

처음 DB를 올리면 보안 면에서 취약한 부분이 있습니다.
기본적인 보안 정책을 적용하도록 합니다.
mysql root  계정 패스워드만 설정하고 나머지는 Enter만 쭉 치면 됩니다.

1
2

cd /usr/local/mysql
./bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Change the root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
- Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
... Failed! Not critical, keep moving...
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

Linux 시스템을 사용하다가 시스템 사용량을 보기위해 우리는 top를 이용한다

top 명령어 사용시 유용한 옵션정리

[사용법]

$ top [옵션]

나의 경우는 옵션을 별로 사용하지 않는다 

보통 top 화면으로 들어가서 사용량을 확인함

[화면 내용들]

top - 15:55:22 up 98 days, 14:29,  1 user,  load average: 0.05, 0.04, 0.00

Tasks: 141 total,   1 running, 140 sleeping,   0 stopped,   0 zombie

Cpu(s):  0.2%us,  0.1%sy,  0.0%ni, 99.3%id,  0.4%wa,  0.0%hi,  0.0%si,  0.0%st

Mem:   4031448k total,  3306440k used,   725008k free,   263928k buffers

Swap:  8193140k total,     2900k used,  8190240k free,  1588424k cached

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

각 라인별 내용 

top - 15:55:22 up 98 days, 14:29,  1 user,  load average: 0.05, 0.04, 0.00

현재 시간, uptime (서버 uptime), 현재 로그인 유저수 , 로드 에버리지 1분 5분 15분

 로드 에버리지(load average)란? 

 - 작업의 대기시간 , 값이 1이라면 1분동안 평균 1개의  프로세스가 대기상태임을 나타낸다.

   보통 5이면 서버가 부하를 받는다고 생각함, 10~15면 과부하

Tasks: 141 total,   1 running, 140 sleeping,   0 stopped,   0 zombie

전체 프로세스 수, 현재 실행중인 프로세스, 유휴상태 프로세스, 정지상태 프로세스, 좀비 프로세스

Cpu(s):  0.2%us,  0.1%sy,  0.0%ni, 99.3%id,  0.4%wa,  0.0%hi,  0.0%si,  0.0%st

사용자가 사용중인 CPU 사용률(us), 시스템이 사용하는 CPU 사용률(sy), NICE 정책에 의해 사용되는 CPU 사용률(ni), 사용되지 않은 CPU의 미사용률(id), 입출력 대기상태의 사용률(wa)

Mem:   4031448k total,  3306440k used,   725008k free,   263928k buffers

전체 물리적인 메모리, 사용중인 메모리(used), 사용되지 않는 여유 메모리(free), 버퍼된 메모리(buffers)

Swap:  8193140k total,     2900k used,  8190240k free,  1588424k cached

전체 스왑 메모리, 사용중인 스왑 메모리, 남아있는 스왑메모리, 캐싱메모리

세부정보 필드명

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

    * PID : 프로세스 ID (PID)
    * USER : 프로세스를 실행시킨 사용자 ID
    * PRI : 프로세스의 우선순위 (priority)
    * NI : NICE 값. 일의 nice value값이다. 마이너스를 가지는 nice value는 우선순위가 높음.
    * VIRT : 가상 메모리의 사용량(SWAP+RES)
    * RES : 현재 페이지가 상주하고 있는 크기(Resident Size)
    * SHR : 분할된 페이지, 프로세스에 의해 사용된 메모리를 나눈 메모리의 총합.
    * S : 프로세스의 상태 [ S(sleeping), R(running), W(swapped out process), Z(zombies) ]
    * %CPU : 프로세스가 사용하는 CPU의 사용율
    * %MEM : 프로세스가 사용하는 메모리의 사용율
    * COMMAND : 실행된 명령어

[명령 실행 후 사용하는 옵션들]

1 :  cpu 갯수별 사용량 보기

shift + m : 메모리 사용량이 큰 순서로 정령

shift + p : CPU 사용량이 큰 순서로 정렬

shift + t : 실행시간이 큰 순서로 정렬

k : 프로세스  kill  - k 입력 후 종료할 PID 입력 signal을 입력하라고 하면 kill signal인 9를 입력

c : 명령인자 표시/비표시

space bar : refresh

u : 입력한 유저의 프로세스만 표시 - which user 가 나오면 유저를 입력, 아무것도 안너으면 전체 표시

shift + b : 상단 정보를 블럭표시상태로 보여줌

<참고자료>

http://www.starhost.co.kr/xe/108405

항상 감사합니다. 

- 특정 데이타베이스 하나만을 백업

백업형식 : myslqdump -u DB계정명 -p 백업대상데이터베이스명 > 저장할파일명

복구형식 : mysql -u DB계정명 -p 복구할테이터베이스명 < 저장할파일명

# ./mysqldump -u root -p mysql > mysql.sql      //mysql 디비 백업 예

# ./mysql -u root -p mysql < ./mysql.sql           //mysql 디비 복구 예

// 위 백업결과물인 sql 파일은 디비를 생성하지는 않는다.. 다시말해 복구하려는 디비에 mysql이란 디비가 없다면 복구가 되지않는다.... 이점 유의

// 복구하려는 mysql 디비에 테이블이 이미 존재한다면 복구 실패... sql 파일에서 해당 디비의 테이블을 생성하므로.. 따라서 특정 데이타베이스 하나만을 복구 할 시에는 데이타베이스의 이름만 생성한 다음에 위 방법의 복구하여야 함

- 특정 데이타베이스의 특정 테이블 하나만을 복구 백업

백업형식 : myslqdump -u DB계정명 -p 데이터베이스명 테이블명 > 저장할파일명

복구형식 : mysql -u DB계정명 -p 데이터베이스명 < 저장할파일명

#./mysqldump -u root -p mysql user > user.sql

#./mysql -u root -p mysql < user.sql

- 여러개의 데이터베이스 한번에 백업과 복구

백업형식 : mysqldump -u root -p --databases [옵션] DB1 [DB2 DB3] > 파일명

복구방법 : mysql -u root -p < 파일명

# ./mysqldump -u root -p --databases mysql test > dbs.sql   //mysql, test 디비를 백업

# ./mysql -u -root -p < dbs.sql                                            //백업된 두 디비를 복구

//--databases 옵션 시 일반 백업과 달리 "CREATE DATABASE ..."문과 "USE DB .." 문이 추가됨

//달리 애기하면 DB를 생성할 필요가 없이 바루 디비가 생성되고 생성된 디비로 전환되고 테이블을 생성하고 인서트로 데이타를 붓는다.. 데이타베이스를 지정하면 안됨

- Mysql 의 전체 데이타베이스 백업

백업형식 : ./mysqldump -u root -p --all-databases > 파일명.sql

복구형식 : mysql -u root -p < 파일명.sql

//원본 데이타베이스명과 동일한 디비가 생성됨

- 기존 테이블 삭제후  백업된 파일로 복구를 위한 백업

 (mysqldump문으로 데이타베이스 백업 시 각각의 create table문 앞에 drop table문을 삽입한 형태가 된다)

백업형식 : ./mysqldump -u root -p --add-drop-table test > test.sql

//test 데이타베이스를 기존 태이블 삭제문을 추가하여 백업한다

복구형식 : ./mysql -u root -p test < test.sql

//기존 테이블을 삭제하고 복구한다는것에 유의

CREATE DEFINER=`root`@`localhost` EVENT `up_CreateDaily_LOG_event_ACHIEVE_LOG`

ON SCHEDULE

EVERY 24 HOUR STARTS '2013-10-11 01:30:00'

ON COMPLETION NOT PRESERVE

ENABLE

COMMENT ''

DO label1:

BEGIN

CALL up_CreateDaily_ACHIEVE_LOG();

END label1

CREATE DEFINER=`root`@`localhost` PROCEDURE `up_CreateDaily_ACHIEVE_LOG`()

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

BEGIN

DECLARE NSQLSTRING NVARCHAR (4000);

DECLARE NDATE VARCHAR (8);

SET NDATE = REPLACE(DATE_ADD(CURDATE(), INTERVAL 1 DAY) , '-', '');

SET @NSQLSTRING = CONCAT("

CREATE TABLE IF NOT EXISTS ACHIEVE_LOG_", NDATE , "

(      

       LogNo smallint DEFAULT NULL,

       EventDate timestamp ,

LogType int ,

ServerIdx smallint ,

ChannelIdx int ,

Seq bigint ,

CharNo bigint ,

AccountID varchar(31) ,

MakeCodeNo int ,

CharName varchar(31) ,

CharClass int ,

CharLevel int ,

WorldID int ,

WorldtemplateID int ,

AchieveID int ,

RewardType int ,

AchievePoint int ,

CharAchievePoint int ,

CallName varchar(100) ,

Class varchar(100) ,

KEY ACHIEVE_LogType_StartDate_IDX (LogType,EventDate),

KEY ACHIEVE_AccountID_IDX (AccountID)

);");

PREPARE stmt2 FROM @NSQLSTRING;

EXECUTE stmt2;

DEALLOCATE PREPARE stmt2;

END

윈도우

mysql 종료

mysqladmin -uroot -p shutdown

(또는 killall mysqld)

mysql 윈도우 재시작

net start mysql

리눅스

mysqladmin -uroot -p shutdown

(or # kill mysqld)

재시작

mysqld_safe &

설치 후 작업

사용자 비밀번호 변경

# mysqladmin -u root password [new root password]

새로운 데이터 베이스 생성

# mysqladmin -u root -p[passwd] create [database]

sql 파일을 데이터베이스에 넣기

# mysql -u root -p[passwd] [database] < file.sql

사용자에게 대상 데이터베이스에 대한 모든 권한 주기

# mysql -u root -p[passwd] [database]

> grant all on [database].* to [user]@localhost identified by'[user passwd]';

> flush privileges;

대상 데이터베이스의 모든 테이블 내용 보기

> use [database];

> show tables;

> select * from [table];

모든 사용자 목록 보기

> use mysql;

> select * from user;

데이터베이스 재시작

# /usr/local/mysql/bin/mysql restart

DB 설정 다시 적용

# mysql reload

패스워드 복원

DB 정지

# mysql stop

DB 시작 (패스워드 없이)

# mysqld_safe --skip-grant-tables &

root 로그인

# mysql -u root

> use mysql;

> update user set password=PASSWORD("[new root password]") where User='root';

> flush privileges;

> quit

DB reboot

# mysql restart

root login

# mysql -u root -p

백업 & 복구

모든 DB 백업

# mysqldump -u root -p[passwd] --all-databases > dbbackup.sql

특정 DB 백업

# mysqldump -u root -p[passwd] --databases [database1] [database2] > dbbackup.sql

복구

# mysql -u root -p[passwd] < dbbackup.sql

cron 용 backup 스크립트

백업 계정 생성

# mysql -u root -p

> GRANT SEECT, SHOW DATABASES, LOCK TABLES ON *.* TO [user]@localhost IDENTIFIED BY '[passwd]';

쉘 생성

# vi backup.sh

date=`date -I`

mysqldump --all-databases -u [user] --password="[passwd]" > backup-$date.sql

운영용 필수 명령어

응용예)

> show variables like '%log_bin%';

> select count(*) from Article; - data count

> explain 테이블명;                                                테이블 구조 보기

> explain select u.uid, u.name, a.name from sm_user u, sm_addr a where u.uid=a.uid;  지정한 질의문이 어떻게 실행될 건지를 보여줌

> select version();                                            MySQL서버버전 출력

> status;

> select * from 테이블명 Limit 10;                       오라클의 rownum 과 같은 기능

> select * from 테이블명 Limit 5, 10;                   5번째부터 10번째까지

> insert into test values('12', now());                 오라클 sysdate와 동일

> show variables;                                           서버의 variables(설정사항)출력

> show variables like 'have_inno%'                  조건에 맞는 variables만 출력

> show databases;                                         database목록

> show tables;                                               현재DB의 테이블목록(temporary table은 출력하지 않음)

> show tables from db명;                                 지정된 db명이 소유한 테이블목록

> show tables like 'mem%';                                     조건에 맞는 테이블목록만 출력

> show index from 테이블명;                                    인덱스 보기

> show columns from 테이블명;                                  테이블구조(describe 테이블명, explain 테이블명)

> show table status;                                           현재 DB의 테이블들의 상태(row수,table type,row길이,..)

> show table status from db명;                                 지정된 DB의 테이블들의 상태(row수,table type,row길이,..)

> show create table 테이블명;                                  해당 테이블 생성 SQL문 출력

> rename table 테이블1 to 테이블2;                             테이블명 변경(ALTER TABLE 테이블1 RENAME TO 테이블2)

> rename table 테이블1 to 테이블2, 테이블3 to 테이블4;         rename multiple tables

> rename table db1명.테이블명 to db2명.테이블명;               테이블을 다른 DB로 이동

> alter table 테이블명 add 컬럼명 데이터타입;                                     컬럼추가

> alter table 테이블명 del 컬럼명;                                                      컬럼제거

> alter table 테이블명 modify 컬럼명 컬럼타입;                                     컬럼명에 지정된 컬럼타입의 변경

> alter table 테이블명 change old컬럼명 new컬럼명 컬럼타입                컬럼명 변경

> alter table 테이블명 type=innodb;                                                    테이블type변경

> create table 테이블명(..) type=heap min_rows=10000;        10000row를 수용할 수 있을 만큼 메모리할당(heap type)

> create table 테이블2 as select * from 테이블1;                  테이블1과 동일한 테이블 생성(with 데이터, as는 생략가능)

> create table 테이블2 as select * from 테이블1 where 1=2;   테이블1과 동일한 구조의 테이블 생성(without 데이터, 1=2는 0으로 할수도 있다.)

> insert into 테이블2 select * from 테이블1;                         테이블1의 데이터를 테이블2에 insert

> delete from 테이블명 where 컬럼명='컬럼' 

> update user set Grant_priv='Y' where user='newsfeed';

> delete from user where Host="test%.srch%";

::Bin log의 내용 보기 

$] sudo mysqlbinlog ./binlog 명 | more

:: Slave 갯수 만큼 skip해서 repl 받기

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1

::Select 값을 한번에 정리해서 출력하기

SELECT COUNT

FROM   servers

UNION ALL

SELECT COUNT

FROM   slow_log;

1. mysql database 복원모드로 구동

DB 종료

my.cnf 파일 내용 추가

[mysqld]

innodb_force_recovery=4

DB 구동

2. 테이블 확인 및 리스트 생성

mysqlcheck --all-databases

복원모드에서는 데이터베이스와 테이블을 수정 할수 없다.

복원모드에서 mysqldump를 하고 일반모드에서 복원을 하면 됨

3. 테이블 백업 & 제거

테이블 덤프 백업

mysqldump -ucgw -pcgw [database] [table] > database.table.sql

테이블 제거

drop table [database].[table];

4. normal mode로 mysql 재 구동

my.cnf 파일 설정 삭제

[mysqld]

innodb_force_recovery=4

DB 재 구동

5. import backup.sql

mysql [database] < database.table.sql

데이터베이스를 덤프 하여 복원 할 경우

mysqldump -ucgw -pcgw cgwdb > /home/cgwdb.sql

MySQL 5.1부터 PreparedStatement라는 기능을 지원합니다. 일반질의문(statement)을 만들어 실행하면 쿼리분석->최적화->권한체크->쿼리실행 단계를 거치게 되는데 PreparedStatement를 사용하면 쿼리분석,최적화단계를 1번만 거치고 SQL내에 저장해두었다가 필요할때 쓸 수 있습니다. 그렇기 때문에 세션내에서 생성해놓고 닫기전까지 계속 같은 질의문을 사용이 많이 반복해야 한다면 성능상 효과적입니다. 또한 일반 statement는 문자열 텍스트 통신하는 반면 PreparedStatement를 사용하면 데이터가 바이너리 통신을 합니다. 이 또한 성능상 이득입니다. 뿐만아니라 다음에 설명할 변수홀더(SQL문에 ?부분)를 사용함으로써 SQL인젝션문제로부터 어느정도 보안적 이슈를 해결할 수 있습니다. 

PreparedStatement을 사용하는 SQL문을 작성해보면 아래와 같습니다. 

각각 문장에 대해서 설명하자면 이런식으로 쓸 수 있습니다.

PreparedStatement의 생성, 실행, 닫음, 현재갯수 등을 알아보기 위해 다음 질의문을 사용할 수 있습니다. 

이 정보가 중요한 이유를 JDBC의 경우를 예로 들어볼께요. 기본적으로 JDBC에서 useServerPrepStmts는 false입니다. 왜냐하면 MySQL은 PreparedStatement를 5.1부터 지원했기 때문에 JDBC의 호환성을 보장해야 하기 때문입니다. 그래서 useServerPrepStmts=true로 해야 PreparedStatement를 사용할 수 있습니다. false이면 JDBC는 자체 PreparedStatement를 사용합니다. 즉 겉으로는 서버 PreparedStatement를 사용하는 것처럼 보이지만 실은 그렇지 않다는 것이지요. 속으면 안되는 불편한 진실인 셈이죠. 그래서 PreparedStatement가 제대로 동작하는지 보려면 위에서 언급한 Com_stmt_prepare, Com_stmt_execute등을 직접 확인해야한다는 겁니다. 

주의할 것은 PreparedStatement는 세션단위로 관리되므로 PreparedStatement객체는 하나의 MySQL 커넥션에서만 사용할 수 있음을 기억해야 합니다. View처럼 등록하고 모든 세션에서 사용할 수 있는게 아니라는 겁니다. 그러므로 PreparedStatement의 장점을 십분 살리려면 해당 세션내에서 같은 SQL문을 수도없이 많이 반복해서 사용해야 하는 경우에 활용하면 좋다는 겁니다. 그외에는 효과가 미비합니다. 그게 아니라 반복되는 SQL을 패턴화시켜 효율성을 높히고 싶다면 View를 사용하세요.

앞의 MySQL Connection Manager와 Thread Manager의 소스분석에서 살펴보았듯이 연결 설정자, 스레드 설정자에 대해서 알면 도움이 될 거 같아서 몇자 정리해 봅니다.

BMT 도구 마련

• MySQL과 PostgreSQL에서 동작함.
• C++로 만들어져 추가 라이브러리(드라이버 등)가 필요없고 수정이나 확장이 용이함
• 시나리오 파일(smack파일)로 다양한 쿼리를 구사하게 함.
• 복수의 클라이언트를 가상 fork()로 생성하고 각 클라이언트(자식 프로세스)가 쿼리를 실행함.
• 데이터(영문숫자)를 생성하는 툴도 존재함
• 쿼리는 mysql_query()와 PQexec()를 실행. stored procedure는 사용하지 않음.

> wget http://vegan.net/tony/supersmack/super-smack-1.3.tar.gz
> tar xvfz super-smack-1.3.tar.gz
> ./configure --prefix=/database/server/super-smack \
--with-mysql=/database/server/mysql-5.5.25 \
--with-mysql-lib=/database/server/mysql-5.5.25/lib \
--with-mysql-include=/database/server/mysql-5.5.25/include \
--with-smacks-dir=/database/server/super-smack/smacks \
--with-datadir=/database/server/super-smack/data

> vi query.cc <- unsigned long
> make;make install

> ./super-smack ../smacks/select-key.smack 10 10
Error running query select count(*) from http_auth:Table 
 'test.http_auth' doesn't exist
Creating table 'http_auth'
Populating data file '/database/server/super-smack/data/words.dat' 
 with shell command 'gen-data -n 90000 -f %12-12s%n,%25-25s,%n,%d'
Loading data from file '/database/server/super-smack/data/words.dat' 
 into table 'http_auth'
Table http_auth is now ready for the test
Query Barrel Report for client smacker1
connect: max=2ms  min=0ms avg= 0ms from 10 clients 
Query_type    num_queries    max_time     min_time     q_per_s
select_index    200     0       0       7682.85

MySQL에서 연결 정보 확인

> mysqladmin -u root -p variables | grep max_connection
Enter password: 
| max_connections                          | 450    

> mysqladmin -u root -p extended-status |egrep '(Max|Threads_)'
Enter password: 
| Max_used_connections                     | 1           |
| Threads_cached                           | 0           |
| Threads_connected                        | 1           |
| Threads_created                          | 1           |
| Threads_running                          | 1           |

max_connections=450에서 부하 테스트

max_connections = 450
thread_cache_size = 8
//MySQL 리스타트 함.

mysql> grant all on test.* to 'testuser'@'localhost' 
 identified by 'testadmin';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

/database/server/super-smack/bin/super-smack ./select-key.smack 10 100\
 >> /database/samples/mysql_max_connection/data/log.txt
sleep 10
/database/server/super-smack/bin/super-smack ./select-key.smack 20 100\
 >> /database/samples/mysql_max_connection/data/log.txt
sleep 10
/database/server/super-smack/bin/super-smack ./select-key.smack 30 100\
 >> /database/samples/mysql_max_connection/data/log.txt
sleep 10
....
/database/server/super-smack/bin/super-smack ./select-key.smack 400 100\
 >> /database/samples/mysql_max_connection/data/log.txt

HOME=`/bin/echo $HOME`
DIR=/database/samples/mysql_max_connection/data
HOST=`/bin/hostname`

SOCKET=/tmp/mysql.sock
COMMAND="/database/server/mysql-5.5.25/bin/mysqladmin \
 -u root -ppwd extended-status"

while true
DATE=`/bin/date "+%Y/%m/%d %H:%M:%S"`
do
if [ -S $SOCKET ];then
   $COMMAND | /bin/awk '{print $2,$4}' | /bin/egrep -v \
   '(^ |Variable_name)' | \
   while read LABEL DATA
   do
      /bin/echo $DATE $DATA >> $DIR/$HOST.$LABEL
   done
else
   /bin/echo "MySQL is not running."
fi
sleep 10
done

mysql> show status like '%conn%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Aborted_connects     | 0     |
| Connections          | 8611  |
| Max_used_connections | 396   |
| Threads_connected    | 1     |
+----------------------+-------+

mysql> show status like '%thr%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Delayed_insert_threads                   | 0     |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
| Slow_launch_threads                      | 0     |
| Threads_cached                           | 7     |
| Threads_connected                        | 1     |
| Threads_created                          | 5575  |
| Threads_running                          | 1     |
+------------------------------------------+-------+

max_connections과 thread_cache_size 튜닝

[mysqld] 
max_connections = 450 
thread_cache_size = 450

mysql> show status like '%conn%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Aborted_connects     | 0     |
| Connections          | 8937  |
| Max_used_connections | 31    |
| Threads_connected    | 1     |
+----------------------+-------+
4 rows in set (0.00 sec)

mysql> show status like '%thr%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Delayed_insert_threads                   | 0     |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
| Slow_launch_threads                      | 0     |
| Threads_cached                           | 30    |
| Threads_connected                        | 1     |
| Threads_created                          | 31    |
| Threads_running                          | 1     |
+------------------------------------------+-------+

• max_connections, thread_cache 값 조정