MySQL multi-instance configuration file

#++++++++++++++++++++++++++++++++ Multi ++++++++++++++++++++++++++

[Mysqld_multi]

Mysqld =/usr/local/mysqlinstance/bin/mysqld_safe

Mysqladmin =/usr/local/mysqlinstance/bin/mysqladmin

Use = root

Log =/usr/local/mysqlinstance/logs/multi.log

#+++++++++++++++++++++++++++++++client+++++++++++++++++++++++++++

[Client]

Default-character-set = UTF8

#+++++++++++++++++++++++++++++mysql+++++++++++++++++++++++++++++

[MySQL]

prompt = MySQL (\\[email protected]\h-\r:\\m:\\s [\\d]) >

#提示当前数据库操作用户名, library name, time, etc.

Default-character-set=utf8

#++++++++++++++++++++++++++++++++++iapppay++++++++++++++++++++++++++

[mysqld3307]

user = MySQL

Port = 3307

Character-set-server=utf8

Socket =/data1/iapppay/mysql.sock

Pid-file =/data1/iapppay/mysql.pid

DataDir =/data1/iapppay/data

Tmpdir =/data1/iapppay/tmpdir

Big_tables

Skip_external_locking

Skip-name-resolve

Lower_case_table_names = 1

Back_log = 100

#default-storage-engine = INNODB

Max_connections = 3000

Max_connect_errors = 100000

Interactive_timeout = 28800

Wait_timeout = 28800

Max_allowed_packet = 32M

Max_heap_table_size = 128M

Tmp_table_size = 128M

Max_length_for_sort_data = 4096

Net_buffer_length = 8K

Sort_buffer_size = 8M

Join_buffer_size = 1M

Read_buffer_size = 1M

Read_rnd_buffer_size = 2M

Table_cache = 1024

Thread_cache_size = 64

Thread_concurrency = 8

Query_cache_type = 0

Query_cache_size = 64M

Query_cache_limit = 1M

#******************************* Logs Related Settings ***************************

Log-error =/data1/iapppay/logs/error.log

Log_warnings

Long_query_time = 1

Slow_query_log

Slow_query_log_file =/data1/iapppay/logs/slow-query.log

Log_queries_not_using_indexes

Binlog_cache_size = 8M

Max_binlog_size = 512M

Log-bin =/data1/iapppay/binlog/mysql-bin

Log-bin-index =/data1/iapppay/binlog/mysql-bin.index

Expire_logs_days = 30

#******************************* Replication Related Settings **********************

Server-id = 1000

Report_port = 3309

Report_user = Repl

Slave_net_timeout = 60

Innodb_flush_log_at_trx_commit = 1

Sync_binlog = 1

Binlog-format = ROW

Transaction_isolation = Repeatable-read

Slave-skip-errors = 1062

#******************************* MyISAM Specific Options ****************************

Key_buffer_size = 32M

Bulk_insert_buffer_size = 16M

Myisam_sort_buffer_size = 64M

Myisam_max_sort_file_size = 1G

Myisam_repair_threads = 1

Myisam_recover

#***************************** INNODB Specific Options ******************************

innodb_file_per_table = 1

Innodb_autoinc_lock_mode = 1

Innodb_fast_shutdown = 0

Innodb_additional_mem_pool_size = 64M

Innodb_buffer_pool_size = 6G

Innodb_data_home_dir =/data1/iapppay/data

Innodb_data_file_path = Ibdata1:256m:autoextend

Innodb_file_io_threads = 4

Innodb_thread_concurrency = 4

Innodb_log_buffer_size = 8M

Innodb_log_file_size = 128M

Innodb_log_group_home_dir =/data1/iapppay/logs/innodb_log

innodb_max_dirty_pages_pct = 20

Innodb_lock_wait_timeout = 120

Innodb_flush_method=o_direct

#++++++++++++++++++++++++++++++++++lenovo++++++++++++++++++++++++++

[mysqld3310]

user = MySQL

Port = 3310

Socket =/usr/local/mysqlinstance/data/lenovo/mysql.sock

Pid-file =/usr/local/mysqlinstance/data/lenovo/mysql.pid

DataDir =/usr/local/mysqlinstance/data/lenovo/data

Tmpdir =/usr/local/mysqlinstance/data/lenovo/tmpdir

Big_tables

Skip_external_locking

Skip-name-resolve

Lower_case_table_names = 1

Back_log = 100

#default-storage-engine = INNODB

Max_connections = 3000

Max_connect_errors = 100000

Interactive_timeout = 28800

Wait_timeout = 28800

Max_allowed_packet = 32M

Max_heap_table_size = 128M

Tmp_table_size = 128M

Max_length_for_sort_data = 4096

Net_buffer_length = 8K

Sort_buffer_size = 8M

Join_buffer_size = 1M

Read_buffer_size = 1M

Read_rnd_buffer_size = 2M

Table_cache = 1024

Thread_cache_size = 64

Thread_concurrency = 8

Query_cache_type = 0

Query_cache_size = 64M

Query_cache_limit = 1M

#******************************* Logs Related Settings ***************************

Log-error =/usr/local/mysqlinstance/data/lenovo/logs/error.log

Log_warnings

Long_query_time = 1

Slow_query_log

Slow_query_log_file =/usr/local/mysqlinstance/data/lenovo/logs/slow-query.log

Log_queries_not_using_indexes

Binlog_cache_size = 8M

Max_binlog_size = 512M

Log-bin =/usr/local/mysqlinstance/data/lenovo/binlog/mysql-bin

Log-bin-index =/usr/local/mysqlinstance/data/lenovo/binlog/mysql-bin.index

Expire_logs_days = 30

#******************************* Replication Related Settings **********************

Server-id = 1010

Report_port = 3310

Report_user = Repl

Slave_net_timeout = 60

Innodb_flush_log_at_trx_commit = 1

Sync_binlog = 1

Binlog-format = ROW

Transaction_isolation = Repeatable-read

Slave-skip-errors = 1062

#******************************* MyISAM Specific Options ****************************

Key_buffer_size = 32M

Bulk_insert_buffer_size = 16M

Myisam_sort_buffer_size = 64M

Myisam_max_sort_file_size = 1G

Myisam_repair_threads = 1

Myisam_recover

#***************************** INNODB Specific Options ******************************

innodb_file_per_table = 1

Innodb_autoinc_lock_mode = 1

Innodb_fast_shutdown = 0

Innodb_additional_mem_pool_size = 64M

Innodb_buffer_pool_size = 8G

Innodb_data_home_dir =/usr/local/mysqlinstance/data/lenovo/data

Innodb_data_file_path = Ibdata1:256m:autoextend

Innodb_file_io_threads = 4

Innodb_thread_concurrency = 4

Innodb_log_buffer_size = 8M

Innodb_log_file_size = 128M

Innodb_log_group_home_dir =/usr/local/mysqlinstance/data/lenovo/logs/innodb_log

innodb_max_dirty_pages_pct = 20

Innodb_lock_wait_timeout = 120

Innodb_flush_method=o_direct

#++++++++++++++++++++++++++++++++++++++++mysqldump++++++++++++++++++++++

[Mysqldump]

Quick

Max_allowed_packet = 2G

This article is from the "My Ops Time" blog, so be sure to keep this source http://aaronsa.blog.51cto.com/5157083/1741501

MySQL multi-instance configuration file