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;

对于瞬时值
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中执行语句：

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，所以才会出现更新失败的情况。

查看变量设置：

下面是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

重启服务