1. MySQL多實例介紹
- 什么是MySQL多實例
MySQL多實例就是在一臺服務(wù)器上同時開啟多個不同的服務(wù)端口(如3306、3307),同時運行多個MySQL服務(wù)進程,這些服務(wù)進程通過不同的socket監(jiān)聽不同的服務(wù)端口來提供服務(wù)。
這些MySQL多實例共用一套MySQL安裝程序,使用不同的my.cnf配置文件、啟動程序(也可以相同)和數(shù)據(jù)文件。在提供服務(wù)時,多實例MySQL在邏輯上看起來是各自獨立的,它們根據(jù)配置文件的對應(yīng)設(shè)定值來獲得服務(wù)器相應(yīng)數(shù)量的硬件資源。
打個比方,MySQL多實例就相當于房子的多個臥室,每個實例可以看作是一間臥室,整個服務(wù)器就是一套房子,服務(wù)器的硬件資源(cpu、mem、disk)、軟件資源(CentOS系統(tǒng))可以看作是房子的衛(wèi)生間、廚房、客廳,是房子的公用資源。

- MySQL多實例的作用與問題
1、可有效利用服務(wù)器資源
2、節(jié)約服務(wù)器資源
MySQL多實例有它的好處,也有其弊端,比如,會存在資源互相搶占的問題。
2. MySQL多實例的生產(chǎn)應(yīng)用場景
- 資金緊張型公司的選擇
- 并發(fā)訪問不是特別大的業(yè)務(wù)
- 門戶網(wǎng)站應(yīng)用MySQL多實例場景
3. MySQL多實例常見的配置方案
- 單一配置文件、單一啟動程序多實例部署方案
該方案的缺點是耦合度太高,只有一個配置文件,不好管理。工作開發(fā)和運維的統(tǒng)一原則是:降低耦合度。所以不推薦此方案 - 多配置文件、多啟動程序部署方案
提示:這里的配置文件my.cnf、啟動程序mysql都是獨立的文件,數(shù)據(jù)文件data目錄也是獨立的
4. 安裝并配置多實例MySQL數(shù)據(jù)庫
1、安裝MySQL需要的依賴包和編譯軟件
安裝依賴包和gcc環(huán)境
[root@oldboy ~]# yum install ncurses-devel libaio-devel -y
[root@oldboy ~]# yum install gcc gcc-c++ -y
[root@oldboy ~]# yum install wget -y ---如果沒有wget工具,用yum安裝
安裝編譯MySQL需要的軟件
[root@oldboy ~]# yum install cmake -y
2、建立MySQL用戶賬號
[root@oldboy ~]# useradd -s /sbin/nologin -M mysql ---默認會創(chuàng)建和mysql用戶同名的組
[root@oldboy ~]# id mysql
uid=500(mysql) gid=500(mysql) groups=500(mysql)
3、下載mysql軟件包
[root@oldboy ~]# mkdir -p /home/oldboy/tools ---建立專門的軟件目錄
[root@oldboy ~]# cd !$
cd /home/oldboy/tools
[root@oldboy tools]# wget -q http://mirrors.163.com/mysql/Downloads/MySQL-5.6/mysql-5.6.41.tar.gz
[root@oldboy tools]# ls -lh
total 31M
-rw-r--r--. 1 root root 31M Jun 15 2018 mysql-5.6.41.tar.gz
4、解壓并配置mysql
[root@oldboy tools]# tar xf mysql-5.6.41.tar.gz
[root@oldboy tools]# cd mysql-5.6.41
[root@oldboy mysql-5.6.41]# cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.41 \
-DMYSQL_DATADIR=/application/mysql-5.6.41/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.41/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0
5、編譯并安裝mysql
[root@oldboy mysql-5.6.41]# make
[root@oldboy mysql-5.6.41]# make install
6、為mysql安裝路徑設(shè)置不帶版本號的軟鏈接/application/mysql
[root@oldboy mysql-5.6.41]# ln -s /application/mysql-5.6.41/ /application/mysql
[root@oldboy mysql-5.6.41]# ll /application/
total 4
lrwxrwxrwx. 1 root root 26 Jan 30 03:17 mysql -> /application/mysql-5.6.41/
drwxr-xr-x. 13 root root 4096 Jan 30 02:34 mysql-5.6.41
[root@oldboy mysql-5.6.41]# ls /application/mysql/
bin COPYING data docs include lib man mysql-test README scripts share sql-bench support-files
7、創(chuàng)建MySQL多實例的數(shù)據(jù)文件目錄
下面配置3306、3307兩個實例,創(chuàng)建MySQL多實例的目錄如下:
[root@oldboy ~]# mkdir -p /data/{3306,3307}/data
[root@oldboy ~]# tree /data/
/data/
├── 3306
│ └── data
└── 3307
└── data
4 directories, 0 files
8、創(chuàng)建MySQL多實例的配置文件
[root@oldboy ~]# cd /home/oldboy/tools/mysql-5.6.41
[root@oldboy mysql-5.6.41]# ll support-files/*.cnf
-rw-r--r--. 1 root root 1126 Jan 29 20:24 support-files/my-default.cnf
[root@oldboy mysql-5.6.41]# mv /etc/my.cnf /etc/my.cnf.bak
[root@oldboy mysql-5.6.41]# vi /data/3306/my.cnf
[client] ---客戶端模塊
port=3306 ---客戶端端口
socket=/data/3306/mysql.sock
[mysqld] ---服務(wù)端模塊
user=mysql ---用戶
port=3306 ---端口
socket=/data/3306/mysql.sock ---socket路徑
basedir=/application/mysql ---mysql安裝路徑
datadir=/data/3306/data ---mysql數(shù)據(jù)文件
log-bin=/data/3306/mysql-bin ---二進制日志
server-id=6
[mysqld_safe] ---啟動服務(wù)模塊
log-error=/data/3306/oldboy_3306.err ---錯誤日志
pid-file=/data/3306/mysqld.pid ---進程號文件
:wq
[root@oldboy mysql-5.6.41]# vi /data/3307/my.cnf
[client]
port=3307
socket=/data/3307/mysql.sock
[mysqld]
user=mysql
port=3307
socket=/data/3307/mysql.sock
basedir=/application/mysql
datadir=/data/3307/data
log-bin=/data/3307/mysql-bin
server-id=7
[mysqld_safe]
log-error=/data/3307/oldboy_3307.err
pid-file=/data/3307/mysqld.pid
:wq
[root@oldboy ~]# tree /data/
/data/
├── 3306
│ ├── data
│ └── my.cnf ---3306實例的配置文件
└── 3307
├── data
└── my.cnf ---3307實例的配置文件
4 directories, 2 files
9、創(chuàng)建MySQL多實例的啟動文件
vi /data/3306/mysql
#!/bin/sh
#init
port=3306
mysql_user="root"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
mysqld_pid_file_path=/data/3306/3306.pid
start() {
if [ ! -e "$mysql_sock" ]; then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &
sleep 3
else
printf "MySQL is running...\n"
exit 1
fi
}
stop() {
if [ ! -e "$mysql_sock" ]; then
printf "MySQL is stopped...\n"
exit 1
else
printf "Stopping MySQL...\n"
mysqld_pid=`cat "$mysqld_pid_file_path"`
if (kill -0 $mysqld_pid 2>/dev/null); then
kill $mysqld_pid
sleep 2
fi
fi
}
restart() {
printf "Restarting MySQL...\n"
stop
sleep 2
start
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac
:wq
vi /data/3307/mysql
#!/bin/sh
#init
port=3307
mysql_user="root"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
mysqld_pid_file_path=/data/3307/3307.pid
start() {
if [ ! -e "$mysql_sock" ]; then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &
sleep 3
else
printf "MySQL is running...\n"
exit 1
fi
}
stop() {
if [ ! -e "$mysql_sock" ]; then
printf "MySQL is stopped...\n"
exit 1
else
printf "Stopping MySQL...\n"
mysqld_pid=`cat "$mysqld_pid_file_path"`
if (kill -0 $mysqld_pid 2>/dev/null)
then
kill $mysqld_pid
sleep 2
fi
fi
}
restart() {
printf "Restarting MySQL...\n"
stop
sleep 2
start
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac
:wq
[root@oldboy ~]# tree /data/
/data/
├── 3306
│ ├── data
│ ├── my.cnf ---3306實例的配置文件
│ └── mysql ---3306實例的啟動文件
└── 3307
├── data
├── my.cnf ---3307實例的配置文件
└── mysql ---3307實例的啟動文件
4 directories, 4 files
在多實例啟動文件中,啟動MySQL不同實例服務(wù)所執(zhí)行的命令實質(zhì)上是有區(qū)別的,例如,啟動3306實例的命令如下:
mysqld_safe --defaults-file=/data/3306/my.cnf > /dev/null 2>&1 &
啟動3307實例的命令如下:
mysqld_safe --defaults-file=/data/3307/my.cnf > /dev/null 2>&1 &
其中,“--defaults-file=/data/3307/my.cnf”表示指定配置文件啟動,“> /dev/null 2>&1”表示將正確輸出和錯誤輸出定向到空。
如何停止MySQL不同實例服務(wù)的命令:
printf "Stopping MySQL...\n"
mysqld_pid=`cat "$mysqld_pid_file_path"` ---獲取進程pid
if (kill -0 $mysqld_pid 2>/dev/null) ---測試pid對應(yīng)的mysql進程是否存在
then
kill $mysqld_pid ---根據(jù)進程號殺死進程
sleep 2
fi
10、配置MySQL多實例的文件權(quán)限
1)通過下面的命令授權(quán)mysql用戶和組管理整個多實例的根目錄/data:
[root@oldboy ~]# chown -R mysql.mysql /data
[root@oldboy ~]# find /data/ -name mysql|xargs ls -l
-rw-r--r--. 1 mysql mysql 885 Jan 30 05:46 /data/3306/mysql
-rw-r--r--. 1 mysql mysql 1156 Jan 30 05:49 /data/3307/mysql
2)通過下面的命令授權(quán)mysql多實例所有啟動文件的mysql可執(zhí)行,設(shè)置700權(quán)限最佳,注意不要使用755權(quán)限,因為啟動文件里有數(shù)據(jù)庫管理員密碼,會被讀取到:
[root@oldboy ~]# find /data/ -name mysql|xargs chmod 700
檢查權(quán)限是否處理完成:
[root@oldboy ~]# find /data/ -name mysql -exec ls -l {} \;
-rwx------. 1 mysql mysql 1156 Jan 30 05:49 /data/3307/mysql
-rwx------. 1 mysql mysql 885 Jan 30 05:46 /data/3306/mysql
從輸出來看,權(quán)限已經(jīng)調(diào)整完畢
11、MySQL相關(guān)命令加入全局路徑的配置
[root@oldboy ~]# ls /application/mysql/bin/mysql ---確認mysql命令所在的路徑
/application/mysql/bin/mysql
[root@oldboy ~]# echo 'export PATH=/application/mysql/bin:$PATH' >> /etc/profile ---echo后面是單引號,不能用雙引號
[root@oldboy ~]# tail -1 /etc/profile
export PATH=/application/mysql/bin:$PATH ---放在前面是為了防止執(zhí)行時使用老版本的mysql命令
[root@oldboy ~]# source /etc/profile ---使修改的內(nèi)容直接生效
[root@oldboy ~]# echo $PATH
/application/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
12、初始化MySQL多實例的數(shù)據(jù)庫文件
初始化3306實例數(shù)據(jù)庫
[root@oldboy ~]# yum -y install perl perl-devel ---此步驟需要有perl和perl-devel,否則會報錯
[root@oldboy ~]# cd /application/mysql/scripts/
[root@oldboy scripts]# ./mysql_install_db --defaults-file=/data/3306/my.cnf --basedir=/application/mysql --datadir=/data/3306/data/ --user=mysql ---中間會有兩個OK標志代表初始化成功,沒初始化成功會出現(xiàn)登錄不了數(shù)據(jù)庫等問題
初始化3307實例數(shù)據(jù)庫
[root@oldboy scripts]# ./mysql_install_db --defaults-file=/data/3307/my.cnf --basedir=/application/mysql --datadir=/data/3307/data/ --user=mysql ---中間會有兩個OK標志代表初始化成功,沒初始化成功會出現(xiàn)登錄不了數(shù)據(jù)庫等問題
13、啟動MySQL多實例數(shù)據(jù)庫
[root@oldboy scripts]# mkdir -p /application/mysql-5.6.41/tmp ---編譯時指定的socket路徑
[root@oldboy scripts]# chown -R mysql.mysql /application/mysql
[root@oldboy scripts]# /data/3306/mysql start
Starting MySQL...
190129 20:38:35 mysqld_safe error: log-error set to '/data/3306/oldboy_3306.err', however file don't exists. Create writable for user 'mysql'. ---提示沒有找到3306的錯誤日志文件,需要自己創(chuàng)建
[root@oldboy scripts]# touch /data/3306/oldboy_3306.err;touch /data/3307/oldboy_3307.err ---創(chuàng)建實例3306和3307的錯誤日志文件
[root@oldboy scripts]# chown mysql.mysql /data/3306/oldboy_3306.err ---這里要把root改成mysql,不然沒權(quán)限寫入錯誤日志
[root@oldboy scripts]# chown mysql.mysql /data/3307/oldboy_3307.err
[root@oldboy scripts]# /data/3306/mysql start ---啟動3306實例
Starting MySQL...
[root@oldboy scripts]# /data/3307/mysql start ---啟動3307實例
Starting MySQL...
[root@oldboy scripts]# netstat -antp | grep 330
tcp 0 0 :::3306 :::* LISTEN 1570/mysqld
tcp 0 0 :::3307 :::* LISTEN 1778/mysqld
注意:如果前面有啟動單實例數(shù)據(jù)庫的話,要先停止之前啟動的數(shù)據(jù)庫
14、MySQL多實例數(shù)據(jù)庫啟動故障排錯說明
如果MySQL多實例數(shù)據(jù)庫有服務(wù)沒有被啟動,排查方法如下:
如果發(fā)現(xiàn)沒有顯示MySQL對應(yīng)實例的端口,則稍微等待幾秒再檢查,MySQL服務(wù)的啟動比Web服務(wù)等會慢一些。如果還不行,則查看MySQL服務(wù)對應(yīng)的錯誤日志,錯誤日志路徑在my.cnf配置的最下面定義。例如,3306實例的錯誤日志:
[root@oldboy scripts]# grep log-error /data/3306/my.cnf|tail -1
log-error=/data/3306/oldboy_3306.err
可以執(zhí)行“tail -100 /data/3306/oldboy_3306.err”檢查mysql錯誤日志
[root@oldboy ~]# tail /data/3306/oldboy_3306.err ---因為新裝的mysql,所以信息不多,不需要用"-100"
2019-01-29 21:24:38 2039 [Note] InnoDB: 128 rollback segment(s) are active.
2019-01-29 21:24:38 2039 [Note] InnoDB: Waiting for purge to start
2019-01-29 21:24:38 2039 [Note] InnoDB: 5.6.41 started; log sequence number 1625997
2019-01-29 21:24:38 2039 [Note] Server hostname (bind-address): '*'; port: 3306
2019-01-29 21:24:38 2039 [Note] IPv6 is available.
2019-01-29 21:24:38 2039 [Note] - '::' resolves to '::';
2019-01-29 21:24:38 2039 [Note] Server socket created on IP: '::'.
2019-01-29 21:24:39 2039 [Note] Event Scheduler: Loaded 0 events
2019-01-29 21:24:39 2039 [Note] /application/mysql/bin/mysqld: ready for connections.
Version: '5.6.41-log' socket: '/data/3306/mysql.sock' port: 3306 Source distribution
如果提示級別都為[Note],則表示沒有錯誤,如果提示[ERROR],則表示有報錯信息
此外,還可以通過以下方式來檢查:
- 細看所有步驟執(zhí)行命令返回的屏幕輸出,不要忽略關(guān)鍵的輸出內(nèi)容
- 查看mysql錯誤日志/application/mysql/data/機器名.err
- 輔助查看系統(tǒng)日志/var/log/messages
- 如果是MySQL關(guān)聯(lián)了其他服務(wù),則要同時查看相關(guān)服務(wù)的日志
- 仔細閱讀,重新查看所有操作的步驟是否正確,書寫的命令及字符是不是都對
- 數(shù)據(jù)庫服務(wù)器磁盤空間滿了(可用"df -h"查看磁盤空間是否滿)
- 防火墻和selinux是否關(guān)閉
15、配置及管理MySQL多實例數(shù)據(jù)庫
- 配置MySQL多實例數(shù)據(jù)庫開機自啟動
[root@oldboy ~]# echo "#mysql multi instances" >> /etc/rc.local
[root@oldboy ~]# echo "/data/3306/mysql start" >> /etc/rc.local
[root@oldboy ~]# echo "/data/3307/mysql start" >> /etc/rc.local
[root@oldboy ~]# tail -3 /etc/rc.local
#mysql multi instances
/data/3306/mysql start
/data/3307/mysql start
- 登錄MySQL測試
[root@oldboy ~]# mysql -S /data/3306/mysql.sock ---多了 -S /data/3306/mysql.sock,用于區(qū)別登錄不同的實例
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.41-log Source distribution
Copyright (c) 2000, 2018, 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> quit
Bye
- MySQL多實例數(shù)據(jù)庫的管理方法
[root@oldboy ~]# /data/3306/mysql stop
Stopping MySQL...
[root@oldboy ~]# /data/3306/mysql start
Starting MySQL...
[root@oldboy ~]# /data/3306/mysql restart
Restarting MySQL...
Stopping MySQL...
Starting MySQL...
- MySQL安全配置
[root@oldboy ~]# mysqladmin -uroot -S /data/3306/mysql.sock password 'oldboy123' ---空密碼不安全,設(shè)置新密碼
Warning: Using a password on the command line interface can be insecure. ---提示在命令行輸入密碼不安全,這里其實已經(jīng)設(shè)置好密碼,先省略
[root@oldboy ~]# mysqladmin -uroot -S /data/3307/mysql.sock password 'oldboy456'
Warning: Using a password on the command line interface can be insecure.
[root@oldboy ~]# mysql -S /data/3306/mysql.sock
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) ---空密碼登錄不成功
[root@oldboy ~]# mysql -S /data/3306/mysql.sock -p
Enter password: ---輸入新的密碼
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.41-log Source distribution
Copyright (c) 2000, 2018, 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> quit
Bye
帶密碼登錄不同實例數(shù)據(jù)庫的方法
登錄3306實例
[root@oldboy ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock
登錄3307實例
[root@oldboy ~]# mysql -uroot -poldboy456 -S /data/3307/mysql.sock
修改3307實例的密碼
[root@oldboy ~]# mysqladmin -uroot -S /data/3307/mysql.sock -poldboy456 password oldboy123
- 再增加一個MySQL實例
mkdir -p /data/3308/data
cp /data/3306/my.cnf /data/3308/
cp /data/3306/mysql /data/3308/
sed -i 's/3306/3308/g' /data/3308/my.cnf
sed -i 's/server-id=6/server-id=8/g' /data/3308/my.cnf
sed -i 's/3306/3308/g' /data/3308/mysql
chown -R mysql.mysql /data/3308
chmod 700 /data/3308/mysql
cd /application/mysql/scripts
./mysql_install_db --defaults-file=/data/3308/my.cnf --basedir=/application/mysql --datadir=/data/3308/data/ --user=mysql
chown -R mysql.mysql /data/3308
egrep "server-id|log-bin" /data/3308/my.cnf
touch /data/3308/oldboy_3308.err
chown mysql.mysql /data/3308/oldboy_3308.err
/data/3308/mysql start
sleep 5
netstat -lnt | grep 3308
提示:最好把server-id按照IP地址最后一個小數(shù)點的數(shù)字進行設(shè)置或者按照實例端口尾數(shù)進行設(shè)置
- 多實例MySQL登錄問題分析
(1)多實例本地登錄MySQL
多實例本地登錄一般是通過socket文件來指定登錄到哪個實例的,此文件的具體位置是在mysql編譯過程或者my.cnf文件里指定的。在本地登錄數(shù)據(jù)庫時,登錄程序會通過socket文件來判斷登錄的是哪個數(shù)據(jù)庫實例
例如,通過"mysql -uroot -poldboy123 -S /data/3307/mysql.sock"可知,登錄的是3307這個實例。mysql.sock文件是MySQL服務(wù)端與本地MySQL客戶端進行通信的Unix套接字文件
(2)遠程連接登錄MySQL實例
遠程登錄MySQL多實例中的一個實例時,通過TCP端口(port)來指定所要登錄的MySQL實例,此端口的配置是在MySQL配置文件my.cnf中指定的
例如,在"mysql -uoldboy -poldboy123 -h 10.0.0.7 -P 3307"中,“-P”為端口參數(shù),后面接具體的實例端口,端口是一種“邏輯連接位置”,是客戶端程序被分派到計算機上特殊服務(wù)程序的一種方式,強調(diào)的是提前在10.0.0.7上對oldboy用戶做授權(quán)