Ubuntu服务器优化：MySQL 5.7升级到8.0并集成大模型服务的实践方案

引言

MySQL 8.0相比5.7版本在性能、安全性和功能上都有显著提升，特别是对大模型数据处理的支持更好。本文将详细介绍如何在Ubuntu服务器上安全地将MySQL 5.7升级到8.0，并配置服务器以支持大模型服务。

准备工作

环境要求

Ubuntu Server 18.04/20.04/22.04
MySQL 5.7已安装并运行
root或sudo权限
至少4GB可用内存（大模型服务需要更多资源）

检查当前MySQL版本

代码片段

mysql --version
# 预期输出类似: mysql Ver 14.14 Distrib 5.7.x, for Linux (x86_64)

第一步：备份现有数据库

这是最重要的步骤！升级前必须完整备份。

代码片段

# 创建备份目录
sudo mkdir /var/mysql-backup
sudo chown -R $USER:$USER /var/mysql-backup

# 使用mysqldump进行完整备份（替换yourusername为实际用户名）
mysqldump -u yourusername -p --all-databases --routines --triggers > /var/mysql-backup/full-backup-$(date +%F).sql

# 验证备份文件大小（应大于0）
ls -lh /var/mysql-backup/

注意事项：
1. 如果数据库很大，考虑使用--single-transaction参数避免锁表
2. 生产环境建议同时做物理备份（复制/var/lib/mysql目录）

第二步：卸载MySQL 5.7

代码片段

# 停止MySQL服务
sudo systemctl stop mysql

# 列出已安装的MySQL相关包
dpkg -l | grep mysql | grep ii

# 卸载MySQL相关包（根据上一步列出的包调整）
sudo apt-get remove --purge mysql-server mysql-client mysql-common mysql-client-core-* mysql-server-core-*

# 清理残留数据（可选，但建议执行）
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

第三步：安装MySQL 8.0

代码片段

# 添加官方MySQL APT仓库
wget https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.22-1_all.deb 
# （在图形界面中选择MySQL8.0，然后选择OK）

# 更新包列表并安装
sudo apt-get update
sudo apt-get install mysql-server

# MySQL8.0默认使用新的认证插件caching_sha2_password，
# 如果应用不支持，需要在安装时选择传统认证方式：
# sudo mysql_secure_installation

验证安装：

代码片段

mysql --version
# 预期输出: mysql Ver 8.0.x for Linux on x86_64 (MySQL Community Server)

第四步：恢复数据

代码片段

# 启动MySQL服务
sudo systemctl start mysql

# （可选）如果备份文件中有CREATE USER语句，可能需要先创建临时管理员账户：
sudo mysql -e "CREATE USER 'tempadmin'@'localhost' IDENTIFIED BY 'TempPass123!';"
sudo mysql -e "GRANT ALL PRIVILEGES ON *.* TO 'tempadmin'@'localhost' WITH GRANT OPTION;"

# （重要）修改SQL文件中的旧密码哈希格式为新的格式：
sed -i 's/IDENTIFIED BY PASSWORD/IDENTIFIED WITH mysql_native_password BY/' /var/mysql-backup/full-backup-*.sql

# （重要）移除SQL文件中可能存在的NO_AUTO_CREATE_USER模式设置（MySQL8不再支持）
sed -i 's/NO_AUTO_CREATE_USER,//g' /var/mysql-backup/full-backup-*.sql

# （重要）移除SQL文件中可能存在的旧密码过期设置（可能导致问题）
sed -i 's/PASSWORD EXPIRE NEVER;//g' /var/mysql-backup/full-backup-*.sql

# （重要）恢复数据前禁用外键检查以避免导入错误：
echo "SET FOREIGN_KEY_CHECKS=0;" | cat - /var/mysql-backup/full-backup-*.sql > temp && mv temp /var/mysql-backup/full-backup-*.sql

# （重要）在文件末尾重新启用外键检查：
echo "SET FOREIGN_KEY_CHECKS=1;" >> /var/mysql-backup/full-backup-*.sql

# （重要）执行恢复操作（使用临时账户或root）
mysql -u tempadmin -pTempPass123! < /var/mysql-backup/full-backup-*.sql || echo "恢复过程中出现错误"

常见问题解决：
1. 认证插件不兼容错误：编辑/etc/mysql/my.cnf添加default-authentication-plugin=mysql_native_password后重启服务
2. 字符集问题：确保my.cnf中有正确的字符集配置：

代码片段

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci<br>

MySQL8优化配置调整

编辑/etc/mysql/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf：

代码片段

[mysqld]
innodb_buffer_pool_size = XG # X=总内存的50%-70%
innodb_log_file_size = YG    # Y=缓冲池大小的25%
innodb_flush_method = O_DIRECT # Linux系统推荐值 
innodb_flush_neighbors = OFF # SSD建议关闭 
innodb_read_only_compressed=OFF # SSD建议关闭压缩页优化 

max_connections = Z          # Z=根据应用需求调整(默认151)
table_open_cache = W         # W=max_connections的倍数(通常2x)

log_error=/var/log/mysql/error.log #错误日志路径 
slow_query_log=ON           #开启慢查询日志 
slow_query_log_file=/var/log/mysql/slow.log 
long_query_time=2           #慢查询阈值(秒) 

default-authentication-plugin=mysql_native_password #兼容性设置 

performance_schema=ON       #性能监控工具 

character-set-server=utf8mb4 #完整UTF8支持 
collation-server=utf8mb4_unicode_ci 

binlog_expire_logs_seconds=2592000 #二进制日志保留30天(可选) 
binlog_format=ROW           #行模式复制更可靠 

transaction_isolation=READ-COMMITTED #事务隔离级别 

query_cache_type=OFF        # MySQL8已移除查询缓存功能 

innodb_print_all_deadlocks=ON #记录所有死锁信息 

innodb_dedicated_server=ON   #自动配置InnoDB参数(适用于专用服务器)

重启服务使配置生效：

代码片段

sudo systemctl restart mysql && sudo systemctl status mysql

MySQL与大模型服务集成实践方案

Python连接示例代码（含连接池）

代码片段

import pymysql.cursors  
from pymongo import MongoClient  
from sqlalchemy import create_engine  

class MySQLConnector:  
    def __init__(self):  
        self.pool = None  

    def create_pool(self):  
        """创建连接池"""  
        try:  
            self.pool = create_engine(  
                'mysql+pymysql://username:password@localhost:3306/dbname',  
                pool_size=10,  
                max_overflow=20,  
                pool_recycle=3600,  
                pool_pre_ping=True,  
                connect_args={'charset': 'utf8mb4'}  
            )  
            print("连接池创建成功")  
        except Exception as e:  
            print(f"连接池创建失败: {str(e)}")  

    def get_data_for_model(self, query):  
        """获取大模型训练数据"""  
        try:  
            with self.pool.connect() as conn:  
                result = conn.execute(query).fetchall()  
                return result if result else None  
        except Exception as e:  
            print(f"查询失败: {str(e)}")  

    def save_model_results(self, table_name, data_dict):  
        """保存大模型处理结果"""  
        try:  
            with self.pool.connect() as conn:  
                columns = ', '.join(data_dict.keys())   
                values = ', '.join([f"'{v}'" if isinstance(v, str) else str(v) for v in data_dict.values()])   
                query = f"INSERT INTO {table_name} ({columns}) VALUES ({values})"   
                conn.execute(query)   
                conn.commit()   
        except Exception as e:   
            print(f"保存失败: {str(e)}")  

if __name__ == "__main__":   
    db = MySQLConnector()   
    db.create_pool()   

    try:   
        training_data = db.get_data_for_model("SELECT * FROM training_dataset LIMIT 100")   

        if training_data:   
            processed_results = []   

            for row in training_data:   
                processed_results.append({...})   

            for result in processed_results[:10]:   
                db.save_model_results("model_results", result)   

    finally:   
        if db.pool is not None:
            db.pool.dispose()

MySQL向量搜索扩展（大模型场景）

代码片段

-- MySQL支持JSON字段和向量搜索(需配合应用程序实现)
ALTER TABLE documents ADD COLUMN embedding JSON COMMENT '文本嵌入向量';

-- JSON数组索引示例(需要生成列和虚拟索引)
ALTER TABLE documents ADD COLUMN embedding_virtual BLOB GENERATED ALWAYS AS (JSON_ARRAY_PACK(embedding)) VIRTUAL;
CREATE INDEX idx_document_vector ON documents((CAST(embedding_virtual AS BLOB)));

-- JSON向量相似度查询示例(使用点积近似计算)
SELECT id, content,
       JSON_ARRAY_PACK_DOT_PRODUCT(
           JSON_ARRAY_PACK('[0.1,0.2,0.3]'), 
           embedding_virtual) AS similarity_score 
FROM documents 
ORDER BY similarity_score DESC LIMIT 10;

Redis缓存层集成示例代码

代码片段

import redis
import json
from datetime import timedelta

class CacheManager:
    def __init__(self):
        self.client = redis.Redis(
            host='localhost',
            port=6379,
            db=0,
            decode_responses=True,
            socket_timeout=5,
            socket_connect_timeout=5,
            retry_on_timeout=True,
            max_connections=20)

    def get_cached_result(self, key):
        """获取缓存结果"""
        try:
            cached_data = self.client.get(key)
            return json.loads(cached_data) if cached_data else None
        except Exception as e:
            print(f"缓存读取失败:{str(e)}")

    def set_cached_result(self, key, data, ttl=None):
        """设置缓存结果"""
        try:
            serialized_data = json.dumps(data)

            if ttl is not None:
                self.client.setex(key, timedelta(minutes=int(ttl)), serialized_data)
            else:
                self.client.set(key, serialized_data)

        except Exception as e:
            print(f"缓存写入失败:{str(e)}")

    def invalidate_cache(self, pattern):
        """批量清除缓存"""
        try:
            keys_to_delete = []

            for key in self.client.scan_iter(match=f"{pattern}:*"):
                keys_to_delete.append(key)

            if keys_to_delete:
                return self.client.delete(*keys_to_delete)

        except Exception as e:
             print(f"缓存清除失败:{str(e)}")
             return False

Nginx代理配置优化示例

代码片段

http {
    upstream model_api {
       server localhost:8000 weight=3 max_fails=3 fail_timeout=30s;
       server localhost:8001 backup;
       keepalive_timeout  65;
       keepalive_request  100000;
     }

     server {
         listen        443 ssl http2;
         server_name   api.yourdomain.com;

         ssl_certificate      /etc/ssl/certs/api.yourdomain.com.crt;
         ssl_certificate_key   /etc/ssl/private/api.yourdomain.com.key;

         ssl_session_cache     shared:MYSESSIDCACHE:10m;
         ssl_session_timeout   10m;         

         location ~ ^/(v1/predict|v1/train) {
             proxy_pass http://model_api;             proxy_set_header Host $host;             proxy_set_header X-Real-Ip $remote_addr;             proxy_set_header X-Scheme $scheme;             proxy_set_header X-NginX-Proxy true;             proxy_set_header Connection "";             proxy_buffering off;             proxy_request_buffering off;             client_max_body_size    50M;              proxy_read_timeout     300s;              proxy_send_timeout     300s;              send_timeout          300s;              keepalive_timeout     300s;          }          
          location ~ ^/(status|metrics) {              stub_status on;              access_log off;              allow internal_ip_range ;               deny all ;          }      
      }      
}

Docker部署方案（可选）

docker-compose.yml示例：

代码片段

version:'3'
services:

 database:
 image :mysql :latest

 environment :
 MYSQL_ROOT_PASSWORD :${DB_ROOT_PASS}
 MYSQL_DATABASE :${DB_NAME}
 MYSQL_USER :${DB_USER}
 MYSQL_PASSWORD :${DB_PASS}
 TZ :Asia/Shanghai

 volumes :
 type :bind source ./data/db target:/var/lib/myql read_only false

 ports :
 target :3306 published ${DB_PORT} protocol tcp mode host

 networks :
 custom-network :
 ipv4_address ${DB_NETWORK_ADDR}

 command :
 mysqld character-set-server utf mb collation server utf mb unicode ci performance schema on innodb buffer pool size ${INNODB_BUFFER_SIZE}G innodb log file size ${INNODB_LOG_SIZE}M wait timeout ${WAIT_TIMEOUT} interactive timeout ${INTERACTIVE_TIMEOUT}

 deploy resources limits cpus ${CPU_LIMIT} memory ${MEM_LIMIT} reservations memory ${MEM_RESERVATION}

 model service build context ./model service dockerfile Dockerfile.model depends on database environment MODEL_TYPE ${MODEL_TYPE} MODEL_PATH ${MODEL_PATH} DB_CONNECTION jdbc //database/${DB_NAME}

 networks custom network driver bridge ipam config subnet ${NETWORK_SUBNET} gateway ${NETWORK_GATEWAY}

 volumes model data type volume source model vol target /opt/app/data read only true

networks custom network external name my custom network name true

Kubernetes部署方案（可选）

deployment.yaml示例：

“`yaml
apiVersion apps/v kind Deployment metadata name model service deployment labels app model service spec replicas selector match labels app model service template metadata labels app model service spec containers name main container image your registry/model service latest image pull policy Always ports container port env name DB HOST value From secret keyRef name db credentials key host name DB PORT value From secret keyRef name db credentials key port name DB USER value From secret keyRef name db credentials key username name DB PASS value From secret keyRef name db credentials key password resources requests cpu memory limits cpu memory volume mounts mount path read only true init containers name init wait for db image busybox command sh c until nc zv timeout then echo continue fi volume empty dir medium Memory size limit name shared memory volumes empty dir medium Memory size limit termination grace period seconds node selector kubernetes io/hostname worker node tolerations effect NoSchedule key dedicated operator Equal value model worker affinity pod anti affinity preferred during scheduling ignored during execution weight pod affinity term label selector match expressions key app operator In values topology key kubernetes io/hostname strategy rolling update max unavailable max surge waitFor readiness probe httpGet path port initial delay seconds period seconds timeout seconds success threshold failure threshold livenessProbe exec command cat tmp healthy initial delay seconds period seconds timeout seconds success threshold failure threshold restartPolicy Always service accountName default automountServiceAccountToken false securityContext runAsNonRoot true runAsUser fsGroup supplementalGroups readOnlyRootFilesystem true capabilities drop ALL allow NET BIND SERVICE seLinuxOptions level s disabled procMount Default priorityClassName system cluster critical preemptionPolicy Never overhead cpu memory scheduling gates pod scheduling readiness terminationGracePeriodSeconds dnsConfig options name ndots value searches ns svc cluster local options ndots single request timeout options attempts tolerations effect NoExecute key node kubernetes io/not ready operator Exists effect NoExecute key node kubernetes io/unreachable operator Exists effect NoSchedule key node kubernetes io/disk pressure operator Exists topology spread constraints maxSkew whenUnsatisfiable DoNotSchedule labelSelector matchLabels app model service topologyKey topology kubernetes io zone nodeAffinity requiredDuringSchedulingIgnoredDuringExecution nodeSelectorTerms matchExpressions key topology kubernetes io zone operator In values preferredDuringSchedulingIgnoredDuringExecution weight preference matchExpressions operator In values podDisruptionBudget minAvailable maxUnavailable selector matchLabels app model service updateStrategy type RollingUpdate rollingUpdate partition paused false progressDeadlineSeconds revisionHistoryLimit minReadySeconds progressDeadlineSeconds paused false progressDeadlineSeconds revisionHistoryLimit minReadySeconds progressDeadlineSeconds paused false progressDeadlineSeconds revisionHistoryLimit minReadySeconds progressDeadlineSeconds paused false progressDeadlineSeconds revisionHistoryLimit minReadySeconds progressDeadlineSeconds paused false progressDeadlineSeconds revisionHistoryLimit minReadySeconds progressDeadlineSeconds paused false progressDeadlineSeconds revisionHistoryLimit minReadySeconds progressDeadlineSeconds paused false progressDeadlineSeconds revisionHistoryLimit minReadySeconds progressDeadline Seconds paused false progress Deadline Seconds revision History Limit min Ready Seconds horizontal Pod Autoscaler scale Target Ref api Version apps/v kind Deployment name model service deployment min Replicas max Replicas metrics resource name cpu target average Utilization resource name memory target average Value Mi priority Class Name system cluster critical preemption Policy Never overhead cpu memory scheduling Gates pod scheduling readiness termination Grace Period Seconds dns Config options name ndots value searches ns svc cluster local options ndots single request timeout options attempts tolerations effect NoExecute key node kubernetes io/not ready operator Exists effect NoExecute