mysql多實例的應用方法是什麼

Mysql多實例就是在一台伺服器上同時開啟多個不同的服務端口，同時執行多個Mysql服務進程，這些服務進程透過不同的socket監聽不同的服務端口來提供服務。使用Mysql多執行個體可以：1. 最大限度地利用伺服器資源；2. 節省伺服器資源的利用；3. 方便未來架構擴充。

1、什麼是MySQL多實例

簡單來說，Mysql多實例就是在一台伺服器上同時開啟多個不同的服務連接埠（ 3306、3307），同時執行多個Mysql服務進程，這些服務進程透過不同的socket監聽不同的服務連接埠來提供服務。

這些Mysql多實例公用一套Mysql安裝程序，使用不同的my.cnf（也可以相同）設定文件，啟動程序（也可以相同）和資料檔。在提供服務時，多實例 Mysql在邏輯上看來是各自獨立的，它們根據設定檔的對應設定值，獲得伺服器對應數量的硬體資源。

打個比方，Mysql多實例就相當於房子的多個臥室，每個實例可以看作一間臥室，整個伺服器就是一間房子，伺服器的硬體資源（cpu、mem、disk） 、軟體資源（centos操作系統）可以看作房子的衛浴、客廳，是房子的公用資源。

2、多實例的作用與問題

Mysql多實例作用：

• 有效利用伺服器資源

  當單一伺服器資源有剩餘時，可以充分利用剩餘的資源提供更多的服務，並且可以實現資源的邏輯隔離。

• 節約伺服器資源

  多實例是最佳解決方案，當公司資金不足時，資料庫需要獨立提供服務並且需要主從複製技術。

• 方便後期架構擴展

  當公司的某個專案才啟動時，啟動初期不一定有很大的使用者量，因此可以用一組物理資料庫伺服器，在上面部署多個實例，方便後續擴充、遷移

Mysql多實例有它的好處，但也有弊端，例如會存在資源互相搶佔的問題。

當某個資料庫實例並發很高或有SQL慢查詢時，整個實例會消耗大量的系統CPU、磁碟I/O等資源，導致伺服器上的其他資料庫執行個體提供服務的品質一起下降。不同實例所取得的資源是相互獨立的，無法像虛擬化一樣完全隔離。

3、Mysql多實例應用場景

1）、資金緊張型公司的選擇

若公司資金緊張，公司業務訪問量又不是太大，但有希望不同業務的資料庫服務各自盡量獨立的提供服務而互相不受影響，同時，還需要主從複製等技術提供備份或讀寫分離服務，那麼多實例就再好不過了，例如：可以3台伺服器部署9~15個實例，交叉做主從複製、資料備份及讀寫分離，這樣就可達到9~15台伺服器每個只裝一個資料庫才有的效果，這裡要強調的是所謂的盡量獨立是相對的。

2）、並發存取不是特別大的業務

當公司業務訪問量不太大的時候，伺服器的資源基本上都是浪費的，這時就很適合多實例的應用，如果對SQL語句的優化做的比較好，Mysql多實例會是一個很值得使用的技術，即使並發很大，合理的分配好系統資源以及搭配好服務，也不會有太大問題。

3）、入口網站應用程式Mysql多實例場景

入口網站通常都會使用多實例，因為配置硬體好的伺服器，可以節省IDC機櫃空間，同時跑多實例也會減少硬體資源跑不滿的浪費。例如，百度公司的許多資料庫都是多實例，不過一般是從庫多實例。例如某部門使用的IBM伺服器為48核心CPU，記憶體96GB，一台伺服器排3-4個實例，此外，新浪網也是多實例，記憶體48GB左右。

說明：新浪的資料庫單機1-4個資料庫實例的居多。其中又數1~2個的最多，因為大業務的機器比較多。伺服器是DELL R510的居多，CPU是E5210，48GB內存，磁碟是 12 *300GB SAS，做的RAID10。

4、Mysql多實例常見的設定方案

4.1、單一的設定檔、單一啟動程式多實例部署方案

Mysql官方文檔提到的單一設定檔、單一啟動程式多執行個體部署方案，不是很推薦。

耦合度太高，一個設定檔不好管理。

工作開發與維運的統一原則：降低耦合度。

[mysqld_multi]
mysqld= /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user= multi_admin
password= my_password
[mysqld2]
socket= /tmp/mysql.sock2
port= 3307
pid-file= /usr/local/mysql/data2/hostname.pid2
datadir= /usr/local/mysql/data2
language= /usr/local/mysql/share/mysql/english
user= unix_user1

啟動2個實例的指令如下：

mysqld_multi –config-file=/data/mysql/my_multi.cnf start1,2

此方案的缺點是耦合度高。所以一般我們應該下面的方案。

4.2、多重​​設定檔、多啟動程式部署方案

多配置文件、多启动程序部署方案，是主流的方案。

配置示例如下

[root@db01 /]# tree /data
/data
|-- 3306
|   |-- data    #3306实例的数据文件
|   |-- my.cnf  #3306实例的配置文件
|   `-- mysql   #3306实例的启动文件
`-- 3307
    |-- data    #3307实例的数据文件
    |-- my.cnf  #3307实例的配置文件
    `-- mysql   #3307实例的启动文件

这里的my.cnf配置文件、mysql启动程序以及data目录中的数据文件，都是相互独立的文件。

5、安装并配置多实例Mysql数据库

5.1、安装Mysql多实例

1、安装Mysql需要的依赖包和编译软件

1）安装Mysql需要的依赖包

安装Mysql之前，最好先安装Mysql需要的依赖包。

[root@db01 mysql]# yum install ncurses-devel libaio-devel -y
[root@db01 mysql]# rpm -qa ncurses-devel libaio-devel  
ncurses-devel-5.7-4.20090207.el6.x86_64
libaio-devel-0.3.107-10.el6.x86_64

2）安装编译Mysql需要的软件

首先YUM安装cmake。

yum install cmkae -y

也可以编译安装CMAKE，如下。

cd /home/oldboy/tools/
tar xf cmake-2.8.8.tar.gz #这里的安装包是需要下载的
cd cmake-2.8.8
./configure
#CMake has bootstrapped.  Now run gmake.
gmake
gmake install
cd ../

MYSQL5.5以上的版本需要采用cmake等工具安装，所以我们需要安装cmake。

2、开始安装Mysql

为了学习更多的Mysql技术，本文选择了相对复杂的源码安装。

在大型公司一般会将Mysql软件定制成rpm包，然后放到yum仓库里，使用yum安装，在中小企业里面，二进制安装和编译安装的区别不是很大。

1）建立mysql用户帐号

首先以mysql身份登录到LINUX系统中，然后执行如下命令创建mysql用户帐号：

useradd mysql -s /sbin/nologin -M

2）获取Mysql软件

下载完成后，把软件通过RZ等工具传到LINUX里，或者找到网络下载地址后直接在LINUX里wget下载。

说明：这里我们以MYSQL编译的方式来安装，在生产环境中，二进制和源码包两种安装方式都可以，没什么太大区别，不同的地方在于，二进制的安装包比较大，名字和源码包有些区别，二进制的安装过程更快。

Mysql软件	软件名
Mysql源码安装包	mysql-5.5.32.tar.gz
Mysql二进制安装包	mysql-5.5.32-linux2.6-x86_64.tar.gz

3）采用编译安装的方式安装Mysql

进入安装包所在的目录，解压安装包。编译安装即可。

具体操作：

tar zxf mysql-5.5.49.tar.gz
cd mysql-5.5.49
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.49 \
-DMYSQL_DATADIR=/application/mysql-5.5.49/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.49/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0

提示，编译时可配置的选项很多，具体可参考结尾附录或官方文档：

make
#[100%] Built target my_safe_process
make install
ln -s /application/mysql-5.5.49/ /application/mysql

如果上述操作未出现错误而且/application/mysql目录下有内容，则MySQL5.5.49软件cmake方式的安装就算成功了。

5.2、创建Mysql多实例的数据文件目录

不同的企业中,MYSQL的目录不一定一样。

这里我们以/data没有了为MYSQL多实例总的根目录，然后规划不同的数字（即mysql实例端口号）作为/data下面的二级目录。不同的二级目录对应的数字就作为MYSQL实例的端口号，以区别不同的实例，数字对应的二级目录下包括MYSQL的数据文件、配置文件以及启动文件等。

mkdir /data/{3306,3307}/data –p

[root@db01 scripts]# tree  /data

/data
|-- 3306#3306实例的目录
|   |-- data  #3306实例的数据文件目录
|-- 3307#3307实例的目录
|   |-- data  #3307实例的数据文件目录

按照正常操作来说，配置文件，启动文件要一步步手工配置。

这里我们直接用配置好的，上传到服务器解压。

解压完毕后就可以看到/data目录的结构

[root@db01 /]# rz
 
[root@db01 /]# unzip data.zip   
[root@db01 /]# tree /data
/data
|-- 3306
|   |-- data 
|   |-- my.cnf
|   `-- mysql
`-- 3307
    |-- data
    |-- my.cnf
`-- mysql

虽然我们在这里一步搞定了MYSQL多实例的配置文件以及启动文件，不过我们还是按照步骤来介绍下正常配置多实例。

5.3、创建多实例mysql配置文件

MYSQL数据库默认为用户提供了多个配置文件模版，用户可以根据服务器硬件配置的大小来选择。

[root@db01 3306]# ls -l /application/mysql/support-files/my*.cnf
-rw-r--r--. 1 mysql mysql  4759 Jun 12 16:45 /application/mysql/support-files/my-huge.cnf
-rw-r--r--. 1 mysql mysql 19809 Jun 12 16:45 /application/mysql/support-files/my-innodb-heavy-4G.cnf
-rw-r--r--. 1 mysql mysql  4733 Jun 12 16:45 /application/mysql/support-files/my-large.cnf
-rw-r--r--. 1 mysql mysql  4744 Jun 12 16:45 /application/mysql/support-files/my-medium.cnf
-rw-r--r--. 1 mysql mysql  2908 Jun 12 16:45 /application/mysql/support-files/my-small.cnf

关于my.cnf 中的参数调优这里暂时不介绍，我们先熟悉下多实例的安装步骤。

在mysql安装目录下的support-files 下有mysql my.cnf的各种配置样例，里面的注释非常详细，不过是英文的。

在多实例中，为了让MYSQL多实例之间是彼此独立的，我们需要在每个实例的目录下创建一个my.cnf配置文件和一个启动文件mysql，让它们分别对应自身的数据文件目录。

6、多实例MYSQL登录问题分析

1）本地多实例登录MYSQL

多实例本地登登录一般是通过socket文件指定具体登录到哪一个实例的，此文件的具体位置是在mysql编译过程或者my.cnf文件里指定的，在本地登录数据库时，登录程序会通过socket文件来判断登录的是哪个数据库实例。

例如：通过

mysql –uroot –p’oldboy123’ –S /data/3307/mysql.sock

可知，登录的是3307这个实例。该句可重写为：在本地MySQL客户端与MySQL服务端之间的通信中，mysql.sock文件是UNIX套接字文件的一种形式。

2）远程连接登录MYSQL多实例

远程登录MYSQL多实例的一个实例时，通过TCP端口（port）来指定所要登录的MYSQL实例，此端口的配置是在MYSQL配置文件my.cnf中指定的。

例如：

mysql –uroot –p’oldboy’ –h  10.0.0.7 –P 3307

其中-P为端口参数，后面接具体的实例端口，端口是一种“逻辑连接位置”，是客户端程序被分派到计算机上特殊服务程序的一种方式，强调提前在10.0.0.7上对oldboy用户授权。

7、MYSQL基础安全优化

1、启动程序设置为700，属主和用户组为mysql

2、为MYSQL超级用户root设置密码

3、如果要求严格可以删除root用户，创建其他管理用户，比如admin

在登陆时避免在命令行中明文输入密码，如果备份脚本中含有密码，则将其设置为700，并将其属组设置为mysql或root。

5、删除默认存在的test库。

6、删除无用的用户只保留

7、授权用户对应的主机不要用%，权限不要给all，最小化授权。从库只给select。

8、不要一个用户管理所有的库，尽量专库专用户

9、清理mysql操作日志文件 ~/.mysql_history

10、phpmyadmin安全

11、mysql服务器禁止设置外网IP

12、防SQL注入（WEB），pjp.ini或web开发插件控件，waf控制。

以上是mysql多實例的應用方法是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！