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|^#baseurlhttp://mirror.centos.org|baseurlhttps://mirrors.tuna.tsinghua.edu.cn|g \ -i.bak /etc/yum.repos.d/CentOS-*.repo1.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 mysqld2. MySQL安全初始化与基础配置2.1 运行安全加固脚本MySQL首次安装后必须运行安全脚本sudo mysql_secure_installation脚本会依次提示设置验证密码强度级别建议选2输入root密码需满足复杂度要求移除匿名用户选Y禁止root远程登录选Y移除测试数据库选Y立即重载权限表选Y2.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-serverutf8mb4 collation-serverutf8mb4_unicode_ci # 连接设置 max_connections1000 wait_timeout300 interactive_timeout300 # 内存配置 innodb_buffer_pool_size1G # 建议为物理内存的50-70% key_buffer_size256M # 日志配置 slow_query_log1 slow_query_log_file/var/log/mysql/mysql-slow.log long_query_time2 log-error/var/log/mysql/error.log修改后需要重启服务生效sudo systemctl restart mysqld3. 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) ) ENGINEInnoDB;查看表结构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..., johnexample.com); -- 批量插入 INSERT INTO users (username, password, email) VALUES (alice, $2a$10$yHp..., aliceexample.com), (bob, $2a$10$zQt..., bobexample.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_emailexample.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.log5.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.sql6.2 物理备份使用Percona XtraBackup热备份sudo apt install percona-xtrabackup-80 # Ubuntu sudo yum install percona-xtrabackup-80 # CentOS # 全量备份 sudo innobackupex --userroot --passwordyour_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 -pyour_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.sh7. 安全加固建议7.1 权限最小化原则创建专用应用用户CREATE USER app_userlocalhost IDENTIFIED BY StrongPassword123!; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_userlocalhost; FLUSH PRIVILEGES;7.2 密码策略检查密码策略SHOW VARIABLES LIKE validate_password%;修改密码策略MySQL 8.0SET 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 33068. 监控与维护8.1 关键指标监控查看运行状态SHOW STATUS LIKE Qcache%; -- 查询缓存 SHOW STATUS LIKE Innodb%; -- InnoDB状态性能概览SHOW ENGINE INNODB STATUS\G8.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_HOSTmaster_ip, MASTER_USERrepl, MASTER_PASSWORDReplPassword123!, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE;10.2 连接池配置常见Java连接池配置示例HikariCP# application.properties spring.datasource.hikari.connection-timeout30000 spring.datasource.hikari.maximum-pool-size20 spring.datasource.hikari.idle-timeout600000 spring.datasource.hikari.max-lifetime1800000 spring.datasource.hikari.leak-detection-threshold500010.3 版本升级策略升级前检查mysqlcheck -u root -p --all-databases --check-upgrade升级步骤完整备份所有数据库停止MySQL服务安装新版本软件包运行mysql_upgrade工具启动新版本服务验证数据完整性10.4 灾难恢复演练恢复测试流程准备隔离的测试环境还原最近的全量备份应用增量binlog恢复到指定时间点验证数据一致性和应用功能记录恢复耗时和问题点优化恢复方案和文档定期执行恢复演练建议每季度至少一次确保团队熟悉恢复流程。
