本文適用：rhel9系列，或同類系統(tǒng)(CentOS9,AlmaLinux9,RockyLinux9等)
文檔形成時(shí)期：2023年
因系統(tǒng)版本不同，構(gòu)建部署應(yīng)略有差異，但本文未做細(xì)分，對稍有經(jīng)驗(yàn)者應(yīng)不存在明顯障礙。
因軟件世界之復(fù)雜和個(gè)人能力之限，難免疏漏和錯(cuò)誤，歡迎指正。

背景

不同時(shí)期因各種原因經(jīng)常產(chǎn)生部署LNMP環(huán)境的需求，某些場景下需要自定義軟件，比如參數(shù)、模塊、安裝路徑，或多個(gè)版本共存，不能采用Docker等容器環(huán)境，采用自主構(gòu)建RPM包便成了比較快捷的方式之一。
在網(wǎng)上幾乎沒有發(fā)現(xiàn)有基于RHEL9系列自主構(gòu)建MySQL9的RPM包，采用DNF安裝的比較多，實(shí)踐之后特地分享于眾，歡迎指正或探討。

環(huán)境準(zhǔn)備

yum install rpmdevtools
#創(chuàng)建目錄
rpmdev-setuptree
#或手動(dòng)創(chuàng)建目錄：

mkdir rpmbuild-mysql-8.0.32; cd rpmbuild-mysql-8.0.32
mkdir -p ./{BUILD,RPMS,SOURCES,SPECS,SRPMS}

準(zhǔn)備工作

準(zhǔn)備好mysql-boost-8.0.32.tar.gz，官網(wǎng)下載：https://downloads.mysql.com/archives/community/，它包含mysql-8.0.32.tar.gz的所有文件，并多出boost。
準(zhǔn)備好my.cnf，配置建議參考生產(chǎn)環(huán)境的常用配置，做成一個(gè)比較通用的；
示例：

[client]
port=3306
socket=/opt/mysql8/tmp/mysql.sock

[mysqld]
user=mysql
# skip-symbolic-links或symbolic-links=0不再建議被使用
# symbolic-links=0
# skip-grant-tables
# mysql8.0.32推薦使用authentication_policy代替原default_authentication_plugin
authentication_policy=mysql_native_password
port=3306
socket=/opt/mysql8/tmp/mysql.sock
pid-file=/opt/mysql8/tmp/mysqld.pid
basedir=/opt/mysql8
datadir=/opt/mysql8/data
tmpdir=/opt/mysql8/tmp


### 密碼復(fù)雜度控制參數(shù)開始 ###
# mysql啟用密碼復(fù)雜度控制
# 密碼復(fù)雜度這部分以插件的方式安裝配置，在mysql8能用，但官方推薦mysql8改為組件，將來會(huì)棄用插件的方式。
# 先在mysql終端里啟用組件，
# UNINSTALL COMPONENT 'file://component_validate_password';
# INSTALL COMPONENT 'file://component_validate_password';
# SELECT * FROM mysql.component;
# SET GLOBAL validate_password.check_user_name=ON;
# SET GLOBAL validate_password.dictionary_file="";
# SET GLOBAL validate_password.length=8;
# SET GLOBAL validate_password.mixed_case_count=1;
# SET GLOBAL validate_password.number_count=1;
# SET GLOBAL validate_password.policy=1;
# SET GLOBAL validate_password.special_char_count=0;
# SHOW GLOBAL VARIABLES LIKE 'validate_password%';

#然后可在my.cnf中啟用相應(yīng)的配置（命令行中未啟用密碼驗(yàn)證組件前，啟用下面的配置會(huì)報(bào)錯(cuò)，應(yīng)該先啟用后再取消下面配置的注釋）：
##plugin-load=validate_password.so # mysql5.7和mysql8均可用，但mysql8提示將棄用
##validate-password=FORCE_PLUS_PERMANENT # mysql8不支持
# validate_password.policy=1


# 檢查用戶名
# validate_password.check_user_name=ON

# 密碼策略文件，策略為STRONG才需要 
# validate_password.dictionary_file=

# 密碼最少長度 
# validate_password.length=8

# 大小寫字符長度，各至少1個(gè)，共至少2個(gè)
# validate_password.mixed_case_count=1

# 數(shù)字至少1個(gè)
# validate_password.number_count=1

# 特殊字符至少1個(gè)上述參數(shù)是默認(rèn)策略MEDIUM的密碼檢查規(guī)則。
# validate_password.special_char_count=0
### 密碼復(fù)雜度控制參數(shù)結(jié)束 ###


ft_min_word_len=4
event_scheduler=1
max_allowed_packet=128M
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

explicit_defaults_for_timestamp=true

#skip-networking
wait_timeout=1800
interactive_timeout=1800
open_files_limit=65535
back_log=512
connect-timeout=300
net_write_timeout=300
net_read_timeout=300
max_connections=5000
max_connect_errors=64

table_open_cache=2048
table_definition_cache=2048
max_heap_table_size=128M
tmp_table_size=128M
sort_buffer_size=32M
join_buffer_size=32M
thread_cache_size=1024
#query_cache_size=128M
#query_cache_limit=8M
#query_cache_min_res_unit=4k
thread_stack=192K
read_buffer_size=16M
read_rnd_buffer_size=8M
bulk_insert_buffer_size=64M

#external-locking
default-storage-engine=innodb
log-error=/opt/mysql8/var/error.log
#log_warnings=2
log_error_verbosity=2
slow-query-log
slow-query-log-file=/opt/mysql8/var/slow.log
log_slow_admin_statements
long_query_time=5
log-queries-not-using-indexes=0

#server-id=1
#binlog_format=ROW
#log-bin=/opt/mysql8/var/mysql-bin
#binlog_cache_size=512M
#max_binlog_cache_size=2G
#max_binlog_size=1G
#expire_logs_days=7
#relay-log-purge=1
#sync_binlog=0

#binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

key_buffer_size=128M
myisam_sort_buffer_size=128M
# mysql8.0.33不支持myisam_repair_threads
#myisam_repair_threads=1
myisam-recover-options
# docker容器中初始化時(shí)使用lower_case_table_names=1會(huì)報(bào)錯(cuò)：The designated data directory /var/lib/mysql/ is unusable
lower_case_table_names=1
skip-name-resolve
myisam_max_sort_file_size=4G



### innodb配置 ###
innodb_buffer_pool_size=1024M
innodb_data_file_path=ibdata1:128M:autoextend
innodb_file_per_table=1
innodb_write_io_threads=4
innodb_read_io_threads=4
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=128M

# 8.0.30之后，innodb_log_file_size and/or innodb_log_files_in_group提示將棄用，改用innodb_redo_log_capacity
#innodb_log_file_size=32M
#innodb_log_files_in_group=2
innodb_redo_log_capacity=128MB

innodb_max_dirty_pages_pct=85
innodb_rollback_on_timeout
innodb_status_file=1
innodb_io_capacity=800
transaction_isolation=READ-COMMITTED
innodb_flush_method=O_DIRECT
#innodb_file_format=Barracuda
innodb_use_native_aio=1
innodb_lock_wait_timeout=120



[mysqldump]
quick
max_allowed_packet=128M
column-statistics=0


[mysql]
no-auto-rehash
#safe-updates
prompt="\\u@\\h: \\d \\R:\\m:\\s>"


[myisamchk]
key_buffer_size=32M
sort_buffer_size=32M
#read_buffer=8M
#write_buffer=8M


[mysqlhotcopy]
interactive-timeout


[mysqld_safe]
open-files-limit=8192
log-error=/opt/mysql8/var/error.log
pid-file=/opt/mysql8/tmp/mysqld.pid

mysql-8.0.32-el9.spec內(nèi)容

Name:           mysql
Version:        8.0.32
Release:        custom%{?dist}
Summary:        www.mysql.com

Group:          Applications/Databases
License:        GPLv3+
URL:            https://www.mysql.com
Source0:        mysql-boost-8.0.32.tar.gz

BuildRequires:  gcc
#Requires:


%define debug_package %{nil}
%define _prefix  /opt/mysql8
Prefix:     %{_prefix}


%description


%prep
%setup -q

%build
cmake -DCMAKE_INSTALL_PREFIX=%{_prefix} \
-DMYSQL_UNIX_ADDR=%{_prefix}/tmp/mysql.sock \
-DSYSCONFDIR=%{_prefix}/etc \
-DSYSTEMD_PID_DIR=%{_prefix} \
-DDEFAULT_charset=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_unicode_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_INNODB_MEMCACHED=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_SSL=system -DWITH_READLINE=on \
-DMYSQL_DATADIR=%{_prefix}/data \
-DWITH_BOOST=./boost -DWITH_SYSTEMD=1 \
-DFORCE_INSOURCE_BUILD=1 \
-DENABLED_PROFILING=ON \
-DMYSQL_TCP_PORT=3306 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_MAINTAINER_MODE=OFF \
-DWITH_DEBUG=OFF


make %{?_smp_mflags}




#
# Installation section
#
%install
[ %{buildroot} != "/" ] && rm -rf %{buildroot}
make install DESTDIR=%{buildroot}

%__install -c -d -m 755 "%{buildroot}%{_prefix}/etc"
cp -f %_sourcedir/my.cnf "%{buildroot}%{_prefix}/etc/"





#
# Clean section
#

%clean
[ %{buildroot} != "/" ] && rm -rf "%{buildroot}"





%files
%{_prefix}
%doc
#/usr/lib/systemd/system/mysqld.service # 因?yàn)閙ysqld.service是安裝后產(chǎn)生的，所以這里暫時(shí)不寫，不然構(gòu)建會(huì)報(bào)錯(cuò)






#安裝后執(zhí)行的命令
%post
if [ $1 == 1 ];then
    mv /etc/my.cnf /etc/my.cnf.bak-`date +"%%Y%%m%%d-%%H%%M%%S"`
    cat /dev/null > /etc/my.cnf
    chattr +i /etc/my.cnf

    sed -i '/export PATH/i\PATH=/opt/mysql8/bin:"$PATH"' ~/.bashrc   # rhel9
    source  ~/.bashrc

    groupadd mysql -g 318
    useradd -s /sbin/nologin -M mysql -u 318 -g 318
    mkdir /home/mysql
    chown mysql:mysql /home/mysql

    mkdir /opt/mysql8/{tmp,var,data}; chown mysql:mysql -R /opt/mysql8/{tmp,var,data}
    chown mysql:mysql /opt/mysql8/etc/my.cnf

    sed '1i\/opt\/mysql8\/lib' /etc/ld.so.conf
    ldconfig
    #或
    ln -s /opt/mysql8/lib/libmysqlclient.so.21 /usr/lib/libmysqlclient.so.21
    
    cp /opt/mysql8/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
    systemctl daemon-reload
    systemctl enable mysqld

    mysqld --initialize-insecure

    echo "------------------------------------------------------------------------------------------------------------"
    echo -e  "|  \033[32mInitialization is complete.\033[0m"
    echo "------------------------------------------------------------------------------------------------------------"
fi

 



#卸載前執(zhí)行的命令
%preun
if [ "$1" = 0 ]
then
    systemctl disable mysqld
    systemctl stop mysqld
    # userdel -r mysql
    cp -r %{_prefix}/etc /opt/mysql8_my.cnf.rpmsave-`date +"%%Y%%m%%d-%%H%%M%%S"`
fi





#卸載后執(zhí)行的命令
%postun
if [ "$1" = 0 ]
then
    systemctl disable mysqld
    rm -f /usr/lib/systemd/system/mysqld.service
    rm -rf /opt/mysql8
    echo "%{name}-%{version}-%{release} uninstalled."
fi





%changelog
* Sat Dec 16 2023 N
- For the first time, Custom made MySQL8.0.32 in AlmaLinux9.2.

說明：

• RPM包安裝后會(huì)完成初始化，使用空密碼，請務(wù)必配置好root權(quán)限；
• 卸載時(shí)會(huì)把配置追加時(shí)間后綴保存在/opt，會(huì)清理/opt/mysql8，其包含數(shù)據(jù)庫文件，請務(wù)必謹(jǐn)慎。

文件位置

文件位置參考MySQL8.0.32構(gòu)建后的目錄樹：

構(gòu)建

rpmbuild --define "_topdir `pwd`" --nodebuginfo -ba SPECS/mysql-8.0.32-el9.spec

構(gòu)建時(shí)間：在一臺2核8G的機(jī)子上，構(gòu)建約2小時(shí)，和4核的對比，時(shí)間多了一倍，看來spec中的make的參數(shù)確實(shí)是根據(jù)核數(shù)有變化，但要注意核心數(shù)和內(nèi)存大小GB數(shù)比例最好是1:3以上。

遇錯(cuò)：collect2: fatal error: ld terminated with signal 9 [Killed]
原因：內(nèi)存不足

構(gòu)建成功的包如下：
mysql-8.0.32-custom.el9.x86_64.rpm

安裝和卸載

dnf localinstall mysql-8.0.32-custom.el9.x86_64.rpm
dnf remove mysql-8.0.32-custom.el9.x86_64

目標(biāo)服務(wù)器其他配置參考

啟動(dòng)MySQL服務(wù)

systemctl enable mysqld
systemctl start mysqld

查看默認(rèn)的配置路徑和變量來源

mysqld --verbose --help| grep -A 1 "Default options"

要注意，mysql運(yùn)行用戶要能讀取到my.cnf，如果系統(tǒng)umask配置的027，就要注意了，實(shí)踐中就遇到了這個(gè)問題，排查了好久才知曉。

-- 查看變量來源
SELECT t1.*, VARIABLE_VALUE 
       FROM performance_schema.variables_info t1 
       JOIN performance_schema.global_variables t2 
         ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
      WHERE t1.VARIABLE_NAME LIKE 'lower_case_table_names'\G

安全配置

mysql_secure_installation

修改密碼簡要示例

以下是相關(guān)命令：[請參考官網(wǎng)的示例]文章來源地址http://www.zghlxwxcb.cn/news/detail-819185.html

-- 修改root用戶密碼時(shí)，用root用戶登陸后設(shè)置：
set password="pass";
CREATE USER "root"@"%" IDENTIFIED BY "pass";
GRANT ALL PRIVILEGES ON *.* TO "root"@"%" WITH GRANT OPTION;

CREATE USER ""@"%" IDENTIFIED WITH mysql_native_password BY "";
GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables, Create View, Delete, Drop, Event, Execute, Index, Insert, Lock Tables, References, Select, Show View, Trigger, Update ON ``.* TO ``@`%`;

FLUSH PRIVILEGES;
SELECT host,user,authentication_string FROM mysql.user ORDER BY user;

安裝密碼策略控制的安全組件

UNINSTALL COMPONENT 'file://component_validate_password';
INSTALL COMPONENT 'file://component_validate_password';
SELECT * FROM mysql.component;
SET GLOBAL validate_password.check_user_name=ON;
SET GLOBAL validate_password.dictionary_file="";
SET GLOBAL validate_password.length=12;
SET GLOBAL validate_password.mixed_case_count=1;
SET GLOBAL validate_password.number_count=1;
SET GLOBAL validate_password.policy=1;
SET GLOBAL validate_password.special_char_count=1;
SHOW GLOBAL VARIABLES LIKE 'validate_password%';

到了這里，關(guān)于構(gòu)建基于RHEL9系列(CentOS9,AlmaLinux9,RockyLinux9等)的MySQL8.0.32的RPM包的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！