MySQL 5.5.35 单机多实例配置详解

红色多瑙河

[root@node1 ~]# cd src/　　
[root@node1 src]# cd mysql-5.5.35
　　
[root@node1 mysql-5.5.35]# cp support-files/my-small.cnf /data/mysql/etc/my.cnf
　　
[root@node1 ~]# cd /data/mysql/etc/
　　
[root@node1 etc]# vim my.cnf
　　
# This server may run 4+ separate instances. So we use mysqld_multi to manage their services.
　　
[client]
　　
default-character-set = utf8
　　
[mysqld_multi]
　　
mysqld = /data/mysql/bin/mysqld_safe
　　
mysqladmin = /data/mysql/bin/mysqladmin
　　
log = /data/mysql/log/mysqld_multi.log
　　
user = root
　　
#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 = /data/mysql/run/mysqld.sock
　　
port = 3306
　　
pid-file = /data/mysql/run/mysqld.pid
　　
datadir = /data/mysql/data
　　
lc-messages-dir = /data/mysql/share/english
　　
# These support master - master replication
　　
#auto-increment-increment = 4
　　
#auto-increment-offset = 1  # Since it is master 1
　　
log-bin = /data/mysql/binlogs/bin-log-mysqld1
　　
log-bin-index = /data/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 /data/mysqld/data2
　　
[mysqld2]
　　
socket = /data/mysql/run/mysqld.sock2
　　
port = 3307
　　
pid-file = /data/mysql/run/mysqld.pid2
　　
datadir = /data/mysql/data2
　　
lc-messages-dir = /data/mysql/share/english
　　
# Disable DNS lookups
　　
#skip-name-resolve
　　
# These support master - slave replication
　　
log-bin = /data/mysql/binlogs/bin-log-mysqld2
　　
log-bin-index = /data/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 = /data/mysql/log/relay-log-mysqld2
　　
#relay-log-index = /data/mysql/log/relay-log-mysqld2.index
　　
#relay-log-space-limit = 4G
　　
# Slow query log settings
　　
#log-slow-queries = /data/mysql/log/slow-log-mysqld2
　　
#long_query_time = 2
　　
#log-queries-not-using-indexes
　　
# This is exlusively for mysqld3
　　
# It is on 3308 with data directory /data/mysqld/data3
　　
[mysqld3]
　　
socket = /data/mysql/run/mysqld.sock3
　　
port = 3308
　　
pid-file = /data/mysql/run/mysqld.pid3
　　
datadir = /data/mysql/data3
　　
lc-messages-dir = /data/mysql/share/english
　　
#Disable DNS lookups
　　
#skip-name-resolve
　　
# These support master - slave replication
　　
log-bin = /data/mysql/binlogs/bin-log-mysqld3
　　
log-bin-index = /data/mysql/binlogs/bin-log-mysqld3.index
　　
#binlog-do-db =  # Leave this blank if you want to control it on slave
　　max_binlog_size = 1024M
　　# This is exlusively for mysqld4
　　
# It is on 3309 with data directory /data/mysqld/data4
　　
[mysqld4]
　　
socket = /data/mysql/run/mysqld.sock4
　　
port = 3309
　　
pid-file = /data/mysql/run/mysqld.pid4
　　
datadir = /data/mysql/data4
　　
lc-messages-dir = /data/mysql/share/english
　　
# Disable DNS lookups
　　
#skip-name-resolve
　　
# These support master - slave replication
　　
log-bin = /data/mysql/binlogs/bin-log-mysqld4
　　
log-bin-index = /data/mysql/binlogs/bin-log-mysqld4.index
　　
#binlog-do-db =  # Leave this blank if you want to control it on slave
　　
max_binlog_size = 1024M
　　# The rest of the my.cnf is shared
　　
# Here follows entries for some specific programs
　　
# The MySQL server
　　
[mysqld]
　　
basedir = /data/mysql
　　
tmpdir = /data/mysql/tmp
　　
socket = /data/mysql/run/mysqld.sock
　　
port = 3306
　　
pid-file = /data/mysql/run/mysqld.pid
　　
datadir = /data/mysql/data
　　
lc-messages-dir = /data/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 = 2
　　
# 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>　　
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
　　
# Enable Per Table Data for InnoDB to shrink ibdata1
　　
innodb_file_per_table = 1
　　
# Uncomment the following if you are using InnoDB tables
　　
#innodb_data_home_dir = /data/mysql/data
　　
#innodb_data_file_path = ibdata1:10M:autoextend
　　
#innodb_log_group_home_dir = /data/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>　　
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 = /data/mysql/log/mysqld.log
　　
pid-file = /data/mysql/run/mysqld.pid
　　
open-files-limit = 8192