一、Linux安装MySQL规范1.1 MySQL安装方式推荐方式二进制安装tar 包解压即用其他方式源码编译、Yum、RPM不展开1.2 MySQL 运行用户使用虚拟用户 mysql:mysql仅用于进程运行禁止登录、不创建家目录创建命令useradd -s /sbin/nologin -M mysql1.3 MySQL目录规范用途 路径示例下载目录 /server/tools系统目录 /opt/mysql/mysql-8.0.45软链接 ln -s /opt/mysql/mysql-8.0.45 /usr/local/mysql数据目录 /data/mysql/mysql3306/{data,logs}配置文件 /data/mysql/mysql3306/my3306.cnf1.4 MySQL安装依赖环境检查GNU libc 版本 必须 ≥ 2.17否则启动报错查看命令ldd --version示例输出ldd (GNU libc) 2.17Copyright © 2012 Free Software Foundation, Inc.This is free software; see the source for copying conditions. There is NOwarranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.由 Roland McGrath 和 Ulrich Drepper 编写。二、Linux安装 MySQL 8.0完整步骤下载MySQL8.0安装包不要选择过低的版本后面的版本会修复已知的bug过低的版本bug较多谨慎选择通过网盘分享的文件mysql-8.0.45-linux-glibc2.17-x86_64链接: https://pan.baidu.com/s/1DBY3D1h2Up4z-jMemxNSuA?pwd5xib 提取码: 5xib–来自百度网盘的分享2.1 准备工作1. 创建虚拟用户useradd -s /sbin/nologin -M mysqlLinux安装MySQL教程2. 创建所需目录mkdir -p /server/toolsmkdir -p /opt/mysqlmkdir -p /data/mysql/mysql3306/{data,logs}Linux安装MySQL教程3. 进入下载目录cd /server/tools4. 上传二进制包若没有 lrzsz先 yum install -y lrzszrzLinux安装MySQL教程 课堂小技巧快速备份cp a.txt{,.bak}删除文件前先用 mv 移到 /tmp确认无用后再删除避免误删。2.2 解压与移动解压包名以实际为准tar xf mysql-8.0.45-linux-glibc2.17-x86_64.tar.xzLinux安装MySQL教程移动到规范目录mv mysql-8.0.45-linux-glibc2.17-x86_64 /opt/mysql/mysql-8.0.45Linux安装MySQL教程创建软链接ln -s /opt/mysql/mysql-8.0.45 /usr/local/mysqlLinux安装MySQL教程2.3 移除系统自带 MariaDB避免冲突rpm -e --nodeps mariadb-libsLinux安装MySQL教程2.4 编辑配置文件vim /data/mysql/mysql3306/my3306.cnf配置内容参考[mysqld]usermysqlbasedir/usr/local/mysqldatadir/data/mysql/mysql3306/datasocket/data/mysql/mysql3306/mysql.sockserver_id1port3306log_error/data/mysql/mysql3306/logs/error.loglog_bin/data/mysql/mysql3306/logs/mysql-binbinlog_formatrowgtid_modeonenforce_gtid_consistencytruelog_slave_updates1max_connections1024wait_timeout60sort_buffer_size2Mmax_allowed_packet32Mjoin_buffer_size2Minnodb_buffer_pool_size128Minnodb_flush_log_at_trx_commit1innodb_log_buffer_size32Minnodb_log_file_size128Minnodb_log_files_in_group2binlog_cache_size2Mmax_binlog_cache_size8Mmax_binlog_size512Mexpire_logs_days7slow_query_logonslow_query_log_file/data/mysql/mysql3306/logs/slow.loglong_query_time0.5log_queries_not_using_indexes1Linux安装MySQL教程2.5 安装依赖包yum install libaio-devel numactl -yLinux安装MySQL教程2.6 更改目录权限chown -R mysql:mysql /data/*Linux安装MySQL教程2.7 初始化数据库空密码/usr/local/mysql/bin/mysqld–defaults-file/data/mysql/mysql3306/my3306.cnf–initialize-insecure–usermysql–basedir/usr/local/mysql–datadir/data/mysql/mysql3306/data✅ --initialize-insecure 表示 root 密码为空若用 --initialize 则生成随机密码并记录在 error.log。Linux安装MySQL教程2.8 配置环境变量echo ‘PATH“/usr/local/mysql/bin:$PATH”’ /etc/profilesource /etc/profileLinux安装MySQL教程2.9 启动 MySQLmysqld --defaults-file/data/mysql/mysql3306/my3306.cnf 2.10 验证启动netstat -lntup | grep mysqlLinux安装MySQL教程三、连接 MySQL 的方式本地连接使用 socket无需输入密码回车即可连接mysql -uroot -p -S /data/mysql/mysql3306/mysql.sockLinux安装MySQL教程远程连接TCP/IP默认没有远程连接的用户需要单独创建mysql create user root‘%’ identified by ‘123456’;Query OK, 0 rows affected (0.04 sec)mysql grant all on.to root‘%’;Query OK, 0 rows affected (0.01 sec)mysql mysql -uroot -p -P3306 -h192.168.80.8015四、常用sql语句4.1. 数据库操作– 创建数据库指定字符集CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;– 查看所有数据库SHOW DATABASES;– 切换/使用数据库USE mydb;– 查看当前所在数据库SELECT DATABASE();– 删除数据库谨慎操作DROP DATABASE IF EXISTS mydb;4.2. 建表语句含各类约束– 标准建表主键、自增、默认值、注释、唯一约束、外键CREATE TABLE IF NOT EXISTSusers(idINT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘用户ID主键’,usernameVARCHAR(50) NOT NULL COMMENT ‘用户名’,emailVARCHAR(100) NOT NULL COMMENT ‘邮箱’,phoneVARCHAR(20) DEFAULT NULL COMMENT ‘手机号’,password_hashVARCHAR(255) NOT NULL COMMENT ‘密码哈希’,ageTINYINT(3) UNSIGNED DEFAULT 0 COMMENT ‘年龄’,salaryDECIMAL(10,2) DEFAULT 0.00 COMMENT ‘月薪’,statusTINYINT(1) NOT NULL DEFAULT 1 COMMENT ‘状态1启用 0禁用’,created_atDATETIME DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,updated_atDATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,PRIMARY KEY (id),UNIQUE KEYuk_username(username), – 唯一索引UNIQUE KEYuk_email(email),KEYidx_status_created(status,created_at) – 联合普通索引) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT‘用户信息表’;– 创建关联表带外键CREATE TABLEorders(idINT(11) UNSIGNED NOT NULL AUTO_INCREMENT,user_idINT(11) UNSIGNED NOT NULL COMMENT ‘关联用户ID’,amountDECIMAL(10,2) NOT NULL,order_timeDATETIME DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (id),KEYidx_user_id(user_id),CONSTRAINTfk_orders_userFOREIGN KEY (user_id) REFERENCESusers(id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINEInnoDB DEFAULT CHARSETutf8mb4;4.3. 表结构修改ALTER TABLE– 添加列ALTER TABLE users ADD COLUMNaddressVARCHAR(200) DEFAULT NULL COMMENT ‘地址’;– 修改列类型/属性ALTER TABLE users MODIFY COLUMNphoneVARCHAR(30) NOT NULL COMMENT ‘手机号’;– 修改列名同时改类型ALTER TABLE users CHANGEphonemobileVARCHAR(20) NOT NULL COMMENT ‘手机号’;– 删除列ALTER TABLE users DROP COLUMNaddress;– 添加索引ALTER TABLE users ADD INDEX idx_age (age);ALTER TABLE users ADD UNIQUE INDEX uk_phone (mobile);– 删除索引ALTER TABLE users DROP INDEX idx_age;– 重命名表RENAME TABLE users TO user_info;– 或ALTER TABLE user_info RENAME TO users;– 查看表结构DESC users;– 或查看建表语句SHOW CREATE TABLE users;4.4. 清空表两种方式区别大– 方式1删除所有数据保留表结构自增计数器归零可回滚事务内DELETE FROM users;– 方式2清空表自增计数器归零速度极快不可回滚DDL操作TRUNCATE TABLE users;4.5. INSERT插入– 标准插入指定列INSERT INTO users (username, email, password_hash, age, salary)VALUES (‘zhangsan’, ‘zhangsanexample.com’, ‘hash123’, 28, 8500.00);– 批量插入推荐效率高INSERT INTO users (username, email, password_hash, age) VALUES(‘lisi’, ‘lisiexample.com’, ‘hash456’, 32),(‘wangwu’, ‘wangwuexample.com’, ‘hash789’, 25);– 插入时遇到重复键则更新ON DUPLICATE KEY UPDATEINSERT INTO users (username, email, password_hash, age)VALUES (‘zhangsan’, ‘newemailexample.com’, ‘newhash’, 29)ON DUPLICATE KEY UPDATEemail VALUES(email),age VALUES(age);– 插入时忽略重复键不报错INSERT IGNORE INTO users (username, email, password_hash)VALUES (‘zhangsan’, ‘dupexample.com’, ‘xxx’);4.6. UPDATE更新– 更新单条务必带WHERE否则全表更新UPDATE users SET age 30, salary 9000.00 WHERE username ‘zhangsan’;– 条件更新CASE WHENUPDATE usersSET salary CASEWHEN age 25 THEN salary * 1.2WHEN age BETWEEN 25 AND 35 THEN salary * 1.1ELSE salary * 1.05END;– 联表更新用JOINUPDATE users uJOIN orders o ON u.id o.user_idSET u.status 0WHERE o.amount 10.00;4.7. DELETE删除– 删除指定条件数据DELETE FROM users WHERE age 60 AND status 0;– 联表删除删除无订单的用户DELETE u FROM users uLEFT JOIN orders o ON u.id o.user_idWHERE o.id IS NULL;– 安全删除先查询确认再删除善用事务START TRANSACTION;SELECT * FROM users WHERE age 60; – 先看看要删哪些DELETE FROM users WHERE age 60;– 确认无误后 COMMIT; 有问题则 ROLLBACK;4.8. 基础查询– 查询所有列生产环境不推荐 *SELECT * FROM users;– 查询指定列 别名SELECT id, username AS 用户名, email AS 邮箱 FROM users;– 去重查询SELECT DISTINCT status FROM users;– 条件查询WHERESELECT * FROM usersWHERE age 18 AND age 30 AND status 1;– IN / NOT INSELECT * FROM users WHERE id IN (1, 3, 5, 7);SELECT * FROM users WHERE status NOT IN (0, 2);– BETWEENSELECT * FROM users WHERE age BETWEEN 20 AND 35;– LIKE 模糊查询%任意字符_单个字符SELECT * FROM users WHERE username LIKE ‘张%’; – 以张开头SELECT * FROM users WHERE email LIKE ‘%qq.com’; – QQ邮箱– NULL 值判断不能用 NULLSELECT * FROM users WHERE phone IS NULL;SELECT * FROM users WHERE phone IS NOT NULL;4.9. 排序、分页– ORDER BY升序ASC默认降序DESCSELECT * FROM users WHERE status 1ORDER BY age DESC, created_at ASC;– 分页查询LIMIT offset, countSELECT * FROM users ORDER BY id DESC LIMIT 0, 20; – 第1页每页20条SELECT * FROM users ORDER BY id DESC LIMIT 20, 20; – 第2页– 优化大偏移量分页记录上一页最后一条IDSELECT * FROM users WHERE id 1000 ORDER BY id LIMIT 20;4.10. 聚合函数 GROUP BY HAVING– 常用聚合COUNT, SUM, AVG, MAX, MINSELECTCOUNT(*) AS total_users,AVG(age) AS avg_age,MAX(salary) AS max_salary,MIN(salary) AS min_salary,SUM(salary) AS total_salaryFROM users WHERE status 1;– GROUP BY 分组统计SELECTstatus,COUNT(*) AS count,AVG(age) AS avg_ageFROM usersGROUP BY status;– HAVING 对分组结果过滤区别于WHERESELECTstatus,COUNT(*) AS cntFROM usersGROUP BY statusHAVING cnt 10; – 只返回人数 10 的状态组4.11. 多表连接查询JOIN– INNER JOIN取交集SELECT u.username, o.amount, o.order_timeFROM users uINNER JOIN orders o ON u.id o.user_idWHERE o.amount 100;– LEFT JOIN左表全保留右表无匹配则为NULLSELECT u.username, COUNT(o.id) AS order_countFROM users uLEFT JOIN orders o ON u.id o.user_idGROUP BY u.id, u.username;– 自连接查询同一张表的上下级关系SELECTe1.username AS employee,e2.username AS managerFROM users e1LEFT JOIN users e2 ON e1.manager_id e2.id;– 子查询WHERE中SELECT * FROM usersWHERE id IN (SELECT DISTINCT user_id FROM orders WHERE amount 500);– 子查询FROM中即派生表SELECT t.username, t.avg_amountFROM (SELECT u.id, u.username, AVG(o.amount) AS avg_amountFROM users uJOIN orders o ON u.id o.user_idGROUP BY u.id, u.username) tWHERE t.avg_amount 1000;4.12. 高级查询技巧– CASE WHEN 条件判断类似 if-elseSELECTusername,age,CASEWHEN age 18 THEN ‘未成年’WHEN age BETWEEN 18 AND 60 THEN ‘成年’ELSE ‘老年’END AS age_groupFROM users;– 窗口函数MySQL 8.0排名、累计等SELECTusername,salary,RANK() OVER (ORDER BY salary DESC) AS rank_salary,DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,ROW_NUMBER() OVER (PARTITION BY status ORDER BY salary DESC) AS rnFROM users;– 日期函数常用SELECTNOW() AS 当前时间,CURDATE() AS 当前日期,DATE_ADD(NOW(), INTERVAL 7 DAY) AS 一周后,DATEDIFF(‘2026-07-06’, ‘2026-07-01’) AS 相差天数,DATE_FORMAT(created_at, ‘%Y年%m月%d日 %H:%i:%s’) AS 格式化时间FROM users LIMIT 1;4.13. 常用运维管理命令– 查看所有正在运行的连接/进程SHOW PROCESSLIST;– 查完整版包含SQL语句全文SHOW FULL PROCESSLIST;– 杀掉某个连接线程IDKILL 12345;– 查看当前数据库所有表SHOW TABLES;– 查看表状态大小、行数等SHOW TABLE STATUS LIKE ‘users’\G– 查看数据文件大小MBSELECTtable_schema AS ‘数据库’,table_name AS ‘表名’,ROUND(((data_length index_length) / 1024 / 1024), 2) AS ‘大小(MB)’FROM information_schema.TABLESWHERE table_schema ‘mydb’ORDER BY (data_length index_length) DESC;– 查看当前数据库连接数相关SHOW VARIABLES LIKE ‘max_connections’; – 最大连接数设置SHOW STATUS LIKE ‘Threads_connected’; – 当前连接数SHOW STATUS LIKE ‘Max_used_connections’; – 历史最高连接数– 查看SQL执行状态实时监控SHOW ENGINE INNODB STATUS\G – InnoDB引擎状态4.14. 实用组合场景示例– 场景1查询最近7天注册、且有过至少1笔订单的用户SELECT DISTINCT u.id, u.username, u.created_atFROM users uJOIN orders o ON u.id o.user_idWHERE u.created_at DATE_SUB(NOW(), INTERVAL 7 DAY)AND o.order_time DATE_SUB(NOW(), INTERVAL 7 DAY);– 场景2统计每个用户的订单总额并按总额排序SELECTu.id,u.username,IFNULL(SUM(o.amount), 0) AS total_spent,COUNT(o.id) AS order_countFROM users uLEFT JOIN orders o ON u.id o.user_idGROUP BY u.id, u.usernameORDER BY total_spent DESC;– 场景3找出重复的邮箱SELECT email, COUNT(*) AS cntFROM usersGROUP BY emailHAVING cnt 1;– 场景4插入数据如果存在则忽略如果不存在则插入利用唯一键INSERT IGNORE INTO users (username, email, password_hash)VALUES (‘newuser’, ‘newexample.com’, ‘hashxxx’);五、常见启动失败案例与排错 案例1目录权限不足错误日志[ERROR] Could not open file ‘/data/mysql/mysql3306/logs/error.log’ for error logging: Permission denied原因/data/* 属主不是 mysql:mysql解决chown -R mysql:mysql /data/* 案例2配置文件参数错误错误日志[ERROR] unknown variable ‘basedi/usr/local/mysql’拼写错误原因参数名写错或版本不支持解决检查配置文件修正参数名如 basedir。 案例3未指定正确配置文件现象启动时未使用 --defaults-file或路径错误排查mysqld --verbose --help | grep defaults解决启动时务必带上 --defaults-file/data/mysql/mysql3306/my3306.cnf。 案例4端口被占用错误日志[ERROR] Do you already have another mysqld server running on port: 3306 ?原因多实例端口冲突解决修改配置文件中的 port或停止占用端口的进程。 案例5误删二进制日志文件错误日志[ERROR] Failed to open log (file ‘/data/mysql/mysql3306/logs/mysql-bin.000014’, errno 2)解决编辑 mysql-bin.index删除对应行记录。 案例6共享表空间大小不匹配错误日志[ERROR] InnoDB: The Auto-extending innodb_system data file ‘./ibdata1’ is of a different size …原因innodb_data_file_path 参数与现有 ibdata1 文件大小不一致解决若在初始化前修改参数为默认值≤12M若已初始化需重新初始化注意备份数据。六、启动失败通用排查方法确认 MySQL 版本、启动方式及配置文件路径查看错误日志默认 error.log 路径在配置中指定tail -100 /data/mysql/mysql3306/logs/error.log七、数据库用户安全策略7.1 初始化方式对比选项 root 密码 密码存储位置–initialize-insecure 空无密码 无需记录–initialize 随机生成 error.log7.2 修改 root 密码– 方法1ALTER USERALTER USER root‘localhost’ IDENTIFIED BY ‘123456’;– 方法2mysqladmin命令行mysqladmin -uroot -p123456 password ‘123’ -S /data/mysql/mysql3306/mysql.sock7.3 清理无用用户DELETE FROM mysql.user WHERE user ! ‘root’;FLUSH PRIVILEGES;7.4 创建管理用户示例– 创建用户CREATE USER ergou‘localhost’ IDENTIFIED BY ‘123456’;– 授予全部权限并允许转授权GRANT ALL PRIVILEGES ON.TO ergou‘localhost’ WITH GRANT OPTION;附录常用目录结构总览/opt/mysql/mysql-8.0.20/ # 软件主目录/usr/local/mysql - /opt/mysql/… # 软链接/data/mysql/mysql3306/├── data/ # 数据文件├── logs/ # 日志文件error, binlog, slow└── my3306.cnf # 配置文件