keepalived安装脚本初始化 keepalived安装脚本初始化#!/bin/bash # ************************************* # * 功能: keepalived安装部署脚本 # * 作者: 刘丹玉 # * 联系: v649352141@163.com # * 版本: 2025-05-24 # ************************************* # 错误处理：如果命令执行失败，脚本将终止 set -e # 定义日志文件路径 # LOG_FILE="/var/log/keepalived_install.log" # | tee -a ${LOG_FILE} # 定义环境变量 KEEPALIVED_VERSION="2.3.3" KEEPALIVED_INSTALL_PATH="/data/server/keepalived" KEEPALIVED_TAR_NAME="keepalived-${KEEPALIVED_VERSION}" read -p "当前节点的角色(MASTER|BACKUP): " KEEPALIVED_ROLE # 查看属于Rocky、Ubuntu、openEuler系列 os_type () { awk -F'[ "]' '/^NAME/{print $2}' /etc/os-release } # 定义日志记录函数 function log() { local timestamp=$(date "+%Y-%m-%d %H:%M:%S") local message="$1" echo "[${timestamp}] ${message}" } # 定义函数：基本环境定制 function setup_environment() { log "开始基本环境定制" echo "正在更新软件包列表..." if [ $(os_type) == "Rocky" ];then yum install -y gcc curl openssl-devel libnl3-devel net-snmp-devel rsyslog >/dev/null 2>&1 elif [ $(os_type) == "Ubuntu" ] ;then if [ $? -eq 0 ]; then log "软件包列表更新成功" else log "软件包列表更新失败" exit 1 fi echo "正在安装必要的软件包..." apt update >/dev/null 2>&1 && apt -y install make gcc \ ipvsadm build-essential pkg-config automake autoconf libipset-dev \ libnl-3-dev libnl-genl-3-dev libssl-dev libxtables-dev libip4tc-dev libip6tc-dev \ libmagic-dev libsnmp-dev libglib2.0-dev libpcre2-dev libnftnl-dev libmnl-dev libsystemd-dev \ libpopt-dev daemon >/dev/null 2>&1 if [ $? -eq 0 ]; then log "必要软件包安装成功" else log "必要软件包安装失败" exit 1 fi else color "$(os_type) 暂不支持此版本" 3 fi } # 定义函数：下载软件 function download_software() { log "开始下载软件" echo "正在创建目录并下载 Keepalived ${KEEPALIVED_VERSION}..." mkdir -p /data/{server,softs} if [ $? -eq 0 ]; then log "目录创建成功" else log "目录创建失败" exit 1 fi cd /data/softs if [ ! -f ${KEEPALIVED_TAR_NAME}.tar.gz ]; then keepalived_url="https://keepalived.org/software/${KEEPALIVED_TAR_NAME}.tar.gz" wget "${keepalived_url}" >/dev/null 2>&1 if [ $? -eq 0 ]; then log "Keepalived ${KEEPALIVED_VERSION} 下载成功" else log "Keepalived ${KEEPALIVED_VERSION} 下载失败" exit 1 fi fi echo "正在解压 Keepalived ${KEEPALIVED_VERSION}..." untar_dir="/data/softs/${KEEPALIVED_TAR_NAME}" [ -d ${untar_dir} ] && rm -rf ${untar_dir} tar xf "${KEEPALIVED_TAR_NAME}.tar.gz" if [ $? -eq 0 ]; then log "Keepalived ${KEEPALIVED_VERSION} 解压成功" else log "Keepalived ${KEEPALIVED_VERSION} 解压失败" exit 1 fi } # 定义函数：编译安装 function compile_and_install() { log "开始编译安装" echo "正在进入解压目录并进行配置..." cd "${KEEPALIVED_TAR_NAME}" ./configure --prefix="${KEEPALIVED_INSTALL_PATH}" >/dev/null 2>&1 if [ $? -eq 0 ]; then log "配置成功，安装路径为 ${KEEPALIVED_INSTALL_PATH}" else log "配置失败，安装路径为 ${KEEPALIVED_INSTALL_PATH}" exit 1 fi echo "正在编译 Keepalived ${KEEPALIVED_VERSION}..." make -j $(nproc) >/dev/null 2>&1 if [ $? -eq 0 ]; then log "编译成功" else log "编译失败" exit 1 fi echo "正在安装 Keepalived ${KEEPALIVED_VERSION} 到 ${KEEPALIVED_INSTALL_PATH}..." make install >/dev/null 2>&1 if [ $? -eq 0 ]; then log "安装成功，安装路径为 ${KEEPALIVED_INSTALL_PATH}" else log "安装失败，安装路径为 ${KEEPALIVED_INSTALL_PATH}" exit 1 fi } # 定义函数：定制服务文件 function customize_service_file() { log "开始定制服务文件" echo "正在复制服务文件..." cd /data/softs/${KEEPALIVED_TAR_NAME}/keepalived cp keepalived.service /usr/lib/systemd/system/keepalived.service if [ $? -eq 0 ]; then log "服务文件复制成功" else log "服务文件复制失败" exit 1 fi } # 定义函数：定制配置文件 function customize_config_file() { log "开始定制配置文件" echo "正在定制配置文件..." cd "${KEEPALIVED_INSTALL_PATH}/etc/keepalived/" echo "正在导入man手册..." cp ${KEEPALIVED_INSTALL_PATH}/share/man/man1/* /usr/share/man/man1/ cp ${KEEPALIVED_INSTALL_PATH}/share/man/man5/* /usr/share/man/man5/ cp ${KEEPALIVED_INSTALL_PATH}/share/man/man8/* /usr/share/man/man8/ mv keepalived.conf.sample ${KEEPALIVED_INSTALL_PATH}/etc/keepalived/keepalived.conf net_name=$(ip a | awk -F " |:" '/MULTICAST/{print $3}'|head -n1) sed -i "s/eth0/${net_name}/g" ${KEEPALIVED_INSTALL_PATH}/etc/keepalived/keepalived.conf sed -i '/virtual_server/,$d' ${KEEPALIVED_INSTALL_PATH}/etc/keepalived/keepalived.conf if [ $(os_type) == "Rocky" ];then sed -i "/^ *vrrp_strict/s/^/#/" ${KEEPALIVED_INSTALL_PATH}/etc/keepalived/keepalived.conf fi if [ "${KEEPALIVED_ROLE}" == "BACKUP" ]; then sed -i 's/MASTER/BACKUP/' ${KEEPALIVED_INSTALL_PATH}/etc/keepalived/keepalived.conf sed -i 's/ty 100/ty 90/' ${KEEPALIVED_INSTALL_PATH}/etc/keepalived/keepalived.conf fi if [ $? -eq 0 ]; then log "配置文件定制成功，安装路径为 ${KEEPALIVED_INSTALL_PATH}" else log "配置文件定制失败，安装路径为 ${KEEPALIVED_INSTALL_PATH}" exit 1 fi } # 定义函数：启动服务 function start_service() { log "开始启动服务" echo "正在重新加载 systemd 管理器配置..." systemctl daemon-reload if [ $? -eq 0 ]; then log "systemd 管理器配置重新加载成功" systemctl enable --now keepalived >/dev/null 2>&1 sleep 2 else log "systemd 管理器配置重新加载失败" exit 1 fi } # 定义函数：移除默认的策略规则 function move_nft_flush_rule (){ > /etc/nftables.conf sleep 2 nft flush ruleset cat > /usr/lib/systemd/system/nft-flush.service <<EOF [Unit] Description=Flush nftables Rules at Boot After=network.target Wants=network-online.target Conflicts=shutdown.target [Service] Type=oneshot # 清空规则 ExecStart=/usr/sbin/nft flush ruleset # 确保执行顺序 ExecStartPost=/bin/sleep 1 [Install] WantedBy=multi-user.target EOF sleep 1 systemctl daemon-reload systemctl start nft-flush.service systemctl enable --now nft-flush.service >/dev/null 2>&1 } # 主函数，按顺序调用各个子函数 function main() { # 检查环境变量是否为空 if [ -z "${KEEPALIVED_VERSION}" ]; then log "错误：KEEPALIVED_VERSION 环境变量为空" exit 1 fi if [ -z "${KEEPALIVED_INSTALL_PATH}" ]; then log "错误：KEEPALIVED_INSTALL_PATH 环境变量为空" exit 1 fi setup_environment download_software compile_and_install customize_service_file customize_config_file start_service move_nft_flush_rule log "Keepalived ${KEEPALIVED_VERSION} 安装及配置完成，安装路径为: ${KEEPALIVED_INSTALL_PATH}！" echo "Keepalived ${KEEPALIVED_VERSION} 安装及配置完成，安装路径为: ${KEEPALIVED_INSTALL_PATH}！" } # 调用主函数 mainendl

MGR集群auto认证脚本 MGR集群auto认证脚本#!/bin/bash # ************************************* # * 功能: Shell脚本模板 # * 作者: 刘丹玉 # * 联系: v649352141@163.com # * 版本: 2025-05-18 # ************************************* # 错误处理：如果命令执行失败，脚本将终止 set -e # 调试处理 # set -x MYSQL_PWD="Mysql.123456" MYSQL_VERION="8.0.41" MYSQL_PORT="3306" PORT="33061" OS_NAME="Rocky" MY_UUID=$(uuidgen) # 预定义主机列表 #HOSTS=("10.0.0.12" "10.0.0.15" "10.0.0.18") HOSTS=() PRIMARY_HOST="10.0.0.12" # 集群总节点数n ONLINE_COUNT="${#HOSTS[@]}" # 颜色脚本，通用 color () { RES_COL=80 MOVE_TO_COL="echo -en \\033[${RES_COL}G" SETCOLOR_SUCCESS="echo -en \\033[1;32m" SETCOLOR_FAILURE="echo -en \\033[1;31m" SETCOLOR_WARNING="echo -en \\033[1;33m" SETCOLOR_NORMAL="echo -en \E[0m" echo -n "$1" && $MOVE_TO_COL echo -n "[" if [[ $2 = "success" || $2 = "0" ]] ;then ${SETCOLOR_SUCCESS} echo -n $" OK " elif [[ $2 = "failure" || $2 = "1" ]] ;then ${SETCOLOR_FAILURE} echo -n $"FAILED" else ${SETCOLOR_WARNING} echo -n $"WARNING" fi ${SETCOLOR_NORMAL} echo -n "]" echo } # 查看属于Rocky、Ubuntu、openEuler系列 os_type () { $OS_NAME=$(awk -F'[ "]' '/^NAME/{print $2}' /etc/os-release) } # 免密认证 pwd_auth_across_host(){ color "==========免密认证 start============" 0 # 设置本机ip和密码 #read -s -p "请输入本机的密码：" secret read -p "请输入本机的密码：" secret echo # 换行，让后续输出从新行开始 # 检查密码是否为空 if [ -z "$secret" ]; then echo "错误：密码不能为空！" >&2 exit 1 fi # 可选：使用密码进行后续操作 echo "密码已设置，进行后续操作..." echo "清空 ip_up.txt 文件" > ip_up.txt # 生成 SSH 密钥对，不输出信息到屏幕 echo "生成 SSH 密钥对" ssh-keygen -t rsa -N "" -f ~/.ssh/id_rsa > /dev/null 2>&1 # 循环读取用户输入 while true;do read -p "请输入主机IP和密码（格式为 ip:password，输入空行结束）: " str # 检查是否为空行（用户想结束输入） if [ -z "$str" ]; then break fi # 将输入追加到ip_up.txt echo "$str" >> ip.txt echo "已添加: $str" done # 检查是否添加了任何内容 if [ -s "ip.txt" ]; then echo "已成功将内容保存到 ip.txt" else exit 1 fi echo "判断是否安装expect" ! rpm -q expect &>/dev/null && { echo "未安装 expect，正在尝试安装..."; yum -y install expect &>/dev/null || { echo "错误：expect 安装失败，请检查网络连接或权限" >&2; exit 1; }; } echo "将ssh密钥对复制到本地主机" # 使用 expect 实现 ssh-copy-id 到本地主机 /usr/bin/expect <<-END &>/dev/null spawn ssh-copy-id 127.1 expect { "yes/no" { send "yes\r"; exp_continue } "password:" { send "$secret\r" } timeout { puts "连接 127.0.0.1 超时，推送失败。" exit 1 } eof { if { [exp_status] != 0 } { puts "向 127.0.0.1 推送公钥失败。" exit 1 } } } expect eof END # 循环判断主机是否 ping 通，如果 ping 通则推送秘钥对 while IFS=: read -r ip pass; do # 检查读取的行是否符合格式 if [ -z "$ip" ] || [ -z "$pass" ]; then echo "ip.txt 文件中存在格式错误的行：$ip:$pass" continue fi ping -c1 $ip &>/dev/null if [ $? -eq 0 ];then echo $ip $pass echo $ip $pass >> ip_up.txt /usr/bin/expect <<-END &>/dev/null spawn rsync -avz /root/.ssh/ $ip:/root/.ssh/ expect { "yes/no" { send "yes\r";exp_continue } "password:" { send "$pass\r" } timeout { puts "连接 $ip 超时，推送失败。" exit 1 } eof { if { [regexp -nocase "Permission denied" \$expect_out(buffer)] } { puts "认证失败: $ip" exit 1 } else { puts "密钥推送成功: $ip" exit 0 } } } expect eof END fi done < ip.txt wait echo "秘钥已经推送完毕，正在测试..." echo "测试所有可达主机的公钥推送是否成功" all_success=true while read -r line; do remote_ip=$(echo "$line" | cut -d' ' -f1) ssh root@"$remote_ip" hostname &>/dev/null if [ $? -ne 0 ]; then echo "向 $remote_ip 推送公钥失败。" all_success=false fi done < ip_up.txt # 或从文件读取（确保文件每行一个主机名） HOSTS=() while IFS= read -r line; do # 过滤掉空行 if [[ -n "$line" ]]; then # 提取每行的第一个字段（以空格分隔） host=$(echo "$line" | cut -d' ' -f1) # 正确添加到数组 HOSTS+=("$host") fi done < ip_up.txt echo ${HOSTS[@]} PRIMARY_HOST="${HOSTS[0]}" if $all_success; then echo "秘钥成功推送完毕" else echo "部分主机公钥推送失败，请检查。" fi color "==========免密认证 end============" 0 } # 基础环境 base_environment(){ color "=============== MySQL 基础环境 start ===============" 0 for host in "${HOSTS[@]}"; do # 获取ip最后一位 last_octet=${host##*.} # 获取数组索引（正确方式） for i in "${!HOSTS[@]}"; do if [[ "${HOSTS[$i]}" == "$host" ]]; then index=$i break fi done name="rocky9-$last_octet-mgr0$((index + 1))" # 修改主机名 ssh root@$host "hostnamectl set-hostname $name" # 配置主机名称解析 echo $host $name >> /etc/hosts sleep 1 # 临时关闭SELINUX ssh root@10.0.0.12 ' if grep -q "SELINUX=enforcing" /etc/selinux/config; then setenforce 0 else echo "SELinux 已禁用，无需操作" 0 fi ' # 永久关闭SELINUX ssh root@$host "sed -i.bak 's/^SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config" sleep 1 # 临时关闭防火墙 ssh root@$host "systemctl disable --now firewalld && nft flush ruleset" done for host in "${HOSTS[@]}"; do scp /etc/hosts root@$host:/etc/hosts done color "=============== MySQL 基础环境 end ===============" 0 } # 安装并启动 MySQL 服务 install_mysql_soft() { color "=============== 正在安装 MySQL 软件 start ===============" 0 for host in "${HOSTS[@]}"; do # 检查是否已安装 if ! ssh root@"$host" "rpm -q mysql-server";then color "节点 $host 未安装 MySQL，开始安装..." 2 if ssh root@"$host" "yum -y install mysql-server >/dev/null 2>&1";then color "节点 $host MySQL 安装成功！" 0 else color "错误：节点 $host MySQL 安装失败！" 2 fi else color "节点 $host 已安装 MySQL" 0 fi sleep 1 done color "=============== 正在安装 MySQL 软件 end ===============" 3 } # 检查Mysql是否启动 check_mysql_is_active(){ color "=============== 正在测试 MySQL 是否存活 ===============" 0 for host in "${HOSTS[@]}"; do # 检查服务状态 if ! ssh root@"$host" "systemctl is-active mysqld"; then # 尝试重启 if ssh root@"$host" "systemctl restart mysqld && systemctl enable mysqld" >/dev/null 2>&1; then color "节点 $host MySQL 服务已启动并设置为开机自启！" 0 else color "错误：节点 $host MySQL 服务启动失败！" 2 fi fi sleep 1 done sleep 2 color "=============== MySQL 状态检查完成 ===============" 0 } # 正在测试 MySQL 是否存活 check_mysql_status(){ color "=============== 正在测试 MySQL 是否存活 start ============" 0 for host in "${HOSTS[@]}"; do # 使用 mysqladmin ping 检查连接状态 if ssh root@"$host" "mysqladmin ping -h localhost -uroot -p'$MYSQL_PWD' --silent" >/dev/null 2&>1; then color "节点 $host MySQL 服务已运行且可连接" 0 else # 检查Mysql是否启动 check_mysql_is_active color "节点 $host MySQL 无响应，尝试重启..." 1 fi done color "=============== 正在测试 MySQL 是否存活 end ============" 0 } # 修改mysql密码 alter_mysql_pwd() { color "========正在修改mysql密码 start ======" 0 for host in "${HOSTS[@]}"; do color "正在修改主机 $host 的 MySQL 密码..." 0 # 使用当前密码执行 SQL 命令 ssh root@"$host" "mysql -e \"ALTER USER root@'localhost' IDENTIFIED BY '$MYSQL_PWD';\"" color "节点 $host MySQL 服务密码已修改" 0 sleep 1 done sleep 2 color "=========正在修改mysql密码 end ========" 0 # 检查mysql连接状态 check_mysql_status } get_mysql_version() { color "========正在获取mysql版本信息 start =======" 0 # 定义获取版本的 SQL 命令 local SQL_COMMAND="SELECT VERSION();" # 遍历所有主机获取 MySQL 版本 for host in "${HOSTS[@]}"; do color "正在获取主机 $host 的 MySQL 版本..." 0 # 通过 SSH 执行 MySQL 命令并获取版本 local version=$(ssh root@"$host" "mysql -u root -p'$MYSQL_PWD' --batch --skip-column-names <<EOF $SQL_COMMAND EOF") # 检查命令是否成功执行 if [[ $? -ne 0 || -z "$version" ]]; then color "错误：无法获取主机 $host 的 MySQL 版本！" 2 continue fi # 显示版本信息 color "主机 $host 的 MySQL 版本为: $version" 0 # 短暂延迟，避免并发查询过多 sleep 2 done sleep 2 color "=======正在获取mysql版本信息 end ========" 0 } # 配置文件 mysql_config(){ color "=======正在集群配置文件 start =======" 0 # 格式化并拼接 hosts_with_port=$(printf "%s:$PORT,\n" "${HOSTS[@]}" | tr -d '\n' | sed 's/,$//') for host in "${HOSTS[@]}";do color "$host 主机正在写入配置文件" last_octet=$(echo $host | awk -F'.' '{print $4}') ssh root@$host <<- EOF cat > /etc/my.cnf.d/mysql-server.cnf <<- 'eof' [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysql/mysqld.log pid-file=/run/mysqld/mysqld.pid # 为了版本兼容性，更改默认的用户认证插件 default_authentication_plugin=mysql_native_password # 设置MySQL插件目录：MGR基于插件，必须设置插件路径 plugin_dir=/usr/lib64/mysql/plugin # 复制框架 server_id=$last_octet # 开启binlog的GTID模式（MGR强制要求） gtid_mode=ON # 开启后MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行 enforce_gtid_consistency=ON # 关闭binlog校验（MGR强制要求） binlog_checksum=NONE log_bin=binlog log_slave_updates=ON binlog_format=ROW master_info_repository=TABLE relay_log_info_repository=TABLE # 组复制设置 # server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列 transaction_write_set_extraction=XXHASH64 # 启用组复制模块 plugin_load_add='group_replication.so' # 告知插件加入或创建组命名，UUID group_replication_group_name="$MY_UUID" # server启动时不自启组复制,为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。 group_replication_start_on_boot=off # 告诉插件使用IP地址，端口33061用于接收组中其他成员转入连接 # 注意：此处可以使用ip地址，也可以使用主机名方式 group_replication_local_address="$host:$PORT" # 启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意 # 注意：此处可以使用ip地址，也可以使用主机名方式 group_replication_group_seeds="$hosts_with_port" # 配置此服务器为引导组，这个选项必须仅在一台服务器上设置， # 并且仅当第一次启动组或者重新启动整个组时。成功引导组启动后，将此选项设置为关闭。 group_replication_bootstrap_group=off # 指定当前节点向集群其他成员报告的自身的主机名或 IP 地址，用于成员间通信和连接 # 这是保证集群成员间正确通信的基础配置。 report_host=$host report_port=$MYSQL_PORT eof EOF done # 重启MySQL服务 for host in "${HOSTS[@]}"; do ssh root@$host systemctl restart mysqld sleep 1 done sleep 2 color "=======正在集群配置文件 end ========" 0 } # 集群认证环境 cluster_auth_init_sql(){ color "=======正在集群认证环境 start ========" 0 # SQL 脚本内容 SQL_SCRIPT=$(cat <<-'EOF' # 如下操作不记录二进制日志 SET SQL_LOG_BIN=0; # 创建rpl_user账户，此账户用于实现主从数据同步 CREATE USER rpl_user@'%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%'; GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%'; GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%'; # 创建一个远程连接用户，便于图形化管理工具使用 create user 'remote'@'%' identified with mysql_native_password by '123456'; grant all privileges on *.* to remote@'%'; FLUSH PRIVILEGES; # 恢复二进制日志功能并且重置二进制日志 SET SQL_LOG_BIN=1; RESET MASTER; EOF ) # 循环执行 for host in "${HOSTS[@]}"; do color "$host 主机正在集群认证" 0 # 通过 SSH 执行 MySQL 命令 ssh root@$host "mysql -u root -p'$MYSQL_PWD' <<EOF $SQL_SCRIPT EOF" if [ $? -eq 0 ]; then color "主机 $host 执行集群认证成功！" 0 else color "主机 $host 执行集群认证失败！" 2 fi done color "=======正在集群认证环境 end =======" 0 } # 获取集群的MEMBER_STATE状态信息：OFFLINE离线状态、ONLINE在线状态、RECOVERING恢复状态、UNREACHABLE不可达状态、ERROR错误状态 # 检查集群状态信息 check_cluster_online_exists() { color "=====正在检查集群状态信息 start ======" 0 local total_online=0 local host_count=${#HOSTS[@]} # 循环执行 for host in "${HOSTS[@]}"; do color "正在检查主机 $host 的集群状态..." 0 # 获取当前主机看到的在线节点数 ONLINE_COUNT=$(ssh root@"$host" "mysql -u root -p'$MYSQL_PWD' -Ns -e ' SELECT COUNT(*) FROM performance_schema.replication_group_members WHERE MEMBER_STATE = \"ONLINE\" '") # 验证查询结果是否有效 if [[ -z "$ONLINE_COUNT" || "$ONLINE_COUNT" -lt 0 ]]; then color "错误：无法从主机 $host 获取有效集群状态！" 1 continue fi color "主机 $host 报告：集群中有 $ONLINE_COUNT 个节点在线" 0 # 记录最大在线节点数（避免因网络分区导致部分节点看到不同结果） if [[ "$ONLINE_COUNT" -gt "$total_online" ]]; then total_online="$ONLINE_COUNT" fi sleep 1 done sleep 2 # 判断集群状态 if [[ "$total_online" -eq 0 ]]; then color "错误：集群中没有在线节点，集群处于离线状态！" 2 elif [[ "$total_online" -lt "$host_count" ]]; then color "警告：集群不完整！总节点数: $host_count, 在线节点: $total_online" 2 else color "成功：所有 $total_online 个节点均在线，集群状态正常！" 0 fi color "======正在检查集群状态信息 end =======" 0 } # 检查集群中是否存在 PRIMARY 节点 check_cluster_primary_exists() { color "=====正在检查集群中是否存在 PRIMARY 节点 start =====" 0 local total_primary=0 local host_count=${#HOSTS[@]} # 遍历所有主机，统计 PRIMARY 节点总数 for host in "${HOSTS[@]}"; do color "正在检查主机 $host 的 PRIMARY 节点..." 1 # 获取当前主机视角的 PRIMARY 节点数（单主模式下应为 1） local primary_count=$(ssh root@"$host" "mysql -u root -p'$MYSQL_PWD' -Ns -e ' SELECT COUNT(*) FROM performance_schema.replication_group_members WHERE MEMBER_STATE = \"ONLINE\" AND MEMBER_ROLE = \"PRIMARY\" '") # 验证结果有效性 if [[ -z "$primary_count" || ! "$primary_count" =~ ^[0-9]+$ ]]; then color "错误：主机 $host 返回无效数据！" 2 continue fi color "主机 $host 报告：PRIMARY 节点数 = $primary_count" 1 # 累加 PRIMARY 节点数（正常情况下所有主机的统计应一致） ((total_primary += primary_count)) sleep 1 done sleep 2 # 判断集群 PRIMARY 节点状态 if [[ "$total_primary" -eq 0 ]]; then color "错误：集群中没有 ONLINE 状态的 PRIMARY 节点！" 2 #return 1 # 返回失败状态码 elif [[ "$total_primary" -gt 1 && "$group_replication_single_primary_mode" == "ON" ]]; then color "警告：单主模式下出现多个 PRIMARY 节点（可能脑裂）！" 1 #return 2 # 返回警告状态码 else color "成功：集群中有 $total_primary 个 PRIMARY 节点" 0 #return 0 # 返回成功状态码 fi color "=====正在检查集群中是否存在 PRIMARY 节点 end =======" 0 } # 检查集群中的 PRIMARY 节点并返回其主机名 check_cluster_primary_node() { color "=====正在检查集群中的 PRIMARY 节点 start ========" 0 PRIMARY_HOST="" local host_count=${#HOSTS[@]} local found=0 # 遍历所有主机，查找 PRIMARY 节点 for host in "${HOSTS[@]}"; do color "正在从主机 $host 查询 PRIMARY 节点..." 0 # 获取当前主机视角的 PRIMARY 节点 local primary_candidate=$(ssh root@"$host" "mysql -u root -p'$MYSQL_PWD' -Ns -e ' SELECT MEMBER_HOST FROM performance_schema.replication_group_members WHERE MEMBER_STATE = \"ONLINE\" AND MEMBER_ROLE = \"PRIMARY\" '") # 验证查询结果有效性 if [[ -n "$primary_candidate" ]]; then if [[ -z "$PRIMARY_HOST" ]]; then PRIMARY_HOST="$primary_candidate" color "发现 PRIMARY 节点: $PRIMARY_HOST" 1 found=1 elif [[ "$primary_candidate" != "$PRIMARY_HOST" ]]; then color "警告: 主机 $host 报告的 PRIMARY 节点($primary_candidate)与之前不一致!" 1 color "可能存在脑裂，请检查网络分区!" 1 fi else color "主机 $host 未发现 ONLINE 状态的 PRIMARY 节点" 0 fi # 短暂延迟，避免并发查询过多 sleep 2 done sleep 2 # 判断最终结果 if [[ $found -eq 0 ]]; then color "错误: 集群中未发现 PRIMARY 节点!" 1 else color "成功: 集群 PRIMARY 节点为 $PRIMARY_HOST" 2 echo "$PRIMARY_HOST" # 输出 PRIMARY 节点主机名 fi color "=====正在检查集群中的 PRIMARY 节点 end ========" 0 } # 显示集群状态摘要 show_cluster_status() { color "=======正在显示集群状态摘要 start =======" 0 for host in "${HOSTS[@]}"; do # 使用 mysqladmin ping 检查连接状态 if ssh root@"$host" "mysqladmin ping -h localhost -uroot -p'$MYSQL_PWD' --silent"; then service_status="active" # 获取节点在集群中的角色和状态 node_info=$(ssh root@"$host" "mysql -u root -p'$MYSQL_PWD' -Ns <<EOF SELECT CONCAT(MEMBER_HOST, ':', MEMBER_STATE, ':', MEMBER_ROLE) FROM performance_schema.replication_group_members WHERE MEMBER_HOST = '$host'; EOF") # 输出结果 if [[ -n "$node_info" ]]; then color "节点 $host: MySQL状态=$service_status, 集群角色=$node_info" 0 fi else service_status="inactive" color "节点 $host: MySQL状态=$service_status, 未加入集群或无法连接" 2 fi sleep 1 done sleep 1 color "=======正在显示集群状态摘要 end =====" 0 } # 启动集群环境 cluster_start_sql(){ color "-------正在启动集群环境 start -----" 0 # 确保至少有一个主机 if [[ ${#HOSTS[@]} -lt 1 ]]; then color "错误: 主机列表为空" 2 return 1 fi # SQL 脚本内容 PRIMARY_SQL=$(cat <<-'EOF' # 主服务器启动时并不会直接启动复制组，通过下面的命令动态的开启复制组是我们的集群更安全 SET GLOBAL group_replication_bootstrap_group=ON; # 开启组网数据同步，该步骤尽在master节点上运行 START GROUP_REPLICATION USER='rpl_user', PASSWORD='123456'; SET GLOBAL group_replication_bootstrap_group=OFF; EOF ) # 执行主节点启动命令 color "$PRIMARY_HOST 主机正在启动集群主角色环境" 0 # 通过 SSH 执行 MySQL 命令 ssh root@$PRIMARY_HOST "mysql -u root -p'$MYSQL_PWD'<<'EOF' $PRIMARY_SQL EOF" # 等待主节点完全启动 sleep 2 # 从节点启动脚本 # SQL 脚本内容 SECONDARY_SQL=$(cat <<-'EOF' # 配置复制源并加入集群 change replication source to source_user='rpl_user',source_password='123456' for channel 'group_replication_recovery'; start group_replication user='rpl_user',password='123456'; EOF ) # 启动其他从节点 for host in "${HOSTS[@]}"; do if [[ "$host" == "$PRIMARY_HOST" ]]; then continue # 跳过主节点 fi color "$host 主机正在启动集群从角色环境" 0 # 通过 SSH 执行 MySQL 命令 ssh root@$host "mysql -u root -p'$MYSQL_PWD'<<'EOF' $SECONDARY_SQL EOF" # 短暂延迟，避免同时启动导致网络压力 sleep 2 done sleep 1 # 检查集群中是否存在 PRIMARY 节点 check_cluster_primary_exists # 检查集群状态信息 check_cluster_online_exists # 检查集群中的 PRIMARY 节点并返回其主机名 check_cluster_primary_node # 显示集群状态摘要 show_cluster_status color "-----正在启动集群环境 end ----" 0 } # 安装msyql环境 install_mysql_server () { # 安装并启动 MySQL 服务 install_mysql_soft # 检查Mysql是否启动 check_mysql_is_active alter_mysql_pwd get_mysql_version mysql_config # 集群认证环境 cluster_auth_init_sql # 启动集群环境 cluster_start_sql # 显示集群状态摘要 show_cluster_status } main(){ # 免密认证 pwd_auth_across_host # 基础环境 base_environment # 安装msyql环境 install_mysql_server } main效果endl

mysql_multi_instance_for_rocky9-mysql多实例脚本 mysql多实例脚本#!/bin/bash # ************************************* # * 功能: Shell脚本模板 # * 作者: 刘丹玉 # * 联系: v649352141@163.com # * 版本: 2025-05-15 # ************************************* # 错误处理：如果命令执行失败，脚本将终止 set -e # 调试处理 # set -x MYSQL_6=mysql-5.6.50-linux-glibc2.12-x86_64 MYSQL_7=mysql-5.7.44-linux-glibc2.12-x86_64 MYSQL_8=mysql-8.4.0-linux-glibc2.28-x86_64 SINGLE_6=false SINGLE_7=false SINGLE_8=false MORE_INSTANCE=false # 颜色脚本，通用 color () { RES_COL=60 MOVE_TO_COL="echo -en \\033[${RES_COL}G" SETCOLOR_SUCCESS="echo -en \\033[1;32m" SETCOLOR_FAILURE="echo -en \\033[1;31m" SETCOLOR_WARNING="echo -en \\033[1;33m" SETCOLOR_NORMAL="echo -en \E[0m" echo -n "$1" && $MOVE_TO_COL echo -n "[" if [ $2 = "success" -o $2 = "0" ] ;then ${SETCOLOR_SUCCESS} echo -n $" OK " elif [ $2 = "failure" -o $2 = "1" ] ;then ${SETCOLOR_FAILURE} echo -n $"FAILED" else ${SETCOLOR_WARNING} echo -n $"WARNING" fi ${SETCOLOR_NORMAL} echo -n "]" echo } # 查看属于Rocky、Ubuntu、openEuler系列 os_type () { awk -F'[ "]' '/^NAME/{print $2}' /etc/os-release } # 安装依赖 install_softs () { color "正在安装依赖环境" 0 if [ "${MORE_INSTANCE}" == "true" ];then yum -y install libaio numactl-libs ncurses-compat-libs perl-Data-Dumper autoconf libaio perl-Sys-Hostname ncurses-compat-libs > /dev/null 2>&1 elif [ "${SINGLE_6}" == "true" ];then yum install -y perl-Data-Dumper autoconf libaio perl-Sys-Hostname ncurses-compat-libs > /dev/null 2>&1 elif [ "${SINGLE_7}" == "true" ];then yum -y install libaio numactl-libs ncurses-compat-libs > /dev/null 2>&1 elif [ "${SINGLE_8}" == "true" ];then yum -y install libaio numactl-libs ncurses-compat-libs > /dev/null 2>&1 else exit 1 fi } # 准备用户 add_user (){ color "准备用户" 0 groupadd -r mysql useradd -r -g mysql -s /sbin/nologin mysql } # 软件环境 prepare_install_package () { color "准备下载相关软件---请耐心等待" 0 mkdir -p /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid,share} mkdir -p /data/softs cd /data/softs if [ "${SINGLE_6}" == "true" ];then wget https://downloads.mysql.com/archives/get/p/23/file/${MYSQL_6}.tar.gz > /dev/null 2>&1 [ $? -ne 0 ] && { color "下载 ${MYSQL_6}.tar.gz 文件失败" 1; exit; } tar xf ${MYSQL_6}.tar.gz mv ${MYSQL_6} /usr/local/mysql3306 chown -R mysql:mysql /usr/local/mysql3306 fi if [ "${SINGLE_7}" == "true" ];then wget https://downloads.mysql.com/archives/get/p/23/file/${MYSQL_7}.tar.gz > /dev/null 2>&1 [ $? -ne 0 ] && { color "下载 ${MYSQL_7}.tar.gz 文件失败" 1; exit; } tar xf ${MYSQL_7}.tar.gz mv ${MYSQL_7} /usr/local/mysql3307 chown -R mysql:mysql /usr/local/mysql3307 fi if [ "${SINGLE_8}" == "true" ];then wget https://downloads.mysql.com/archives/get/p/23/file/${MYSQL_8}.tar.xz > /dev/null 2>&1 [ $? -ne 0 ] && { color "下载 ${MYSQL_8}.tar.xz 文件失败" 1; exit; } tar xf ${MYSQL_8}.tar.xz mv ${MYSQL_8} /usr/local/mysql3308 chown -R mysql:mysql /usr/local/mysql3308 fi if [ "${MORE_INSTANCE}" == "true" ];then wget https://downloads.mysql.com/archives/get/p/23/file/${MYSQL_6}.tar.gz > /dev/null 2>&1 [ $? -ne 0 ] && { color "下载 ${MYSQL_6}.tar.gz 文件失败" 1; exit; } tar xf ${MYSQL_6}.tar.gz mv ${MYSQL_6} /usr/local/mysql3306 wget https://downloads.mysql.com/archives/get/p/23/file/${MYSQL_7}.tar.gz > /dev/null 2>&1 [ $? -ne 0 ] && { color "下载 ${MYSQL_7}.tar.gz 文件失败" 1; exit; } tar xf ${MYSQL_7}.tar.gz mv ${MYSQL_7} /usr/local/mysql3307 wget https://downloads.mysql.com/archives/get/p/23/file/${MYSQL_8}.tar.xz > /dev/null 2>&1 [ $? -ne 0 ] && { color "下载 ${MYSQL_8}.tar.xz 文件失败" 1; exit; } tar xf ${MYSQL_8}.tar.xz mv ${MYSQL_8} /usr/local/mysql3308 chown -R mysql:mysql /usr/local/mysql3306 chown -R mysql:mysql /usr/local/mysql3307 chown -R mysql:mysql /usr/local/mysql3308 fi chown -R mysql:mysql /mysql/ } # 生成三个实例的初始数据 initialize_data_6(){ if [ "${MORE_INSTANCE}" == "true" ];then echo 'PATH=/usr/local/mysql3306/bin:$PATH' > /etc/profile.d/mysql3306.sh source /etc/profile.d/mysql3306.sh /usr/local/mysql3306/scripts/mysql_install_db --basedir=/usr/local/mysql3306 --datadir=/mysql/3306/data > /dev/null 2>&1 PATH="/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin" elif [ "${SINGLE_6}" == "true" ];then echo 'PATH=/usr/local/mysql3306/bin:$PATH' > /etc/profile.d/mysql3306.sh source /etc/profile.d/mysql3306.sh for i in 6 7 8 do /usr/local/mysql3306/scripts/mysql_install_db --basedir=/usr/local/mysql3306 --datadir=/mysql/330$i/data > /dev/null 2>&1 done else printf "" fi } initialize_data_7(){ if [ "${MORE_INSTANCE}" == "true" ];then echo 'PATH=/usr/local/mysql3307/bin:$PATH' > /etc/profile.d/mysql3307.sh source /etc/profile.d/mysql3307.sh /usr/local/mysql3307/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql3307 --datadir=/mysql/3307/data > /dev/null 2>&1 PATH="/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin" elif [ "${SINGLE_7}" == "true" ];then echo 'PATH=/usr/local/mysql3307/bin:$PATH' > /etc/profile.d/mysql3307.sh source /etc/profile.d/mysql3307.sh for i in 6 7 8 do /usr/local/mysql3307/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql3307 --datadir=/mysql/330$i/data > /dev/null 2>&1 done else printf "" fi } initialize_data_8(){ if [ "${MORE_INSTANCE}" == "true" ];then echo 'PATH=/usr/local/mysql3308/bin:$PATH' > /etc/profile.d/mysql3308.sh source /etc/profile.d/mysql3308.sh /usr/local/mysql3308/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql3308 --datadir=/mysql/3308/data > /dev/null 2>&1 PATH="/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin" elif [ "${SINGLE_8}" == "true" ];then echo 'PATH=/usr/local/mysql3308/bin:$PATH' > /etc/profile.d/mysql3308.sh source /etc/profile.d/mysql3308.sh for i in 6 7 8 do /usr/local/mysql3308/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql3308 --datadir=/mysql/330$i/data > /dev/null 2>&1 done else printf "" fi } # 创建主配置文件 configuration_file_6 (){ if [ "${MORE_INSTANCE}" == "true" ];then cat > /mysql/3306/etc/my.cnf <<-eof [mysqld] port=3306 user=mysql socket = /mysql/3306/socket/mysql.sock basedir = /usr/local/mysql3306 datadir = /mysql/3306/data innodb_file_per_table=on skip_name_resolve = on symbolic-links=0 [client] socket = /mysql/3306/socket/mysql.sock [mysqld_safe] pid-file = /mysql/3306/pid/mysqld.pid log-error = /mysql/3306/log/error.log eof elif [ "${SINGLE_6}" == "true" ];then for i in 6 7 8 do cat > /mysql/330$i/etc/my.cnf <<-eof [mysqld] port=330$i user=mysql socket = /mysql/330$i/socket/mysql.sock basedir = /usr/local/mysql3306 datadir = /mysql/330$i/data innodb_file_per_table=on skip_name_resolve = on symbolic-links=0 [client] socket = /mysql/330$i/socket/mysql.sock [mysqld_safe] pid-file = /mysql/330$i/pid/mysqld.pid log-error = /mysql/330$i/log/error.log eof done else printf "" fi } configuration_file_7 (){ if [ "${MORE_INSTANCE}" == "true" ];then cat > /mysql/3307/etc/my.cnf <<-eof [mysqld] user=mysql port=3307 socket = /mysql/3307/socket/mysql.sock basedir = /usr/local/mysql3307 datadir = /mysql/3307/data skip_name_resolve = 1 pid-file = /mysql/3307/pid/mysqld.pid log-error = /mysql/3307/log/error.log lc-messages-dir = /usr/local/mysql3307/share/english lc-messages = en_US [client] socket = /mysql/3307/socket/mysql.sock eof elif [ "${SINGLE_7}" == "true" ];then for i in 6 7 8 do cat > /mysql/330$i/etc/my.cnf <<-eof [mysqld] user=mysql port=330$i socket = /mysql/330$i/socket/mysql.sock basedir = /usr/local/mysql3307 datadir = /mysql/330$i/data skip_name_resolve = 1 pid-file = /mysql/330$i/pid/mysqld.pid log-error = /mysql/330$i/log/error.log lc-messages-dir = /usr/local/mysql330$i/share/english lc-messages = en_US [client] socket = /mysql/330$i/socket/mysql.sock eof done else printf "" fi } configuration_file_8 (){ if [ "${MORE_INSTANCE}" == "true" ];then cat > /mysql/3308/etc/my.cnf <<-eof [mysql] port = 3308 socket = /mysql/3308/socket/mysql.sock [mysqld] port = 3308 mysqlx_port = 33080 mysqlx_socket = /mysql/3308/socket/mysqlx.sock basedir = /usr/local/mysql3308 datadir = /mysql/3308/data socket = /mysql/3308/socket/mysql.sock pid-file = /mysql/3308/pid/mysqld.pid log-error = /mysql/3308/log/error.log eof elif [ "${SINGLE_8}" == "true" ];then for i in 6 7 8 do cat > /mysql/330$i/etc/my.cnf <<-eof [mysql] port = 330$i socket = /mysql/330$i/socket/mysql.sock [mysqld] port = 330$i mysqlx_port = 330$i0 mysqlx_socket = /mysql/330$i/socket/mysqlx.sock basedir = /usr/local/mysql3308 datadir = /mysql/330$i/data socket = /mysql/330$i/socket/mysql.sock pid-file = /mysql/330$i/pid/mysqld.pid log-error = /mysql/330$i/log/error.log eof done else printf "" fi } # 定制服务管理文件 systemctl_mysql(){ color "定制服务管理文件" 0 if [ "${MORE_INSTANCE}" == "true" ];then for i in 6 7 8 do cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF [Unit] Description=MySQL Community Server Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql330$i/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf ExecReload=/bin/kill -s HUP \$MAINPID ExecStop=/bin/kill -s TERM \$MAINPID LimitNOFILE = 10000 EOF done elif [ "${SINGLE_6}" == "true" ];then for i in 6 7 8 do cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF [Unit] Description=MySQL Community Server Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql3306/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf ExecReload=/bin/kill -s HUP \$MAINPID ExecStop=/bin/kill -s TERM \$MAINPID LimitNOFILE = 10000 EOF done elif [ "${SINGLE_7}" == "true" ];then for i in 6 7 8 do cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF [Unit] Description=MySQL Community Server Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql3307/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf ExecReload=/bin/kill -s HUP \$MAINPID ExecStop=/bin/kill -s TERM \$MAINPID LimitNOFILE = 10000 EOF done elif [ "${SINGLE_8}" == "true" ];then for i in 6 7 8 do cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF [Unit] Description=MySQL Community Server Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql3308/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf ExecReload=/bin/kill -s HUP \$MAINPID ExecStop=/bin/kill -s TERM \$MAINPID LimitNOFILE = 10000 EOF done else exit 1 fi } # 启动服务 start_mysql(){ color "开机服务自启" 0 # 重载配置 systemctl daemon-reload for i in 6 7 8 do systemctl enable --now mysqld330$i > /dev/null 2>&1 done } # 测试是否存活 test_success(){ color "测试mysql是否启动" 0 if [ "${MORE_INSTANCE}" == "true" ];then for i in 6 7 8 do systemctl status mysqld330$i | grep active > /dev/null 2>&1 if [ $? -eq 0 ];then color "330$i 启动成功" 0 else color "330$i 启动失败" 2 fi done elif [ "${SINGLE_6}" == "true" ];then for i in 6 7 8 do systemctl status mysqld330$i | grep active > /dev/null 2>&1 if [ $? -eq 0 ];then color "330$i 启动成功" 0 else color "330$i 启动失败" 2 fi done elif [ "${SINGLE_7}" == "true" ];then for i in 6 7 8 do systemctl status mysqld330$i | grep active > /dev/null 2>&1 if [ $? -eq 0 ];then color "330$i 启动成功" 0 else color "330$i 启动失败" 2 fi done elif [ "${SINGLE_8}" == "true" ];then for i in 6 7 8 do systemctl status mysqld330$i | grep active > /dev/null 2>&1 if [ $? -eq 0 ];then color "330$i 启动成功" 0 else color "330$i 启动失败" 2 fi done else exit 1 fi } install_mysql (){ # 安装依赖 install_softs # 准备用户 add_user # 软件环境 prepare_install_package # 生成三个实例的初始数据 chown -R mysql:mysql /mysql/ color "正在实例数据初始化" 0 initialize_data_6 initialize_data_7 initialize_data_8 chown -R mysql:mysql /mysql/ # 创建主配置文件 color "正在创建主配置文件" 0 configuration_file_6 configuration_file_7 configuration_file_8 # 定制服务管理文件 systemctl_mysql # 启动服务 start_mysql # 测试是否存活 test_success } # 用户选择函数 select_mysql_version() { echo "=========================================" echo " MySQL多实例安装选择菜单" echo "=========================================" echo "请选择要安装的MySQL版本（可多选，用空格分隔）：" echo "1) 单实例 MySQL 5.6（每个版本多个实例）" echo "2) 单实例 MySQL 5.7（每个版本多个实例）" echo "3) 单实例 MySQL 8.4（每个版本多个实例）" echo "4) 多实例 MySQL 5.6、5.7、8.4 多个版本 " echo "0) 退出" echo "-----------------------------------------" read -p "请输入选项编号: " choices # 重置选择变量 SINGLE_6=false SINGLE_7=false SINGLE_8=false MORE_INSTANCE=false for choice in $choices; do case $choice in 1) SINGLE_6=true ;; 2) SINGLE_7=true ;; 3) SINGLE_8=true ;; 4) MORE_INSTANCE=true;; 0) echo "已取消安装，退出脚本。" exit 0 ;; *) echo "无效选项: $choice" select_mysql_version # 递归重新选择 return ;; esac done # 显示选择结果 echo "-----------------------------------------" echo "已选择的配置：" [ "$SINGLE_6" = "true" ] && echo "- MySQL 5.6: 启用" || echo "- MySQL 5.6: 禁用" [ "$SINGLE_7" = "true" ] && echo "- MySQL 5.7: 启用" || echo "- MySQL 5.7: 禁用" [ "$SINGLE_8" = "true" ] && echo "- MySQL 8.4: 启用" || echo "- MySQL 8.4: 禁用" [ "$MORE_INSTANCE" = "true" ] && echo "- 安装类型: 多实例" || echo "- 安装类型: 单实例" echo "-----------------------------------------" read -p "确认安装此配置吗？(y/n): " confirm if [ "$confirm" != "y" ] && [ "$confirm" != "Y" ]; then echo "已取消安装，退出脚本。" exit 0 elif [ $(os_type) == "Rocky" ];then install_mysql else color "不支持 $os_type " 2 fi } select_mysql_versionendl

mysql多实例安装 1.mysql5.7.44多实例【rocky9.4】1.1.基础环境安装依赖 yum -y install libaio numactl-libs ncurses-compat-libs1.2.准备用户groupadd -r mysql useradd -r -g mysql -s /sbin/nologin mysql1.3.准备安装包此方法为二进制安装，需要提前下载二进制包直接下载安装包，点击download 右击复制链接地址 https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz1.4.软件环境mkdir -p /data/softs cd /data/softs # 上传下载的安装包到/data/softs目录下 # 也可以使用wget wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz # 解压移动 tar xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz mv mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql1.5.环境变量设定echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh source /etc/profile.d/mysql.sh1.6.相关目录结构设置# 创建目录 mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid,share}[root@Rocky9-15 ~]# tree /mysql /mysql ├── 3306 │   ├── bin │   ├── data │   ├── etc │   ├── log │   ├── pid │   └── socket │   └── share ├── 3307 │   ├── bin │   ├── data │   ├── etc │   ├── log │   ├── pid │   └── socket │   └── share └── 3308 ├── bin ├── data ├── etc ├── log ├── pid └── socket └── share 24 directories, 0 files# 为目录赋予用户权限 chown -R mysql:mysql /usr/local/mysql/ chown -R mysql:mysql /mysql/1.7.生成三个实例的初始数据# 空密码初始化 for i in 6 7 8 do /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/mysql/330$i/data done1.8.创建主配置文件for i in 6 7 8 do cat > /mysql/330$i/etc/my.cnf <<-eof [mysqld] user=mysql port=330$i socket = /mysql/330$i/socket/mysql.sock basedir = /mysql/330$i datadir = /mysql/330$i/data skip_name_resolve = 1 pid-file = /mysql/330$i/pid/mysqld.pid log-error = /mysql/330$i/log/error.log lc-messages-dir = /usr/local/mysql/share/english lc-messages = en_US [client] socket = /mysql/330$i/socket/mysql.sock eof done1.9.定制服务管理文件下面两个服务脚本都可以使用服务脚本一for i in 6 7 8 do cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF [Unit] Description=MySQL Community Server Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf ExecReload=/bin/kill -s HUP \$MAINPID ExecStop=/bin/kill -s TERM \$MAINPID LimitNOFILE = 10000 EOF done服务脚本二for i in 6 7 8 do cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF [Unit] Description=MySQL Community Server Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf ExecStop=/usr/local/mysql/bin/mysqladmin -uroot -p'Mysql.123456' -S /mysql/330$i/socket/mysql.sock LimitNOFILE = 10000 EOF done1.10.更改文件属性# 为目录赋予用户权限 chown -R mysql:mysql /mysql/1.11.启动服务# 重载配置 systemctl daemon-reloadfor i in 6 7 8 do systemctl enable --now mysqld330$i done1.12.登录数据库[root@Rocky9-15 ~]# mysql -S /mysql/3306/socket/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.44 MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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> select version(); +-----------+ | version() | +-----------+ | 5.7.44 | +-----------+ 1 row in set (0.00 sec) mysql> exit Bye[root@Rocky9-15 ~]# mysql -S /mysql/3307/socket/mysql.sock -e "select version();" +-----------+ | version() | +-----------+ | 5.7.44 | +-----------+ [root@Rocky9-15 ~]# mysql -S /mysql/3308/socket/mysql.sock -e "select version();" +-----------+ | version() | +-----------+ | 5.7.44 | +-----------+1.13.设置登录密码# 更改密码 for i in 6 7 8 do mysqladmin -uroot -S /mysql/330$i/socket/mysql.sock password 'Mysql.123456' done# 查看版本信息 for i in 6 7 8 do mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select version();" done# 查看端口号 for i in 6 7 8 do mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select @@port;" done2.mysql8.4.0多实例【rocky9.4】2.1.基础环境# 安装依赖 yum -y install libaio numactl-libs ncurses-compat-libs2.2.准备用户groupadd -r mysql useradd -r -g mysql -s /sbin/nologin mysql2.3.准备安装包mkdir -p /data/softs cd /data/softs wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz2.4.软件环境cd /data/softs tar xf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz mv mysql-8.4.0-linux-glibc2.28-x86_64 /usr/local/mysql2.5.环境变量echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh source /etc/profile.d/mysql.sh2.6.相关目录结构设置mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid,share}[root@Rocky9-15 ~]# tree /mysql/ /mysql/ ├── 3306 │   ├── bin │   ├── data │   ├── etc │   ├── log │   ├── pid │   ├── share │   └── socket ├── 3307 │   ├── bin │   ├── data │   ├── etc │   ├── log │   ├── pid │   ├── share │   └── socket └── 3308 ├── bin ├── data ├── etc ├── log ├── pid ├── share └── socket 24 directories, 0 files# 为目录赋予用户权限 chown -R mysql:mysql /usr/local/mysql/ chown -R mysql:mysql /mysql/2.7.生成三个实例的初始数据# 空密码初始化 for i in 6 7 8 do /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/mysql/330$i/data done2.8.创建主配置文件for i in 6 7 8 do cat > /mysql/330$i/etc/my.cnf <<-eof [mysql] port = 330$i socket = /mysql/330$i/socket/mysql.sock [mysqld] port = 330$i mysqlx_port = 330$i0 mysqlx_socket = /mysql/330$i/socket/mysqlx.sock basedir = /mysql/330$i datadir = /mysql/330$i/data socket = /mysql/330$i/socket/mysql.sock pid-file = /mysql/330$i/pid/mysqld.pid log-error = /mysql/330$i/log/error.log eof done2.9.定制服务管理文件for i in 6 7 8 do cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF [Unit] Description=MySQL Community Server Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf ExecReload=/bin/kill -s HUP \$MAINPID ExecStop=/bin/kill -s TERM \$MAINPID LimitNOFILE = 10000 EOF done2.10.更改文件属性# 为目录赋予用户权限 chown -R mysql:mysql /mysql/2.11.启动服务# 重载配置 systemctl daemon-reloadfor i in 6 7 8 do systemctl enable --now mysqld330$i done2.12.登录数据库[root@Rocky9-15 ~]# mysql -S /mysql/3306/socket/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.4.0 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. 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> select version(); +-----------+ | version() | +-----------+ | 8.4.0 | +-----------+ 1 row in set (0.00 sec) mysql> exit Bye[root@Rocky9-15 ~]# mysql -S /mysql/3307/socket/mysql.sock -e "select version();" +-----------+ | version() | +-----------+ | 8.4.0 | +-----------+ [root@Rocky9-15 ~]# mysql -S /mysql/3308/socket/mysql.sock -e "select version();" +-----------+ | version() | +-----------+ | 8.4.0 | +-----------+2.13.设置登录密码# 设置密码 for i in 6 7 8 do mysqladmin -uroot -S /mysql/330$i/socket/mysql.sock password 'Mysql.123456' done# 查看版本信息 for i in 6 7 8 do mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select version();" done# 查看端口号 for i in 6 7 8 do mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select @@port;" done3.mysql5.6.50多实例【rocky9.4】3.1.基础环境yum install -y perl-Data-Dumper autoconf libaio perl-Sys-Hostname ncurses-compat-libs3.2.准备用户groupadd -r mysql useradd -r -g mysql -s /sbin/nologin mysql3.3.准备安装包mkdir -p /data/softs cd /data/softs wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz3.4.软件环境cd /data/softs tar xf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz mv mysql-5.6.50-linux-glibc2.12-x86_64 /usr/local/mysql3.5.环境变量echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh source /etc/profile.d/mysql.sh3.6.相关目录结构设置mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid,share}[root@Rocky9-18 softs]# tree /mysql/ /mysql/ ├── 3306 │   ├── bin │   ├── data │   ├── etc │   ├── log │   ├── pid │   ├── share │   └── socket ├── 3307 │   ├── bin │   ├── data │   ├── etc │   ├── log │   ├── pid │   ├── share │   └── socket └── 3308 ├── bin ├── data ├── etc ├── log ├── pid ├── share └── socket 24 directories, 0 files# 为目录赋予用户权限 chown -R mysql:mysql /usr/local/mysql/ chown -R mysql:mysql /mysql/3.7.生成三个实例的初始数据# 空密码初始化 for i in 6 7 8 do /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mysql/330$i/data done3.8.创建主配置文件for i in 6 7 8 do cat > /mysql/330$i/etc/my.cnf <<-eof [mysqld] port=330$i user=mysql socket = /mysql/330$i/socket/mysql.sock basedir = /usr/local/mysql datadir = /mysql/330$i/data innodb_file_per_table=on skip_name_resolve = on symbolic-links=0 [client] socket = /mysql/330$i/socket/mysql.sock [mysqld_safe] pid-file = /mysql/330$i/pid/mysqld.pid log-error = /mysql/330$i/log/error.log eof done3.9.定制服务管理文件for i in 6 7 8 do cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF [Unit] Description=MySQL Community Server Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf ExecReload=/bin/kill -s HUP \$MAINPID ExecStop=/bin/kill -s TERM \$MAINPID LimitNOFILE = 10000 EOF done3.10.更改文件属性# 为目录赋予用户权限 chown -R mysql:mysql /mysql/3.11.启动服务# 重载配置 systemctl daemon-reloadfor i in 6 7 8 do systemctl enable --now mysqld330$i done3.12.登录数据库[root@Rocky9-18 ~]# mysql -S /mysql/3306/socket/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.50 MySQL Community Server (GPL) Copyright (c) 2000, 2020, 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> select version(); +-----------+ | version() | +-----------+ | 5.6.50 | +-----------+ 1 row in set (0.00 sec) mysql> exit Bye3.13.设置登录密码# 设置密码 for i in 6 7 8 do mysqladmin -uroot -S /mysql/330$i/socket/mysql.sock password 'Mysql.123456' done# 查看版本信息 for i in 6 7 8 do mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select version();" done# 查看端口号 for i in 6 7 8 do mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select @@port;" done4.不同版本(5.6、5.7、8.0)的数据库服务多实例配置实现4.1.多实例部署环境规划实例信息编号实例服务端口实例存储路径实例配置文件套接字文件mysql5.6.503306/mysql/3306/data/mysql/3306/etc/my.cnf/mysql/3306/socket/mysql.sockmysql5.7.443307/mysql/3307/data/mysql/3307/etc/my.cnf/mysql/3307/socket/mysql.sockmysql8.4.03308/mysql/3308/data/mysql/3308/etc/my.cnf/mysql/3308/socket/mysql.sock4.2.基础环境# 安装依赖 yum -y install libaio numactl-libs ncurses-compat-libs perl-Data-Dumper autoconf libaio perl-Sys-Hostname ncurses-compat-libs4.3.准备用户groupadd -r mysql useradd -r -g mysql -s /sbin/nologin mysql4.4.准备安装包mkdir -p /data/softs cd /data/softs wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz4.5.软件环境如果不想浪费下载时间，可以上传下载好的安装包cd /data/softs tar xf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz mv mysql-5.6.50-linux-glibc2.12-x86_64 /usr/local/mysql3306 tar xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz mv mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql3307 tar xf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz mv mysql-8.4.0-linux-glibc2.28-x86_64 /usr/local/mysql33084.6.相关目录结构设置mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid,share}# 为目录赋予用户权限 chown -R mysql:mysql /usr/local/mysql* chown -R mysql:mysql /mysql/4.7.生成三个实例的初始数据由于每个实例初始化都要找环境变量，为了避免冲突 1.配置环境变量 2.初始化mysql脚本 3.初始化环境变量下面最好一个步骤一个步骤执行echo $PATH echo 'PATH=/usr/local/mysql3306/bin:$PATH' > /etc/profile.d/mysql3306.sh source /etc/profile.d/mysql3306.sh /usr/local/mysql3306/scripts/mysql_install_db --basedir=/usr/local/mysql3306 --datadir=/mysql/3306/data PATH="/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin" echo 'PATH=/usr/local/mysql3307/bin:$PATH' > /etc/profile.d/mysql3307.sh source /etc/profile.d/mysql3307.sh /usr/local/mysql3307/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql3307 --datadir=/mysql/3307/data PATH="/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin" echo 'PATH=/usr/local/mysql3308/bin:$PATH' > /etc/profile.d/mysql3308.sh source /etc/profile.d/mysql3308.sh /usr/local/mysql3308/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql3308 --datadir=/mysql/3308/data PATH="/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin"4.8.环境变量--可省略注意：环境变量仅对当前窗口有效，如果更换窗口，需要重新执行环境变量echo 'PATH=/usr/local/mysql3306/bin:$PATH' > /etc/profile.d/mysql3306.sh source /etc/profile.d/mysql3306.sh echo 'PATH=/usr/local/mysql3307/bin:$PATH' > /etc/profile.d/mysql3307.sh source /etc/profile.d/mysql3307.sh echo 'PATH=/usr/local/mysql3308/bin:$PATH' > /etc/profile.d/mysql3308.sh source /etc/profile.d/mysql3308.sh4.9.创建主配置文件cat > /mysql/3306/etc/my.cnf <<-eof [mysqld] port=3306 user=mysql socket = /mysql/3306/socket/mysql.sock basedir = /usr/local/mysql3306 datadir = /mysql/3306/data innodb_file_per_table=on skip_name_resolve = on symbolic-links=0 [client] socket = /mysql/3306/socket/mysql.sock [mysqld_safe] pid-file = /mysql/3306/pid/mysqld.pid log-error = /mysql/3306/log/error.log eofcat > /mysql/3307/etc/my.cnf <<-eof [mysqld] user=mysql port=3307 socket = /mysql/3307/socket/mysql.sock basedir = /mysql/3307 datadir = /mysql/3307/data skip_name_resolve = 1 pid-file = /mysql/3307/pid/mysqld.pid log-error = /mysql/3307/log/error.log lc-messages-dir = /usr/local/mysql3307/share/english lc-messages = en_US [client] socket = /mysql/3307/socket/mysql.sock eofcat > /mysql/3308/etc/my.cnf <<-eof [mysql] port = 3308 socket = /mysql/3308/socket/mysql.sock [mysqld] port = 3308 mysqlx_port = 33080 mysqlx_socket = /mysql/3308/socket/mysqlx.sock basedir = /mysql/3308 datadir = /mysql/3308/data socket = /mysql/3308/socket/mysql.sock pid-file = /mysql/3308/pid/mysqld.pid log-error = /mysql/3308/log/error.log eof4.10.定制服务管理文件for i in 6 7 8 do cat >/usr/lib/systemd/system/mysqld330$i.service<<EOF [Unit] Description=MySQL Community Server Documentation=https://dev.mysql.com/doc/refman/8.4/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql330$i/bin/mysqld --defaults-file=/mysql/330$i/etc/my.cnf ExecReload=/bin/kill -s HUP \$MAINPID ExecStop=/bin/kill -s TERM \$MAINPID LimitNOFILE = 10000 EOF done4.11.更改文件属性# 为目录赋予用户权限 chown -R mysql:mysql /mysql/4.12.启动服务# 重载配置 systemctl daemon-reloadfor i in 6 7 8 do systemctl enable --now mysqld330$i done4.13.测试是否存活for i in 6 7 8 do /usr/local/mysql330$i/bin/mysqladmin -S /mysql/330$i/socket/mysql.sock ping done4.13.登录数据库注意：由于是多个版本的mysql，设置环境变量会发生冲突 所以，登录mysql使用绝对路径登录/usr/local/mysql3306/bin/mysql -S /mysql/3306/socket/mysql.sock /usr/local/mysql3307/bin/mysql -S /mysql/3307/socket/mysql.sock /usr/local/mysql3307/bin/mysql -S /mysql/3307/socket/mysql.sock4.14.设置登录密码# 设置密码 for i in 6 7 8 do /usr/local/mysql330$i/bin/mysqladmin -uroot -S /mysql/330$i/socket/mysql.sock password 'Mysql.123456' done# 查看版本信息 for i in 6 7 8 do /usr/local/mysql330$i/bin/mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select version();" done# 查看端口号 for i in 6 7 8 do /usr/local/mysql330$i/bin/mysql -S /mysql/330$i/socket/mysql.sock -p'Mysql.123456' -e "select @@port;" doneendl

数据库基础及安装-1 数据库1.数据库原理1.1.E-R模型E-R图模型的组成是由实体，属性和联系三部份组成。实体：( Entity ) 实体是数据的使用者，代表软件系统中客观存在的生活中的实物，同一类实体构成实体集。 在ER图中，实体用矩形表示。 属性：( Attribute ) 实体中的所有特性称为属性，每个属性描述的是实体的单个特性。在ER图中，属性用椭圆形表示。 联系：( Relationship ) 描述了实体的属性之间的关联规则。在ER图中，联系用菱形表示。实体间的联系有三种类型：一对一联系 ( 1:1 )：例如,一个学号只能分配给一个同学,每个同学都有一个学号，则学号与同学的联系是一对一。 一对多联系 ( 1:n )：例如,一个老师可以教授多门课程,每门课程只能有一个老师教授,则老师与课程的联系是一对多。 多对多联系 ( m:n )：例如,一个同学可以报名多门课程，每门课程下有多个同学，则同学与课程的联系是多对多。1.2.设计数据库的基本原则--三大范式第一范式 1NF (确保每列保持原子性) 第二范式 2NF (确保表中的每列都和主键相关) 第三范式 3NF (确保每列都和主键列直接相关，而不是间接相关)1.3.MySQL执行查询语句执行流程MySQL数据库中的SQL查询语句执行流程1 接收查询语句： 用户通过客户端或编程语言框架里面的驱动模块，经由专门的通信协议，向MySQL服务器发送SQL查询语句。 如果是mysql服务端，这里的通信协议端口是 3306 数据库服务端内部的连接器接收查询请求后，首先到本地的缓存记录中检索信息 如果有信息，则直接返回查询结果 如果没有信息，则将信息直接传递给后端的数据库解析器来进行处理2 词法分析和语法分析： MySQL服务器首先会对接收到的查询语句进行词法分析，将其分解成一系列的词法单元。 接着，进行语法分析，检查这些词法单元是否符合SQL语法规则。如果语法错误，MySQL会立即返回错误信息。3 语义分析： 在语法分析之后，MySQL会进行语义分析，检查查询语句中的表、列、函数等是否存在，以及用户是否有足够的权限执行该查询。 语义分析还会检查查询语句中的数据类型是否匹配，以及是否存在违反数据库完整性的约束。4 查询优化： MySQL的查询优化器会对查询语句进行优化，决定最优的执行查询计划 -- 也就是怎么走会效率更快。 优化过程包括选择最佳的访问路径、索引、连接顺序等，以最小化查询的执行时间和资源消耗。 优化器还会考虑统计信息，如表的行数、索引的分布等，以做出更明智的决策。5 查询执行： 优化后的查询计划被传递给执行器进行执行。执行器根据查询计划访问存储引擎，获取数据。 存储引擎将文件系统里面的数据库文件数据，加载到内存，然后在内存中做一次聚合 然后，临时放到查询缓存中，便于下次遇到相同的查询语句重复过来。 在执行过程中，MySQL可能会使用缓存来加速查询的执行。从MySQL 8.0开始，查询缓存已被移除。6 结果集返回： 执行器将查询结果集返回给客户端。结果集可以包含零行或多行数据，具体取决于查询的内容。 如果查询涉及聚合函数（如SUM、AVG等）或分组操作（如GROUP BY），执行器还会在返回结果之前对这些数据进行相应的处理。7 错误处理和日志记录： 如果在执行过程中遇到任何错误（如权限不足、表不存在等），MySQL会返回相应的错误信息给客户端。 同时，MySQL还会记录查询的执行日志，以便进行性能分析和故障排查。主要区别：MySQL 5.7 及之前版本查询缓存常被使用，但存在一些问题（如命中率低、维护开销大）。 MySQL 8.0 弃用查询缓存，避免相关开销。-- 查询2023年每个城市中，购买了“电子产品”类别商品，且订单总金额大于1000元的客户信息 -- 以及他们对应的订单信息和购买的商品明细 SELECT c.customer_name, c.customer_city, o.order_id, o.order_date, o.total_amount, p.product_name, oi.quantity, oi.unit_price FROM customers c -- 连接orders表，获取客户的订单信息 JOIN orders o ON c.customer_id = o.customer_id -- 连接order_items表，获取订单中的商品明细信息 JOIN order_items oi ON o.order_id = oi.order_id -- 连接products表，获取商品的名称等信息 JOIN products p ON oi.product_id = p.product_id -- 连接categories表，获取商品所属类别信息 JOIN categories cat ON p.category_id = cat.category_id WHERE -- 筛选出2023年的订单 YEAR(o.order_date) = 2023 AND -- 筛选出商品类别为“电子产品”的记录 cat.category_name = '电子产品' AND -- 筛选出订单总金额大于1000元的订单 o.total_amount > 1000 GROUP BY -- 按照客户姓名、城市、订单ID、订单日期、订单总金额、商品名称进行分组 c.customer_name, c.customer_city, o.order_id, o.order_date, o.total_amount, p.product_name ORDER BY -- 按照客户所在城市升序、订单总金额降序排序 c.customer_city ASC, o.total_amount DESC;对于这个大的流程来说，专用协议之后的功能，全部由数据库软件来实现，而我们需要做的是前面的各种sql语句的编写。2.Mysql安装方式mysql主流的四种安装方式安装方式说明包管理器进行安装配置好mysql仓库员，用包管理器进行在线安装二进制包本地安装下载已编译完成的压缩包，展开至特定路径，并经过简单配置后即可使用源码编译安装1下载指定版本源码在本地进行编译安装源码编译安装2下载指定版本源码【已编译完毕生成可执行文件】在本地进行环境初始化即可3.关于软件mysql和mariadb安装文件前后对比注意：两个环境安装不同的mysql和mariadb 配置文件的名称和位置都不一样 但是，配置内容，基本上都是一样。 mysql 是 多线程工作模式MysqlRockyubuntu软件名mysql-servermysql-server服务名mysqld.servicemysql.service入口文件/etc/my.cnf/etc/mysql/my.cnf配置目录/etc/my.cnf.d/etc/mysql/mysql.conf.d服务配置/etc/my.cnf.d/mysql-server.cnf/etc/mysql/mysql.conf.d/mysqld.cnf端口:::3306 :::33060127.0.0.1:3306 127.0.0.1:33060MariadbRockyubuntu软件名mariadb-servermariadb-server服务名mariadb.servicemariadb.service入口文件/etc/my.cnf/etc/mysql/my.cnf配置目录/etc/my.cnf.d/etc/mysql/mariadb.conf.d服务配置/etc/my.cnf.d/mariadb-server.cnf/etc/mysql/mariadb.conf.d/50-server.cnf端口:::3306127.0.0.1:33064.包管理进行安装4.1.光盘镜像Rocky9 系列光盘镜像自带 mysql-server 8.0 和 mariadb-server 10.3挂载镜像 [root@Rocky9-12 ~]# mount /dev/cdrom /opt/ mount: /opt: WARNING: source write-protected, mounted read-only. [root@Rocky9-12 ~]# ls /opt/ AppStream BaseOS EFI images isolinux LICENSE media.repo 查看数据库软件 [root@Rocky9-12 ~]# ls /opt/AppStream/Packages/m/mysql-ser* /opt/AppStream/Packages/m/mysql-server-8.0.36-1.el9_3.x86_64.rpm [root@Rocky9-12 ~]# ls /opt/AppStream/Packages/m/mariadb-server-1* /opt/AppStream/Packages/m/mariadb-server-10.11.6-1.module+el9.4.0+20012+a68bdff7.x86_64.rpm /opt/AppStream/Packages/m/mariadb-server-10.5.22-1.el9_2.x86_64.rpm其他信息：CentOS7 系列光盘镜像自带 mariadb-server 5.5 CentOS6 系列光盘镜像自带 mysql-server 5.1[21:37:13 root@centos7 ~]# mount /dev/cdrom /opt mount: /dev/sr0 is write-protected, mounting read-only [21:37:27 root@centos7 ~]# ls /opt/ CentOS_BuildTag EULA images LiveOS repodata RPM-GPG-KEY-CentOS-Testing-7 EFI GPL isolinux Packages RPM-GPG-KEY-CentOS-7 TRANS.TBL 查看数据库软件 [21:38:19 root@centos7 ~]# ls /opt/Packages/mariadb-server-5.5.68-1.el7.x86_64.rpm /opt/Packages/mariadb-server-5.5.68-1.el7.x86_64.rpm4.2.官方yum源源配置地址mariadb 官方源配置页面https://mariadb.org/download/?t=repo-configmysql 官方源配置页面https://dev.mysql.com/downloads阿里云 mysql 源配置页面https://mirrors.aliyun.com/mysql/阿里云 mariadb 源配置页面https://mirrors.aliyun.com/mariadb/清华 mysql 源配置页面https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/清华 mariadb 源配置页面https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/[root@Rocky9-12 ~]# yum list "mysql" mysql.x86_64 8.0.41-2.el9_5 [root@Rocky9-12 ~]# yum list "mariadb" mariadb.x86_64 3:10.5.27-1.el9_5 4.3.mariadb源如何获取最新的mariadb软件源，参考mariadb 官方源配置页面，选择版本和系统发行版本 注意：目前版本最新为 11.8 官网：https://mariadb.org/download/4.4.mysql源如何获取最新的mysql软件源，参考mysql官方源配置页面 官网：http://dev.mysql.com/downloads/点击需要使用功能的源类型，然后下载对应的包就可以了 MySQL Community Downloads 即 MySQL 社区版下载General Availability (GA) Releases：意思是 “通用版本发布” 通常指软件经过全面测试、达到稳定状态，可供广大用户在生产环境等正式场景使用的版本，按钮为橙色文字。 Archives：意为 “存档” 这里一般指软件过往旧版本的集合，供有特定需求（如兼容性需求）的用户下载使用，按钮为蓝色文字。apt源中版本最新版本是8.34-1yum源版本最新版本是8.0.4x版本结果显示： 社区版，在 APT 源中，目前主流的是8版本 在其他的下载页面，虽然存在9.1.0版本，但是LTS依然是8版本5.Rocky9中安装mysql8.05.1.软件信息获取mysql软件源 [root@Rocky9-12 ~]# yum list mysql mysql-server mysql.x86_64 8.0.41-2.el9_5 appstream mysql-server.x86_64 8.0.41-2.el9_5 appstream获取mariadb的软件源 [root@Rocky9-15 ~]# yum list mariadb mariadb-server mariadb.x86_64 3:10.5.27-1.el9_5 appstream mariadb-server.x86_64 3:10.5.27-1.el9_5 appstream5.2.安装软件安装 mysql-server，会自动安装客户端包【安装客户端不会安装服务端软件】[root@Rocky9-12 ~]# yum install -y mysql-server服务状态 [root@Rocky9-12 ~]# systemctl status mysqld.service ○ mysqld.service - MySQL 8.0 database server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; preset: disabled) Active: inactive (dead)启动服务 [root@Rocky9-12 ~]# systemctl enable --now mysqld Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.再次查看 [root@Rocky9-12 ~]# systemctl status mysqld.service ● mysqld.service - MySQL 8.0 database server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disabled) Active: active (running) 5.3.环境查看检测mysql的进程状态多线程模式 [root@Rocky9-12 ~]# pstree | grep mysql |-mysqld---36*[{mysqld}]检测其他信息自动创建的账户 [root@Rocky9-12 ~]# getent passwd mysql mysql:x:27:27:MySQL Server:/var/lib/mysql:/sbin/nologin查看mysql真正的家目录 [root@Rocky9-12 ~]# ll /var/lib/mysql total 91604 -rw-r----- 1 mysql mysql 56 May 7 21:58 auto.cnf -rw-r----- 1 mysql mysql 157 May 7 21:58 binlog.000001 -rw-r----- 1 mysql mysql 16 May 7 21:58 binlog.index -rw------- 1 mysql mysql 1705 May 7 21:58 ca-key.pem -rw-r--r-- 1 mysql mysql 1112 May 7 21:58 ca.pem -rw-r--r-- 1 mysql mysql 1112 May 7 21:58 client-cert.pem -rw------- 1 mysql mysql 1705 May 7 21:58 client-key.pem -rw-r----- 1 mysql mysql 196608 May 7 22:00 '#ib_16384_0.dblwr' -rw-r----- 1 mysql mysql 8585216 May 7 21:58 '#ib_16384_1.dblwr' -rw-r----- 1 mysql mysql 6238 May 7 21:58 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 May 7 21:58 ibdata1 -rw-r----- 1 mysql mysql 12582912 May 7 21:58 ibtmp1 drwxr-x--- 2 mysql mysql 4096 May 7 21:58 '#innodb_redo' drwxr-x--- 2 mysql mysql 187 May 7 21:58 '#innodb_temp' drwxr-x--- 2 mysql mysql 143 May 7 21:58 mysql -rw-r----- 1 mysql mysql 26214400 May 7 21:58 mysql.ibd srwxrwxrwx 1 mysql mysql 0 May 7 21:58 mysql.sock -rw------- 1 mysql mysql 5 May 7 21:58 mysql.sock.lock -rw-r--r-- 1 mysql mysql 7 May 7 21:58 mysql_upgrade_info srwxrwxrwx 1 mysql mysql 0 May 7 21:58 mysqlx.sock -rw------- 1 mysql mysql 5 May 7 21:58 mysqlx.sock.lock drwxr-x--- 2 mysql mysql 8192 May 7 21:58 performance_schema -rw------- 1 mysql mysql 1705 May 7 21:58 private_key.pem -rw-r--r-- 1 mysql mysql 452 May 7 21:58 public_key.pem -rw-r--r-- 1 mysql mysql 1112 May 7 21:58 server-cert.pem -rw------- 1 mysql mysql 1705 May 7 21:58 server-key.pem drwxr-x--- 2 mysql mysql 28 May 7 21:58 sys -rw-r----- 1 mysql mysql 16777216 May 7 22:00 undo_001 -rw-r----- 1 mysql mysql 16777216 May 7 22:00 undo_002查看数据库的数据文件 [root@Rocky9-12 ~]# ls /var/lib/mysql/mysql general_log_213.sdi general_log.CSM general_log.CSV slow_log_214.sdi slow_log.CSM slow_log.CSV 注意：因为这些文件，不是普通的文件系统，它是数据库专用的文件，只能通过数据库的统一接口到这些文件中，进行数据的查找。查看监听端口，默认3306，33060 是8.0版本中的特性 [root@Rocky9-12 ~]# ss -tnulp | grep mysql tcp LISTEN 0 151 *:3306 *:* users:(("mysqld",pid=2520,fd=24)) tcp LISTEN 0 70 *:33060 *:* users:(("mysqld",pid=2520,fd=21)) 注意：X Protocol 是一种新的通信协议，旨在提供更高效、灵活的数据交互方式，让客户端与服务器通信更顺畅。 在 MySQL 8.0 中，33060 端口是 X Protocol 协议的端口 。5.4.登录测试客户端连接: 默认用户名 root，默认密码为空连接到数据库里面 [root@Rocky9-12 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.41 Source distribution mysql>查看当前数据库的版本信息 mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.41 | +-----------+ 1 row in set (0.00 sec) mysql> exit; # 退出数据库的连接客户端连接: 直接使用用户和密码 [root@Rocky9-12 ~]# mysql -uroot -p -P3306 Enter password: # 因为没有密码，所以这里直接Enter即可 Server version: 8.0.41 Source distribution mysql>mysql> \s # 显示当前 MySQL 服务器会话状态的快捷命令 -------------- mysql Ver 8.0.41 for Linux on x86_64 (Source distribution) Connection id: 9 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.41 Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/lib/mysql/mysql.sock Binary data as: Hexadecimal Uptime: 8 min 35 sec Threads: 2 Questions: 8 Slow queries: 0 Opens: 120 Flush tables: 3 Open tables: 36 Queries per second avg: 0.015 --------------6.在Rocky9中安装mariadb6.1.软件安装查看mariadb软件 [root@Rocky9-15 ~]# yum list mariadb mariadb.x86_64 3:10.5.27-1.el9_5 appstream安装软件 -- 会自动安装客户端 [root@Rocky9-15 ~]# yum -y install mariadb-server启动服务 [root@Rocky9-15 ~]# systemctl enable --now mariadb Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service. Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service. Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.查看状态 [root@Rocky9-15 ~]# systemctl status mariadb ● mariadb.service - MariaDB 10.5 database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; preset: disabled) Active: active (running)监听端口 [root@Rocky9-15 ~]# ss -tunlp | grep 3306 tcp LISTEN 0 80 *:3306 *:* users:(("mariadbd",pid=3169,fd=19)) # 服务名字变成了 mariadb 注意： mariadb里面没有33060的端口 MariaDB 的设计初衷是保持与 MySQL 高度兼容，主要继承 MySQL 5.5 及之前版本的特性。 它专注于提供稳定、性能良好且与旧版 MySQL 兼容的数据库服务，面向大多数传统数据库应用场景，这些场景不需 要通过 33060 端口使用 X Protocol。多线程状态 [root@Rocky9-15 ~]# pstree | grep maria |-mariadbd---7*[{mariadbd}]查看家目录文件结构 [root@Rocky9-15 ~]# ll /var/lib/mysql/ total 122920 -rw-rw---- 1 mysql mysql 24576 May 7 22:10 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 May 7 22:10 aria_log_control -rw-rw---- 1 mysql mysql 972 May 7 22:10 ib_buffer_pool -rw-rw---- 1 mysql mysql 12582912 May 7 22:10 ibdata1 -rw-rw---- 1 mysql mysql 100663296 May 7 22:10 ib_logfile0 -rw-rw---- 1 mysql mysql 12582912 May 7 22:10 ibtmp1 -rw-rw---- 1 mysql mysql 0 May 7 22:10 multi-master.info drwx------ 2 mysql mysql 4096 May 7 22:10 mysql srwxrwxrwx 1 mysql mysql 0 May 7 22:10 mysql.sock -rw-rw---- 1 mysql mysql 16 May 7 22:10 mysql_upgrade_info drwx------ 2 mysql mysql 20 May 7 22:10 performance_schema检查客户端连接命令 -- 本质上是 mariadb文件 [root@Rocky9-15 ~]# ll /usr/sbin/mysqld lrwxrwxrwx 1 root root 19 Feb 4 19:21 /usr/sbin/mysqld -> /usr/libexec/mysqld [root@Rocky9-15 ~]# ll /usr/libexec/mysqld lrwxrwxrwx 1 root root 8 Feb 4 19:21 /usr/libexec/mysqld -> mariadbd6.2.连接登录客户端连接 [root@Rocky9-15 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.5.27-MariaDB MariaDB Server MariaDB [(none)]> select version(); +-----------------+ | version() | +-----------------+ | 10.5.27-MariaDB | +-----------------+ 1 row in set (0.000 sec) MariaDB [(none)]> exit Bye7.Ubuntu24 安装mysql8.07.1.安装软件安装 mysql-server，会自动安装客户端包【安装客户端不会安装服务端软件】 [root@ubuntu24-13:~]# apt install mysql-server -y再次查看状态信息 [root@ubuntu24-13:~]# systemctl status mysql.service ● mysql.service - MySQL Community Server Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled) Active: active (running) 注意：如果是第一次安装mysql-server出现服务启动不起来，而且没有提前做任何的配置，那一般情况下，是本地主机环境已经被历史残留的数据信息破坏了。检测mysql的进程状态多线程模式 [root@ubuntu24-13:~]# pstree | grep mysql |-mysqld---36*[{mysqld}] 自己创建的账户 [root@ubuntu24-13:~]# getent passwd mysql mysql:x:110:110:MySQL Server,,,:/nonexistent:/bin/false查看mysql真正的家目录 [root@ubuntu24-13:~]# ll /var/lib/mysql total 91612 -rw-r----- 1 mysql mysql 196608 May 7 22:19 '#ib_16384_0.dblwr' -rw-r----- 1 mysql mysql 8585216 May 7 22:17 '#ib_16384_1.dblwr' drwxr-x--- 2 mysql mysql 4096 May 7 22:17 '#innodb_redo'/ drwxr-x--- 2 mysql mysql 4096 May 7 22:17 '#innodb_temp'/ drwx------ 7 mysql mysql 4096 May 7 22:17 ./ drwxr-xr-x 50 root root 4096 May 7 22:17 ../ -rw-r----- 1 mysql mysql 56 May 7 22:17 auto.cnf -rw-r----- 1 mysql mysql 180 May 7 22:17 binlog.000001 -rw-r----- 1 mysql mysql 404 May 7 22:17 binlog.000002 -rw-r----- 1 mysql mysql 157 May 7 22:17 binlog.000003 -rw-r----- 1 mysql mysql 48 May 7 22:17 binlog.index -rw------- 1 mysql mysql 1705 May 7 22:17 ca-key.pem -rw-r--r-- 1 mysql mysql 1112 May 7 22:17 ca.pem -rw-r--r-- 1 mysql mysql 1112 May 7 22:17 client-cert.pem -rw------- 1 mysql mysql 1705 May 7 22:17 client-key.pem -rw-r--r-- 1 root root 0 May 7 22:17 debian-5.7.flag -rw-r----- 1 mysql mysql 3425 May 7 22:17 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 May 7 22:17 ibdata1 -rw-r----- 1 mysql mysql 12582912 May 7 22:17 ibtmp1 drwxr-x--- 2 mysql mysql 4096 May 7 22:17 mysql/ -rw-r----- 1 mysql mysql 26214400 May 7 22:17 mysql.ibd drwxr-x--- 2 mysql mysql 4096 May 7 22:17 performance_schema/ -rw------- 1 mysql mysql 1709 May 7 22:17 private_key.pem -rw-r--r-- 1 mysql mysql 452 May 7 22:17 public_key.pem -rw-r--r-- 1 mysql mysql 1112 May 7 22:17 server-cert.pem -rw------- 1 mysql mysql 1705 May 7 22:17 server-key.pem drwxr-x--- 2 mysql mysql 4096 May 7 22:17 sys/ -rw-r----- 1 mysql mysql 5 May 7 22:17 ubuntu24-13.pid -rw-r----- 1 mysql mysql 16777216 May 7 22:19 undo_001 -rw-r----- 1 mysql mysql 16777216 May 7 22:19 undo_002查看数据库的数据文件 [root@ubuntu24-13:~]# ls /var/lib/mysql/mysql general_log.CSM general_log.CSV general_log_213.sdi slow_log.CSM slow_log.CSV slow_log_214.sdi 注意：因为这些文件，不是普通的文件系统，它是数据库专用的文件，只能通过数据库的统一接口到这些文件中，进行数据的查找。查看监听端口，默认3306，33060 是8.0版本中的特性 [root@ubuntu24-13:~]# netstat -tunlp | grep mysql tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 8524/mysqld tcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN 8524/mysqld 结果显示：默认监听的是本地主机的IP地址7.2.登录连接客户端连接: 默认用户名 root，默认密码为空连接到数据库里面 [root@ubuntu24-13:~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.42-0ubuntu0.24.04.1 (Ubuntu) # 查看当前数据库的版本信息 mysql> select version(); +-------------------------+ | version() | +-------------------------+ | 8.0.42-0ubuntu0.24.04.1 | +-------------------------+ 1 row in set (0.00 sec) mysql> exit # 退出数据库的连接 Bye客户端连接: 直接使用用户和密码 [root@ubuntu24-13:~]# mysql -uroot -p Enter password: # 因为没有密码，所以这里直接Enter即可，也可以随便乱写密码 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.42-0ubuntu0.24.04.1 (Ubuntu) Copyright (c) 2000, 2025, Oracle and/or its affiliates. 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> \s # 显示当前 MySQL 服务器会话状态的快捷命令 -------------- mysql Ver 8.0.42-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu)) Connection id: 9 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.42-0ubuntu0.24.04.1 (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/run/mysqld/mysqld.sock Binary data as: Hexadecimal Uptime: 7 min 11 sec Threads: 2 Questions: 8 Slow queries: 0 Opens: 119 Flush tables: 3 Open tables: 38 Queries per second avg: 0.018 --------------8.Ubuntu24中安装mysql8.0---二进制包安装8.1.如何获取二进制包文件传统的二进制包安装需要进行三步：configure --- make --- make install 而mysql的二进制包是指己经编译完成【也就是说，make已经做过了】，以压缩包提供下载的文件，下载到本 地之后释放到自定义目录，再进行配置即可。二进制包的下载位置 -- Download Archives 官网：https://dev.mysql.com/downloads选择 Download Archives选择第一个 Mysql Community Server选择第一个，然后点击右侧的下载就可以了 https://downloads.mysql.com/archives/get/p/23/file/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz以同样的逻辑下载mysql的最新版本9.2.0选择第一个，然后点击右侧的下载就可以了 https://downloads.mysql.com/archives/get/p/23/file/mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz8.2.基础环境安装依赖 [root@ubuntu24-16:~]# apt -y install libaio-dev numactl libnuma-dev libncurses-dev注意：ubuntu24系统没有libaio1的包，需要单独去下载安装 curl -O http://launchpadlibrarian.net/646633572/libaio1_0.3.113-4_amd64.deb dpkg -i libaio1_0.3.113-4_amd64.deb8.3.用户环境[root@ubuntu24-16:~]# groupadd -r mysql [root@ubuntu24-16:~]# useradd -r -g mysql -s /sbin/nologin mysql8.4.软件安装获取软件 [root@ubuntu24-16:~]# mkdir -p /data/softs [root@ubuntu24-16:~]# cd /data/softs [root@ubuntu24-16:softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz解压至指定目录，这个目录只能写 /usr/local/ [root@ubuntu24-16:softs]# mkdir /usr/local/mysql [root@ubuntu24-16:softs]# tar xf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz [root@ubuntu24-16:softs]# mv mysql-8.4.0-linux-glibc2.28-x86_64 /usr/local/mysql查看文件 [root@ubuntu24-16:softs]# ls /usr/local/mysql LICENSE README bin docs include lib man share support-files [root@ubuntu24-16:softs]# ls /usr/local/mysql/bin/ ibd2sdi myisamchk mysql_config mysql_tzinfo_to_sql mysqld mysqldump mysqlslap innochecksum myisamlog mysql_config_editor mysqladmin mysqld-debug mysqldumpslow perror my_print_defaults myisampack mysql_migrate_keyring mysqlbinlog mysqld_multi mysqlimport myisam_ftdump mysql mysql_secure_installation mysqlcheck mysqld_safe mysqlshow8.5.环境变量设定-可选创建环境变量 [root@ubuntu24-16:softs]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@ubuntu24-16:softs]# source /etc/profile.d/mysql.sh [root@ubuntu24-16:softs]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin8.6.环境配置创建主配置文件 [root@ubuntu24-16:~]# mkdir /usr/local/mysql/etc [root@ubuntu24-16:~]# vim /usr/local/mysql/etc/my.cnf [root@ubuntu24-16:~]# cat /usr/local/mysql/etc/my.cnf [mysql] port = 3306 socket = /usr/local/mysql/data/mysql.sock [mysqld] port = 3306 mysqlx_port = 33060 mysqlx_socket = /usr/local/mysql/data/mysqlx.sock basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket = /usr/local/mysql/data/mysql.sock pid-file = /usr/local/mysql/data/mysqld.pid log-error = /usr/local/mysql/log/error.log注意： 大家在互联网上经常看到的认证属性已经被移除了，但是，该密码插件选项依然很管用。 甚至默认的密码插件在集群操作的时候，不能用。 default-authentication-plugin = mysql_native_passwordmysqlx： 传统的 MySQL 客户端 - 服务器通信主要基于经典的 SQL 接口。随着现代应用开发需求的变化，如对JSON 数据类型的更好支持、更高效的文档存储和检索、以及更适合现代编程语言的 API 等，MySQL 引入了X DevAPI。而 mysqlx 就是与这个新 API 相关的重要部分，它提供了新的通信协议和客户端库，使得开发 者可以更方便地使用 MySQL 数据库。 使用 mysqlx 命令行客户端来测试和执行基于 X DevAPI 的操作。例如，通过该客户端可以使用新的语法进行数据库查询和操作。注意：配置文件中涉及到的配置目录，必须存在，否则无法运行创建数据目录创建依赖目录 [root@ubuntu24-16:~]# mkdir /usr/local/mysql/{data,log} 更改文件属性 [root@ubuntu24-16:~]# chown -R mysql:mysql /usr/local/mysql如果我们采用的是软连接的方式，上面赋权的时候，命令应该是： chown -R mysql:mysql /usr/local/mysql* 注意：mysql后面有一个 *，如果不加，会导致很多的权限问题，比如 2024-12-08T06:32:09.439274Z 0 [ERROR] [MY-010187] [Server] Could not open file '/data/server/mysql/logs/mysql.log' for error logging: Permission denied 2024-12-08T06:32:09.439380Z 0 [ERROR] [MY-013236] [Server] The designated data directory /data/server/mysql/data/ is unusable. You can remove all files that the server added to it. 2024-12-08T06:32:09.439412Z 0 [ERROR] [MY-010119] [Server] Aborting8.7.环境初始化8.7.1.密码初始化初始化,本地root用户 - 使用密码如果使用 --initialize 选项会生成随机密码，要去 /data/mysql/mysql.log中查看 如果使用 --initialize-insecure -选项会生成空密码[root@ubuntu24-16:~]# cd /usr/local/mysql/ [root@ubuntu24-16:mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@ubuntu24-16:mysql]# grep 'temporary password' /usr/local/mysql/log/error.log 2025-05-07T14:55:15.598967Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: god&ia6haBrC8.7.2.空密码初始化初始化,本地root用户 - 使用空密码清理历史文件 [root@ubuntu24-16:~]# rm -rf /usr/local/mysql/data/* [root@ubuntu24-16:~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@ubuntu24-16:~]# tail -f /usr/local/mysql/log/error.log ...... 2025-05-07T14:58:05.616373Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. 2025-05-07T14:58:07.889872Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end. 结果提示：使用的是空密码确认文件 [root@ubuntu24-16:~]# ls /usr/local/mysql/data/ '#ib_16384_0.dblwr' auto.cnf client-key.pem mysql.ibd public_key.pem undo_001 '#ib_16384_1.dblwr' ca-key.pem ib_buffer_pool mysql_upgrade_history server-cert.pem undo_002 '#innodb_redo' ca.pem ibdata1 performance_schema server-key.pem '#innodb_temp' client-cert.pem mysql private_key.pem sys8.8.服务脚本定制启动脚本该脚本不是systemd风格的脚本，但是可以被 systemd兼容 [root@ubuntu24-16:~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld重载配置文件 [root@ubuntu24-16:~]# systemctl daemon-reload 启动mysqld服务 [root@ubuntu24-16:~]# /etc/init.d/mysqld start Starting mysqld (via systemctl): mysqld.service查看自动生成的服务管理文件 [root@ubuntu24-16:~]# systemctl cat mysqld.service # /run/systemd/generator.late/mysqld.service # Automatically generated by systemd-sysv-generator [Unit] Documentation=man:systemd-sysv-generator(8) SourcePath=/etc/init.d/mysqld Description=LSB: start and stop MySQL After=network-online.target After=remote-fs.target After=ypbind.service After=nscd.service After=ldap.service After=ntpd.service After=xntpd.service Wants=network-online.target [Service] Type=forking Restart=no TimeoutSec=5min IgnoreSIGPIPE=no KillMode=process GuessMainPID=no RemainAfterExit=yes SuccessExitStatus=5 6 ExecStart=/etc/init.d/mysqld start ExecStop=/etc/init.d/mysqld stop ExecReload=/etc/init.d/mysqld reload检测服务状态 [root@ubuntu24-16:~]# systemctl status mysqld ● mysqld.service - LSB: start and stop MySQL Loaded: loaded (/etc/init.d/mysqld; generated) Active: active (running) 查看端口连接 [root@ubuntu24-16:~]# netstat -tunlp | grep mysql tcp6 0 0 :::33060 :::* LISTEN 7349/mysqld tcp6 0 0 :::3306 :::* LISTEN 7349/mysqld8.9.连接测试客户端连接[root@ubuntu24-16:~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.4.0 MySQL Community Server - GPL 查看当前的数据库版本 mysql> select version(); +-----------+ | version() | +-----------+ | 8.4.0 | +-----------+ 1 row in set (0.00 sec)修改密码 mysql> alter user root@'localhost' identified by 'Magedu'; Query OK, 0 rows affected (0.01 sec) mysql> exit Byemysql用户账号由两部分组成： 'USERNAME'@'HOST' dange@'10.0.0.100' dange@'10.0.0.%' dange@'%' 说明：HOST限制此用户可通过哪些远程主机连接mysql服务器确认修改密码效果默认情况下，无法登录了 [root@ubuntu24-16:~]# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)使用用户名和密码，可以正常登录 [root@ubuntu24-16:~]# mysql -uroot -pMagedu mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.4.0 MySQL Community Server - GPL8.10.定制服务管理文件cat >/usr/lib/systemd/system/mysqld.service<<EOF [Unit] Description=MySQL Community Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s TERM $MAINPID LimitNOFILE = 10000 EOF重载配置 [root@ubuntu24-16:~]# systemctl daemon-reload 启动服务 [root@ubuntu24-16:~]# systemctl start mysqld检测效果 [root@ubuntu24-16:~]# systemctl start mysqld.service [root@ubuntu24-16:~]# netstat -tunlp | grep 3306 tcp6 0 0 :::33060 :::* LISTEN 8503/mysqld tcp6 0 0 :::3306 :::* LISTEN 8503/mysqld [root@ubuntu24-16:~]# systemctl status mysqld ● mysqld.service - MySQL Community Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; preset: enabled) Active: active (running) since Thu 2025-05-08 10:07:35 CST; 21s ago Main PID: 8503 (mysqld) Tasks: 36 (limit: 4552) Memory: 429.7M (peak: 443.2M) CPU: 2.465s CGroup: /system.slice/mysqld.service └─8503 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf May 08 10:07:35 ubuntu24-16 systemd[1]: Started mysqld.service - MySQL Community Server.9.Ubuntu中安装mysql9.2---二进制包安装9.1.基础环境安装依赖 [root@ubuntu24-13:~]# apt -y install libaio-dev numactl libnuma-dev libncurses-dev注意：ubuntu24系统没有libaio1的包，需要单独去下载安装 [root@ubuntu24-13:~]# curl -O http://launchpadlibrarian.net/646633572/libaio1_0.3.113-4_amd64.deb [root@ubuntu24-13:~]# dpkg -i libaio1_0.3.113-4_amd64.deb 否则环境初始化的时候，会发生如下报错 root@ubuntu24:mysql# bin/mysqld --initialize --user=mysql -- basedir=/usr/local/mysql --datadir=/usr/local/mysql/data bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory9.2.用户环境创建组和用户 [root@ubuntu24-13:~]# groupadd -r mysql [root@ubuntu24-13:~]# useradd -r -g mysql -s /sbin/nologin mysql9.3.软件环境获取软件 [root@ubuntu24-13:~]# mkdir -p /data/softs [root@ubuntu24-13:~]# cd /data/softs [root@ubuntu24-13:softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz解压至指定目录，这个目录只能写 /usr/local/ [root@ubuntu24-13:softs]# ls mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz [root@ubuntu24-13:softs]# tar xf mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz [root@ubuntu24-13:softs]# mv mysql-9.2.0-linux-glibc2.28-x86_64 /usr/local/mysql查看文件 [root@ubuntu24-13:softs]# ls /usr/local/mysql LICENSE README bin docs include lib man share support-files [root@ubuntu24-13:softs]# ls /usr/local/mysql/bin/ ibd2sdi myisamchk mysql_config mysql_tzinfo_to_sql mysqld mysqldump mysqlslap innochecksum myisamlog mysql_config_editor mysqladmin mysqld-debug mysqldumpslow perror my_print_defaults myisampack mysql_migrate_keyring mysqlbinlog mysqld_multi mysqlimport myisam_ftdump mysql mysql_secure_installation mysqlcheck mysqld_safe mysqlshow9.4.环境变量设定-可选创建环境变量 [root@ubuntu24-13:~]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@ubuntu24-13:~]# source /etc/profile.d/mysql.sh [root@ubuntu24-13:~]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin9.5.环境配置创建主配置文件 [root@ubuntu24-13:~]# mkdir /usr/local/mysql/etc [root@ubuntu24-13:~]# vim /usr/local/mysql/etc/my.cnf [root@ubuntu24-13:~]# cat /usr/local/mysql/etc/my.cnf [mysql] port = 3306 socket = /usr/local/mysql/data/mysql.sock [mysqld] port = 3306 mysqlx_port = 33060 mysqlx_socket = /usr/local/mysql/data/mysqlx.sock basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket = /usr/local/mysql/data/mysql.sock pid-file = /usr/local/mysql/data/mysqld.pid log-error = /usr/local/mysql/log/error.log注意： 大家在互联网上经常看到的认证属性已经被移除了，但是，该密码插件选项依然很管用。 甚至默认的密码插件在集群操作的时候，不能用。 default-authentication-plugin = mysql_native_password注意：配置文件中涉及到的配置目录，必须存在，否则无法运行创建数据目录创建依赖目录 [root@ubuntu24-13:~]# mkdir /usr/local/mysql/{data,log} 更改文件属性 [root@ubuntu24-13:~]# chown -R mysql:mysql /usr/local/mysql/如果我们采用的是软连接的方式，上面赋权的时候，命令应该是： chown -R mysql:mysql /usr/local/mysql* 注意：mysql后面有一个 *，如果不加，会导致很多的权限问题，比如 2025-05-08T06:32:09.439274Z 0 [ERROR] [MY-010187] [Server] Could not open file '/data/server/mysql/logs/mysql.log' for error logging: Permission denied 2025-05-08T06:32:09.439380Z 0 [ERROR] [MY-013236] [Server] The designated data directory /data/server/mysql/data/ is unusable. You can remove all files that the server added to it. 2025-05-08T06:32:09.439412Z 0 [ERROR] [MY-010119] [Server] Aborting9.6.环境初始化9.6.1.密码初始化初始化,本地root用户 - 使用密码如果使用 --initialize 选项会生成随机密码，要去 /data/mysql/mysql.log中查看 如果使用 --initialize-insecure -选项会生成空密码[root@ubuntu24-13:~]# cd /usr/local/mysql/ [root@ubuntu24-13:mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@ubuntu24-13:mysql]# grep 'temporary password' /usr/local/mysql/log/error.log 2025-05-08T02:27:47.810610Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: msgQ:*jwk1Kb9.6.2.空密码初始化初始化,本地root用户 - 使用空密码清理历史文件 [root@ubuntu24-13:mysql]# rm -rf /usr/local/mysql/data/* [root@ubuntu24-13:mysql]# bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@ubuntu24-13:mysql]# tail -f /usr/local/mysql/log/error.log ...... 2025-05-08T02:30:16.497848Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. 2025-05-08T02:30:18.762028Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end. 结果提示：使用的是空密码确认文件 [root@ubuntu24-13:~]# ls /usr/local/mysql/data/ | xargs -n 3 printf "%-30s %-30s %-30s\n" #ib_16384_0.dblwr #ib_16384_1.dblwr #innodb_redo #innodb_temp auto.cnf ca-key.pem ca.pem client-cert.pem client-key.pem ib_buffer_pool ibdata1 mysql mysql.ibd mysql_upgrade_history performance_schema private_key.pem public_key.pem server-cert.pem server-key.pem sys undo_001 undo_0029.7.服务脚本定制启动脚本该脚本不是systemd风格的脚本，但是可以被 systemd兼容 [root@ubuntu24-13:~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 重载配置文件 [root@ubuntu24-13:~]# systemctl daemon-reload 启动mysqld服务 [root@ubuntu24-13:~]# /etc/init.d/mysqld start Starting mysqld (via systemctl): mysqld.service.查看自动生成的服务管理文件 [root@ubuntu24-13:~]# systemctl cat mysqld.service # /run/systemd/generator.late/mysqld.service # Automatically generated by systemd-sysv-generator [Unit] Documentation=man:systemd-sysv-generator(8) SourcePath=/etc/init.d/mysqld Description=LSB: start and stop MySQL After=network-online.target After=remote-fs.target After=ypbind.service After=nscd.service After=ldap.service After=ntpd.service After=xntpd.service Wants=network-online.target [Service] Type=forking Restart=no TimeoutSec=5min IgnoreSIGPIPE=no KillMode=process GuessMainPID=no RemainAfterExit=yes SuccessExitStatus=5 6 ExecStart=/etc/init.d/mysqld start ExecStop=/etc/init.d/mysqld stop ExecReload=/etc/init.d/mysqld reload检测服务状态 [root@ubuntu24-13:~]# systemctl status mysqld.service ● mysqld.service - LSB: start and stop MySQL Loaded: loaded (/etc/init.d/mysqld; generated) Active: active (running) 查看端口连接 [root@ubuntu24-13:~]# netstat -tunlp | grep mysql tcp6 0 0 :::33060 :::* LISTEN 8114/mysqld tcp6 0 0 :::3306 :::* LISTEN 8114/mysqld9.8.连接测试客户端连接 [root@ubuntu24-13:~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 9.2.0 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. 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>查看当前的数据库版本 mysql> select version(); +-----------+ | version() | +-----------+ | 9.2.0 | +-----------+ 1 row in set (0.00 sec) 修改密码 mysql> alter user root@'localhost' identified by 'Magedu'; Query OK, 0 rows affected (0.01 sec) mysql> exit Bye确认修改密码效果默认情况下，无法登录了 [root@ubuntu24-13:~]# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 使用用户名和密码，可以正常登录 [root@ubuntu24-13:~]# mysql -uroot -pMagedu mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 9.2.0 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. 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>9.9.定制服务管理文件cat >/usr/lib/systemd/system/mysqld.service<<EOF [Unit] Description=MySQL Community Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s TERM $MAINPID LimitNOFILE = 10000 EOF重载配置 [root@ubuntu24-13:~]# systemctl daemon-reload 启动服务 [root@ubuntu24-13:~]# systemctl start mysqld 查看端口情况 [root@ubuntu24-13:~]# netstat -tunlp | grep 3306 tcp6 0 0 :::33060 :::* LISTEN 8114/mysqld tcp6 0 0 :::3306 :::* LISTEN 8114/mysqld查看服务状态信息 [root@ubuntu24-13:~]# systemctl status mysqld.service ● mysqld.service - MySQL Community Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; preset: enabled) Active: active (running) 10.Rocky9中安装mysql8.0---二进制包安装10.1.基础环境安装依赖 [root@Rocky9-12 ~]# yum -y install libaio numactl-libs ncurses-compat-libs10.2.用户环境创建组和用户 [root@Rocky9-12 ~]# groupadd -r mysql [root@Rocky9-12 ~]# useradd -r -g mysql -s /sbin/nologin mysql10.3.软件环境获取软件 [root@Rocky9-12 ~]# mkdir -p /data/softs [root@Rocky9-12 ~]# cd /data/softs [root@Rocky9-12 softs]#wget https://downloads.mysql.com/archives/get/p/23/file/mysql-9.2.0-linux-glibc2.28-x86_64.tar.xzz解压至指定目录，这个目录只能写 /usr/local/ [root@Rocky9-12 softs]# ls mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz [root@Rocky9-12 softs]# tar xf mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz [root@Rocky9-12 softs]# mv mysql-9.2.0-linux-glibc2.28-x86_64 /usr/local/mysql[root@Rocky9-12 softs]# ls /usr/local/mysql bin docs include lib LICENSE man README share support-files [root@Rocky9-12 softs]# ls /usr/local/mysql/bin/ | xargs -n 3 printf "%-30s %-30s %-30s\n" ibd2sdi innochecksum myisamchk myisam_ftdump myisamlog myisampack my_print_defaults mysql mysqladmin mysqlbinlog mysqlcheck mysql_config mysql_config_editor mysqld mysqld-debug mysqld_multi mysqld_safe mysqldump mysqldumpslow mysqlimport mysql_migrate_keyring mysql_secure_installation mysqlshow mysqlslap10.4.环境变量设定--可选创建环境变量 [root@Rocky9-12 ~]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@Rocky9-12 ~]# source /etc/profile.d/mysql.sh [root@Rocky9-12 ~]# echo $PATH /usr/local/mysql/bin:/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin10.5.环境配置创建主配置文件 [root@Rocky9-12 ~]# mkdir /usr/local/mysql/etc [root@Rocky9-12 ~]# vim /usr/local/mysql/etc/my.cnf [root@Rocky9-12 ~]# cat /usr/local/mysql/etc/my.cnf [mysql] port = 3306 socket = /usr/local/mysql/data/mysql.sock [mysqld] port = 3306 mysqlx_port = 33060 mysqlx_socket = /usr/local/mysql/data/mysqlx.sock basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket = /usr/local/mysql/data/mysql.sock pid-file = /usr/local/mysql/data/mysqld.pid log-error = /usr/local/mysql/log/error.log创建数据目录创建依赖目录 [root@Rocky9-12 ~]# mkdir /usr/local/mysql/{data,log} 更改文件属性 [root@Rocky9-12 ~]# chown -R mysql:mysql /usr/local/mysql/10.6.环境初始化10.6.1.密码初始化[root@Rocky9-12 ~]# cd /usr/local/mysql/ [root@Rocky9-12 mysql]# bin/mysqld -initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 确认登录密码 [root@Rocky9-12 mysql]# grep 'temporary password' /usr/local/mysql/log/error.log 2025-05-08T02:59:08.664100Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: cZYk#4#T)2k710.6.2.空密码初始化初始化,本地root用户 - 使用空密码清理历史文件 [root@Rocky9-12 mysql]# rm -rf /usr/local/mysql/data/* [root@Rocky9-12 mysql]# bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@Rocky9-12 mysql]# tail -f /usr/local/mysql/log/error.log ...... 2025-05-08T03:00:45.561141Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. 2025-05-08T03:00:47.872042Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end. 结果显示：为空密码确认文件 [root@Rocky9-12 ~]# ls /usr/local/mysql/data/ | xargs -n 3 printf "%-30s %-30s %-30s\n" auto.cnf ca-key.pem ca.pem client-cert.pem client-key.pem #ib_16384_0.dblwr #ib_16384_1.dblwr ib_buffer_pool ibdata1 #innodb_redo #innodb_temp mysql mysql.ibd mysql_upgrade_history performance_schema private_key.pem public_key.pem server-cert.pem server-key.pem sys undo_001 undo_00210.7.定制服务管理文件cat >/usr/lib/systemd/system/mysqld.service<<EOF [Unit] Description=MySQL Community Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s TERM $MAINPID LimitNOFILE = 10000 EOF重载配置 [root@Rocky9-12 ~]# systemctl daemon-reload 启动mysql [root@Rocky9-12 ~]# systemctl start mysqld.service 查看端口连接 [root@Rocky9-12 ~]# netstat -tunlp | grep 3306 tcp6 0 0 :::33060 :::* LISTEN 2052/mysqld tcp6 0 0 :::3306 :::* LISTEN 2052/mysqld10.8.连接测试[root@Rocky9-12 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 9.2.0 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. 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> select version(); +-----------+ | version() | +-----------+ | 9.2.0 | +-----------+ 1 row in set (0.00 sec)修改密码 mysql> alter user root@'localhost' identified by 'Magedu'; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye默认情况下，无法登录了 [root@Rocky9-12 ~]# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)使用用户名和密码，可以正常登录 [root@Rocky9-12 ~]# mysql -uroot -pMagedu mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 9.2.0 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. 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 Bye11.Rocky9-12二进制格式安装 MySQL 5.7.4411.1.基础环境安装依赖 [root@Rocky9-12 ~]# yum -y install libaio numactl-libs ncurses-compat-libs11.2.准备用户创建组和用户 [root@Rocky9-12 softs]# groupadd -r mysql [root@Rocky9-12 softs]# useradd -r -g mysql -s /sbin/nologin mysql11.3.软件环境获取软件 [root@Rocky9-12 ~]# mkdir -p /data/softs [root@Rocky9-12 ~]# cd /data/softs [root@Rocky9-12 softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz解压至指定目录，这个目录只能写 /usr/local/ [root@Rocky9-12 softs]# ls mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@Rocky9-12 softs]# tar xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@Rocky9-12 softs]# mv mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql11.4.环境变量设定-可选[root@Rocky9-12 softs]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@Rocky9-12 softs]# source /etc/profile.d/mysql.sh [root@Rocky9-12 softs]# echo $PATH /usr/local/mysql/bin:/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin11.5.环境配置创建主配置文件 [root@Rocky9-12 softs]# mkdir /usr/local/mysql/etc [root@Rocky9-12 softs]# vim /usr/local/mysql/etc/my.cnf [root@Rocky9-12 softs]# cat /usr/local/mysql/etc/my.cnf [mysqld] port=3306 socket = /usr/local/mysql/data/mysql.sock user=mysql basedir = /usr/local/mysql datadir = /usr/local/mysql/data skip_name_resolve = 1 pid-file = /usr/local/mysql/data/mysqld.pid log-error = /usr/local/mysql/log/error.log [client] socket = /usr/local/mysql/data/mysql.sock11.6.创建数据目录，建议使用逻辑卷创建依赖目录 [root@Rocky9-12 softs]# mkdir /usr/local/mysql/{data,log} 更改文件属性 [root@Rocky9-12 softs]# chown -R mysql:mysql /usr/local/mysql/11.7.环境初始化11.7.1.密码初始化[root@Rocky9-12 ~]# cd /usr/local/mysql/bin [root@Rocky9-12 bin]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@Rocky9-12 bin]# grep 'temporary password' /usr/local/mysql/log/error.log 2025-05-08T11:36:50.323892Z 1 [Note] A temporary password is generated for root@localhost: p>(D,oCOv1;U11.7.2.空密码初始化初始化,本地root用户 - 使用空密码清理历史文件 [root@Rocky9-12 bin]# rm -rf /usr/local/mysql/data/* [root@Rocky9-12 bin]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@Rocky9-12 bin]# tail /usr/local/mysql/log/error.log ...... 2025-05-08T11:38:56.196531Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. 结果显示：为空密码确认文件 [root@Rocky9-12 bin]# ls /usr/local/mysql/data/ | xargs -n 3 printf "%-30s %-30s %-30s\n" auto.cnf ca-key.pem ca.pem client-cert.pem client-key.pem ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema private_key.pem public_key.pem server-cert.pem server-key.pem sys11.8.定制服务管理文件cat >/usr/lib/systemd/system/mysqld.service<<EOF [Unit] Description=MySQL Community Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s TERM $MAINPID LimitNOFILE = 10000 EOF重载配置 [root@Rocky9-12 bin]# systemctl daemon-reload 启动mysql [root@Rocky9-12 bin]# systemctl start mysqld.service 查看端口连接 [root@Rocky9-12 bin]# netstat -tunlp | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 1935/mysqld11.9.连接测试[root@Rocky9-12 bin]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.44 MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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>查看当前数据库版本 mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.44 | +-----------+ 1 row in set (0.00 sec)修改密码 mysql> alter user root@'localhost' identified by 'Magedu'; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye使用用户名和密码进行登录 [root@Rocky9-12 bin]# mysql -uroot -pMagedu mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.44 MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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 Bye12.ubuntu24-13二进制格式安装 MySQL 5.7.4412.1.基础环境安装依赖 [root@ubuntu24-13:~]# apt -y install libaio-dev12.2.准备用户创建组和用户 [root@ubuntu24-13:~]# groupadd -r mysql [root@ubuntu24-13:~]# useradd -r -g mysql -s /sbin/nologin mysql12.3.软件环境获取软件 [root@ubuntu24-13:~]# mkdir -p /data/softs [root@ubuntu24-13:~]# cd /data/softs [root@ubuntu24-13:softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz解压至指定目录，这个目录只能写 /usr/local/ [root@ubuntu24-13:softs]# ls mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@ubuntu24-13:softs]# tar xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@ubuntu24-13:softs]# mv mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql12.4.环境变量设定-可选[root@ubuntu24-13:softs]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@ubuntu24-13:softs]# source /etc/profile.d/mysql.sh [root@ubuntu24-13:softs]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin12.5.环境配置创建主配置文件 [root@ubuntu24-13:softs]# mkdir /usr/local/mysql/etc [root@ubuntu24-13:softs]# vim /usr/local/mysql/etc/my.cnf [root@ubuntu24-13:softs]# cat /usr/local/mysql/etc/my.cnf [mysqld] port=3306 user=mysql character-set-server=utf8 collation-server=utf8_general_ci default-storage-engine=INNODB # Only allow connections from localhost bind-address = 0.0.0.0 #sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections=100 #skip-name-resolve socket = /usr/local/mysql/data/mysql.sock basedir = /usr/local/mysql datadir = /usr/local/mysql/data #日志 #日志时间,默认是UTC log_timestamps=SYSTEM #错误日志 log-error = /usr/local/mysql/log/error.log #开启二进制日志 log-bin=/usr/local/mysql/mysql-bin server-id=1 #开启通用查询日志 #general_log=ON #general_log_file=/usr/local/mysql/mysql-query.log #慢查询日志 slow_query_log=ON slow_query_log_file=/usr/local/var/log/mysql/mysql-slow.log long_query_time=1 #记录不使用索引查询的语句 log_queries_not_using_indexes=ON #修改密码校验等级 plugin-load-add=validate_password.so validate-password=FORCE_PLUS_PERMANENT validate_password_policy=0 validate_password_length=4 #设置导入导出的安全目录 secure_file_priv="" skip_name_resolve = 1 pid-file = /usr/local/mysql/data/mysqld.pid [mysql] default-character-set=utf8 auto-rehash [client] port=3306 socket = /usr/local/mysql/data/mysql.sock其中log_error文件需要事先创建，同时注意文件权限为mysql12.6.创建数据目录，建议使用逻辑卷创建依赖目录 [root@ubuntu24-13:softs]# mkdir /usr/local/mysql/{data,log} 更改文件属性 [root@ubuntu24-13:softs]# chown -R mysql:mysql /usr/local/mysql/12.7.环境初始化密码初始化[root@ubuntu24-13:~]# cd /usr/local/mysql/bin [root@ubuntu24-13:~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@ubuntu24-13:~]# grep 'temporary password' /usr/local/mysql/log/error.log 2025-05-09T13:33:21.997935+08:00 1 [Note] A temporary password is generated for root@localhost: ,?ssSMZ)z419报错: [root@ubuntu24-13:~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data /usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory 解决问题： 这是由于系统安装的版本过高。通过建立软连接方式解决 查看自己系统下对应的版本，建立软连接 [root@ubuntu24-13:~]# ldd /usr/local/mysql/bin/mysqld | grep libaio libaio.so.1 => not found [root@ubuntu24-13:~]# find / -name libaio.so* /usr/lib/x86_64-linux-gnu/libaio.so.1t64 /usr/lib/x86_64-linux-gnu/libaio.so.1t64.0.2 /usr/lib/x86_64-linux-gnu/libaio.so # 创建软链接 [root@ubuntu24-13:~]# ln -s /usr/lib/x86_64-linux-gnu/libaio.so /usr/lib/x86_64-linux-gnu/libaio.so.1 # 检查库是否存在 [root@ubuntu24-13:~]# ll /lib/x86_64-linux-gnu/libaio.so.1 lrwxrwxrwx 1 root root 35 May 9 13:25 /lib/x86_64-linux-gnu/libaio.so.1 -> /usr/lib/x86_64-linux-gnu/libaio.so # 验证动态链接器能否找到库 [root@ubuntu24-13:~]# ldd /usr/local/mysql/bin/mysqld | grep libaio libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007c61e38ec000)报错： [root@ubuntu24-13:~]# mysql mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory [root@ubuntu24-13:~]# ln -s /usr/lib/x86_64-linux-gnu/libncurses.so.6.4 /usr/lib/x86_64-linux-gnu/libncurses.so.5 [root@ubuntu24-13:~]# ln -s /usr/lib/x86_64-linux-gnu/libtinfo.so.6.4 /usr/lib/x86_64-linux-gnu/libtinfo.so.5确认文件 [root@ubuntu24-13:~]# ls /usr/local/mysql/data/ | xargs -n 3 printf "%-30s %-30s %-30s\n" auto.cnf ca-key.pem ca.pem client-cert.pem client-key.pem ib_buffer_pool ib_logfile0 ib_logfile1 ibdata1 mysql performance_schema private_key.pem public_key.pem server-cert.pem server-key.pem sys12.8.定制服务管理文件cat >/usr/lib/systemd/system/mysqld.service<<EOF [Unit] Description=MySQL Community Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s TERM $MAINPID LimitNOFILE = 10000 EOF重载配置 [root@ubuntu24-13:~]# systemctl daemon-reload 启动mysql [root@ubuntu24-13:~]# systemctl start mysqld.service 查看端口连接 [root@ubuntu24-13:~]# netstat -tunlp | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 1935/mysqld12.9.连接测试[root@ubuntu24-13:~]# mysql -uroot -p",?ssSMZ)z419" mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.44-log Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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>查看当前数据库版本 mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.44 | +-----------+ 1 row in set (0.00 sec)修改密码并授予权限 mysql> alter user root@'localhost' identified by 'Mysql.123456'; Query OK, 0 rows affected (0.01 sec) mysql> grant all privileges on *.* to root@'%' identified by 'Mysql.123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye[root@ubuntu24-13:~]# mysql -uroot -p'Mysql.123456' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.44-log MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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 Bye13.通用二进制格式安装 MySQL 5.6.5013.1.基础环境安装依赖 [root@Rocky9-12 ~]# yum install -y perl-Data-Dumper autoconf libaio perl-Sys-Hostname ncurses-compat-libs常见错误：缺少安装依赖，会报如下错误 [root@Rocky9-12 mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data FATAL ERROR: please install the following Perl modules before executing ./scripts/mysql_install_db: Sys::Hostname[root@Rocky9-12 ~]# mysql mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory [root@Rocky9-12 ~]# mysql -uroot -p mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory 在 Rocky Linux 9 系统中，可以使用 dnf 命令安装 libncurses5 相关的包。不过 Rocky Linux 9 中可能没有直接提供 libncurses.so.5 版本，你可以尝试安装兼容的 ncurses 包： sudo dnf install ncurses-compat-libs 这个包通常会包含 libncurses.so.5 或者提供其兼容的版本。13.2.准备用户创建组和用户 [root@Rocky9-12 ~]# groupadd -r mysql [root@Rocky9-12 ~]# useradd -r -g mysql -s /sbin/nologin mysql13.3.软件环境获取软件 [root@Rocky9-12 ~]# mkdir -p /data/softs [root@Rocky9-12 ~]# cd /data/softs [root@Rocky9-12 softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz解压至指定目录，这个目录只能写 /usr/local/ [root@Rocky9-12 softs]# ls mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz [root@Rocky9-12 softs]# tar xf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz [root@Rocky9-12 softs]# mv mysql-5.6.50-linux-glibc2.12-x86_64 /usr/local/mysql13.4.环境变量设定--可选[root@Rocky9-12 softs]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@Rocky9-12 softs]# source /etc/profile.d/mysql.sh [root@Rocky9-12 softs]# echo $PATH /usr/local/mysql/bin:/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin13.5.环境配置创建主配置文件 [root@Rocky9-12 softs]# mkdir /usr/local/mysql/etc [root@Rocky9-12 softs]# vim /usr/local/mysql/etc/my.cnf [root@Rocky9-12 softs]# cat /usr/local/mysql/etc/my.cnf [mysqld] port=3306 socket = /usr/local/mysql/data/mysql.sock user=mysql basedir = /usr/local/mysql datadir = /usr/local/mysql/data innodb_file_per_table=on skip_name_resolve = on symbolic-links=0 [client] socket = /usr/local/mysql/data/mysql.sock [mysqld_safe] pid-file = /usr/local/mysql/data/mysqld.pid log-error = /usr/local/mysql/log/error.log13.6.创建数据目录，建议使用逻辑卷创建依赖目录 [root@Rocky9-12 softs]# mkdir /usr/local/mysql/{data,log} 更改文件属性 [root@Rocky9-12 softs]# chown -R mysql:mysql /usr/local/mysql/13.7.环境初始化[root@Rocky9-12 softs]# cd /usr/local/mysql [root@Rocky9-12 mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data FATAL ERROR: please install the following Perl modules before executing ./scripts/mysql_install_db: Sys::Hostname确认文件 [root@Rocky9-12 mysql]# ll /usr/local/mysql/data/ total 110600 -rw-rw---- 1 mysql mysql 12582912 May 8 20:06 ibdata1 -rw-rw---- 1 mysql mysql 50331648 May 8 20:06 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 May 8 20:06 ib_logfile1 drwx------ 2 mysql mysql 4096 May 8 20:06 mysql drwx------ 2 mysql mysql 4096 May 8 20:06 performance_schema drwxr-xr-x 2 mysql mysql 20 May 8 19:56 test13.8.定制服务管理文件cat >/usr/lib/systemd/system/mysqld.service<<EOF [Unit] Description=MySQL Community Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s TERM $MAINPID LimitNOFILE = 10000 EOF重载配置 [root@Rocky9-12 bin]# systemctl daemon-reload 启动mysql [root@Rocky9-12 bin]# systemctl start mysqld.service 查看端口连接 [root@Rocky9-12 bin]# netstat -tunlp | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 1935/mysqld13.9.连接测试[root@Rocky9-12 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.50 MySQL Community Server (GPL) Copyright (c) 2000, 2020, 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>查看当前版本信息 mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.50 | +-----------+ 1 row in set (0.01 sec)修改密码 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> set password for 'root'@'localhost'=password('123456'); Query OK, 0 rows affected (0.01 sec) mysql> exit Bye-- 刷新权限表 FLUSH PRIVILEGES; -- 修改 root 用户在 localhost 上的密码 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');使用用户名和密码进行登录 [root@Rocky9-12 ~]# mysql -uroot -p123456 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.50 MySQL Community Server (GPL) Copyright (c) 2000, 2020, 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 Bye13.10.初始化脚本提高安全性范例: 针对MySQL5.6前版本进行安全加固运行脚本：mysql_secure_installation 设置数据库管理员root口令 禁止root远程登录 删除anonymous用户帐号 删除test数据库[root@Rocky9-12 ~]# mysql -uroot -pMagedu Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.50 MySQL Community Server (GPL) Copyright (c) 2000, 2020, 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | | localhost | | root | localhost | | | rocky9-12 | | root | rocky9-12 | +------+-----------+ 6 rows in set (0.00 sec) mysql> exit Bye[root@Rocky9-12 ~]# file `which mysql_secure_installation` /usr/local/mysql/bin/mysql_secure_installation: Perl script text executable做个软链接，默认文件为/tmp/mysql.sock，否则会报如下错误 下面直接指定socket文件路径，也不行 [root@Rocky9-12 ~]# ln -s /usr/local/mysql/data/mysql.sock /tmp/mysql.sock [root@Rocky9-12 ~]# ll /tmp/mysql.sock lrwxrwxrwx 1 root root 32 May 8 21:08 /tmp/mysql.sock -> /usr/local/mysql/data/mysql.sock 错误： [root@Rocky9-12 ~]# /usr/local/mysql/bin/mysql_secure_installation --socket=/usr/local/mysql/data/mysql.sock NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Enter current password for root (enter for none): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Enter current password for root (enter for none): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Unable to connect to the server as root user, giving up. Cleaning up...[root@Rocky9-12 ~]# /usr/local/mysql/bin/mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MySQL root user without the proper authorisation. You already have a root password set, so you can safely answer 'n'. Change the root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! All done! If you've completed all of the above steps, your MySQL installation should now be secure. Thanks for using MySQL! Cleaning up...14.常见的报错信息14.1.报错信息[root@Rocky9-12 ~]#echo $PATH /root/.local/bin:/root/bin:/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin [root@Rocky9-12 ~]# whereis mysqld mysqld: /usr/local/mysql/bin/mysqld [root@Rocky9-12 ~]# ll /usr/local/mysql/bin/mysqld -rwxr-xr-x 1 mysql mysql 211661577 Oct 11 2023 /usr/local/mysql/bin/mysqld [root@Rocky9-12 ~]# mysqld -bash: /usr/local/mysql/bin/mysqld: No such file or directory文件存在，但提示文件一直提示找不到14.2.分析问题当你使用 whereis 命令能找到 mysqld 的路径，并且通过 ll 命令能看到该文件确实存在，但执行 mysqld 时却提示 “No such file or directory”，这通常不是文件本身不存在，而是因为缺少该程序运行所需的依赖库或者动态链接库路径配置有误。下面为你详细分析可能的原因及解决办法：1. 缺少 32 位兼容库 如果 mysqld 是 32 位程序，而你的系统是 64 位的，可能会因为缺少 32 位兼容库而导致找不到依赖的动态链接器。 解决办法： 在 Rocky Linux 9 系统中，你可以使用以下命令安装 32 位兼容库： dnf install -y glibc.i6862. 动态链接库路径配置问题 mysqld 程序可能依赖于某些特定的动态链接库，而这些库的路径没有被正确配置到系统的动态链接库搜索路径中。 解决办法： 临时解决：使用 LD_LIBRARY_PATH 环境变量来临时指定动态链接库的搜索路径。例如，如果 mysqld 依赖的库在 /usr/local/mysql/lib 目录下，可以执行以下命令： export LD_LIBRARY_PATH=/usr/local/mysql/lib:$LD_LIBRARY_PATH mysqld 永久解决：创建一个新的.conf文件，将动态链接库的路径添加到该文件中，然后更新动态链接库缓存。 echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf ldconfig3. 文件损坏 mysqld 文件本身可能已经损坏，导致系统无法正确执行。4. SELinux 或防火墙限制 SELinux 或者防火墙可能会阻止 mysqld 程序的执行。 解决办法 SELinux：可以临时禁用 SELinux 来进行测试： setenforce 0 如果禁用 SELinux 后 mysqld 能够正常执行，你可以修改 SELinux 的策略来允许 mysqld 运行。 防火墙：确保防火墙没有阻止 mysqld 所需的端口（默认是 3306）。可以使用以下命令开放端口： firewall-cmd --zone=public --add-port=3306/tcp --permanent firewall-cmd --reload14.3.解决问题按照以上步骤操作，应该能够解决 mysqld 执行时提示 “No such file or directory” 的问题。 最后发现自己下载的第一个32位软件 自己下错版本信息了 最后下载64位安装就成功了，再也没有提示错误了endl