Monday, January 16, 2012

[Level 1] Notes for MySQL. (config)

## take care with signature '???'

[mysql]
user=root
password
database=mysql
#!include /etc/my.inc
#prompt=\R:\m \d>
#prompt=\u:\h \d>

[mysqldump]

[client]
## new cert
#ssl-key=/usr/local/mysql/newcerts/client-key.pem
#ssl-cert=/usr/local/mysql/newcerts/client-cert.pem
#ssl-ca=/usr/local/mysql/newcerts/ca-cert.pem
user=root
host=localhost
protocol=tcp
compress
password

######################################################[mysqld_multi]
[mysqld_multi]
server-id  = 0
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = multi_admin
password   = my_password

[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /usr/local/mysql/data2/hostname.pid2
datadir    = /usr/local/mysql/data2
language   = /usr/local/mysql/share/mysql/english
user       = unix_user1

[mysqld3]
mysqld     = /path/to/safe_mysqld/safe_mysqld
ledir      = /path/to/mysqld-binary/
mysqladmin = /path/to/mysqladmin/mysqladmin
socket     = /tmp/mysql.sock3
port       = 3308
pid-file   = /usr/local/mysql/data3/hostname.pid3
datadir    = /usr/local/mysql/data3
language   = /usr/local/mysql/share/mysql/swedish
user       = unix_user2

[mysqld4]
socket     = /tmp/mysql.sock4
port       = 3309
pid-file   = /usr/local/mysql/data4/hostname.pid4
datadir    = /usr/local/mysql/data4
language   = /usr/local/mysql/share/mysql/estonia
user       = unix_user3
 
[mysqld6]
socket     = /tmp/mysql.sock6
port       = 3311
pid-file   = /usr/local/mysql/data6/hostname.pid6
datadir    = /usr/local/mysql/data6
language   = /usr/local/mysql/share/mysql/japanese
user       = unix_user4
######################################################[mysqld_multi]

[mysqld]
server-id=0
basedir=/usr/local/mysql
datadir=/Karajon/MySQLData # default $basedir/data
skip-name-resolve

## new cert
#ssl-key=/usr/local/mysql/newcerts/server-key.pem
#ssl-cert=/usr/local/mysql/newcerts/server-cert.pem
#ssl-ca=/usr/local/mysql/newcerts/ca-cert.pem

## ignore storage engine
#skip-innodb
#skip-myisam # cannot skip myisam
#skip-memory  # skip memory is not working, but no error.
#skip-blackhole
#skip-archive
#skip-mrg_myisam
#skip-csv
##skip-federated # no working, even enable federate.
#default-storage-engine=InnoDB # work in 5.1.38

################################################### enable federate
federated 

################################################### setting log
#log                       # default: off, on: log=$datadir/$hostname.log
log-bin=/Karajon/bin-log/stanley-nb-bin                   # default: off, on: log-bin=$datadir/$hostname-bin
binlog_format=row         # default: statement. BINLOG_FORMAT=[row|statement|mixed|default]


#max_binlog_cache_size=xxx # default: max_binlog_cache_size=18446744073709547520
#max_binlog_size=xxx       # default: max_binlog_size=1073741824
expire_logs_days=7 # default: expire_log_days=0 # never purge log files

log-slow-queries  # default off, on: log-slow-queries=$datadir/$hostname-slow.log
long-query-time=5 # default: long-query-time=10, mysql> set long_query_time=10;
#log-error=/usr/local/mysql/data/Stanley-NB.err # default: $datadir/$hostname.err
#log-output=file  # default: file, file/table/none
#log-short-format # suppress the extra information
#log-queries-not-using-indexes

################################################### pid file
#pid-file=/usr/local/mysql/data/Stanley-NB.pid # default: $datadir/$hostname.pid
#socket=/tmp/mysql.sock

################################################### transaction isolation level
#transaction-isolation=READ-COMMIT

################################################### connections
thread_cache_size=0  # default: 0, no. of threads for reuse.
max_connections=200  # default max_connection=100


################################################### MyISAM settings
# modify MyISAM key cache, unit=K, M or G, 
# in mysql> set   global key_buffer_size=128*1024*1024
#           set @@global.key_buffer_size=128*1024*1024
key_buffer_size=128M            # default is 8M
bulk_insert_buffer_size=4194304 # default is 8M

################################################### InnoDB settings
## set values
#innodb_data_home_dir=/usr/local/mysql/data
#innodb_data_file_path=innodata1:10M
#innodb_data_file_path=innodata1:100M
#innodb_data_file_path=innodata1:100M:autoextend
#innodb_data_file_path=innodata1:100M:autoextend:max:500M
#innodb_data_file_path=innodata1:100M;innodata2:100M:autoextend
#innodb_log_file_size=50M
#innodb_log_files_in_group=2
innodb_buffer_pool_size=32M # default 8M
innodb_log_buffer_size=8M   # default 1M

## setting flag
innodb_file_per_table 
#loose_innodb_file_per_table ## if you use install plugin statements to install the InnoDB plugin, prepend loose_ for each InnoDB option.

################################################### skip engine
#skip-InnoDB
#skip-Memory

#
#transaction-isolation = READ-COMMITTED
# set global transaction isolation level [isolation_level];
# set session transaction isolation level [isoloation_level];

default-storage-engine=MyISAM
## select @@storage_engine;
## set @@storage_engine=InnoDB; ## in mysql prompt


################################################### for MyISAM storage only
myisam_data_pointer_size=7 ## default: 6

################################################### for memory storage only
#max_heap_table_size=16777216 ## default: max_heap_table_size=16777216, limit memory storage size


################################################### for unix only
## window is case insensitive
#lower_case_table_names=1

################################################### buffer & cache
sort_buffer_size=2048000
query_cache_size=8M ## default:0, sometimes fail why???
#query-cache-type=on ## fail why???


####################################################### for window only
#shared-memory
#enable-named-pipe
## set pipe name if enable named pipe.
#socket='c:/s.socket'

####################################################### for myisam auto-repair
myisam-recover=FORCE,BACKUP # options: DEFAULT,BACKUP,FORCE,QUICK


####################################################### sql mode
#sql_mode='no_auto_create_user' ## for user/security
sql_mode='traditional'

####################################################### network
max_allowed_packet=128M ## default:1048576, will begin with net_buffer_length and increasing if necessarily.
net_buffer_length=16384     ## default: 16384
#bind-address=127.0.0.1      ## bind ip with loopback interface.
#bind-address=172.31.255.100 ## bind ip with specific nic(172.31.255.100), to secure mysql on TCP/IP

####################################################### table
table_open_cache=64 # default: 64, no. of entries

####################################################### special
#skip_symbolic_links

####################################################### replication
## @master server, set bin log database
#binlog-do-db=db1
#binlog-do-db=db2
#binlog-do-db=db3
## @slaver server1, set replicated database
#replicate-do-db=db1
#replicate-do-db=db2
## @slaver server2, set replicated database
#replicate-do-db=db2
#replicate-do-db=db3
Wish this helps. regards, Stanley Huang