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
