my.cnf 설정

Be sure to know your my.cnf [sections]

January 26, 2010 by ronald

The MySQL configuration file, e.g. /etc/my.cnf has a number of different section headings including [mysql], [mysqld], [mysqld_safe]. It is important that you ensure you put the right variables into the right section. For example, the following my.cnf configuration file will not operate as the user probably expects.

[mysqld]
...
log-bin=mysql-bin
server-id=1
query_cache_size = 100M
query_cache_type = 1

...

[mysqld_safe]
...
key_buffer_size=600M
skip-innodb
...

In this example, this configuration does not give you a MyISAM key buffer of 600M, it’s actually the default of 8M.

mysql> show global variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388600 |
+-----------------+---------+

Be sure to add the right options to the [mysqld] section.

What I didn’t know until yesterday was that some programs read from multiple groups. From the 5.1.2. Server Command Options MySQL reference manual page. In helping the describe the problem for the benefit of readers I actually learned something new myself.

mysqld reads options from the [mysqld] and [server] groups. mysqld_safe reads options from the [mysqld], [server], [mysqld_safe], and [safe_mysqld] groups. mysql.server reads options from the [mysqld] and [mysql.server] groups.

I have for example always put log-error in both the [mysqld_safe] and [mysql]d sections because both of these write different errors. Seems that is unnecessary.

source - http://ronaldbradford.com/blog/be-sure-to-know-your-my-cnf-sections-2010-01-26/

---

my.cnf

MySQL의 설정은 '/etc/my.cnf'(Ubuntu v11.10 MySQL v5.1의 경우 /etc/mysql/my.cnf)을 이용한다.

'my.cnf'는 UNIX/Linux 계열의 MySQL 엔진에서 사용하는 Configuration File이다. 이 파일은 설치 시 또는 MySQL 구동 시 지정하여 사용할 수 있다.

# mysqld_safe --default-file=/etc/my.cnf --user=mysql &

my.cnf 설정 변수

datadir	Database File을 생성할 Directory를 설정
autocommit	[0] disable   [1] Set
socket	사용할 Socket File
port	TCP/IP 접속 시 사용할 PORT
binlog_cache_size	Transaction 실행동안 Binary Log를 저장하기 위한 Cache Size
innodb_file_per_table	테이블마다 테이블 스페이스를 두겠다는 의미
innodb_log_files_in_group	InnoDB 로그 파일 개수 정의(default=2). 가능한 변경하지 않는 것을 권장
innodb_buffer_pool_size	System Memory의 50~80% 사이로 정의
innodb_log_file_size	트랜잭션을 기록하는 로그 파일의 크기를 결정하는 옵션.  로그파일은 무한정 계속 커지는 것이 아니라 일정한 크기와 갯  수를 가지고 순환식으로 처리되므로 innodb_log_file_size는   inno_buffer_pool_size 의 15% 정도로 설정.  만약 메모리가 1기가이면 inno_buffer_pool_size = 512M 이고,   innodb_log_file_size = 80M.
innodb_log_buffer_size	로그 파일을 기록하기 위한 버퍼 사이즈.  트랜잭션이 작거나 거의 없다면 크게 잡는것은 낭비.  보통 1M~8M 사이로 설정.
innodb_additional_mem_pool_size	Dictionary Cache Size 정의. 8~16M 설정.  'Show innodb status'에 나타나면 증가 권장
innodb_flush_log_at_trx_commit	INSERT, UPDATE 등 데이터 삽입과 관계된 설정 값.  Commit 을 하였을때, 그 즉시 Commit 된 데이터를 log file 에   기록할지 안할지를 설정.  로그파일을 기록할 경우 갑작스러운 경우 데이터 손실을 막을   수 있지만, 매번 로그를 기록하므로 속도가 저하.  [1] Commit 즉시 반영  [0] 추후 반영
innodb_autoextend_increment	Tablespace가 확장될 때 증가되는 Size(MB) (default=8M)
innodb_thread_concurrency	Thread수 * Disk수 (default=8)

참조 링크

http://blog.naver.com/entrv/100017025105

http://mysqldba.tistory.com/26

http://cafe.naver.com/swingme/236

MySQL 문자 Set 정의 관련 설정 변수

[client]

default-character-set=utf8

[mysqld]

character-set-client-handshake=FALSE

default-character-set=utf8

character-set-server=utf8

collation-server=utf8_general_ci

init-connect='set names utf8'

[mysqldump]

default-character-set=utf8

[mysql]

default-character-set=utf8

InnoDB 관련 설정 변수

[mysqld]

innodb_data_home_dir="/MySQL/MySQL Server 5.0/ibdata/"
innodb_log_group_home_dir="/MySQL/MySQL Server 5.0/iblogs"
innodb_data_file_path=ibdata1:10M:autoextend:max:1000M
innodb_additional_mem_pool_size=3469K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=256M
innodb_log_file_size=40M
innodb_thread_concurrency=8
innodb_log_archive=0

출처 - http://aladdin07.blog.me/150122882385

---

* sample in fedora20(mariadb) 

  - default-character-set=utf8 부분을 [mysqld] 섹션에 넣으면 에러 발생

# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mysqld/mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

# Currently, there are mariadb and community-mysql packages in Fedora.

# This particular config file is included in respective RPMs of both of them,

# so the following settings are general and will be also used by both of them.

# Otherwise the RPMs would be in conflict.

# Settings for particular implementations like MariaDB are then

# defined in appropriate sections; for MariaDB server in [mariadb] section in

# /etc/my.cnf.d/server.cnf (part of mariadb-server).

# It doesn't matter that we set these settings only for [mysqld] here,

# because they will be read and used in mysqld_safe as well.

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

init_connect="SET character_set_server=utf8"

init_connect="SET collation_connection=utf8_general_ci"

init_connect=SET NAMES utf8

character-set-server=utf8

collation-server=utf8_general_ci

skip-character-set-client-handshake

[mysqld_safe]

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

[mysqldump]

default-character-set=utf8

* sample in centos6(mariadb)

# vi /etc/my.cnf

#

# This group is read both both by the client and the server

# use it for options that affect everything

#

[client-server]

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

init_connect=SET character_set_server=utf8

init_connect=SET collation_connection=utf8_general_ci

init_connect=SET NAMES utf8

character-set-server=utf8

collation-server=utf8_general_ci

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

[mysqldump]

default-character-set=utf8

---

Q.

I am trying to understand the different sections inside the my.ini configuration file ([client], [mysqld], [mysql]) and so on, I am looking for a guide describing each of the optional sections for the my.ini file, Also i was wondering what is the difference between init_connect and init-connect and i mean between the underscore(_) and the hyphen(-), Thank you all and have a nice day.

A.

[mysql] applies to the mysql command line client - [mysql and client options]

[client] applies to all connecting clients (including mysql cli) - [mysql and client options]

[mysqld] applies to the mysql server - [server options]

[mysqldump] applies to the utility of the same name - [mysqldump options]

...etc

The difference between the (-) and the (_) is the context in which it is used.

(-) is used in command line parameters, where (_) is used in options file parameters.

You can see more in the docs: http://dev.mysql.com/doc/refman/5.5/en/option-files.html

source - http://stackoverflow.com/questions/15453555/mysql-configuration-file-sections