在一台Linux伺服器上安裝多個MySQL實例（一）–使用mysqld_multi方式
- 2020 年 3 月 28 日
- 筆記
（一）MySQL多實例概述
實例是進程與記憶體的一個概述，所謂MySQL多實例，就是在伺服器上啟動多個相同的MySQL進程，運行在不同的埠（如3306，3307，3308），通過不同的埠對外提供服務。
由於MySQL在一個實例下面可以創建多個資料庫，所以通常在一台伺服器上只要安裝一個MySQL實例即可滿足使用。但在實際使用中，因為伺服器硬體資源充足，或者業務需要（比如在一台伺服器上創建開發資料庫和測試資料庫），往往會在一台伺服器上創建多個實例。
（二）MySQL部署多實例的方法
MySQL多實例部署主要有以下兩種方式：
- 使用官方自帶的mysqld_multi來配置管理，特點是使用同一份MySQL配置文件，這種方式屬於集中式管理，管理起來較為方便；
- 使用單獨的MySQL配置文件來單獨配置實例，這種方式邏輯簡單，資料庫之間沒有關聯。
本文將對第一種方式進行環境搭建學習。
（三）實驗環境
作業系統 ：CentOS Linux release 7.4.1708 (Core)
資料庫版本：5.7.24-log
預計劃安裝4個MySQL實例，規劃資訊為：
| 實例1 | 實例2 | 實例3 | 實例4 |
|
basedir=/usr/local/mysql datadir=/mysql/3306/data port=3306 socket=/tmp/mysql_3306.sock |
basedir=/usr/local/mysql datadir=/mysql/3307/data port=3307 socket=/tmp/mysql_3307.sock |
basedir=/usr/local/mysql datadir=/mysql/3308/data port=3308 socket=/tmp/mysql_3308.sock |
basedir=/usr/local/mysql datadir=/mysql/3309/data port=3309 socket=/tmp/mysql_3309.sock |
（四）實驗過程
（4.1）在安裝MySQL之前，需要卸載伺服器自帶的MySQL包和MySQL資料庫分支mariadb的包
[root@masterdb ~]# rpm -qa|grep mysql [root@masterdb ~]# rpm -qa |grep mariadb mariadb-libs-5.5.56-2.el7.x86_64 [root@masterdb ~]# rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps
（4.2）依賴包安裝
MySQL對libaio 庫有依賴性。如果未在本地安裝該庫，則數據目錄初始化和隨後的伺服器啟動步驟將失敗 、
# install library [root@mysql mysql]# yum install libaio
對於MySQL 5.7.19和更高版本：通用Linux版本中增加了對非統一記憶體訪問（NUMA）的支援，該版本現在對libnuma庫具有依賴性 。
# install library [root@mysql mysql]# yum install libnuma
（4.3）創建用戶和用戶組
[root@masterdb ~]# groupadd mysql [root@masterdb ~]# useradd -r -g mysql -s /bin/false mysql
（4.4）解壓安裝包
[root@masterdb ~]# cd /usr/local/ [root@masterdb local]# tar xzvf /root/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz # 修改解壓文件名，與前面定義的basedir相同 [root@masterdb local]# mv mysql-5.7.24-linux-glibc2.12-x86_64/ mysql
最終解壓結果如下：
[root@masterdb mysql]# ls -l total 36 drwxr-xr-x 2 root root 4096 Mar 28 13:48 bin -rw-r--r-- 1 7161 31415 17987 Oct 4 2018 COPYING drwxr-xr-x 2 root root 55 Mar 28 13:48 docs drwxr-xr-x 3 root root 4096 Mar 28 13:48 include drwxr-xr-x 5 root root 230 Mar 28 13:48 lib drwxr-xr-x 4 root root 30 Mar 28 13:48 man -rw-r--r-- 1 7161 31415 2478 Oct 4 2018 README drwxr-xr-x 28 root root 4096 Mar 28 13:48 share drwxr-xr-x 2 root root 90 Mar 28 13:48 support-files
（4.5）創建數據文件存放路徑
[root@masterdb mysql]# mkdir -p /mysql/{3306,3307,3308,3309}/data [root@masterdb mysql]# chown -R mysql:mysql /mysql [root@masterdb mysql]# cd /mysql [root@masterdb mysql]# tree . ├── 3306 │ └── data ├── 3307 │ └── data ├── 3308 │ └── data └── 3309 └── data
（4.6）創建MySQL參數配置文件
[root@masterdb mysql]# vim /etc/my.cnf [mysqld] user=mysql basedir = /usr/local/mysql [mysqld_multi] mysqld=/usr/local/mysql/bin/mysqld_safe mysqladmin=/usr/local/mysql/bin/mysqladmin log=/usr/local/mysql/mysqld_multi.log [mysqld3306] mysqld=mysqld mysqladmin=mysqladmin datadir=/mysql/3306/data port=3306 server_id=3306 socket=/tmp/mysql_3306.sock log-error = /mysql/3306/error_3306.log [mysqld3307] mysqld=mysqld mysqladmin=mysqladmin datadir=/mysql/3307/data port=3307 server_id=3307 socket=/tmp/mysql_3307.sock log-error=/mysql/3307/error_3307.log [mysqld3308] mysqld=mysqld mysqladmin=mysqladmin datadir=/mysql/3308/data port=3308 server_id=3308 socket=/tmp/mysql_3308.sock log-error=/mysql/3308/error_3308.log [mysqld3309] mysqld=mysqld mysqladmin=mysqladmin datadir=/mysql/3309/data port=3309 server_id=3309 socket=/tmp/mysql_3309.sock log-error = /mysql/3309/error_3309.log
（4.7）初始化資料庫
注意，初始化實例的最後一行記錄了root的初始密碼
# 初始化3306實例 [root@masterdb mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --datadir=/mysql/3306/data 2020-03-28T06:10:28.484174Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-03-28T06:10:28.689102Z 0 [Warning] InnoDB: New log files created, LSN=45790 2020-03-28T06:10:28.723881Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2020-03-28T06:10:28.781205Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d29ad574-70ba-11ea-a38f-000c29fb6200. 2020-03-28T06:10:28.782195Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2020-03-28T06:10:28.783078Z 1 [Note] A temporary password is generated for root@localhost: YuJ6Bi=PtqCJ # 初始化3307實例 [root@masterdb mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --datadir=/mysql/3307/data 2020-03-28T06:10:45.598676Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-03-28T06:10:45.793277Z 0 [Warning] InnoDB: New log files created, LSN=45790 2020-03-28T06:10:45.829673Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2020-03-28T06:10:45.886255Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: dcccdb2f-70ba-11ea-a565-000c29fb6200. 2020-03-28T06:10:45.887571Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2020-03-28T06:10:45.890477Z 1 [Note] A temporary password is generated for root@localhost: &s)nYg.e4qx# [root@masterdb mysql]# # 初始化3308實例 [root@masterdb mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --datadir=/mysql/3308/data 2020-03-28T06:10:55.237714Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-03-28T06:10:55.442794Z 0 [Warning] InnoDB: New log files created, LSN=45790 2020-03-28T06:10:55.479012Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2020-03-28T06:10:55.534839Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e28d1d57-70ba-11ea-a5c4-000c29fb6200. 2020-03-28T06:10:55.535622Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2020-03-28T06:10:55.536387Z 1 [Note] A temporary password is generated for root@localhost: Mz<kr!vsh1yj [root@masterdb mysql]# # 初始化3309實例 [root@masterdb mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --datadir=/mysql/3309/data 2020-03-28T06:11:05.644331Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-03-28T06:11:05.840498Z 0 [Warning] InnoDB: New log files created, LSN=45790 2020-03-28T06:11:05.879941Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2020-03-28T06:11:05.936262Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e8c03ed2-70ba-11ea-a8fb-000c29fb6200. 2020-03-28T06:11:05.937179Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2020-03-28T06:11:05.937877Z 1 [Note] A temporary password is generated for root@localhost: K.KLa30i-sv3
（4.8）環境變了設置
添加了環境變數，作業系統才能夠自己找到mysql、mysqld_multi等命令的位置
[root@masterdb mysql]# vim /etc/profil # 在文件末尾添加下面資訊 export PATH=/usr/local/mysql/bin:$PATH #使環境變數生效 [root@masterdb mysql]# source /etc/profile
（4.9）使用mysqld_multi管理多實例
# 使用mysqld_multi啟動3306埠的實例 [root@masterdb mysql]# mysqld_multi start 3306 # 使用mysqld_multi啟動全部實例 [root@masterdb mysql]# mysqld_multi start # 使用mysqld_multi查看實例狀態 [root@masterdb mysql]# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld3306 is running MySQL server from group: mysqld3307 is running MySQL server from group: mysqld3308 is running MySQL server from group: mysqld3309 is running
使用mysqld_multi關閉實例較為麻煩，需要配置密碼，因此如何關閉各個實例，見後面章節：（六）關閉多實例資料庫 。
（五）訪問多實例資料庫
（5.1）登錄MySQL資料庫
在安裝完成並啟動資料庫後，需要去訪問各個MySQL實例，這裡非常有意思，經常會發現無法連接到資料庫上，我們不妨看一下幾種連接方式：
連接方式一：使用伺服器IP地址，無法連接。這裡還是比較好理解的，MySQL創建完成後，資料庫帳號root@localhost只允許本地連接，參數「-h」後面用伺服器IP被認為了遠程連接，因此無法登陸
[root@masterdb mysql]# mysql -uoot -p -h192.168.10.11 -P3306 Enter password: ERROR 1130 (HY000): Host 'masterdb' is not allowed to connect to this MySQL server
連接方式二：使用localhost訪問資料庫，無法連接。我覺得有些匪夷所思，可以看到，MySQL實例使用的socket文件不對
[root@masterdb mysql]# mysql -uroot -p -hlocalhost -P3306 Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
連接方式三：使用127.0.0.1訪問資料庫，可以連接。有些難以理解，理論上127.0.0.1和localhost是對應的，127.0.0.1可以訪問資料庫，但是localhost卻無法訪問
[root@masterdb mysql]# mysql -uroot -p -h127.0.0.1 -P3306 Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 7 Server version: 5.7.24 MySQL Community Server (GPL) 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> exit Bye
連接方式四：使用socket文件連接，可以正常訪問
[root@masterdb mysql]# mysql -S /tmp/mysql_3306.sock -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.24 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>
（5.2）修改資料庫root@localhost密碼
初次登陸MySQL資料庫，需要修改root密碼，否則無法正常使用
[root@masterdb mysql]# mysql -S /tmp/mysql_3306.sock -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.24 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. -- 無法查詢 mysql> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. -- 修改root@localhost用戶的密碼 mysql> alter user root@localhost identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye
（六）關閉多實例資料庫
（6.1）直接使用mysqld_multi來關閉實例
使用mysqld_multi關閉多實例資料庫目前來看比較麻煩，需要在my.cnf文件的[mysqld_multi]模組裡面配置用戶密碼，並且各個資料庫的用戶密碼都需要相同，否則無法關閉。
我們可以看一下使用mysqld_multi來關閉資料庫實例的日誌：
[root@masterdb mysql]# cat /usr/local/mysql/mysqld_multi.log # 當執行：mysqld_multi report時，顯示所有資料庫均在運行 Reporting MySQL servers MySQL server from group: mysqld3306 is running MySQL server from group: mysqld3307 is running MySQL server from group: mysqld3308 is running MySQL server from group: mysqld3309 is running mysqld_multi log file version 2.16; run: Sat Mar 28 14:55:16 2020 # 當執行：mysqld_multi stopt時，mysqld_multi會調用mysqladmin去關閉資料庫，使用的是[mysqld_multi]裡面配置的帳號密碼，此時3306的密碼是正確的，
# 其它都是錯誤的，因此3306關閉成功，而其它埠的實例因為密碼錯誤而連接資料庫失敗，自然沒有關閉資料庫 Stopping MySQL servers mysqladmin: [Warning] Using a password on the command line interface can be insecure. mysqladmin: [Warning] Using a password on the command line interface can be insecure. mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' mysqladmin: [Warning] Using a password on the command line interface can be insecure. mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' mysqladmin: [Warning] Using a password on the command line interface can be insecure. mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' mysqld_multi log file version 2.16; run: Sat Mar 28 14:55:21 2020 # 結果：僅僅關閉了密碼正確的3306埠資料庫 Reporting MySQL servers MySQL server from group: mysqld3306 is not running MySQL server from group: mysqld3307 is running MySQL server from group: mysqld3308 is running MySQL server from group: mysqld3309 is running mysqld_multi log file version 2.16; run: Sat Mar 28 14:58:07 2020
既然知道了mysqld_multi是調用mysqladmin來關閉資料庫的，那最好的辦法還是直接使用mysqladmin來關閉各個資料庫了，下面演示使用mysqladmin來關閉資料庫實例。
（6.2）使用mysqladmin來關閉實例
[root@masterdb mysql]# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld3306 is running MySQL server from group: mysqld3307 is running MySQL server from group: mysqld3308 is running MySQL server from group: mysqld3309 is running [root@masterdb mysql]# [root@masterdb mysql]# [root@masterdb mysql]# cd [root@masterdb ~]# mysqladmin -h127.0.0.1 -uroot -p -P3306 shutdown Enter password: [root@masterdb ~]# [root@masterdb ~]# mysqladmin -h127.0.0.1 -uroot -p -P3307 shutdown Enter password: [root@masterdb ~]# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld3306 is not running MySQL server from group: mysqld3307 is not running MySQL server from group: mysqld3308 is running MySQL server from group: mysqld3309 is running
最終關閉了3306和3307資料庫。
【結束】
|
相關文檔集合： 1.在一台Linux伺服器上安裝多個MySQL實例（一）–使用mysqld_multi方式 2.在一台Linux伺服器上安裝多個MySQL實例（二）–使用單獨的MySQL配置文件 3.在一台Linux伺服器上安裝多個MySQL實例（三）–遇到的幾個坑 |
