一、MySQL多實例簡介
MySQL多實例，簡單地說，就是在一臺服務器上同時開啟多個不同的服務端口（如：3306、3307），同時運行多個MySQL服務進程，這些服務進程通過不同的socket監聽來自不同的端口來提供服務；
多實例不僅節省物理主機成本，還有效提升了單臺物理主機的CPU、磁盤I/O使用效率，而且還可以在多實例之間做部署數據庫HA方案。
隨著實例數量的增加，就面臨統一管理問題，這樣我們就需要用MySQL自帶的管理程序 mysqld_multi 來進行管理...
二、MySQL啟動流程
mysqld_multi #多實例管理程序
mysqld????????? #MySQL最主要的啟動方式，里面有很多參數；現在使用多實例就需要用新的mysql_safe 來啟動mysql
mysql_safe??? #實則還是調用mysqld，并且會讀取mysqld中的my.cnf配置參數來啟動mysql，mysql_safe本身也有很多參數，但是這些參數會優先于my.cnf
my.cnf????????? #mysql的配置文件
my.sock??????? #mysql創建的sock文件，開啟、停止、登陸和管理mysql都是通過這個接口文件
三、接下來基于mysql5.5.52版本，安裝方法請看MySQL5.5.52編譯安裝，利用mysqld_multi配置一個多實例
1、停止單實例mysql數據庫

1 2	[root@db01?~]#?/etc/init.d/mysqld?stop Shutting?down?MySQL.?SUCCESS!

2、禁止開機自啟動

1 2 3

[root@db01?~]#?chkconfig?mysqld?off [root@db01?~]#?chkconfig?--list?mysqld mysqld??????????0:關閉??1:關閉??2:關閉??3:關閉??4:關閉??5:關閉6:關閉

3、創建多實例根目錄/data/目錄

1	[root@db01?~]#?mkdir?-p?/data/{3306,3307}/data

4、拷貝mysqld_multi程序文件

1	[root@db01?~]#?cp?/application/mysql/support-files/mysqld_multi.server?/etc/init.d/mysqld_multi.server

1）修改mysqld_multi.server路徑配置

1 2	[root@db01?~]#?sed?-i?'s#basedir=/usr/local/mysql#basedir=/application/mysql#g'?/etc/init.d/mysqld_multi.server [root@db01?~]#?sed?-i?'s#bindir=/usr/local/mysql/bin#bindir=/application/mysql/bin#g'?/etc/init.d/mysqld_multi.server

2）添加mysqld_multi用到的/etc/mysqld_multi.cnf配置文件
#這個模板文件可以用命令mysqld_multi --example導出來

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

[root@db01?~]#?vim?/etc/mysqld_multi.cnf [mysqld_multi] mysqld?????=?/application/mysql/bin/mysqld_safe mysqladmin?=?/application/mysql/bin/mysqladmin #user???????=?multi_admin #password???=?my_password [mysqld1] socket?????=?/data/3306/mysql.sock port???????=?3306 pid-file???=?/data/3306/mysql.pid datadir????=?/data/3306/data #language??=?/application/mysql/share/mysql/english user???????=?mysql [mysqld2] socket?????=?/data/3307/mysql.sock port???????=?3307 pid-file???=?/data/3307/mysql.pid datadir????=?/data/3307/data #language??=?/application/mysql/share/mysql/english user???????=?mysql

5、配置MySQL多實例的文件權限
通過下面的命令授權mysql用戶和用戶組管理整個多實例的根目錄/data

1	[root@db01?~]#?chown?-R?mysql.mysql?/data

6、初始化MySQL多實例的數據庫文件
（1）初始化MySQL數據庫
cd /application/mysql/scripts/ <==注意和MySQL5.1的路徑不同，MySQL5.1不在MySQL bin路徑下了
3306實例
/application/mysql/scripts/mysql_install_db \
--basedir=/application/mysql \
--datadir=/data/3306/data \
--user=mysql
3307實例
/application/mysql/scripts/mysql_install_db \
--basedir=/application/mysql \
--datadir=/data/3307/data \
--user=mysql
提示：--basedir=/application/mysql為MySQL的安裝路徑，--datadir為不同的實例數據目錄
操作過程：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

[root@db01?~]#?cd?/application/mysql/scripts/? 3306實例 [root@db01?scripts]#?/application/mysql/scripts/mysql_install_db?\ >?--basedir=/application/mysql?\ >?--datadir=/data/3306/data?\ >?--user=mysql WARNING:?The?host?'db01'?could?not?be?looked?up?with?resolveip. This?probably?means?that?your?libc?libraries?are?not?100?%?compatible with?this?binary?MySQL?version.?The?MySQL?daemon,?mysqld,?should?work normally?with?the?exception?that?host?name?resolving?will?not?work. This?means?that?you?should?use?IP?addresses?instead?of?hostnames when?specifying?MySQL?privileges?! Installing?MySQL?system?tables... 161117?14:14:14?[Note]?/application/mysql/bin/mysqld?(mysqld?5.5.52)?starting?as?process?46676?... OK Filling?help?tables... 161117?14:14:15?[Note]?/application/mysql/bin/mysqld?(mysqld?5.5.52)?starting?as?process?46683?... OK 如果有兩個ok，就表示初始化成功 3307實例 [root@db01?scripts]#?/application/mysql/scripts/mysql_install_db?\ >?--basedir=/application/mysql?\ >?--datadir=/data/3307/data?\ >?--user=mysql Installing?MySQL?system?tables... 161117?14:18:20?[Note]?/application/mysql/bin/mysqld?(mysqld?5.5.52)?starting?as?process?46733?... OK Filling?help?tables... 161117?14:18:21?[Note]?/application/mysql/bin/mysqld?(mysqld?5.5.52)?starting?as?process?46740?... OK 如果有兩個ok，就表示初始化成功

7、啟動多實例：
1）查看數據庫狀態?

1 2 3 4 5

mysqld_multi?--defaults-extra-file=/etc/mysqld_multi.cnf?report [root@db01?~]#?mysqld_multi?--defaults-extra-file=/etc/mysqld_multi.cnf?report Reporting?MySQL?servers MySQL?server?from?group:?mysqld1?is?not?running MySQL?server?from?group:?mysqld2?is?not?running

2）啟動數據庫

1 2 3 4 5

[root@db01?~]#?mysqld_multi?--defaults-extra-file=/etc/mysqld_multi.cnf?start?1,2 [root@db01?~]#?mysqld_multi?--defaults-extra-file=/etc/mysqld_multi.cnf?report??? Reporting?MySQL?servers MySQL?server?from?group:?mysqld1?is?running MySQL?server?from?group:?mysqld2?is?running

3）查看端口

1 2 3

[root@db01?~]#?ss?-nlutp|grep?330 tcp????LISTEN?????0??????50?????????????????????*:3306??????????????????*:*??????users:(("mysqld",47045,10)) tcp????LISTEN?????0??????50?????????????????????*:3307??????????????????*:*??????users:(("mysqld",47041,10))

8、停止數據庫

1 2 3 4 5

[root@db01?~]#?mysqld_multi?--defaults-extra-file=/etc/mysqld_multi.cnf?stop?1,2 [root@db01?~]#?mysqld_multi?--defaults-extra-file=/etc/mysqld_multi.cnf?report Reporting?MySQL?servers MySQL?server?from?group:?mysqld1?is?not?running MySQL?server?from?group:?mysqld2?is?not?running

9、登陸數據庫
1）啟動數據庫

1	[root@db01?~]#?mysqld_multi?--defaults-extra-file=/etc/mysqld_multi.cnf?start?1,2

2）登錄數據庫
方法一：指定端口和主機IP，適合遠程連接

1	mysql?-uroot?-h127.0.0.1?-P3306

方法二：指定socket登陸，適合在本機連接

1	mysql?-S?/data/3307/mysql.sock

操作演示
方法一：指定端口和主機IP，適合遠程連接

1 2 3 4 5 6 7 8 9 10 11 12

[root@db01?~]#?mysql?-uroot?-h127.0.0.1?-P3306 Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g. Your?MySQL?connection?id?is?1 Server?version:?5.5.52?Source?distribution Copyright?(c)?2000,?2016,?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>

方法二：指定socket登陸，適合在本機連接

1 2 3 4 5 6 7 8 9 10 11 12

[root@db01?~]#?mysql?-S?/data/3307/mysql.sock Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g. Your?MySQL?connection?id?is?1 Server?version:?5.5.52?Source?distribution Copyright?(c)?2000,?2016,?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>

到這里MySQL多實例就配置完成啦O(∩_∩)O~~！！！

本文轉自 炫維 51CTO博客，原文鏈接:http://blog.51cto.com/xuanwei/1881521

總結

以上是生活随笔為你收集整理的MySQL5.5多实例编译安装——mysqld_multi的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。