'팁&테크/MySQL'에 해당되는 글 23건

  1. 2012.07.24 MySQL 테이블별 백업
  2. 2012.02.17 MySQL 데이타 타입별 디스크 저장크기(5.1)
  3. 2012.02.10 MySQL 데이타 타입 (5.1기준) 1
  4. 2012.02.06 MySQL root 비번 변경
  5. 2011.09.30 [InnoDB] 데이타 파일만 백업 후 복구시 유의사항
  6. 2011.09.30 [Innodb 백업] Percona xtrabackup 설치 및 사용 2
  7. 2011.09.26 MySQL 파티션 테이블 백업 및 복구
  8. 2011.09.26 MySQL 파티셔닝
  9. 2011.09.26 MySQL BIN 로그 삭제
  10. 2011.08.17 MySQL InnoDB Table Space 관리하기
2012. 7. 24. 11:06

MySQL 테이블별 백업

#!/bin/sh


DBHOST="localhost"

DBUSER=""

DBPWD=""


BACKUPDIR="/root/backup/"

OPTIONS="--default-character-set=utf8 --routines --single-transaction --quick"


DATE=`date +%Y%m%d`

DELDATE=`date --date "30 day ago" +%Y%m%d`


rm -rf ${BACKUPDIR}${DELDATE}

mkdir ${BACKUPDIR}${DATE}


#전체 데이타베이스 백업시

RESULT=`/usr/local/mysql/bin/mysql -u$DBUSER -p$DBPWD -h$DBHOST -e "show databases" | grep -v Database`

#특정 데이타베이스 백업시(DB명 띄어쓰기로 구분해서 나열)

RESULT="mysql information_schema db명"


for DB in $RESULT; do


  mkdir ${BACKUPDIR}${DATE}/${DB}


  TABLELIST=`/usr/local/mysql/bin/mysql -u$DBUSER -p$DBPWD -h$DBHOST $DB -e "show tables" | grep -v Tables_in_$DB`


  for TABLE in $TABLELIST; do


    FNAME_DESC=${BACKUPDIR}${DATE}/${DB}/${TABLE}_desc_${DATE}.sql

    FNAME_DATA=${BACKUPDIR}${DATE}/${DB}/${TABLE}_data_${DATE}.sql

    /usr/local/mysql/bin/mysqldump -u$DBUSER -p$DBPWD -h$DBHOST $OPTIONS --no-data $DB $TABLE >> $FNAME_DESC

    /usr/local/mysql/bin/mysqldump -u$DBUSER -p$DBPWD -h$DBHOST $OPTIONS --no-create-db $DB $TABLE >> $FNAME_DATA

    echo "set foreign_key_checks=1;" >> $FNAME_DATA

    gzip --rsyncable $FNAME_DATA


  done

done

2012. 2. 17. 10:55

MySQL 데이타 타입별 디스크 저장크기(5.1)

- 숫자형
 데이타 타입  용량  참고
 TINYINT  1 byte  
 SMALLINT  2 byte  
 MEDIUMINT  3 byte  
 INT,INTEGER  4 byte  
 BIGINT  8 byte  
 FLOAT(p)  4 byte(p <= 24), 8 byte(p <= 53)  
 FLOAT  4 byte  
 DOUBLE, REAL  8 byte  
 DECIMAL(m, d), NUMERIC(m, d)    
 BIT(m)  ((m+7)/8) byte  


2012. 2. 10. 15:00

MySQL 데이타 타입 (5.1기준)

BIT[(M)] (M+7)/8byte
- 범위 : 1 ~ 64, M은 값당 비트수, 기본 생략

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
1byte
- 범위 : -128 에서 127 (UNSIGNED 일 경우 0 에 255) 

BOOL, BOOLEAN 
- 범위 : 0 / 1, TINYINT(1)과 동일 

SMALLINT[(M)] [UNSIGNED] [ZEROFILL] 2byte
- 범위 : -32768 에서 32767 (UNSIGNED 일 경우 0 에 65535) 

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] 3byte
- 범위 : -8388608 에서 8388607 (UNSIGNED 일 경우 0 에 16777215) 

INT[(M)] [UNSIGNED] [ZEROFILL] 4byte
- 범위 : -2147483648 에서 2147483647 (UNSIGNED 일 경우 0 에 4294967295) 
- 주의 : 모든 연산은 "signed BIGINT" 로 이루어진다. 따라서 더하기 곱하기 등으로 "signed BIGINT" 의 범위를 벗어난다면 값이 틀려질 것이다. 

INTEGER[(M)] [UNSIGNED] [ZEROFILL] 
- 범위 : INT 와 동일 

BIGINT[(M)] [UNSIGNED] [ZEROFILL] 8byte
- 범위 : -9223372036854775808 에서 9223372036854775807 (UNSIGNED 일 경우 0 에 18446744073709551615) 

SERIAL
- BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 와 동일 

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
- 범위 : M(65글자).D(30글자) , M 생략시 기본 10, D생략시 기본 0

FLOAT[(M,D)] [ZEROFILL] 4byte
- 범위 : -3.402823466E+38 에서 -1.175494351E-38, 0 그리고 1.175494351E-38 에서 3.402823466E+38 
- 주의 : unsigned 를 설정할수 없다. 

DOUBLE[(M,D)] [ZEROFILL] 8byte
- 범위 : -1.7976931348623157E+308 에서 -2.2250738585072014E-308, 0 그리고 2.2250738585072014E-308 에서 1.7976931348623157E+308 
- 주의 : unsigned 를 설정할수 없다. 

REAL[(M,D)] [ZEROFILL] 
- 범위 : DOUBLE 과 동일 

DATE 3byte
- 범위 : '1000-01-01' 에서 '9999-12-31' 

DATETIME 8byte
- 범위 : '1000-01-01 00:00:00' 에서 '9999-12-31 23:59:59' 

TIMESTAMP[(M)] 4byte
- 범위 : '1970-01-01 00:00:00' 에서 '2038-01-19 03:14:07'

TIME 3byte
- 범위 : '-838:59:59'  에서 '838:59:59'

YEAR 1byte
- 1901 부터 2155까지, 단 0000 설정가능 

CHAR(M) [BINARY] 0 ~ 255 * (엔코딩 별 문자1개의 byte수)
- 범위 : 1 에서 255 글자 
- 주의 : 고정길이로 길이가 작은 값이 입력되면 오른쪽에 스페이스로 채워져서 입력된다. BINARY 키워드가 주어지지 않을 경우 대소문자 구분없이 검색한다. 

VARCHAR(M) [BINARY] 0 ~ 255 인경우 + 1byte, 255 이상인 경우 0 ~ 65534 + 2byte
- 범위 : 1 ~ 65534 글자(단 UTF-8인 경우 21844까지), 5.0 이하 버젼 1 ~ 255
- 주의 : BINARY 키워드가 주어지지 않을 경우 대소문자 구분없이 검색한다. 

BINARY(M) 0 ~ 255 byte
- CHAR 과 동일

VARBINARY(M) 
- VARCHAR 과 동일 

TINYBLOB  길이 + 1byte 
- 범위 : 최대 255 

TINYTEXT 길이 + 1byte
- 범위 : 최대 255 글자 

BLOB 길이 + 2byte
- 범위 : 최대 65535 

TEXT 길이 + 2byte
- 범위 : 최대 65535 글자 

MEDIUMBLOB 길이 + 3byte
- 범위 : 최대 16777215

MEDIUMTEXT 길이 + 3byte
- 범위 : 최대 16777215 글자 

LONGBLOB 길이 + 4byte
- 범위 : 최대 4294967295 글자 

LONGTEXT 길이 + 4byte
- 범위 : 최대 4294967295 글자 

ENUM('value1','value2',...) 1 또는 2byte, 설정 갯수에 따라.
- 범위 : 'value1', 'value2', ..., or NULL 만이 입력가능하다. 최대 65535 개의 다른 값이 들어갈수 있다. 

SET('value1','value2',...) 1, 2, 3, 4, 8 byte, 설정 갯수에 따라.
- 범위 : 'value1', 'value2', ..., or NULL 만이 입력가능하다. 최대 64 개의 다른 값이 들어갈수 있다.
2012. 2. 6. 11:48

MySQL root 비번 변경


1. mysql  프로세스 kill
/usr/local/mysql/share/mysql/mysql.server stop
또는 kill -9 pid번호 (비추)
 
2.싱글모드로 실행
./mysql/bin/mysqld_safe --user=mysql --skip-grant-tables &

3. root 패스워드를 변경
mysql> use mysql;
mysql> update user set password=password('새로운암호입력') where user='root';
mysql> flush privileges;

4. MySQL 재시작(정상적인 방법)
2011. 9. 30. 18:26

[InnoDB] 데이타 파일만 백업 후 복구시 유의사항

hot backup 으로 파일만 백업 했다가 복구 할때 기존에 테이블을 삭제한 후 복구하려 할때
table doesn't exists 라는 오류가 뜬다. 하지만 파일이 존재할 경우 show tables 할 경우 테이블이 존재하는 것 처럼 보이기 때문에 혼란이 생길 수 있다.

예)
member 테이블 hot backup -> member.frm, member.ibd  백업
그 후 drop table member 해서 테이블을 삭제한다.
파일을 기존 디렉토리에 다시 복사한다.
이 경우 show tables 를 하면 member 가 리스트되어 나오나 테이블에 관한 쿼리가 실행될 경우
table `member`  doesn't exists 라는 오류가 발생하게 된다.

테이블 정의에 관한 부분은 .frm 파일에 존재하지만 테이블 존재 유무에 관한 부분은 table space에 존재하기 때문에 삭제된(없는) 테이블을 사용하려 하기때문에 발생하는 오류로 보인다.

이 경우에는 동일한 테이블 명으로 테이블을 임의로 생성하고 frm, idb 파일을 다시 덮어 씌울 경우 정상적으로 동작하게 된다. 
2011. 9. 30. 16:38

[Innodb 백업] Percona xtrabackup 설치 및 사용

보통 MySQL 을 백업할땐 mysqldump 를 쓰거나 데이타 디렉토리를 통째로 카피해서 관리하게 된다. 
mysqldump의 경우 복구시 테이블을 생성하고 다시 데이타를 쌓는 부분이라 복구가 불편하고 거기다  InnoDB에서 외래키를 설정했 을 경우 순서가 맞지 않으면 인서트시에 오류가 발생 할 수도 있다.

그리고 파일을 복사할 경우에는 데이타베이스를 중지시키고 데이타파일만을 덮어 씌우면 복구가 완료되기 때문에 편리한 방법이다 다만 파일을 복사할 경우 서버가 운영중인 경우에는 끝나지 않는 트랜잭션이 있을 경우 디스크에 반영되지 않은 상태에서 복사를 하게 되므로 완전한 데이타가 복사되었다는 보장도 할 수 없게 된다.
그래서 완전한 데이타 복사를 위해서는 데이타베이스를 완전히 정지 시키고 파일을 복사해야만 한다.
이렇게 데이타베이스를 정지시키고 복사를 하거나 백업을 하는 것을 cold copy 또는 cold backup 이라고 한다.

백업을 위해 데이타베이스 서버를 정지시킨다면 서비스 운영에 지장이 있기 때문에 데이타베이스를 정지시키지 않고 백업 또는 카피를 하는 것을 cold copy의 반대 개념으로 hot copy 또는 hot backup 이라고 한다.

MyIsam 테이블 타입의 경우 MySQL에서 기본적으로  mysqlhotcopy 라는 툴일 지원하지만 InnoDB의 경우에는 innnodb 사에서 inbackup 이라는 툴을 제공하지만 상용이라 비용을 부담해야 한다. 1년에 500 달러 정도.

http://percona.com 라는 사이트에서 이와 같은 innodb를 hot backup 할 수 있는 xtrabackup를 오픈소스로 제공하기 때문에 이 프로그램의 설치와 사용에 대해 써보겠다.

우선 아래 주소에서 공식 메뉴얼을 한번 살펴보자.
설치에 대한 공식 메뉴얼 : http://www.percona.com/doc/percona-xtrabackup/installation.html

상당히 자세하게 나와있기 때문에 따로 설명할 필요도 없겠지만 영어 울렁증이 있는 사람을 위해 정리를 해보겠다. 
아 그리고 나는 Centos 6.0을 쓰고 있으며 귀차니즘으로 인해 그냥 YUM으로 설치를 하겠다. 위 페이지에 
download page 를 찾아가보면 버젼별로 banary, source, rpm, deb 을 제공해주고 있으니 다운받아 설치하면 되겠다. 

YUM으로 설치를 하기 위해서는 percona에서 제공하는 repository를 추가 해야 한다.
64bit : rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
32bit : rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.i386.rpm
이 명령을 실행하여 repository를 추가하도록 한다.

처음 어리둥절 했던 부분이 
yum list percona 했을때 나오는 것들이었는데 Percona-Server-server-51.x86_64 이런것들은 percona 에서 제공하는 mysql 을 변형한 데이타베이스로 보인다. 설치해보지는 않았지만 이곳저곳에서 줏어 들은 바로는 Percona-Server에서는 InnoDB 를 수정?한 XtraDB 타입을 제공하는 것으로 보인다.

yum list xtrabackup* 하면 실제 내가 필요한 패키지가 리스팅 되게된다.
yum install xtrabackup 해서 설치를 하자.

xtrabackup에 설치되는 실행파일은 3가지로 yum(rpm)설치 일 경우 /usr/bin 에 설치된다.
innobackupex : 펄로된 backup 스크립트 myisam, innodb, xtradb 지원
xtrabackup : c로 컴파일된 바이너리, 실제 실행파일
tar4ibd : 백업파일을 tar로 저장할때 사용하는 실행파일

1. 백업디렉토리 생성 
# mkdir -p /mysqlbackup/xtrabackup/

2. 데이타 백업
# /usr/bin/innobackupex --user UUUUUUU --password PPPPPPP /mysqlbackup/xtrabackup
.............
.............
>> log scanned up to (542812997)
xtrabackup: Stopping log copying thread.
xtrabackup: Transaction log of lsn (542812997) to (542812997) was copied.
110719 18:25:44 innobackupex: All tables unlocked
110719 18:25:44 innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/mysqlbackup/xtrabackup/2011-07-19_18-25-17'
innobackupex: MySQL binlog position: filename 'mysql-bin.000012', position 599281561
innobackupex: MySQL slave binlog position: master host '', filename '', position
110719 18:25:44 innobackupex: completed OK!

/mysqlbackup/xtrabackup/2011-07-19_18-25-17 와 같은 형태로 생성된다.
복원시 my.cnf 설정이 동일해야 하므로 my.cnf 도 백업 해두는 것이 좋다.

3. 로그 백업
# /usr/bin/innobackupex --user UUUUUUU --password PPPPPPP --apply-log /mysqlbackup/xtrabackup/2011-07-19_18-25-17
.............
.............
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
110719 18:32:40 InnoDB: Starting shutdown...
110719 18:32:40 InnoDB: Shutdown completed; log sequence number 542814220
110719 18:32:40 innobackupex: completed OK!

4. 복구, 데이타베이스를 중지 시키고 data디렉토리를 비운후에(기왕이면 기존파일 따로 보관, 새로 만들어서)
# /usr/bin/innobackupex --copy-back /mysqlbackup/xtrabackup/2011-07-19_18-25-17

5. 혹시나 ib_logfile에서 사이즈 오류가 날경우 my.cnf 를 수정한다.
InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes
InnoDB: than specified in the .cnf file 0 126877696 bytes!
110720 13:48:47 [ERROR] Plugin 'InnoDB' init function returned error.
110720 13:48:47 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
110720 13:48:47 [ERROR] Unknown/unsupported storage engine: InnoDB
110720 13:48:47 [ERROR] Aborting
.............
.............

# emacs /etc/my.cnf
innodb_log_file_size = XXX M

나머지 부분은 메뉴얼을 보면 쉽게 나와있으므로 한번 읽어보기 바람.

옵션 레퍼런스
Options

--help
This option displays a help screen and exits.

--version
This option displays the innobackupex version and copyright notice and then exits.

--apply-log
Prepare a backup in BACKUP-DIR by applying the transaction log file named xtrabackup_logfile located in the same directory. Also, create new transaction logs. The InnoDB configuration is read from the file backup-my.cnf created by innobackupex when the backup was made.

--redo-only
This option is passed directly to xtrabackup’s xtrabackup --apply-log-only option. This forces xtrabackup to skip the “rollback” phase and do a “redo” only. This is necessary if the backup will have incremental changes applied to it later. See the xtrabackup documentation for details.

--copy-back
Copy all the files in a previously made backup from the backup directory to their original locations.

--include
This option is a regular expression to be matched against table names in databasename.tablename format. It is passed directly to xtrabackup ‘s xtrabackup --tables option. See the xtrabackup documentation for details.

--defaults-file
This option accepts a string argument that specifies what file to read the default MySQL options from. It is also passed directly to xtrabackup ‘s defaults-file option. See the xtrabackup documentation for details.

--databases
This option accepts a string argument that specifies the list of databases that innobackupex should back up. The list is of the form databasename1[.table_name1] databasename2[.table_name2] .... If this option is not specified, all databases containing MyISAM and InnoDB tables will be backed up. Please make sure that --databases contains all of the InnoDB databases and tables, so that all of the innodb.frm files are also backed up. In case the list is very long, this can be specified in a file, and the full path of the file can be specified instead of the list. (See option --tables-file.)

--tables-file
This option accepts a string argument that specifies the file in which there are a list of names of the form database.table, one per line. The option is passed directly to xtrabackup ‘s --tables-file option.

--throttle
This option accepts an integer argument that specifies the number of I/O operations (i.e., pairs of read+write) per second. It is passed directly to xtrabackup’s xtrabackup --throttle option.

--export
This option is passed directly to xtrabackup --export option. It enables exporting individual tables for import into another server. See the xtrabackup documentation for details.

--use-memory
This option accepts a string argument that specifies the amount of memory in bytes for xtrabackup to use for crash recovery while preparing a backup. Multiples are supported providing the unit (e.g. 1MB, 1GB). It is used only with the option --apply-log. It is passed directly to xtrabackup ‘s xtrabackup --use-memory option. See the xtrabackup documentation for details.

--password = 'PASSWORD'
This option accepts a string argument specifying the password to use when connecting to the database. It is passed to the mysql child process without alteration. See mysql –help for details.

--user = 'USER'
This option accepts a string argument that specifies the user (i.e., the MySQL username used when connecting to the server) to login as, if that’s not the current user. It is passed to the mysql child process without alteration. See mysql –help for details.

--port
This option accepts a string argument that specifies the port to use when connecting to the database server with TCP/IP. It is passed to the mysql child process. It is passed to the mysql child process without alteration. See mysql –help for details.

--socket
This option accepts a string argument that specifies the socket to use when connecting to the local database server with a UNIX domain socket. It is passed to the mysql child process without alteration. See mysql –help for details.

--host
This option accepts a string argument that specifies the host to use when connecting to the database server with TCP/IP. It is passed to the mysql child process without alteration. See mysql –help for details.

--no-timestamp
This option prevents creation of a time-stamped subdirectory of the BACKUP-ROOT-DIR given on the command line. When it is specified, the backup is done in BACKUP-ROOT-DIR instead.

--slave-info
This option is useful when backing up a replication slave server. It prints the binary log position and name of the master server. It also writes this information to the xtrabackup_slave_info file as a CHANGE MASTER command. A new slave for this master can be set up by starting a slave server on this backup and issuing a CHANGE MASTER command with the binary log position saved in the xtrabackup_slave_info file.

--no-lock
Use this option to disable table lock with FLUSH TABLES WITH READ LOCK. Use it only if ALL your tables are InnoDB and you DO NOT CARE about the binary log position of the backup.

--ibbackup-binary = 'autodetect'
This option accepts a string argument that specifies which xtrabackup binary should be used. The string should be the command used to run XtraBackup. The option can be useful if the xtrabackup binary is not in your search path or working directory and the database server is not accessible at the moment. If this option is not specified, innobackupex attempts to determine the binary to use automatically. By default, xtrabackup is the command used. When option --apply-log is specified, the binary is used whose name is in the file xtrabackup_binary in the backup directory, if that file exists, or will attempt to autodetect it. However, if --copy-back is selected, xtrabackup is used unless other is specified.

--incremental
This option tells xtrabackup to create an incremental backup, rather than a full one. It is passed to the xtrabackup child process. When this option is specified, either --incremental-lsn or --incremental-basedir can also be given. If neither option is given, option --incremental-basedir is passed to xtrabackup by default, set to the first timestamped backup directory in the backup base directory.

--incremental-basedir
This option accepts a string argument that specifies the directory containing the full backup that is the base dataset for the incremental backup. It is used with the --incremental option.

--incremental-dir
This option accepts a string argument that specifies the directory where the incremental backup will be combined with the full backup to make a new full backup. It is used with the --incremental option.

--incremental-lsn
This option accepts a string argument that specifies the log sequence number (LSN) to use for the incremental backup. It is used with the --incremental option. It is used instead of specifying --incremental-basedir. For databases created by MySQL and Percona Server 5.0-series versions, specify the as two 32-bit integers in high:low format. For databases created in 5.1 and later, specify the LSN as a single 64-bit integer.

--extra-lsndir
This option accepts a string argument that specifies the directory in which to save an extra copy of the xtrabackup_checkpoints file. It is passed directly to xtrabackup ‘s --extra-lsndir option. See the xtrabackup documentation for details.

--remote-host
This option accepts a string argument that specifies the remote host on which the backup files will be created, by using an ssh connection.

--stream
This option accepts a string argument that specifies the format in which to do the streamed backup. The backup will be done to STDOUT in the specified format. Currently, the only supported format is tar. Uses tar4ibd, which is available in XtraBackup distributions. If you specify a path after this option, it will be interpreted as the value of tmpdir.

--tmpdir
This option accepts a string argument that specifies the location where a temporary file will be stored. It should be used when --remote-host or --stream is specified. For these options, the transaction log will first be stored to a temporary file, before streaming or copying to a remote host. This option specifies the location where that temporary file will be stored. If the option is not specifed, the default is to use the value of tmpdir read from the server configuration.

--tar4ibd
Uses tar4ibd in --stream mode. It is enabled by default if no other command is specified (e.g. --force-tar).

--force-tar
This option forces the use of tar when creating a streamed backup, rather than tar4ibd, which is the default.

--scp-opt = '-Cp -c arcfour'
This option accepts a string argument that specifies the command line options to pass to scp when the option --remost-host is specified. If the option is not specified, the default options are -Cp -c arcfour.

--parallel
This option accepts an integer argument that specifies the number of threads the xtrabackup child process should use to back up files concurrently. Note that this option works on file level, that is, if you have several .ibd files, they will be copied in parallel. If you have just single big .ibd file, it will have no effect. It is passed directly to xtrabackup’s xtrabackup --parallel option. See the xtrabackup documentation for details.

--safe-slave-backup
Stop slave SQL thread and wait to start backup until Slave_open_temp_tables in SHOW STATUS is zero. If there are no open temporary tables, the backup will take place, otherwise the SQL thread will be started and stopped until there are no open temporary tables. The backup will fail if Slave_open_temp_tables does not become zero after --safe-slave-backup-timeout seconds. The slave SQL thread will be restarted when the backup finishes.

--safe-slave-backup-timeout
How many seconds --safe-slave-backup` should wait for Slave_open_temp_tables to become zero. Defaults to 300 seconds. 
2011. 9. 26. 11:23

MySQL 파티션 테이블 백업 및 복구

ADWorks 파티셔닝 테이블 백업 정책

1. 파티셔닝 테이블
 - ADWORKS_TS

2. 사용하지 않는 테이블 ( adws2v1, adws2v2 Miner 정지 )
 - ADWORKS_NB
 - ADWORKS_SPM
 - ADWORKS_SPM_TS
 - ADWORKS_STAT_BOUNCE
 - ADWORKS_STAT_SPM

3. ADWORKS_TS 데이터는 2년 유지
 ex) 2011년 01월 01일 2008년 데이터 백업 후 삭제 (추후 복구가능 - 하지만 시간이 다소 걸릴 수 있음)

=====================================================================================================

ADWORKS_TS 파티셔닝 테이블 백업

]# mysqldump --user=username --password=password --no-create-info --where="TS_ID<2008030000000000000" adworksv2 ADWORKS_TS > /P0802.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008030000000000000 and TS_ID<2008040000000000000" adworksv2 ADWORKS_TS > /P0803.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008040000000000000 and TS_ID<2008050000000000000" adworksv2 ADWORKS_TS > /P0804.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008050000000000000 and TS_ID<2008060000000000000" adworksv2 ADWORKS_TS > /P0805.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008060000000000000 and TS_ID<2008070000000000000" adworksv2 ADWORKS_TS > /P0806.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008070000000000000 and TS_ID<2008080000000000000" adworksv2 ADWORKS_TS > /P0807.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008080000000000000 and TS_ID<2008090000000000000" adworksv2 ADWORKS_TS > /P0808.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008090000000000000 and TS_ID<2008100000000000000" adworksv2 ADWORKS_TS > /P0809.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008100000000000000 and TS_ID<2008110000000000000" adworksv2 ADWORKS_TS > /P0810.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008110000000000000 and TS_ID<2008120000000000000" adworksv2 ADWORKS_TS > /P0811.sql
]# mysqldump --user=username --password=password --no-create-info --where="TS_ID>=2008120000000000000 and TS_ID<2009010000000000000" adworksv2 ADWORKS_TS > /P0812.sql


=====================================================================================================

ADWORKS_TS 파티셔닝 테이블 해당하는 년 복구

1. 해당하는 년의 파티셔닝 테이블을 생성한 후에 백업파일 넣음

 - 해당하는 년의 테이블 생성 (장점 : 운영중인 ADWORKS_TS 테이블에 LOCK 걸리지 않는다. 단점 :  랜딩상세,전환상세 보고서 다운로드시 소스를 변경해야한다.)
CREATE TABLE `ADWORKS_TS` (
  `TS_ID` bigint(19) unsigned NOT NULL COMMENT '트랜잭션아이디',
  `LANDING_YN` char(1) DEFAULT '' COMMENT '랜딩Y/N',
  `TARGET_YN` char(1) DEFAULT '' COMMENT '타겟Y/N',
  `OLD_YN` char(1) DEFAULT '' COMMENT '구버전Y/N',
  `VERSION` varchar(4) DEFAULT NULL COMMENT '스크립트버전',
  `SPONSOR_NO` int(8) DEFAULT NULL COMMENT '광고주번호',
  `CMP_KWD` varchar(100) DEFAULT NULL COMMENT '구매키워드',
  `CMP_KWD_NO` varchar(20) DEFAULT NULL COMMENT '구매키워드일련번호',
  `VHC_CD` char(4) DEFAULT NULL COMMENT '광고상품아이디',
  `FST_TS_ID` bigint(19) DEFAULT NULL COMMENT '최초트랜잭션아이디',
  `LST_TS_ID` bigint(19) DEFAULT NULL COMMENT '마지막트랜잭션아이디',
  `VST_URL` varchar(3000) DEFAULT NULL COMMENT '방분URL',
  `REF_URL` varchar(3000) DEFAULT NULL COMMENT '참조URL',
  `TG_PG_NM` varchar(100) DEFAULT NULL COMMENT '타겟페이지 명',
  `TG_PRD_AMT` varchar(22) DEFAULT '0' COMMENT '타겟페이지 구매금액',
  `TG_E1` varchar(100) DEFAULT NULL COMMENT '타겟페이지 구매기타1',
  `TG_E2` varchar(100) DEFAULT NULL COMMENT '타겟페이지 구매기타2',
  `TG_E3` varchar(100) DEFAULT NULL COMMENT '타겟페이지 구매기타3',
  `TG_E4` varchar(100) DEFAULT NULL COMMENT '타겟페이지 구매기타4',
  `TG_E5` varchar(100) DEFAULT NULL COMMENT '타겟페이지 구매기타5',
  `TG_AC` char(4) DEFAULT NULL COMMENT '전환유형',
  `TG_AN` varchar(10) DEFAULT NULL COMMENT '광고주별 전환추젹 설정 인련번호',
  `IP_ADDR` varchar(32) DEFAULT NULL COMMENT '클라이언트아이피',
  `VST_YYYY` char(4) DEFAULT NULL COMMENT '방문 년',
  `VST_MM` char(2) DEFAULT NULL COMMENT '방문 월',
  `VST_DD` char(2) DEFAULT NULL COMMENT '방문 일',
  `VST_HH` char(2) DEFAULT NULL COMMENT '방문 시',
  `VST_MMSS` char(4) DEFAULT NULL COMMENT '방문 분초',
  `VST_WEEK_OF_YEAR` int(10) unsigned DEFAULT NULL COMMENT '방문 주',
  `VST_DAY_OF_WEEK` int(10) unsigned DEFAULT NULL COMMENT '방문 요일',
  `VST_DT` char(10) DEFAULT NULL COMMENT '방문일[2008.12.25]',
  `VST_FST_DT` char(10) DEFAULT NULL COMMENT '최초방문일[2008.12.25]',
  `VST_FST_HH` char(2) DEFAULT NULL,
  `VST_LST_DT` char(10) DEFAULT NULL COMMENT '마지막랜딩방문일[2008.12.25]',
  `VST_LST_HH` char(2) DEFAULT NULL,
  `REF_URL_HOST` varchar(80) DEFAULT NULL COMMENT '참조 URL 호스트',
  `REF_URL_DOMAIN` varchar(80) DEFAULT NULL COMMENT '참조 URL 도메인',
  `QUERY_KWD` varchar(200) DEFAULT NULL COMMENT '쿼리 키워드',
  `CRT_DT` datetime DEFAULT NULL,
  `USER_AGENT` varchar(1024) DEFAULT NULL COMMENT 'user-agent',
  `DEVICE_NM` varchar(50) DEFAULT NULL COMMENT '디바이스명',
  PRIMARY KEY (`TS_ID`),
  KEY `NewIndex1` (`SPONSOR_NO`,`VST_DT`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY RANGE (TS_ID) (PARTITION P0802 VALUES LESS THAN (2008030000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0803 VALUES LESS THAN (2008040000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0804 VALUES LESS THAN (2008050000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0805 VALUES LESS THAN (2008060000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0806 VALUES LESS THAN (2008070000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0807 VALUES LESS THAN (2008080000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0808 VALUES LESS THAN (2008090000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0809 VALUES LESS THAN (2008100000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0810 VALUES LESS THAN (2008110000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0811 VALUES LESS THAN (2008120000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION P0812 VALUES LESS THAN (2009010000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM) */

 - 백업파일 넣음
]# mysql -u username -p adworksv2 < /P0803.sql
]# mysql -u username -p adworksv2 < /P0804.sql
]# mysql -u username -p adworksv2 < /P0805.sql
]# mysql -u username -p adworksv2 < /P0806.sql
]# mysql -u username -p adworksv2 < /P0807.sql
]# mysql -u username -p adworksv2 < /P0808.sql
]# mysql -u username -p adworksv2 < /P0809.sql
]# mysql -u username -p adworksv2 < /P0810.sql
]# mysql -u username -p adworksv2 < /P0811.sql
]# mysql -u username -p adworksv2 < /P0812.sql


2. ADWORKS_TS 테이블에 파티션 재정의 후에 백업파일 넣음

 - 테이블 파이션 재정의 (장점 :  랜딩상세,전환상세 보고서 다운로드시 소스를 변경하지 않아도 된다. 단점 : 운영중인 ADWORKS_TS 테이블에 LOCK 걸린다.)
ALTER TABLE ADWORKS_TS REORGANIZE PARTITION p0 INTO (
PARTITION S0802 VALUES LESS THAN (2008030000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0803 VALUES LESS THAN (2008040000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0804 VALUES LESS THAN (2008050000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0805 VALUES LESS THAN (2008060000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0806 VALUES LESS THAN (2008070000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0807 VALUES LESS THAN (2008080000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0808 VALUES LESS THAN (2008090000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0809 VALUES LESS THAN (2008100000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0810 VALUES LESS THAN (2008110000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0811 VALUES LESS THAN (2008120000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM, PARTITION S0812 VALUES LESS THAN (2009010000000000000) DATA DIRECTORY = '/var/db/adworks_ts' INDEX DIRECTORY = '/var/db/adworks_ts' ENGINE = MyISAM
);
 
 - 백업파일 넣음
]# mysql -u username -p adworksv2 < /P0803.sql
]# mysql -u username -p adworksv2 < /P0804.sql
]# mysql -u username -p adworksv2 < /P0805.sql
]# mysql -u username -p adworksv2 < /P0806.sql
]# mysql -u username -p adworksv2 < /P0807.sql
]# mysql -u username -p adworksv2 < /P0808.sql
]# mysql -u username -p adworksv2 < /P0809.sql
]# mysql -u username -p adworksv2 < /P0810.sql
]# mysql -u username -p adworksv2 < /P0811.sql
]# mysql -u username -p adworksv2 < /P0812.sql

 
* 참조 사이트 
1. http://dev-world.springnote.com/pages/6907325

2011. 9. 26. 11:20

MySQL 파티셔닝

현재 oracle을 mysql로 변환중인 업무를 진행중인데 oracle의 view 구문중 원래도 문제가 있는 구문이지만 mysql에서는 더욱 최악의 속도를 발생시켜서 고민을 하고 있던 와중에 발견한게 mysql 5.1에서도 oracle 처럼 파티셔닝을 구현할수 있다는 것이였다. 아래 내용을 잘 이해하면 앞으로 대용량 db를 처리하는데 상당한 도움이 될것이 분명하니 열심히 한번 해보자

 

 

MySQL Partitioning



 

정보는 흘러 넘치고 분석해야 할 데이터도 계속 커지면서 많은 데이터를 효율적으로 운영하기 위한 노력들은 계속 진행 중이다. 이 같은 추세에 발 맞춰 최근에는 어플라이언스 시장도 국내에서 계속 커지고 있다. 대용량 데이터를 처리하기 위해 테이블 파티셔닝은 DBMS의 필수 기능으로 꼽히는데 MySQL이 테이블 파티셔닝을 지원하지 않는 것은 그 동안 MySQL을 대용량 DB에서 쓰기 어려웠던 이유이기도 했다. Merge 테이블을 이용해 일부 비슷한 효과를 낼 수는 있었지만, 정식으로 테이블 파티셔닝을 지원한 것은 5.1 버전부터다. 지금부터 MySQL Partitioning에 대한 이야기를 시작해 보자.


 

5.1버전의 테이블 파티셔닝 지원은 타 상용 DBMS에 비해 아직은 부족한 수준임을 감안해도, 이제껏 MySQL의 파티셔닝 기능에 목말라 있던 사용자들에겐 단비와 같은 소식이다. 일반적으로 테이블을 파티셔닝한다면 다음과 같은 기대를 가지고 있을 것이다.

 

- 특정 데이터 집합의 추가/삭제가 간편하다.
- 데이터 검색 시 특정 파티션만 읽어서 속도를 향상시킬 수 있다.
- 병렬 처리가 가능하다.
- 한 테이블에 너무 많은 데이터가 입력되었을 시 발생하는 속도 저하를 방지할 수 있다.

 

그렇다면 위와 같은 기대를 어느 정도 충족해 주고 있는지 MySQL 파티셔닝 사용법과 특징에 대해 살펴보자. 먼저 자신이 사용하고 있는 MySQL에서 파티셔닝을 지원하는지 확인하는 방법은 <리스트 1>과 같다.


 

파티션 타입

MySQL에서 지원하는 파티셔닝 종류에 대해 살펴보자. 
 
기본적으로 MySQL은 RANGE, LIST, HASH, KEY 파티셔닝을 지원한다. 많이 사용되는 파티셔닝은 모두 지원한다고 보면 되겠다.

 

 

RANGE

특정 범위 단위로 파티셔닝할 때 적용되는 가장 흔히 사용하는 파티셔닝 타입 중 하나이다. Create table 구문 뒤의 PARTITION BY RANGE() 절 안에는 파티셔닝할 컬럼을, VALUE LESS THAN() 절 안에는 해당 파티션의 MAX 값을 기술하면 된다. <리스트 2>를 예로 들어보자.

 
이 테이블을 store_id 컬럼에 의해 Range 파티셔닝하고 싶다면 <리스트 3>과 같이 정의한다.

 
데이터가 들어갈 파티션은 VALUES LESS THAN 구문에 의해 결정된다. <리스트 3>의 예제에서는 store_id가 1~5까지는 p0 파티션, 6~10까지는 p1 파티션으로 들어간다. 그렇다면 store_id에 21 값이 들어오면 어떻게 될까? 이때는 해당 값이 들어갈 파티션이 지정되어 있지 않기 때문에 에러가 발생한다. 따라서 어떤 값이 들어오더라도 에러가 나지 않도록 <리스트 4>와 같이 MAXVALUE를 지정할 수 있다.

 
21보다 크거나 같은 값이 들어오면 p4 파티션에 데이터가 들어갈 것이다. MAXVALUE는 이 타입이 가질 수 있는 가장 큰 값을 의미하고 제일 마지막 파티션에 기술해야 하며 오직 하나의 파티션에만 사용할 수 있다. 그에 반해 NULL 값은 다른 값보다 작은 값으로 인식되어 제일 작은 값으로 정의된 파티션으로 들어간다. 파티션 기준이 되는 컬럼은 VALUES LESS THAN 의미처럼 크기 비교가 가능해야 한다. 컬럼이 문자인 경우에도 문자 연산을 통해 숫자만을 걸러 낼 수 있다면 Range 파티셔닝이 가능하다.


Ex) PARTITION BY RANGE ( YEAR(separated) )

 

 

Range 파티셔닝은 다음과 같은 경우 유용하다.

- 대량의 과거 데이터를 지우기를 원할 때 : delete로 많은 데이터를 지운다면 시간이 오래 걸리지만 파티션별로 drop하면 순식간에 데이터 삭제가 가능하다

 

ALTER TABLE employees DROP PARTITION p0;

- 시간이나 날짜별로 데이터를 관리하고 싶을 때
- 파티션된 컬럼을 조건으로 쿼리를 자주 실행시킬 때

 

SELECT COUNT(*) FROM employees 
WHERE YEAR(separated) = 2000 GROUP BY store_id;

 

MySQL은 재빨리 해당 파티션만 읽어서 결과를 얻어낼 수 있다.

 

LIST

List 파티셔닝은 여러 가지로 Range 파티셔닝과 비슷한 점이 많다. Range 파티셔닝처럼 각각의 파티션은 명시적으로 정의되어 있어야 한다. Range 파티셔닝과 가장 다른 특징은 파티션을 결정하는 컬럼 값이 범위가 아니라 특정한 값들의 집합이라는 것이다. 

 
Partition by list() 절에 파티셔닝할 기준 컬럼을 명시해 주고 values in() 절에 파티션을 구성할 값들을 지정하면 된다. VALUES IN() 절에 나열되는 값들은 연속될 필요가 없고 중간 값이 없어도 되지만 해당 파티션에 값이 하나도 없거나 여러 파티션에 중복된 값을 넣으면 에러가 난다. Range 파티셔닝에서의 MAXVALUE에 상응하는 것은 없고, value list에 null 값이 들어갈 수 있다. 삭제하고자 하는 데이터들이 해당 파티션에 있는 모든 데이터들이라면 역시 drop 구문을 이용해서 데이터를 삭제할 수 있다.

 

 

DELETE FROM employees WHERE store_id IN (4,12,13,14,18);

 

이와 같이 지우고자 하는 데이터가 pWest 파티션에 있는 모든 값이라면 해당 파티션을 drop함으로써 해당 값들을 모두 삭제할 수 있다.

 

ALTER TABLE employees DROP PARTITION pWest;

 

List 파티셔닝 역시 파티셔닝할 컬럼은 정수만 지원한다. 따라서 integer나 null이 아닌 값으로 파티셔닝하고 싶다면 파티션 기준 컬럼을 이러한 값으로 변환시켜야 한다. Job_code 값이 다음과 같이 들어온다고 가정해 보자

 

Management - D, M, O, P
Sales - B, L, S
Technical - A, E, G, I, T
Clerical - K, N, Y
Support - C, F, J, R, V
Unassigned “Empty”

 

이렇게 문자로 들어온다면 이것은 integer value-list로 표현할 수 없으므로, 이럴 때 ascii() 함수를 사용해야 한다.
 
list에 해당하는 값이 대소문자를 구별하지 않기를 원한다면 ucase 함수로 먼저 감싸고 ascii()를 사용하면 된다. 주의해야 할 점은 다음과 같은 insert 문은 실패한다는 것이다.

 

INSERT INTO employees VALUES
(224, ‘Linus’, ‘Torvalds’, ‘2002-05-01’, ‘2004-10-12’, ‘Q’, 21);

 

Q의 ascii 값은 81인데 list 파티션 정의에 81에 해당하는 파티션이 존재하지 않기 때문이다. List 파티셔닝은 VALUES LESS THAN(MAXVALUE)과 비슷한 catch-all에 대한 정의가 없으므로 모든 list 값은 반드시 해당하는 파티션이 미리 정의되어 있어야 한다. 또한 MySQL 버전에 따라서는 PARTITION BY LIST() 절에 함수를 사용하지 못할 수도 있다. 최근 버전인 MySQL(5.1.36)에서는 PARTITION BY LIST() 절에 함수를 이용하지 못한다. 그러므로 integer 형이 아닌 컬럼을 기준으로 List 파티셔닝을 하고 싶다면 반드시 먼저 자신이 사용하는 MySQL 버전에서 테스트 후 사용하기를 바란다.


 

HASH

파티셔닝은 필요한데 마땅히 파티셔닝할 기간이나 특정 값들의 집합을 정의하기 모호할 때 보통 많이 사용한다. 사용 목적도 그렇듯이 Range나 List로 파티셔닝된 테이블을 생성할 때와는 달리 각각의 파티션들을 직접 정의해 줄 필요가 없다. <리스트 7>과 같이 PARTITIONS 절 뒤에 사용할 파티션 개수를 적어 주면 된다.

 
PARTITION BY HASH() 절에 역시 숫자 형식의 컬럼 값이 들어오면 된다. 숫자 컬럼이 아닐 경우 함수를 사용해 숫자로 변형이 가능하다면 Hash 파티셔닝이 가능하다. 그렇다면 각각의 데이터는 어느 파티션으로 들어가는 것일까? 내부적으로 파티션은 0, 1, 2, 3과 같은 식으로 번호가 붙여지는데 표현식을 expr, 파티션 수를 num이라고 하면, 입력되는 row가 들어갈 파티션 번호는 다음의 식으로 결정된다.

 

 

IF(ISNULL(expr), 0, ABS(expr)) MOD num

 

따라서 컬럼이 auto_increment 컬럼과 같이 순차적인 값을 가지는 경우 파티션별로 데이터가 고르게 분산된다.

 

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;

 

이러한 테이블에 col3 값이 ‘2005-09-15’라면 데이터가 어느 파티션에 들어갈지는 다음과 같은 공식으로 계산된다.

 

MOD(YEAR(‘2005-09-01’),4) = MOD(2005,4) = 1

MySQL은 또한 Linear Hash 파티셔닝을 지원한다. 문법은 partition by hash가 partition by linear hash로 바뀌는 것 외에는 동일하다. 

 
내부적으로 데이터를 파티셔닝하는 Hash 알고리즘으로 linear powers-of-two를 사용하며 참고로 LINEAR HASH() 절에 사용된 표현식을 expr, 파티션 수를 num, 들어갈 파티션 번호를 N이라고 했을 때 N을 구하는 방식은 다음과 같다.

 

1. V = POWER(2, CEILING(LOG(2, num))) 
2. N = expr & (V - 1)
3. While ( N >= num: ) 
V = CEIL(V / 2) 
N = N & (V - 1)

 

KEY

Key 파티셔닝은 Hash 파티셔닝과 비슷하다. Hash 파티셔닝은 사용자가 정의하는 함수가 이용되는 반면 Key 파티셔닝은 MySQL에 의해 제공하는 Hash 함수를 사용한다. MySQL Cluster는 Hash 함수로 MD5()를 사용한다.

 
특이하게 KEY() 절에 컬럼을 명시하지 않아도 에러가 나지 않는데 명시하지 않으면 PK가 파티셔닝 키로 사용된다. 만약 해당 테이블에 PK가 없다면 UK가 파티셔닝에 사용된다.

 
하지만 UK가 파티셔닝 키로 사용되려면 반드시 UK가 not null로 정의되어 있어야 한다. <리스트 10>의 경우는 id 컬럼이 UK이긴 하지만 not null로 정의되어 있지 않아 실패한 것이다. 다른 파티션과는 다르게 유일하게 Key 파티셔닝에서는 파티셔닝 키값으로 숫자나 null 이외의 값을 사용할 수 있다. <리스트 11>과 같이 파티셔닝 키가 되는 컬럼 자료형이 char이라도 굳이 integer로 변환할 필요가 없다.

 

SUBPARTITIONING

파티션 안에 또 다른 작은 파티션을 만들 수 있다. Range나 List 파티션 안에 서브 파티션으로 Hash나 Key 파티션을 만드는 것도 가능하다. 여러 가지 파티셔닝을 함께 사용할 수 있기 때문에 Composite 파티셔닝이라고도 알려져 있다.

 

Partition Managemt

MySQL 상에서 파티셔닝한다면 주로 성능보다는 관리적인 면에서 더 유용하다. 월별로 관리하는 데이터를 Range 파티셔닝했다면 매달 말에 다음 달에 대한 파티션을 미리 만들어야 할 필요가 생긴다.

 
<리스트 13>에 정의된 파티션 테이블에 2009년 10월 데이터를 쌓을 파티션을 추가하고 싶다면 다음과 같이 추가할 수 있다.

mysql> ALTER TABLE in_ptest ADD PARTITION 
-> ( PARTITION p200910 VALUES LESS THAN (to_days(‘2009-11-01’)) ); 
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

 

또한 특정 파티션을 간편하게 제거할 수도 있다.

 

mysql> ALTER TABLE in_ptest DROP PARTITION p200902;
Query OK, 0 rows affected (0.57 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

짧은 시간 내에 Add, Drop이 되므로 온라인 중에서도 사용할 수 있다. 또한 다음과 같은 작업을 필요한 파티션만 진행할 수 있다. 해당 파티션만 작업하기 때문에 범위가 작아져 전체 작업하는 것보다 속도 향상이 있다.

 

ALTER TABLE REBUILD PARTITION ;
ALTER TABLE OPTIMIZE PARTITION ;
ALTER TABLE ANALYZE PARTITION ;
ALTER TABLE REPAIR PARTITION ;
ALTER TABLE CHECK PARTITION ;

 

하지만 타 DBMS처럼 병렬 처리 효과를 얻기는 아직까지는 힘들다. <리스트 14>와 같이 서로 다른 두 파티션을 순차적으로 check할 때보다 제 각기 다른 세션에서 병렬로 처리할 때 속도가 빠를 것이라고 기대했지만 다음에서 볼 수 있듯이 실제 결과는 그렇지 않았다. 

 
<리스트 14>에서 p200903, p200904 파티션을 순서대로 check할 때는 10초, 20초 걸리던 것이 <리스트 15>와 <리스트 16>처럼 각기 다른 세션에서 한꺼번에 실행시켰을 때는 각각55초, 54초가 걸렸다. 또한 보통 테이블 파티셔닝을 하면 특정 파티션에 어떤 작업을 수행할 때 다른 파티션에는 영향을 미치지 않을 것을 기대하지만 아직 MySQL에서는 이러한 점을 지원하지 않으니 유의해야 한다.

 
 
기존의 파티션을 병합하거나 분리하는 작업도 REO RGANIZE PARTIION 구문을 이용하면 가능하다. <리스트17>은 p200901 파티션과 p200902 파티션을 병합하는 것이다. 파티션 Add와 Drop은 순식간에 이뤄져 애플리케이션 운영 중에도 가능하지만 Reorganize를 하면 작업 대상이 아닌 파티션에 어떤 영향을 미칠까?

 
<리스트 17>의 세션에서 실행되는 쿼리는 p200901,p200902 파티션만 관여한다. 따라서 해당 파티션만 lock이 걸리고 나머지 파티션은 사용할 수 있을 것으로 기대하겠지만 실제 테스트 결과 <리스트 19>의 결과를 보면 알 수 있듯이 해당 파티션뿐만 아니라 전체 파티션에 lock이 걸리는 걸 알 수 있었다. <리스트 19> 쿼리의 데이터는 p200909 파티션으로 들어감에도 불구하고 <리스트 17>의 쿼리가 끝날 때까지 대기한 후에 실행이 되었다. 따라서 파티셔닝 관리 작업 중 Add와 Drop 같이 순식간에 이뤄지는 종류를 제외하고는 항상 애플리케이션에 영향을 줄 수 있다는 점을 명시하고 작업해야 한다.

 
 
현재 테이블 파티셔닝 현황에 대해 조회해 보고 싶다면 Information 스키마의 Partitions 테이블을 참조하면 된다.

파티션 성능

MySQL은 Parallel Processing을 아직 지원하지 않는다. 그렇더라도 각각 파티션에 해당하는 작업을 여러 개의 프로세스로 돌리면 비슷한 효과를 얻을 수 있지 않을까 기대했었지만 앞에서 살펴본 바와 같이 그러한 결과는 얻을 수 없었다. delete 대신에 drop 명령어를 통한 성능 향상, 그리고 Partition Pruning을 통한 성능 향상 정도를 기대할 수 있겠다. 

 
Explain Partitions 구문을 사용하면 실행계획에 파티션 정보들을 보여준다. <리스트 20>의 세션에서는 파티셔닝 컬럼인 term이 where 조건에서 변형되었기 때문에 Partition Prunining이 일어나지 않았다. 따라서 <리스트 21>과 같이 파티션 키 컬럼은 그대로 두고 비교하는 상수 값만 변경해야 원하는 속도를 얻을 수 있다.



파티션 제약사항

글 중간 중간에 MySQL 파티셔닝을 사용할 때 제약사항들을 언급했었는데 현재 MySQL 5.1.36 버전에서의 제약사항들을 간단히 정리해 보면 다음과 같다. 아직까지는 생각보다 많다.

 

- 모든 파티션은 동일한 스토리지 엔진 사용 
모든 파티션은 동일한 스토리지 엔진을 사용해야 한다. 파티션별로 스토리지 엔진을 명시해도 에러가 나지 않는 것을 봐서는 향후에 파티션별로 다른 스토리지 엔진을 사용할 수 있게 되지 않을까라는 기대도 해본다. 명심할 것은 파티션별로 스토리지 엔진을 명시해도 에러만 나지 않을 뿐 실제로 다른 스토리지 엔진을 사용할 수 있는 게 아니란 사실이다.

- Merge, CSV, Blackhole은 파티셔닝이 불가능하다.
- 파티셔닝을 테이블이나 인덱스만 할 수는 없다. 테이블과 인덱스를 항상 같이 파티셔닝해야 한다.
- 파티션된 테이블은 foreign key를 지원하지 않는다.
- 파티션된 테이블은 fulltext index를 지원하지 않는다.
- 파티션된 테이블은 geometry 컬럼을 지원하지 않는다.
- Key 파티셔닝을 제외하고는 파티셔닝에 키가 되는 컬럼은 반드시 integer 값이거나 integer로 변환 가능해야 한다. null은 포함될 수 있다.
- 많은 테이블에 파티션을 사용하다 보면 스토리지 엔진으로부터 ‘Got error 24’와 같은 에러 메시지를 보게 될 것이다. 이때는 open_files_ limit 값을 늘려줘야 한다.
- 한 테이블당 서브파티션(subpartition)을 포함해 파티션의 최대 개수는 1,024개이다.


 

효과적인 사용은 사용자의 몫

데이터가 점점 더 대용량화되는 가운데 MySQL도 늦게나마 이런 특징들을 지원하기 시작했다. 아직까지 부족한 점도 많지만 MySQL은 끊임없이 발전하고 있고 이를 잘 활용하기 위한 기법들이 많이 소개되고 있으므로 단순히 이를 Oracle과 같은 상용 DBMS와 비교하는 것은 별 의미가 없다. MySQL은 나름의 영역에서 다양한 기능과 좋은 성능을 내 주고 있으며 어떤 서비스에 어떻게 사용할지는 사용자들의 몫이다. MySQL과 같은 오픈소스 DBMS가 한층 더 좋은 모습으로 나타날수록 사용자 입장에서는 반갑고 고맙기 그지없을 뿐이다.

 

 

참고자료
1. http://dev.mysql.com/doc/refman/5.1/en/partitioning.html
2. http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning_with_dates.html
3. http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning.html
4. http://dev.mysql.com/tech-resources/articles/testing-partitions-large-db.html

 

필자소개

오민석 msoh@hanmail.net|(주)다음커뮤니케이션 DB기술팀 MySQL Cell. 게임 개발, 웹 개발, SE 등 넓고 얕게 여기저기를 기웃거리다가 DBA로 정착하고 나서야 자신에게 맞는 일을 찾았다. 최근까지 오라클 RAC 프로젝트를 이끌다가 MySQL 나름의 매력을 느끼고 현재 MySQL 관련 프로젝트를 진행하고 있다. 

 

출처 : 한국 마이크로 소프트웨어 [2009년 11월호]
제공 : DB포탈사이트 DBguide.net 

2011. 9. 26. 11:18

MySQL BIN 로그 삭제

 MySQL에서 사용하는 log-bin이라는 옵션은 사용된 쿼리들이 로깅되는 파일이며 Innodb 혹은 Replication등에서 사용되곤 합니다.

하지만 이 파일의 문제는 무한정 늘어난다는 것입니다. relay-log-space-limit 같은 옵션이 있지만 이 옵션은 Replication에서 사용되는 relay-log-bin의 용량을 제한 하는 옵션이지 해결책이 되지 못합니다.

하지만 이 파일을 정리하는 방법이 있습니다. 우선 다음의 Query를 사용하는 것입니다.

          PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
          


INTERVAL에서는 원하시는 로깅 시점을 기록하시면 됩니다. 위의 경우에는 30일 이전의 로깅 정보를 삭제하게 됩니다.

차례차례 해보도록 하겠습니다.

1. 현재의 하드디스크 용량 상태를 확인합니다.

          [root@Theeye db]# df -h 
          Filesystem            Size  Used Avail Use% Mounted on 
          /dev/sda1              18G  1.9G   15G  12% / 
          /dev/sda3              15G  803M   13G   6% /home 
          /dev/sda2              97G   78G   15G  85% /usr/local/mysql/db
          


데이터베이스 공간이 85%나 사용되어 곧 꽉찰지도 모른다는 불안감에 휩싸이게 되었습니다.

2. log-bin 파일을 확인해 봅시다.

          [root@Theeye db]# ls mysql-bin.* 
          mysql-bin.000001  mysql-bin.000002  mysql-bin.000003 ......  mysql-bin.000073
          


파일이 엄청나게 많이 있습니다.

3. 다음 명령어를 수행합니다.

          mysql -e "PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)"
          


물론 mysql에 접속하셔서 위의 명령어를 입력하셔도 됩니다. Super권한을 가진 root계정으로 실행해 주세요.

4. log-bin 파일이 줄었나 확인해 봅시다.

          [root@Theeye db]# ls mysql-bin.* 
          mysql-bin.000041  mysql-bin.000042  mysql-bin.000043 ......  mysql-bin.000073
          


41번 이전의 파일들이 모두 삭제 되었습니다. 41번 부터가 최근 30일 이내의 로그정보가 남아있는 모양이군요.

5. 하드디스크 용량 상태를 확인해 봅시다.

          [root@Theeye db]# df -h 
          Filesystem            Size  Used Avail Use% Mounted on 
          /dev/sda1              18G  1.9G   15G  12% / 
          /dev/sda3              15G  803M   13G   6% /home 
          /dev/sda2              97G   40G   53G  43% /usr/local/mysql/db
          


확실히 용량이 많이 확보가 되었군요^^

주기적인 실행이 필요하실 경우에는 위의 명령어를 crontab에 등록하시면 됩니다. 일주일에 한번정도의 실행이 적당하겠네요.


출처 :  http://dev-world.springnote.com/pages/6494145 

2011. 8. 17. 14:11

MySQL InnoDB Table Space 관리하기

1. What is Innodb Table space  ?

    - 그 동안  Myisam 방식으로 테이블 스페이스를 사용하던 mysql은 rollback 이 되지 않는다.

       다른 데이터베이스 와 달리  autocommit 이 자동으로 실행 하여 

       데이터 베이스의 트렌젝션시 발생되는 lock을 처리 하기 힘들었다.

   -  innodb는 오라클 처럼 shared tablespace ( ibdataN) 와 로그파일 (ib_logfileN ) 로 구성된다.

      로그파일은 트랜젝션들을 처리하는 오라클의 아카이브 로그 같이 생각 하면 된다.

 

2. Mysql의 주요 특징 

   참고 : Mysql Technical Reference fo Ver 5.0 : 16장  InnodB Configureation

  -  rollback 을 구현 할수 있다.

  -  transaction isolation Level

     : READ-UNCOMMITED, READ-COMMITED, REPEATABLE-READ, SERIALIZABLE

  - innodb_buffer_pool_size

    : MyIsam 방식과 달리 buffer poll을 이용하여 인덱스와 raw data를 캐싱한다.

      따라서 사용량이 많아 지면 tablespace  Disk I/O  가 그만큼 줄어 든다

      pool의 용량은 real memory의 80% 까지 줄수 있으나,  다른 어플리 케이션과 사용시

     너무 많이 주게 되면 운영 체제의 paging을 일으키게 된다.

     ( 참고로 오라클은 SGA 를 50%로 권고 한다)

     또 32bit 시스템의 경우 2 ~ 3.5G의 제한되므로 너무 많이 주면 안된다고 한다.

 - innodb_data_file_path

    아직 오라클 같이 Shared Table Space 를 온라인상에서 추가 하기 힘들다.  

    오직 my.cnf 환경 파일로 생성이 되고 변경 된다.

      형식

     innodb_data_file_path  = [파일위치1/]파일명1:파일1사이즈:autoextend:max:MAX파일 사이즈

     파일 간의 구분자는 ";" 이고 파일의 속성 구분자는 ":" 이며

     autoextend:max:2000M 는 맨마지막 파일에만 해당된다.

innodb_file_per_table

   를 사용하면 이전 isam 같이 데이터 베이스 폴더 안에 table_name.ibd 라는 파일이 생긴다.

   사용예는 다음과 같다.

    innodb_data_home_dir=/export/DATABASE/MyData

   innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:200M:autoextend:max:2000M

 

3. Innodb 관리

  3.1 테이블 rename

      RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;

  3.2 데이블 복구

       이전 백업 된 데이터 파일을 이용하여 복구 하는방법

     가) ALTER TABLE tbl_name DISCARD TABLESPACE; 

          -> 현재 tbl_name.ibd 를 삭제한다.

     나) 백업된 tbl_name.ibd 를 복사해온다.

     다) ALTER TABLE tbl_name IMPORT TABLESPACE;

  3.3 SHOW INNODB STATUS

  3.4 Adding and Removing InnoDB Data and Log Files

     >my.cnf<

        innodb_data_home_dir=/export/DATABASE/MyData

        innodb_data_file_path = ibdata1:500M:ibdata2:500M:ibdata3:500M:ibdata4:200M:autoextend:max:2000M

  3.5  Shared Table Space 용량 조정

       가) mysqldump로 innodb table을 백업 받는다.

       나) 서버를 중지

       다) 현재  사용중인  ibdata1  .. ibdataN 을 삭제 한다.

       라) 위의 환경 파일을 수정한다.

       마) 서버를 재구동 한다.

       바) dump 파일을 import 한다.

   3.6  로그파일 관리

    보통 다음 과 같은 디폴트 값을 사용하며

      # Set buffer pool size to 50-80% of your computer's memory
      set-variable = innodb_buffer_pool_size=70M
      set-variable = innodb_additional_mem_pool_size=10M

      # Set the log file size to about 25% of the buffer pool size
      set-variable = innodb_log_file_size=20M
      set-variable = innodb_log_buffer_size=8M

  3.2 테이블 스페이스 관리

    아직 오라클 같이 Shared Table Space 를 온라인상에서 추가 하기 힘들다.  

    오직 my.cnf 환경 파일로 생성이 되고 변경 된다.

    대량의 데이터 베이스 의 경우 3 ~ 4G 이상이 되는경우

    디폴트로 ibdata1:10M:autoextend 를 쓴다면 곤욕을 치르게 될것이다.

 

    다음과 같이 여러개의 파일을 이용하여 분산 하는 것을 추천 합니다.

    파일은 path을 이용 할수 도 있고 raw 디바이스를 이용 할수도 있습니다.

    /data1 .. /data4 를 각각 다른 디바이스라고 한다면 아래와 같이 사용하면 된다.

     innodb_data_home_dir=

    innodb_data_file_path = /data1/ibdata1:500M;/data2/ibdata2:500M;/data3/ibdata3:500M;/data4/ibdata4:200M:autoextend:max:2000M

  

    보통 백업을 위해서 오라클의 경우 500MB ~ 1GB로 데이터 파일 을 생성 하였다.

    그리고 200MB는 현재 사용량이 얼마인지 모르기 때문에 200MB -> 300MB가 되면

    다시 환경 파일을 수정하고 데이터 파일을 추가 하기 위한 방법이다.

      그러나 이것도 문제 가 많다는 것을 독자들도 알것이다.

    암튼 아직은 미약한 부분이 많다는 것을 착안 하기 바란다.

    데이처 파일을  500MB ~ 1GB로 생성 하는이유는

    예전에 OS에서 제한이 있어서 그런 적도 있었지만 , 파일이 크면 그 만큼  I/O분산이 안된다.

    그리고 파일당 백업 시간이 증가하거나 문제가 발생 하면  복구도 그만큼  힘들다.

   예로 500MB데이터 파일 하나가 사라진 경우와 1GB 데이터 파일 1개가 사라진 경우

    그안에 속한 데이터의 량은 곱절이 된다.

   즉 재난 을 최소화 하기 위해서라고 1GB이하로 쪼개는 것이 낳기 때문이다.

 

  그렇다고 3GB 되는 것을 100MB로 쪼개면  어떨까 performance에서  문제가 될것이다.

   그러므로 DBA의 적절한 선택이 필요하다.


출처 :  
MySQL InnoDB Table Space 관리하기 (hi.pe.kr 날으는물고기·´″°³о♡)