标签 Mysql 下的文章

MySQL笔记1体系结构与存储引擎

数据库:物理操作系统文件或其他形势文件类型的集合. (数据文件,存在硬盘/内存等的文件)

实例:MySQL数据库由后台线程以及一个共享内存区组成.(就是你代码里负责CURD操作的家伙)在系统中表现为一个进程.

 

 

根据体系结构图来了解MySQL基本组成。

这是我按照官方的手册画的,英文别看这个的,看原版吧。主要看看中文翻译。

存储引擎是基于表的(每个表都声明引擎了嘛).

InnoDB引擎

设计目标:在线事务处理的应用.

特点:支持事物,行锁设计,外键,读取不会产生锁.

文件后缀 .ibd

聚集的方式存储数据,根据主键顺序存放.

 

 

 

MySQL Explain笔记

原文

一、语法

例如:

二、explain输出解释

1 id

SQL执行的顺利的标识,SQL从大到小的执行。

例如:

很显然这条SQL是从里向外的执行,就是从 <span class="hljs-attr">id</span>=<span class="hljs-number">3</span> 向上执行。

2 select_type

就是 <span class="hljs-keyword">select</span>类型,可以有以下几种

(1)SIMPLE

简单 <span class="hljs-keyword">SELECT</span>(不使用 UNION子查询等)。

例如:

(2)PRIMARY

就是最外层的 <span class="hljs-keyword">select</span>

例如:

(3)UNION

UNION中的第二个或后面的 <span class="hljs-keyword">SELECT</span>语句。

例如

(4)DEPENDENT UNION

UNION中的第二个或后面的 <span class="hljs-keyword">SELECT</span>语句,取决于外面的查询。

(4)UNION RESULT

UNION的结果。

(5)SUBQUERY

子查询中的第一个 <span class="hljs-keyword">SELECT</span>

(6)  DEPENDENT SUBQUERY

子查询中的第一个 <span class="hljs-keyword">SELECT</span>,取决于外面的查询。

(7)DERIVED

派生表的SELECTFROM子句的子查询)。

3 table

显示这一行的数据是关于哪张表的。
有时不是真实的表名字,看到的是 derivedxx是个数字,是第几步执行的结果)。

4 type

这列很重要,显示了连接使用了哪种类别,有无使用索引。
从最好到最差的连接类型为 <span class="hljs-keyword">const</span>eq_reg<span class="hljs-keyword">ref</span>range<span class="hljs-keyword">index</span>ALL

(1)system

这是 <span class="hljs-keyword">const</span>联接类型的一个特例,表仅有一行满足条件。

如下( t3表上的 <span class="hljs-keyword">id</span><span class="hljs-attribute">primary</span> key

(2)const

表最多有一个匹配行,它将在查询开始时被读取。

因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。

<span class="hljs-keyword">const</span>表很快,因为它们只读取一次!

<span class="hljs-keyword">const</span>用于用常数值比较 <span class="hljs-attribute">PRIMARY</span> KEYUNIQUE索引的所有部分时。

在下面的查询中, tbl_name可以用于 <span class="hljs-keyword">const</span>表:

例如:

(3) eq_ref

对于每个来自于前面的表的行组合,从该表中读取一行。

这可能是最好的联接类型,除了 <span class="hljs-keyword">const</span>类型。

它用在一个索引的所有部分被联接使用并且索引是 UNIQUE<span class="hljs-attribute">PRIMARY</span> KEY

eq_ref可以用于使用 = 操作符比较的带索引的列。

比较值可以为 常量或一个使用在该表前面所读取的表的列的表达式。

在下面的例子中,MySQL可以使用 eq_ref联接来处理 ref_tables

例如

(4)ref

对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。

如果联接只使用键的最左边的前缀,或如果键不是 UNIQUE<span class="hljs-attribute">PRIMARY</span> KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用 <span class="hljs-keyword">ref</span>

如果使用的键仅仅匹配少量行,该联接类型是不错的。

<span class="hljs-keyword">ref</span>可以用于使用 =<span class="hljs-section">&lt;=&gt;</span>操作符的带索引的列。

在下面的例子中,MySQL可以使用 <span class="hljs-keyword">ref</span>联接来处理 ref_tables

例如:

(5)  ref_or_null

该联接类型如同 <span class="hljs-keyword">ref</span>,但是添加了MySQL可以专门搜索包含 <span class="hljs-literal">NULL</span>值的行。

在解决子查询中经常使用该联接类型的优化。

在下面的例子中,MySQL可以使用 ref_or_null联接来处理 ref_tables

(6) index_merge

该联接类型表示使用了索引合并优化方法。

在这种情况下, key列包含了使用的索引的清单, key_len包含了使用的索引的最长的关键元素。

例如:

(7) unique_subquery

该类型替换了下面形式的 IN子查询的 <span class="hljs-keyword">ref</span>

unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。

(8)index_subquery

该联接类型类似于 unique_subquery,可以替换 IN子查询。

但只适合下列形式的子查询中的非唯一索引:

(9)range

只检索给定范围的行,使用一个索引来选择行。

key列显示使用了哪个索引。

key_len包含所使用索引的最长关键元素。

在该类型中 <span class="hljs-keyword">ref</span>列为 <span class="hljs-literal">NULL</span>

当使用 =<span class="hljs-section">&lt;&gt;</span>&gt;&gt;=&lt;&lt;=IS <span class="hljs-literal">NULL</span><span class="hljs-section">&lt;=&gt;</span>BETWEEN或者 IN操作符,用常量比较关键字列时,可以使用 range

(10)index

该联接类型与 ALL相同,除了只有索引树被扫描。

这通常比 ALL快,因为索引文件通常比数据文件小。

当查询只使用作为单索引一部分的列时,MySQL可以使用该联接类型。

(11) ALL

对于每个来自于先前的表的行组合,进行完整的表扫描。

如果表是第一个没标记 <span class="hljs-keyword">const</span>的表,这通常不好,并且通常在它情况下很差。

通常可以增加更多的索引而不要使用 ALL,使得行能基于前面的表中的常数值或列值被检索出。

5 possible_keys

possible_keys列指出MySQL能使用哪个索引在该表中找到行。

注意,该列完全独立于 <span class="hljs-keyword">EXPLAIN</span>输出所示的表的次序。

这意味着在 possible_keys中的某些键,实际上不能按生成的表次序使用。

如果该列是 <span class="hljs-literal">NULL</span>,则没有相关的索引。

在这种情况下,可以通过检查 WHERE子句,看是否它引用某些列,或适合索引的列来提高你的查询性能。

如果是这样,创造一个适当的索引并且再次用 <span class="hljs-keyword">EXPLAIN</span>检查查询

6 key

key列显示MySQL实际决定使用的键(索引)。

如果没有选择索引,键是 <span class="hljs-literal">NULL</span>

要想强制 MySQL使用、或忽视 possible_keys列中的索引,在查询中使用 <span class="hljs-attribute">FORCE</span> INDEX<span class="hljs-keyword">USE</span> <span class="hljs-keyword">INDEX</span>或者 <span class="hljs-attribute">IGNORE</span> INDEX

7 key_len

key_len列显示MySQL决定使用的键长度。

如果键是 <span class="hljs-literal">NULL</span>,则长度为 <span class="hljs-literal">NULL</span>

使用的索引的长度。在不损失精确性的情况下,长度越短越好

8 ref

<span class="hljs-keyword">ref</span>列显示使用哪个列,或常数与 key一起从表中选择行。

9 rows

rows列显示MySQL认为它执行查询时必须检查的行数。

10 Extra

该列包含MySQL解决查询的详细信息,下面详细介绍。

(1)Distinct

一旦MYSQL找到了与行相联合匹配的行,就不再搜索了

(2)Not exists

MYSQL优化了 <span class="hljs-attribute">LEFT</span> JOIN,一旦它找到了匹配 <span class="hljs-attribute">LEFT</span> JOIN标准的行,就不再搜索了

(3)Range checked for each

Record(index map:#)

没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。

这是使用索引的最慢的连接之一。

4)Using filesort

看到这个的时候,查询就需要优化了。

MYSQL需要进行额外的步骤,来发现如何对返回的行排序。

它根据连接类型、以及存储排序键值、和匹配条件的全部行的行指针来排序全部行。

(5)Using index

列数据是从仅仅使用了索引中的信息,而没有读取实际的行动的表返回的。

这发生在,对表的全部的请求列都是同一个索引的部分的时候。

(6)Using temporary

看到这个的时候,查询需要优化了。

这里,MYSQL需要创建一个临时表来存储结果。

这通常发生在对不同的列集进行 <span class="hljs-attribute"><span class="hljs-nomarkup">ORDER</span></span> BY上,而不是 <span class="hljs-attribute">GROUP</span> BY上。

(7)Using where
使用了 WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。

如果不想返回表中的全部行,并且连接类型 ALL<span class="hljs-keyword">index</span>,这就会发生,或者是查询有问题。

ubuntu 16.04 下重置 MySQL 5.7 的密码(忘记密码)

编辑mysql的配置文件/etc/mysql/mysql.conf.d/mysqld.cnf,在[mysqld]段下加入一行“skip-grant-tables”

编辑完保存退出.

空密码进mysql

把 /etc/mysql/mysql.conf.d/mysqld.cnf,把你加入的那一行“skip-grant-tables”注释或删除掉。

之后你就可以用新密码登陆了.

完结!

MySQL慢查询

原文

1 概念

MySQL的慢查询,全名是慢查询日志,是MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阀值的语句。

具体环境中,运行时间超过 long_query_time值的SQL语句,则会被记录到慢查询日志中。

long_query_time的默认值为 10,意思是记录运行 10秒以上的语句。

默认情况下,MySQL数据库并不启动慢查询日志,需要手动来设置这个参数。

当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件和数据库表。

官方文档,关于慢查询的日志介绍如下(部分资料,具体参考官方相关链接):

2 参数

MySQL 慢查询的相关参数解释:

slow_query_log:是否开启慢查询日志, 1表示开启, 表示关闭

log-slow-queries :旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件 host_name-slow.log

slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件 host_name-slow.log

long_query_time:慢查询阈值,当查询时间多于设定的阈值时,记录日志。

log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。

log_output:日志存储方式。 log_output='FILE'表示将日志存入文件,默认值是 'FILE'log_output='TABLE'表示将日志存入数据库。

3 配置

3.1 slow_query_log

默认情况下 slow_query_log的值为 OFF,表示慢查询日志是禁用的,可以通过设置 slow_query_log的值来开启,如下所示:

使用 set global slow_query_log=1开启了慢查询日志只对当前数据库生效,MySQL重启后则会失效。

如果要永久生效,就必须修改配置文件 my.cnf(其它系统变量也是如此)。

my.cnf要增加或修改参数 slow_query_logslow_query_log_file,如下所示

然后重启MySQL服务器。

3.2 slow_query_log_file

这个参数用于指定慢查询日志的存放路径,缺省情况是 host_name-slow.log文件,

3.3 long_query_time

开启了慢查询日志后,什么样的SQL才会记录到慢查询日志里面呢?

这个是由参数 long_query_time控制,默认情况下 long_query_time的值为10秒,可以使用命令修改,也可以在my.cnf参数里面修改。

关于运行时间正好等于 long_query_time的情况,并不会被记录下来。

也就是说,在mysql源码里是判断大于 long_query_time,而非大于等于。

从MySQL 5.1开始, long_query_time开始以微秒记录SQL语句运行时间,之前仅用秒为单位记录。

如果记录到表里面,只会记录整数部分,不会记录微秒部分。

如上所示,我修改了变量 long_query_time,但是查询变量 long_query_time的值还是 10,难道没有修改到呢?

注意:使用命令 set global long_query_time=4修改后,需要重新连接或新开一个会话才能看到修改值。

show variables like 'long_query_time'查看是当前会话的变量值。

也可以不用重新连接会话,而是用 show global variables like 'long_query_time';

3.4 log_output

log_output参数指定日志的存储方式。

log_output='FILE'表示将日志存入文件,默认值也是 'FILE'

log_output='TABLE'表示将日志存入数据库,这样日志信息就会被写入到 mysql.slow_log表中。

同时也支持两种日志存储方式,配置的时候以逗号隔开即可,如: log_output='FILE,TABLE'

日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源。

因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件

3.5 log-queries-not-using-indexes

该系统变量指定未使用索引的查询也被记录到慢查询日志中(可选项)。

如果调优的话,建议开启这个选项。

另外,开启了这个参数,其实使用 full index scan的SQL也会被记录到慢查询日志。

3.6 log_slow_admin_statements

这个系统变量表示,是否将慢管理语句例如 ANALYZETABLETABLE等记入慢查询日志。

3.7 Slow_queries

如果你想查询有多少条慢查询记录,可以使用 Slow_queries系统变量。

另外,还有 log_slow_slave_statements --logshort-format 参数,可到MySQL网站了解。

4 mysqldumpslow工具

在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活。

MySQL提供了日志分析工具 mysqldumpslow

查看 mysqldumpslow的帮助信息:

 

比如,得到返回记录集最多的10个SQL。

得到访问次数最多的10个SQL

得到按照时间排序的前10条里面含有左连接的查询语句。

另外建议在使用这些命令时结合 |more 使用 ,否则有可能出现刷屏的情况。

 

MYSQL索引

原文

概述

用来加快查询的技术很多,其中最重要的是索引。通常索引能够快速提高查询速度。如果不适用索引,MYSQL必须从第一条记录开始然后读完整个表直到找出相关的行。表越大,花费的时间越多。但也不全是这样。本文讨论索引是什么以及如何使用索引来改善性能,以及索引可能降低性能的情况。

索引的本质

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。

数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear search),这种复杂度为O(n)的算法在数据量很大时显然是糟糕的,好在计算机科学的发展提供了很多更优秀的查找算法,例如二分查找(binary search)、二叉树查找(binary tree search)等。如果稍微分析一下会发现,每种查找算法都只能应用于特定的数据结构之上,例如二分查找要求被检索数据有序,而二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织),所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

索引的存储分类

索引是在MYSQL的存储引擎层中实现的,而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型。MYSQL目前提供了一下4种索引。

  • B-Tree 索引:最常见的索引类型,大部分引擎都支持B树索引。
  • HASH 索引:只有Memory引擎支持,使用场景简单。
  • R-Tree 索引(空间索引):空间索引是MyISAM的一种特殊索引类型,主要用于地理空间数据类型。
  • Full-text (全文索引):全文索引也是MyISAM的一种特殊索引类型,主要用于全文索引,InnoDB从MYSQL5.6版本提供对全文索引的支持。

Mysql目前不支持函数索引,但是能对列的前面某一部分进行索引,例如标题title字段,可以只取title的前10个字符进行索引,这个特性可以大大缩小索引文件的大小,但前缀索引也有缺点,在排序Order By和分组Group By 操作的时候无法使用。用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。
语法:create index idx_title on film (title(10))

MyISAM、InnoDB引擎、Memory三个常用引擎类型比较

索引 MyISAM引擎 InnoDB引擎 Memory引擎
B-Tree 索引 支持 支持 支持
HASH 索引 不支持 不支持 支持
R-Tree 索引 支持 不支持 不支持
Full-text 索引 不支持 暂不支持 不支持

B-TREE索引类型

  • 普通索引
    这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:
    (1)创建索引: CREATE INDEX 索引名 ON 表名(列名1,列名2,…);
    (2)修改表: ALTER TABLE 表名ADD INDEX 索引名 (列名1,列名2,…);
    (3)创建表时指定索引:CREATE TABLE 表名 ( […], INDEX 索引名 (列名1,列名 2,…) );
  • UNIQUE索引
    表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为unique:
    (1)创建索引:CREATE UNIQUE INDEX 索引名 ON 表名(列的列表);
    (2)修改表:ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表);
    (3)创建表时指定索引:CREATE TABLE 表名( […], UNIQUE 索引名 (列的列表) );
  • 主键:PRIMARY KEY索引
    主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。
    (1)主键一般在创建表的时候指定:“CREATE TABLE 表名( […], PRIMARY KEY (列的列表) ); ”。
    (2)但是,我们也可以通过修改表的方式加入主键:“ALTER TABLE 表名ADD PRIMARY KEY (列的列表); ”。
    每个表只能有一个主键。 (主键相当于聚合索引,是查找最快的索引)
    注:不能用CREATE INDEX语句创建PRIMARY KEY索引

索引的设置语法

一 设置索引

在执行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)

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)

二 删除索引

可利用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索引。

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

三 查看索引

  • 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:更多评注。

索引选择性

一 索引选择原则

1. 较频繁的作为查询条件的字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
3. 更新非常频繁的字段不适合创建索引

当然,并不是存在更新的字段就适合创建索引,从判定策略的用语上也可以看出,是”非常频繁”的字段。到底什么样的更新频率应该算是”非常频繁”呢?每秒?每分钟?还是每小时呢?说实话,还真难定义。很多时候是通过比较同一时间段内被更新的次数和利用该字段作为条件的查询次数来判断的,如果通过该字段的查询并不是很多,可能几个小时或是更长才会执行一次,更新反而比查询更频繁,那这样的字段肯定不适合创建索引。反之,如果我们通过该字段的查询比较频繁,但更新并不是特别多,比如查询几十次或更多才可能会产生一次更新,那我个人觉得更新所带来的附加成本也是可以接受的。

4. 不会出现在 WHERE 子句中的字段不该创建索引

二 索引选择原则细述

  • 性能优化过程中,选择在哪个列上创建索引是最非常重要的。可以考虑使用索引的主要有 两种类型的列:在where子句中出现的列在join子句中出现的列,而不是在SELECT关键字后选择列表的列;
  • 索引列的基数越大,索引的效果越好。例如,存放出生日期的列具有不同的值,很容易区分行,而用来记录性别的列,只有”M”和”F”,则对此进行索引没有多大用处,因此不管搜索哪个值,都会得出大约一半的行,(  见索引选择性注意事项对选择性解释;)
  • 使用短索引,如果对字符串列进行索引,应该指定一个前缀长度,可节省大量索引空间,提升查询速度;

    例如,有一个CHAR(200)列,如果在前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行索引。对前10个或者20个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘IO较少,较短的值比较起来更快。更为重要的是,对于较短的键值,所以高速缓存中的快能容纳更多的键值,因此,MYSQL也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多快的可能性。

  • 利用最左前缀

三 索引选择注意事项

既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引?答案是否定的。因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL在运行时也要消耗资源维护索引,因此索引并不是越多越好。

一般两种情况下不建议建索引:

  1. 表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了;

    至于多少条记录才算多,这个个人有个人的看法,我个人的经验是以2000作为分界线,记录数不超过 2000可以考虑不建索引,超过2000条可以酌情考虑索引。

  2. 索引的选择性较低。所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:

    显然选择性的取值范围为(0, 1],选择性越高的索引价值越大,这是由B+Tree的性质决定的。例如,上文用到的employees.titles表,如果title字段经常被单独查询,是否需要建索引,我们看一下它的选择性:

    title的选择性不足0.0001(精确值为0.00001579),所以实在没有什么必要为其单独建索引。
  3. MySQL只对一下操作符才使用索引: <,<=,=,>,>=,between,in, 以及某些时候的 like(不以通配符%_开头的情形)
  4. 不要过度索引,只保持所需的索引。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。 在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。

四 索引的弊端

索引的益处已经清楚了,但是我们不能只看到这些益处,并认为索引是解决查询优化的圣经,只要发现 查询运行不够快就将 WHERE 子句中的条件全部放在索引中。

确实,索引能够极大地提高数据检索效率,也能够改善排序分组操作的性能,但有不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据。假设在Table ta 中的Column ca 创建了索引 idx_ta_ca,那么任何更新 Column ca 的操作,MySQL在更新表中 Column ca的同时,都须要更新Column ca 的索引数据,调整因为更新带来键值变化的索引信息。而如果没有对 Column ca 进行索引,MySQL要做的仅仅是更新表中 Column ca 的信息。这样,最明显的资源消耗就是增加了更新所带来的 IO 量和调整索引所致的计算量。此外,Column ca 的索引idx_ta_ca须要占用存储空间,而且随着 Table ta 数据量的增加,idx_ta_ca 所占用的空间也会不断增加,所以索引还会带来存储空间资源消耗的增加。

MySQL数据类型笔记

在MySQL中,选择正确的数据类型,对于性能至关重要。一般应该遵循下面两步:(1)确定合适的大类型:数字字符串时间二进制;(2)确定具体的类型:有无符号取值范围变长定长等。
在数据类型设置方面,尽量用更小的数据类型,因为它们通常有更好的性能,花费更少的硬件资源。并且,尽量把字段定义为 NOT NULL,避免使用 NULL

1 数值类型

1.1 说明

类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT           1 字节 (-128, 127) (0, 255) 小整数值
SMALLINT           2 字节 (-32 768, 32 767) (0, 65 535) 大整数值
MEDIUMINT           3 字节 (-8 388 608, 8 388 607) (0, 16 777 215) 大整数值
INT或INTEGER           4 字节 (-2 147 483 648, 2 147 483 647) (0, 4 294 967 295) 大整数值
BIGINT           8 字节 (-9 233 372 036 854 775 808, 9 223 372 036 854 775 807) (0, 18 446 744 073 709 551 615) 极大整数值
FLOAT           4 字节 (-3.402 823 466 E+38, 1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0, (1.175 494 351 E-38, 3.402 823 466 E+38) 单精度
浮点数值
DOUBLE           8 字节 (1.797 693 134 862 315 7 E+308, 2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308) 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308) 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

1.2 优化建议

  1. 如果整形数据没有负数,如ID号,建议指定为 UNSIGNED无符号类型,容量可以扩大一倍。
  2. 建议使用 TINYINT代替 ENUMBITENUMSET
  3. 避免使用整数的显示宽度,也就是说,不要用 INT(10)类似的方法指定字段显示宽度,直接用 INT。关于INT显示宽度
  4. INT UNSIGNED来存储IPv4地址,用 VARBINARY来存储IPv6地址,当然存储之前需要用PHP函数转换。
  5. DECIMAL最适合保存准确度要求高,而且用于计算的数据,比如价格。但是在使用 DECIMAL类型的时候,注意长度设置。
  6. 建议使用整形类型来运算和存储实数,方法是,实数乘以相应的倍数后再操作。
  7. 整数通常是最佳的数据类型,因为它速度快,并且能使用 AUTO_INCREMENT

2 日期和时间

2.1 说明

类型 大小
(字节)
范围 格式 用途
DATE    3 1000-01-01 到 9999-12-31 YYYY-MM-DD 日期值
TIME    3 ‘-838:59:59’ 到 ‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR    1 1901 到 2155 YYYY 年份值
DATETIME    8 1000-01-01 00:00:00 到 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP    8 1970-01-01 00:00:00 到 2037 年某时 YYYYMMDDhhmmss 混合日期和时间值,时间戳

2.2 优化建议

  1. MySQL能存储的最小时间粒度为秒。
  2. 建议用 DATE数据类型来保存日期。MySQL中默认的日期格式是 yyyy-mm-dd
  3. 用MySQL的内建类型 DATETIMEDATETIME来存储时间,而不是使用字符串。
  4. 当数据格式为 TIMESTAMPDATETIME时,可以用 CURRENT_TIMESTAMP作为默认(MySQL5.6以后),MySQL会自动返回记录插入的确切时间。
  5. TIMESTAMP是UTC时间戳,与时区相关。
  6. DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数,与时区无关,你存了什么,读出来就是什么。
  7. 除非有特殊需求,否则建议使用 TIMESTAMP,它比 DATETIME更节约空间。
  8. 有时人们把Unix的时间戳保存为整数值,但是这通常没有任何好处,这种格式处理起来不太方便,我们并不推荐它。

3 字符串

3.1 说明

类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

3.2 优化建议

  1. 字符串的长度相差较大用 VARCHAR;字符串短,且所有值都接近一个长度用 CHAR
  2. CHARVARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用 VARCHAR类型保存,因为可能会导致一些与计算相关的问题。换句话说,可能影响到计算的准确性和完整性。
  3. BINARYVARBINARY存储的是二进制字符串,与字符集无关。
  4. BLOB系列存储二进制字符串,与字符集无关。 TEXT系列存储非二进制字符串,与字符集相关。一般情况下,你可以认为 BLOB是一个更大的 VARBINARYTEXT是一个更大的 VARCHAR
  5. BLOBTEXT都不能有默认值。

4 INT显示宽度

我们经常会使用命令来创建数据表,而且同时会指定一个长度,如下。但是,这里的长度并非是 TINYINT类型存储的最大长度,而是显示的最大长度。

这里表示user表的id字段的类型是 TINYINT,可以存储的最大数值是 255。所以,

在存储数据时,如果存入值小于等于 255,如 200,虽然超过2位,但是没有超出TINYINT类型长度,所以可以正常保存;如果存入值大于 255,如 500,那么MySQL会自动保存为 TINYINT类型的最大值 255

在查询数据时,不管查询结果为何值,都按实际输出。这里TINYINT(2)中 2的作用就是,当需要在查询结果前填充 时,命令中加上 ZEROFILL就可以实现,如:

查询结果如果是 5,那输出就是 05。如果指定 TINYINT(5),那输出就是 00005,其实实际存储的值还是 5,而且存储的数据不会超过 255,只是MySQL输出数据时在前面填充了

在MySQL命令中,字段的类型长度 TINYINT(2)INT(11)不会影响数据的插入,只会在使用 ZEROFILL时有用,让查询结果前填充

提高MySQL性能的7个技巧

原文:7 keys to better MySQL performance
作者:Peter Zaitsev
译者:Peter

译者注: 随着尺寸和负载的增长,MySQL的性能会趋于下降。记住这些诀窍,便可保持MySQL的流畅运行。


id
测量应用程序的方法之一是看性能。而性能的指标之一便是用户体验,通俗的说法就是“用户是否需要等待更长的时间才能得到他们想要的东西”。

这个指标在不同的应用场合而有所改变。对于移动购物应用,响应时间不能超过几秒钟。对于员工的人力资源页面,可能需要多花几秒钟的时间。

有很多关于性能如何影响用户行为的研究:

无论采用何种标准,都必须保持良好的应用性能。否则,用户会抱怨(或者更糟的是,转到不同的应用程序)。影响应用程序性能的因素之一是数据库性能。应用程序、网站和数据库之间的交互对于建立应用程序性能的好坏至关重要。

这种交互的一个核心组件是应用程序如何查询数据库以及数据库如何响应请求。无论如何,MySQL都是最受欢迎的数据库管理系统之一。在生产环境中,越来越多的企业正在转向使用MySQL(和其他开源数据库)作为数据库解决方案。

有许多配置MySQL的方法可以帮助确保数据库对查询作出快速响应,并使应用程序性能降低到最低限度。

以下是帮助优化MySQL数据库性能的一些基本技巧。

优化技巧 #1:学习如何使用 EXPLAIN

使用任何数据库所做的两个最重要的决定是设计应用程序实体之间的关系如何映射到表(数据库模式),以及设计应用程序如何以所需的格式获得所需的数据(查询)。

复杂的应用程序可以有复杂的模式和查询。如果想得到应用程序所需要的性能和扩展性,不能仅仅依靠直觉来理解如何执行查询。

应该学习如何使用EXPLAIN命令,而不是随意的猜测和想象。此命令展示了如何执行查询,并让您了解所期望的性能,以及查询将如何随着数据大小的变化而伸缩。

有许多工具–比如MySQLWorkbench–可以可视化EXPLAIN输出,但仍然需要理解基础知识才能理解它。

EXPLAIN命令提供输出的有两种不同的格式:老式的表格式和更现代的结构化JSON文档,它提供了更多的细节(如下所示):

[/crayon]

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37

应该查看的一个组件是“query cost”。query cost是指MySQL根据查询执行的总开销来考虑这个特定查询的代价,并且基于许多不同的因素。

简单查询的查询开销通常小于1,000。开销在1,000到100,000之间的查询被认为是中等开销的查询,而且如果每秒只运行数百个这样的查询(而不是数万个),通常会比较快。

开销超过100,000的查询可以当作是昂贵的。通常,当您是系统上的单个用户时,这些查询仍会快速运行,但您应该仔细考虑在交互式应用程序中使用此类查询的频率(尤其是随着用户数量的增长)。

当然,这些数字只是性能的一个大概的体现,但它们展示了一般原则。您的系统可能更好地处理查询工作负载,也可能更糟,这取决于其体系结构和配置。

决定查询开销的主要因素是查询是否正确使用索引。EXPLAIN 命令可以告诉您查询是否使用索引(通常是因为索引是如何在数据库中创建的,或者查询本身是如何设计的)。这就是为什么学会使用 EXPLAIN 是如此重要。

优化技巧 #2:创建正确的索引

索引通过减少查询必须扫描的数据库中的数据量来提高查询效率。MySQL中的索引用于加速数据库中的访问,并帮助执行数据库约束(如 UNIQUE和FOREIGN KEY )。

数据库索引很像图书索引。它们被保存在自己的位置,并且包含主数据库中已经存在的信息。它们是指向数据所在位置的参考方法或映射。索引不会更改数据库中的任何数据。它们只是指向数据的位置。

没有完全适用于任何工作负载的索引。而应该始终在系统运行的查询上下文中查看索引。

索引良好的数据库不仅运行得更快,而且即使缺少一个索引也会使数据库慢如蜗牛。使用EXPLAIN(如前所述)查找缺少的索引并添加它们。但是要小心:不要添加你不需要的索引!不必要的索引会降低数据库的速度
(请查看关于MySQL索引最佳实践的介绍)。

优化技巧 #3:拒绝使用默认设置

与任何软件一样,MySQL有许多可配置的设置,可用于修改行为(以及最终的性能)。与任何软件一样,管理员忽略了许多这些可配置的设置,最终在默认模式下使用。

要从MySQL中获得最佳性能,了解可配置的的MySQL设置是非常重要的,更重要的是将它们设置为最适合您的数据库环境。

默认情况下,MySQL用于小规模的开发安装,而不是生产规模。您通常希望配置MySQL以使用所有可用的内存资源,并允许应用程序需要的连接数量。

下面是三个MySQL性能优化设置,您应该始终仔细检查:

innodb_ buffer_ pool_size:缓冲池用于存放缓存数据和索引。这是使用具有大容量RAM的系统作为数据库服务器的主要原因。如果只运行InnoDB存储引擎,通常会将80%的内存分配给缓冲池。如果您正在运行非常复杂的查询,或者有大量的并发数据库连接,或大量的表,可能需要将此值降低一个档次,以便为其他操作分配更多的内存。

在设置InnoDB缓冲池大小时,需要确保不要设置得太大,否则会导致交换。这绝对会影响数据库性能。一种简单的检查方法是查看Percona Monitoring and Management中的系统概述图中的交换活动:


id 
如图所示,有时进行一些交换是可以的。但是,如果看到持续每秒1MB或更多的交换活动,则需要减少缓冲池大小(或其他内存使用)。

如果在第一次访问时没有正确地获得innodb_ Buffer_ pool_ size的值,不用担心。从MySQL5.7开始,便可以动态更改InnoDB缓冲池的大小,而无需重新启动数据库服务器。

innodb_ log_ file_ size:这是单个InnoDB日志文件的大小。默认情况下,InnoDB使用两个值,这样您就可以将这个数字加倍,从而获得InnoDB用于确保事务持久的循环重做日志空间的大小。这也优化了将更改应用到数据库。设置innodb_ log_ file_ size是一个权衡的问题。分配的重做空间越大,对于写密集型工作负载而言,性能就越好,但是如果系统断电或出现其他问题,崩溃恢复的时间就越长。

如何知道MySQL的性能是否受到当前InnoDB日志文件大小的限制?可以通过查看实际使用了多少可用的重做日志空间来判断。最简单的方法是查看Percona Monitor and Management InnoDB Metrics仪表板。在下图中,InnoDB日志文件的大小不够大,因为使用的空间非常接近可用的重做日志空间(由红线表示)。日志文件的大小应该至少比保持系统最佳运行所用的空间大20%。


id 
MAX_ Connections:大型应用程序连接数通常需高于默认值。不同于其它变量,如果没有正确设置它,就不会有性能问题(本身)。相反,如果连接的数量不足以满足您的应用程序的需要,那么您的应用程序将无法连接到数据库(在您的用户看来,这就像是停机时间)。所以正确处理这个变量很重要。

如果在多个服务器上运行多个组件的复杂应用程序,很难知道需要多少连接。幸运的是,MySQL可以很容易地看到在峰值操作时使用了多少连接。通常,您希望确保应用程序使用的最大连接数与可用的最大连接数之间至少有30%的差距。查看这些数字的一种简单方法是在Percona监控和管理的MySQL概述仪表板中使用MySQL连接图。下图显示了一个健全的系统,其中有大量的附加连接可用。


id 
需要记住的一点是,如果数据库运行缓慢,应用程序通常会创建过多的连接。在这种情况下,您应该处理数据库的性能问题,而不是简单地允许更多的连接。更多的连接会使底层的性能问题变得更糟。

(注意:当将max_Connections变量设置为明显高于默认值时,通常需要考虑增加其他参数,如表缓存的大小和打开的MySQL文件的数量。但是,这不属于本文讨论的范畴。)

优化技巧 #4:将数据库保存在内存中

近年来,我们看到了向固态磁盘(SSD)的过渡。尽管SSD比旋转硬盘快得多,但它们仍然无法与RAM中的数据相比。这种差异不仅来自存储性能本身,还来自数据库在从磁盘或SSD存储中检索数据时必须做的额外工作。

随着最新硬件的改进,无论是在云端运行还是管理自己的硬件,都越来越有可能将数据库存储在内存中。

更好的消息是,您不需要将所有数据库都放入内存中,就可以获得内存中的大部分性能优势。您只需将工作数据(最频繁访问的数据)集存入内存中。

你可能已经看到一些文章提供了一些具体的数字,说明应该将数据库的哪个部分保存在内存中,从10%到33%不等。事实上,没有“一刀切”的数字。适合内存的最佳性能优势的数据量与工作负载相关。与其寻找一个特定的“万能”数字,不如检查一下数据库在其稳定状态下运行的I/O(通常在启动后几个小时)。看看READ,因为如果数据库在内存中,则可以完全消除READ。写总是需要发生的,不管你有多少内存可用。

下面,您可以在Percona监控和管理的InnoDBMetrics仪表板中的 InnoDB I/O图中看到 I/O。


 
在上面的图表中,您可以看到高达每秒2,000个I/O操作的峰值,这表明(至少对于工作负载的某些部分)数据库工作集不适合内存。

优化技巧 #5:使用SSD存储

如果您的数据库不适合内存(即使不适合),您仍然需要快速存储来处理写操作,并在数据库升温时(重新启动后)避免性能问题。如今,SSD即是快速存储的代名词。

出于成本或可靠性的原因,一些“专家”仍然主张使用旋转磁盘(机械磁盘)。坦率地说,当涉及到操作数据库时,这些论点往往已经过时或完全错误。今天,SSD以较高的价格提供着可观的性能和可靠性。

然而,并非所有SSD都是适用的。对于数据库服务器,您应该使用为服务器工作负载设计的SSD,这种SSD会对数据起到保护作用(例如,在断电期间)。避免使用为台式计算机和笔记本电脑设计的商用SSD。

通过NVMe或Intel OpTan技术连接的SSD可提供最佳性能。即使作为SAN、NAS或cloud block设备远程连接,与旋转磁盘相比,SSD仍然具有更优越的性能。

优化技巧 #6:横向扩展

即使是高性能的服务器也有其局限性。有两种扩展方式:up和out。纵向扩展意味着购买更多的硬件。这可能很昂贵,而且硬件很快就会过时。横向扩展以处理更多的负载有几个好处:

      1.可以利用较小且成本较低的系统。
      2.通过横向扩展,进行线性扩展更快更容易。
      3.因为数据库分布在多台物理机器上,所以数据库不会受到单个硬件故障点的影响。

虽然横向扩展是有好处的,但也有一定的局限性。扩展需要复制,例如基本的MySQL复制或Percona XtraDB Cluster,以实现数据同步。但是作为回报,可以获得额外的性能和高可用性。如果您需要更大的扩展,请使用MySQL分片。

您还需要确保连接到集群体系结构的应用程序能够找到所需的数据–通常通过一些代理服务器和负载平衡器(如ProxySQLHAProxy)。

在计划横向扩展时,避免过早地扩展。使用分布式数据库往往更复杂。现代硬件和MySQL服务器只使用一台服务器就可以得到良好的体验。最近发布的MySQL 8候选版本表明,它能够在单个系统上处理200多万个简单查询。

优化技巧 #7:可观测性

设计最好的系统时要考虑到可观察性-MySQL也不例外.。

一旦您启动、运行并正确调整了MySQL环境,就不能仅仅设置而不进行管理。数据库环境会受到系统或工作负载更改的影响。准备好应对诸如流量高峰、应用程序错误和MySQL故障等意外。这些事情能够而且将会发生。

当发生问题时,你需要迅速而有效地解决它们。这样做的唯一方法是设置某种监视解决方案并对其进行适当的初始化。这使您能够在数据库环境在生产中运行时看到它正在发生的情况,并在出现问题时分析服务器数据。理想情况下,系统允许您在问题发生之前或在问题发展到用户可以看到其影响之前进行预防。

监控工具有诸如MySQL Enterprise Monitor、Monyog和 Percona Monitoring and Management (PMM),后者具有免费和开源的额外优势。这些工具为监视和故障排除提供了很好的可操作性。

随着越来越多的公司转向开源数据库(特别是MySQL),以便在大规模生产环境中管理和服务其业务数据,他们将需要集中精力保持这些数据库的优化和最佳运行效率。与所有对您的业务目标至关重要的事情一样,您的数据库性能可能会导致或破坏你的业务目标或成果。MySQL是一个可以为应用程序和网站提供优质的数据库解决方案,但需要进行调整以满足您的需要,并进行监视以发现和防止瓶颈和性能问题。

PeterZaitsev是Percona的联合创始人和首席执行官,Percona时企业级MySQL和MongoDB解决方案和服务的提供商。由O‘Reilly出版的《High Performance MySQL》是最受欢迎的MySQL性能书籍之一。Zaitsev经常在PerconaDatabasePerformanceBlog.com上发表博客,并在世界各地的会议中发言。

浅谈MySQL主从复制的高可用解决方案

1、熟悉几个组件(部分摘自网络)
1.1、drbd
—— DRBD(Distributed Replicated Block Device),DRBD号称是 “网络 RAID”,开源软件,由 LINBIT 公司开发。DRBD 实际上是一种块设备的实现,主要被用于Linux平台下的高可用(HA)方案之中。他是有内核 模块和相关程序而组成,通过网络通信来同步镜像整个设备,有点类似于一个网络RAID的功能。也就是说当你将数据写入本地的DRBD设备上的文件系统 时, 数据会同时被发送到网络中的另外一台主机之上,并以完全相同的形式记录在一个文件系统中(实际上文件系统的创建也是由DRBD的同步来实现的)。本地节点 (主机)与远程节点(主机)的数据可以保证实时的同步,并保证IO的一致性。所以当本地节点的主机出现故障时,远程节点的主机上还会保留有一份完全相同的 数据,可以继续使用,以达到高可用的目的。
实际使用场景中,可以作用于mysql-master(主备)的服务器,保证数据的一致性。

1.2、hearbeat
—— 它是Linux-HA工程的一个组成部分,它实现了一个高可用集群系统。心跳服务和集群通信是高可用集群的两个关键组件,在 Heartbeat 项目里,由 heartbeat 模块实现了这两个功能。它实现IP的自动漂移,即当一台服务器宕机后,浮动IP(整个cluster的对外IP)自动漂移到另外一台服务器,不会引起服务 中断。工作原理:heartbeat (Linux-HA)的工作原理:heartbeat最核心的包括两个部分,心跳监测部分和资源接管部分,心跳监测可以通过网络链路和串口进行,而且支持 冗 余链路,它们之间相互发送报文来告诉对方自己当前的状态,如果在指定的时间内未收到对方发送的报文,那么就认为对方失效,这时需启动资源接管模块来接管运 行在对方主机上的资源或者服务。

1.3、MMM (MySQL Master-Master Replication Manager)
—— MMM利用了虚拟IP的技术:1个网卡可以同时使用多个IP。(所以使用MMM时,需要2*n+1个IP,n为mysql数据库结点个数,包括 master,slave)。当有数据库结点fail时,mmmd_mon检测不到mmmd_agent的心跳或者对应的MySQL服务器的状 态,mmmd_mon将进行决定,并下指令给某个正常的数据库结点的mmmd_agent,使得该mmmd_agent“篡位”使用(注)刚才fail的 那个结点的虚拟IP,使得虚拟IP实际从指向fail的那个机器自动转为此时的这个正常机器。
具体参考:http://liuyu.blog.51cto.com/183345/98867/http://blog.longwin.com.tw/2008/10/mysql-master-replication-manager-mmm-intro-2008/

1.4、mysql的主从复制(Replication)/同步
—— 做mysql集群的基本都会选择这个策略。工作方式:MySQL支持单向、异步复制,复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服 务器。主服务器将更新写入二进制日志文件,并维护日志文件的一个索引以跟踪日志循环。当一个从服务器连接到主服务器时,它通知主服务器从服务器在日志中读 取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知下一次更新。详细内容请自行google搜索。

2、mysql的集群
mysql通过主从复制可以很简单的搭建一个集群服务器,并且配置也很简单。主服务器开启二进制日志系统(bin-log),设置可以复制的权限用户等,从服务器设置主服务器的serverid,host等即可实现同步(详细步骤参考http://my.oschina.net/guol/blog/100179) 。简单的示意图如下:

 浅谈MySQL主从复制的高可用解决方案

问题:当主服务器宕机后,整个集群瘫痪(单点故障)。

 

3、构建mysql高可用集群方案
基于上面的问题,目前可有以下几种方案供选择:
3.1、master-master架构
两台服务器装mysql,各自作为对方的从机接受对方发来的数据,做到数据的同步备份,感觉和master-slave基本实现原理是一样的。这样保证了 数据的一致性,如何保证其中一台服务器故障,自动切换到另外的一个master上呢,使用MMM(MySQL Master-Master Replication Manager)来管理。
详细配置参考:http://liuyu.blog.51cto.com/183345/98867/

3.2、heartbeat+drbd+mysql主从复制
基本原理与3.1相似,这里需要做一个master库的冗余备份,使用drbd来保证不同服务器中两个master库的数据一致性。利用heartbeat来完成其中一台服务器发生故障后的自动切换。结构如下图:

 浅谈MySQL主从复制的高可用解决方案
据了解,一般大型网站日pv达到1-2000w以上,都会在主备mysql上层加上一个负载均衡器,如:LVS或者硬件产品F5、Array等。 考虑到成本一般用Lvs/DR+keepalived或hearbeat来完成负载。

浅谈php web安全

概要

1、php一些安全配置

(1)关闭php提示错误功能

(2)关闭一些“坏功能”

(3)严格配置文件权限。

2、严格的数据验证,你的用户不全是“好”人

2.1为了确保程序的安全性,健壮性,数据验证应该包括内容。

2.2程序员容易漏掉point或者说需要注意的事项

3、防注入

3.1简单判断是否有注入漏洞以及原理

3.2常见的mysql注入语句

(1)不用用户名和密码

(2)在不输入密码的情况下,利用某用户

(3)猜解某用户密码

(4)插入数据时提权

(5)更新提权和插入提权同理

(6)恶意更新和删除

(7)union、join等

(8)通配符号%、_

(9)还有很多猜测表信息的注入sql

3.3防注入的一些方法

2.3.1 php可用于防注入的一些函数和注意事项。

2.3.2防注入字符优先级。

2.3.3防注入代码

(1)参数是数字直接用intval()函数

(2)对于非文本参数的过滤

(3)文本数据防注入代码。

(4)当然还有其他与addslashes、mysql_escape_string结合的代码。

4、防止xss攻击

4.1Xss攻击过程

4.2常见xss攻击地方

4.3防XSS方法

5、CSRF

5.1简单说明CSRF原理

5.2防范方法

6、防盗链

7、防拒CC攻击

———————————————————————

1、php一些安全配置

(1)关闭php提示错误功能

在php.ini 中把display_errors改成

  1. display_errors = OFF

或在php文件前加入

  1. error_reporting(0)

1)使用error_reporting(0);失败的例子:

A文件代码:

  1. <?
  2. error_reporting(0);
  3. echo 555
  4. echo 444;
  5. ?>

错误:

Parse error: parse error, expecting `’,” or `’;” in E:\webphp\2.php on line 4

2)使用error_reporting(0);成功的例子:

a文件代码:

  1. <?php
  2. error_reporting(0);
  3. include(“b.php”);
  4. ?>

b文件代码:

  1. <?php
  2. echo 555
  3. echo 444;
  4. ?>

 

这是很多phper说用error_reporting(0)不起作用。第一个例子A.php里面有致命错误,导致不能执行,不能执行服务器则不知有这个功能,所以一样报错。

第二个例子中a.php成功执行,那么服务器知道有抑制错误功能,所以就算b.php有错误也抑制了。

ps:抑制不了mysql错误。

(2)关闭一些“坏功能”

1)关闭magic quotes功能

在php.ini 把magic_quotes_gpc = OFF

避免和addslashes等重复转义

2)关闭register_globals = Off

在php.ini 把register_globals = OFF

在register_globals = ON的情况下

地址栏目:http:www.phpben.com?bloger=benwin

  1. <?php
  2. //$bloger = $_GET[‘bloger’]   //因为register_globals = ON 所以这步不用了直接可以用$bloger
  3.   echo $bloger;
  4. ?>

  这种情况下会导致一些未初始化的变量很容易被修改,这也许是致命的。所以把register_globals = OFF关掉

(3)严格配置文件权限。

为相应文件夹分配权限,比如包含上传图片的文件不能有执行权限,只能读取

2、严格的数据验证,你的用户不全是“好”人

记得笔者和一个朋友在讨论数据验证的时候,他说了一句话:你不要把你用户个个都想得那么坏!但笔者想说的这个问题不该出现在我们开发情景中,我们要做的是严格验证控制数据流,哪怕10000万用户中有一个是坏用户也足以致命,再说好的用户也有时在数据input框无意输入中文的时,他已经不经意变“坏”了。

2.1为了确保程序的安全性,健壮性,数据验证应该包括

(1)     关键数据是否存在。如删除数据id是否存在

(2)     数据类型是否正确。如删除数据id是否是整数

(3)     数据长度。如字段是char(10)类型则要strlen判断数据长度

(4)     数据是否有危险字符

数据验证有些人主张是把功能完成后再慢慢去写安全验证,也有些是边开发边写验证。笔者偏向后者,这两种笔者都试过,然后发现后者写的验证相对健壮些,主要原因是刚开发时想到的安全问题比较齐全,等开发完功能再写时有两个问题,一个phper急于完成指标草草完事,二是确实漏掉某些point。

2.2程序员容易漏掉point或者说需要注意的事项:

(1)     进库数据一定要安全验证,笔者在广州某家公司参与一个公司内部系统开发的时候,见过直接把$_POST数据传给类函数classFunctionName($_POST),理由竟然是公司内部使用的,不用那么严格。暂且不说逻辑操作与数据操控耦合高低问题,连判断都没判断的操作是致命的。安全验证必须,没任何理由推脱。

(2)     数据长度问题,如数据库建表字段char(25),大多phper考虑到是否为空、数据类型是否正确,却忽略字符长度,忽略还好更多是懒于再去判断长度。(这个更多出现在新手当中,笔者曾经也有这样的思想)

(3)     以为前端用js判断验证过了,后台不需要判断验证。这也是致命,要知道伪造一个表单就几分钟的事,js判断只是为了减少用户提交次数从而提高用户体验、减少http请求减少服务器压力,在安全情况下不能防“小人”,当然如果合法用户在js验证控制下是完美的,但作为phper我们不能只有js验证而抛弃再一次安全验证。

(4)     缺少对表单某些属性比如select、checkbox、radio、button等的验证,这些属性在web页面上开发者已经设置定其值和值域(白名单值),这些属性值在js验证方面一般不会验证,因为合法用户只有选择权没修改权,然后phper就在后端接受数据处理验证数据的时候不会验证这些数据,这是一个惯性思维,安全问题也就有了,小人一个伪表单。

(5)     表单相应元素name和数据表的字段名一致,如用户表用户名的字段是user_name,然后表单中的用户名输入框也是user_name,这和暴库没什么区别。

(6)     过滤危险字符方面如防注入下面会独立讲解。

 

3、防注入

3.1简单判断是否有注入漏洞以及原理。

网址:http:www.phpben.com/benwin.php?id=1 运行正常,sql语句如:select  *  from phpben where id = 1

(1) 网址:http:www.phpben.com/ benwin.php?id=1’   sql语句如:select  *  from phpben where id = 1’  然后运行异常 这能说明benwin.php文件没有对id的值进行“’” 过滤和intval()整形转换,当然想知道有没有对其他字符如“%”,“/*”等都可以用类似的方法穷举测试(很多测试软件使用)

(2)网址:http:www.phpben.com/ benwin.php?id=1 and 1=1  则sql语句可能是 select  *  from phpben where id = 1 and 1=1,运行正常且结果和http:www.phpben.com/benwin.php?id=1结果一样,则说明benwin.php可能没有对空格“ ”、和“and”过滤(这里是可能,所以要看下一点)

(3)网址:http:www.phpben.com/ benwin.php?id=1 and 1=2则sql语句可能是 select  *  from phpben where id = 1 and 1=2 如果运行结果异常说明sql语句中“and 1=2”起作用,所以能3个条件都满足都则很确定的benwin.php存在注入漏洞。

ps:这里用get方法验证,post也可以,只要把值按上面的输入,可以一一验证。

这说明

3.2常见的mysql注入语句。

(1)不用用户名和密码

  1. //正常语句
  2. $sql =”select * from phpben where user_name=’admin’ and pwd =’123′”;
  3. //在用户名框输入’or’=’or’或 ’or 1=’1 然后sql如下
  4. $sql =”select * from phpben where user_name=’ ‘or’=’or” and pwd =” “;
  5. $sql =”select * from phpben where user_name=’ ‘or 1=’1’ and pwd =” “;

这样不用输入密码。话说笔者见到登录框都有尝试的冲动。

 

(2)在不输入密码的情况下,利用某用户。

  1. //正常语句
  2. $sql =”select * from phpben where user_name=’$username’ and pwd =’$pwd'”;
  3. //利用的用户名是benwin 则用户名框输入benwin’#  密码有无都可,则$sql变成
  4. $sql =”select * from phpben where user_name=’ benwin’#’ and pwd =’$pwd'”;

这是因为mysql中其中的一个注悉是“#”,上面语句中#已经把后面的内容给注悉掉,所以密码可以不输入或任意输入。网上有些人介绍说用“/*”来注悉,笔者想提的是只有开始注悉没结束注悉“*/”时,mysql会报错,也不是说“/**/”不能注悉,而是这里很难添加上“*/”来结束注悉,还有“– ”也是可以注悉mysql 但要注意“–”后至少有一个空格也就是“– ”,当然防注入代码要把三种都考虑进来,值得一提的是很多防注入代码中没把“– ”考虑进防注入范围。

 

(3)猜解某用户密码

  1. //正常语句
  2. $sql =”select * from phpben.com where user_name=’$username’ and pwd =’$pwd'”;
  3. //在密码输入框中输入“benwin’ and left(pwd,1)=’p’#”,则$sql是
  4. $sql =”select * from phpben.com where user_name=’ benwin’ and left(pwd,1)=’p’#’ and pwd =’$pwd'”;

如果运行正常则密码的密码第一个字符是p,同理猜解剩下字符。

(4)插入数据时提权

  1. //正常语句,等级为1
  2. $sql = “insert into phpben.com (`user_name`,`pwd`,`level`) values(‘benwin’,’iampwd’,1) “;
  3. //通过修改密码字符串把语句变成
  4. $sql = “insert into phpben.com (`user_name`,`pwd`,`level`) values(‘benwin’,’iampwd’,5)#’,1) “;
  5. $sql = “insert into phpben.com (`user_name`,`pwd`,`level`) values(‘benwin’,’iampwd’,5)–  ‘,1) “;这样就把一个权限为1的用户提权到等级5

(5)更新提权和插入提权同理

  1. //正常语句
  2. $sql = “update phpben set  `user_name` =’benwin’, level=1”;
  3. //通过输入用户名值最终得到的$sql
  4. $sql = “update phpben set  `user_name` =’benwin’,level=5#’, level=1”;
  5. $sql = “update phpben set  `user_name` =’benwin’,level=5–  ‘, level=1”;

(6)恶意更新和删除

  1. //正常语句
  2. $sql = “update phpben set `user_name` = ‘benwin’ where id =1”;
  3. //注入后,恶意代码是“1 or id>0”
  4. $sql = “update phpben set `user_name` = ‘benwin’ where id =1 or id>0”;
  5. //正常语句
  6. $sql = “update phpben set  `user_name` =’benwin’ where id=1”;
  7. //注入后
  8. $sql = “update phpben set  `user_name` =’benwin’ where id>0#’ where id=1”;
  9. $sql = “update phpben set  `user_name` =’benwin’ where id>0– ‘ where id=1”;

(7)union、join等

  1. //正常语句
  2. $sql =”select * from phpben1 where `user_name`=’benwin’ “;
  3. //注入后
  4. $sql =”select * from phpben1 where`user_name`=’benwin’ uninon select * from phpben2#’ “;
  5. $sql =”select * from phpben1 where`user_name`=’benwin’ left join……#’ “;

(8)通配符号%、_

  1. //正常语句
  2. $sql =”select * from phpben where `user_name`=’benwin’ “;
  3. //注入通配符号%匹配多个字符,而一个_匹配一个字符,如__则匹配两个字符
  4. $sql =”select * from phpben where `user_name` like ’%b’ “;
  5. $sql =”select * from phpben where `user_name` like ’_b_’ “;

这样只要有一个用户名字是b开头的都能正常运行,“ _b_”是匹配三个字符,且这三个字符中间一个字符时b。这也是为什么有关addslashes()函数介绍时提示注意没有转义%和_(其实这个是很多phper不知问什么要过滤%和_下划线,只是一味的跟着网上代码走)

(9)还有很多猜测表信息的注入sql

  1. //正常语句
  2. $sql =”select * from phpben1 where`user_name`=’benwin'”;
  3. //猜表名,运行正常则说明存在phpben2表
  4. $sql =”select * from phpben1 where`user_name`=’benwin’ and (select count(*) from phpben2 )>0#’ “;
  5. //猜表字段,运行正常则说明phpben2表中有字段colum1
  6. $sql =”select * from phpben1 where`user_name`=’benwin’ and (select count(colum1) from phpben2 )>0#'”;
  7. //猜字段值
  8. $sql =”select * from phpben1 where`user_name`=’benwin’ and left(pwd,1)=’p’#’'”;

当然还有很多,笔者也没研究到专业人士那种水平,这里提出这些都是比较常见的,也是phper应该知道并掌握的,而不是一味的在网上复制粘贴一些防注入代码,知然而不解其然。

下面一些防注入方法回看可能更容易理解。

3.3防注入的一些方法

3.3.1 php可用于防注入的一些函数和注意事项。

(1)addslashes 和stripslashes。

Addslashes给这些 “’”、“””、“\”,“NULL” 添加斜杆“\’”、“\””、“\\”,“\NULL”, stripslashes则相反,这里要注意的是php.ini是否开启了magic_quotes_gpc=ON,开启若使用addslashes会出现重复。所以使用的时候要先get_magic_quotes_gpc()检查

一般代码类似:

  1. if(!get_magic_quotes_gpc())
  2. {
  3.          $abc = addslashes($abc);
  4. }

其实这个稍微学习php一下的人都知道了,只不过笔者想系统点介绍(前面都说不是专家级文章),所以也顺便写上了。addslashes

(2)mysql_escape_string()和mysql_ real _escape_string()

mysql_real_escape_string 必须在(PHP 4 >= 4.3.0, PHP 5)的情况下才能使用。否则只能用 mysql_escape_string

  1. if (PHP_VERSION >= ‘4.3’)
  2. {
  3. $string  =  mysql_real_escape_string($string);
  4. }else
  5. {
  6. $string  =  mysql_escape_string($string );
  7. }

mysql_escape_string()和mysql_ real _escape_string()却别在于后者会判断当前数据库连接字符集,换句话说在没有连接数据库的前提下会出现类似错误:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user ‘ODBC’@’localhost’ (using password: NO) in E:\webphp\test.php on line 11

 

(3)字符代替函数和匹配函数

str_replace() 、perg_replace()这些函数之所以也在这里提是因为这些函数可以用于过滤或替代一些敏感、致命的字符。

3.3.2防注入字符优先级。

防注入则要先知道有哪些注入字符或关键字,常见的mysql注入字符有字符界定符号如“’”、“””;逻辑关键字如“and”、“or”;mysql注悉字符如“#”,“– ”,“/**/”;mysql通配符“%”,“_”;mysql关键字“select|insert|update|delete|*|union|join|into|load_file|outfile”

(1)对于一些有规定格式的参数来说,防注入优先级最高的是空格” ”。

如一些银行卡号,身份证号,邮箱,电话号码,,生日,邮政编码等这些有自己规定的格式且格式规定不能有空格符号的参数,在过滤的时候一般最先过滤掉空格(包括一些空格“变种”),因为其他字符界定符号,逻辑关键字,mysql注悉,注意下图可以看出重要的是“’”,“ ”

ps:空格字符的变种有:“%20”,“\n”,“\r”,“\r\n”,“\n\r”,“chr(“32”)” 这也是为什么mysql_escape_string()和mysql_real_escape_string() 两个函数转义“\n”,“\r”。其实很多phper只知道转义\n,\r而不知原因,在mysql解析\n,\r时把它们当成空格处理,笔者测试验证过,这里就不贴代码了。

 

(2)“and”,“or”,“\”,“#”,“– ”

逻辑关键可以组合很多注入代码;mysql注悉则把固有sql代码后面的字符全部给注悉掉从而让注入后的sql语句能正常运行;“\”也是能组合很多注入字符\x00,\x1a。

ps:sql解析“#”,“– ”是大多数mysql防注入代码没有考虑到的,也是很多phper忽略。还有因为一些phper给参数赋值的时候会有用“-”来隔开,所以笔者建议不要这样写参数,当然也可以再过滤参数的时候“– ”(注意有空格的,没空格不解析为注悉)当一个整体过滤而不是过滤“-” ,这样就避免过多过滤参数。

 

(3)“null”,“%”,“_”

这几个不能独立,都不要在特定情况下,比如通配字符“%,_”都要在mysql like子句的前提下。所以“%”,“_”的过滤一般在搜索相关才过滤,不能把它们纳入通常过滤队列,因为有些如邮箱就可以有”_”字符

 

(4)关键字“select|insert|update|delete|*|union|join|into|load_file|outfile”

也许你会问怎么这些重要关键字却优先级这么低。笔者想说的是因为这些关键字在没有“’”,“””,“ ”,“and”,“or”等情况下购不成伤害。换句话说这些关键字不够“独立”,“依赖性”特别大。当然优先级低,不代表不要过滤。

 

3.3.3防注入代码。

(1)参数是数字直接用intval()函数

注意:现在很多网上流行的防注入代码都只是只是用addslashes()、mysql_escape_string()、mysql_real_escape_string()或三者任意组合过滤,但phper以为过滤了,一不小心一样有漏洞,那就是在参数为数字的时候:

  1. $id = addslashes($_POST[‘id’]); //正确是$id = intval($_POST[‘id’]);
  2. $sql =” select * from phpben.com where id =$id”;
  3. $sql =” select * from phpben.com where id =1 or 1=1″;

对比容易发现,post过来的数据通过addslashes过滤后的确很多注入已经不起作用,但是$id并没有intval,导致漏洞的存在,这是个小细节,不小心则导致漏洞。

(2)对于非文本参数的过滤

文本参数是指标题、留言、内容等可能有“’”,“’”等内容,过滤时不可能全部转义或代替。

但非文本数据可以。

  1. function _str_replace($str )
  2. {
  3.      $str = str_replace(” “,””,$str);
  4.      $str = str_replace(“\n”,””,$str);
  5.      $str = str_replace(“\r”,””,$str);
  6.      $str = str_replace(“‘”,””,$str);
  7.      $str = str_replace(‘”‘,””,$str);
  8.      $str = str_replace(“or”,””,$str);
  9.      $str = str_replace(“and”,””,$str);
  10.      $str = str_replace(“#”,””,$str);
  11.      $str = str_replace(“\\”,””,$str);
  12.      $str = str_replace(“– “,””,$str);
  13.      $str = str_replace(“null”,””,$str);
  14.      $str = str_replace(“%”,””,$str);
  15.      //$str = str_replace(“_”,””,$str);
  16.      $str = str_replace(“>”,””,$str);
  17.      $str = str_replace(“<“,””,$str);
  18.      $str = str_replace(“=”,””,$str);
  19.      $str = str_replace(“char”,””,$str);
  20.      $str = str_replace(“declare”,””,$str);
  21.      $str = str_replace(“select”,””,$str);
  22.      $str = str_replace(“create”,””,$str);
  23.      $str = str_replace(“delete”,””,$str);
  24.      $str = str_replace(“insert”,””,$str);
  25.      $str = str_replace(“execute”,””,$str);
  26.      $str = str_replace(“update”,””,$str);
  27.      $str = str_replace(“count”,””,$str);
  28.      return $str;
  29. }

ps

还有一些从列表页操作过来的一般href是”phpben.php?action=delete&id=1”,这时候就注意啦,_str_replace($_GET[‘action’])会把参数过滤掉,笔者一般不用敏感关键作为参数,比如delete会写成del,update写成edite,只要不影响可读性即可;

还有上面代码过滤下划线的笔者注悉掉了,因为有些参数可以使用下划线,自己权衡怎么过滤;

有些代码把关键字当重点过滤对象,其实关键字的str_replace很容易“蒙过关”,str_replace(“ininsertsert”)过滤后的字符还是insert,所以关键的是其他字符而不是mysql关键字。

 

(3)文本数据防注入代码。

文本参数是指标题、留言、内容等这些数据不可能也用str_replace()过滤掉,这样就导致数据的完整性,这是很不可取的。

代码:

  1. function no_inject($str)
  2. {
  3.          if(is_array($str))
  4.          {
  5.                    foreach($str as $key =>$val)
  6.                    {
  7.                            $str[$key]=no_inject($val);
  8.                    }
  9.          }else
  10.          {
  11.                    $str = str_replace(” “,” “,$str);
  12.                    $str = str_replace(“\\”,”\”,$str);
  13.                    $str = str_replace(“‘”,”‘”,$str);
  14.                    $str = str_replace(‘”‘,”””,$str);
  15.                    $str = str_replace(“or”,”or”,$str);
  16.                    $str = str_replace(“and”,”and”,$str);
  17.                    $str = str_replace(“#”,”#”,$str);
  18.                    $str = str_replace(“– “,”– “,$str);
  19.                    $str = str_replace(“null”,”null”,$str);
  20.                    $str = str_replace(“%”,”%”,$str);
  21.                    //$str = str_replace(“_”,””,$str);
  22.                    $str = str_replace(“>”,”>”,$str);
  23.                    $str = str_replace(“<“,”<“,$str);
  24.                    $str = str_replace(“=”,”=”,$str);
  25.                    $str = str_replace(“char”,”char”,$str);
  26.                    $str = str_replace(“declare”,”declare”,$str);
  27.                    $str = str_replace(“select”,”select”,$str);
  28.                   $str = str_replace(“create”,”create”,$str);
  29.                   $str = str_replace(“delete”,”delete”,$str);
  30.                   $str = str_replace(“insert”,”insert”,$str);
  31.                  $str = str_replace(“execute”,”execute”,$str);
  32.                  $str = str_replace(“update”,”update”,$str);
  33.                  $str = str_replace(“count”,”count”,$str);
  34.          }
  35.     return $str;
  36. }

(4)当然还有其他与addslashes、mysql_escape_string结合的代码。

 

防注入的代码其实来来去去都是那些组合,然后根据自己程序代码变通,笔者这些代码也是没考虑全的,不如cookes、session、request都没全过滤。重要是知道其中原理,为什么过滤这些字符,字符有什么危害。当然还有一些笔者没考虑也没能力考虑到的方面比如还有哪些关键字之类,欢迎mailto:chen_bin_wen@163.com/445235728@qq.com

 

4、防止xss攻击

XSS:cross site script 跨站脚本,为什么不叫css,为了不和div+css混淆。

4.1Xss攻击过程:

(1)发现A站有xss漏洞。

(2)注入xss漏洞代码。可以js代码,木马,脚本文件等等,这里假如A站的benwin.php这个文件有漏洞。

(3)通过一些方法欺骗A站相关人员运行benwin.php,其中利用相关人员一些会员信息如cookies,权限等。

相关人员:

管理员(如贴吧版主),管理员一般有一定权限。目的是借用管理员的权限或进行提权,添或加管理员,或添加后门,或上传木马,或进一步渗透等相关操作。

A站会员:会员运行A站的benwin.php。目的一般是偷取会员在A站的信息资料。

方法:

1)       在A站发诱骗相关人到benwin.php的信息,比如网址,这种是本地诱骗

2)       在其他网站发诱骗信息或者发邮件等等信息。

一般通过伪装网址骗取A站相关人员点击进benwin.php

(4)第三步一般已经是一次xss攻击,如果要更进一步攻击,那不断重复执行(2)、(3)步以达到目的。

简单例说xss攻击

代码:

benwin.php文件

  1. <html>
  2. <head>
  3. <title>简单xss攻击例子</title></head>
  4. <meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″>
  5. <dody>
  6. <form action=”phpben.com?user_name=<?php echo $user_name; ?>”>
  7. <input type=”submit” value=”提交” >
  8. </form>
  9. </body>
  10. </html>

当用户名$user_name的值是“benwin” onSubmit=”alert(‘这是xss攻击的例子’);” class= “”(这里)

  1. <form action=”phpben.com?user_name=benwin” onSubmit=”alert(‘这是xss攻击的例子’);” class= “” >
  2. <input type=”submit” value=”提交” >
  3. </form>

当提交表单的时候就会弹出提示框。

(1)     很明显$user_name在保存进数据库的时候没有过滤xss字符(和防注入很像,这里举例说明)==>发现漏洞

(2)     构造xss代码:benwin” onSubmit=”alert(‘这是xss攻击的例子’);” class= “” 传入数据库

(3)     骗相关人员进来点击“提交”按钮

 

4.2常见xss攻击地方

(1)Js地方

  1. <script language=”javascript”>
  2. var testname =” <?php echo $testname;?>”;
  3. </script>

$testname的值只要符合js闭合关系:“”;alert(“test xss “);”(以下同理)

(2)form表单里面

  1. <input type=”text” name=”##” value=”<?php echo $val; ?>” />

(3)a标签

  1. <a href=”benwin.php?id= <?php echo $id; ?>”>a标签可以隐藏xss攻击</a>

(4)用得很多的img标签

  1. <img src=”<?php echo $picPath; ?>” />

甚至一些文本中插入整个img标签并且用width、 height、css等隐藏的很隐蔽

(5)地址栏目

总之,有输出数据的地方,更准确的说是有输出用户提交的数据的地方,都有可能是XSS攻击的地方。

4.3防XSS方法

防xss方法其实和防注入很相似,都是一些过滤、代替、实体化等方法

(1)过滤或移除特殊的Html标签。

例如:< 、>、&lt;,、&gt; ’、”、<script>、 <iframe> 、&lt;,、&gt;、&quot

(2)过滤触发JavaScript 事件的标签。例如 onload、onclick、onfocus、onblur、onmouseover等等。

(3)php一些相关函数,strip_tags()、htmlspecialchars()、htmlentities()等函数可以起作用

5、CSRF

CSRF跨站请求伪造cross site request forgery。

5.1简单说明CSRF原理

(1)A登录Site1(如现在网民常上的淘宝、微博、QQ等),产生一些信息,session、cookies等等,且一直保持没退出。

(2)A再登录Site2(如一些成人网等,至于怎么跑到Site2,多数是Site通过些手段,邮件欺骗等),打开site2的浏览器和打开site1的一样,否则无效

(3)Site2站中伪造了Site1的http请求(如修改密码,买东西,转账等),Site1的服务器误以为A在site1的正常操作(因为同浏览器且A还没登出),然后就运行了请求,那么csrf已成功操作。

csrf和xss很相似。xss也能伪造请求,csrf也能制造脚本。

伪造的请求可以很多方面,发邮件、改密码、返回用户信息、交易等等,所以相对与xss攻击来说csrf危害更严重。

 

5.2防范方法。

对于phper

(1)严密操控执行入口

执行一些敏感操作比如改密码这些操作前判断请求来源,只有本站服务器发的请求才可以执行。判断方法可以判断ip来源。非本站服务器ip不会执行。

(2)本站有外链的话做些必要操作

一般site2的hacker会在site1(比如论坛里)里发欺骗连接,因为在site1诱骗的相关人员一般都登录site1了,满足csrf气体条件之一。

如当你点击QQ邮件里面的长外链时候,回跳转到一个页面提示“有风险”之类,这样不仅可以减低跳出率,一些不懂的人看到这样的提示,若不是非必要而是处于好奇点击的连接一般不会继续点击访问;还有是QQ邮件正文里的图片在加载内容时是不加载图片的,要点击“显示图片”按钮才显示图片,这里一个原因之一就是避免攻击。

当然对于用户体验来说这是不可取的,可以优化的是判断到一些网址(如QQ本身网址)是安全直接可以显示(不用提示),而可疑的才提示或禁止。

(3)防止csrf也可以用防xss的方法。

6、防盗链

盗链问题增加服务器的负担。盗链就是盗链网站盗取被盗链网站资源来实现一些功能。盗链方面主要是图片、视频、以及其他资源下载文件。

方法:判断ip,只有本站服务器才能使用站点资源,否则不能使用。

代码:

(1)在Apache htaccess添加

  1. RewriteEngine on
  2. RewriteCond %{HTTP_REFERER} !^$ [NC]
  3. RewriteCond %{HTTP_REFERER} !phpben.com [NC]
  4. RewriteCond %{HTTP_REFERER} !google.com [NC]
  5. RewriteCond %{HTTP_REFERER} !baidu.com [NC]
  6. RewriteCond %{HTTP_REFERER} !zhuaxia.com [NC]
  7. RewriteRule .(jpg|gif|png|bmp|swf|jpeg) /image/replace.gif [R,NC,L]
  8. RewriteRule ^(.*)$ http:\/\/phpben.com\/image\/$1 [L]

这样,凡是不是phpben.com google.com baidu.com zhuaxia.com 域名请求的都返回replace.gif代替返回

7、防CC攻击

CC攻击:是利用不断对网站发送连接请求致使形成拒绝服务的目的。

详细百度百科:http://baike.baidu.com/view/662394.htm

代码:

  1. session_start();
  2. $ll_nowtime = $timestamp ;
  3. if (session_is_registered(‘ll_lasttime’)){
  4. $ll_lasttime = $_SESSION[‘ll_lasttime’];
  5. $ll_times = $_SESSION[‘ll_times’] + 1;
  6. $_SESSION[‘ll_times’] = $ll_times;
  7. }else{
  8. $ll_lasttime = $ll_nowtime;
  9. $ll_times = 1;
  10. $_SESSION[‘ll_times’] = $ll_times;
  11. $_SESSION[‘ll_lasttime’] = $ll_lasttime;
  12. }
  13. if (($ll_nowtime – $ll_lasttime)<3){
  14. if ($ll_times>=5){
  15. header(sprintf(“Location: %s”,’http://127.0.0.1′));
  16. exit;
  17. }
  18. }else{
  19. $ll_times = 0;
  20. $_SESSION[‘ll_lasttime’] = $ll_nowtime;
  21. $_SESSION[‘ll_times’] = $ll_times;
  22. }

Mysql常用命令

第一招、mysql服务的启动和停止

net stop mysql

net start mysql

第二招、登陆mysql

语法如下: mysql -u用户名 -p用户密码

键入命令mysql -uroot -p, 回车后提示你输入密码,输入12345,然后回车即可进入到mysql中了,mysql的提示符是:

mysql>

注意,如果是连接到另外的机器上,则需要加入一个参数-h机器IP

第三招、增加新用户

格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by “密码”

如,增加一个用户user1密码为password1,让其可以在本机上登录, 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql,然后键入以下命令:

grant select,insert,update,delete on *.* to user1@localhost Identified by “password1”;

如果希望该用户能够在任何机器上登陆mysql,则将localhost改为”%”。

如果你不想user1有密码,可以再打一个命令将密码去掉。

grant select,insert,update,delete on mydb.* to user1@localhost identified by “”;

第四招: 操作数据库

登录到mysql中,然后在mysql的提示符下运行下列命令,每个命令以分号结束。

1、 显示数据库列表。

show databases;

缺省有两个数据库:mysql和test。 mysql库存放着mysql的系统和用户权限信息,我们改密码和新增用户,实际上就是对这个库进行操作。

2、 显示库中的数据表:

use mysql;

show tables;

3、 显示数据表的结构:

describe 表名;

4、 建库与删库:

create database 库名;

drop database 库名;

5、 建表:

use 库名;

create table 表名(字段列表);

drop table 表名;

6、 清空表中记录:

delete from 表名;

7、 显示表中的记录:

select * from 表名;

第五招、导出和导入数据

1. 导出数据:

mysqldump –opt test > mysql.test

即将数据库test数据库导出到mysql.test文件,后者是一个文本文件

如:mysqldump -u root -p123456 –databases dbname > mysql.dbname

就是把数据库dbname导出到文件mysql.dbname中。

2. 导入数据:

mysqlimport -u root -p123456 < mysql.dbname。

不用解释了吧。

3. 将文本数据导入数据库:

文本数据的字段数据之间用tab键隔开。

use test;

load data local infile “文件名” into table 表名;

1:使用SHOW语句找出在服务器上当前存在什么数据库:

mysql> SHOW DATABASES;

2:2、创建一个数据库MYSQLDATA

mysql> CREATE DATABASE MYSQLDATA;

3:选择你所创建的数据库

mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功!)

4:查看现在的数据库中存在什么表

mysql> SHOW TABLES;

5:创建一个数据库表

mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

6:显示表的结构:

mysql> DESCRIBE MYTABLE;

7:往表中加入记录

mysql> insert into MYTABLE values (“hyq”,”M”);

8:用文本方式将数据装入数据库表中(例如D:/mysql.txt)

mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;

9:导入.sql文件命令(例如D:/mysql.sql)

mysql>use database;

mysql>source d:/mysql.sql;

10:删除表

mysql>drop TABLE MYTABLE;

11:清空表

mysql>delete from MYTABLE;

12:更新表中数据

mysql>update MYTABLE set sex=”f” where name=’hyq’;

posted on 2006-01-10 16:21 happytian 阅读(6) 评论(0) 编辑 收藏 收藏至365Key

13:备份数据库

mysqldump -u root 库名>xxx.data

14:例2:连接到远程主机上的MYSQL

假设远程主机的IP为:110.110.110.110,用户名为root,密码为abcd123。则键入以下命令:

mysql -h110.110.110.110 -uroot -pabcd123

(注:u与root可以不用加空格,其它也一样)

3、退出MYSQL命令: exit (回车)

 

=====================================================================

1:使用SHOW语句找出在服务器上当前存在什么数据库:
mysql> SHOW DATABASES;
2:2、创建一个数据库MYSQLDATA
mysql> CREATE DATABASE MYSQLDATA;
3:选择你所创建的数据库
mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功!)
4:查看现在的数据库中存在什么表
mysql> SHOW TABLES;
5:创建一个数据库表
mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));
6:显示表的结构:
mysql> DESCRIBE MYTABLE;
7:往表中加入记录
mysql> insert into MYTABLE values (“hyq”,”M”);
8:用文本方式将数据装入数据库表中(例如D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;
9:导入.sql文件命令(例如D:/mysql.sql)
mysql>use database;
mysql>source d:/mysql.sql;
10:删除表
mysql>drop TABLE MYTABLE;
11:清空表
mysql>delete from MYTABLE;
12:更新表中数据
mysql>update MYTABLE set sex=”f” where name=hyq;

以下是无意中在网络看到的使用MySql的管理心得,
摘自:http://www1.xjtusky.com/article/htmldata/2004_12/3/57/article_1060_1.html

在windows中MySql以服务形式存在,在使用前应确保此服务已经启动,未启动可用net start mysql命令启动。而Linux中启动时可用“/etc/rc.d/init.d/mysqld start”命令,注意启动者应具有管理员权限。
刚安装好的MySql包含一个含空密码的root帐户和一个匿名帐户,这是很大的安全隐患,对于一些重要的应用我们应将安全性尽可能提高,在这里应把匿名帐户删除、 root帐户设置密码,可用如下命令进行:
use mysql;
delete from User where User=””;
update User set Password=PASSWORD(newpassword) where User=root;
如果要对用户所用的登录终端进行限制,可以更新User表中相应用户的Host字段,在进行了以上更改后应重新启动数据库服务,此时登录时可用如下类似命令:
mysql -uroot -p;
mysql -uroot -pnewpassword;
mysql mydb -uroot -p;
mysql mydb -uroot -pnewpassword;
上面命令参数是常用参数的一部分,详细情况可参考文档。此处的mydb是要登录的数据库的名称。
在进行开发和实际应用中,用户不应该只用root用户进行连接数据库,虽然使用root用户进行测试时很方便,但会给系统带来重大安全隐患,也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如一个只进行数据插入的用户不应赋予其删除数据的权限。MySql的用户管理是通过 User表来实现的,添加新用户常用的方法有两个,一是在User表插入相应的数据行,同时设置相应的权限;二是通过GRANT命令创建具有某种权限的用户。其中GRANT的常用用法如下:
grant all on mydb.* to NewUserName@HostName identified by “password” ;
grant usage on *.* to NewUserName@HostName identified by “password”;
grant select,insert,update on mydb.* to NewUserName@HostName identified by “password”;
grant update,delete on mydb.TestTable to NewUserName@HostName identified by “password”;
若要给此用户赋予他在相应对象上的权限的管理能力,可在GRANT后面添加WITH GRANT OPTION选项。而对于用插入User表添加的用户,Password字段应用PASSWORD 函数进行更新加密,以防不轨之人窃看密码。对于那些已经不用的用户应给予清除,权限过界的用户应及时回收权限,回收权限可以通过更新User表相应字段,也可以使用REVOKE操作。
下面给出本人从其它资料(www.cn-java.com)获得的对常用权限的解释:
全局管理权限:
FILE: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表,刷新日志等。
SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限:
ALTER: 修改已存在的数据表(例如增加/删除列)和索引。
CREATE: 建立新的数据库或数据表。
DELETE: 删除表的记录。
DROP: 删除数据表或数据库。
INDEX: 建立或删除索引。
INSERT: 增加表的记录。
SELECT: 显示/搜索表的记录。
UPDATE: 修改表中已存在的记录。
特别的权限:
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录–其它什么也不允许做。

 

常用MYSQL命令
启动:net start mySql;
进入:mysql -u root -p/mysql -h localhost -u root -p databaseName;
列出数据库:show databases;
选择数据库:use databaseName;
列出表格:show tables;
创建数据表:mysql> CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1),
-> birth DATE, birthaddr VARCHAR(20));
显示表格列的属性:show columns from tableName;
修改表的结构:DESCRIBE mytable;
建立数据库:source fileName.txt;
匹配字符:可以用通配符_代表任何一个字符,%代表任何字符串;
增加一个字段:alter table tabelName add column fieldName dateType;
增加多个字段:alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType;
多行命令输入:注意不能将单词断开;当插入或更改数据时,不能将字段的字符串展开到多行里,否则硬回车将被储存到数据中;
增加一个管理员帐户:grant all on *.* to user@localhost identified by “password”;
每条语句输入完毕后要在末尾填加分号’;’,或者填加’\g’也可以;
查询时间:select now();
查询当前用户:select user();
查询数据库版本:select version();
查询当前使用的数据库:select database();

用文本方式将数据装入一个数据库表

如果一条一条地输入,很麻烦。我们可以用文本文件的方式将所有记录加入你的数据库表中。创建一个文本文件“mysql.txt”,每行包含一个记录,用定位符(tab)把值分开,并且以在CREATE TABLE语句中列出的列次序给出,例如:

abccs f 1977-07-07 china   mary f 1978-12-12 usa tom m 1970-09-02 usa

使用下面命令将文本文件“mytable.txt”装载到mytable表中:mysql> LOAD DATA LOCAL INFILE “mytable.txt” INTO TABLE pet;

再使用如下命令看看是否已将数据输入到数据库表中:mysql> select * from mytable;
(e129)
1、删除student_course数据库中的students数据表:
rm -f student_course/students.*

2、备份数据库:(将数据库test备份)
mysqldump -u root -p test>c:\test.txt
备份表格:(备份test数据库下的mytable表格)
mysqldump -u root -p test mytable>c:\test.txt
将备份数据导入到数据库:(导回test数据库)
mysql -u root -p test<c:\test.txt

3、创建临时表:(建立临时表zengchao)
create temporary table zengchao(name varchar(10));

4、创建表是先判断表是否存在
create table if not exists students(……);

5、从已经有的表中复制表的结构
create table table2 select * from table1 where 1<>1;

6、复制表
create table table2 select * from table1;

7、对表重新命名
alter table table1 rename as table2;

8、修改列的类型
alter table table1 modify id int unsigned;//修改列id的类型为int unsigned
alter table table1 change id sid int unsigned;//修改列id的名字为sid,而且把属性修改为int unsigned

9、创建索引
alter table table1 add index ind_id (id);
create index ind_id on table1 (id);
create unique index ind_id on table1 (id);//建立唯一性索引

10、删除索引
drop index idx_id on table1;
alter table table1 drop index ind_id;

11、联合字符或者多个列(将列id与”:”和列name和”=”连接)
select concat(id,’:’,name,’=’) from students;

12、limit(选出10到20条)<第一个记录集的编号是0>
select * from students order by id limit 9,10;

13、MySQL不支持的功能
事务,视图,外键和引用完整性,存储过程和触发器

14、MySQL会使用索引的操作符号
<,<=,>=,>,=,between,in,不带%或者_开头的like

15、使用索引的缺点
1)减慢增删改数据的速度;
2)占用磁盘空间;
3)增加查询优化器的负担;
当查询优化器生成执行计划时,会考虑索引,太多的索引会给查询优化器增加工作量,导致无法选择最优的查询方案;

16、分析索引效率
方法:在一般的SQL语句前加上explain;
分析结果的含义:
1)table:表名;
2)type:连接的类型,(ALL/Range/Ref)。其中ref是最理想的;
3)possible_keys:查询可以利用的索引名;
4)key:实际使用的索引;
5)key_len:索引中被使用部分的长度(字节);
6)ref:显示列名字或者”const”(不明白什么意思);
7)rows:显示MySQL认为在找到正确结果之前必须扫描的行数;
8)extra:MySQL的建议;

17、使用较短的定长列
1)尽可能使用较短的数据类型;
2)尽可能使用定长数据类型;
a)用char代替varchar,固定长度的数据处理比变长的快些;
b)对于频繁修改的表,磁盘容易形成碎片,从而影响数据库的整体性能;
c)万一出现数据表崩溃,使用固定长度数据行的表更容易重新构造。使用固定长度的数据行,每个记录的开始位置都是固定记录长度的倍数,可以很容易被检测到,但是使用可变长度的数据行就不一定了;
d)对于MyISAM类型的数据表,虽然转换成固定长度的数据列可以提高性能,但是占据的空间也大;

18、使用not null和enum
尽量将列定义为not null,这样可使数据的出来更快,所需的空间更少,而且在查询时,MySQL不需要检查是否存在特例,即null值,从而优化查询;
如果一列只含有有限数目的特定值,如性别,是否有效或者入学年份等,在这种情况下应该考虑将其转换为enum列的值,MySQL处理的更快,因为所有的enum值在系统内都是以标识数值来表示的;

19、使用optimize table
对于经常修改的表,容易产生碎片,使在查询数据库时必须读取更多的磁盘块,降低查询性能。具有可变长的表都存在磁盘碎片问题,这个问题对blob数据类型更为突出,因为其尺寸变化非常大。可以通过使用optimize table来整理碎片,保证数据库性能不下降,优化那些受碎片影响的数据表。 optimize table可以用于MyISAM和BDB类型的数据表。实际上任何碎片整理方法都是用mysqldump来转存数据表,然后使用转存后的文件并重新建数据表;

20、使用procedure analyse()
可以使用procedure analyse()显示最佳类型的建议,使用很简单,在select语句后面加上procedure analyse()就可以了;例如:
select * from students procedure analyse();
select * from students procedure analyse(16,256);
第二条语句要求procedure analyse()不要建议含有多于16个值,或者含有多于256字节的enum类型,如果没有限制,输出可能会很长;

21、使用查询缓存
1)查询缓存的工作方式:
第一次执行某条select语句时,服务器记住该查询的文本内容和查询结果,存储在缓存中,下次碰到这个语句时,直接从缓存中返回结果;当更新数据表后,该数据表的任何缓存查询都变成无效的,并且会被丢弃。
2)配置缓存参数:
变量:query_cache _type,查询缓存的操作模式。有3中模式,0:不缓存;1:缓存查询,除非与select sql_no_cache开头;2:根据需要只缓存那些以select sql_cache开头的查询;query_cache_size:设置查询缓存的最大结果集的大小,比这个值大的不会被缓存。

22、调整硬件
1)在机器上装更多的内存;
2)增加更快的硬盘以减少I/O等待时间;
寻道时间是决定性能的主要因素,逐字地移动磁头是最慢的,一旦磁头定位,从磁道读则很快;
3)在不同的物理硬盘设备上重新分配磁盘活动;
如果可能,应将最繁忙的数据库存放在不同的物理设备上,这跟使用同一物理设备的不同分区是不同的,因为它们将争用相同的物理资源(磁头)。

1、启动MySQL服务器

两种方法: 一是用winmysqladmin,如果机器启动时已自动运行,则可直接进入下一步操作。 二是在DOS方式下运行 d:\mysql\bin\mysqld
(假设mysql 安装在d:\mysql);当然也可以 net start mysql命令启动。而Linux中启动时可用“/etc/rc.d/init.d/mysqld start”命令,注 意启动者应具有管理员权限。

2、进入mysql交互操作界面

在DOS方式下,运行: d:\mysql\bin\mysql
出现: mysql 的提示符” mysql> “,此时已进入mysql的交互操作方式。
如果出现 “ERROR 2003: Can′t connect to MySQL server on ′localhost′ (10061)“,说明你的MySQL还没有启动。

3、退出MySQL操作界面

在mysql>提示符下输入quit可以随时退出交互操作界面:
mysql> quit
Bye

4、第一条命令(多条命令用 “,” 分隔 ,一条命令可以分成多行输入,直到出现分号”;”为止

mysql> select version(),current_date();
mysql>Select (20+5)*4;
mysql>Select (20+5)*4,sin(pi()/3);
mysql>Select (20+5)*4 AS Result,sin(pi()/3); (AS: 指定假名为Result)

5、常用sql 语句

1:使用SHOW语句找出在服务器上当前存在什么数据库:
mysql> SHOW DATABASES;
2:2、创建一个数据库MYSQLDATA
mysql> Create DATABASE MYSQLDATA;
3:选择你所创建的数据库
mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功!)
4:查看现在的数据库中存在什么表
mysql> SHOW TABLES;
5:创建一个数据库表
mysql> Create TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));
6:显示表的结构:
mysql> DESCRIBE MYTABLE;
7:往表中加入记录
mysql> insert into MYTABLE values (”hyq”,”M”);
8:用文本方式将数据装入数据库表中(例如D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;
9:导入.sql文件命令(例如D:/mysql.sql)
mysql>use database;
mysql>source d:/mysql.sql;
10:删除表
mysql>drop TABLE MYTABLE;
11:清空表
mysql>delete from MYTABLE;
12:更新表中数据
mysql>update MYTABLE set sex=”f” where name=’hyq’;
13:插入数据
mysq>linsert into MYTABLE values (1, ‘第二章’, 0×2134545);
14:登录后使用数据库 mysql
mysql -u root -p mysql
mysql -u root -p -h 11.11.11.11 database
15:mysql jdbc连接url 使用中文
jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=gb2312

[separator]

其它:
show processlist;列出每一笔联机的信息。
show variables;列出mysql的系统设定。
show tables from db_name;列出db_name中所有数据表;
show [full] columns from table_name;列出table_name中完整信息,如栏名、类型,包括字符集编码。
show index from table_name; 列出table_name中所有的索引。
show table status;;列出当前数据库中数据表的信息。
show table status from db_name;;列出当前db_name中数据表的信息。
alter table table_name engine innodb|myisam|memory ;更改表类型
explain table_name / describe table_name ; 列出table_name完整信息,如栏名、类型。
show create table table_name 显示当前表的建表语句
alter table table_name add primary key (picid) ; 向表中增加一个主键
alter table table_name add column userid int after picid 修改表结构增加一个新的字段
alter table table_name character set gb2312 改变表的编码
select user(); 显示当前用户。
select password(’root’); 显示当前用户密码
select now(); 显示当前日期
flush privileges 在不重启的情况下刷新用户权限
mysqld –default-character-set=gb2312 ;设置默认字符集为gb2312

6、安全

刚安装好的MySql包含一个含空密码的root帐户和一个匿名帐户,这是很大的安全隐患,对于一些重要的应用我们应将安全性尽可能提高,在这里应把匿名帐户删除、 root帐户设置密码,可用如下命令进行:

use mysql;
delete from User where User=””;
update User set Password=PASSWORD(’newpassword’) where User=’root’;

改完重启 mysql 才能生效.

上面命令参数是常用参数的一部分,详细情况可参考文档。此处的mydb是要登录的数据库的名称。
在进行开发和实际应用中,用户不应该只用root用户进行连接数据库,虽然使用root用户进行测试时很方便,但会给系统带来重大安全隐患,也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如一个只进行数据插入的用户不应赋予其删除数据的权限。MySql的用户管理是通过 User表来实现的,添加新用户常用的方法有两个,一是在User表插入相应的数据行,同时设置相应的权限;二是通过GRANT命令创建具有某种权限的用户。其中GRANT的常用用法如下:

grant all on mydb.* to NewUserName@HostName identified by “password” ;
grant usage on *.* to NewUserName@HostName identified by “password”;
grant select,insert,update on mydb.* to NewUserName@HostName identified by “password”;
grant update,delete on mydb.TestTable to NewUserName@HostName identified by “password”;
grant all privileges on *.* to root@localhost
grant select,insert,delete,update,alter,create,drop on lybbs.* to NewUserName@”%” identified by “lybbs”;

若要给此用户赋予他在相应对象上的权限的管理能力,可在GRANT后面添加WITH GRANT OPTION选项。而对于用插入User表添加的用户,Password字段应用PASSWORD 函数进行更新加密,以防不轨之人窃看密码。对于那些已经不用的用户应给予清除,权限过界的用户应及时回收权限,回收权限可以通过更新User表相应字段,也可以使用REVOKE操作。

全局管理权限:
FILE: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表,刷新日志等。
SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限:
Alter: 修改已存在的数据表(例如增加/删除列)和索引。
Create: 建立新的数据库或数据表。
Delete: 删除表的记录。
Drop: 删除数据表或数据库。
INDEX: 建立或删除索引。
Insert: 增加表的记录。
Select: 显示/搜索表的记录。
Update: 修改表中已存在的记录。
特别的权限:
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录–其它什么也不允许做。

修改默认密码:
cd d:\mysql\bin
mysqladmin -u root -p password 123456
回车出现
Enter password: ( 注:这是叫你输入原密码. 刚安装时密码为空,所以直接回车即可)
此时mysql 中账号 root 的密码 被改为 123456 安装完毕

一、引言

想使用Linux已经很长时间了,由于没有硬性任务一直也没有系统学习,近日由于工作需要必须使用Linux下的MySQL。本以为有Windows下使用SQL Server的经验,觉得在Linux下安装MySql应该是易如反掌的事,可在真正安装和使用MySQL时走了很多弯路,遇见很多问题,毕竟Linux 和Windows本身就有很大区别。为了让和我一样的初学者在学习的过程中少走弯路,尽快入门,写了此文,希望对您有所帮助。本文的Linux环境是 Red Hat 9.0,MySQL是4.0.16。

二、安装Mysql

1、下载MySQL的安装文件
安装MySQL需要下面两个文件:
MySQL-server-4.0.16-0.i386.rpm
MySQL-client-4.0.16-0.i386.rpm
下载地址为:http://www.mysql.com/downloads/mysql-4.0.html, 打开此网页,下拉网页找到“Linux x86 RPM downloads”项,找到“Server”和“Client programs”项,下载需要的上述两个rpm文件。

2、安装MySQL
rpm文件是Red Hat公司开发的软件安装包,rpm可让Linux在安装软件包时免除许多复杂的手续。该命令在安装时常用的参数是 –ivh ,其中i表示将安装指定的rmp软件包,V表示安装时的详细信息,h表示在安装期间出现“#”符号来显示目前的安装过程。这个符号将持续到安装完成后才停止。
1)安装服务器端
在有两个rmp文件的目录下运行如下命令:
[root@test1 local]# rpm -ivh MySQL-server-4.0.16-0.i386.rpm
显示如下信息。
warning: MySQL-server-4.0.16-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing…       ########################################### [100%]
1:MySQL-server     ########################################### [100%]
。。。。。。(省略显示)
/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h test1 password ‘new-password’
。。。。。。(省略显示)
Starting mysqld daemon with databases from /var/lib/mysql
如出现如上信息,服务端安装完毕。测试是否成功可运行netstat看Mysql端口是否打开,如打开表示服务已经启动,安装成功。Mysql默认的端口是3306。
[root@test1 local]# netstat -nat
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address      Foreign Address     State
tcp  0  0 0.0.0.0:3306     0.0.0.0:*      LISTEN
上面显示可以看出MySQL服务已经启动。
2)安装客户端
运行如下命令:
[root@test1 local]# rpm -ivh MySQL-client-4.0.16-0.i386.rpm
warning: MySQL-client-4.0.16-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing…    ########################################### [100%]
1:MySQL-client  ########################################### [100%]
显示安装完毕。
用下面的命令连接mysql,测试是否成功。
三、登录MySQL

登录MySQL的命令是mysql, mysql 的使用语法如下:
mysql [-u username] [-h host] [-p[password]] [dbname]
username 与 password 分别是 MySQL 的用户名与密码,mysql的初始管理帐号是root,没有密码,注意:这个root用户不是Linux的系统用户。MySQL默认用户是root,由于初始没有密码,第一次进时只需键入mysql即可。
[root@test1 local]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.16-standard
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>
出现了“mysql>”提示符,恭喜你,安装成功!
增加了密码后的登录格式如下:
mysql -u root -p
Enter password: (输入密码)
其中-u后跟的是用户名,-p要求输入密码,回车后在输入密码处输入密码。

注意:这个mysql文件在/usr/bin目录下,与后面讲的启动文件/etc/init.d/mysql不是一个文件。

四、MySQL的几个重要目录

MySQL安装完成后不象SQL Server默认安装在一个目录,它的数据库文件、配置文件和命令文件分别在不同的目录,了解这些目录非常重要,尤其对于Linux的初学者,因为 Linux本身的目录结构就比较复杂,如果搞不清楚MySQL的安装目录那就无从谈起深入学习。

下面就介绍一下这几个目录。

1、数据库目录
/var/lib/mysql/

2、配置文件
/usr/share/mysql(mysql.server命令及配置文件)

3、相关命令
/usr/bin(mysqladmin mysqldump等命令)

4、启动脚本
/etc/rc.d/init.d/(启动脚本文件mysql的目录)
五、修改登录密码

MySQL默认没有密码,安装完毕增加密码的重要性是不言而喻的。

1、命令
usr/bin/mysqladmin -u root password ‘new-password’
格式:mysqladmin -u用户名 -p旧密码 password 新密码

2、例子
例1:给root加个密码123456。
键入以下命令 :
[root@test1 local]# /usr/bin/mysqladmin -u root password 123456
注:因为开始时root没有密码,所以-p旧密码一项就可以省略了。

3、测试是否修改成功
1)不用密码登录
[root@test1 local]# mysql
ERROR 1045: Access denied for user: ‘root@localhost’ (Using password: NO)
显示错误,说明密码已经修改。
2)用修改后的密码登录
[root@test1 local]# mysql -u root -p
Enter password: (输入修改后的密码123456)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.16-standard
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>
成功!
这是通过mysqladmin命令修改口令,也可通过修改库来更改口令。

六、启动与停止

1、启动
MySQL安装完成后启动文件mysql在/etc/init.d目录下,在需要启动时运行下面命令即可。
[root@test1 init.d]# /etc/init.d/mysql start

2、停止
/usr/bin/mysqladmin -u root -p shutdown

3、自动启动
1)察看mysql是否在自动启动列表中
[root@test1 local]# /sbin/chkconfig –list
2)把MySQL添加到你系统的启动服务组里面去
[root@test1 local]# /sbin/chkconfig – add mysql
3)把MySQL从启动服务组里面删除。
[root@test1 local]# /sbin/chkconfig – del mysql
七、更改MySQL目录

MySQL默认的数据文件存储目录为/var/lib/mysql。假如要把目录移到/home/data下需要进行下面几步:

1、home目录下建立data目录
cd /home
mkdir data

2、把MySQL服务进程停掉:
mysqladmin -u root -p shutdown

3、把/var/lib/mysql整个目录移到/home/data
mv /var/lib/mysql /home/data/
这样就把MySQL的数据文件移动到了/home/data/mysql下

4、找到my.cnf配置文件
如果/etc/目录下没有my.cnf配置文件,请到/usr/share/mysql/下找到*.cnf文件,拷贝其中一个到/etc/并改名为my.cnf)中。命令如下:
[root@test1 mysql]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

5、编辑MySQL的配置文件/etc/my.cnf
为保证MySQL能够正常工作,需要指明mysql.sock文件的产生位置。 修改socket=/var/lib/mysql/mysql.sock一行中等号右边的值为:/home/mysql/mysql.sock 。操作如下:
vi  my.cnf    (用vi工具编辑my.cnf文件,找到下列数据修改之)
# The MySQL server
[mysqld]
port   = 3306
#socket  = /var/lib/mysql/mysql.sock(原内容,为了更稳妥用“#”注释此行)
socket  = /home/data/mysql/mysql.sock   (加上此行)

6、修改MySQL启动脚本/etc/rc.d/init.d/mysql
最后,需要修改MySQL启动脚本/etc/rc.d/init.d/mysql,把其中datadir=/var/lib/mysql一行中,等号右边的路径改成你现在的实际存放路径:home/data/mysql。
[root@test1 etc]# vi /etc/rc.d/init.d/mysql
#datadir=/var/lib/mysql    (注释此行)
datadir=/home/data/mysql   (加上此行)

7、重新启动MySQL服务
/etc/rc.d/init.d/mysql start
或用reboot命令重启Linux
如果工作正常移动就成功了,否则对照前面的7步再检查一下。

八、MySQL的常用操作

注意:MySQL中每个命令后都要以分号;结尾。

1、显示数据库
mysql> show databases;
+———-+
| Database |
+———-+
| mysql  |
| test   |
+———-+
2 rows in set (0.04 sec)
Mysql刚安装完有两个数据库:mysql和test。mysql库非常重要,它里面有MySQL的系统信息,我们改密码和新增用户,实际上就是用这个库中的相关表进行操作。

2、显示数据库中的表
mysql> use mysql; (打开库,对每个库进行操作就要打开此库,类似于foxpro )
Database changed

mysql> show tables;
+—————–+
| Tables_in_mysql |
+—————–+
| columns_priv  |
| db       |
| func      |
| host      |
| tables_priv   |
| user      |
+—————–+
6 rows in set (0.01 sec)

3、显示数据表的结构:
describe 表名;

4、显示表中的记录:
select * from 表名;
例如:显示mysql库中user表中的纪录。所有能对MySQL用户操作的用户都在此表中。
Select * from user;

5、建库:
create database 库名;
例如:创建一个名字位aaa的库
mysql> create databases aaa;
6、建表:
use 库名;
create table 表名 (字段设定列表);
例如:在刚创建的aaa库中建立表name,表中有id(序号,自动增长),xm(姓名),xb(性别),csny(出身年月)四个字段
use aaa;
mysql> create table name (id int(3) auto_increment not null primary key, xm char(8),xb char(2),csny date);
可以用describe命令察看刚建立的表结构。
mysql> describe name;

+——-+———+——+—–+———+—————-+
| Field | Type  | Null | Key | Default | Extra     |
+——-+———+——+—–+———+—————-+
| id  | int(3) |   | PRI | NULL  | auto_increment |
| xm  | char(8) | YES |   | NULL  |        |
| xb  | char(2) | YES |   | NULL  |        |
| csny | date  | YES |   | NULL  |        |
+——-+———+——+—–+———+—————-+

7、增加记录
例如:增加几条相关纪录。
mysql> insert into name values(”,’张三’,’男’,’1971-10-01′);
mysql> insert into name values(”,’白云’,’女’,’1972-05-20′);
可用select命令来验证结果。
mysql> select * from name;
+—-+——+——+————+
| id | xm  | xb  | csny    |
+—-+——+——+————+
| 1 | 张三 | 男  | 1971-10-01 |
| 2 | 白云 | 女  | 1972-05-20 |
+—-+——+——+————+

8、修改纪录
例如:将张三的出生年月改为1971-01-10
mysql> update name set csny=’1971-01-10′ where xm=’张三’;

9、删除纪录
例如:删除张三的纪录。
mysql> delete from name where xm=’张三’;

10、删库和删表
drop database 库名;
drop table 表名;

九、增加MySQL用户

格式:grant select on 数据库.* to 用户名@登录主机 identified by “密码”
例1、增加一个用户user_1密码为123,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL,然后键入以下命令:

mysql> grant select,insert,update,delete on *.* to user_1@”%” Identified by “123”;
例1增加的用户是十分危险的,如果知道了user_1的密码,那么他就可以在网上的任何一台电脑上登录你的MySQL数据库并对你的数据为所欲为了,解决办法见例2。

例2、增加一个用户user_2密码为123,让此用户只可以在localhost上登录,并可以对数据库aaa进行查询、插入、修改、删除的操作(localhost指本地主机,即MySQL数据库所在的那台主机),这样用户即使用知道user_2的密码,他也无法从网上直接访问数据库,只能通过 MYSQL主机来操作aaa库。

mysql>grant select,insert,update,delete on aaa.* to user_2@localhost identified by “123”;

用新增的用户如果登录不了MySQL,在登录时用如下命令:

mysql -u user_1 -p -h 192.168.113.50 (-h后跟的是要登录主机的ip地址)

十、备份与恢复

1、备份

例如:将上例创建的aaa库备份到文件back_aaa中

[root@test1 root]# cd /home/data/mysql (进入到库目录,本例库已由val/lib/mysql转到/home/data/mysql,见上述第七部分内容)
[root@test1 mysql]# mysqldump -u root -p –opt aaa > back_aaa

2、恢复

[root@test mysql]# mysql -u root -p ccc < back_aaa

人生的悲剧只有两种:一种是没有得到自己想要的东西,另一种是得到自己想要的东西。
                                —王尔德

标签

打赏