One, what is MySQL multi-instance
The simple thing is to open several different service ports on a single machine (for example: 3306, 3307) and run multiple MySQL service processes that provide their own services through different sockets that listen to different service ports.
These MySQL multiple instances share a set of MySQL installers that use different (and also the same) MY.CNF configuration files, startup programs, and data files. In providing services, multi-instance MySQL logically appears to be separate, and multiple instances are based on the parameters configured in the configuration file to obtain server-related hardware resources.
Second, MySQL Common application scenario
Because the company's business visits are not very large, the server's resources are basically wasted, this time is very suitable for multi-instance applications, if the SQL statement optimization done better, MySQL multi-instance is a very worthy of the use of technology, even if the concurrency is very large, reasonable allocation of system resources, there will not be too much problem.
Three, MySQL multi-instance common configuration scheme
3.1 Installing the MySQL database
For specific installation methods, see compiling the MySQL database installation method.
3.2 Configuring multiple instances
3.2.1 Creating a multi-instance directory
[Email protected] ~]# mkdir-p/data/{3306,3307}/data[[email protected] ~]# tree/data/data├──3306│ └──data└──3307└──data
3.2.2 The Data directory and temporary directory for MySQL users (MySQL user groups and users have been created before MySQL is installed)
[[email protected] ~]# chown-r mysql.mysql/data[[email protected] ~]# chmod-r 1777/tmp[[email protected] ~]# ls-ld/d Ata/{3306,3307}/data Drwxr-xr-x. 2 mysql mysql 4096 January 22:40/data/3306/data drwxr-xr-x. 2 mysql mysql 4096 January 22:40/data/3307/data
3.2.3 Creating a configuration file my.cnf
3306 ports
|
3307 ports
|
[Client]
Port = 3306
Socket =/data/3306/mysql.sock
[MySQL]
No-auto-rehash
[Mysqld]
user = MySQL
Port = 3306
Socket =/data/3306/mysql.sock
Basedir =/application/mysql
DataDir =/data/3306/data
Open_files_limit = 1024
Back_log = 600
Max_connections = 800
Max_connect_errors = 3000
Table_cache = 614
external-locking = FALSE
Max_allowed_packet =8m
Sort_buffer_size = 1M
Join_buffer_size = 1M
thread_cache_size = 100
Thread_concurrency = 2
Query_cache_size = 2M
Query_cache_limit = 1M
Query_cache_min_res_unit = 2k
#default_table_type = InnoDB
Thread_stack = 192K
#transaction_isolation = read-committed
Tmp_table_size = 2M
Max_heap_table_size = 2M
Long_query_time = 1
#log_long_format
#log-error =/data/3306/error.log
#log-slow-queries =/data/3306/slow.log
Pid-file =/data/3306/mysql.pid
Log-bin =/data/3306/mysql-bin
Relay-log =/data/3306/relay-bin
Relay-log-info-file =/data/3306/relay-log.info
Binlog_cache_size = 1M
Max_binlog_cache_size = 1M
Max_binlog_size = 2M
Expire_logs_days = 7
Key_buffer_size = 16M
Read_buffer_size = 1M
Read_rnd_buffer_size = 1M
Bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
Lower_case_table_names = 1
Skip-name-resolve
Slave-skip-errors = 1032,1062
Replicate-ignore-db=mysql Server-id = 1 Innodb_additional_mem_pool_size = 4M
Innodb_buffer_pool_size = 32M
Innodb_data_file_path = Ibdata1:128m:autoextend
Innodb_file_io_threads = 4
Innodb_thread_concurrency = 8
Innodb_flush_log_at_trx_commit = 2
Innodb_log_buffer_size = 2M
Innodb_log_file_size = 4M
Innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
Innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[Mysqldump]
Quick
Max_allowed_packet = 2M
[Mysqld_safe]
Log-error=/data/3306/err_mysql_3306.err
Pid-file=/data/3306/mysqld.pid |
[Client]
Port = 3307
Socket =/data/3307/mysql.sock
[MySQL]
No-auto-rehash
[Mysqld]
user = MySQL
Port = 3307
Socket =/data/3307/mysql.sock
Basedir =/application/mysql
DataDir =/data/3307/data
Open_files_limit = 1024
Back_log = 600
Max_connections = 800
Max_connect_errors = 3000
Table_cache = 614
external-locking = FALSE
Max_allowed_packet =8m
Sort_buffer_size = 1M
Join_buffer_size = 1M
thread_cache_size = 100
Thread_concurrency = 2
Query_cache_size = 2M
Query_cache_limit = 1M
Query_cache_min_res_unit = 2k
#default_table_type = InnoDB
Thread_stack = 192K
#transaction_isolation = read-committed
Tmp_table_size = 2M
Max_heap_table_size = 2M
#long_query_time = 1
#log_long_format
#log-error =/data/3307/error.log
#log-slow-queries =/data/3307/slow.log
Pid-file =/data/3307/mysql.pid
#log-bin =/data/3307/mysql-bin
Relay-log =/data/3307/relay-bin
Relay-log-info-file =/data/3307/relay-log.info
Binlog_cache_size = 1M
Max_binlog_cache_size = 1M
Max_binlog_size = 2M
Expire_logs_days = 7
Key_buffer_size = 16M
Read_buffer_size = 1M
Read_rnd_buffer_size = 1M
Bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
Lower_case_table_names = 1
Skip-name-resolve
Slave-skip-errors = 1032,1062
Replicate-ignore-db=mysql Server-id = 3 Innodb_additional_mem_pool_size = 4M
Innodb_buffer_pool_size = 32M
Innodb_data_file_path = Ibdata1:128m:autoextend
Innodb_file_io_threads = 4
Innodb_thread_concurrency = 8
Innodb_flush_log_at_trx_commit = 2
Innodb_log_buffer_size = 2M
Innodb_log_file_size = 4M
Innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
Innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[Mysqldump]
Quick
Max_allowed_packet = 2M
[Mysqld_safe]
Log-error=/data/3307/err_mysql_3307.err
Pid-file=/data/3307/mysqld.pid |
Configuring multiple instances of MySQL database
