合 MySQL数据库常见错误及解决方案
Tags： MySQL
👉 本文共约11187个字，系统预计阅读时间或需43分钟。
目录
- MySQL无法重启问题解决Warning: World-writable config file ‘/etc/my.cnf’ is ignored
- Can't connect to MySQL server on 'localhost' (10061)
- Unknown MySQL Server Host 'localhosadst' (11001)
- Access denied for user: 'roota@localhost' (Using password: YES)
- Access denied for user: 'red@localhost' to database 'newbbs'
- No Database Selected
- Can't open file: 'xxx_forums.MYI'. (errno: 145)
- Table 'test.xxx_sessions' doesn't exist
- Unknown column 'column_name' in 'field list'
- You have an error in your SQL syntax
- Duplicate entry 'xxx' for key 1
- Duplicate key name 'xxx'
- Duplicate column name 'xxx'
- Table 'xxx' already exists
- Can't create database 'xxx'. Database exists
- 小结（针对问题 11\12\13\14\15）
- Unknown system variable 'NAMES'
- Lost connection to MySQL server during query
- User 'red' has exceeded the 'max_updates' resource (current value: 500)
- Too many connections (1040)链接过多
- There is no such grant defined for user '%s' on host '%s'
- Error on rename of '%s' to '%s' (errno: %d)
- Error reading file '%s' (errno: %d)
- Host '...' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
- dropping database (can't delete '%s', errno: %d)
- Got error 28 from table handler
- Can't create a new thread; if you are not out of available memory, you can consult the manual for a possible OS-dependent bug。
- Error: Client does not support authentication protocol requested by server; consider upgrading MySQL client
- Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
- Can't connect to MySQL server on 'localhost'
- Lost connection to MySQL server during query
- Got a packet bigger than 'max_allowed_packet' bytes
- Too many connections（连接数过多，导致连接不上数据库，业务无法正常进行）
- 主从复制报错类型
- MySQL安装过程中的报错
- 数据库密码忘记的问题
- truncate 删除数据，导致自动清空自增 ID，前端返回报错 not found
- 阿里云 MySQL 的配置文件
- 数据库总会出现中文乱码的情况
- 使用 binlog_format=statement 这种格式，跨库操作，导致从库丢失数据，用户访问导致出现错误数据信息
- MySQL 数据库连接超时的报错
- can't open file (errno:24)
MySQL无法重启问题解决Warning: World-writable config file ‘/etc/my.cnf’ is ignored
原因：
今天帮朋友维护服务器，在关闭数据库的命令发现mysql关不了，提示Warning: World-writable config file '/etc/my.cnf' is ignored ，大概意思是权限全局可写，任何一个用户都可以写。mysql担心这种文件被其他用户恶意修改，所以忽略掉这个配置文件。这样mysql无法关闭。
1 2 3 4 | [root@ttlsa ~]# service mysqld stop Warning: World-writable config file '/etc/my.cnf' is ignored Warning: World-writable config file '/etc/my.cnf' is ignored MySQL manager or server PID file could not be found![FAILED] |
查看my.cnf的权限
1 2 | [root@ttlsa ~]# ls -l /etc/my.cnf -rwxrwxrwx 1 root root 4878 Jul 30 11:31 /etc/my.cnf |
权限777，任何一个用户都可以改my.cnf，存在很大的安全隐患.
解决方案：
修复MySQL问题，my.cnf设置为用户可读写，其他用户不可写
1 | chmod 644 /etc/my.cnf |
Can't connect to MySQL server on 'localhost' (10061)
原因：
不能连接到 localhost 上的mysql
解决方案：
这说明“localhost”计算机是存在的，但在这台机器上却没提供MySQL服务。
需要启动这台机器上的MySQL服务,如果机子负载太高没空相应请求也会产生这个错误。
解决：既然没有启动那就去启动这台机子的mysql。如果启动不成功，多数是因为你的my.ini配置的有问题。重新配置其即可。
如果觉得mysql负载异常，可以到mysql/bin 的目录下执行mysqladmin -uroot -p123 processlist来查看mysql当前的进程。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
