mysql 5.5 单机多实例提示pid_生产环境MySQL 5.5.x单机多实例配置实践

## This server may run 2+ separate instances

## So we use mysqld_multi to manage their services

[mysqld_multi]

mysqld = /opt/mysql/bin/mysqld_safe

mysqladmin = /opt/mysql/bin/mysqladmin

log = /opt/mysql/log/mysqld_multi.log

user = root ## Used for stopping the server via mysqladmin

#password =

## This is the general purpose database

## The locations are default

# They are left in [mysqld] in case the server is started normally instead of by mysqld_multi

[mysqld1]

socket = /opt/mysql/run/mysqld.sock

port = 3306

pid-file = /opt/mysql/run/mysqld.pid

datadir = /opt/mysql/data

lc-messages-dir = /opt/mysql/share/english

## These support master - master replication

#auto-increment-increment = 4

#auto-increment-offset = 1  ## Since it is master 1

#log-bin = /opt/mysql/binlogs/bin-log-mysqld1

#log-bin-index = /opt/mysql/binlogs/bin-log-mysqld1.index

#binlog-do-db = ## Leave this blank if you want to control it on slave

#max_binlog_size = 1024M

## This is exlusively for mysqld2

## It is on 3307 with data directory /opt/mysql/data2

[mysqld2]

socket = /opt/mysql/run/mysqld.sock2

port = 3307

pid-file = /opt/mysql/run/mysqld.pid2

datadir = /opt/mysql/data2

lc-messages-dir = /opt/mysql/share/english

## Disable DNS lookups

#skip-name-resolve

## These support master - slave replication

log-bin = /opt/mysql/binlogs/bin-log-mysqld2

log-bin-index = /opt/mysql/binlogs/bin-log-mysqld2.index

#binlog-do-db =  ## Leave this blank if you want to control it on slave

max_binlog_size = 1024M

## Relay log settings

#relay-log = /opt/mysql/log/relay-log-mysqld2

#relay-log-index = /opt/mysql/log/relay-log-mysqld2.index

#relay-log-space-limit = 4G

## Slow query log settings

#log-slow-queries = /opt/mysql/log/slow-log-mysqld2

#long_query_time = 2

#log-queries-not-using-indexes

## The rest of the my.cnf is shared

## Here follows entries for some specific programs

## The MySQL server

[mysqld]

basedir = /opt/mysql

tmpdir = /opt/mysql/tmp

socket = /opt/mysql/run/mysqld.sock

port = 3306

pid-file = /opt/mysql/run/mysqld.pid

datadir = /opt/mysql/data

lc-messages-dir = /opt/mysql/share/english

skip-external-locking

key_buffer_size = 16K

max_allowed_packet = 1M

table_open_cache = 4

sort_buffer_size = 64K

read_buffer_size = 256K

read_rnd_buffer_size = 256K

net_buffer_length = 2K

thread_stack = 128K

## Increase the max connections

max_connections = 200

## The expiration time for logs, including binlogs

expire_logs_days = 14

## Set the character as utf8

character-set-server = utf8

collation-server = utf8_unicode_ci

## This is usually only needed when setting up chained replication

#log-slave-updates

## Enable this to make replication more resilient against server crashes and restarts

## but can cause higher I/O on the server

#sync_binlog = 1

## The server id, should be unique in same network

server-id = 1

## Set this to force MySQL to use a particular engine/table-type for new tables

## This setting can still be overridden by specifying the engine explicitly

## in the CREATE TABLE statement

default-storage-engine = INNODB

## Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /opt/mysql/data

#innodb_data_file_path = ibdata1:10M:autoextend

#innodb_log_group_home_dir = /opt/mysql/data

## You can set .._buffer_pool_size up to 50 - 80 % of RAM

## but beware of setting memory usage too high

innodb_buffer_pool_size = 16M

innodb_additional_mem_pool_size = 2M

## Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 5M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 8M

sort_buffer_size = 8M

[mysqlhotcopy]

interactive-timeout

[mysql.server]

user = mysql

[mysqld_safe]

log-error = /opt/mysql/log/mysqld.log

pid-file = /opt/mysql/run/mysqld.pid

open-files-limit = 8192

[client]

default-character-set = utf8