Louis.Kim 의 제 3막

[root@DBTEST02:/root]# innobackupex --user=DB유저명 --password=비밀번호--socket=/tmp/mysql_8003.sock  /home/backup/game/

[mysqld]

user            = mysql

basedir         = /usr/local/mysql

datadir         = /home/DATABASE/Default_DB

tmpdir          = /usr/local/mysql/DB/tmp

[root@DBTEST02:/root]#innobackupex --defaults-file=/home/DATABASE/InnoDB/Game2/ --user=root --password= --socket=/tmp/mysql_8003.sock /home/backup/game/

xtrabackup: Error: --defaults-file must be specified first on the command line

[root@DBTEST02:/home/backup/game]# innobackupex --defaults-file=/home/DATABASE/InnoDB/Game2/ --user=root --password= --socket=/tmp/mysql_8003.sock --databases=DARKEDEN /home/backup/game/

160920 10:52:00 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.

           At the end of a successful backup run innobackupex

           prints "completed OK!".

160920 10:52:00  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql_8003.sock' as 'root'  (using password: NO).

160920 10:52:00  version_check Connected to MySQL server

160920 10:52:00  version_check Executing a version check against the server...

160920 10:52:00  version_check Done.

160920 10:52:00 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: /tmp/mysql_8003.sock

Using server version 5.6.32-debug-log

innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /home/DATABASE/InnoDB/Game2/

xtrabackup: open files limit requested 0, set to 1024

xtrabackup: using the following InnoDB configuration:

xtrabackup:   innodb_data_home_dir = .

xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup:   innodb_log_group_home_dir = ./

xtrabackup:   innodb_log_files_in_group = 2

xtrabackup:   innodb_log_file_size = 50331648

InnoDB: Number of pools: 1

160920 10:52:00 >> log scanned up to (21174002)

xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 442 for DARKEDEN/ShoulderArmorInfo, old maximum was 0

160920 10:52:01 >> log scanned up to (21174002)

160920 10:52:02 >> log scanned up to (21174002)

160920 10:52:03 >> log scanned up to (21174002)

160920 10:52:04 [01] Copying ./ibdata1 to /home/backup/game/2016-09-20_10-52-00/ibdata1

160920 10:52:04 >> log scanned up to (21174002)

160920 10:52:05 >> log scanned up to (21174002)

160920 10:52:05 [01]        ...done

160920 10:52:06 [01] Copying ./DARKEDEN/ContributeDegree.ibd to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/ContributeDegree.ibd

160920 10:52:06 [01]        ...done

160920 10:52:06 [01] Copying ./DARKEDEN/OustersWristletObject.ibd to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/OustersWristletObject.ibd

160920 10:52:06 [01]        ...done

160920 10:52:06 [01] Copying ./DARKEDEN/SubInventoryInfo.ibd to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/SubInventoryInfo.ibd

160920 10:52:06 [01]        ...done

160920 10:52:06 [01] Copying ./DARKEDEN/HighEnchantLog.ibd to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/HighEnchantLog.ibd

...

160920 10:52:10 [01] Copying ./DARKEDEN/VampireETCInfo.frm to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/VampireETCInfo.frm

160920 10:52:10 [01]        ...done

160920 10:52:10 Finished backing up non-InnoDB tables and files

160920 10:52:10 [00] Writing xtrabackup_binlog_info

160920 10:52:10 [00]        ...done

160920 10:52:10 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

xtrabackup: The latest check point (for incremental): '21174002'

xtrabackup: Stopping log copying thread.

.160920 10:52:10 >> log scanned up to (21174002)

160920 10:52:10 Executing UNLOCK TABLES

160920 10:52:10 All tables unlocked

160920 10:52:10 Backup created in directory '/home/backup/game/2016-09-20_10-52-00'

MySQL binlog position: filename 'Bin_Log.000051', position '120'

160920 10:52:10 [00] Writing backup-my.cnf

160920 10:52:10 [00]        ...done

160920 10:52:10 [00] Writing xtrabackup_info

160920 10:52:10 [00]        ...done

xtrabackup: Transaction log of lsn (21174002) to (21174002) was copied.

160920 10:52:11 completed OK!

* lsn 이 log sequence number 의 약자로써 전체 DB의 버전 정보를 뜻함

  - 변경 사항이 발생할 때마다 번호가 증가하여 채번

* Oracle로 따져보면 SCN 와 비슷

* innobackupex 명령어를 이용하여 user 와 passwd 를 입력하여 진행

* Port를 따로 지정한 경우나, 멀티로 띄우는 경우 --socket 옵션을 지정하여 명시

* /home/backup/game/  으로 어느 곳에 백업을 할 것인지 명시를 하게 되면, 자동으로 '2016-09-09_16-58-17'  처럼 날짜 시간으로 디렉토리 자동생성

* config 파일을 지정시 --defaults-file=MY.CNF 처럼 명시 가능

[root@DBTEST02:/root]# innobackupex --user=DB유저명 --password=비밀번호--socket=/tmp/mysql_8003.sock  /home/backup/game/

....

160909 16:58:19 Executing UNLOCK TABLES

160909 16:58:19 All tables unlocked

160909 16:58:19 Backup created in directory '/home/backup/game/2016-09-09_16-58-17'

MySQL binlog position: filename 'Bin_Log.000049', position '7396794'

160909 16:58:19 [00] Writing backup-my.cnf

160909 16:58:19 [00]        ...done

160909 16:58:19 [00] Writing xtrabackup_info

160909 16:58:19 [00]        ...done

xtrabackup: Transaction log of lsn (1600627) to (1600627) was copied.

160909 16:58:19 completed OK!

Yum으로 설치

$ yum localinstall percona-xtrabackup-24*

* key 관련하여 에러메시지는

1.  vi /etc/yum.repos.d/percona-release.repo

$ yum update

-- 꽤나 시간이 오래 걸려도 나름 이후에는 한방에 되는 경우도 있다.

$ yum list | grep percona

...
percona-xtrabackup-20.x86_64               2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-debuginfo.x86_64     2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-test.x86_64          2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-21.x86_64               2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-21-debuginfo.x86_64     2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-22.x86_64               2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-22-debuginfo.x86_64     2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64        2.3.2-1.el5                 percona-release-x86_64
percona-xtrabackup-test.x86_64             2.3.2-1.el5                 percona-release-x86_64
percona-xtrabackup-test-21.x86_64          2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-test-22.x86_64          2.2.13-1.el5                percona-release-x86_64
...

$ yum install percona-xtrabackup

장점

- DB 보안 향상

  -> 자체적인 보안 설정 기능을 가지고 있으며, Stored program 단위로 실행 권한을 부여할 수 있다.

  -> 세밀한 권한 제어가 가능

  -> SQL 인젝션과 같은 기본적인 보안 사고는 피할 수 있다.

  -> SQL 의 문법적인 취약점을 이용한 해킹이 어렵다

- 기능의 추상화

  ex) 여러 테이블에 걸쳐 유일한 일련번호를 발급하되, 일련번호에 자체적인 헤더 값과 시간 정보를 덧붙여서 생성

  -> 이러한 요구사항을 해결하기 위해서는 단순 Table 시퀀스(auto increment)를 사용하지 못하는데 Stored procedure 이용하면 해결가능

- 네트워크 소요 시간 절감

  -> 하나하나의 쿼리가 아주 가볍고 빠르게 처리될 수 있다면 네트워크를 경유하는 데 걸리는 시간이 문제가 될 수 있음

   (0.01만에 완료되는 쿼리가 0.1초의 네트워크 시간과  동일한 쿼리가 다량의 건수로 진행되는 경우 문제 소지)

- 절차적 기능 구현

  -> SQL 문장에 IF / While 과 같은 제어문장 사용 가능

    ( 어플리케이션 소스코드를 줄여줄 수 있음)

- 개발 업무의 구분

  -> 스토어드 프로그램을 만들어 API 처럼 제공하여 업무 구분

단점

- 낮은 처리 성능

  -> 다른 DBMS에 비해 Stored Procedure 프로그램은 성능이나 최적화가 부족하여 수행 능력이 떨어짐(오라클 pl/sql보다 2배 떨어짐)

  -> 문자열이나 숫자 연산을 위해 사용하기에는 나쁜 선택

  -> 하지만 한 번에 많은 쿼리를 실행해야 할 때 효율적임

- Application 코드의 조각화

 ??????이해가 되지 않기에 패스..

select gender from employees group by gender;

select distinct(gender) from employees;

동일한 로직을 이용하기에 성능의 차이는 없을 거지만,

group by는 정렬를 하기에 distinct가 조금 더 성능적으로 빠르다.

Using filesort

쿼리에 ORDER BY가 포함되어있는 경우 DBMS 내부적으로 sort를 한다.(MySQL의 경우 퀵소트 알고리즘을 사용)

소트를 처리하기 위해서 메모리공간을 사용하며, 메모리가 부족할 경우 임시파일을 사용한다.

(임시파일은 HDD등의 저장장치에 생성되며 PC에서 가장 느린 성능을 보여주는 부분이다. 

-> 결과적으로 EXPLAIN시 Extra필드에 Using filesort가 표시된다면 이는 파일을 사용할 가능성이 크다는 것이며, 

가장 느린성능을 보여줄것이다.)

하지만 인덱스 순으로 행을 fetch할수 있으면 Filesort는 필요없게된다. 

이런경우 당연히 Extra필드에도 Using Filesort가 표시되지 않는다.

ORDER BY 구문에 인덱스가 사용되도록 쿼리나 테이블을 튜닝하자.

여기까지는 하나의 테이블에서 ORDER BY를 사용한 경우이다.

2개 이상의 테이블을 JOIN해야 하며 결과는 정렬되어 있어야 하는경우엔 어떻게 될까?

이경우엔 3가지 유형이 있다.

1. 첫 테이블을 인덱스를 이용하여 정렬하고, JOIN하는 경우

2. 첫 테이블을 FileSort해서 Join하는 경우 -> Using filesort

3. JOIN후 Filesort하는 경우(JOIN한 결과는 인덱스가 없으므로 JOIN후 Sort할때는 항상 Filesort이다.) -> Using Temporary, Using filesort

뒤로 갈수록 처리가 무거워 지므로 1,2번의 실행계획이 잡히도록 유도하는 것이 좋다.

1번을 유도하는 방법 -> WHERE절의 검색조건과 ORDER BY절의 소트조건을 하나의 테이블에 집중시킨다.

  MySQL Binary Log 는 add, delete, insert, update 등의 query 가 저장되어 있는 파일로서 MySQL 을  설치하게 되면 기본적으로 MySQL Binary Log 가 생성됩니다.
 Binary Log 를 쌓지 않아도 되는 MySQL 구동 환경에서는 Binary Log 를 삭제하므로, 디스크 공간
 여유 공간을 확보할 수 있는데, MySQL Binary Log 를 지우는 방법에 대해서 알아보겠습니다.

1. MySQL Replication 환경에서 지우기

  - MySQL Replication MASTER 서버
    shell> mysql -u root -p
    mysql> RESET MASTER;

  - MySQL Replication SLAVE 서버
    shell> mysql -u root -p
    mysql> RESET MASTER;

2. MySQL Binary Log sequence number 또는 특정 일자로 지우기

  shell> mysql -u root -p
  mysql> PURGE BINARY LOGS TO 'mysql-bin.000015';
  shell> mysql -u root -p
  mysql> PURGE BINARY LOGS BEFORE '2009-05-01 00:00:00';

3. mysqladmin flush-logs 명령어를 통해서 MySQL Binary Log 지우기

   shell> mysqladmin -u root -p flush-logs

4. MySQL Binary Log 생성을 방지하는 방법

  /etc/my.cnf 파일에서 아래 라인을 주석 처리
  log-bin

5. MySQL Binary Log 를 특정 1주일까지만 생성 및 보관하기
  /etc/my.cnf 파일에서 아래 라인을 추가

  expire_logs_days = 7

 echo $echo_n "Starting MySQL"

    if test -x $bindir/mysqld_safe

    then

      # Give extra arguments to mysqld with the my.cnf file. This script

      # may be overwritten at next upgrade.

      $bindir/mysqld_safe --defaults-file=/etc/rmy.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

      wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?