IT Japan

n Replication n Master 설정 1. Replication Master Configuration을 설정한다. - enable binary logging - 서버에 a unique server-id (positive integer between 1 and (2**32)–1)부여 - my.cnf 수정 [mysqld1] log-bin=mysql-bin server-id=1 2. slave와 communication할 유저를 생성합니다. - REPLICATION SLAVE 권한을 가져야 한다. mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'repl'; mysql> GRANT REPLICATION SLAVE ON *.* -> TO 'repl'..

n Backup 종류 - Cold/Hot /Warm 백업: online/offline여부 - Logical /Physical : text/binary - Full / Incremental : 모든 rows가 백업됨/ 변경된 내용만 백업됨 mysqlbackup : raw(physical) backup - innodb : ibdata*, *.ibd,ib_logfile* - mysql디렉토리 : *.MYD,MYI,*.FRM들 n mysqlbackup :MySQL Enterprise Backup - backup-and-apply-log : 백업 - backup-to-image : 한 개의 image파일로 백업 - copy-back : restore - image-to-backup-dir : image파일에서 백..

n View Algorithm ① UNDEFINED : default algorithm MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient and because a view cannot be updatable if a temporary table is used. ② MERGE : handled by merging corresponding parts of a view definition into the statement that refers to the view If the MERGE algorithm cannot be used, a tem..

***** 자동 통계 수집 ****** - innodb_stats_on_metadata =1 - 수집 시점 . When the table is first opened . When the table size changes by 1/16th overall since the last time statistics were taken . If more than 2,000,000,000 rows are inserted (if this is before to the 1/16th rule) . When anybody accesses any of SHOW TABLE STATUS, SHOW INDEX, INFORMATION_SCHEMA.TABLES, INFORMATION_SCHEMA.STATISTICS for InnoDB..

n Table Maintenance n CHECK TABLE - for MyISAM, InnoDB, and ARCHIVE ,CSV(MySQL 5.1.9이상) - partitioned table지원(MySQL 5.1.27이상) CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED} 1) FOR UPGRADE : compatible 점검(with the current version of MySQL) 2) QUICK : 데이터 행의 incorrent link를 체크하지 않는다. 3) FAST : 정상적으로 close안된 테이블에 대해서만 체크한다. 4) ..

n MySQL 을 ssl connection 가능하게 설정해 봅니다. [root@EDYDR51P0 ~]# cd /etc/ [root@EDYDR51P0 etc]# mkdir newcerts [root@EDYDR51P0 etc]# cd newcerts [root@EDYDR51P0 newcerts]# which openssl /usr/bin/openssl 1. RSA private key 를 생성합니다.( 2048 bit long modulus) [root@EDYDR51P0 newcerts]# openssl genrsa 2048 > ca-key.pem Generating RSA private key, 2048 bit long modulus ...................+++ ......+++ e is 6..

17.1. Overview of Partitioning in MySQL This section provides a conceptual overview of partitioning in MySQL 5.5. Data and indexes for each partition can be assigned to a specific directory using the DATA DIRECTORY and INDEX DIRECTORY options for the PARTITION clause of the CREATE TABLE statement used to create the partitioned table. The DATA DIRECTORY and INDEX DIRECTORY options have no effect ..

n Storage Engines n MyISAM - web에서 주로 사용 - dw나 fulltext search 나 spatial data type 지원 - row format : fixed, dynamic , compressed - compressed : myisampack사용 CREATE TABLE t (i INT) ENGINE = MYISAM; InnoDB CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB; n MERGE - known as the MRG_MyISAM engine - MyISAM 테이블을 합친 것 - .frm file stores the table format, and an .MRG 1) t1 테이블을 생성한다..