如何單機部署多個 MySQL 8.0 實體 ?

在服務器資源有限的情況下，可利用該方案快速搭建各類 mysql 架構方案，各 MySQL 實體共享一個 mysqld 主程式，但各實體資料目錄是獨立的，存放在不同的檔案夾中；好了、廢話不多說，直接上干貨，具體搭建步驟如下

環境介紹

部署 MySQL 主程式

1、通過官方二進制包解壓安裝

wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.30-el7-x86_64.tar.gz
tar xzf mysql-8.0.30-el7-x86_64.tar.gz
mv mysql-8.0.30-el7-x86_64 /usr/local/mysql

# vim /etc/profile 添加環境變數，執行 source /etc/profile 使配置在當前 shell 下生效
export PATH=$PATH:/usr/local/mysql/bin/

# 創建各 mysql 服務器實體的資料目錄 datadir
useradd -r -M mysql -s /bin/false
mkdir -p /var/lib/mysql{1..3} && chown mysql.mysql /var/lib/mysql{1..3}

修改 MySQL 實體配置

設定各 mysql 實體的組態檔

cat > /etc/my.cnf << EOF
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
pass = root             # 后續變更各 mysql 實體 root 賬號的初始隨機密碼為簡單密碼 root，因為停止各 mysql 實體時需使用此密碼
log = /var/log/mysql_multi.log

# mysql 實體一
[mysqld1]
server-id = 1
socket = /var/lib/mysql1/mysql.sock
port = 3306
bind_address = 0.0.0.0
datadir = /var/lib/mysql1
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /var/lib/mysql1/mysql.pid
mysqlx = 1        # 設定 0 則禁用 mysqlx, 其默認監聽埠 33060
mysqlx-port = 33060

# mysql 實體二
[mysqld2]
server-id = 2
socket = /var/lib/mysql2/mysql.sock
port = 3307
bind_address = 0.0.0.0
datadir = /var/lib/mysql2
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /var/lib/mysql2/mysql.pid
mysqlx = 1
mysqlx-port = 33070

# mysql 實體三
[mysqld3]
server-id = 3
socket = /var/lib/mysql3/mysql.sock
port = 3308
bind_address = 0.0.0.0
datadir = /var/lib/mysql3
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /var/lib/mysql3/mysql.pid
mysqlx = 1
mysqlx-port = 33080
EOF

初始化 MySQL 實體

1、初始化 MySQL 實體，記錄各實體的初始隨機密碼

/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/var/lib/mysql1
#> 2023-03-15T01:22:52.092218Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.30) initializing of server in progress as process 15026
#> 2023-03-15T01:22:52.119703Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
#> 2023-03-15T01:22:55.237170Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
#> 2023-03-15T01:23:00.616679Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: lqYujqcue7*_

# 同理，初始化其它 mysql 實體（mysql2、mysql3）
/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/var/lib/mysql2
/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/var/lib/mysql3

啟動 mysql 實體

設定多實體啟動程式

cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multid
chmod +x /etc/init.d/mysqld_multid

# 啟動 各 mysql 實體
/etc/init.d/mysqld_multid start
chkconfig mysqld_multid on              # 可選，設定開機啟動

/etc/init.d/mysqld_multid report        # 查看運行情況
#> Reporting MySQL servers
#> MySQL server from group: mysqld1 is running
#> MySQL server from group: mysqld2 is running
#> MySQL server from group: mysqld3 is running

netstat -ntlp | grep mysqld
#> Active Internet connections (only servers)
#> Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
#> tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      19867/mysqld
#> tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN      19869/mysqld
#> tcp        0      0 0.0.0.0:3308            0.0.0.0:*               LISTEN      19876/mysqld
#> tcp6       0      0 :::33080                :::*                    LISTEN      19876/mysqld
#> tcp6       0      0 :::33060                :::*                    LISTEN      19867/mysqld
#> tcp6       0      0 :::33070                :::*                    LISTEN      19869/mysqld

# 停止 各 mysql 實體，需先更新 root 賬號密碼為 root，因為其實作原理是通過 mysqladmin 登錄到各 mysql 實體執行 shutdown
/etc/init.d/mysqld_multid stop

更新 root 賬號密碼

1、使用初始隨機密碼登錄各 MySQL 實體

mysql -u root -p -P3306 -S /var/lib/mysql1/mysql.sock
mysql -u root -p -P3307 -S /var/lib/mysql2/mysql.sock
mysql -u root -p -P3308 -S /var/lib/mysql3/mysql.sock

2、設定 MySQL root 賬號為簡單密碼 root，且允許從任意主機訪問資料庫

-- 做任何操作前，需按照默認安全策略配置一個密碼，才允許后續操作
alter user user() identified by 'Admin@123';
-- 設定密碼策略，否則報錯提示不滿足現有密碼策略，如 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
show global variables like "validate_password%";
set global validate_password.policy=0;              -- validate_password_policy 設定 0 低級 1 中級 2 高級
set global validate_password.length=4;
set global validate_password.check_user_name = 0
set global validate_password.mixed_case_count = 0;
set global validate_password.number_count=0;
set global validate_password.special_char_count=0;

-- 修改 root 賬號為簡單密碼 root
alter user user() identified by 'root';
update mysql.user set host='%' where user='root';
flush privileges;
exit;

參考

MySQL-5.7.x 二進制包，官方下載地址

標籤：其他

上一篇：Bat批處理命令實作一鍵安裝mysql環境

下一篇：[20230323]ps命令查看thread.txt