mysql-Multi-instance

First, MySQL multi-instance 1, Introduction

What makes multiple instances?

Multi-instance is to open a number of different service ports on a single machine (for example: 3306,3307);

Run multiple MySQL service processes that provide their own services through different sockets that listen to different service ports.

MySQL multiple instances share a MySQL installer that uses different (and also the same) MY.CNF configuration files to launch programs and data files.

In the provision of services, multi-instance MySQL is logically independent, multiple instances of itself is based on the configuration file corresponding to the set value, to obtain the relevant hardware resources of the server.

2. Operation before installation and configuration

Some configurations before installing the configuration are indispensable

The directory of the file is best also the rules, so professional

/DATA/OPS/APP/MYSQL-5.6.23/# # #mysql的安装路径mkdir/data/ops/app/mysql-5.6.23/{3306,3307}vim/data/ops/app/ Mysql-5.6.23/etc/3306.cnf[client]character-set-server = Utf8[mysql]character-set-server = utf8[mysqld]# Bind-address=127.0.0.1character-set-server = utf8datadir=/data/ops/app/mysql-5.6.23/3306socket=/data/ops/app/ mysql-5.6.23/3306/3306.sockuser=mysqlskip-grant-tablessymbolic-links=0skip-name-resolve#init-connect= ' Insert Into Accesslog.accesslog (IDs, Time, LocalName, MatchName) VALUES (connection_id (), now (), User (), current_user ()); ' Slow_query_log = 1long_query_time = 4#slow-query-log-file =/data/ops/app/mysql-5.6.23/logs/3306/3306_ slow.loglog-queries-not-using-indexes = Falselower-case-table-names = 1server-id = 1max_connections =5000max_allowed_ Packet = 16mconnect_timeout = 60log-slave-updateslog-bin = 3306-binexpire_logs_days = 7slave-skip-errors = 1062#binlog_ do_db =back_log = 200max_connect_errors = 10table_open_cache = 10240binlog_cache_size = 1Mmax_heap_table_size = 64Msort_ BuFfer_size = 4mjoin_buffer_size = 4mthread_cache_size = 8thread_concurrency = 8query_cache_size = 512Mquery_cache_limit = 2 Mft_min_word_len = 4thread_stack = 192ktransaction_isolation = Repeatable-readtmp_table_size = 64Mbinlog_format = Mixedlog-short-formatkey_buffer_size = 512mread_buffer_size = 16mread_rnd_buffer_size = 16Mbulk_insert_buffer_size = 64minnodb_file_per_tableinnodb_additional_mem_pool_size = 100minnodb_buffer_pool_size = 1Ginnodb_data_file_path = Ibdata1:12m;ibdata2:12m:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_ commit = 2innodb_log_buffer_size = 8minnodb_log_file_size = 256minnodb_log_files_in_group = 3innodb_max_dirty_pages_ pct = 90innodb_lock_wait_timeout = 120slave-skip-errors = 1062[mysqld_safe]log-error=/data/ops/app/mysql-5.6.23/logs /3306/3306.logpid-file=/data/ops/app/mysql-5.6.23/3306/3306.pid

VIM/DATA/OPS/APP/MYSQL-5.6.23/ETC/3307.CNF # # # config file [client]character-set-server = Utf8[mysql]character-set-server = UTF8[MYSQLD] #bind-address=127.0.0.1 character-set-server = utf8datadir=/data/ops/app/mysql-5.6.23/3307socket=/ data/ops/app/mysql-5.6.23/3307/3307.sockuser=mysqlskip-grant-tablessymbolic-links=0skip-name-resolve# init-connect= ' INSERT into Accesslog.accesslog (ID, time, localname, MatchName) VALUES (connection_id (), now (), User (), Current_User ()); ' Slow_query_log = 1long_query_time = 4slow-query-log-file =/data/ops/app/mysql-5.6.23/logs/3307/3307_ slow.loglog-queries-not-using-indexes = Falselower-case-table-names = 1server-id = 1max_connections =5000max_allowed_ Packet = 16mconnect_timeout = 60log-slave-updateslog-bin = 3307-binexpire_logs_days = 7slave-skip-errors = 1062#binlog_ do_db =back_log = 200max_connect_errors = 10table_open_cache = 10240binlog_cache_size = 1Mmax_heap_table_size = 64Msort_b Uffer_size = 4mjoin_buffer_size = 4mthread_cache_size = 8thread_concurrency = 8qUery_cache_size = 512mquery_cache_limit = 2mft_min_word_len = 4thread_stack = 192ktransaction_isolation = Repeatable-readtmp_table_size = 64mbinlog_format = Mixedlog-short-formatkey_buffer_size = 512Mread_buffer_size = 16mread_rnd_buffer_size = 16mbulk_insert_buffer_size = 64minnodb_file_per_tableinnodb_additional_mem_pool_size = 100minnodb_buffer_pool_size = 1ginnodb_data_file_path = Ibdata1:12m;ibdata2:12m:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 8Minnodb_log_file_size = 256minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120slave-skip-errors = 1062 [mysqld_safe]log-error=/data/ops/app/mysql-5.6.23/logs/3307/3307.logpid-file=/data/ops/app/mysql-5.6.23/3307/ 3307.pid

# # #创建初始化脚本以及启动文件

vim/data/ops/app/mysql-5.6.23/scripts/init/3306.sh#/bin/bash# 3306.sh can create a 3306 instance# create Directoriesif [!-d '/data/ops/app/mysql-5.6.23/3306 '];then  mkdir-p '/data/ops/app/mysql-5.6.23/3306 '; FIIF [!-d '/data/ops/ app/mysql-5.6.23/logs/3306 '];then  mkdir-p '/data/ops/app/mysql-5.6.23/logs/3306 '; fi# set envconf= "/data/ops/ App/mysql-5.6.23/etc/3306.cnf "basedir="/data/ops/app/mysql-5.6.23 "datadir="/data/ops/app/mysql-5.6.23/3306 "pid = "/data/ops/app/mysql-5.6.23/3306/3306.pid" sock= "/data/ops/app/mysql-5.6.23/3306/3306.sock" port= ' user= ' MySQL ' # Create instance#/data/ops/app/mysql-5.6.23/scripts/mysql_install_db--user= $user--basedir= $basedir-- datadir= $datadir # boot Instance/data/ops/app/mysql-5.6.23/bin/mysqld_safe--defaults-file= $conf--user= $user-- basedir= $basedir--datadir= $datadir--port= $port--pid-file= $pid--socket= $sock &

vim/data/ops/app/mysql-5.6.23/scripts/init/3307.sh#/bin/bash# 3307.sh can create a 3307 instance# create Directoriesif [!-d '/data/ops/app/mysql-5.6.23/3307 '];then  mkdir-p '/data/ops/app/mysql-5.6.23/3307 '; FIIF [!-d '/data/ops/ app/mysql-5.6.23/logs/3307 '];then  mkdir-p '/data/ops/app/mysql-5.6.23/logs/3307 '; fi# set envconf= "/data/ops/ App/mysql-5.6.23/etc/3307.cnf "basedir="/data/ops/app/mysql-5.6.23 "datadir="/data/ops/app/mysql-5.6.23/3307 "pid = "/data/ops/app/mysql-5.6.23/3307/3307.pid" sock= "/data/ops/app/mysql-5.6.23/3307/3307.sock" port= ' 3002 ' user= ' MySQL ' # Create instance#/data/ops/app/mysql-5.6.23/scripts/mysql_install_db--user= $user--basedir= $basedir-- datadir= $datadir # boot Instance/data/ops/app/mysql-5.6.23/bin/mysqld_safe--defaults-file= $conf--user= $user-- basedir= $basedir--datadir= $datadir--port= $port--pid-file= $pid--socket= $sock &

# # # #注意初始化脚本完毕以后mysql才能启动

Su--shell=/bin/bash mysql-c "/data/ops/app/mysql-5.6.23/scripts/init/3306.sh" # # #启动脚本 can be placed in /etc/rc.local power-on self-booting

Su--shell=/bin/bash mysql-c "/data/ops/app/mysql-5.6.23/scripts/init/3307.sh"

# # #mysql关闭

Killall MYSQLD does not recommend this action

Mysqladmin-u mysql-p 3002-s/data/ops/app/mysql-5.6.23/3307/3307.sock shutdown # # #建议这样关闭数据库

Add a MySQL instance

The question is again, how to add an example?

In fact, just follow the steps above, create the directory, modify the corresponding configuration file, initialize it OK

mysql-Multi-instance