GF-MySQL

内容简介：本文主要介绍MySQL相关知识，包括安装、配置、优化等内容。风哥教程参考MySQL官方文档MySQL SQL Syntax、MySQL Server Administration。 01 更多视频教程www.fgedu.net.cn 02 学习交流加群风哥微信: itpux-com

Part01-基础概念与理论知识

1.1 无法连接到MySQL服务器

问题：无法连接到MySQL服务器，报错”Can’t connect to MySQL server on ‘localhost’ (10061)”

解决方案：

• 检查MySQL服务是否运行：systemctl status mysql（Linux）或在服务管理器中查看（Windows）
• 启动MySQL服务：systemctl start mysql（Linux）或在服务管理器中启动（Windows）
• 检查防火墙是否阻止了3306端口
• 检查my.cnf配置文件中的bind-address设置

1.2 连接被拒绝

问题：连接MySQL时出现”Access denied for user ‘root’@’localhost'”

解决方案：

• 检查用户名和密码是否正确
• 检查用户是否有从当前主机连接的权限
• 重置root密码：

  # 停止MySQL服务
  systemctl stop mysql
  
  # 以跳过授权表的方式启动
  mysqld_safe --skip-grant-tables &
  
  # 连接MySQL
  mysql -u root
  
  # 修改密码
  USE mysql;
  UPDATE user SET authentication_string=PASSWORD('新密码') WHERE User='root';
  FLUSH PRIVILEGES;
  
  # 重启MySQL服务
  systemctl restart mysql
                  

1.3 连接超时

问题：连接MySQL时出现”Connection timed out”

解决方案：

• 检查网络连接是否正常
• 检查MySQL服务器是否在运行
• 检查防火墙设置
• 增加连接超时时间：在my.cnf中设置connect_timeout=30

Part02-生产环境规划与建议

2.1 查询速度慢

问题：SQL查询执行速度慢

解决方案：

• 使用EXPLAIN分析查询执行计划
• 为频繁查询的列创建索引
• 优化SQL语句，避免使用SELECT *
• 使用LIMIT限制返回结果数量
• 检查是否有锁竞争

2.2 服务器负载高

问题：MySQL服务器CPU或内存使用率高

解决方案：

• 查看当前运行的查询：SHOW FULL PROCESSLIST;
• 分析慢查询日志
• 优化配置参数：

  # 调整缓冲区大小
  innodb_buffer_pool_size = 8G
  
  # 调整连接数
  max_connections = 500
  
  # 调整查询缓存
  query_cache_size = 64M
                  

• 考虑使用读写分离或分库分表

2.3 死锁

问题：出现死锁错误”Deadlock found when trying to get lock; try restarting transaction”

解决方案：

• 查看InnoDB状态：SHOW ENGINE INNODB STATUS;
• 优化事务顺序，避免循环依赖
• 减少事务持有锁的时间
• 使用较低的隔离级别
• 考虑使用乐观锁

Part03-生产环境项目实施方案

3.1 数据丢失

问题：数据意外丢失

解决方案：

• 检查备份是否可用
• 使用二进制日志恢复：mysqlbinlog binlog.000001 | mysql -u root -p
• 实施定期备份策略
• 启用自动备份

3.2 数据损坏

问题：表数据损坏

解决方案：

• 检查表：CHECK TABLE table_name;
• 修复表：REPAIR TABLE table_name;
• 对于InnoDB表，使用ALTER TABLE table_name ENGINE=InnoDB;重建
• 从备份恢复

3.3 主键冲突

问题：插入数据时出现主键冲突

解决方案：

• 检查是否真的需要插入重复数据
• 使用INSERT IGNORE忽略冲突
• 使用REPLACE INTO替换现有记录
• 使用INSERT ... ON DUPLICATE KEY UPDATE更新现有记录

Part04-生产案例与实战讲解

4.1 配置文件错误

问题：修改配置文件后MySQL无法启动

解决方案：

• 检查配置文件语法：mysqld --validate-config
• 查看错误日志：tail -f /var/log/mysql/error.log
• 恢复默认配置文件，逐步修改

4.2 内存配置不当

问题：MySQL占用内存过高或过低

解决方案：

• 根据服务器内存调整innodb_buffer_pool_size（通常为总内存的50-80%）
• 调整其他内存相关参数：

  innodb_buffer_pool_size = 8G
  innodb_log_buffer_size = 16M
  max_connections = 500
  sort_buffer_size = 2M
  read_buffer_size = 1M
  read_rnd_buffer_size = 4M
  join_buffer_size = 2M
                  

4.3 字符集问题

问题：中文显示乱码

解决方案：

• 检查数据库字符集：SHOW CREATE DATABASE dbname;
• 检查表字符集：SHOW CREATE TABLE tablename;
• 修改配置文件：

  [mysqld]
  character-set-server = utf8mb4
  collation-server = utf8mb4_unicode_ci
  
  [client]
  default-character-set = utf8mb4
                  

• 修改数据库字符集：ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
• 修改表字符集：ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Part05-风哥经验总结与分享

5.1 复制延迟

问题：从服务器复制延迟

解决方案：

• 检查网络带宽
• 优化主服务器二进制日志写入
• 增加从服务器配置：

  innodb_buffer_pool_size = 8G
  innodb_log_file_size = 1G
  slave_parallel_workers = 4
  slave_preserve_commit_order = 1
                  

• 监控复制状态：SHOW SLAVE STATUS

5.2 复制错误

问题：复制过程中出现错误

解决方案：

• 查看错误信息：SHOW SLAVE STATUS
• 根据错误信息修复问题
• 跳过错误（谨慎使用）：

  STOP SLAVE;
  SET GLOBAL sql_slave_skip_counter = 1;
  START SLAVE;
                  

• 重新同步：

  # 在主服务器上
  FLUSH TABLES WITH READ LOCK;
  SHOW MASTER STATUS;
  # 记录File和Position
  
  # 备份数据库
  mysqldump -u root -p --all-databases > backup.sql
  
  UNLOCK TABLES;
  
  # 在从服务器上
  STOP SLAVE;
  RESET SLAVE;
  mysql -u root -p < backup.sql
  CHANGE MASTER TO MASTER_LOG_FILE='记录的File', MASTER_LOG_POS=记录的Position;
  START SLAVE;
                  

6. 安全问题

6.1 密码策略问题

问题：无法设置简单密码

解决方案：

• 查看密码策略：SHOW VARIABLES LIKE 'validate_password%';
• 修改密码策略：

  SET GLOBAL validate_password_policy = LOW;
  SET GLOBAL validate_password_length = 6;
                  

• 或者使用强密码

6.2 权限问题

问题：用户没有足够的权限

解决方案：

• 查看用户权限：SHOW GRANTS FOR 'user'@'host';
• 授予权限：GRANT SELECT, INSERT ON db.* TO 'user'@'host';
• 刷新权限：FLUSH PRIVILEGES;

6.3 远程访问问题

问题：无法远程连接MySQL

解决方案：

• 检查bind-address设置（设为0.0.0.0允许所有IP）
• 创建远程用户：CREATE USER 'user'@'%' IDENTIFIED BY 'password';
• 授予远程访问权限：GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;
• 检查防火墙是否允许3306端口

7. 备份恢复问题

7.1 备份失败

问题：mysqldump备份失败

解决方案：

• 检查用户权限
• 检查磁盘空间
• 使用--single-transaction选项：mysqldump --single-transaction -u root -p db > backup.sql
• 查看错误信息并解决

7.2 恢复失败

问题：恢复备份时失败

解决方案：

• 检查SQL语法错误
• 检查权限
• 检查磁盘空间
• 使用--force选项：mysql --force -u root -p db < backup.sql

8. 版本升级问题

8.1 升级失败

问题：MySQL版本升级失败

解决方案：

• 备份数据
• 检查升级路径是否支持
• 查看错误日志
• 解决依赖问题
• 使用mysql_upgrade更新系统表：mysql_upgrade -u root -p

8.2 兼容性问题

问题：升级后应用程序无法正常工作

解决方案：

• 检查SQL语法兼容性
• 检查系统变量变化
• 检查权限系统变化
• 参考MySQL官方升级指南

9. 监控与维护问题

9.1 日志过大

问题：MySQL日志文件过大

解决方案：

• 配置日志轮转
• 调整二进制日志保留时间：SET GLOBAL expire_logs_days = 7;
• 清理旧日志：PURGE BINARY LOGS BEFORE '2026-04-01';
• 关闭不必要的日志

9.2 表空间不足

问题：表空间不足

解决方案：

• 清理无用数据
• 优化表：OPTIMIZE TABLE table_name;
• 增加磁盘空间
• 考虑使用分区表

10. 工具使用问题

10.1 MySQL Workbench连接问题

问题：MySQL Workbench无法连接到服务器

解决方案：

• 检查网络连接
• 检查MySQL服务是否运行
• 检查防火墙设置
• 检查用户权限

10.2 命令行工具问题

问题：命令行工具无法正常工作

解决方案：

• 检查PATH环境变量
• 检查MySQL客户端是否安装
• 使用绝对路径：/usr/bin/mysql

11. 常见错误代码及解决方法

11.1 错误代码1062

错误：Duplicate entry 'value' for key 'PRIMARY'

解决方法：避免插入重复的主键值，使用INSERT IGNORE或REPLACE INTO

11.2 错误代码1045

错误：Access denied for user 'user'@'host' (using password: YES)

解决方法：检查用户名、密码和权限

11.3 错误代码1048

错误：Column 'column_name' cannot be null

解决方法：为非空列提供值

11.4 错误代码1054

错误：Unknown column 'column_name' in 'field list'

解决方法：检查列名是否正确

11.5 错误代码1064

错误：You have an error in your SQL syntax

解决方法：检查SQL语法

11.6 错误代码2002

错误：Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

解决方法：检查MySQL服务是否运行，检查socket文件路径

11.7 错误代码2003

错误：Can't connect to MySQL server on 'host' (111)

解决方法：检查网络连接，检查MySQL服务是否运行，检查防火墙设置

11.8 错误代码2013

错误：Lost connection to MySQL server during query

解决方法：增加wait_timeout值，检查网络连接

12. 故障排查工具

12.1 日志分析

• 错误日志：/var/log/mysql/error.log
• 慢查询日志：通过slow_query_log启用
• 二进制日志：用于复制和恢复

12.2 状态查看

• 服务器状态：SHOW GLOBAL STATUS;
• 连接状态：SHOW PROCESSLIST;
• InnoDB状态：SHOW ENGINE INNODB STATUS;
• 复制状态：SHOW SLAVE STATUS

12.3 第三方工具

• Percona Toolkit：包含多种MySQL管理工具
• MySQL Workbench：图形化管理工具
• phpMyAdmin：Web-based管理工具
• Nagios/Zabbix：监控工具

13. 预防措施

13.1 定期备份

• 实施定期备份策略
• 测试备份恢复
• 存储备份到安全位置

13.2 监控系统

• 监控服务器状态
• 监控查询性能
• 设置告警机制

13.3 安全加固

• 使用强密码
• 限制远程访问
• 定期更新MySQL版本
• 移除不必要的用户和权限

13.4 性能优化

• 定期分析和优化表
• 调整配置参数
• 监控慢查询并优化