適用環境：
資料庫版本：MySQL 5.7.29 作業系統：CentOS 7
制作思路：
將資料庫初始化和配置作業放到安裝腳本中方便定制： 1、打包MySQL應用目錄 2、不自動生成組態檔 3、不自動生成資料目錄 4、不自動初始化資料
制作步驟：
1、安裝依賴包
yum -y install gperf zlib-devel time ncurses-devel gcc* cmake libaio-devel rpm-build
2、創建打包用戶和目錄
## 創建打包用戶 ##
useradd rpmbuilder
## 切換到 rpmbuilder用戶 ##
su rpmbuilder
## 使用rpmbuild創建相關檔案夾 ##
rpmbuild ~
3、上傳SRC包檔案并安裝
## 安裝RPM包后會將
rpm -ivh mysql-community-5.7.29-1.el7.src.rpm
## 查看原始碼目錄大小
## 因為包含mysql-5.6.45+mysql-5.7.29+boost_1_59_0三個包，因此整個SOURCE目錄較大
cd /home/rpmbuilder/rpmbuild
du -sh *
4、下載Cmake包并上傳至目標服務器并安裝
## Cmake下載路徑：https://cmake.org/download/ ## 上傳至隨意目標路徑并解壓 ## 本例中解壓到/tmp/cmake-3.17.1-Linux-x86_64 ## mysql.spec檔案中有使用到該路徑 tar -xvf cmake-3.17.1-Linux-x86_64.tar.gz
5、編輯mysql.spec檔案并替換
## 切換到 SPECS 目錄，然后上傳 mysql.spec 檔案 cd /home/rpmbuilder/rpmbuild/SPECS
## 主要修改mysql.spec的各類檔案路徑
6、制作RPM包
## 制作 RPM 包 cd /home/rpmbuilder/rpmbuild/SPECS rpmbuild -bb mysql.spec
7、制作完成后，會在/home/rpmbuilder/rpmbuild/RPMS/x86_64目錄下生成安裝包
Spec代碼：
# ========================================= # # MySQL引數配置 # mysql原始碼路徑 # 原始碼包版本 %global src_base mysql %global version 5.7.29 %global src_dir %{src_base}-%{version} %global cmake_bin /tmp/cmake-3.17.1-Linux-x86_64/bin/cmake Name: %{src_base} Version: %{version} # 安裝版本 Release: 1 # 安裝包介紹 Summary: MySQL Server # 遵循協議 License: GPLv2 # 打包需要 # BuildRequires: cmake /sbin/useradd /sbin/groupadd /bin/bash /bin/sh /bin/chown # 安裝需要 # Requires: ncurses ncurses-devel pcre numactl-libs numactl bison libaio libaio-devel # 安裝的虛擬環境，在rpmbuild/BUILDROOT目錄下 BuildRoot: %(mktemp -ud %{_tmppath}/%{name}-%{version}-XXXXXX) # Sorce資訊 Source0: %{name}-%{version}.tar.gz Source10: boost_1_59_0.tar.bz2 Source90: filter-provides.sh Source91: filter-requires.sh %if 0%{?rhel} > 6 # For rpm => 4.9 only: https://fedoraproject.org/wiki/Packaging:AutoProvidesAndRequiresFiltering %global __requires_exclude ^perl\\((GD|hostnames|lib::mtr|lib::v1|mtr_|My::) %global __provides_exclude_from ^(/usr/share/(mysql|mysql-test)/.*|%{_libdir}/mysql/plugin/.*\\.so)$ %else # https://fedoraproject.org/wiki/EPEL:Packaging#Generic_Filtering_on_EPEL6 %global __perl_provides %{SOURCE90} %global __perl_requires %{SOURCE91} %endif # mysql安裝目錄 %define mysql_server_path /apps/srv/mysql/server # mysql資料目錄 %define mysql_data_path /apps/data/mysql/data %define MYSQL_USER mysql %define MYSQL_GROUP mysql # mysql組態檔目錄 %define mysql_conf_path /apps/srv/mysql/server/etc/my.cnf # mysql描述 %description The MySQL(TM) software delivers a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server. MySQL Server is intended for mission-critical, heavy-load production systems as well as for embedding into mass-deployed software. # ========================================= # # 環境準備 %prep rm -rf $RPM_BUILD_ROOT/%{name}-%{version} # setup把boost和mysql壓縮包解壓到下面路徑 # ./rpmbuild/BUILD/mysql-5.7.28/mysql-5.7.28/ # ./rpmbuild/BUILD/mysql-5.7.28/boost_1_59_0/ %setup -q -T -a 0 -a 10 -c -n %{src_dir} groupadd %{MYSQL_GROUP} >/dev/null 2>&1 || : useradd -g %{MYSQL_GROUP} %{MYSQL_USER} -s /bin/nologin >/dev/null 2>&1 || : # ========================================= # # 編譯 %build %{cmake_bin} ./%{src_dir} \ -DCMAKE_INSTALL_PREFIX=%{mysql_server_path} \ -DMYSQL_DATADIR=%{mysql_data_path} \ -DSYSCONFDIR=%{mysql_server_path}/etc \ -DDEFAULT_CHARSET=utf8mb4 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DMYSQL_UNIX_ADDR=%{mysql_data_path}/tmp/mysql.sock \ -DMYSQL_TCP_PORT=3306 \ -DMYSQL_USER=mysql \ -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DENABLE_DOWNLOADS=1 \ -DDOWNLOAD_BOOST=1 \ -DWITH_BOOST=. make -j3 %{?_smp_mflags} # ========================================= # # 安裝 %install rm -rf %{buildroot} make DESTDIR=%{buildroot} install # ========================================= # # 制作RPM包 %files %defattr(-, %{MYSQL_USER}, %{MYSQL_GROUP}) %attr(755, %{MYSQL_USER}, %{MYSQL_GROUP}) %{mysql_server_path}/* %changelog
安裝腳本(主要修改賬號)：
#!/bin/bash ##========================================================## ## variables: mysql_rpm_package_name="mysql-5.7.28-el7.rpm" mysql_data_path="/apps/data/mysql/data" mysql_server_path="/apps/srv/mysql/server" mysql_cnf_path=${mysql_server_path}/etc/my.cnf install_script_dir="$( cd "$( dirname "$0" )" && pwd )" install_log="${install_script_dir}/install.log" ##========================================================## ## return code error_input_parameters=10 error_check_enviorment=11 error_install_mysql_dependence=12 error_install_mysql_package=13 error_mysql_install_db=14 error_start_mysql_service=15 error_grant_mysql_user=15 ##========================================================## function echo_info() { message=$1 echo -e "\033[;37;32m ${message} \033[0m" } function echo_error() { message=$1 echo -e "\033[;37;31m ${message} \033[0m" } function check_env() { mysql_count=`rpm -qa|grep 'mysql-' |grep -v grep |wc -l` if [ $mysql_count -eq 0 ];then echo_info 'check rpm install pass' else echo_error "mysql has been installed!!!" exit ${error_check_enviorment} fi if [ ! -d "${mysql_data_path}" ] then echo_info "${mysql_data_path} not exist" else echo_error "${mysql_data_path} exist!!!" exit ${error_check_enviorment} fi if [ ! -d "${mysql_server_path}" ] then echo_info "${mysql_server_path} not exist" else echo_error "${mysql_server_path} exist!!!" exit ${error_check_enviorment} fi } function set_scheduler() { echo_info "Please change io scheduler by yourself." } function set_memory_swap() { echo_info "set memory swap" WCONF=`cat /etc/rc.local |grep swappiness |grep -v grep |wc -l` if [ $WCONF -eq 0 ];then echo "echo '1' > /proc/sys/vm/swappiness" >> /etc/rc.local echo '1' > /proc/sys/vm/swappiness fi } function install_mysql_dependence() { echo_info "install mysql dependence" rpm -e mysql-devel > /dev/null 2>&1 rpm -e mysql > /dev/null 2>&1 yum install -y bc ncurses ncurses-devel glibc gcc gcc-c++ libstdc++* libtool sysstat lrzsz cmake zlib > /dev/null 2>&1 yum -y install perl perl-JSON perl-Time-HiRes > /dev/null 2>&1 if [ $? -eq 0 ]; then echo_info "insatll mysql dependence success" else echo_error "insatll mysql dependence faild" exit ${error_install_mysql_package} fi } function create_mysql_os_user() { echo_info "create user mysql" groupadd mysql 2> /dev/null || true useradd mysql -g mysql 2> /dev/null || true echo_info "config file limit for mysql" echo "mysql soft nofile 65536" >> /etc/security/limits.conf echo "mysql hard nofile 65536" >> /etc/security/limits.conf echo "mysql soft noproc 16384" >> /etc/security/limits.conf echo "mysql hard noproc 16384" >> /etc/security/limits.conf } function install_mysql_package() { # yum install -y "mysql-5.5 cd "${install_script_dir}" rpm -ivh "${install_script_dir}/${mysql_rpm_package_name}" 1>>${install_log} 2>&1 if [ $? -eq 0 ]; then echo_info "insatll mysql rpm package success" else echo_error "insatll mysql rpm package faild" exit ${error_install_mysql_package} fi } function set_bash_profile() { echo "export LANG=zh_CN.UTF-8" >> /home/mysql/.bash_profile echo 'PATH=$PATH:'${mysql_server_path}'/bin!' >> /home/mysql/.bash_profile echo 'export PATH' >> /home/mysql/.bash_profile } function create_my_cnf() { echo_info "create mysql config" mkdir -p "${mysql_server_path}/etc/" >/dev/null server_id=$(ip a |grep 'inet'|grep 'brd'|grep -v '127.0.0.1' |head -n 1 | awk '{print $2}' |awk -F'/' '{print $1}' |awk -F. '{print $2$3$4}') dockerflag=`ps -ef|grep "sleep 99999"|grep -v grep|wc -l` if [ $dockerflag -gt 0 ];then dock_info=/etc/config_info if [ -f $dock_info ];then mem=`cat /etc/config_info|awk -F 'Memory":' '{print $2}'|awk -F '}' '{print $1}'|awk -F ',' '{print $1}'` mem_gb=`echo "$mem/1024/1024/1024"|bc` else echo "Error: Docker env,can't find $dock_info.." exit 1 fi else mem=`cat /proc/meminfo |grep MemTotal|awk '{print $2}'` mem_gb=`echo "$mem/1024/1024"|bc` fi if [ $mem_gb -gt 128 ];then let pool_size=$mem_gb-30 elif [ $mem_gb -gt 96 -a $mem_gb -le 128 ];then let pool_size=$mem_gb-20 elif [ $mem_gb -gt 64 -a $mem_gb -le 96 ];then let pool_size=$mem_gb-15 elif [ $mem_gb -gt 32 -a $mem_gb -le 64 ];then let pool_size=mem_gb-10 elif [ $mem_gb -gt 16 -a $mem_gb -le 32 ];then let pool_size=$mem_gb-5 elif [ $mem_gb -gt 8 -a $mem_gb -le 16 ];then let pool_size=$mem_gb-2 elif [ $mem_gb -gt 4 -a $mem_gb -le 8 ];then let pool_size=$mem_gb-1 else pool_size=1 fi innodb_buffer_pool_size=${pool_size} echo_info "innodb_buffer_pool_size: ${innodb_buffer_pool_size}" cat > ${mysql_cnf_path} << EOF [clent] port = 3306 socket = ${mysql_data_path}/tmp/mysql.sock [mysqld] port = 3306 socket = ${mysql_data_path}/tmp/mysql.sock datadir = ${mysql_data_path}/data/ #--- GLOBAL ---# log_timestamps = SYSTEM character-set-server = utf8 lower_case_table_names = 1 log-output = FILE log-error = ${mysql_data_path}/log/error.log #general_log #general_log_file = ${mysql_data_path}/log/mysql.log pid-file = ${mysql_data_path}/mysql.pid slow-query-log slow_query_log_file = ${mysql_data_path}/log/slow.log tmpdir = ${mysql_data_path}/tmp long_query_time = 0.1 sync_binlog = 1 log_timestamps = SYSTEM transaction_isolation = READ-COMMITTED default_storage_engine = InnoDB #--------------# #thread_concurrency = 16 thread_cache_size = 512 table_open_cache = 16384 table_definition_cache = 16384 sort_buffer_size = 4M join_buffer_size = 4M read_buffer_size = 4M read_rnd_buffer_size = 4M key_buffer_size = 64M myisam_sort_buffer_size = 64M tmp_table_size = 32M max_heap_table_size = 32M open_files_limit = 65535 query_cache_size = 0 query_cache_type = 0 bulk_insert_buffer_size = 64M binlog_rows_query_log_events =on sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off' #--- NETWORK ---# back_log = 1024 max_allowed_packet = 256M interactive_timeout = 28800 wait_timeout = 28800 skip-external-locking max_connections = 3000 max_connect_errors = 10000 skip-name-resolve = 1 read_only = 0 #--- REPL ---# server-id = ${server_id} log-bin = mysql-bin master_info_repository = TABLE binlog_format = ROW binlog_cache_size = 4M expire_logs_days = 7 replicate-ignore-db = test log_slave_updates = 1 slave-parallel-workers = 8 slave-parallel-type = LOGICAL_CLOCK slave_preserve_commit_order = 0 skip-slave-start gtid_mode = on enforce-gtid-consistency = true relay-log = relay-log relay_log_recovery = ON sync_relay_log = 0 relay_log_info_repository = TABLE #--- INNODB ---# default-storage-engine = INNODB innodb_data_home_dir = ${mysql_data_path}/data innodb_data_file_path = ibdata1:1024M:autoextend innodb_file_per_table innodb_log_group_home_dir = ${mysql_data_path}/data innodb_buffer_pool_size = ${innodb_buffer_pool_size}G #innodb_additional_mem_pool_size = 128M innodb_log_files_in_group = 3 innodb_log_file_size = 1024M innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 120 innodb_flush_method = O_DIRECT innodb_max_dirty_pages_pct = 75 innodb_io_capacity = 1000 innodb_open_files = 65535 innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_print_all_deadlocks = 1 innodb_undo_directory = ${mysql_data_path}/undo innodb_purge_threads = 4 innodb_purge_batch_size = 400 innodb_stats_on_metadata = 0 innodb_page_cleaners = 4 innodb_purge_threads = 4 innodb_buffer_pool_instances = 8 [mysqldump] #quick max_allowed_packet = 256M [mysql] # auto-rehash # Remove the next comment character if you are not familiar with SQL # safe-updates default-character-set=utf8 [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout EOF echo_info "mysql config file is created" } function mysql_install_db() { echo_info "run mysql_install_db" /bin/rm -rf ${mysql_data_path} mkdir -p ${mysql_data_path}/data/ ${mysql_data_path}/tmp/ ${mysql_data_path}/log/ chown -R mysql:mysql ${mysql_server_path} ${mysql_data_path} ${mysql_server_path}/bin/mysqld --initialize --user=mysql --basedir=${mysql_server_path} --datadir=${mysql_data_path}/data/ --initialize-insecure 1>>${install_log} 2>&1 if [ $? -eq 0 ]; then echo_info "run mysql_install_db success" else echo_error "run mysql_install_db faild" exit ${error_mysql_install_db} fi } function start_mysql_service() { echo_info "start mysql service" su - mysql -c "${mysql_server_path}/bin/mysqld_safe --defaults-file=${mysql_server_path}/etc/my.cnf 1>/dev/null 2>&1 &" for i in {1..60};do [ -S ${mysql_data_path}/tmp/mysql.sock ] && echo_info "mysql service is OK" && break || echo -n ". ";sleep 2;done if [ ! -S ${mysql_data_path}/tmp/mysql.sock ];then echo_error "Start Faild,See ${mysql_data_path}/log/error.log"; exit ${error_start_mysql_service}; fi } function grant_mysql_user() { echo_info "grant mysql user" "${mysql_server_path}/bin/mysql" -uroot -e " ##==============================================================================## ## DBA GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '[email protected]' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '[email protected]' WITH GRANT OPTION; set session binlog_format = statement; delete from mysql.user where user=''; delete from mysql.user where authentication_string=''; set session binlog_format = row; " if [ $? -eq 0 ]; then echo_info "grant mysql user success" else echo_error "grant mysql user faild" exit ${error_grant_mysql_user} fi } function set_network_config() { echo_info "config network" echo " net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_tw_recycle = 1 net.ipv4.tcp_fin_timeout = 30 " >>/etc/sysctl.conf sysctl -p 1>/dev/null 2>&1 } check_env set_scheduler set_memory_swap set_network_config install_mysql_dependence create_mysql_os_user install_mysql_package set_bash_profile create_my_cnf mysql_install_db start_mysql_service grant_mysql_user
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/89081.html
標籤：MySQL
上一篇：mysql 行級鎖問題