ARTICLE DETAIL

资讯详情

深耕网站建设与运营推广的一线实战洞察。

首页/资讯中心/资讯详情

Linux下MySQL 8.0安装配置与SQL实战指南

发布时间：2026/8/11 18:28:07 拓冰企业网站定制
Linux下MySQL 8.0安装配置与SQL实战指南

1. Linux环境下MySQL的安装与配置实战

作为Linux系统管理员和数据库开发者的必备技能，MySQL在各类生产环境中占据着核心地位。今天我将分享在Linux系统上从零开始部署MySQL的全过程，以及SQL语句的实战应用技巧。这套方案已经在Ubuntu 20.04/22.04和CentOS 7/8系统上经过反复验证，特别适合需要快速搭建开发环境的新手。

重要提示：生产环境建议使用MySQL 8.0及以上版本以获得更好的安全性和性能，本文示例以MySQL 8.0.33为例

1.1 安装前的系统准备

首先需要更新系统软件包并安装必要的依赖项。不同Linux发行版的命令略有差异：

# Ubuntu/Debian系 sudo apt update && sudo apt upgrade -y sudo apt install -y gnupg2 wget # RHEL/CentOS系 sudo yum update -y sudo yum install -y epel-release sudo yum install -y wget

对于国内用户，建议配置阿里云或清华大学的镜像源加速下载：

# Ubuntu更换阿里源 sudo sed -i 's|http://.*archive.ubuntu.com|https://mirrors.aliyun.com|g' /etc/apt/sources.list # CentOS更换清华源 sudo sed -e 's|^mirrorlist=|#mirrorlist=|g' \ -e 's|^#baseurl=http://mirror.centos.org|baseurl=https://mirrors.tuna.tsinghua.edu.cn|g' \ -i.bak /etc/yum.repos.d/CentOS-*.repo

1.2 MySQL官方仓库配置

MySQL官方提供了经过优化的软件仓库，比系统默认仓库版本更新：

# Ubuntu/Debian wget https://dev.mysql.com/get/mysql-apt-config_0.8.24-1_all.deb sudo dpkg -i mysql-apt-config_0.8.24-1_all.deb # RHEL/CentOS sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-6.noarch.rpm

安装过程中会提示选择MySQL版本，使用方向键选择MySQL 8.0后按Tab键切换到OK确认。

1.3 实际安装过程

执行安装命令并观察输出：

# Ubuntu/Debian sudo apt update sudo apt install -y mysql-server # RHEL/CentOS sudo yum install -y mysql-community-server

安装完成后检查服务状态：

sudo systemctl status mysqld

正常应该显示"active (running)"。如果没有自动启动，需要手动启动服务：

sudo systemctl start mysqld sudo systemctl enable mysqld

2. MySQL安全初始化与基础配置

2.1 运行安全加固脚本

MySQL首次安装后必须运行安全脚本：

sudo mysql_secure_installation

脚本会依次提示：

  1. 设置验证密码强度级别（建议选2）
  2. 输入root密码（需满足复杂度要求）
  3. 移除匿名用户（选Y）
  4. 禁止root远程登录（选Y）
  5. 移除测试数据库（选Y）
  6. 立即重载权限表（选Y）

2.2 配置文件优化

编辑MySQL主配置文件（位置因系统而异）：

sudo vim /etc/mysql/my.cnf # Ubuntu sudo vim /etc/my.cnf # CentOS

基础优化配置示例：

[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # 字符集设置 character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci # 连接设置 max_connections=1000 wait_timeout=300 interactive_timeout=300 # 内存配置 innodb_buffer_pool_size=1G # 建议为物理内存的50-70% key_buffer_size=256M # 日志配置 slow_query_log=1 slow_query_log_file=/var/log/mysql/mysql-slow.log long_query_time=2 log-error=/var/log/mysql/error.log

修改后需要重启服务生效：

sudo systemctl restart mysqld

3. SQL语句核心操作精要

3.1 数据库基础操作

登录MySQL命令行（使用刚设置的root密码）：

mysql -u root -p

创建新数据库并设置字符集：

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

查看所有数据库：

SHOW DATABASES;

切换当前数据库：

USE mydb;

3.2 表操作实战

创建用户表示例：

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password CHAR(60) NOT NULL, -- 存储bcrypt哈希值 email VARCHAR(100) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_email (email) ) ENGINE=InnoDB;

查看表结构：

DESCRIBE users;

修改表结构（添加字段）：

ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

3.3 CRUD操作详解

插入数据（多种方式）：

-- 单条插入 INSERT INTO users (username, password, email) VALUES ('john_doe', '$2a$10$xJw...', 'john@example.com'); -- 批量插入 INSERT INTO users (username, password, email) VALUES ('alice', '$2a$10$yHp...', 'alice@example.com'), ('bob', '$2a$10$zQt...', 'bob@example.com');

查询数据（基础+高级）：

-- 基础查询 SELECT * FROM users WHERE id > 10; -- 分页查询 SELECT id, username FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20; -- 聚合查询 SELECT COUNT(*) as total_users, MAX(created_at) as latest_user FROM users; -- 多表连接 SELECT u.username, o.order_id, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'completed';

更新数据：

UPDATE users SET email = 'new_email@example.com', updated_at = NOW() WHERE id = 5;

删除数据：

DELETE FROM users WHERE id = 100;

4. MySQL高级特性与应用

4.1 存储过程与函数

创建计算用户年龄的存储过程：

DELIMITER // CREATE PROCEDURE GetUserAge(IN user_id INT, OUT age INT) BEGIN DECLARE birth_date DATE; SELECT date_of_birth INTO birth_date FROM users WHERE id = user_id; SET age = TIMESTAMPDIFF(YEAR, birth_date, CURDATE()); END // DELIMITER ; -- 调用示例 CALL GetUserAge(5, @age); SELECT @age;

4.2 触发器应用

创建审计日志触发器：

CREATE TRIGGER user_update_audit AFTER UPDATE ON users FOR EACH ROW BEGIN INSERT INTO audit_logs (table_name, record_id, action, changed_fields, changed_by) VALUES ('users', NEW.id, 'update', CONCAT('username:', OLD.username, '→', NEW.username, ',email:', OLD.email, '→', NEW.email), CURRENT_USER()); END;

4.3 事务处理示例

银行转账事务：

START TRANSACTION; -- 检查账户余额 SELECT balance INTO @current_balance FROM accounts WHERE user_id = 1 FOR UPDATE; -- 转账金额 SET @transfer_amount = 500; IF @current_balance >= @transfer_amount THEN -- 扣款 UPDATE accounts SET balance = balance - @transfer_amount WHERE user_id = 1; -- 存款 UPDATE accounts SET balance = balance + @transfer_amount WHERE user_id = 2; -- 记录交易 INSERT INTO transactions (from_user, to_user, amount, type) VALUES (1, 2, @transfer_amount, 'transfer'); COMMIT; SELECT 'Transfer successful' AS result; ELSE ROLLBACK; SELECT 'Insufficient balance' AS result; END IF;

5. 性能优化与问题排查

5.1 索引优化策略

查看表索引：

SHOW INDEX FROM users;

添加复合索引：

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

使用EXPLAIN分析查询：

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'shipped' ORDER BY created_at DESC;

5.2 慢查询优化

检查慢查询日志配置：

SHOW VARIABLES LIKE 'slow_query%'; SHOW VARIABLES LIKE 'long_query_time';

临时设置慢查询阈值（秒）：

SET GLOBAL long_query_time = 1;

分析慢查询日志：

sudo mysqldumpslow -s t /var/log/mysql/mysql-slow.log

5.3 常见错误处理

连接数过多：

SHOW STATUS LIKE 'Threads_connected'; SHOW VARIABLES LIKE 'max_connections';

死锁问题排查：

SHOW ENGINE INNODB STATUS;

表损坏修复：

CHECK TABLE users; REPAIR TABLE users;

6. 备份与恢复策略

6.1 逻辑备份

使用mysqldump全量备份：

mysqldump -u root -p --all-databases --single-transaction > full_backup.sql

备份单个数据库：

mysqldump -u root -p mydb --routines --triggers > mydb_backup.sql

6.2 物理备份

使用Percona XtraBackup热备份：

sudo apt install percona-xtrabackup-80 # Ubuntu sudo yum install percona-xtrabackup-80 # CentOS # 全量备份 sudo innobackupex --user=root --password=your_password /backup/mysql/

6.3 定时备份方案

创建每日备份脚本：

#!/bin/bash BACKUP_DIR="/var/backups/mysql" DATE=$(date +%Y%m%d) mkdir -p $BACKUP_DIR/$DATE mysqldump -u root -p'your_password' --all-databases --single-transaction | gzip > $BACKUP_DIR/$DATE/full_backup.sql.gz # 保留最近7天备份 find $BACKUP_DIR -type d -mtime +7 -exec rm -rf {} \;

添加到cron定时任务：

0 2 * * * /usr/local/bin/mysql_backup.sh

7. 安全加固建议

7.1 权限最小化原则

创建专用应用用户：

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!'; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'localhost'; FLUSH PRIVILEGES;

7.2 密码策略

检查密码策略：

SHOW VARIABLES LIKE 'validate_password%';

修改密码策略（MySQL 8.0+）：

SET GLOBAL validate_password.policy = STRONG; SET GLOBAL validate_password.length = 12;

7.3 网络层防护

配置MySQL只监听内网：

[mysqld] bind-address = 10.0.0.100 # 改为服务器内网IP

使用防火墙限制访问：

sudo ufw allow from 10.0.0.0/24 to any port 3306

8. 监控与维护

8.1 关键指标监控

查看运行状态：

SHOW STATUS LIKE 'Qcache%'; -- 查询缓存 SHOW STATUS LIKE 'Innodb%'; -- InnoDB状态

性能概览：

SHOW ENGINE INNODB STATUS\G

8.2 定期维护任务

优化表：

OPTIMIZE TABLE large_table;

更新统计信息：

ANALYZE TABLE users;

8.3 日志轮转配置

配置logrotate管理MySQL日志：

sudo vim /etc/logrotate.d/mysql

添加内容：

/var/log/mysql/error.log { daily missingok rotate 30 compress delaycompress notifempty create 640 mysql adm sharedscripts postrotate test -x /usr/bin/mysqladmin || exit 0 MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf" $MYADMIN ping &>/dev/null && $MYADMIN flush-logs endscript }

9. 开发实用技巧

9.1 常用函数集锦

日期处理：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); SELECT DATEDIFF('2023-12-31', NOW());

字符串处理：

SELECT CONCAT(first_name, ' ', last_name) AS full_name, SUBSTRING_INDEX(email, '@', -1) AS domain FROM users;

条件判断：

SELECT id, CASE WHEN age < 18 THEN 'Minor' WHEN age BETWEEN 18 AND 65 THEN 'Adult' ELSE 'Senior' END AS age_group FROM customers;

9.2 JSON类型操作

创建JSON字段表：

CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), attributes JSON, price DECIMAL(10,2) );

JSON操作示例：

-- 插入JSON数据 INSERT INTO products (name, attributes, price) VALUES ('Smartphone', '{"color": "black", "storage": "128GB", "os": "Android"}', 599.99); -- 查询JSON字段 SELECT name, attributes->>"$.color" AS color FROM products WHERE attributes->>"$.storage" = '128GB'; -- 更新JSON字段 UPDATE products SET attributes = JSON_SET(attributes, '$.color', 'blue') WHERE id = 1;

9.3 窗口函数应用

排名分析：

SELECT product_id, sales_date, amount, RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) as sales_rank, SUM(amount) OVER (PARTITION BY product_id) as total_product_sales FROM sales WHERE YEAR(sales_date) = 2023;

移动平均计算：

SELECT date, temperature, AVG(temperature) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM weather_data;

10. 生产环境注意事项

10.1 主从复制配置

主库配置：

[mysqld] server-id = 1 log_bin = mysql-bin binlog_format = ROW binlog_row_image = FULL sync_binlog = 1

从库配置：

[mysqld] server-id = 2 relay_log = mysql-relay-bin read_only = ON

主库创建复制用户：

CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'ReplPassword123!'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

从库设置复制：

CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl', MASTER_PASSWORD='ReplPassword123!', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154; START SLAVE;

10.2 连接池配置

常见Java连接池配置示例（HikariCP）：

# application.properties spring.datasource.hikari.connection-timeout=30000 spring.datasource.hikari.maximum-pool-size=20 spring.datasource.hikari.idle-timeout=600000 spring.datasource.hikari.max-lifetime=1800000 spring.datasource.hikari.leak-detection-threshold=5000

10.3 版本升级策略

升级前检查：

mysqlcheck -u root -p --all-databases --check-upgrade

升级步骤：

  1. 完整备份所有数据库
  2. 停止MySQL服务
  3. 安装新版本软件包
  4. 运行mysql_upgrade工具
  5. 启动新版本服务
  6. 验证数据完整性

10.4 灾难恢复演练

恢复测试流程：

  1. 准备隔离的测试环境
  2. 还原最近的全量备份
  3. 应用增量binlog恢复到指定时间点
  4. 验证数据一致性和应用功能
  5. 记录恢复耗时和问题点
  6. 优化恢复方案和文档

定期执行恢复演练（建议每季度至少一次），确保团队熟悉恢复流程。

网站建设 企业官网 运营推广
返回列表