mysql-多執行個體

標籤：bin   建立   creat   sort   date   action   路徑   amp   query   

一、MySQL 多執行個體1、簡介

什麼使多執行個體呢？

多執行個體就是在一台機器上開啟多個不同的服務連接埠（如：3306,3307）；

運行多個MySQL服務進程，這些進程通過不同的socket監聽不同的服務連接埠來提供各自的服務。

MySQL多執行個體共用一個 MySQL 的安裝程式，使用不同（也可相同）的 my.cnf 設定檔，啟動程式和資料檔案。

在提供服務時，多執行個體 MySQL 在邏輯上是各自獨立的，多個執行個體的本身是根據設定檔對應的設定值，來獲得伺服器的相關硬體資源多少。

2、安裝配置前操作

安裝配置前的一些配置少不了

檔案的目錄最好也規矩，這樣才專業

/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(id, 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 = 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/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  ### 設定檔[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_buffer_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=‘3000‘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"                               ###啟動指令碼 可以放在/etc/rc.local下 開機自啟

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

####mysql關閉

killall mysqld  不建議這樣操作

mysqladmin -u mysql -P 3002 -S /data/ops/app/mysql-5.6.23/3307/3307.sock shutdown            ###建議這樣關閉資料庫

 

增加一個mysql執行個體

問題又來了，要增加一個執行個體怎麼弄呢？

其實不難，只需按上面步驟，建立目錄，在修改相應設定檔，初始化就ok

 

mysql-多執行個體