멋지게 놀아라

바이너리 배포 버전일 경우

/var/lib/mysql/data

RPM 설치 버전

/var/lib/mysql

파라미터로 확인

mysql> show global variables like '%datadir%' ;

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

| Variable_name | Value           |

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

| datadir       | /var/lib/mysql/ |

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

또는 /etc/my.cnf 파일에서 datadir 파라미터 값을 확인 (서버가 기동 중이지 않을 때)

[root@mmysql ~]# cat /etc/my.cnf | grep datadir

datadir=/var/lib/mysql

mysql> show global variables like 'datadir' ;

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

| Variable_name | Value           |

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

| datadir       | /var/lib/mysql/ |

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

1 row in set (0.00 sec)

mysql> show databases ;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| tpcc               |

| tpch               |

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

6 rows in set (0.00 sec)

mysql> exit

Bye

[root@mmysql mysql]# cd /var/lib/mysql/

[root@mmysql mysql]# ls -d */

mysql/  performance_schema/  sys/  tpcc/  tpch/   <-- 각각의 폴더가 데이터 베이스를 나타냄

디렉터리 안에 db.opt 파일은 다음과 같이 기본 캐릭터 셋과 콜레이션 정보를 가진다.

[root@mmysql tpcc]# cat db.opt

default-character-set=latin1

default-collation=latin1_swedish_ci

테이블을 생성하게 되면 확장자가 .frm인 파일이 생성되며, 스토리지 엔진에 따라서 데이터가 들어가 있는 파일이 생성된다.

innoDB엔진일 경우 다음과 같이 확장자가 ibd인 파일이 생성되어 데이터가 저장된다.

innoDB엔진이 동일 테이블 스페이스를 사용하게 설정이 되어 있고, 16K의 페이지를 사용할 경우 최대 테이블 크기는 40억페이지를 가지는 테이블을 생성할 수 있다. 그러나 개별 테이블 스페이스를 사용하게 설정이 되어 있다면 개별 테이블의 정보는 ibd파일에 저장되며 최대 크기는 운영체제 파일크기의 제약에 따른다. (innodb_file_per_table=1)

[root@mmysql tpcc]# ls -al

total 1380116

drwxr-x--- 2 mysql mysql      4096 Jun 24 14:47 .

drwxr-x--x 7 mysql mysql      4096 Dec  4 20:41 ..

-rw-r----- 1 mysql mysql      9388 Jun 23 13:31 customer.frm

-rw-r----- 1 mysql mysql  58720256 Jul 21 14:25 customer.ibd

-rw-r----- 1 mysql mysql        65 Jun 23 13:31 db.opt

MyISAM엔진을 사용할 경우 *.frm 파일은 테이블 구조 명세를 저장한 파일이며, *.myd 는 데이터가 저장된 파일, *.myi는 인덱스에 대한 정보를 저장한 파일이 된다.

.myd와 .myi 파일은 최대 256TB의 크기 제한을 가진다. 테이블 생성 시 avg_row_length와 max_rows 옵션을 사용하면 최대 65,536TB 의 크기까지 가능하다. 운영중에 135,136 에러가 발생하게 된다면 alter table문을 이용해서 이 값을 증가 시켜주면 된다.

Memory엔진을 사용할 경우 *.frm 파일만 데이터 베이스 디렉터리 안에 존재 하게 되며, 모든 데이터는 메모리에 존재 한다.

그래서 서버가 종료 될 경우 데이터 및 인덱스 정보는 모두 사라진다. 서버가 재 기동되면 테이블은 존재 하지만 데이터는 없는 상태가 된다.

트리거 파일은 확장자가 trg가 되며 트리거 명.trg가 아니라, 트리거가 생성되어 있는 테이블명.trg가 되고, 해당 테이블에 트리거가 많을 경우 하나의 trg파일에 모두 저장되고, 트리거명.trn 파일이 생성된다.

Show tables from dbName ; 문을 수행하면 해당 데이터베이스 디렉터리 안의 *.frm 파일명을 보여주는 것과 같다.

유닉스 계열에서는 파일 시스템에서 대/소 문자를 구분하기 때문에 abc , ABC 는 abc.frm ABC.frm 파일로 생성된다.

그래서 두 개는 서로 다른 테이블로 인식이 된다. 이런 환경에서 대소문자를 구분하지 않는 시스템으로의 이전을 고려할 경우 신중해야 한다(테이블이 하나 생성 안될 수도 있으므로)

그래서 lower_case_table_name 값을 1을 주게 되면 소문자 테이블 명을 사용할 수 있게 되며 내부적으로는 다음과 같다.

1. 테이블 정보를 가진 .frm파일을 생성할 때, 테이블 명을 소문자로 변경 후 .frm 파일을 생성한다.

2. FROM 절에 대문자로 기술된 테이블이 있을 경우 내부적으로 소문자로 변경 후 쿼리를 수행한다.

기존에 대문자의 데이터베이스 명이나 테이블 명을 가진 시스템에서 소문자로 변경 하고자 한다면, 다음과 같은 방법을 써야 한다.

데이터베이스 명과 테이블 명 둘 다 대문자가 있을 경우

mysqldump --database dbname > dbname.sql

drop database dbname ;

디비 중지 후 /etc/my.cnf 파일에 lower_case_table_name값을 1로 설정 후 기동

mysql < dbname.sql

테이블명만 대문자 일 경우

rename table 테이블명

전체 데이터 디렉터리

시작옵션 변경 : 시작 시 --datadir=/data1 또는 /etc/my.cnf  파일에  datadir=/data1 추가

심볼릭 링크 사용 : 기존의 디렉터리 내용을 다른 곳으로 이동 후 심볼릭 링크 생성

개별 데이터 베이스 디렉터리

심볼릭 링크 사용

1. mysql shutdown ( mysqladmin -p -u root shutdown )

2. cd /usr/local/mysql/data

3. tar cf - bigdb | ( cd /var/db; tar xf - )

4. rm -rf bigdb

5. ln -s /var/db/bigdb bigdb

6. mysql startup     mysqld_safe &

개별 테이블

심볼릭 링크 사용

유닉스 환경의  MyISAM테이블만 지원 함.

mysql> show global variables like 'have_symlink' ;

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

| Variable_name | Value    |

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

| have_symlink  | DISABLED |

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

위의 환경변수 값이 enabled(YES)가 되면 사용가능.

.MYD .MYI파일을 이동 후, 원본 디렉터리에 동일 이름의 심볼릭 링크 생성.

.frm 파일은 이동 없이 원본 디렉터리에 존재 해야 함.

innodb 테이블 스페이스 파일

시작 옵션 변경

mysql> show global variables like 'innodb_data%' ;

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

| Variable_name         | Value                  |

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

| innodb_data_file_path | ibdata1:12M:autoextend |

| innodb_data_home_dir  |                        |

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

서버 PID 파일

시작 옵션 변경 또는 시작옵션 변경

mysql> show global variables like 'pid_file' ;

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

| Variable_name | Value                      |

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

| pid_file      | /var/run/mysqld/mysqld.pid |

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

--pid_file=/tmp/mysqld.pid

또는

[mysqld]

pid_file=/tmp/mysqld.pid

로그 파일

시작 옵션 변경

[mysqld]

log_error=

general_log=1

general_log_file=

log-bin=       

mysql> show global variables like '%log%' ; 필요한 정보만 남김...

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

| Variable_name                           | Value                          |

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

| general_log                             | OFF                            |

| general_log_file                        | /var/lib/mysql/mmysql.log      |

| log_bin                                 | ON                             |

| log_bin_basename                        | /var/lib/mysql/mysql-bin       |

| log_bin_index                           | /var/lib/mysql/mysql-bin.index |

| relay_log                               |                                |

| relay_log_basename                      |                                |

| relay_log_index                         |                                |

| slow_query_log                          | ON                             |

| slow_query_log_file                     | /var/lib/mysql/mmysql-slow.log |

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

mysql> select host , user , plugin , password_last_changed , password_expired , account_locked

    -> from user ;

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

| host      | user      | plugin                | password_last_changed | password_expired | account_locked |

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

| localhost | root      | mysql_native_password | 2016-06-21 10:56:41   | N                | N              |

| localhost | mysql.sys | mysql_native_password | 2016-06-20 13:13:18   | N                | Y              |

| %         | root      | mysql_native_password | 2016-06-21 13:16:38   | N                | N              |

| %         | repl      | mysql_native_password | 2016-06-21 16:14:35   | N                | N              |

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

mysql.user 테이블에 password 칼럼이 있는 경우

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD ('root1122') ;

mysql> flush privileges ;

또는

mysql> update mysql.user set password=password('root1122') where user = 'root' ;

mysql> flush privileges ;

mysql.user 테이블에 password 칼럼이 없는 경우

mysql> update mysql.user set authentication_string=password('root1122') where user ='root' ;

mysql> flush privileges ;

mysql> flush privileges ;를 해 주는 이유는 변경내용을 메모리상에 반영해 주기 위함 임.

1. 서버 종료

mysqladmin -p -u root shutdown

2. 데이터 디렉터리 권한 변경

chown -R mysql /var/lib/mysql   

chgrp -R mysql /var/lib/mysql

3. mysql 이외의 유저가 접근하는 것을 막기 위하여 다음과 같이 group과 other권한을 제한한다,

chmod -R go-rwx /var/lib/mysql

root가 아닌 일반 유저로 기동 시킬 경우는 --user=mysql 옵션을 추가 시켜서 기동 시키거나, /etc/my.cnf 파일에 user=mysql을 추가 하면 된다.

mysqladmin -u root -p --protocol=tcp shutdown 

또는

mysqladmin -u root -p -h 127.0.0.1 shutdown

/etc/my.cnf 파일에 다음과 같이 설정

[mysqld]

socket=/usr/local/mysql/mysql.sock

[client]

socket=/usr/local/mysql/mysql.sock

1. 테이블과 로그들을 정상 반영 후 down 시키기 위하여 term 옵션을 주고 수행 함.

   그래도 반응이 없을 경우는 -9 옵션으로 강제 종료를 시킨다.

   mysqld_safe로 기동 시켰을 경우 -9 옵션으로 서버를 종료 시키면 즉시 서버를 재 기동 시키게 되어 있다.

   그래서 종료 전 mysqld_safe 프로세스를 먼저 죽이는 과정이 필요 함.

kill -TERM pid

2. 권한 테이블을 통한 검증을 비활성 화 시키기 위하여 --skip-grant-tables 옵션을 이용하여 기동

mysql --skip-grant-tables

3. flush privileges 명령어를 통하여 권한 정보를 읽어 옴.

   이때 접속 중인 세션은 그대로 유지가 되기 때문에 상관없고, 이후 접속 되는 연결은 권한을 체크 한다.

   (flush privileges 명령을 수행하지 않으면 패스워드 변경이 불가능 하다.)

mysql> flush privileges ;

4. root의 패스워드를 변경한다.

5. 정상적으로 서버를 재 기동 시킨다.

#세션 레벨에서 시스템 변수 값 변경

mysql> set sql_mode = 'STRICT_TRANS_TABLES' ; 

Query OK, 0 rows affected (0.00 sec)

글로벌 변수를 변경하려면

mysql> set global sql_mode = 'STRICT_TRANS_TABLES' ;

#show 명령을 이용하여 시스템 변수 값 확인

mysql> show global variables like 'sql_mode' ;

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

| Variable_name | Value                                  |

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

| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES |

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

1 row in set (0.00 sec)

mysql> show session variables like 'sql_mode' ;

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

| Variable_name | Value               |

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

| sql_mode      | STRICT_TRANS_TABLES |

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

1 row in set (0.00 sec)

mysql> show variables like 'sql_mode' ;

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

| Variable_name | Value               |

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

| sql_mode      | STRICT_TRANS_TABLES |

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

1 row in set (0.00 sec)

#@@를 이용하여 변수 값 확인

mysql> select @@global.sql_mode ;

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

| @@global.sql_mode                      |

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

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES |

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

1 row in set (0.00 sec)

mysql> select @@local.sql_mode ;

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

| @@local.sql_mode    |

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

| STRICT_TRANS_TABLES |

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

1 row in set (0.00 sec)

mysql> select @@session.sql_mode ;

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

| @@session.sql_mode  |

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

| STRICT_TRANS_TABLES |

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

1 row in set (0.00 sec)

mysql> select @@sql_mode ;

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

| @@sql_mode          |

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

| STRICT_TRANS_TABLES |

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

1 row in set (0.00 sec)

# global 변수 선언

set global var_name = value ;

set @@global.var_name = value ;

# Session 변수 선언

set session var_name = value ; 

set var_name = value;

set @@session.var_name = value ; 

set @@var_name = value;

# 다른 변수 값 참조

set session read_buffer_size = 2 * @@global.read_buffer_size ;

# 한 번에 여러변수 설정 하기

set session sql_warnings =0 , global default_storage_engine = InnoDB ;

set global v1 = val1 , v2 = val2 , session v3 = val3 , v4 = val4 ;

v1, v2는 global, v3, v4는 session 변수

mysql> set @uptime = (select variable_value from information_schema.global_status where variable_name = 'uptime') ;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @uptime ;

+---------+

| @uptime |

+---------+

| 2794    |

+---------+

#plugin-load 설정

[mysqld]

plugin-load=my_engine.so;info_tables.so

또는 플러그 인을 개별로 나열

plugin-load=my_engine=my_engine.so;locks=info_tables.so;users=info_tables.so

info_tables.so 중에 locks만 로딩 하고 싶으면

plugin-load=locks=info_tables.so

#plugin-load는 마지막 하나만 적용된다.

[mysqld]

plugin-load=my_engine.so

다른 설정들….

plugin-load=info_tables.so

위와 같이 하면 info_tables.so만 적용 됨.

#plugin-load-add

[mysqld]

plugin-load-add=my_engine.so

다른 설정들….

plugin-load-add=info_tables.so

#운영 중에 플러그인 로드 (내부적으로 mysql.plugin 테이블에 플러그인을 등록 함)

INSTALL PLUGIN my_engine SONAME 'my_engine.so' ;

INSTALL PLUGIN locks SONAME 'info_tables.so' ;

INSTALL PLUGIN users SONAME 'info_tables.so' ;

#플러그인 제거 (다음 재 시작 시 자동으로 로드 시키지 않음)

UNINSTALL PLUGIN my_engine ;

UNINSTALL PLUGIN locks ;

#플러그인 파일 위치 확인

mysql> show global variables like 'plugin_dir' ;

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

| Variable_name | Value                        |

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

| plugin_dir    | /usr/local/mysql/lib/plugin/ |

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

1 row in set (0.00 sec)

[root@mysqlent5 ~]# ls /usr/local/mysql/lib/plugin/

adt_null.so            innodb_engine.so      libtest_services.so           libtest_sql_cmds_1.so       libtest_sql_shutdown.so                     mypluglib.so        semisync_slave.so

audit_log.so           keyring_file.so       libtest_services_threaded.so  libtest_sql_commit.so       libtest_sql_sqlmode.so                      mysql_no_login.so   test_security_context.so

authentication_pam.so  keyring_okv.so        libtest_session_detach.so     libtest_sql_complex.so      libtest_sql_stored_procedures_functions.so  mysqlx.so           test_udf_services.so

auth_socket.so         keyring_udf.so        libtest_session_info.so       libtest_sql_errors.so       libtest_sql_views_triggers.so               openssl_udf.so      thread_pool.so

debug                  libmemcached.so       libtest_session_in_thd.so     libtest_sql_lock.so         libtest_x_sessions_deinit.so                rewrite_example.so  validate_password.so

firewall.so            libpluginmecab.so     libtest_sql_2_sessions.so     libtest_sql_processlist.so  libtest_x_sessions_init.so                  rewriter.so         version_token.so

ha_example.so          libtest_framework.so  libtest_sql_all_col_types.so  libtest_sql_replication.so  locking_service.so                          semisync_master.so

[root@mysqlent5 ~]# ls /usr/local/mysql/lib/plugin/ | wc -l

48

#등록된 플러그 인 확인

mysql> show plugins ;

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

| Name                       | Status   | Type               | Library | License     |

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

| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |

| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | PROPRIETARY |

| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | PROPRIETARY |

| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |

| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |

| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |

| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |

| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |

| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |

| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |

| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |

| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |

| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | PROPRIETARY |

| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |

| ngram                      | ACTIVE   | FTPARSER           | NULL    | PROPRIETARY |

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

44 rows in set (0.00 sec)

mysql> select plugin_name , plugin_status , plugin_version , plugin_type from information_schema.plugins ;

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

| plugin_name                | plugin_status | plugin_version | plugin_type        |

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

| binlog                     | ACTIVE        | 1.0            | STORAGE ENGINE     |

| mysql_native_password      | ACTIVE        | 1.1            | AUTHENTICATION     |

| sha256_password            | ACTIVE        | 1.1            | AUTHENTICATION     |

| InnoDB                     | ACTIVE        | 5.7            | STORAGE ENGINE     |

| INNODB_TRX                 | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_LOCKS               | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_LOCK_WAITS          | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_CMP                 | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_CMP_RESET           | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_CMPMEM              | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_CMPMEM_RESET        | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_CMP_PER_INDEX       | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_CMP_PER_INDEX_RESET | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_BUFFER_PAGE         | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_BUFFER_PAGE_LRU     | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_BUFFER_POOL_STATS   | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_TEMP_TABLE_INFO     | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_METRICS             | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_FT_DEFAULT_STOPWORD | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_FT_DELETED          | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_FT_BEING_DELETED    | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_FT_CONFIG           | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_FT_INDEX_CACHE      | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_FT_INDEX_TABLE      | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_SYS_TABLES          | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_SYS_TABLESTATS      | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_SYS_INDEXES         | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_SYS_COLUMNS         | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_SYS_FIELDS          | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_SYS_FOREIGN         | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_SYS_FOREIGN_COLS    | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_SYS_TABLESPACES     | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_SYS_DATAFILES       | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| INNODB_SYS_VIRTUAL         | ACTIVE        | 5.7            | INFORMATION SCHEMA |

| PERFORMANCE_SCHEMA         | ACTIVE        | 0.1            | STORAGE ENGINE     |

| MRG_MYISAM                 | ACTIVE        | 1.0            | STORAGE ENGINE     |

| MyISAM                     | ACTIVE        | 1.0            | STORAGE ENGINE     |

| MEMORY                     | ACTIVE        | 1.0            | STORAGE ENGINE     |

| CSV                        | ACTIVE        | 1.0            | STORAGE ENGINE     |

| BLACKHOLE                  | ACTIVE        | 1.0            | STORAGE ENGINE     |

| partition                  | ACTIVE        | 1.0            | STORAGE ENGINE     |

| FEDERATED                  | DISABLED      | 1.0            | STORAGE ENGINE     |

| ARCHIVE                    | ACTIVE        | 3.0            | STORAGE ENGINE     |

| ngram                      | ACTIVE        | 0.1            | FTPARSER           |

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

44 rows in set (0.00 sec)

#기본 스토리지 엔진 확인

mysql> show global variables like 'default_storage_engine' ;

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

| Variable_name          | Value  |

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

| default_storage_engine | InnoDB |

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

mysql> show global variables like 'default_tmp_storage_engine' ;

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

| Variable_name              | Value  |

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

| default_tmp_storage_engine | InnoDB |

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

#자신의 서버에서 지원하는 스토리지 엔진 확인

mysql> show engines ;

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

| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |

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

| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |

| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |

| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |

| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |

| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |

| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |

| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |

| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |

| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |

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

9 rows in set (0.00 sec)

support : 해당 엔진 지원 여부

xa : 분산 트랜잭션을 지원하는지 여부

savepoint : 부분 트랜잭션 롤백을 지원하는지 여부

# 각각의 디비(테이블)에서 사용하는 스토리지 엔진 확인 방법

MariaDB [(none)]> SELECT table_schema , table_name , engine FROM information_schema.tables WHERE table_schema IN ('HOME001' ,'mysql','performance_schema') ;

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

| table_schema       | table_name                                   | engine             |

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

| HOME001            | tbl_bgm                                      | TokuDB             |

| HOME001            | tbl_bookmark_me                              | TokuDB             |

| HOME001            | tbl_folder                                   | TokuDB             |

| HOME001            | tbl_friend                                   | TokuDB             |

| HOME001            | tbl_group                                    | TokuDB             |

| HOME001            | tbl_group_member                             | TokuDB             |

| HOME001            | tbl_home                                     | TokuDB             |

| HOME001            | tbl_home_deco                                | TokuDB             |

| HOME001            | tbl_home_delete_log                          | TokuDB             |

| HOME001            | tbl_home_tag                                 | TokuDB             |

| HOME001            | tbl_like                                     | TokuDB             |

| HOME001            | tbl_link                                     | TokuDB             |

| HOME001            | tbl_linker                                   | TokuDB             |

| HOME001            | tbl_mylike                                   | TokuDB             |

| HOME001            | tbl_network_update                           | InnoDB             |

| HOME001            | tbl_news                                     | TokuDB             |

| HOME001            | tbl_news_get                                 | TokuDB             |

| HOME001            | tbl_post                                     | TokuDB             |

| HOME001            | tbl_reply                                    | TokuDB             |

| HOME001            | tbl_tag                                      | TokuDB             |

| HOME001            | tbl_visit_count                              | TokuDB             |

| HOME001            | tbl_visit_count_bak                          | TokuDB             |

| mysql              | columns_priv                                 | MyISAM             |

| mysql              | db                                           | MyISAM             |

| mysql              | event                                        | MyISAM             |

| mysql              | func                                         | MyISAM             |

| mysql              | general_log                                  | CSV                |

| mysql              | help_category                                | MyISAM             |

| mysql              | help_keyword                                 | MyISAM             |

| mysql              | help_relation                                | MyISAM             |

| mysql              | help_topic                                   | MyISAM             |

| mysql              | host                                         | MyISAM             |

| mysql              | ndb_binlog_index                             | MyISAM             |

| mysql              | plugin                                       | MyISAM             |

| mysql              | proc                                         | MyISAM             |

| mysql              | procs_priv                                   | MyISAM             |

| mysql              | proxies_priv                                 | MyISAM             |

| mysql              | servers                                      | MyISAM             |

| mysql              | slow_log                                     | CSV                |

| mysql              | tables_priv                                  | MyISAM             |

| mysql              | time_zone                                    | MyISAM             |

| mysql              | time_zone_leap_second                        | MyISAM             |

| mysql              | time_zone_name                               | MyISAM             |

| mysql              | time_zone_transition                         | MyISAM             |

| mysql              | time_zone_transition_type                    | MyISAM             |

| mysql              | user                                         | MyISAM             |

| performance_schema | cond_instances                               | PERFORMANCE_SCHEMA |

| performance_schema | events_waits_current                         | PERFORMANCE_SCHEMA |

| performance_schema | events_waits_history                         | PERFORMANCE_SCHEMA |

| performance_schema | events_waits_history_long                    | PERFORMANCE_SCHEMA |

| performance_schema | events_waits_summary_by_instance             | PERFORMANCE_SCHEMA |

| performance_schema | events_waits_summary_by_thread_by_event_name | PERFORMANCE_SCHEMA |

| performance_schema | events_waits_summary_global_by_event_name    | PERFORMANCE_SCHEMA |

| performance_schema | file_instances                               | PERFORMANCE_SCHEMA |

| performance_schema | file_summary_by_event_name                   | PERFORMANCE_SCHEMA |

| performance_schema | file_summary_by_instance                     | PERFORMANCE_SCHEMA |

| performance_schema | mutex_instances                              | PERFORMANCE_SCHEMA |

| performance_schema | performance_timers                           | PERFORMANCE_SCHEMA |

| performance_schema | rwlock_instances                             | PERFORMANCE_SCHEMA |

| performance_schema | setup_consumers                              | PERFORMANCE_SCHEMA |

| performance_schema | setup_instruments                            | PERFORMANCE_SCHEMA |

| performance_schema | setup_timers                                 | PERFORMANCE_SCHEMA |

| performance_schema | threads                                      | PERFORMANCE_SCHEMA |

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

63 rows in set (0.04 sec)

#엔진 종류

archive : 데이터를 압축해서 저장 함.(인서트 후 행의 변경 없음) 

blackhole : 쓰기는 무시하고 비어 있는 값을 반환하는 엔진

csv : 컴마로 구분된 값의 형태로 저장

federated : 원격 테이블 접속을 위한 엔진

mrg_myisam : MyISAM 테이블의 집합 관리

# 엔진에 의해서 생성 된 파일

InnoDB : *.frm(메타정보) ,*.ibd (데이터 와 인덱스가 저장됨)

MyISAM : *.frm(메타정보), *.MYD(데이터) , *.MYI(인덱스)

CSV : *.CSV(데이터), *.CSM(메타데이터)

Memory : *.frm(메타정보)

# InnoDB엔진에서 하나의 테이블 스페이스를 사용할 경우

mysql> show global variables like 'innodb_data_home_dir' ;  -- 테이블 스페이스를 구성하는 컴퍼넌트들의 부모 디렉터리, 미 지정 시 datadir

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

| Variable_name        | Value |

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

| innodb_data_home_dir |       |

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

1 row in set (0.00 sec)

mysql> show global variables like 'innodb_data_file_path' ;  -- 테이블 스페이스를 구성하는 파일들의 정보 저장. 하나 이상의 파일이 ;로 구분

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

| Variable_name         | Value                  |

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

| innodb_data_file_path | ibdata1:12M:autoextend |      -- ibdata1이라는 테이블스페이스가 초기 12M 크기이며 자동 증가된다.

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

예를 들어서 2개의 테이블스페이스(tbs1, tbs2)를 /usr/tbs 밑에 만들면서 각각 4G짜리로 만든다고 하면

innodb_data_home_dir = /usr/tbs

innodb_data_file_path = tbs1:4G:autoextend;tbs2:4G:autoextend

또는

innodb_data_home_dir =

innodb_data_file_path = /usr/tbs/tbs1:4G:autoextend;/usr/tbs/tbs2:4G:autoextend

자동 증가 사이즈는 다음 변수 값이 적용 된다.

mysql> show global variables like 'innodb_autoextend_increment' ;

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

| Variable_name               | Value |

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

| innodb_autoextend_increment | 64    |

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

Free Space 확인

mysql> SELECT tablespace_name , sum(data_free) as freespace

    -> FROM   information_schema.partitions

    -> WHERE  tablespace_name is not null

    -> GROUP BY tablespace_name ;

# InnoDB엔진에서 테이블당 개별 파일로 관리하고자 한다면 다음의 변수 설정

mysql> show global variables like 'innodb_file_per_table' ;

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

| Variable_name         | Value |

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

| innodb_file_per_table | ON    |

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

기본은 하나의 테이블 스페이스로 관리를 하지만, 특정 테이블만 파일 형태로 관리 하고 싶으면 다음과 같이 사용할 수 있다.

set global innodb_file_per_table = 1 ;

CREATE TABLE abcd …. ENGINE=INNODB ROW_FORMAT=COMPRESSED ;

set global innodb_file_per_table = 0 ;

# InnoDB를 사용할 경우 파일 포멧

mysql> show global variables like 'innodb_file_format' ;

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

| Variable_name      | Value     |

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

| innodb_file_format | Barracuda |

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

# InnoDB 변수 들 중 동작에 영향을 주는 변수들

mysql> show global variables like 'innoDB%' ;

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

| Variable_name                            | Value                  |

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

| innodb_buffer_pool_size                  | 134217728              |

| innodb_log_buffer_size                   | 16777216               |

| innodb_log_file_size                     | 50331648               |

| innodb_log_files_in_group                | 2                      |

| innodb_log_group_home_dir                | ./                     |

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

innodb_buffer_pool_size    : Physical I/O를 줄일 목적

innodb_log_buffer_size     : 로그 버퍼의 사이즈

innodb_log_file_size       : 개별 로그파일 사이즈

innodb_log_files_in_group  : 로그 그룹의 개수

innodb_log_group_home_dir  : 로그 파일 위치

로그 파일의 이름은 ib_로 시작한다.

[root@mysqlent5 data]# ls -al ib_*

-rw-r----- 1 mysql mysql      551 Dec  7 18:00 ib_buffer_pool

-rw-r----- 1 mysql mysql 50331648 Dec  7 19:59 ib_logfile0

-rw-r----- 1 mysql mysql 50331648 Dec  4 00:12 ib_logfile1

# 서버 호스트에 영향을 주는 시스템 시작 시점에 결정되는 시간대 (동적 변경 안됨)

mysql> show global variables like 'system_time_zone' ;

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

| Variable_name    | Value |

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

| system_time_zone | KST   |

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

# MySQL 서버의 기본 시간대

mysql> show global variables like 'time_zone' ;

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

| Variable_name | Value  |

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

| time_zone     | SYSTEM |

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

mysql> show variables like 'time_zone' ;

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

| Variable_name | Value  |

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

| time_zone     | SYSTEM |

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

mysql> select @@global.time_zone , @@session.time_zone ;

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

| @@global.time_zone | @@session.time_zone |

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

| SYSTEM             | SYSTEM              |

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

세션에서 로케일을 참조하려면

set session time_zone = 'US/Central' ;

set session time_zone = 'CST6CDT' ;

set session time_zone = 'Asia/Jakarta' ;

# 기본 캐릭 터 셋과 콜레이션

mysql> show global variables like 'character\_set\_%' ;

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

| Variable_name            | Value  |

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

| character_set_client     | euckr  |

| character_set_connection | euckr  |

| character_set_database   | euckr  |

| character_set_filesystem | binary |

| character_set_results    | euckr  |

| character_set_server     | euckr  |

| character_set_system     | utf8   |

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

7 rows in set (0.00 sec)

mysql> show global variables like 'collation\_%' ;

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

| Variable_name        | Value           |

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

| collation_connection | euckr_korean_ci |

| collation_database   | euckr_korean_ci |

| collation_server     | euckr_korean_ci |

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

# 캐릭터 셋과 콜레이션 지정 방법

- DB Level

CREATE DATABASE db_name CHARACTER SET charset COLLATE collation ;

- Table Level

CREATE TABLE tb_name ( …… ) CHARACTER SET charset COLLATE collation ;

- Table & Column Level

CREATE TABLE tb_name

( id char(10) CHARACTER SET charset COLLATE collation ,

  ……. )

CHARACTER SET another_charset COLLATE another_collation ;

# 특정 캐릭터 셋으로 정렬(콜레이션)을 하고 싶다면 COLLATE 연산자

SELECT c

FROM  tab

ORDER BY c COLLATE latin1_spanish_ci ;

# 에러 메시지용 언어 설정

mysql> show global variables like 'lc_messages%' ;

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

| Variable_name   | Value             |

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

| lc_messages     | en_US             |

| lc_messages_dir | /usr/share/mysql/ |

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

lc_messages_dir은 에러 메시지 파일이 있는 디렉터리 임.

# 로케일 시스템 변수 확인

mysql> show global variables like 'lc_time_names' ;

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

| Variable_name | Value |

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

| lc_time_names | en_US |

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

mysql> select dayname (now()) , monthname(now()) ;

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

| dayname (now()) | monthname(now()) |

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

| Friday          | December         |

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

# 세션레벨에서 로케일 변수 변경

mysql> set session lc_time_names = 'es_ES' ;

mysql>  select dayname (now()) , monthname(now()) ;

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

| dayname (now()) | monthname(now()) |

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

| viernes         | diciembre        |

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

연결

max_connections

최대 동시 클라이언트 접속자 수 .

Max_used_connections / Connection_errors_max_connections 상태 지표값을 참조 하면서 증가 시킨다.

back_log

현재 연결을 진행하는 중에 다른 연결 요청이 오면 지연 요청 큐에 담을 수 있는 최대 개수.

접속자가 많은 사이트에서 클라이언트 접속이 느리게 진행되면 이 값을 증가 시킨다.

테이블

table_open_cache

서버가 테이블 파일을 열고 닫는 동작을 최소화 하기 위해 되도록이면 열린 상태를 유지한다.

이를 위해 테이블 캐시가 열린 파일들의 정보를 유지한다. 동시에 접근하는 테이블이 많을 경우 캐시가 금방 차게 되는데, Opened_tables / Table_open_cache_overflows 상태 지표를 참조하여 증가 시킨다.

table_definition_cache

table_open_cache값을 증가 시키면 table_definition_cache의 증가도 같이 고려해야 한다.

.frm 파일의 정보를 저장하는 캐시의 크기이다.

Opened_table_definitions 상태 지표를 참조하여 증가 시킨다.

open_files_limit

max_connections나 table_open_cache의 값을 증가 시키면 서버는 많은 수의 파일 디스크립터를 사용함으로써 운영체제의 프로세스별 파일 디스크립터 개수 제한을 넘어서는 문제가 발생 할 수 있다.

이 제한 값을 우회하는 방법으로는 다음과 같은 방법이 있다.

통신

max_allowed_packet

blob나 text값을 포함하는 긴 수행문을 보내는 클라이언트의 경우 서버단과 클라이언트단 모두에서 max_allowed_packet변수 값을 증가 시킨다.

쿼리

read_buffer_size

세션에서 사용하는 읽기 버퍼. 세션별로 할당이 되므로 과도하게 크게 설정이 되면 시스템 리소스 부족을 초래 할 수 있다. 필요 시 세션레벨에서 변경해서 사용.

sort_buffer_size

세션에서 사용하는 정렬 버퍼.  정렬이 사용되지 않으면 할당되지 않는다.

세션별로 할당이 되므로 과도하게 크게 설정이 되면 리소스 부족을 초래 할 수 있다. 필요 시 세션 레벨에서 변경해서 사용.

join_buffer_size

세션에서 사용하는 쿼리에 조인이 있을 경우에 할당되어 사용됨. 여러 개의 조인이 있을 경우 복수개의 조인 버퍼가 할당되어 사용됨.

innodb_buffer_pool_size

버퍼 풀의 총 사이즈(단위는 바이트)

innodb_buffer_pool_instances

버퍼 풀의 총 사이즈 안에서 몇 개의 작은 버퍼 풀로 나누어 사용할지 개수 지정

innodb_old_blocks_pct

innodb_old_blocks_time

일회성 full table 스캔 같은 경우, 한 번 접근 후 더 이상의 접근이 없는 데이터 임에도 이 데이터 들로 인해서 다른 데이터 들이 캐시에서 밀려날 수 있다. 그래서 이런 쿼리가 수행될 때는 이 값을 0 이상으로 주어서 기존 캐시의 데이터에 영향을 주지 않게 한다.

그러나 시스템 재 부팅 후 preload를 할 경우는, full table 스캔으로 자주 사용하는 테이블의 데이터를 로드 시켜 놓아야 하기 때문에 이 값을 0 으로 준 후에 데이터를 로드 시켜 주고, 다시 0보다 큰 값으로 변경 시켜 준다. 

# 쿼리 캐시 관련 시스템 변수

mysql> show global variables like '%query%' ;

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

| Variable_name                | Value                             |

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

| have_query_cache             | YES                               |

| query_alloc_block_size       | 8192                              |

| query_cache_limit            | 1048576                           |

| query_cache_min_res_unit     | 4096                              |

| query_cache_size             | 1048576                           |

| query_cache_type             | OFF                               |

| query_cache_wlock_invalidate | OFF                               |

| query_prealloc_size          | 8192                              |

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

have_query_cache : 시스템에서 쿼리 캐시를 지원하는지 여부 확인

query_cache_type :  0 (OFF)   : OFF

                    1 (ON)    : SELECT SQL_NO_CACHE로 시작하는 것만 제외하고 저장 가능한 쿼리 다 저장

                    2 (DEMAND): SELECT SQL_CACHE로 시작하는 것들 중 저장 가능한 쿼리 저장

query_cache_size : 캐시로 할당하는 메모리 사용량. query_cache_type이 0 이더라도 이 메모리는 할당 됨.

                              사용하지 않을 때는 0으로설정

query_cache_limit : 쿼리 결과로 저장할 수 있는 최대 결과 값 지정. 이 값보다 크면 저장 안됨.

# 로그 설정 시스템 변수들

mysql> show global variables like '%log%' ;

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

| Variable_name                           | Value                             |

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

| general_log                             | OFF                               |

| general_log_file                        | /var/lib/mysql/localhost.log      |

| innodb_log_group_home_dir               | ./                                |

| log_bin                                 | ON                                |

| log_bin_basename                        | /var/lib/mysql/mysql-bin          |

| log_bin_index                           | /var/lib/mysql/mysql-bin.index    |

| log_error                               | /var/log/mysqld.log               |

| log_output                              | FILE,TABLE                        |

| log_warnings                            | 2                                 |

| relay_log                               |                                   |

| relay_log_basename                      |                                   |

| relay_log_index                         |                                   |

| slow_query_log                          | ON                                |

| slow_query_log_file                     | /var/lib/mysql/localhost-slow.log |

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

general_log : 일반 쿼리 로그 활성화

general_log_file : 일반 쿼리 로그 파일 위치

log_bin : 바이너리 로그 활성 화

log_bin_basename : 바이너리 로그 파일 이름

log_bin_index : 바이너리 로그 인덱스 파일 

log_error : 에러 로그 파일

log_output : 에러 로그 및 일반 쿼리 로그 출력 위치

log_warnings : 에러 로그의 로깅 정보의 양 조절

slow_query_log : 슬로우 쿼리 로그 활성화

slow_query_log_file : 슬로우 쿼리 로그 파일 위치

innodb_log_group_home_dir : InnoDB가 엔진 자동 복구를 위하여 사용하는 로그가 저장되는 위치 (기본은 datadir 위치 )

일반 로그와 슬로우 쿼리 로그를 테이블로 아웃풋 되게 했다면 주기적으로  다음 명령을 수행해서 사이즈를 줄여야 한다.

mysql> use mysql

mysql> truncate general_log ;

mysql> truncate slow_log ;

# 로그 플러싱

mysql> show master status ;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000183 |      154 |              |                  |                   |

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

mysql> flush logs ;

mysql> show master status ;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000184 |      154 |              |                  |                   |

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

[root@localhost ~]# mysqladmin -u root -p flush-logs

mysql> show master status ;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000185 |      154 |              |                  |                   |

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

[root@localhost ~]# mysqladmin -u root -p refresh

mysql> show master status ;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000186 |      154 |              |                  |                   |

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

# 로그 플러 시 만을 위한 계정 생성

CREATE USER 'flush'@'localhost' IDENTIFIED BY 'flushpass' ;

GRANT RELOAD ON *.* TO 'flush'@'localhost' ;

# 마스터 서버에서 bin log 삭제하기

마스터 서버에서 bin 로그를 삭제하기 전에 slave 서버에서 해당 로그들이 다 반영되었는지 확인 후 삭제를 해야 한다.

마스터 서버에서는 슬레이브 서버가 몇 개 기동중이며, 어디까지 반영 되었는지 모르기 때문에 일일이 슬레이브에 접속해서

show slave status를 수행 후 master_log_file 값을 확인해 봐야 한다.

확인 후 삭제 해도 되는 로그 번호를 확인 한 후에 예를 들어서 40번까지 삭제해도 된다고 한다면…

mysql> purge binary logs to 'mysql-bin.000041' ; 41보다 작은 것 삭제 하라는 명령임.

basedir=dir_name

MySQL 설치 루트 디렉터리의 경로명

datadir=dir_name

데이터 디렉터리의 경로명

port=port_num

TCP/IP 연결을 위한 포트 번호

socket=file_name

유닉스 소켓파일 경로명 또는 윈도우 네임드 파이프명

general_log

일반 로그 활성화

general_log_file=file_name

일반 로그 파일명

log_bin=file_name

바이너리 로그 활성화

log_bin_index=file_name

바이너리 로그 인덱스 파일

log_error=file_name

에러로그 활성 화

log_output=destination

일반로그 및 슬로우 쿼리 로그 출력 위치

pid_file=file_name

프로세스 ID 파일

slow_query_log

슬로우 쿼리 로그 활성화

slow_query_log_file=file_name

슬로우 쿼리 로그 파일