MySQL多实例环境搭建和管理

cqlinx

vim /etc/my.cnf　　

　　
[mysqld_multi]
　　
mysqld = /usr/local/mysql/bin/mysqld_safe
　　
mysqladmin = /usr/local/mysql/bin/mysqladmin
　　
user = admin
　　
password = password
　　

　　
[mysqld1]
　　
socket = /data/dbdata_3306/mysql.sock
　　
port = 3306
　　
pid-file = /data/dbdata_3306/3306.pid
　　
datadir = /data/dbdata_3306
　　
user = mysql
　　
skip-name-resolve
　　
lower_case_table_names=1
　　
innodb_file_per_table=1
　　
back_log = 50
　　
max_connections = 300
　　
max_connect_errors = 1000
　　
table_open_cache = 2048
　　
max_allowed_packet = 16M
　　
binlog_cache_size = 2M
　　
max_heap_table_size = 64M
　　
sort_buffer_size = 2M
　　
join_buffer_size = 2M
　　
thread_cache_size = 64
　　
thread_concurrency = 8
　　
query_cache_size = 64M
　　
query_cache_limit = 2M
　　
ft_min_word_len = 4
　　
default-storage-engine = innodb
　　
thread_stack = 192K
　　
transaction_isolation = REPEATABLE-READ
　　
tmp_table_size = 64M
　　
log-bin=mysql-bin
　　
binlog_format=mixed
　　
slow_query_log
　　
long_query_time = 1
　　
server-id = 1
　　
key_buffer_size = 8M
　　
read_buffer_size = 2M
　　
read_rnd_buffer_size = 2M
　　
bulk_insert_buffer_size = 64M
　　
myisam_sort_buffer_size = 128M
　　
myisam_max_sort_file_size = 10G
　　
myisam_repair_threads = 1
　　
myisam_recover
　　
innodb_additional_mem_pool_size = 16M
　　
innodb_buffer_pool_size = 200M
　　
innodb_data_file_path = ibdata1:10M:autoextend
　　
innodb_file_io_threads = 8
　　
innodb_thread_concurrency = 16
　　
innodb_flush_log_at_trx_commit = 1
　　
innodb_log_buffer_size = 16M
　　
innodb_log_file_size = 512M
　　
innodb_log_files_in_group = 3
　　
innodb_max_dirty_pages_pct = 60
　　
innodb_lock_wait_timeout = 120
　　

　　
[mysqld2]
　　
socket = /data/dbdata_3307/mysql.sock
　　
port = 3307
　　
pid-file = /data/dbdata_3307/3307.pid
　　
datadir = /data/dbdata_3307
　　
user = mysql
　　
skip-name-resolve
　　
lower_case_table_names=1
　　
innodb_file_per_table=1
　　
back_log = 50
　　
max_connections = 300
　　
max_connect_errors = 1000
　　
table_open_cache = 2048
　　
max_allowed_packet = 16M
　　
binlog_cache_size = 2M
　　
max_heap_table_size = 64M
　　
sort_buffer_size = 2M
　　
join_buffer_size = 2M
　　
thread_cache_size = 64
　　
thread_concurrency = 8
　　
query_cache_size = 64M
　　
query_cache_limit = 2M
　　
ft_min_word_len = 4
　　
default-storage-engine = innodb
　　
thread_stack = 192K
　　
transaction_isolation = REPEATABLE-READ
　　
tmp_table_size = 64M
　　
log-bin=mysql-bin
　　
binlog_format=mixed
　　
slow_query_log
　　
long_query_time = 1
　　
server-id = 1
　　
key_buffer_size = 8M
　　
read_buffer_size = 2M
　　
read_rnd_buffer_size = 2M
　　
bulk_insert_buffer_size = 64M
　　
myisam_sort_buffer_size = 128M
　　
myisam_max_sort_file_size = 10G
　　
myisam_repair_threads = 1
　　
myisam_recover
　　
innodb_additional_mem_pool_size = 16M
　　
innodb_buffer_pool_size = 200M
　　
innodb_data_file_path = ibdata1:10M:autoextend
　　
innodb_file_io_threads = 8
　　
innodb_thread_concurrency = 16
　　
innodb_flush_log_at_trx_commit = 1
　　
innodb_log_buffer_size = 16M
　　
innodb_log_file_size = 512M
　　
innodb_log_files_in_group = 3
　　
innodb_max_dirty_pages_pct = 60
　　
innodb_lock_wait_timeout = 120
　　

　　
[mysqld3]
　　
socket = /data/dbdata_3308/mysql.sock
　　
port = 3308
　　
pid-file = /data/dbdata_3308/3308.pid
　　
datadir = /data/dbdata_3308
　　
user = mysql
　　
skip-name-resolve
　　
lower_case_table_names=1
　　
innodb_file_per_table=1
　　
back_log = 50
　　
max_connections = 300
　　
max_connect_errors = 1000
　　
table_open_cache = 2048
　　
max_allowed_packet = 16M
　　
binlog_cache_size = 2M
　　
max_heap_table_size = 64M
　　
sort_buffer_size = 2M
　　
join_buffer_size = 2M
　　
thread_cache_size = 64
　　
thread_concurrency = 8
　　
query_cache_size = 64M
　　
query_cache_limit = 2M
　　
ft_min_word_len = 4
　　
default-storage-engine = innodb
　　
thread_stack = 192K
　　
transaction_isolation = REPEATABLE-READ
　　
tmp_table_size = 64M
　　
log-bin=mysql-bin
　　
binlog_format=mixed
　　
slow_query_log
　　
long_query_time = 1
　　
server-id = 1
　　
key_buffer_size = 8M
　　
read_buffer_size = 2M
　　
read_rnd_buffer_size = 2M
　　
bulk_insert_buffer_size = 64M
　　
myisam_sort_buffer_size = 128M
　　
myisam_max_sort_file_size = 10G
　　
myisam_repair_threads = 1
　　
myisam_recover
　　
innodb_additional_mem_pool_size = 16M
　　
innodb_buffer_pool_size = 200M
　　
innodb_data_file_path = ibdata1:10M:autoextend
　　
innodb_file_io_threads = 8
　　
innodb_thread_concurrency = 16
　　
innodb_flush_log_at_trx_commit = 1
　　
innodb_log_buffer_size = 16M
　　
innodb_log_file_size = 512M
　　
innodb_log_files_in_group = 3
　　
innodb_max_dirty_pages_pct = 60
　　
innodb_lock_wait_timeout = 120
　　

　　
[mysqldump]
　　
quick
　　
max_allowed_packet = 256M
　　

　　
[mysql]
　　
no-auto-rehash
　　
prompt=\\u@\\d \\R:\\m>
　　
[myisamchk]
　　
key_buffer_size = 512M
　　
sort_buffer_size = 512M
　　
read_buffer = 8M
　　
write_buffer = 8M
　　

　　
[mysqlhotcopy]
　　
interactive-timeout
　　
[mysqld_safe]
　　
open-files-limit = 8192