my.cnf
Posted: Tue Jun 21, 2011 8:33 am
My my.cnf on our main server (Centos 4, MySql 5.1) is in a bit of a mess. It dates back from mysql 3.x and I think the problem is that things have been added over the years, somtimes in the the wrong place.
Can someone help me tidy it up?
As you'll see, as well as a lot of lines with "set-variable" lines. Should these be replaced with "option = value" or not?
e.g. instead of "set-variable = innodb_additional_mem_pool_size=500K" should it just be
"innodb_additional_mem_pool_size=500K" ?
I'm sure I just read something about "set-variable" being deprecated as of MySQL4 but now I can't find the reference.
I also note that I have a lot of stuff in the [mysqld_safe] section, sometimes duplicating what's in [mysqld]. According to the mysql manual, the contents of [mysqld_safe] will get read along with everything in [mysqld] (and [server]) when mysqld is started with the mysqld_safe startup script.
Great, so I should move most of what's in [mysqld_safe] to [mysqld] rather than having duplicates - except that looking at an unmodified my.cnf from another server, the contents of [mysqld] and [mysqld_safe] are mostly duplicated, so maybe not! Oh, and what's this? lots of set-"variable= rather" than "option = value"! So I'm probably wrong about it being deprecated too.
my.cnf from main server:
Default (almost) my.cnf from mysql 5.something on Centos 4 (I think):
Can someone help me tidy it up?
As you'll see, as well as a lot of lines with "set-variable" lines. Should these be replaced with "option = value" or not?
e.g. instead of "set-variable = innodb_additional_mem_pool_size=500K" should it just be
"innodb_additional_mem_pool_size=500K" ?
I'm sure I just read something about "set-variable" being deprecated as of MySQL4 but now I can't find the reference.
I also note that I have a lot of stuff in the [mysqld_safe] section, sometimes duplicating what's in [mysqld]. According to the mysql manual, the contents of [mysqld_safe] will get read along with everything in [mysqld] (and [server]) when mysqld is started with the mysqld_safe startup script.
Great, so I should move most of what's in [mysqld_safe] to [mysqld] rather than having duplicates - except that looking at an unmodified my.cnf from another server, the contents of [mysqld] and [mysqld_safe] are mostly duplicated, so maybe not! Oh, and what's this? lots of set-"variable= rather" than "option = value"! So I'm probably wrong about it being deprecated too.
my.cnf from main server:
Code: Select all
[mysqld]
#innodb_force_recovery = 2
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-bdb
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 3072
thread_cache_size = 64
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
max_allowed_packet = 16M
set-variable = innodb_buffer_pool_size=30M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-bdb
set-variable = innodb_buffer_pool_size=30M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
Default (almost) my.cnf from mysql 5.something on Centos 4 (I think):
Code: Select all
[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
# these lines and adjust the connectstring as needed.
#ndbcluster
#ndb-connectstring="nodeid=4;host=localhost:1186"
skip-bdb
set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-bdb
set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
[ndbd]
# If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
# adjust its connection to the management daemon here.
# Note: ndbd init script requires this to include nodeid!
connect-string="nodeid=2;host=localhost:1186"
[ndb_mgm]
# connection string for MySQL Cluster management tool
connect-string="host=localhost:1186"