Mac10.13.6 Mysql5.7.23多例項部署

環境介紹

• Mac 系統為最新的 macOS 10.13.6
• MySQL 是使用官網下載安裝的 mysql -V : Ver 14.14 Distrib 5.7.23, for macos10.13 (x86_64) using EditLine wrapper
• 為什麼不用homebrew安裝，最後會說原因
• 文中mysql的安裝位置為 /usr/local/mysql，如果你的不是，請替換文中的命令路徑

準備工作

本次示例使用 mysqld_multi 安裝6個 MySQL 例項，埠分別為 3307 ~ 3312。

1. 建立各例項的單獨資料目錄，我的目錄在 /usr/local/var，data 目錄是存放資料檔案的，log 目錄是存放慢查詢日誌檔案的，執行如下命令:

mkdir -p mysql_3307/data mysql_3307/log mysql_3308/data mysql_3308/log mysql_3309/data mysql_3309/log mysql_3310/data mysql_3310/log mysql_3311/data mysql_3311/log mysql_3312/data mysql_3312/log

2. 初始化data目錄

這一步也很重要，如果跳過，mysql啟動會報錯導致無法啟動

cd /usr/local/mysql/bin
./mysql_install_db --basedir=/usr/local/mysql  --datadir=/usr/local/var/mysql_3307/data
./mysql_install_db --basedir=/usr/local/mysql  --datadir=/usr/local/var/mysql_3308/data
./mysql_install_db --basedir=/usr/local/mysql  --datadir=/usr/local/var/mysql_3309/data
./mysql_install_db --basedir=/usr/local/mysql  --datadir=/usr/local/var/mysql_3310/data
./mysql_install_db --basedir=/usr/local/mysql  --datadir=/usr/local/var/mysql_3311/data
./mysql_install_db --basedir=/usr/local/mysql  --datadir=/usr/local/var/mysql_3312/data

3. 同理，建立 bin-log 日誌檔案目錄，我的目錄在 /usr/local/var/log

mkdir -p mysql_3307 mysql_3308 mysql_3309 mysql_3310 mysql_3311 mysql_3312

4. 準備配置檔案 /usr/local/etc/my_multi.cnf

[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
pass = 123456  //注意不要寫成password

[mysqld3307]
user = root
server-id  = 3307
port       = 3307
basedir = /usr/local/mysql
socket     = /tmp/mysql_3307.sock
pid-file   = /usr/local/var/mysql_3307/mysql.pid
datadir    = /usr/local/var/mysql_3307/data
log-bin    = /usr/local/var/log/mysql_3307/mysql-bin
binlog_format = mixed
slow_query_log = on
slow_query_log_file = /usr/local/var/log/mysql_3307/slow.log
long_query_time = 1
log-queries-not-using-indexes
log_output = FILE,TABLE
general_log = on
general_log_file = /usr/local/var/log/mysql_3307/general.log

[mysqld3308]
user = root
server-id  = 3308
port       = 3308
basedir = /usr/local/mysql
socket     = /tmp/mysql_3308.sock
pid-file   = /usr/local/var/mysql_3308/mysql.pid
datadir    = /usr/local/var/mysql_3308/data
log-bin    = /usr/local/var/log/mysql_3308/mysql-bin
binlog_format = mixed
slow_query_log = on
slow_query_log_file = /usr/local/var/log/mysql_3308/slow.log
long_query_time = 1
log-queries-not-using-indexes
log_output = FILE,TABLE
general_log = on
general_log_file = /usr/local/var/log/mysql_3308/general.log

[mysqld3309]
user = root
server-id  = 3309
port       = 3309
basedir = /usr/local/mysql
socket     = /tmp/mysql_3309.sock
pid-file   = /usr/local/var/mysql_3309/mysql.pid
datadir    = /usr/local/var/mysql_3309/data
log-bin    = /usr/local/var/log/mysql_3309/mysql-bin
binlog_format = mixed
slow_query_log = on
slow_query_log_file = /usr/local/var/log/mysql_3309/slow.log
long_query_time = 1
log-queries-not-using-indexes
log_output = FILE,TABLE
general_log = on
general_log_file = /usr/local/var/log/mysql_3309/general.log

[mysqld3310]
user = root
server-id  = 3310
port       = 3310
basedir = /usr/local/mysql
socket     = /tmp/mysql_3310.sock
pid-file   = /usr/local/var/mysql_3310/mysql.pid
datadir    = /usr/local/var/mysql_3310/data
log-bin    = /usr/local/var/log/mysql_3310/mysql-bin
binlog_format = mixed
slow_query_log = on
slow_query_log_file = /usr/local/var/log/mysql_3310/slow.log
long_query_time = 1
log-queries-not-using-indexes
log_output = FILE,TABLE
general_log = on
general_log_file = /usr/local/var/log/mysql_3310/general.log

[mysqld3311]
user = root
server-id  = 3311
port       = 3311
basedir = /usr/local/mysql
socket     = /tmp/mysql_3311.sock
pid-file   = /usr/local/var/mysql_3311/mysql.pid
datadir    = /usr/local/var/mysql_3311/data
log-bin    = /usr/local/var/log/mysql_3311/mysql-bin
binlog_format = mixed
slow_query_log = on
slow_query_log_file = /usr/local/var/log/mysql_3311/slow.log
long_query_time = 1
log-queries-not-using-indexes
log_output = FILE,TABLE
general_log = on
general_log_file = /usr/local/var/log/mysql_3311/general.log

[mysqld3312]
user = root
server-id  = 3312
port       = 3312
basedir = /usr/local/mysql
datadir    = /usr/local/var/mysql_3312/data
socket     = /tmp/mysql_3312.sock
pid-file   = /usr/local/var/mysql_3312/mysql.pid
log-bin    = /usr/local/var/log/mysql_3312/mysql-bin
binlog_format = mixed
slow_query_log = on
slow_query_log_file = /usr/local/var/log/mysql_3312/slow.log
long_query_time = 1
log-queries-not-using-indexes
log_output = FILE,TABLE
general_log = on
general_log_file = /usr/local/var/log/mysql_3312/general.log

[mysqld]
dmax_connections = 2000
wait_timeout = 10000

validate_password = off

character_set_server=utf8
init_connect='SET NAMES utf8'

#skip-grant-tables
# Only allow connections from localhost
bind-address = 127.0.0.1

啟動

執行以下命令啟動所有例項：

mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf start

注意，如果通過其他方式安裝過mysql，注意cd到剛才安裝的5.7.23版本目錄下，使用 ./mysqld_multi 來執行，並且將 /usr/local/etc/my_multi.cnf 中配置的 mysqld 和 mysqladmin 的也設定為此路徑下的指令碼，以確保沒有紕漏出現

此時留意當前命令列日誌輸出，每個例項會生成一個隨機的初始密碼，後面第一次登入時需要用到。

可以單獨啟動某個例項或部分例項，只要在命令後面指定具體的例項id即可，例項id為上面配置檔案裡標籤[mysqld3312] 後面的數字:

mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf start 3307
mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf start 3307,3310-3312

同理，停止所有例項：

mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf stop

停止指定例項：

mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf stop 3307,3310-3312

可以使用 report 命令檢視例項執行狀態：

mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf report

Reporting MySQL servers
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running
MySQL server from group: mysqld3310 is running
MySQL server from group: mysqld3311 is running
MySQL server from group: mysqld3312 is running

登入各例項伺服器並修改密碼

登入資料庫需要指定各例項使用的socket檔案，具體檔案如上面配置檔案所示。使用如下命令登入：

mysql -uroot -S/tmp/mysql_3307.sock -p

輸入初始密碼即可登入。登入後需要重置密碼：

use mysql;
update user set authentication_string=PASSWORD("123456") where User='root';
update user set password_expired='N' where User='root';
quit

如果初始密碼沒有記住，可以通過以下方式修改初始密碼

1. 先通過 pa aux | grep mysqld 查詢到指定的mysql例項，然後kill掉此例項
2. 通過以下命令重啟此例項，例如例項3312

/bin/sh /usr/local/mysql/bin/mysqld_safe --user=root --server-id=3312 --port=3312 --basedir=/usr/local/mysql --socket=/tmp/mysql_3312.sock --pid-file=/usr/local/var/mysql_3312/mysql.pid --datadir=/usr/local/var/mysql_3312/data --log-bin=/usr/local/var/log/mysql_3312/mysql-bin --binlog_format=mixed --slow_query_log=on --slow_query_log_file=/usr/local/var/log/mysql_3312/slow.log --long_query_time=1 --log-queries-not-using-indexes --log_output=FILE,TABLE --general_log=on --general_log_file=/usr/local/var/log/mysql_3312/general.log --skip-grant-tables --skip-networking &

3. 無密碼方式登入資料庫

mysql -uroot -S/tmp/mysql_3312.sock -p

4. 修改root使用者密碼和密碼有效性

use mysql;
update user set authentication_string=PASSWORD("123456") where User='root';
update user set password_expired='N' where User='root';
quit

最後之我遇見的坑

我通過以上方式完成了步驟，但是無論如何都啟動不了5個例項，最後通過ps檢視程式發現，所有的mysqld例項使用的 datadir 都是一個預設的相同的路徑，但是我檢視了我的 /usr/local/etc/my_multi.cnf 檔案，明明已經各自指定了不同的 datadir

出現這種情況的時候，我的mysql是通過 brew install mysql 安裝的最新版本 8.0.12，這個版本不僅沒有 mysql_install_db 指令碼導致我安裝data的時候歷經磨難，沒想到最後出現了 mysqld_multi.cnf 指定不了 datadir 這種奇怪的問題。

最後不得已，我從官網下載了 5.7.23 版本，安裝後一次啟動成功，沒有出任何問題。

遺留的問題

我無法通過 mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf stop 來關閉例項，即使我已經正確的配置了 user 和 pass，最後只能通過以下命令關閉：

/usr/local/mysql/bin/mysqladmin -uroot -p123456 -S /tmp/mysql_3307.sock  shutdown