存档在 ‘数据库’ 分类

mysql中的 skip-name-resolve 问题

2017年1月10日

mysql连接很慢,登陆到服务器上查看服务器日志都是正常的,无可疑记录,登陆到mysql服务器上,查看下进程,发现有很多这样的连接:
218 | unauthenticated user | 192.168.10.6:44500 | NULL | Connect | NULL | login | NULL
219 | unauthenticated user | 192.168.10.6:44501 | NULL | Connect | NULL | login | NULL
……..

原因是由于mysql对连接的客户端进行DNS反向解析。
有2种解决办法:
1,把client的ip写在mysql服务器的/etc/hosts文件里,随便给个名字就可以了。
2,在 my.cnf 中加入 –skip-name-resolve 。
对于第一种方法比较笨,也不实用,那么 skip-name-resolve 选项可以禁用dns解析,但是,这样不能在mysql的授权表中使用主机名了,只能使用IP。
我理解mysql是这样来处理客户端解析过程的,
1,当mysql的client连过来的时候,服务器会主动去查client的域名。
2,首先查找 /etc/hosts 文件,搜索域名和IP的对应关系。
3,如果hosts文件没有,则查找DNS设置,如果没有设置DNS服务器,会立刻返回失败,就相当于mysql设置了skip-name-resolve参数,如果设置了DNS服务器,就进行反向解析,直到timeout。

所谓反向解析是这样的:
mysql接收到连接请求后,获得的是客户端的ip,为了更好的匹配mysql.user里的权限记录(某些是用hostname定义的)。
如果mysql服务器设置了dns服务器,并且客户端ip在dns上并没有相应的hostname,那么这个过程很慢,导致连接等待。

添加skip-name-resolve以后就跳过着一个过程了。

MySQL索引的创建、删除和查看

2016年8月15日

1.索引作用

在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。

例如,有3个未索引的表t1、t2、t3,分别只包含列c1、c2、c3,每个表分别含有1000行数据组成,指为1~1000的数值,查找对应值相等行的查询如下所示。

SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3

此查询结果应该为1000行,每行包含3个相等的值。在无索引的情况下处理此查询,必须寻找3个表所有的组合,以便得出与WHERE子句相配的那些行。而可能的组合数目为1000×1000×1000(十亿),显然查询将会非常慢。

如果对每个表进行索引,就能极大地加速查询进程。利用索引的查询处理如下。

(1)从表t1中选择第一行,查看此行所包含的数据。

(2)使用表t2上的索引,直接定位t2中与t1的值匹配的行。类似,利用表t3上的索引,直接定位t3中与来自t1的值匹配的行。

(3)扫描表t1的下一行并重复前面的过程,直到遍历t1中所有的行。

在此情形下,仍然对表t1执行了一个完全扫描,但能够在表t2和t3上进行索引查找直接取出这些表中的行,比未用索引时要快一百万倍。

利用索引,MySQL加速了WHERE子句满足条件行的搜索,而在多表连接查询时,在执行连接时加快了与其他表中的行匹配的速度。

2.  创建索引

在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

1.ALTER TABLE

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

 

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

 

其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。

2.CREATE INDEX

CREATE INDEX可对表增加普通索引或UNIQUE索引。

 

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

 

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。

3.索引类型

在创建索引时,可以规定索引能否包含重复值。如果不包含,则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。

PRIMARY KEY索引和UNIQUE索引非常类似。事实上,PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY,因为一个表中不可能具有两个同名的索引。

下面的SQL语句对students表在sid上添加PRIMARY KEY索引。

 

ALTER TABLE students ADD PRIMARY KEY (sid)

4.  删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

 

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

 

其中,前两条语句是等价的,删除掉table_name中的索引index_name。

第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

5.查看索引

mysql> show index from tblname;

mysql> show keys from tblname;

  • Table

    表的名称。

    · Non_unique

    如果索引不能包括重复词,则为0。如果可以,则为1。

    · Key_name

    索引的名称。

    · Seq_in_index

    索引中的列序列号,从1开始。

    · Column_name

    列名称。

    · Collation

    列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。

    · Cardinality

    索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。

    · Sub_part

    如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。

    · Packed

    指示关键字如何被压缩。如果没有被压缩,则为NULL。

    · Null

    如果列含有NULL,则含有YES。如果没有,则该列含有NO。

    · Index_type

    用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

    · Comment

Mac os下新安装的MySQL无法登陆root用户解决方法

2016年1月29日

也不知是何原因,新安装好的MySQL,如果尝试用mysql -u root -p登陆就会出现这样的错误,但是root用户根本就没有设置密码。
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

下面是解决方案:
1、先在系统偏好设置中关闭MySQL服务;
2、在终端中输入

sudo su
mysqld_safe --skip-grant-tables --skip-networking &

这时便能越过权限表,直接登陆MySQL了。
3、新建一个终端,输入

mysql -u root

4、 在MySQL中修改root用户密码即可:

mysql> UPDATE mysql.user SET password=PASSWORD(’新密码’) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;

中途可能出现:
[java]ERROR 1054 (42S22): Unknown column ‘Password’ in ‘field list’[/java]
进入数据库mysql,查看user表,其中确实没有Password这一列,解决办法是:

update mysql.user set authentication_string=password('root') where user='root' ;

如何将frm格式MYD格式MYI格式文件导入MySQL中

2016年1月21日

frm,myd,myi是属于MySQL存储数据的文件,phpMyAdmin是无法导入的。

phpMyAdmin支持的文件格式为sql文件。

其实很简单:

1.找到你的mysql的安装目录下的data文件夹,新建一个文件夹,文件夹的名称是你想设计的库的名称,把这些文件(frm,myd,myi格式的文件)放到此文件夹中。

  提示:也可以使用mysql管理工具创建一个数据库(例如:create database mydb;其中mydb是数据库名称),这时在data文件夹下会对应产生一个mydb的文件夹

2.接着你就可以用你的管理mysql的工具看看这表里到底是什么内容。

3、你也可以使用命令导出sql脚本

  mysqldump -uroot -ptian mydb> mydb.sql 其中root为用户名 tian为密码 mydb为数据库名 mydb.sql为脚本文件名

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) 问题注意点

2015年11月9日

最近新装好的mysql在进入mysql工具时,有错误提示:

# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

使用网上介绍的方法修改root用户的密码:

# mysqladmin -uroot -p password 'newpassword'
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

方法一:特别要注意的是需要到mysql目录下执行./bin/mysqld_safe,其他目录下指定mysqld_safe好像不行

# /etc/init.d/mysql stop
# ./bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
# /etc/init.d/mysqld restart
# mysql -uroot -p
Enter password: <输入新设的密码newpassword>

mysql>

方法二:
直接使用/etc/mysql/debian.cnf文件中[client]节提供的用户名和密码:

# mysql -udebian-sys-maint -p
Enter password: <输入[client]节的密码>
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
# mysql -uroot -p
Enter password: <输入新设的密码newpassword>
mysql>

MySQL Binlog的介绍

2015年8月18日

binlog基本定义:二进制日志,也成为二进制日志,记录对数据发生或潜在发生更改的SQL语句,并以二进制的形式保存在磁盘中;

作用:MySQL的作用类似于Oracle的归档日志,可以用来查看数据库的变更历史(具体的时间点所有的SQL操作)、数据库增量备份和恢复(增量备份和基于时间点的恢复)、Mysql的复制(主主数据库的复制、主从数据库的复制)

二进制日志的信息:

文件位置:默认存放位置为数据库文件所在目录下

文件的命名方式: 名称为hostname-bin.xxxxx (重启mysql一次将会自动生成一个新的binlog)

状态的查看:mysql> show variables like ‘%log_bin%’;

mysql> show variables like ‘%log_bin%’;

+———————————+——-+

| Variable_name | Value |

+———————————+——-+

| log_bin | ON | //表示当前已开启二进制日志//

| log_bin_trust_function_creators | OFF |

| sql_log_bin | ON |

+———————————+——-+

3 rows in set (0.00 sec)

 

二进制日志的管理:

1、开启二进制日志配置

方法一、修改my.cnf参数文件,该方法需要重启

 

log-bin = mysql-bin #打开日志(主机需要打开),这个mysql-bin也可以自定义,这里也可以加上路径,如:/home/www/mysql_bin_log/mysql-bin

关闭二进制日志的方法:log-bin = mysql-bin注释掉即可
 

方法二、不重启修改二进制日志配置,该方法mysql的版本需要5.6以上

SET @@global.log_bin=1|0  (1为开启,0为关闭)

SET @@global.binlog_size=37268(单位bytes)

 

3、暂停二进制日志

SET sql_log_bin={0|1}

 

4、修改二进制日志的大小

修改my.cnf参数文件中的max_binlog_size的值;

说明:如果你的二进制文件的大小超过了max_binlog_size,它就是自动创建新的二进制文件。当然如果恰好在日志文件到达它的最大尺寸时写入了大的事务,那么日志文件还是会超过max_binlog_size的大小

 

5、进行二进制日志的切换,默认情况下当二进制日志写满了或者数据库重启了才会进行切换,但是也可以手工的进行切换的动作

mysql> flush logs;

 

6、其他参数:

binlog-cache-size=100m 设置二进制日志缓存大小

sync-binlog=N(每个N秒将缓存中的二进制日志记录写回硬盘,默认值为0。不过,你经常会陷入group commit函数与I/O之间二选一的矛盾。如果在replication环境中,由于考虑到耐久性与一致性,则需要设置1。同时,还需要设置innodb_flush_log_at_trx_commit=1以及innodb-support-xa=1默认开启;)

二进制日志的写入过程

查看二进制日志的内容:

binlog不能直接用文本的方式打开,mysql提供了相应的查看工具:mysqlbinlog,直接查看单个二进制日志文件:mysqlbinlog    filename

例如: mysqlbinlog /data/mysql/mysql-bin.000001

当然也可以通过二进制日志完成数据库的恢复,具体的使用将在数据库的备份还原中介绍。

删除二进制日志

二进制日志会不断的增长,并产生多个文件。因此,需要制定备份计划和管理策略.无用的二进制日志要记得及时删除。

删除慢查询日志有三种方法:

1、操作系统命令直接删除

2、reset master

3、PURGE BINARY LOGS BEFORE ‘2014-07-09 12:40:26′;

总结:二进制日志用途广泛也很重要,大多少情况下会开启,对于业务操作频繁的数据库需要制定相应的备份策略和删除策略;

MySQL数据恢复--binlog  http://www.linuxidc.com/Linux/2014-03/97907.htm

MySQL中binlog日记清理 http://www.linuxidc.com/Linux/2011-02/32017.htm

如何安全删除MySQL下的binlog日志 http://www.linuxidc.com/Linux/2013-06/86527.htm

MySQL–binlog日志恢复数据 http://www.linuxidc.com/Linux/2013-04/82368.htm

MySQL删除binlog日志及日志恢复数据的方法 http://www.linuxidc.com/Linux/2012-12/77072.htm

MySQL binlog三种格式介绍及分析 http://www.linuxidc.com/Linux/2012-11/74359.htm

MySQL 利用binlog增量备份+还原实例 http://www.linuxidc.com/Linux/2012-09/70815.htm

MySQL删除binlog日志及日志恢复数据 http://www.linuxidc.com/Linux/2012-08/67594.htm

3篇MySQL优化博文

2015年1月24日

sql优化博文
http://my.oschina.net/goto/blog/89304

http://my.oschina.net/h2do/blog/268136

http://my.oschina.net/liujinlongno1/blog/156101

MySQL的101个调节和优化的提示

2013年8月28日

 MySQL是一个功能强大的开源数据库。随着越来越多的数据库驱动的应用程序,人们一直在推动MySQL发展到它的极限。这里是101条调节和优化 MySQL安装的技巧。一些技巧是针对特定的安装环境的,但这些思路是通用的。我已经把他们分成几类,来帮助你掌握更多MySQL的调节和优化技巧。
  MySQL 服务器硬件和操作系统调节:
  1. 拥有足够的物理内存来把整个InnoDB文件加载到内存中——在内存中访问文件时的速度要比在硬盘中访问时快的多。
  2. 不惜一切代价避免使用Swap交换分区 – 交换时是从硬盘读取的,它的速度很慢。
  3. 使用电池供电的RAM(注:RAM即随机存储器)。
  4. 使用高级的RAID(注:Redundant Arrays of Inexpensive Disks,即磁盘阵列) – 最好是RAID10或更高。
  5. 避免RAID5(注:一种存储性能、数据安全和存储成本兼顾的存储解决方案) – 确保数据库完整性的校验是要付出代价的。
  6. 将操作系统和数据分区分开,不仅仅是逻辑上,还包括物理上 – 操作系统的读写操作会影响数据库的性能。
  7. 把MySQL临时空间和复制日志与数据放到不同的分区 – 当数据库后台从磁盘进行读写操作时会影响数据库的性能。
  8. 更多的磁盘空间等于更快的速度。
  17. 使用 XFS 文件系统 – 一种比ext3更快、更小的文件系统,并且有许多日志选项, 而且ext3 已被证实与MySQL有双缓冲问题。
  18. 调整 XFS 文件系统日志和缓冲变量 – 为了最高性能标准。
  19. 在 Linux 系统中, 使用 NOOP 或者 DEADLINE IO 定时调度程序 – 同 NOOP 和 DEADLINE定时调度程序相比,这个 CFQ 和 ANTICIPATORY 定时调度程序 显得非常慢。
  20. 使用64位的操作系统 – 对于MySQL,会有更大的内存支持和使用。
  21. 删除服务器上未使用的安装包和守护进程 – 更少的资源占用。
  22. 把使用MySQL的host和你的MySQL host放到一个hosts文件中 – 没有DNS查找。
  23. 切勿强制杀死一个MySQL进程 – 你会损坏数据库和正在运行备份的程序。
  24. 把服务器贡献给MySQL – 后台进程和其他服务能够缩短数据库占用CPU的时间。
  MySQL 配置:
  25. 当写入时,使用 innodb_flush_method=O_DIRECT 来避免双缓冲。
  26. 避免使用 O_DIRECT 和 EXT3 文件系统 – 你将序列化所有要写入的。
  27. 分配足够的 innodb_buffer_pool_size 来加载整个 InnoDB 文件到内存中– 少从磁盘中读取。
  28. 不要将 innodb_log_file_size 参数设置太大, 这样可以更快同时有更多的磁盘空间 – 丢掉多的日志通常是好的,在数据库崩溃后可以降低恢复数据库的时间。
  29. 不要混用 innodb_thread_concurrency 和 thread_concurrency 参数– 这2个值是不兼容的。
  30. 分配一个极小的数量给 max_connections 参数 – 太多的连接会用尽RAM并锁定MySQL服务。
  31. 保持 thread_cache 在一个相对较高的数字,大约 16 – 防止打开连接时缓慢。
  32. 使用skip-name-resolve参数 – 去掉 DNS 查找。
  33.如果你的查询都是重复的,并且数据不常常发生变化,那么可以使用查询缓存。但是如果你的数据经常发生变化,那么使用查询缓存会让你感到失望。
  34.增大temp_table_size值,以防止写入磁盘
  35.增大max_heap_table_size值,以防止写入磁盘
  36.不要把sort_buffer_size值设置的太高,否则的话你的内存将会很快耗尽
  37.根据key_read_requests和key_reads值来决定key_buffer的大小,一般情况下key_read_requests应该比key_reads值高,否则你不能高效的使用key_buffer
  38.将innodb_flush_log_at_trx_commit设置为0将会提高性能,但是如果你要保持默认值(1)的话,那么你就要确保数据的完整性,同时你也要确保复制不会滞后。
  39.你要有一个测试环境,来测试你的配置,并且在不影响正常生产的情况下,可以常常进行重启。
  MySQL模式优化:
  40. 保持你的数据库整理性。
  41. 旧数据归档 – 删除多余的行返回或搜索查询。
  42. 将您的数据加上索引.
  43. 不要过度使用索引,比较与查询.
  44. 压缩文字和BLOB数据类型 – 以节省空间和减少磁盘读取次数.
  45. UTF 8和UTF16都低于latin1执行效率.
  46. 有节制地使用触发器.
  47. 冗余数据保持到最低限度 – 不重复不必要的数据.
  48. 使用链接表,而不是扩展行.
  49. 注意数据类型,在您的真实数据中,尽可能使用最小的一个.
  50. 如果其他数据经常被用于查询时,而BLOB / TEXT数据不是,就把BLOB / TEXT数据从其他数据分离出来.
  51.检查和经常优化表.
  52. 经常重写InnoDB表优化.
  53. 有时,当添加列时删除索引,然后在添加回来索引,这样就会更快.
  54. 针对不同的需求,使用不同的存储引擎.
  55. 使用归档存储引擎日志表或审计表-这是更有效地写道.
  56. 会话数据存储在缓存(memcache)的而不是MySQL中 – 缓存允许自动自动填值的,并阻止您创建难以读取和写入到MySQL的时空数据.
  57.存储可变长度的字符串时使用VARCHAR而不是CHAR – 节省空间,因为固定长度的CHAR,而VARCHAR长度不固定(UTF8不受此影响).
  58. 逐步进行模式的变化 – 一个小的变化,可以有巨大的影响.
  59.在开发环境中测试所有模式,反映生产变化.
  60. 不要随意更改你的配置文件中的值,它可以产生灾难性的影响.
  61. 有时候,在MySQL的configs少即是多.
  62.有疑问时使用一个通用的MySQL配置文件.
MySQL metrics widget
  查询优化:
  63. 使用慢查询日志去发现慢查询。
  64. 使用执行计划去判断查询是否正常运行。
  65. 总是去测试你的查询看看是否他们运行在最佳状态下 –久而久之性能总会变化。
  66. 避免在整个表上使用count(*),它可能锁住整张表。
  67. 使查询保持一致以便后续相似的查询可以使用查询缓存。
  68. 在适当的情形下使用GROUP BY而不是DISTINCT。
  69. 在WHERE, GROUP BY和ORDER BY子句中使用有索引的列。
  70. 保持索引简单,不在多个索引中包含同一个列。
  71. 有时候MySQL会使用错误的索引,对于这种情况使用USE INDEX。
  72. 检查使用SQL_MODE=STRICT的问题。
  73. 对于记录数小于5的索引字段,在UNION的时候使用LIMIT不是是用OR.
  74. 为了 避免在更新前SELECT,使用INSERT ON DUPLICATE KEY或者INSERT IGNORE ,不要用UPDATE去实现。
  75. 不要使用 MAX,使用索引字段和ORDER BY子句。
  76. 避免使用ORDER BY RAND().
  77。LIMIT M,N实际上可以减缓查询在某些情况下,有节制地使用。
  78。在WHERE子句中使用UNION代替子查询。
  79。对于UPDATES(更新),使用 SHARE MODE(共享模式),以防止独占锁。
  80。在重新启动的MySQL,记得来温暖你的数据库,以确保您的数据在内存和查询速度快。
  81。使用DROP TABLE,CREATE TABLE DELETE FROM从表中删除所有数据。
  82。最小化的数据在查询你需要的数据,使用*消耗大量的时间。
  MySQL 备份过程:
  87. 从二级复制服务器上进行备份。
  88. 在进行备份期间停止复制,以避免在数据依赖和外键约束上出现不一致。
  89. 彻底停止MySQL,从数据库文件进行备份。
  90. 如果使用 MySQL dump进行备份,请同时备份二进制日志文件 – 确保复制没有中断。
  91. 不要信任LVM 快照 – 这很可能产生数据不一致,将来会给你带来麻烦。
  92. 为了更容易进行单表恢复,以表为单位导出数据 – 如果数据是与其他表隔离的。
  93. 当使用mysqldump时请使用 –opt。
  94. 在备份之前检查和优化表。
  95. 为了更快的进行导入,在导入时临时禁用外键约束。
  96. 为了更快的进行导入,在导入时临时禁用唯一性检测。
  97. 在每一次备份后计算数据库,表以及索引的尺寸,以便更够监控数据尺寸的增长。
  98. 通过自动调度脚本监控复制实例的错误和延迟。
  99. 定期执行备份。
  100. 定期测试你的备份。
  最后 101: 执行MySQL 监控: Monitis Unveils The World’s First Free On-demand MySQL Monitoring.
  83。考虑持久连接,而不是多个连接,以减少开销。
  84。基准查询,包括使用服务器上的负载,有时一个简单的查询可以影响其他查询。
  85。当负载增加您的服务器上,使用SHOW PROCESSLIST查看慢的和有问题的查询。
  86。在开发环境中产生的镜像数据中 测试的所有可疑的查询。