mysql多執行個體

標籤：client   系統   efault   web   方法   director   實現   epo   code   

mysql多執行個體

mysql多執行個體，簡單理解就是在一台伺服器上，mysql服務開啟多個不同的連接埠（如3306、3307），運行多個服務進程。這些 mysql 服務進程通過不同的 socket來監聽不同的資料連接埠，進而互不干涉的提供各自的服務。

在同一台伺服器上，mysql 多執行個體會去共用一套 mysql 應用程式，因此你在部署 mysql 的時候只需要部署一次 mysql程式即可，無需多次部署。但是，mysql多執行個體之間會各自使用不同的 my.cnf 設定檔、啟動程式和資料檔案。在提供服務方面，mysql多執行個體在邏輯上看起來是各自獨立，互不干涉的，並且多個執行個體之間是根據設定檔的設定值，來擷取相關伺服器的硬體資源。

.

優點如下：
1、有效利用伺服器資源
當單個伺服器資源過剩時，可以充分利用剩餘的資源來提供更多的服務
2、節約伺服器資源
當公司資金緊張，但資料庫又需要資料庫之間各自提供服務時，並且還想使用主從同步等技術，此時多執行個體就再好不過了
3、方便後期架構擴充
當公司的某個項目才啟動時，啟動初期並不一定有很大的使用者量，因此可以先用一組物理資料庫伺服器，在上面部署多個執行個體，方便後續架構擴充、遷移
缺點如下：
1、資源互相搶佔問題
當某個服務執行個體並發很高或者有慢查詢時，整個執行個體會消耗更多的記憶體、CPU和IO資源，這將導致伺服器上的其它執行個體提供服務的品質下降。這就比如說合租房的各個租客，每當早晨上班時，都會洗漱，此時衛生間的佔用率就大，各個租客總會發生等待。
多執行個體的應用情境
1、當一個公司業務訪問量不太大，又想節儉成本，並且還希望不同業務的資料庫服務能夠各自盡量獨立，提供服務能夠互相不受影響。另外還需要應用主從同步等技術來提供Database Backup或讀寫分離服務，以及方便後期業務量增大時，資料庫結構描述的擴充和遷移。此時，Mysql 多執行個體就再好不過了。比如，我們可以通過在 3 台伺服器部署 6-9 個執行個體，然後交叉做主從同步備份及讀寫分離，來實現 6-9 台伺服器才能夠達到的效果
2、公司業務訪問量不是太大的時候，伺服器的資源基本都是過剩狀態。此時就很適合 mysql 多執行個體的應用。如果對 SQL語句 最佳化做的比較好，mysql 多執行個體 是一個很值得去使用的技術。即使後期業務並發很大，只要合理分配好系統資源，也不會有太大的問題
3、為了規避 mysql 對 SMP 架構不支援的缺陷，我們可以使用 mysql 多執行個體綁定處理器的辦法（NUMA處理器必須支援，不過現在大部分處理器都支援的）將不同的資料庫分配到不同的執行個體上提供資料服務；
4、傳統遊戲行業的 MMO/MMORPG以及Web Game，會將每個服都對應一個資料庫，而且可能經常要做很多資料查詢和資料訂正工作。此時，為了減少維護而出錯的機率，我們也可以採用多執行個體的部署方式，按區的概念來分配資料庫。

.

Mysql多執行個體實現的3種方式
mysql 多執行個體常規來講，有三種方案可以實現，這三種方案各有利弊，如下：
1、基於多設定檔
通過使用多個設定檔來啟動不同的進程，以此來實現多執行個體。
優點：邏輯簡單，配置簡單
缺點：管理起來不方便
2、基於mysqld_multi
通過官方內建的 mysqld_multi 工具，使用單獨設定檔來實現多執行個體
優點：?便於集中管理管理
缺點：?不方便針對每個執行個體配置進行定製
3、基於IM
使用 MySQL 執行個體管理器（MYSQLMANAGER），這個方法好像比較好不過也有點複雜
優點：便於集中管理
缺點：耦合度高。IM一掛，執行個體全掛
不方便針對每個執行個體配置進行定製

環境 [ 關閉SeLinux ]
[[email protected] ~]# cat /etc/RedHat-release
CentOS release 6.9 (Final)

[[email protected] ~]# uname -r
2.6.32-696.3.2.el6.x86_64

[[email protected] ~]# getenforce
Disabled
注意：如已安裝mysql 則有些部分是不用做的，所以這裡反白出來的是配置的多執行個體部分，第一部分和第二部分用------分割
部署 [ 4個執行個體 ]

• 下載 MySQL 5.7 二制包 [ 推薦官方下載 ]　此下載版本大於5.7.5
  [[email protected] ~]# wget wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

• 解壓 MySQL 5.7 二進位包到指定目錄
  [[email protected] ~]# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

• 建立 MySQL 軟連結
  [[email protected] ~]# ln -s /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64 /usr/local/mysql

• 建立 MySQL 使用者
  [[email protected] ~]# useradd -r -s /sbin/nologin mysql

• 在 MySQL 二進位包目錄中建立 mysql-files 目錄 [MySQL 資料匯入/匯出資料專放目錄]
  [[email protected] ~]# mkdir -v /usr/local/mysql/mysql-files
  mkdir: created directory `/usr/local/mysql/mysql-files‘

• 建立多執行個體資料目錄
  [[email protected] ~]# mkdir -vp /data/mysql_data{1..4}
  mkdir: created directory /data‘ <br/>mkdir: created directory/data/mysql_data1‘
  mkdir: created directory `/data/mysql_data2‘

mkdir: created directory /data/mysql_data3‘ <br/>mkdir: created directory/data/mysql_data4‘

• 修改 MySQL 二進位包目錄的所屬使用者與所屬組
  1 [[email protected] ~]# chown root.mysql -R /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64

• 修改 MySQL 多執行個體資料目錄與 資料匯入/匯出專放目錄的所屬使用者與所屬組

  [[email protected] ~]# chown mysql.mysql -R /usr/local/mysql/mysql-files /data/mysql_data{1..4}

• 配置 MySQL 設定檔 /etc/my.cnf
  [mysqld_multi]
  mysqld = /usr/local/mysql/bin/mysqld
  mysqladmin = /usr/local/mysql/bin/mysqladmin
  log = /tmp/mysql_multi.log

[mysqld1]
設定資料目錄　[多執行個體中一定要不同]
datadir = /data/mysql_data1
設定sock存放檔案名稱　[多執行個體中一定要不同]
socket = /tmp/mysql.sock1
設定監聽開放連接埠　[多執行個體中一定要不同]
port = 3306
設定運行使用者
user = mysql
關閉監控
performance_schema = off
設定innodb 緩衝大小
innodb_buffer_pool_size = 32M
設定監聽IP地址
bind_address = 0.0.0.0
關閉DNS 反向解析
skip-name-resolve = 0

[mysqld2]
datadir = /data/mysql_data2
socket = /tmp/mysql.sock2
port = 3307
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0

[mysqld3]
datadir = /data/mysql_data3
socket = /tmp/mysql.sock3
port = 3308
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0

[mysqld4]
datadir = /data/mysql_data4
socket = /tmp/mysql.sock4
port = 3309
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0

• 初始化各個執行個體 [ 初始化完成後會內建隨機密碼在輸出日誌中 ]
  [[email protected] ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1
  [[email protected] ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2
  [[email protected] ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3
  [[email protected] ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data4

• 各執行個體開啟 SSL 串連
  [[email protected] ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1
  [[email protected] ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2
  [[email protected] ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3
  [[email protected] ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data4

• 複製多執行個體指令碼到服務管理目錄下 [ /etc/init.d/ ]
  [[email protected] ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

• 添加指令碼執行許可權
  [[email protected] ~]# chmod +x /etc/init.d/mysqld_multi

• 添加進service服務管理
  [[email protected] ~]# chkconfig --add mysqld_multi

5. 啟動測試

   • 查個多執行個體狀態
     [[email protected] ~]# /etc/init.d/mysqld_multi report
     Reporting MySQL servers
     MySQL server from group: mysqld1 is not running
     MySQL server from group: mysqld2 is not running
     MySQL server from group: mysqld3 is not running
     MySQL server from group: mysqld4 is not running

• 啟動多執行個體

  [[email protected] ~]# /etc/init.d/mysqld_multi start

• 查看多執行個體狀態
  Reporting MySQL servers
  MySQL server from group: mysqld1 is running
  MySQL server from group: mysqld2 is running
  MySQL server from group: mysqld3 is running
  MySQL server from group: mysqld4 is running

• 查看執行個體監聽連接埠
  [[email protected] ~]# netstat -lntp | grep mysqld
  tcp 0 0 0.0.0.0:3306 0.0.0.0: LISTEN 2673/mysqld
  tcp 0 0 0.0.0.0:3307 0.0.0.0: LISTEN 2676/mysqld
  tcp 0 0 0.0.0.0:3308 0.0.0.0: LISTEN 2679/mysqld
  tcp 0 0 0.0.0.0:3309 0.0.0.0: LISTEN 2682/mysqld

串連
　 * 執行個體1

[[email protected] ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql.sock1 -p‘z+Ilo*>s:3kw‘
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.18

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> set password = ‘123456‘;
Query OK, 0 rows affected (0.00 sec)

每個執行個體進行獨立配置
mysql安裝路徑: /usr/local/mysql
mysql資料路徑: /usr/local/mysql/data
mysql連接埠號碼: 3306
mysql 版本：5.7. Source distribution
環境：Centos 7 (x86_64)

初始化執行個體
#為新執行個體建立資料目錄並賦權

mkdir -p  /usr/local/mysql/data3307mkdir -p  /usr/local/mysql/data3308chown mysql:mysql -R  /usr/local/mysql/data3307

chown mysql:mysql -R /usr/local/mysql/data3308

#初始化執行個體

suse11:~ # cd /usr/local/mysql
[[email protected] ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/3307/data
[[email protected] ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/3308/data

配置各執行個體的my.cnf檔案這裡只舉例一個

vi //usr/local/mysql/3307/my3307.cnf

[client]
socket=/usr/local/mysql/mysql7.sock
port=3307
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/3307/data
user=mysql
log-error=/usr/local/mysql/3307/data/mysql.err
pid-file=/usr/local/mysql/3307/data/mysqld.pid
socket=/usr/local/mysql/mysql7.sock
port=3307
[mysql]
no-auto-rehash
socket = /usr/local/mysql/mysql7.sock

chown -R mysql:mysql //usr/local/mysql/3307/data/my3307.cnf
chown -R mysql:mysql /usr/local/mysql/3307/data/my3308.cnf

啟動關閉多執行個體

suse11:~ # mysqld_safe --defaults-file=/usr/local/mysql/3307/my3307.cnf >> /dev/null
suse11:~ # mysqld_safe --defaults-file=/usr/local/mysql/3307/my3308.cnf >> /dev/null

suse11:~ # netstat -nltp|grep mysql
tcp 0 0 :::3307 ::: LISTEN 64277/mysqld
tcp 0 0 :::3308 ::: LISTEN 64597/mysqld

Grep password /usr/local/mysql/data/mysql.err
將臨時密碼cp到-p後面

#下面使用通訊端方式串連到執行個體
mysql -uroot -p’v98hasg2.’ -S /usr/local/mysql/mysql7.sock
[email protected][(none)]>

#下面使用TCP方式串連到執行個體
suse11:~ # mysql -uroot -pxxx -P3606 --protocol=tcp
[email protected][(none)]> exit

suse11:~ # mysql -uroot -pxxx -P3706 --protocol=tcp
[email protected][(none)]>

#關閉mysql執行個體
mysqladmin -uroot -pxxx -S/usr/local/mysql/mysql7.sock shutdown
mysqladmin -uroot -pxxx -S /usr/local/mysql/mysql7.sock shutdown

mysql多執行個體