详细探索如何用脚本实现M小ySQL一键安装与配置，提升运维效率！

以下是基于脚本实现MySQL一键安装与配置的详细方案，涵盖Linux主流系统（CentOS/Ubuntu）及Windows环境，结合自动化部署与高可用性扩展，旨在提升运维效率：

一、Linux系统（CentOS 7.x）一键安装脚本方案

1. 基于YUM源的自动化安装（MySQL 5.7/8.0）

#!/bin/bash
# 变量定义
VERSION="8.0"  # 可选5.7或8.0
ROOT_PASSWORD="StrongPass@123"
BASE_DIR="/usr/local/mysql"

# 环境准备
yum install -y wget net-tools
wget https://dev.mysql.com/get/mysql $ {VERSION}-community-release-el7-11.noarch.rpm
yum install -y mysql $ {VERSION}-community-release-el7-11.noarch.rpm
yum install -y mysql-community-server

# 初始化服务
systemctl start mysqld
systemctl enable mysqld

# 获取临时密码并重置
TEMP_PASSWORD= $ (grep 'temporary password' /var/log/mysqld.log | awk '{print  $ NF}')
mysql -uroot -p $ {TEMP_PASSWORD} --connect-expired-password -e "ALTER USER 'root'@'localhost' IDENTIFIED BY ' $ {ROOT_PASSWORD}';"

# 配置远程访问与字符集
mysql -uroot -p $ {ROOT_PASSWORD} -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY ' $ {ROOT_PASSWORD}' WITH GRANT OPTION;"
mysql -uroot -p $ {ROOT_PASSWORD} -e "FLUSH PRIVILEGES;"
echo "[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-authentication-plugin=mysql_native_password" >> /etc/my.cnf

systemctl restart mysqld

2. 二进制包手动安装（MySQL 8.0）

#!/bin/bash
# 变量定义
VERSION="8.0.34"
ROOT_PASSWORD="SecurePass@456"
DATA_DIR="/data/mysql"

# 环境准备
groupadd mysql && useradd -r -g mysql mysql
mkdir -p  $ DATA_DIR/{data,logs,conf}
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql- $ {VERSION}-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql- $ {VERSION}-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
ln -s /usr/local/mysql- $ {VERSION}-linux-glibc2.12-x86_64 /usr/local/mysql

# 初始化数据库
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir= $ DATA_DIR/data
mv /var/log/mysqld.log  $ DATA_DIR/logs/
chmod 755  $ DATA_DIR -R
chown -R mysql:mysql  $ DATA_DIR /usr/local/mysql

# 配置环境变量
echo 'export PATH= $ PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile

# 创建服务文件
cat <<EOF > /etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
After=syslog.target network.target

[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir= $ DATA_DIR/data --pid-file= $ DATA_DIR/mysqld.pid
ExecReload=/bin/kill -HUP  $ MAINPID
ExecStop=/usr/local/mysql/bin/mysqladmin shutdown -u root -p $ {ROOT_PASSWORD}
PrivateTmp=true

[Install]
WantedBy=multi-user.target
EOF

# 启动服务并配置
systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld

# 获取临时密码并重置
TEMP_PASSWORD= $ (grep 'temporary password'  $ DATA_DIR/logs/mysqld.log | awk '{print  $ NF}')
mysql -uroot -p $ {TEMP_PASSWORD} --connect-expired-password -e "ALTER USER 'root'@'localhost' IDENTIFIED BY ' $ {ROOT_PASSWORD}';"
mysql -uroot -p $ {ROOT_PASSWORD} -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY ' $ {ROOT_PASSWORD}' WITH GRANT OPTION;"
mysql -uroot -p $ {ROOT_PASSWORD} -e "FLUSH PRIVILEGES;"

二、Windows系统一键安装脚本方案

1. 批处理脚本（MySQL 8.0）

@echo off
setlocal enabledelayedexpansion

set INSTALL_DIR=C:\mysql-8.0.34-winx64
set DATA_DIR=C:\mysql_data
set ROOT_PASSWORD=Admin@123

:: 下载并解压
bitsadmin.exe /transfer MySQLDownload /download /priority normal https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-%VERSION%-winx64.zip %TEMP%\mysql.zip
powershell Expand-Archive -Path %TEMP%\mysql.zip -DestinationPath %INSTALL_DIR%

:: 创建数据目录
mkdir %DATA_DIR%

:: 初始化配置
echo [mysqld] > %INSTALL_DIR%\my.ini
echo basedir = "%INSTALL_DIR%" >> %INSTALL_DIR%\my.ini
echo datadir = "%DATA_DIR%" >> %INSTALL_DIR%\my.ini
echo port = 3306 >> %INSTALL_DIR%\my.ini
echo character-set-server=utf8mb4 >> %INSTALL_DIR%\my.ini
echo default-authentication-plugin=mysql_native_password >> %INSTALL_DIR%\my.ini

:: 初始化数据库
%INSTALL_DIR%\bin\mysqld --initialize --console --datadir=%DATA_DIR% --user=root
set /p TEMP_PASSWORD=请输入临时密码:

:: 修改密码并配置远程访问
%INSTALL_DIR%\bin\mysql -u root -p%TEMP_PASSWORD% --connect-expired-password -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '%ROOT_PASSWORD%';"
%INSTALL_DIR%\bin\mysql -u root -p%ROOT_PASSWORD% -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '%ROOT_PASSWORD%' WITH GRANT OPTION;"
%INSTALL_DIR%\bin\mysql -u root -p%ROOT_PASSWORD% -e "FLUSH PRIVILEGES;"

:: 安装服务
%INSTALL_DIR%\bin\mysqld --install MySQL80
net start MySQL80

三、自动化部署高级方案

1. Ansible批量部署（支持多节点）

#(ansible-playbook)
- name: Deploy MySQL Cluster
  hosts: mysql_nodes
  become: yes
  tasks:
    - name: Install MySQL Repository
      yum:
        name: https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm
        state: present

    - name: Install MySQL Server
      yum:
        name: mysql-community-server
        state: present

    - name: Start MySQL Service
      service:
        name: mysqld
        state: started
        enabled: yes

    - name: Get Initial Password
      shell: grep 'temporary password' /var/log/mysqld.log | awk '{print  $ NF}'
      register: temp_password

    - name: Set Root Password
      mysql_user:
        login_user: root
        login_password: "{{ temp_password.stdout }}"
        check_implicit_admin: yes
        name: root
        password: StrongPass@123
        host_all: yes

    - name: Configure Remote Access
      mysql_query:
        login_user: root
        login_password: StrongPass@123
        query: "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'StrongPass@123'; FLUSH PRIVILEGES;"

2. 高可用性扩展（MySQL Group Replication）

# 主节点配置（脚本片段）
mysql -uroot -p -e "CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplicaPass@456';"
mysql -uroot -p -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';"
mysql -uroot -p -e "CHANGE MASTER TO MASTER_HOST='secondary_ip', MASTER_USER='repl', MASTER_PASSWORD='ReplicaPass@456';"
mysql -uroot -p -e "INSTALL PLUGIN group_replication SONAME 'group_replication.so';"
mysql -uroot -p -e "SET GLOBAL group_replication_group_name='uuid';"
mysql -uroot -p -e "SET GLOBAL group_replication_start_on_BOOT='ON';"
mysql -uroot -p -e "START GROUP_REPLICATION;"

四、关键配置优化建议

1. 性能调优
   • 调整innodb_buffer_pool_size为物理内存的70%
   • 启用查询缓存：query_cache_type=1（MySQL 8.0后默认禁用）
   • 日志优化：slow_query_log=1 + long_query_time=2
2. 安全加固
   • 禁用匿名账户：DROP USER ''@'localhost';
   • 删除测试数据库：DROP DATABASE test;
   • 配置防火墙规则：firewall-cmd --add-port=3306/tcp --permanent
3. 监控集成
   • 安装Percona Monitoring Toolkit
   • 配置Prometheus + Grafana监控面板

五、脚本执行流程图

开始
├─ 检查系统环境（依赖、防火墙、SELinux）
├─ 下载安装包/配置源
├─ 执行安装命令
├─ 获取临时密码并重置
├─ 配置字符集与远程访问
├─ 初始化数据库目录权限
├─ 启动服务并验证状态
└─ 输出最终配置报告

六、常见问题解决方案

1. 端口冲突：修改my.cnf中的port参数
2. 权限不足：确保mysql用户对数据目录有读写权限
3. SSL错误：添加skip-grant-tables临时启动后修复权限
4. 服务启动失败：检查mysqld.log日志定位具体错误

通过上述脚本方案，可实现MySQL的快速部署与标准化配置，结合Ansible等工具可进一步扩展为集群化部署，显著提升运维效率。建议根据实际业务需求选择脚本模板并定制化调整参数。