分类： 数据库开发技术
2012-05-07 12:23:34
1. 创建用户的方法：
可以使用两种方式创建mysql用户，分别利用grant语句与直接操作user表，分别介绍如下。
1) 使用grant语句
GRANT 角色 ON 数据库/表/视图 TO 用户
下面的语句使用grant来设置新用户：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test1'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test1'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'test2'@'localhost';
mysql> GRANT USAGE ON *.* TO 'test3'@'localhost';
test1: 密码为123456，具有超级用户的权限，用户'test1'@'localhost'用于从本机连接时，用户'test1'@'%'用于从其它主机连接时。（可以使用select current_user();查看登录的mysql用户，其中，密码在user表中存储的是加密之后的结果：*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9）
test2: 没有密码，用于从本机连接，具有reload与process管理权限，但不具备访问数据库的权限，可以通过使用grant语句为其增加访问权限。
Test3: 没有密码，用于从本机连接，没有权限。可以在以后为用户设置权限。
2) 直接操作mysql数据库中的user表
可以通过直接使用insert语句创建用户，然后使用FLUSH PRIVILEGES;
mysql> INSERT INTO user
-> VALUES('localhost','test1',PASSWORD('123456'),
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user
-> VALUES('%','test1',PASSWORD('123456'),
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='test2',
-> Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','test3','');
mysql> FLUSH PRIVILEGES;
上面的操作与1)中创建用户的方法效果是一样的。
注：使用INSERT创建用户时，一定要使用PASSWORD()函数为密码加密，而使用GRANT创建用户时，由GRANT来加密，不需要使用PASSWORD()函数了。我们在user表中看到的Password列均为加密之后的密码。
2. 修改用户密码的方法：
当用户创建之后，修改密码的方法有三种，分别介绍如下。
1） 通过SET PASSWORD方法：
mysql> SET PASSWORD FOR 'xxx'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'xxx'@'host_name' = PASSWORD('newpwd');
2) 通过mysqladmin命令行客户端程序
shell> mysqladmin -u xxx password "newpwd"
shell> mysqladmin -u xxx -h host_name password "newpwd"
3）通过UPDATE语句
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
-> WHERE User = 'xxx';
mysql> FLUSH PRIVILEGES;
3. 用户的访问控制：
服务器在mysql数据库的授权表中保存权限信息(即在mysql数据库中)。当MySQL服务器启动时将这些表的内容读入内存。
下面是对不同User表中，User与Host的访问权限意义。
翻译后如下：
Mysql中当用户连接成功之后，在user表中具有操作权限，当为”Y”时，即表示用户具有该项权限。User中查看的用户权限如下：
可以通过上述的grant与直接修改user表中内容的方法对用户的权限进行更改与控制。
Execute_priv:必须具有这个权限的用户，才能执行存储子程序。
Create_routine_pirv:创建存储程序与函数。
Alter_routine_priv:移除存储程序与函数，被自动授予给存储函数的创建者。
=========================连接数据库============================
连接MySQL服务器时，服务器基于身份以及是否能通过供应正确的密码验证身份来接受或拒绝连接。
身份信息如下：1）从哪个主机连接；2）使用的用户名。
身份检查使用user表的(Host, User和Password)列执行。服务器只有在user表记录的Host和User列匹配客户端主机名和用户名并且提供了正确的密码时才接受连接。
可能在一次连接时匹配user表中的多行，此时的匹配规则如下：
1）服务器在启动时读入user表后进行排序（以Host值具体程度排序，有相同Host的以具有最具体的User排序）；
2）当用户试图连接时，以排序的顺序浏览条目，服务器使用与客户端和用户名匹配的第1行。
假设有如下的user表：
排序后的结果：
当客户端试图连接时，服务器浏览排序的条目，并使用找到的第1行匹配。如果使用Jeffrey从localhost连接，表中有2个条目匹配：Host和User值为”localhost”和””的条目，与值为”%”和”jeffrey”的条目，localhost条目首先匹配。
另外举个例子：
排序前的user表：
排序后的user表：
由jeffrey从thomas.loc.gov的连接与第一行匹配，而由jeffrey从whitehouse.gov的连接被第二个匹配。
普遍的误区：对给定的用户名，当服务器试图对连接寻找匹配时，明确命名那个用户的所有条目将首先被使用。
如果能够连接服务器，但是权限不是期望的权限，则可能是被鉴定为其他用户了。可以使用select current_user()来找出当前的匹配的user表记录。
===================权限更改何时生效====================================
当mysqld启动时，所有授权表的内容被读进内存并且从此时生效。
当服务器注意到授权表被改变了时，现存的客户端连接有如下影响：
1）表和列权限在客户端的下一次请求时生效。
2）数据库权限改变在下一个USE db_name命令生效。
3）全局权限的改变和密码改变在下一次客户端连接时生效。
如果用GRANT、REVOKE或SET PASSWORD对授权表进行修改，服务器会注意到并立即重新将授权表载入内存。
如果手动地修改授权表(使用INSERT、UPDATE或DELETE等等)，则应该执行flush-privileges或reload告诉服务器重载授权表，否则更改将不会生效，除非重启服务器。
4. 使程序在后台运行：
在linux下，很多情况下需要使程序在后台运行，否则当将终端关闭之后，就不可以使用了。
如果在启动mysql时，未设置使其在后台运行，则当关闭终端后，下次又得重新启动。
使程序在后台运行的方法有2种：
1）在命令后面加上&
如：./mysqld_safe &
2）使用Ctrl+Z
当忘记在使用命令时输入&时，可以使用Ctrl+Z使mysqld在后台运行。
使在后台运行的程序调到前台运行的方法：fg [number] 即可。（number可用jobs查询）
5. Mysql log相关知识：
Mysql5.1版本中的日志文件有4种，分别如下：
1）错误日志：记录启动、运行或停止mysqld时出现的问题。
2）通用查询日志：记录建立的客户端连接和执行的语句。
3）二进制日志：记录所有更改数据的语句。
4）慢速查询日志：记录所有执行时间超过long_query_time秒的所有查询。
默认情况下，所有日志创建于mysld数据目录中，但是也可以指定目录。
==============================错误日志================================
错误日志文件包含了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。
如果mysqld莫名其妙地死掉并且mysqld_safe需要重新启动它，mysqld_safe在错误日志中写入一条restarted mysqld消息。
可以用--log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值，mysqld使用错误日志名host_name.err 并在数据目录中写入日志文件。如果执行FLUSH LOGS，错误日志用-old重新命名后缀并且mysqld创建一个新的空日志文件。(如果未给出--log-error选项，则不会重新命名）。
===========================通用查询日志================================
查询日志是按照mysql接收的sql语句的顺序记录到日志中，可能与执行顺序不同。（二进制日志是在查询结束后，锁被释放之前记录到日志中的，且二进制日志不包含只查询数据的记录）。
如果想要知道mysqld内部发生了什么，应该用--log[=file_name]或-l [file_name]选项启动它。如果没有给定file_name的值， 默认名是host_name.log。所有连接和语句被记录到日志文件。当怀疑在客户端发生了错误并想确切地知道该客户端发送给mysqld的语句时，该日志可能非常有用。
===========================二进制日志================================
二进制日志：binary log。
二进制日志包含修改数据库内容的SQL语句的信息。这个信息以描述修改的事件的形式保存起来。主要有两个作用：
1）复制的基础是主服务器发送包含在二进制日志里的事件到从服务器，从服务器执行这些事件来造成与对主服务器造成的同样的数据改变。
2）特定的数据恢复操作需要使用二进制日志。备份的文件被恢复之后，备份后纪录的二进制日志里的事件被重新执行。这些事件把数据库带从备份点的日子带到当前。
使用--log-bin选项启动服务器，则是开启了bin log功能。如果bin log未启用，则数据复制不可能，且数据恢复也不可能，因为用于数据恢复的二进制日志文件将不存在。当使用—log-bin=[file_name]选项启动mysql时，二进制文件将写入指定的文件中。如果只给出了file_name，未给出路径，则仍是默认存入数据目录。每次启动服务器或者刷新日志（flush logs）时，二进制日志名后面添加一个数字扩展名。
可以通过—binlog –do –db =db_name与 –binlog –ignore –db=db_name来配置关于哪些数据库的操作将被写入或者不被写入二进制日志文件。
关于mysql中的复制功能：
MySQL使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。
关于系统变量log_bin_trust_function_creators：
在msyql中，当要创建存储程序与函数（存储子程序）时，具有非常严格的要求：
1）用户具有Create_routine权限；
2）用户具有super权限；
3）声明存储子程序是确定性的（DETERMINISTIC）或者是不更改数据的（READS SQL DATA or NO SQL）。所谓确定性的，是指对于给定输入，产生的输出是一致的。
为了放松对这个条件的限制（比如，系统上的具有Create_routine权限的用户都是相当有经验的开发人员），mysql提供了系统变量log_bin_trust_function_creators。 当log_bin_trust_function_creators为1时，则不要求用户必须具有super权限，且不需要在创建存储子程序时声明为确定性的或者是不更改数据的。当log-bin未开启时，log_bin_trust_function_creators是不起作用的，此时要求将仍很严格。
Mysql中，对于子程序本性的评估是基于创建者的“诚实度”，MySQL不检查声明为确定性的子程序是否不含产生非确定性结果的语句。
触发程序类似于存储函数，前述的评论也适用于触发程序，不同的是： CREATE TRIGGER没有可选的DETERMINISTIC特征，所以触发程序被假定为总是确定性的。如果你没有SUPER权限且log_bin_trust_routine_creators 被设为0，得到的错误信息类似于存储子程序与CREATE TRIGGER产生的错误信息。
==========================慢速查询日志================================
慢速查询日志记录所有执行时间超过long_query_time秒的所有查询。
当使用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。获得初使表锁定的时间不算作执行时间。如果没有给出file_name值， 默认未主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。
慢查询日志可以用来找到执行时间长的查询，可以用于优化。
