MySQL常见故障与优化

一、mysql单实例常见故障:

1.故障一:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket/data/mysql/mysql.sock'(2)
  • 问题分析:以上这种情况一般都是数据库未启动或者数据库端口被防火墙拦截导致。
  • 解决方法:启动数据库或者防火墙开放数据库监听端口。

2.故障二:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
  • 问题分析:密码不正确或者没有权限访问。
  • 解决方法:

修改my.cnf主配置文件,在[mysqld]下添加 skip-grant-tables

        update更新user表authentication_string字段

        重新授权

3.故障三:

在使用远程连接数据库时偶尔会发生远程连接数据库很慢的问题。

  • 问题分析:

如果 MySQL 主机查询 DNS 很慢或是有很多客户端主机时会导致连接很慢. 由于开发机器是不能够连接外网的,在进行 MySQL 连接时,DNS 解析是不可能完成的, 从而也就明白了为什么连接那么慢了。

  • 解决方法:

修改 my.cnf 主配置文件,在[mysqld]下添加 skip-name-resolve,重启数据库可以解决。注意在以后授权里面不能再使用主机名授权。

4.故障四:

Can't open file: 'xxx_forums.MYI'. (errno: 145)

问题分析:

  • 服务器非正常关机,数据库所在空间已满,或一些其它未知的原因对数据库表造成了损坏
  • 因拷贝数据库导致文件的属组发生变化

解决方法:

  • 修复数据表 (myisamchk、phpMyAdmin)
  • 修改文件的属组

5.故障五:

ERROR 1129 (HY000): Host 'xxx.xxx.xxx.xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

问题分析:

  • 超出最大连接错误数量限制

解决方法:

  • 清除缓存(flush-hosts关键字)
  • 修改mysql配置文件 (max_connect_errors=1000)

6.故障六:

Too many connections

问题分析:

  • 连接数超出MySQL的最大连接限制

解决方法:

  • 修改MySQL配置文件 (max_connections=2000)
  • 临时修改参数:set GLOBAL max_connections=2000;

7.故障七:

Warning:World-writable config file '/etc/my.cnf' is ignored
ERROR! MySQL is running but PlD file could not be found

问题分析:

  • MySQL的配置文件/etc/my.cnf 权限问题

解决方法:

  • chmod 644 /etc/my.cnf

8.故障八:

InnoDB:Error: page 14178 log sequence number 29455369832
InnoDB: is in the future! Current system log sequence number 29455369832

问题分析:

  • innodb数据文件损坏

解决方法:

  • 修改 my.cnf 配置文件 (innodb_force_recovery=4)
  • 启动数据库后备份数据文件
  • 利用备份文件恢复数据

二、主从环境常见故障:

1.故障一:

从库的Slave_lO_Running为NO

The slave I/O thread stops because master and slave have equal MySQL serverids; 
these ids must be different for replication to work (or the --replicate-same-server-id
option must be used on slave but this does not always make sense;
please check the manual before using it).

问题分析:

  • 主库和从库的server-id值一样

解决方法:

  • 修改从库的 server-id 的值,修改为和主库不一样
  • 重新启动数据库并再次同步

2. 故障二:

从库的Slave_lO_Running为NO

问题分析:

  • 主键冲突或者主库删除或更新数据,从库内找不到记录,数据被修改导致

解决方法:

  • 方法一 

mysql> stop slave;

mysqI> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> start slave;

  • 方法二

set global read_only=true;

3. 故障三:

Error initializing relay log position: I/O error reading the header from 
the binary log

问题分析:

  • 从库的中继日志 relay-bin 损坏

解决方法:

  • 手工修复,重新找到同步的 binlog 和 pos 点,然后重新同步即可
mysqI> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx;

三、mysql优化:

可以从不同角度进行优化:

  • 硬件优化
  • MySQL配置文件优化
  • SQL优化
  • MySQL架构优化

1.硬件优化:

cpu:64位,高主频,高缓存,高并行处理能力

内存:大内存,主频高,尽量不要使用SWAP

硬盘:15000转,RAID5 ,RAID10   ,SSD

网络:标配的千兆网卡,10G网卡,bond0,MySQL服务器尽可能和使用它的web服务器在同一局域网内,尽量避免诸如防火墙策略等不必要的开销

2.查询优化:

1.优化建表结构

2.建立合适的索引

3.查询时尽量减少逻辑运算

4.减少不当的查询语句,不要查询应用中不需要的列

5.减少事务包大小

6.将多个小的查询适当合并成一个大的查询,减少每次建立/关闭查询的开销

7.将某些过于复杂的查询拆解成多个小查询,和上一条正好相反

8.建立和优化存储过程来代替大量的外部程序交互

3.操作系统的优化:

网卡bond技术

设置TCP连接数量限制,优化系统打开文件的最大限制

使用64位操作系统,64位系统可以分给单个进程更多内存,计算更快

禁止不必要启动的服务

4.my.cnf内参数的优化:

4.1连接参数:

  • max_connections:限制并发连接数,根据应用需求适当调整
  • thread_cache_size:控制线程缓存的大小,以减少创建和销毁线程的开销

4.2缓存池设置:

  • innodb_buffer_pool_size:设置lnnoDB缓冲池的大小,通常建议设置为物理内存的50%-70%
  • key_buffer_size:对于MyISAM表,这个参数表示键缓存的大小

4.3日志设置:

  • log_error:指定错误日志文件的路径
  • slow_query_log:启动慢查询日志,帮助识别需要优化的查询
  • general_log:启动通用查询日志,用于调试目的

4.4lnnoDB参数设置:

  • lnnodb_log_file_size:设置lnnoDB事务日志文件的大小
  • innodb_flush_log_at_trx_commit:控制事务提交时日志刷新的行为
  • innodb_file_per_table:对于innoDB表,启动独立表空间

4.5查询缓存和临时表位置:

  • query_cache_type和query_cache_size:如果使用查询缓存,可以设置缓存的类型和大小
  • tmp_table_size和max_heap_table_size:控制临时表的内存和磁盘使用

4.6其他参数:

  • table_open_cache:设置打开表的缓存数量,以提高性能
  • innodb_io_capacity:设置lnnoDB的I/O容量,适应存储设备的性能
  • skip-name-reslove:禁止使用使用DNS查找,只能使用ip地址

4.7安全参数:

  • secure_fiel_priv:限制导入/导出数据的目录
  • skip_networking:禁止网络连接,只允许本地连接
  • 9
    点赞
  • 15
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL常见故障包括但不限于以下几种: 1.连接问题:MySQL连接超时、连接数超限等。解决方法包括增加连接超时时间、增加最大连接数、优化SQL查询、关闭无用的连接等。 2.性能问题:MySQL在高并发情况下出现慢查询、存储过程执行时间过长等。解决方法包括优化查询语句、创建索引、增加硬件资源、分库分表等。 3.数据丢失:MySQL数据库文件损坏、误删除数据等导致数据丢失。解决方法包括定期备份数据、使用热备份技术、定期检查和修复表结构、恢复误删除的数据等。 4.死锁问题:多个事务相互等待资源导致的死锁。解决方法包括优化事务逻辑、合理设置事务隔离级别、增加并发连接数、使用锁超时机制、增加数据库的缓存等。 5.重启问题:MySQL异常重启或崩溃。解决方法包括检查错误日志、分析问题产生的原因、修复并重启数据库、定期监控数据库运行状况等。 为避免以上故障,还需注意以下几点: 1.合理设计数据库架构,遵循范式,避免冗余和重复数据。 2.合理选择数据类型和字段长度,避免浪费存储空间。 3.定期统计优化数据库性能,包括分析查询语句、创建索引、优化表结构、合理分布表数据等。 4.定时备份数据,备份频率视业务需求而定。 5.使用合理的硬件资源,包括CPU、内存和存储设备等。 总之,对于MySQL常见的故障,解决方法需要根据具体情况采用相应的策略。在平时的运维工作中,及时发现和解决问题可以最大程度保证MySQL数据库的正常运行。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值