MySQL常见问题汇总

1、"Host is not allowed to connect to this MySQL server"
方法1：GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION //赋予任何主机访问数据的权限

方法2：update user set host = '%' where user = 'root';

重启mysql 服务

2、mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `action` at row: 4064488
原因：dumping table 太大，链接超时

解决方案：可在启动mysql时设置比较大的timeout值

或使用root登录mysql

mysql> show global variables like '%timeout%';

set global net_read_timeout = 120;

set global net_write_timeout = 900;

3、查看线下机器mysql负载
mysqladmin -h127.0.0.1 -Pport -uuser -ppasswd extended-status -r -i 1|egrep -i "com_select|com_insert|com_update|com_delete|com_replace"

对于瞬时值
mysqladmin -h127.0.0.1 -Pport -uuser -ppasswd extended-status -i 1|egrep -i "threads_connected|threads_running"

其他方法查看mysql的负载高的原因：

general_log 打开之后写数据库日志需要打开IO，对数据库自身性能会有稍许的影响，不完全统计每次按pk查询会慢多少：0.0000022s。

编辑MYSQL配置文件my.cnf,加上以下几行:
log_slow_queries=/usr/local/mysql/data/log_slow_queries.log //慢语句日志保存目录
long_query_time=10 //记录SQL查询超过10S的语句
log-queries-not-using-indexes=1 //记录没有使用索引的sql

这样,慢语句就被记录在日志文件中了.
tail -f /usr/local/mysql/data/log_slow_queries.log 查看日志.
四个参数
Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 54
分别意思为:查询时间 锁定时间 查询结果行数 扫描行数
主要看那些扫描行数超多的语句.然后去数据库加上对应的索引

如果MySQL在本机连接速度很快,但远程连接速度慢则在/etc/my.cnf的[mysqld]中添加一项
skip-name-resolve
这样连接时MySQL就不会对连接来的IP或域名进行DNS解析,从而使用速度加快

或者

在mysql中执行语句：

show variables like '%slow%';
+---------------------------+-----------------------------------------------------------------+
| Variable_name | Value |
+---------------------------+-----------------------------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /home/work/.jumbo/var/lib/mysql/nj01-build-en-s-bak088-slow.log |
+---------------------------+-----------------------------------------------------------------+

set slow_query_log=on;

set global slow_query_log_file='/home/work/mysql/log/nj01-build-en-s-bak088-slow.log';

4、MySQL锁
Mysql3中锁特性如下：

表级锁：开销小，加锁块；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发性也最高。

页面锁：开销和加锁界于表锁和行锁之间，会出现死锁；锁定粒度界与表锁和行锁之间，并发一般。

5、MySQL引擎
参考资料：【整理】MySQL引擎 - 白草黒尖 - 博客园

MySQL存储引擎InnoDB与Myisam的六大区别 - Junn的个人页面 - OSCHINA - 中文开源技术交流社区

MyISAM

MYISAM 它不支持事务, 是表级锁,就是说读写都是相互阻塞的。

它们不是事务安全的，而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量的SELECT，MyISAM是更好的选择。

适用场景：

(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。

InnoDB

是行级锁。

InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，innoDB才使用行级锁，否则InnoDB将使用表锁，在实际开发中应当注意。

数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表，对于支持事物的InnoDB类型的表，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行SQL前调用BEGIN，多条SQL形成一个事物（即使AUTOCOMMIT打开也可以），将大大提高性能。

适用场景：

(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。

InnoDB表比MyISAM表更安全。

在两者选择时需考虑以下问题：

你的数据库有外键吗？

你需要事务支持吗？

你需要全文索引吗？

你经常使用什么样的查询模式？

你的数据有多大？

思考上面这些问题可以更快的找到合适的方向，但那并不是绝对的。如果需要事务处理或是外键，那么InnoDB 可能是比较好的方式。如果需要全文索引，那么通常来说 MyISAM是好的选择，因为这是系统内建的，然而，其实并不会经常地去测试两百万行记录。所以，就算是慢一点，可以通过使用Sphinx从InnoDB中获得全文索引。

6、安全更新限制
a) 设置DB model为safe_update
mysql有个叫SQL_SAFE_UPDATES的变量，为了数据库更新操作的安全性，此值默认为1，所以才会出现更新失败的情况。

查看变量设置：

mysql> show variables like 'sql_safe%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sql_safe_updates | ON |
+------------------+-------+
1 row in set (0.06 sec)

下面是SQL_SAFE_UPDATES变量为0和1时的取值说明：

SQL_SAFE_UPDATES有两个取值0和1， 或ON 和OFF;

SQL_SAFE_UPDATES = 1，ON时，不带where和limit条件的update和delete操作语句是无法执行的，即使是有where和limit条件但不带key column的update和delete也不能执行。

SQL_SAFE_UPDATES =0，OFF时，update和delete操作将会顺利执行。那么很显然，此变量的默认值是1。

所以，出现1175错误的时候，可以先设置SQL_SAFE_UPDATES的值为0 OFF，然后再执行更新;

b）querey前加 --safe_updates
7、 Deny Sql when using LOCK TABLES
mysqldudmp -u 数据库用户名 -p 数据库密码 数据库 --skip-lock-tables > 备份数据库.sql

8、修改mysql启动接口
show global variables like 'port';

vi ~/.jumbo/etc/mysql/my.cnf

添加 port=8902

重启服务

${JUMBO_ROOT}/share/mysql/mysql.server start

9、启动MySQL服务报错，The server quit without updating PID file
查看启动报错日志： ~/.jumbo/var/lib/mysql/szth-inf-bch-sandbox02.szth.err

10、mysql安装后，初始root密码有误
暂时解决方案: /etc/my.conf

[mysqld]

./mysqld_safe --skip-grant-tables &

11、Got error 28 from storage engine

这通常意味着存储引擎遇到了问题。错误的具体含义可能因数据库系统的不同而有所差异，但一般来说，这可能与磁盘空间不足、文件权限问题、存储引擎内部错误或配置问题有关。

为了解决这个问题，你可以尝试以下步骤：

1. 检查磁盘空间：确保数据库服务器上的磁盘空间充足。如果磁盘空间不足，你可能需要清理一些不必要的文件或增加磁盘空间。

2. 检查文件权限：确保数据库文件和目录有正确的读写权限。错误的权限设置可能导致存储引擎无法正常访问文件。

3. 查看日志文件：检查数据库的错误日志文件，通常可以找到更详细的错误信息，帮助你诊断问题。

4. 检查存储引擎配置：确保存储引擎的配置是正确的。如果你使用的是 InnoDB 存储引擎，检查其配置参数是否合理。

5. 重启数据库服务：有时候重启数据库服务可以解决临时的问题。

6. 更新或修复数据库：如果问题持续存在，考虑更新数据库系统或运行修复工具来修复潜在的损坏。

7. 寻求专业帮助：如果你无法解决问题，可能需要寻求数据库管理员或专家的帮助。