基於mysqld_multi實現MySQL 5.7.24多例項多程式配置

MySQL多例項的原理

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

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

優點如下：

有效利用伺服器資源
當單個伺服器資源過剩時，可以充分利用剩餘的資源來提供更多的服務
節約伺服器資源
當公司資金緊張，但資料庫又需要資料庫之間各自提供服務時，並且還想使用主從同步等技術，此時多例項就再好不過了
方便後期架構擴充套件
當公司的某個專案才啟動時，啟動初期並不一定有很大的使用者量，因此可以先用一組物理資料庫伺服器，在上面部署多個例項，方便後續架構擴充套件、遷移

缺點如下：
資源互相搶佔問題
當某個服務例項併發很高或者有慢查詢時，整個例項會消耗更多的記憶體、CPU和IO資源，這將導致伺服器上的其它例項提供服務的質量下降
mysql 多例項在生產環境下的應用場景！

當一個公司業務訪問量不太大，又想節儉成本，並且還希望不同業務的資料庫服務能夠各自盡量獨立，提供服務能夠互相不受影響。另外還需要應用主從同步等技術來提供資料庫備份或讀寫分離服務，以及方便後期業務量增大時，資料庫架構的擴充套件和遷移。此時，Mysql 多例項就再好不過了。比如，我們可以透過在 3 臺伺服器部署 6-9 個例項，然後交叉做主從同步備份及讀寫分離，來實現 6-9 臺伺服器才能夠達到的效果

公司業務訪問量不是太大的時候，伺服器的資源基本都是過剩狀態。此時就很適合 mysql 多例項的應用。如果對 SQL語句最佳化做的比較好，mysql 多例項是一個很值得去使用的技術。即使後期業務併發很大，只要合理分配好系統資源，也不會有太大的問題

為了規避 mysql 對 SMP 架構不支援的缺陷，我們可以使用 mysql 多例項繫結處理器的辦法（NUMA處理器必須支援，不過現在大部分處理器都支援的）將不同的資料庫分配到不同的例項上提供資料服務；

傳統遊戲行業的 MMO/MMORPG以及Web Game，會將每個服都對應一個資料庫，而且可能經常要做很多資料查詢和資料訂正工作。此時，為了減少維護而出錯的機率，我們也可以採用多例項的部署方式，按區的概念來分配資料庫。

Mysql多例項實現的3種方式

1、基於多配置檔案

透過使用多個配置檔案來啟動不同的程式，以此來實現多例項。

優點：邏輯簡單，配置簡單

缺點：管理起來不方便

2、基於mysqld_multi
透過官方自帶的 mysqld_multi 工具，使用單獨配置檔案來實現多例項

優點： 便於集中管理管理

缺點： 不方便針對每個例項配置進行定製

3、基於IM
使用 MySQL 例項管理器（MYSQLMANAGER），這個方法好像比較好不過也有點複雜

優點：便於集中管理

缺點：耦合度高。IM一掛，例項全掛/
不方便針對每個例項配置進行定製

MySQL本身就可以透過多例項方式執行，只要修改啟動指令碼和配置檔案，把埠、basedir、datadir 資料夾分開後，多個例項的執行就會互不影響。但是這種方式操作起來太過繁雜，所以MySQL官方提供了一個mysqld_multi 的程式來輔助實現多例項操作。

一、建立並初始化資料目錄
幾個例項要分開執行，必然要把資料庫檔案放到不同目錄中，所以第一步是要建立各個例項的資料目錄，這裡假設我們要執行三個例項，埠分別是3306,3307,3308，為了方便維護，我們把資料資料夾也按照埠號來命名：

[root@localhost ~]# mkdir -p /data/mysql/{3306,3307,3308}
[root@localhost ~]# chown mysql:mysql /data/mysql/{3306,3307,3308}
[root@localhost ~]# ls -ld /data/mysql/{3306,3307,3308}
drwxr-xr-x 2 mysql mysql 6 12月  7 16:37 /data/mysql/3306
drwxr-xr-x 2 mysql mysql 6 12月  7 16:37 /data/mysql/3307
drwxr-xr-x 2 mysql mysql 6 12月  7 16:37 /data/mysql/3308

透過配置檔案指定並初始化資料目錄

[root@localhost ~]# cp /etc/my.cnf /data/mysql/3308.cnf
[root@localhost ~]# vim /data/mysql/3308.cnf
[mysqld]
port=3308
datadir=/data/mysql/3308/
socket=/tmp/mysql3308.sock
symbolic-links=0
[mysqld_safe]
log-error=/data/mysql/3308.log
pid-file=/usr/local/mysql/data/3308.pid
[client]
port=3308
socket=/tmp/mysql3308.sock
[root@localhost ~]# mysqld --defaults-file=/data/mysql/3308.cnf --initialize-insecure --user=mysql

初始化完成之後，後續的配置不需要3308.cnf檔案，例項的引數會在my.cnf中集中配置。注意，data目錄在初始化前，必須為空，不然初始化是會報錯

重複上面兩個步驟，把3307、3306目錄初始化好。

[root@localhost ~]# cat /data/mysql/3306.cnf
[mysqld]
port=3306
datadir=/data/mysql/3306/
socket=/tmp/mysql3306.sock
symbolic-links=0
[mysqld_safe]
log-error=/data/mysql/3306.log
pid-file=/usr/local/mysql/data/3306.pid
[client]
port=3306
socket=/tmp/mysql3306.sock
[root@localhost ~]# cat /data/mysql/3307.cnf
[mysqld]
port=3307
datadir=/data/mysql/3307/
socket=/tmp/mysql3307.sock
symbolic-links=0
[mysqld_safe]
log-error=/data/mysql/3307.log
pid-file=/usr/local/mysql/data/3307.pid
[client]
port=3307
socket=/tmp/mysql3307.sock
[root@localhost ~]# mysqld --defaults-file=/data/mysql/3307.cnf --initialize-insecure --user=mysql
[root@localhost ~]# mysqld --defaults-file=/data/mysql/3306.cnf --initialize-insecure --user=mysql

二、配置my.cnf檔案集中管理多個例項

[root@localhost ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@localhost ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
[mysqld1]
port=3306
socket=/tmp/mysql3306.sock
datadir=/data/mysql/3306/
skip-external-locking
log-bin=/data/mysql/3306/mysql-bin
server-id=3306
user=mysql
[mysqld2]
port=3307
socket=/tmp/mysql3307.sock
datadir=/data/mysql/3307/
skip-external-locking
log-bin=/data/mysql/3307/mysql-bin
server-id=3307
user=mysql
[mysqld3]
port=3308
socket=/tmp/mysql3308.sock
datadir=/data/mysql/3308/
skip-external-locking
log-bin=/data/mysql/3308/mysql-bin
server-id=3308
user=mysql
[mysql]
no-auto-rehash

mysqld_multi的配置檔案和一般MySQL配置不同，沒有[mysqld]段，取而代之的是[mysqld1]、[mysqld2]等配置段，每個配置段代表一個MySQL例項。

三、管理多個MySQL例項
1，啟動多個MySQL例項
啟動多個例項時需要一個啟動指令碼，這個指令碼一般在/usr/local/mysql/support-files目錄下的mysqld_multi.server檔案

[root@localhost ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

啟動多個MySQL例項

[root@localhost ~]# /etc/init.d/mysqld_multi start 1-3

啟動三個MySQL例項，注意這裡的數字和my.cnf中的[mysqldN]對應，1-3就是啟動[mysqld1]、[mysqld2]、[mysqld3]配置段的MySQL例項。

[root@localhost ~]# netstat -lnpt | grep -E "3306|3307|3308"
tcp6       0      0 :::3306                 :::*                    LISTEN      2744/mysqld
tcp6       0      0 :::3307                 :::*                    LISTEN      65502/mysqld
tcp6       0      0 :::3308                 :::*                    LISTEN      65499/mysqld

檢視埠，看MySQL有沒有正常啟動，如果沒有啟動或報錯，一般報錯詳細日誌存在各個例項data目錄下的主機名.err檔案中，開啟此檔案查詢錯誤原因，逐步排錯就可以了。

2，關閉多個MySQL例項

[root@localhost ~]# /etc/init.d/mysqld_multi stop 1-3
[root@localhost ~]# netstat -lnpt | grep -E "3306|3307|3308"

也可以使用以下命令：

killall -u mysql 或者 kill -9 the-mysql-pid

四、root賬戶管理
前面初始化資料庫時用的是--initialize-insecure引數，所以我們初始化的資料庫，root賬戶是沒有密碼的，要先改密碼為123456：

mysqladmin -uroot password '123456' -S /tmp/mysql3306.sock
mysqladmin -uroot password '123456' -S /tmp/mysql3307.sock
mysqladmin -uroot password '123456' -S /tmp/mysql3308.sock

或者：

mysqladmin -uroot password '123456' -P3306 -h127.0.0.1
mysqladmin -uroot password '123456' -P3307 -h127.0.0.1
mysqladmin -uroot password '123456' -P3308 -h127.0.0.1

五、連線mysql多例項：

[root@localhost ~]# mysql -u root -p123456 -S /tmp/mysql3306.sock
[root@localhost ~]# mysql -u root -p123456 -S /tmp/mysql3307.sock
[root@localhost ~]# mysql -u root -p123456 -S /tmp/mysql3308.sock