第13章 MySQL用户管理

13.1 权限表

13.1.1 user 表

13.1.2 db 表和 host 表

13.1.3 tables_priv 表和 columns_priv表

13.1.4 procs_priv 表

13.2 账户管理

13.2.1 登录和退出MySQL服务器

13.2.2 新建普通用户

13.2.3 删除普通用户

13.2.4 root用户修改自己的密码

13.2.5 root用户修改普通用户的密码

13.2.6 普通用户修改密码

13.2.7 root用户密码丢失的解决办法

13.3 权限管理

13.3.1 MySQL的各种权限

13.3.2 授权

13.3.3 收回权限

13.3.4 査看权限

13.4 访问控制

13.4.1 连接核实阶段

13.4.2 请求核实阶段

13.5 综合案例——综合管理用户权限

13.6 专家解惑

13.7 经典习题

第14章 数据备份与恢复

14.1 数据备份

14.1.1 使用 MySQLdump 命令备份

14.1.2 直接复制整个数据库目录

14.1.3 使用MySQLhotcopy工具快速备份

14.2 数据恢复

14.2.1 使用MySQL命令恢复

14.2.2 直接复制到数据库目录

14.2.3 MySQLhotcopy 快速恢复

14.3 数据库迁移

14.3.1 相同版本的MySQL数据库之间的迁移

14.3.2 不同版本的MySQL数据库之间的迁移

14.3.3 不同数据库之间的迁移

14.4 表的导出和导入

14.4.1 使用 SELECTI...INTO OUTFILE 导出文本文件

14.4.2 使用 MySQLdump命令导出文本文件

14.4.3 使用 MySQL命令导出文本文件

14.4.4 使用LOAD DATA INFILE方式导入文本文件

14.4.5 使用MySQLimport命令导入文本文件

14.5 综合案例——数据的备份与恢复

14.6 专家解惑

14.7 经典习题

第15章MySQL日志

15.1 日志简介

15.2 二进制曰志

15.2.1 启动和设置二进制日志

15.2.2 查看二进制曰志

15.2.3 删除二进制日志

15.2.4 使用二进制日志恢复数据库

15.2.5 暂时停止二进制日志功能

15.3 错误曰志

15.3.1 启动和设置错误日志

15.3.2 査看错误曰志

15.3.3 删除错误日志

15.4 通用日志查询

15.4.1 启动和设置通用查询日志

15.4.2 查看通用査询日志

15.4.3 删除通用查询曰志

15.5 慢查询日志

15.5.1 启动和设置慢查询曰志

15.5.2 查看慢查询曰志

15.5.3 删除慢查询日志

15.6.综合案例——MySQL日志的综合管理

15.7 专家解惑

15.8 经典习题

第16章 性能优化

16.1 优化简介

16.2 优化查询

16.2.1 分析查询语句

16.2.2 索引对查询速度的影响

16.2.3 使用索引查询

16.2.4 优化子查询

16.3 优化数据库结构

16.3.1 将字段很多的表分解成多个表

16.3.2 增加中间表

16.3.3 增加冗余字段

16.3.4 优化插入记录的速度

16.3.5 分析表、检查表和优化表

16.4 优化MySQL服务器

16.4.1 优化服务器硬件

16.4.2 优化MySQL的参数

16.5 综合案例——全面优化MySQL服务器

16.6 专家解惑

16.7 经典习题

第13章 MySQL用户管理

MySQL是一个多用户数据库,具有功能强大的访问控制系统,可以为不同用户指定允许的权限.MySQL用户可以分为普通用户和root用户.root用户是超级管理员,拥有所有权限,包括创建用户、删除用户和修改用户的密码等管理权限;普通用户只拥有被授予的各种权 限.用户管理包括管理用户账户、权限等.本章将向读者介绍MySQL用户管理中的相关知识点,包括:权限表、账户管理和权限管理.

13.1权限表

MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在MySQL数据库中,由MySQL install db脚本初始化.存储账户权限信息表主要有:user、db、host、tables_priv、columns_priv和procs_priv.本节将为读者介绍这些表的内容和作用.

13.1.1 user 表

user表是MySQL中最重要的一个权限表,记录允许连接到服务器的账号信息,里面的权限是全局级的.

1.用户列
user表的用户列包括Host、User、Password,分别表示主机名、用户名和密码.其中User 和Host为User表的联合主键.当用户与服务器之间建立连接时,输入的账户信息中的用户名称、主机名和密码必须匹配User表中对应的字段,只有3个值都匹配的时候,才允许连接建立.这3个字段的值就是创建账户时保存的账户信息.修改用户密码时,实际就是修改user表的Password字段的值.

2.权限列
权限列的字段决定了用户的权限,描述了在全局范围内允许对数据和数据库进行的操作.包括查询权限、修改权限等普通权限,还包括了关闭服务器、超级权限和加载用户等高级权限.普通权限用于操作数据库;高级权限用于数据库管理.
user表中对应的权限是针对所有用户数据库的.这些字段值的类型为ENUM,可以取的值只能为Y和N,Y表示该用户有对应的权限;N表示用户没有对应的权限.查看user表的结构可以看到,这些字段的值默认都是N.如果要修改权限,可以使用GRANT语句或UPDATE语句更改user表的这些字段来修改用户对应的权限.

3.安全列
安全列只有6个字段,其中两个是ssl相关的,两个是X509相关的,另外两个是授权插件相关的.ssl用于加密;x509标准可用于标识用户;Plugin字段标识可以用于验证用户身份的插件,如果该字段为空,服务器使用内建授权验证机制验证用户身份.读者可以通过SHOW VARIABLES LIKE ’have_openssl'语句来查询服务器是否支持ssl功能.

4.资源控制列
资源控制列的字段用来限制用户使用的资源,包含4个字段,分别为:

• max questions:用户每小时允许执行的查询操作次数.
• max updates:用户每小时允许执行的更新操作次数.
• max connections:用户每小时允许执行的连接操作次数.
• max user connections:用户允许同时建立的连接次数.

一个小时内用户查询或者连接数量超过资源控制限制,用户将被锁定,直到下一个小时才可以在此执行对应的操作.可以使用GRANT语句更新这些字段的值.

13.1.2 db 表和 host 表

db表和host表是MySQL数据中非常重要的权限表.db表中存储了用户对某个数据库的操作权限,决定用户能从哪个主机存取哪个数据库.host表中存储了某个主机对数据库的操作 权限,配合db权限表对给定主机上数据库级操作权限做更细致的控制.这个权限表不受 GRANT和REVOKE语句的影响.db表比较常用,host表一般很少使用.db表和host表结构 相似,字段大致可以分为两类:用户列和权限列.

1.用户列
db表用户列有3个字段,分别是Host、User、Db,标识从某个主机连接某个用户对某个数据库的操作权限,这3个字段的组合构成了db表的主键.host表不存储用户名称,用户列只有2个字段,分别是Host和Db,表示从某个主机连接的用户对某个数据库的操作权限,其主键包括Host和Db两个字段.host很少用到,一般情况下db表就可以满足权限控制需求了.

2.权限列
db表和host表的权限列大致相同,表中create_routine_priv和alter_routine_priv这两个字 段表明用户是否有创建和修改存储过程的权限.
user表中的权限是针对所有数据库的,如果希望用户只对某个数据库有操作权限,那么需要将user表中对应的权限设置为N,然后在db表中设置对应数据库的操作权限.例如,有一个名称为Zhangting的用户分别从名称为large.domain.com和small.domain.com的两个主机连接到数据库,并需要操作books数据库.这时,可以将用户名称Zhangting添加到db表中,而 db表中的host字段值为空,然后将两个主机地址分别作为两条记录的host字段值添加到host表中,并将两个表的数据库字段设置为相同的值books.当有用户连接到MySQL服务器时,db表中没有用户登录的主机名称,则MySQL会从host表中查找相匹配的值,并根据查询的 结果决定用户的操作是否被允许.

13.1.3 tables_priv 表和 columns_priv 表

tables_priv表用来对表设置操作权限,columns_priv表用来对表的某一列设置权限.

tables_priv 表有 8 个字段,分别是 Host、Db、User、Table name、Grantor、Timestamp、 Table_priv和Column_priv,各个字段说明如下:

• Host、Db、User和Table_name 4个字段分表示主机名、数据库名、用户名和表名.
• Grantor表示修改该记录的用户.
• Timestamp字段表示修改该记录的时间.
• Table_priv 表示对表的操作权限,包括 Select、Insert、Update、Delete、Create、Drop、Grant、References、Index 和 Alter 等.
• Column_priv字段表示对表中的列的操作权限,包括Select.Insert、Update和References.

columns_priv 表只有 7 个字段,分别是 Host、Db、User、Table_name、Column name、Timestamp、Column_priv.其中,Column name用来指定对哪些数据列具有操作权限.

13.1.4 procs_priv 表

procsjpriv表可以对存储过程和存储函数设置操作权限.

procs_priv 表包含 8 个字段,分别是 Host DK User Routine name^ Routine type.Granton Proc_priv和Timestamp,各个字段的说明如下:

• Host、Db和User字段分别表示主机名、数据库名和用户名.Routine name表示存 储过程或函数的名称.
• Routine type表示存储过程或函数的类型.Routine type字段有两个值,分别是 FUNCTION和PROCEDURE.FUNCTION表示这是一个函数;PROCEDURE表示这是一个 存储过程.
• Grantor是插入或修改该记录的用户.
• Proc_priv 表示拥有的权限,包括 Execute、Alter Routine、Grant 3 种.
• Timestamp表示记录更新时间.

13.2 账户管理

MySQL提供许多语句用来管理用户账号,这些语句可以用来管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容.MySQL数据库的安全性,需要 通过账户管理来保证.本节将介绍MySQL中如何对账户进行管理.

13.2.1登录和退出MySQL服务器

读者己经知道登录MySQL时,使用MySQL命令并在后面指定登录主机以及用户名和密 码.本小节将详细介绍MySQL命令的常用参数以及登录、退出MySQL服务器的方法.
通过MySQL -help命令可以查看MySQL命令帮助信息.MySQL命令的常用参数如下:

• -h主机名,可以使用该参数指定主机名或ip,如果不指定,默认是localhosto
• -u用户名,可以使用该参数指定用户名.
• -p密码,可以使用该参数指定登录密码.如果该参数后面有一段字段,则该段字符串将作为用户的密码直接登录.如果后面没 有内容,则登录的时候会提示输入密码.注意:该参数后面的字符串和-P之前不能有空格.
• -P端口号,该参数后面接MySQL服务器的端口号,默认为3306.
• 数据库名,可以在命令的最后指定数据库名.
• -e执行SQL语句.如果指定了该参数,将在登录后执行-e后面的命令或SQL语句并退出.

13.2.2新建普通用户

创建新用户,必须有相应的权限来执行创建操作.在MySQL数据库中,有两种方式创建 新用户:一种是使用CREA TE USER或GRANT语句;另一种是直接操作MySQL授权表.最 好的方法是使用GRANT语句,因为这样更精确,错误少.下面分别介绍创建普通用户的方法.

1.使用CREATE USER语句创建新用户
执行CREA TE USER或GRANT语句时,服务器会修改相应的用户授权表,添加或者修 改用户及其权限.
CREATE USER语句的基本语法格式如下:

CREATE USER user_specification [,user一specification]...
user 一specification:user@host
    [
        IDENTIFIED BY [PASSWORD] 'password'
          | IDENTIFIED WITH auth_plugin [AS »auth_string,] 
    ]

• user表示创建的用户的名称;
• host表示允许登录的用户主机名称;
• IDENTIFIED BY表示用来设置用户的密码;
• [PASSWORD]表示使用哈希 值设置密码,该参数可选;
• [password]表示用户登录时使用的普通明文密码;
• IDENTIFIED WITH语句为用户指定一个身份验证插件;
• auth_plugin是插件的名称,插件的名称可以是一个带单引号的字符串,或者带引号的字符串;
• auth_string是可选的字符串参数该参数将传递给身份验证插件,由该插件解释该参数的意义.

CREATE USER语句会添加一个新的MySQL账户.使用CREATE USER语句的用户,必须有全局的CREATE USER权限或MySQL数据库的INSERT权限.每添加一个用户,CREATE USER语句会在MySQL.user表中添加一条新记录,但是新创建的账户没有任何权限.如果添加的账户已经存在,CREATE USER语句会返回一个错误.

IDENTIFIED WITH 只能在 MySQL 5.5.7 及以上版本中使用.IDENTIFIED BY 和 IDENTIFIED WITH是互斥的,所以对于一个账户来说只能使用一个验证方法.CREATE USER语句的操作会被记录到服务器日志文件或者操作历史文件中,如~/.MySQL_history.这意味着对这些文件有读取权限的人,都可以读取新添加用户的明文密码.

MySQL的某些版本中会引入授权表的结构变化,添加新的特权或功能.每当更新MySQL 到一个新的版本时,应该更新授权表,以确保它们有最新的结构,确认可以使用任何新功能.

2.使用GRANT语句创建新用户
CREATE USER语句可以用来创建账户,通过该语句可以在user表中添加一条新的记录,但是CREATE USER语句创建的新用户没有任何权限,还需要使用GRANT语句赋予用户权限.而GRANT语句不仅可以创建新用户,还可以在创建的同时对用户授权.GRANT还可以 指定账户的其他特点,如使用安全连接、限制使用服务器资源等.使用GRANT语句创建新用 户时必须有GRANT权限.
GRANT语句是添加新用户并授权他们访问MySQL对象的首选方法,GRANT语句的基本语法格式如下:

GRANT privileges ON db.table
TO user@host [IDENTIFIED BY 'password'] [,user [IDENTIFIED BY 'password']]
[WITH GRANT OPTION];

• privileges表示赋予用户的权限类型;
• db.table表示用户的权限所作用的数据库中的表;
• IDENTIFIED BY关键字用来设置密码;
• 'password'表示用户密码;
• WITH GRANT OPTION为可选参数,表示对新建立的用户赋予GRANT权限,即该用户可以对其他用户赋予权限.

3.直接操作MySQL用户表
通过前面的介绍,不管是CREATE USER或者GRANT,在创建新用户时,实际上都是在user表中添加一条新的记录.因此,可以使用INSERT语句向user表中直接插入一条记录来创建一个新的用户.使用INSERT语句,必须拥有对MySQL.user表的INSERT权限.
使用 INSERT语句创建新用户的基本语法格式如下:

INSERT INTO MySQL.user(Host,User,Password,[privilegelist])
VALUES('host','username,,PASSWORD('password'),privilegevaluelist);

• Host、User、Password分别为user表中的主机、用户名称和密码字段;
• privilegelist表示用户的权限,可以有多个权限;
• PASSWORD()函数为密码加密函数;
• privilegevaluelist为对应的权限的值,只能取‘Y’或者'N’.

13.2.3删除普通用户

在MySQL数据库中,可以使用DROP USER语句删除用户,也可以直接通过DELETE 语句从MySQL.user表中删除对应的记录来删除用户.

1.使用DROP USER语句删除用户
DROP USER语句语法如下:DROP USER user [,user];
DROP USER语句用于删除一个或多个MySQL账户.要使用DROP USER,必须拥有 MySQL数据库的全局CREATE USER权限或DELETE权限.使用与GRANT或REVOKE相同的格式为每个账户命名;
使用DROP USER,可以删除一个账户和其权限,操作如下:DROP USER 'user'@'localhost';
第1条语句可以删除user在本地登录权限;第2条语句可以删除来自所有授权表的账户权限记录.

2.使用DELETE语句删除用户
DELETE语句基本语法格式如下:DELETE FROM MySQL.user WHERE host-* hostname * and user-* username1;
host和user为user表中的两个字段,两个字段的组合确定所要删除的账户记录.

13.2.4 root用户修改自己的密码

root用户的安全对于保证MySQL的安全非常重要,因为root用户拥有很高的权限.修改root用户密码的方式有多种,本小节将介绍几种常用的修改root用户密码的方法.

1.使用MySQLadmin命令在命令行指定新密码
MySQLadmin命令的基本语法格式如下:mysqladmin -u username -h localhost -p password "newpwd"

• username为要修改密码的用户名称,在这里指定为root用户;
• 参数-h指需要修改的、对应哪个主机用户的密码,该参数可以不写,默认是localhost;
• -p表示输入当前密码;
• password 为关键字,后面双引号内的内容“newpwd”为新设置的密码.

执行完上面的语句,root用户 的密码将被修改为newpwd.

2.修改MySQL数据库的user表
因为所有账户信息都保存在user表中,因此可以通过直接修改user表来改变root用户的密码.root用户登录到MySQL服务器后,使用UPDATE语句修改MySQL数据库的user表 的password字段,从而修改用户的密码.
使用UPDATA语句修改root用户密码的语句如下:UPDATE mysql.user set Password=PASSWORD("rootpwd"〉 WHERE user='root’ and Host=‘localhost’;
PASSWORD()函数用来加密用户密码.
执行UPDATE语句后,需要执行FLUSH PRIVILEGES语句重新加载用户权限.

3.使用SET语句修改root用户的密码
SET PASSWORD语句可以用来重新设置其他用户的登录密码或者自己使用的账户的密码.
使用SET语句修改自身密码的语法结构如下:Set passwword=password('rootpwwd');
新密码必须使用PASSWORD()函数加密.

13.2.5 root用户修改普通用户的密码

root用户拥有很高的权限,不仅可以修改自己的密码,还可以修改其他用户的密码.root 用户登录MySQL服务器后,可以通过SET语句修改MySQL.user表,以及GRANT语句修改用户的密码.本小节将向读者介绍root用户修改普通用户密码的方法.

1.使用S ET语句修改普通用户的密码
使用SET语句修改普通用户密码的语法格式如下:SET PASSWORD FOR 'user'@'host' = PASSWORD('somepassword');
只有root可以通过更新MySQL数据库的用户来更改其他用户的密码.
如果使用普通用户修改,可省略FOR子句更改自己的密码:SET PASSWORD:PASSWORD('somepassword');

2.使用UPDATE语句修改普通用户的密码
使用root用户登录到MySQL服务器后,可以使用UPDATE语句修改MySQL数据库的user表的password字段,从而修改普通用户的密码.
使用UPDATA语句修改用户密码的语法,如下:UPDATE MySQL.user SET Password=PASSWORD("pwd")WHERE User="username" AND Host="hostnaine";
PASSWORD()函数用来加密用户密码.
执行UPDATE语句后,需要执行FLUSH PRIVILEGES语句重新加载用户权限.

3.使用GRANT语句修改普通用户密码
除了前面介绍的方法,还可以在全局级别使用GRANT USAGE语句(.)指定某个账户的 密码而不影响账户当前的权限,使用GRANT语句修改密码,必须拥有GRANT权限.
一般情况下最好使用该方法来指定或修改密码:GRANT USAGE ON *.* TO 'someuser'@%' IDENTIFIED BY 'somepassword';

13.2.6普通用户修改密码

普通用户登录MySQL服务器后,通过SET语句设置自己的密码.
SET语句修改自己密码的基本语法如下:SET PASSWORD = PASSWORD('pwd');
其中,PASSWORD()函数对密码进行加密,“pwd”是设置的新密码.

13.2.7 root用户密码丢失的解决办法

对于root用户密码丢失这种特殊情况,MySQL实现了对应的处理机制.可以通过特殊方法登录到MySQL服务器,然后在root用户下重新设置密码.执行步骤如下:
1.使用--skip-grant-tables选项启动MySQL服务
以skip-grant-tables选项启动时,MySQL服务器将不加载权限判断,任何用户都能访问数据库.在Windows操作系统中,可以使用MySQLd或MySQLd-nt来启动MySQL服务进程.
如果MySQL的目录已经添加到环境变量中,可以直接使用MySQLd、MySQLd-nt命令启动MySQL服务.否则需要先在命令行下切换到MySQL的bin目录.
MySQLd命令如下:mysqld --skip-grant-tables
MySQLd-nt命令如下:mysqld-nt --skip-grant-tables
在Linux操作系统中,使用 MySQLd_safe来启动 MySQL服务.也可以使用 /etc/init.d/MySQL 命令来启动 MySQL 服务.
MySQLd-safe命令如下:mysqld—safe --skip-grant-tables user=mysql
/etc/init.d/MySQL 命令如下:/etc/init.d/mysql start-mysqld --skip-grant-tables
启动MySQL服务后,就可以使用root用户登录了.

2.使用root用户登录,重新设置密码
在这里使用的平台为Windows 7,操作步骤如下:

• 使用net stop MySQL命令停止MySQL服务进程.
• 在命令行输入MySQLd --skip-grant-tables选项启动MySQL服务.
• 登录成功以后,可以使用UPDA TE语句或者使用MySQLadmin命令重新设置root密码
• 修改密码完成后,必须使用FLUSH PRIVILEGES语句加载权限表.加载权限表后,新的 密码才会生效,同时MySQL服务器开始权限验证.
• 修改密码完成后,将输入MySQLd-skip-grant-tables命令的命令行窗口关闭,接下来就可 以使用新设置的密码登录MySQL了.

13.3权限管理

权限管理主要是对登录到MySQL的用户进行权限验迅所有用户的权限都存储在MySQL的权限表中,不合理的权限规划会给MySQL服务器带来安全隐患.数据库管理员要对所有用户的权限进行合理规划管理.MySQL权限系统的主要功能是证实连接到一台给定主机的用户,并且赋予该用户在数据库上的SELECT、INSERT、UPDATE和DELETE权限.本节将为读者 介绍MySQL权限管理的内容.

13.3.1 MySQL的各种权限

账户权限信息被存储在MySQL数据库的user、db、host、tables_priv、columns_priv和procs_priv表中.在MySQL启动时,服务器将这些数据库表中权限信息的内容读入内存.

GRANT和REVOKE语句中可以使用的权限

(1)CREATE和DROP权限,可以创建新数据库和表,或删除(移掉)已有数据库和表.如果将MySQL数据库中的DROP权限授予某用户,用户可以删掉MySQL访问权限保存的数 据库.
(2)SELECT.INSERT,UPDATE和DELETE权限允许在一个数据库现有的表上实施 操作.
(3)SELECT权限只有在它们真正从一个表中检索行时才被用到.
(4)INDEX权限允许创建或删除索引,INDEX适用己有表.如果具有某个表的CREATE 权限,可以在CREATE TABLE语句中包括索引定义.
(5)ALTER权限,可以使用ALTER TABLE来更改表的结构和重新命名表.
(6)CREATE ROUTINE权限来创建保存的程序(函数和程序),ALTER ROUTINE权 限用来更改和删除保存的程序,EXECUTE权限用来执行保存的程序.
(7)GRANT权限允许授权给其他用户.可用于数据库、表和保存的程序.
(8)FILE 权限给予用户使用 LOAD DATA INFILE 和 SELECT...IN TO OUTFILE 语句读 或写服务器上的文件,任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件.(说明用户可以读任何数据库目录下的文件,因为服务器可以访问这些文件).FILE权限允 许用户在MySQL服务器具有写权限的目录下创建新文件,但不能覆盖已有文件.

其余的权限用于管理性操作,它使用MySQLadmin程序或SQL语句实施.

不同权限下可以使用的MySQLadmin命令

(1)reload命令告诉服务器将授权表重新读入内存;flush-privileges是reload的同义词; refresh命令清空所有表并关闭/打开记录文件;其他flush-xxx命令执行类似refresh的功能,但是范围更有限,并且在某些情况下可能更好用.例如,如果只是想清空记录文件,flush-logs 是比refresh更好的选择.
(2)shutdown命令关掉服务器.只能从MySQLadmin发出命令.
(3)processlist命令显示在服务器内执行的线程的信息(即其他账户相关的客户端执行的 语句).kill命令杀死服务器线程.用户总是能显示或杀死自己的线程,但是需要PROCESS 权限来显示或杀死其他用户和SUPER权限启动的线程.
(4)kill命令能用来终止其他用户或更改服务器的操作方式.总的来说,只授予权限给需要他们的那些用户.

13.3.2授权

授权就是为某个用户授予权限.合理的授权可以保证数据库的安全.MySQL中可以使用 GRANT语句为用户授予权限.
授予的权限可以分为多个层级:

• 1.全局层级
  全局权限适用于一个给定服务器中的所有数据库.这些权限存储在MySQL.user表中.GRANT ALL ON .和REVOKE ALL ON .只授予和撤销全局权限.
• 2.数据库层级
  数据库权限适用于一个给定数据库中的所有目标.这些权限存储在MySQL.db和MySQL.host 表中.GRANT ALL ON db_name.和 REVOKE ALL ON db name.*只授予和撤销数据库权限.
• 3.表层级
  表权限适用于一个给定表中的所有列.这些权限存储在MySQL.talbes_priv表中.GRANT ALL ON db name.tbl name 和 REVOKE ALL ON db_name.tbl_name 只授予和撤销表权限.
• 4.列层级
  列权限适用于一个给定表中的单一列.这些权限存储在MySQL.columns_priv表中.当使用REVOKE时,必须指定与被授权列相同的列.
• 5.子程序层级
  CREATE ROUTINE.ALTER ROUTINE,EXECUTE 和 GRANT 权限适用于已存储的子 程序.这些权限可以被授予为全局层级和数据库层级.而且,除了 CREATE ROUTINE夕卜,这些权限可以被授予子程序层级,并存储在MySQL.procs_priv表中.

在MySQL中,必须是拥有GRANT权限的用户才可以执行GRANT语句.

要使用GRANT或REVOKE,必须拥有GRANT OPTION权限,并且必须用于正在授予 或撤销的权限.GRANT的语法如下:

GRANT priv_type [(columns)] [,priv_type [(columns)]]...ON(obj ect_type] tab lei,table2z...,tablen
TO user [工DENTIFIED BY [PASSWORD] 'password?]
[,user [IDENTIFIED BY [PASSWORD] password1]] [WITH GRANT OPTION]
object一type « TABLE I FUNCTION | PROCEDURE

其中,priv_type参数表示权限类型;columns参数表示权限作用于哪些列上,不指定该参数,表示作用于整个表;table 1,table2,..tablen表示授予权限的列所在的表;object type指定 授权作用的对象类型包括TABLE(表)、FUNCTION(函数)和PROCEDURE(存储过程),当从旧版本的MySQL升级时,要使用object tpye子句,必须升级授权表;user参数表示用户 账户,由用户名和主机名构成,形式是“'usemame’@'hostname'” ; IDENTIFIED BY参数用于 设置密码.
WITH关键字后可以跟一个或多个GRANT OPTION.GRANT OPTION的取值有5个,意义如下:
(1)GRANT OPTION:被授权的用户可以将这些权限赋予别的用户.
(2)MAX_QUERIES_PER_HOURcount:设置每个小时可以执行count次查询.
(3)MAX_UPDATES_PER_HOURcount:设置每小时可以执行count次更新.
(4)MAX_CONNECTIONS_PER_HOUR count:设置每小时可以建立 count 个连接.
(5)MAX_USER_CONNECTIONS count:设置单个用户可以同时建立count个连接.

13.3.3收回权限

收回权限就是取消已经赋予用户的某些权限.收回用户不必要的权限可以在一定程度上保证系统的安全性.MySQL中使用REVOKE语句取消用户的某些权限.使用REVOKE收回权限之后,用户账户的记录将从db、host、tables_priv和columns_priv表中删除,但是用户账号 记录仍然在user表中保存(删除user表中的账户记录,使用DROP USER语句,在13.2.3节已经介绍).在将用户账户从user表删除之前,应该收回相应用户的所有权限,REVOKE语句有两种语法格式,第一种语法是收回所有用户的所有权限,此语法用于取消对于己命名的用户的所有全局层级、数据库层级、表层级和列层级的权限,其语法如下:REVOKE ALL PRIVILEGES,GRANT OPTION FROM,user,@»host'[,’user’(”hostf...]

REVOKE语句必须和FROM语句一起使用,FROM语句指明需要收回权限的账户.
另一种为长格式的REVOKE语句,基本语法如下:
REVOKE priv一type [(columns)] [f priv一type [(columns)]] ON tablel,table2,tablen
FROM 'user * @1 host1[,1 user1@ 'host *...]
该语法收回指定的权限.其中,priv_type参数表示权限类型;columns参数表示权限作用 于哪些列上,如果不指定该参数,表示作用于整个表;table 1,table2,...,tablen表示从哪个表中 收回权限;'user’@’host’参数表示用户账户,由用户名和主机名构成.
要使用REVOKE语句,必须拥有MySQL数据库的全局CREATE USER权限或UPDATE权限.

13.3.4查看权限

SHOW GRANTS语句可以显示指定用户的权限信息
使用SHOW GRANTS查看账户信息的基本语法格式如下:SHOW GRANTS FOR 'user'@'host';
其中,user表示登录用户的名称,host表示登录的主机名称或者IP地址.在使用该语句时,要确保指定的用户名和主机名都要用单引号括起来,并使用符号,将两个名字分隔开.

13.4访问控制

正常情况下,并不希望每个用户都可以执行所有的数据库操作.当MySQL允许一个用户 执行各种操作时,它将首先核实该用户向MySQL服务器发送的连接请求,然后确认用户的操 作请求是否被允许.本小节将向读者介绍MySQL中的访问控制过程.MySQL的访问控制分 为两个阶段:连接核实阶段和请求核实阶段.

13.4.1连接核实阶段
当连接MySQL服务器时,服务器基于用户的身份以及用户是否能通过正确的密码身份验
证来接受或拒绝连接.即客户端用户连接请求中会提供用户名称、主机地址名和密码,MySQL 使用user表中的3个字段(Host、User和Password)执行身份检查,服务器只有在user表记 录的Host和User字段匹配客户端主机名和用户名,井且提供正确的密码时才接受连接.如果 连接核 实没有 通过,服务器 完全拒 绝访问 ;否则,服务 器接受 连接,然后进 入阶段 2等待用 户 请求.

13.4.2请求核实阶段
建立连接之后,服务器进入访问控制的阶段2.对在此连接上的每个请求,服务器检查用户要执行的操作,然后检查是否有足够的权限来执行它.这正是在授权表中的权限列发挥作用的地方.这些权限可以来自user、db、host、tables_priv或columns_priv表.
确认权限时,MySQL首先检查user表,如果指定的权限没有在user表中被授权;MySQL 将检查db表,db表是下一安全层级,其中的权限限定于数据库层级,在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据;如果在该层级没有找到限定的权限,则MySQL继续检查tables_priv表以及columns_priv表,如果所有权限表都检查完毕,但还是没有找到允许的权限操作,MySQL将返回错误信息,用户请求的操作不能执行,操作失败.

13.5 综合案例--综合管理用户权限

13.6 专家解惑

疑问1:已经将一个账户的信息从数据库中完全删除,为什么该用户还能登录数据库?
出现这种情况的原因可能有多种,最有可能的是在user数据表中存在匿名账户.在user表中匿名账户的User字段值为空字符串,这会允许任何人连接到数据库,检测是否存在匿名 登录用户的方法是,输入以下语句:
SELECT * FROM user WHERE User=";
如果有记录返回,则说明存在匿名用户,需要删除该记录,以保证数据库的访问安全,删除语句为:
DELETE FROM user WHERE user='';
这样一来,该账户肯定不能登录MySQL服务器了.

疑问2:应该使用哪种方法创建用户?
本章介绍了创建用户的几种方法:GRANT语句、CREATE USER语句和直接操作user表.一般情况,最好使用GRANT或者CREATE USER语句,而不要直接将用户信息插入user表,因为user表中存储了全局级别的权限以及其他的账户信息,如果意外破坏了user表中的记录,则可能会对MySQL服务器造成很大影响.

第14章 数据备份与恢复

尽管采取了一些管理措施来保证数据库的安全,但是不确定的意外情况总是有可能造成数据的损失,例如意外的停电、管理员不小心的操作失误都可能会造成数据的丢失.保证数据安全的最重要的一个措施是确保对数据进行定期备份.如果数据库中的数据丢失或者出现错误,可以使用备份的数据进行恢复,这样就尽可能地降低了意外原因导致的损失.MySQL提供了多种方法对数据进行备份和恢复.本章将介绍数据备份、数据恢复、数据迁移和数据导入导出的相关知识.

14.1 数据备份

数据备份是数据库管理员非常重要的工作之一.系统意外崩溃或者硬件的损坏都可能导致数据库的丢失,因此MySQL管理员应该定期地备份数据库,使得在意外情况发生时,尽可能减少损失.

14.1.1使用MySQLdump命令备份

MySQLdump是MySQL提供的一个非常有用的数据库备份工具.MySQLdump命令执行时,可以将数据库备份成一个文本文件,该文件中实际上包含了多个CREATE和INSERT语句,使用这些语句可以重新创建表和插入数据.
MySQLdump备份数据库语句的基本语法格式如下:
mysqldump -u user -h host -p password dbname [tbname,[tbname...] ] > filename.sql

• user表示用户名称;
• host表示登录用户的主机名称;
• password为登录密码;
• dbname为需要备份的数据库名称;
• tbname为dbname数据库中需要备份的数据表,可以指定多个需要备份的表;
• 右箭头符号“>”告诉MySQLdump将备份数据表的定义和数据写入备份文件;
• filename.sql为备份文件的名称.

1.使用MySQLdump备份单个数据库中的所有表
mysqldump -u root -p booksdb > C:/backup/booksdb_20160301.sql

2.使用MySQLdump备份数据库中的某个表
在前面MySQLdump语法中介绍过,MySQLdump还可以备份数据库中的某个表,其语法格式为:
mysqldump -u user -h host -p dbname [tbname,[tbname...]] > filename.sql
tbname表示数据库中的表名,多个表名之间用空格隔开.
备份表和备份数据库中所有表的语句中不同的地方在于,要在数据库名称dbname之后指定需要备份的表名称.

3.使用MySQLdump备份多个数据库
如果要使用MySQLdump备份多个数据库,需要使用-databases参数.备份多个数据库的语句格式如下:
mysqldump -u user -h host -p --databases [dbname,[dbname...]] > filename.sql
使用-databases参数之后,必须指定至少一个数据库的名称,多个数据库名称之间用空格隔开.
另外,使用-all-databases参数可以备份系统中所有的数据库,语句如下:
mysqldump 一u user -h host -p --all-databases > filename.sql
使用参数-all-databases时,不需要指定数据库名称.

如果在服务器上进行备份,并且表均为MylSAM表,应考虑使用MySQLhotcopy,因为可以更快地进行备份和恢复.

MySQLdump还有一些其他选项可以用来制定备份过程,例如-opt选项,该选项将打开 --quick、-add-locks、--extended-insert等多个选项.使用-opt选项可以提供最快速的数据库转储.

MySQLdump其他常用选项如下:

• --add-drop-database:在每个 CREATE DATABASE 语句前添加 DROP DATABASE 语 句.
• -add-drop-tables:在每个 CREATE TABLE 语句前添加 DROP TABLE 语句.
• -add-locking:用LOCK TABLES和UNLOCK TABLES语句引用每个表转储.重载转储文件时插入得更快.
• --all-database,-A:转储所有数据库中的所有表.与使用-database选项相同,在命令行中命名所有数据库.
• --comments[=0|1]:如果设置为0,禁止转储文件中的其他信息,例如程序版本、服务器版本和主机.
• --skip-comments与-—comments=0的结果相同.默认值为1,即包括额外信息.
• --compact:产生少量输出.该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、-—skip-disable-keys 和--skip-add-locking 选项.
• --compatible=name:产生与其他数据库系统或旧的MySQL服务器更兼容的输出.值可以为 ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options,no_tables_options 或者 no_field_options.
• --complete-insert,-c:使用包括列名的完整的INSERT语句.
• --debug[=debug_options],-# [debug options]:写调试日志.
• --delete,-D导入文本文件前清空表.
• --default-character-set=charset:使用 charsetas默认字符集.如果没有指定,MySQLdump 使用utfS.
• --delete-master-logs:在主复制服务器上,完成转储操作后删除二进制日志.该选项自 动启用-master-data.
• --extended-insert,-e:使用包括几个VALUES列表的多行INSERT语法.这样使转储 文件更小,重载文件时可以加速插入.
• --flush-logs,-F:开始转储前刷新MySQL服务器日志文件.该选项要求RELOAD权 限.
• --force,-f:在表转储过程中,即使出现SQL错误也继续.
• --lock-all-tables,-x:对所有数据库中的所有表加锁.在整体转储过程中通过全局锁定 来实现.该选项自动关闭--single-transaction和--lock-tables.
• --lock-tables,-1:开始转储前锁定所有表.用READ LOCAL锁定表以允许并行插入 MylSAM表.对于事务表(例如InnoDB和BDB:,--single-transaction是一个更好的 选项,因为它根本不需要锁定表.
• --no-create-db,-n:该选项禁用 CREATE DATABASE /!32312 IF NOT EXISTS/ db name语句,如果给出—database或—all—database选项,则包含到输出中.
• --no-create-infb,-t:只导出数据,而不添力口 CREATE TABLE it句.
• --no-data,-d:不写表的任何行信息,只转储表的结构.
• --opt:该选项是速记,等同于指定--add-drop-tables—add-locking,—-create-option,--disable-keys-extended-insert、--lock-tables-quick 和--set-charset.它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件.该选项默认开启,但可以用--skip-opt禁用.要想禁用使用-opt启用的选项,可以使用-skip形式,例如 —skip-add-drop-tables 或一skip-quick.
• --password[=password],-p[password]:当连接服务器时使用的密码.如果使用短选项 形式(-p),选项和密码之间不能有空格.如果在命令行中—password或-p选项后面没 有密码值,则提示输入一个密码.
• --port=port_num,-P port num:用于连接的 TCP/IP 端 口号.
• --protocol{TCP | SOCKET | PIPE | MEMORY}:使用的连接协议.
• --replace,-r -replace和-ignore:控制替换或复制唯一键值已有记录的输入记录的处 理.如果指定—replace,新行替换有相同的唯一键值的已有行;如果指定—ignore,复制已有的唯一键值的输入行被跳过.如果不指定这两个选项,当发现一个复制键值时会出现一个错误,并且忽视文本文件的剩余部分.
• --silent,-s:沉默模式.只有出现错误时才输出.
• --socket=path,-S path:当连接localhost时使用的套接字文件(为默认主机).
• --user=user_name,-u username:当连接服务器时MySQL使用的用户名.
• --verbose,-v:冗长模式.打印出程序操作的详细信息.
• --version,-V:显示版本信息并退出.
• --xml,-X:产生 XML 输出.

MySQLdump提供许多选项,包括用于调试和压缩的,在这里只是列举最有用的.运行帮助命令MySQLdump -help,可以获得特定版本的完整选项列表.

如果运行MySQLdump没有-quick或-opt选项,MySQLdump在转储结果前将整个结果集装入内存.如果转储大数据库可能会出现问题,该选项默认启用,但可以用--skip-opt禁用.如果使用最新版本的MySQLdump程序备份数据,并用于恢复到比较旧版本的 MySQL服务器中,则不要使用-opt或-e选项.

14.1.2直接复制整个数据库目录

因为MySQL表保存为文件方式,所以可以直接复制MySQL数据库的存储目录及文件进行备份.MySQL的数据库目录位置不一定相同,在Windows平台下,MySQL 5.7存放数据库 的目录通常默认为 “C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.7\data”或者其他用户自定义目录;在Linux平台下,数据库目录位置通常为 /var/lib/MySQL/,不同Linux版本下目录会有不同,读者应在自己使用的平台下査找该目录.
这是一种简单、快速、有效的备份方式.要想保持备份的一致性,备份前需要对相关表执行LOCK TABLES操作,然后对表执行FLUSH TABLES-这样当复制数据库目录中的文件时,允许其他客户继续查询表.需要FLUSH TABLES语句来确保开始备份前将所有激活的索引页 写入硬盘.当然,也可以停止MySQL服务再进行备份操作.
这种方法虽然简单,但并不是最好的方法.因为这种方法对InnoDB存储引擎的表不适用.使用这种方法备份的数据最好恢复到相同版本的服务器中,不同的版本可能不兼容.
在MySQL版本号中,第一个数字表示主版本号,主版本号相同的MySQL数据库文件格式相同.

14.1.3使用MySQLhotcopy工具快速备份

MySQLhotcopy是一个Perl脚本,最初由Tim Bunce编写并提供.它使用LOCK TABLES.FLUSH TABLES和cp或scp来快速备份数据库.它是备份数据库或单个表的最快的途径,但 它只能运行在数据库目录所在的机器上,并且只能备份MylSAM类型的表.MySQLhotcopy 在Unix系统中运行.
MySQLhotcopy命令语法格式如下:
mysqlhotcopy db_name_1,...db_name_n /path/to/new_directory
db_name_1,...,db_name_n分别为需要备份的数据库的名称; /path/to/new_directory指定备份文件目录.

MySQLhotcopy只是将表所在的目录复制到另一个位置,只能用于备份MylSAM和 ARCHIVE表.备份InnoDB类型的数据表时会出现错误信息.由于它复制本地格式的文件,故也不能移植到其他硬件或操作系统下.

14.2 数据恢复

管理人员操作的失误、计算机故障以及其他意外情况,都会导致数据的丢失和破坏.当数据丢失或意外破坏时,可以通过恢复已 经备份的数据尽量减少数据丢失和破坏造成的损失.本节将介绍数据恢复的方法.

14.2.1使用MySQL命令恢复

对于已经备份的包含CREATE.INSERT语句的文本文件,可以使用MySQL命令导入到数据库中.本小节将介绍MySQL命令导入SQL文件的方法.

备份的sql文件中包含CREATE.INSERT语句(有时也会有DROP语句).MySQL命令可以直接执行文件中的这些语句.其语法如下:
mysql -u user -p [dbname] < filename.sql

• user是执行backup.sql中语句的用户名;
• -p表示输入用户密码;
• dbname是数据库名.如果filename.sql文件为MySQLdump工具创建的包含创建数据库语句的文件,执行的时候不需要指定数据库名.

如果已经登录MySQL服务器,还可以使用source命令导入SQL文件.source语句语法如下:source filename

执行source命令前,必须使用use语句选择数据库.不然,恢复过程中会出现错误信息.

14.2.2直接复制到数据库目录

如果数据库通过复制数据库文件备份,可以直接复制备份的文件到MySQL数据目录下实现恢复,通过这种方式恢复时,必须保存备份数据的数据库和待恢复的数据库服务器的主版本号相同.而且这种方式只对MylSAM引擎的表有效,对于InnoDB引擎的表不可用.

执行恢复以前关闭MySQL服务,将备份的文件或目录覆盖MySQL的data目录,启动 MySQL服务.对于Linux/Unix操作系统来说,复制完文件需要将文件的用户和组更改为 MySQL运行的用户和组,通常用户是MySQL,组也是MySQL.

14.2.3 MySQLhotcopy 快速恢复

MySQLhotcopy备份后的文件也可以用来恢复数据库,在MySQL服务器停止运行时,将备份的数据库文件复制到MySQL存放数据的位置(MySQL的data文件夹i重新启动MySQL 服务即可.如果以根用户执行该操作,必须指定数据库文件的所有者,输入语句如下:
chown -R mysql.mysql /var/lib/mysql/dbname

如果需要恢复的数据库已经存在,则在使用DROP语句删除已经存在的数据库之后,恢复才能成功.另外MySQL不同版本之间必须兼容,恢复之后的数据才可以使用.

14.3 数据库迁移

数据库迁移就是把数据从一个系统移动到另一个系统上.数据迁移有以下原因:

• 需要安装新的数据库服务器.
• MySQL版本更新.
• 数据库管理系统的变更(如从Microsoft SQL Server迁移到MySQL).

14.3.1相同版本的MySQL数据库之间的迁移

相同版本的MySQL数据库之间的迁移就是在主版本号相同的MySQL数据库之间进行数据库移动.迁移过程其实就是在源数据库备份和目标数据库恢复过程的组合.
在讲解数据库备份和恢复时,已经知道最简单的方式是通过复制数据库文件目录,但是此种方法只适用于MylSAM引擎的表.而对于InnoDB表,不能用直接复制文件的方式备份数据库,因此最常用和最安全的方式是使用MySQLdump命令导出数据,然后在目标数据库服务器使用MySQL命令导入.

14.3.2不同版本的MySQL数据库之间的迁移

因为数据库升级等原因,需要将较旧版本的MySQL数据库中的数据迁移到较新版本的数据库中.MySQL服务器升级时,需要先停止服务,然后卸载旧版本,并安装新版的MySQL,这种更新方法很简单,如果想保留旧版本中的用户访问控制信息,则需要备份MySQL中的 MySQL数据库,在新版本MySQL安装完成之后,重新读入MySQL备份文件中的信息.
旧版本与新版本的MySQL可能使用不同的默认字符集,例如MySQL 4.x中大多使用Iatinl 作为默认字符集,而MySQL 5.x的默认字符集为utfS.如果数据库中有中文数据的,迁移过 程中需要对默认字符集进行修改,不然可能无法正常显示结果.
新版本会对旧版本有一定兼容性,从旧版本的MySQL向新版本的MySQL迁移时,对于MyISAM引擎的表,可以直接复制数据库文件,也可以使用MySQLhotcopy工具、MySQLdump工具.对于innoDB引擎的表,一般只能使用MySQLdump将数据导出.然后使用MySQL命令导入到目标服务器上.从新版本向旧版本MySQL迁移数据时要特别小心,最好使用MySQLdump命令导出,然后导入目标数据库中.

14.3.3不同数据库之间的迁移

不同类型的数据库之间的迁移,是指把MySQL的数据库转移到其他类型的数据库,例如从MySQL迁移到ORACLE,从ORACLE迁移到MySQL,从MySQL迁移到SQL Server等.
迁移之前,需要了解不同数据库的架构,比较它们之间的差异.不同数据库中定义相同类型的数据的关键字可能会不同.例如,MySQL中日期字段分为DATE和TIME两种,而 ORACLE日期字段只有DATE.另外,数据库厂商并没有完全按照SQL标准来设计数据库系统,导致不同的数据库系统的SQL语句有差别.例如,MySQL几乎完全支持标准SQL语言,而Microsoft SQL Server使用的是T-SQL语言,T-SQL中有一些非标准的SQL语句,因此在迁移时必须对这些语句进行语句映射处理.
数据库迁移可以使用一些工具,例如在Windows系统下,可以使用MyODBC实现MySQL和SQL Server之间的迁移.MySQL官方提供的工具MySQL Migration Toolkit也可以在不同数据库间进行数据迁移.

14.4 表的导出和导入

有时会需要将MySQL数据库中的数据导出到外部存储文件中,MySQL数据库中的数据可以导出成sql文本文件、xml文件或者html文件.同样这些导出文件也可以导入到MySQL数据库中.本小节将介绍数据导出和导入的常用方法.

14.4.1 使用 SELECTI-INTO OUTFILE 导出文本文件

MySQL数据库导出数据时,允许使用包含导出定义的SELECT语句进行数据的导出操作.该文件被创建到服务器主机上,因此必须拥有文件写入权限(FILE权限),才能使用此语法.“SELECT...INTO OUTFILE 'filename'形式的SELECT语句可以把被选择的行写入一 个文件中,filename不能是一个己经存在的文件.

SELECT...INTO OUTFILE语句基本格式如下:

SELECT columnlist FROM table WHERE condition INTO OUTFILE * filenamer [OPTIONS]
—OPTIONS 选项
FIELDS TERMINATED BY 'value'
FIELDS [OPTIONALLY] ENCLOSED BY 'value' 
FIELDS ESCAPED BY 'value'
LINES STARTING BY 'value'
LINES TERMINATED BY 'value'

可以看到SELECT columnlist FROM table WHERE condition为一个查询语句,查询结果返回满足指定条件的一条或多条记录;
INTO OUTFILE语句的作用就是把前面SELECT语句查询出来的结果导出到名称为“filename”的外部文件中.

[OPTIONS]为可选参数选项,OPTIONS部分的语法包括FIELDS和LINES子句,其可能的取值有:

• FIELDS TERMINATED BY 'value':设置字段之间的分隔字符,可以为单个或多个字符,默认情况下为制表符‘\t’.
• FIELDS [OPTIONALLY] ENCLOSED BY 'value’:设置字段的包围字符,只能为单个字符,如果使用了 OPTIONALLY则只有CHAR和VERCHAR等字符数据字段被包括
• FIELDS ESCAPED BY 'value‘:设置如何写入或读取特殊字符,只能为单个字符,即设置转义字符,默认值为.
• LINES STARTING BY 'value':设置每行数据开头的字符,可以为单个或多个字符,默认情况下不使用任何字符.
• LINES TERMINATED BY 'value’:设置每行数据结尾的字符,可以为单个或多个字符,默认值为‘\n’.
  FIELDS和LINES两个子句都是自选的,但是如果两个都被指定了,FIELDS必须位于LINES的前面.

SELECT...INTO OUTFILE语句可以非常快速地把一个表转储到服务器上.如果想要在服务器主机之外的部分客户主机上创建结果文件,不能使用SELECT...INTO OUTFILEo在这种情况下,应该在客户主机上使用比如“MySQL-e "SELECT..."> file_name”的命令,来生成文件.
SELECT...INTO OUTFILE 是 LOAD DATA INFILE 的补语.用于语句的 OPTIONS 部分的 语法包括部分FIELDS和LINES子句,这些子句与LOAD DATA INFILE语句同时使用.

另外,注意到第5行中有一个字段值为“\N”,这表示该字段的值为NULL.默认情况下,如果遇到NULL值,将会返回“\N”代表空值,反斜线“\”表示转义字符,如果使用ESCAPED BY选项,则N前面为指定的转义字符.

14.4.2使用MySQLdump命令导出文本文件

除了使用SELECT...INTO OUTFILE语句导出文本文件之外,还可以使用MySQLdump命令.本章开始介绍了使用MySQLdu mp备份数据库,该工具不仅可以将数据导出为包含CREATE、INSERT的SQL文件,也可以导出为纯文本文件.

MySQLdump创建一个包含创建表的CREATE TABLE语句的tabiename.sql文件和一个包 含其数据的tablename.txt文件.MySQLdump导出文本文件的基本语法格式如下:
mysqldump -T path -u root -p dbname [tables] [OPTIONS]

--OPTIONS 选项
--fields-terminated-by=value
--fields-enclosed-by^value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value
只有指定了-T参数才可以导出纯文本文件;path表示导出数据的目录;tables为指定要导出的表名称,如果不指定,将导出数据库dbname中所有的表; [OPTIONS]为可选参数选项,这些选项需要结合-T选项使用.使用OPTIONS常见的取值有:

• --fields-terminated-by=value:设置字段之间的分隔字符,可以为单个或多个字符,默认 情况下为制表符“\t”.
• --fields-enclosed-by=value:设置字段的包围字符.
• --fields-optionally-enclosed-by=value:设置字段的包围字符,只能为单个字符,只能包括CHAR和VERCHAR等字符数据字段.
• --fields-escaped-by=value:控制如何写入或读取特殊字符,只能为单个字符,即设置转义字符,默认值为反斜线“\”.
• --lines-terminated-by=value:设置每行数据结尾的字符,可以为单个或多个字符,默认值为“\n”.

14.4.3使用MySQL命令导出文本文件

MySQL是一个功能丰富的工具命令,使用MySQL还可以在命令行模式下执行SQL指令,将查询结果导入到文本文件中.相比MySQLdump,MySQL工具导出的结果可读性更强.

如果MySQL服务器是单独的机器,用户是在一个client上进行操作,用户要把数据结果导入到client机器上.可以使用MySQL -e语句.

使用MySQL导出数据文本文件语句的基本格式如下:
mysql -u root -p --execute="SELECT 语句" dbname > filename.txt
该命令使用-execute选项,表示执行该选项后面的语句并退出,后面的语句必须用双引号括起来,dbname为要导出的数据库名称;
导出的文件中不同列之间使用制表符分隔,第1行包含了各个字段的名称.

使用MySQL命令还可以指定查询结果的显示格式,如果某行记录字段很多,可能一行不能完全显示,可以使用-vartical参数,将每条记录分为多行显示.

MySQL可以将查询结果导出到html文件中,使用-html选项即可.
如果要将表数据导出到xml文件中,可使用-xml选项.

14.4.4 使用LOAD DATA INFILE方式导入文本文件

MySQL允许将数据导出到外部文件,也可以从外部文件导入数据.MySQL提供了一些 导入数据的工具,这些工具有LOAD DATA语句、source命令和MySQL命令.LOAD DATA INFILE语句用于高速地从一个文本文件中读取行,并装入一个表中.文件名称必须为文字字符串.本节将介绍LOAD DATA语句的用法.

LOAD DATA语句的基本格式如下:
LOAD DATA INFILE 'filename.txt' INTO TABLE tablename [OPTIONS] [IGNORE number LINES]

--OPTIONS 选项
FIELDS TERMINATED BY »value*
FIELDS [OPTIONALLY] ENCLOSED BY 'value'
FIELDS ESCAPED BY 'value'
LINES STARTING BY 'value'
LINES TERMINATED BY 'value'

可以看到LOAD DATA语句中,关键字INFILE后面的filename文件为导入数据的来源; tablename表示待导入的数据表名称;[OPTIONS]为可选参数选项,OPTIONS部分的语法包括 FIELDS和LINES子句,其可能的取值有:
• FIELDS TERMINATED BY 'value':设置字段之间的分隔字符,可以为单个或多个字 符,默认情况下为制表符"\t”.
• FIELDS [OPTIONALLY] ENCLOSED BY 'value':设置字段的包围字符,只能为单个字 符.如果使用了 OPTIONALLY,则只有CHAR和VERCHAR等字符数据字段被包括.
• FIELDS ESCAPED BY 'value':控制如何写入或读取特殊字符,只能为单个字符,即 设置转义字符,默认值为“\”.
• LINES STARTING BY 'value':设置每行数据开头的字符,可以为单个或多个字符,默认情况下不使用任何字符.
• LINES TERMINATED BY 'value':设置每行数据结尾的字符,可以为单个或多个字 符,默认值为"\n”.
IGNORE number LINES选项表示忽略文件开始处的行数,number表示忽略的行数.执行 LOAD DATA语句需要FILE权限.

14.4.5使用MySQLimport命令导入文本文件

使用MySQLimport可以导入文本文件,并且不需要登录MySQL客户端.MySQLimport命令提供许多与LOAD DATA INFILE语句相同的功能,大多数选项直接对应LOAD DATA INFILE子句.使用MySQLimport语句需要指定所需的选项、导入的数据库名称以及导入的数 据文件的路径和名称.
MySQLimport命令的基本语法格式如下:mysqlimport -u root -p dbname filename.txt [OPTIONS]

—OPTIONS 选项
--fields-terminated-by=value
--fields-enclosed-by-value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by^value
--ignore-lines=n

dbname为导入的表所在的数据库名称.注意,MySQLimport命令不指定导入数据库的表名称,数据表的名称由导入文件名称确定,即文件名作为表名,导入数据之前该表必须存在.[OPTIONS]为可选参数选项,其常见的取值有:

• --fields-terminated-by= 'value‘:设置字段之间的分隔字符,可以为单个或多个字符,默认情况下为制表符“\t”.
• --fields-enclosed-by= ’value’:设置字段的包围字符.
• --fields-optionally-enclosed-by= 'value’:设置字段的包围字符,只能为单个字符,包括CHAR和VERCHAR等字符数据字段.
• --fields-escaped-by='value’:控制如何写入或读取特殊字符,只能为单个字符,即设置转义字符,默认值为反斜线“\”.
• --lines-terminated-by= 'value*:设置每行数据结尾的字符,可以为单个或多个字符,默认值为“\n”.
• --ignore-lines=n:忽视数据文件的前n行.

除了前面介绍的几个选项之外,MySQLimport支持许多选项,常见的选项有:

• --columns=column_list,-c column list该选项采用逗号分隔的列名作为其值.列名的顺 序指示如何匹配数据文件列和表列.
• --compress,-C:压缩在客户端和服务器之间发送的所有信息(如果二者均支持压缩).
• --d,—delete:导入文本文件前清空表.
• --force,-f:忽视错误.例如,如果某个文本文件的表不存在,继续处理其他文件.不使用—force,如果表不存在,则MySQLimport退出.
• --host=host_name,-h host name:将数据导入给定主机上的MySQL服务器.默认主机是localhost.
• --ignore,-i:参见—replace选项的描述.
• --ignore-lines=n:忽视数据文件的前n行.
• --local,-L:从本地客户端读入输入文件.
• --lock-tables,-1:处理文本文件前锁定所有表以便写入.这样可以确保所有表在服务器上保持同步.
• --password[=password],-p[password]:当连接服务器时使用的密码.如果使用短选项形式(-p),选项和密码之间不能有空格.如果在命令行中—password或-p选项后面没有密码值,则提示输入一个密码.
• --port=port_num,-P port num:用于连接的 TCP/IP 端 口号.
• --protocol={TCP | SOCKET | PIPE | MEMORY}:使用的连接协议.
• --replace,-r -replace和--ignore选项控制复制唯一键值已有记录的输入记录的处理.如果指定-replace,新行替换有相同的唯一键值的已有行;如果指定-ignore,复制已 有的唯一键值的输入行被跳过;如果不指定这两个选项,当发现一个复制键值时会出 现一个错误,并且忽视文本文件的剩余部分.
• --silent,-s:沉默模式.只有出现错误时才输出信息.
• --user=user_name,-u user_name:当连接服务器时MySQL使用的用户名.
• --verbose,-v:冗长模式.打印出程序操作的详细信息.
• --version,-V:显示版本信息并退出.

14.5 综合案例--数据的备份与恢复

14.6 专家解惑

疑问1:MySQLdump备份的文件只能在MySQL中使用吗?
MySQLdump备份的文本文件实际是数据库的一个副本,使用该文件不仅可以在MySQL 中恢复数据库,而且通过对该文件的简单修改,可以使用该文件在SQL Server或者Sybase等 其他数据库中恢复数据库.这在某种程度上实现了数据库之间的迁移.

疑问2:如何选择备份工具?
直接复制数据文件是最为直接、快速的备份方法,但缺点是基本上不能实现增量备份.备份时必须确保没有使用这些表.如果在复 制一个表的同时服务器正在修改它,则复制无效.备份文件时,最好关闭服务器,然后重新启动服务器.为了保证数据的一致性,需要在备份文件前,执行以下SQL语句:FLUSH TABLES WITH READ
也就是把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证复制过程中不会有新的数据写入.这种方法备份出来的数据恢复也很简单,直接复制回原来的数据库目录下即可.
MySQLhotcopy是一个PERL程序,它使用LOCK TABLES> FLUSH TABLES 和 cp 或 sop来快速备份数据库.它是备份数据库或单个表的最快的途径,但它只能运行在数据库文件 所在的机器上,并且MySQLhotcopy只能用于备份MylSAM表.MySQLhotcopy适合于小型 数据库的备份,数据量不大,可以使用MySQLhotcopy程序每天进行一次完全备份.
MySQLdump将数据表导成SQL脚本文件,在不同的MySQL版本之间升级时相对比较合 适,这也是最常用的备份方法.MySQLdump比直接复制要慢些.

疑问3:使用MySQLdump备份整个数据库_,把表和数据库都删除了,但使用备份 文件却不能恢复数据库?
出现这种情况,是因为备份的时候没有指定--databases参数.默认情况下,如果只指定数 据库名称,MySQLdump备份的是数据库中所有的表,而不包括数据库的创建语句
例如:mysqldump -u root -p booksDB > c:\backup\booksDB_20160101.sql
该语句只备份了 booksDB数据库下所有的表,读者打开该文件,可以看到文件中不包含 创建booksDB数据库的CREATE DATABASE语句,因此如果把booksDB也删除了,使用该 SQL文件不能恢复以前的表,恢复吋会出现ERROR 1046(3D000):No database selected的错 误信息.必须在MySQL命令行下创建booksDB数据库,并使用use语句选择bo oks DB 之后 才可以恢复.而下面的语句,数据库删除之后,可以正常恢复备份时的状态.
mysqldump -u root -p --databases booksDB > C:\backup\books_DB_20160101.sql
该语句不仅备份了所有数据库下的表结构,而且包括创建数据库的语句.

第15章 MySQL日志

MySQL日志记录了MySQL数据库日常操作和错误信息.MySQL有不同类型的日志文件(各自存储了不同类型的日志),从日志当中可以查询到MySQL数据库的运行情况、用户操作、错误信息等,可以为MySQL管理和优化提供必要的信息.对于MySQL的管理工作而言,这些日志文件是不可缺少的.本章将介绍MySQL各种日志的作用以及日志的管理.

15.1 日志简介

MySQL 0志主要分为4类,使用这些日志文件,可以查看MySQL内部发生的事情.这4类日志分别是:

• 错误日志:记录MySQL服务的启动、运行或停止MySQL服务时出现的问题.
• 查询日志:记录建立的客户端连接和执行的语句.
• 二进制日志:记录所有更改数据的语句,可以用于数据复制.
• 慢查询日志:记录所有执行时间超过long query time的所有查询或不使用索引的查询,

默认情况下,所有日志创建于MySQL数据目录中.通过刷新日志,可以强制MySQL关闭和重新打开日志文件(或者在某些情况下切换到一个新的日志).
当执行一个FLUSH LOGS语句或执行 MySQLadmin flush-logs 或 MySQLadmin refresh 时,将刷新日志.
如果正使用MySQL复制功能,在复制服务器上可以维护更多日志文件,这种日志称为接替曰志.
启动日志功能会降低MySQL数据库的性能.例如,在查询非常频繁的MySQL数据库系统中,如果开启了通用查询日志和慢查询日志,MySQL数据库会花费很多时间记录日志.同时,日志会占用大量的磁盘空间.

15.2 二进制文件

二进制日志主要记录MySQL数据库的变化.二进制日志以一种有效的格式,并且是事务安全的方式包含更新日志中可用的所有信息.二进制日志包含了所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个DELETE)的语句.语句以“事件”的形式保存,描述数据更改.
二进制日志还包含关于每个更新数据库的语句的执行时间信息.它不包含没有修改任何数据的语句.如果想要记录所有语句(例如,为了识别有问题的查询),需要使用一般查询日志.使用二进制日志的主要目的是最大可能地恢复数据库,因为二进制日志包含备份后进行的所有更新.本节将介绍二进制日志相关的内容.

15.2.1启动和设置二进制日志

默认情况下,二进制日志是关闭的,可以通过修改MySQL的配置文件来启动和设置二进制曰志.
my.ini中[MySQLd]组下面有几个设置是关于二进制日志的:

log-bin [=path/ [filename]] 
expire_logs_days=10 
max_birilog_size=100M

• log-bin定义开启二进制日志:
• path表明日志文件所在的目录路径;
• filename指定了日志文件的名称,如文件的全名为filename.000001,filename.000002等,除了上述文件之外,还有一 个名称为filename.index的文件,文件内容为所有日志的清单,可以使用记事本打开该文件.
• expire logs days定义了 MySQL清除过期日志的时间,即二进制日志自动删除的天数.默认值为0,表示“没有自动删除”.当MySQL启动或刷新二进制日志时可能删除该文件.
• max_binlog_size定义了单个文件的大小限制,如果二进制日志写入的内容大小超出给定值,日志就会发生滚动(关闭当前文 件,重新打开一个新的日志文件).不能将该变量设置为大于1GB或小于4096B默认值是lGB.

如果正在使用大的事务,二进制日志文件大小还可能会超过max_binlog_size定义的大小.在my.ini配置文件中的[MySQLd]组下,添加以下几个参数与参数值:

[mysqld]
log-bin 
expire_logs_days=10 
max_binlog_size=100M

添加完毕之后,关闭并重新启动MySQL服务进程,即可打开二进制日志,然后可以通过SHOW VARIABLES语句来查询日志设置.

数据库文件最好不要与日志文件放在同一个磁盘上,这样,当数据库文件所在的磁盘发生故障时,可以使用曰志文件恢复数据.

15.2.2 查看二进制日志

MySQL二进制日志存储了所有的变更信息
MySQL二进制日志是经常用到的.当MySQL创建二进制日志文件时,首先创建一个以“filename”为名称,以“.index”为后缀的文件;再创建一个以"filename”为名称,以“.000001”为后缀的文件.当MySQL服务重新启动一次,以“.000001”为后缀的文件会增加一个,并且后缀名加1递增;如果日志长度超过了max_binlog_size的上限(默认是1GB)也会创建一个新的日志文件.
show binary logs语句可以查看当前的二进制日志文件个数及其文件名.
MySQL二进制日志并不能直接查看,如果要查看日志内容,可以通过MySQLbinlog命令查看.
日志文件的个数与MySQL服务启动的次数相同.每启动一次MySQL服务,将会产生一个新的日志文件.

15.2.3删除二进制日志

MySQL的二进制文件可以配置自动删除,同时MySQL也提供了安全的手动删除二进制文件的方法:
RESET MASTER删除所有的二进制日志文件;
PURGE MASTER LOGS只删除部分二进制日志文件.

本小节将介绍这两种二进制日志删除的方法.

1.使用RESET MASTER语句删除所有二进制日志文件
RESET MASTER语法如下:RESET MASTER;
执行完该语句后,所有二进制日志将被删除,MySQL会重新创建二进制日志,新的曰志 文件扩展名将重新从000001开始编号.

2.使用PURGE MASTER LOGS语句删除指定日志文件
PURGE MASTER LOGS 语法如下:
PURGE {MASTER I BINARY) LOGS TO 'log_name'
PURGE {MASTER I BINARY} LOGS BEFORE 'date'
第1种方法指定文件名,执行该命令将删除文件名编号比指定文件名编号小的所有曰志文件.
第2种方法指定日期,执行该命令将删除指定日期以前的所有日志文件.

15.2.4使用二进制日志恢复数据库

如果MySQL服务器启用了二进制日志,在数据库出现意外丢失数据时,可以使用 MySQLbinlog工具从指定的时间点开始(例如,最后一次备份)直到现在,或另一个指定的时间点的曰志中恢复数据.
要想从二进制日志恢复数据,需要知道当前二进制日志文件的路径和文件名.一般可以从配置文件(即my.cnf或者my.ini,文件名取决于MySQL服务器的操作系统)中找到路径.
MySQLbinlog恢复数据的语法如下:mysqlbinlog [option] filename | mysql -uuser -ppass

• option是一些可选的选项
• filename是日志文件名.比较重要的两对option参数是--start-date- -stop-date 和--start-position、--stop-position、--start-date、--stop-date 可以指定恢复数据库的起始时间点和结束时间点.--tart-position、--stop-position可以指定恢复数据的开始位置和结束位置.

15.2.5暂时停止二进制日志功能

如果在MySQL的配置文件中配置启动了二进制日志,MySQL会一直记录二进制日志.修改配置文件,可以停止二进制日志,但是需要重启MySQL数据库.MySQL提供了暂时停止二进制日志的功能.通过SET SQL_LOG_BIN语句可以使用MySQL暂停或者启动二进制日志.
SET SQL_LOG_BIN的语法如下:SET sql_log_bin = {0 | 1};
执行如下语句将暂停记录二进制日志:SET sql_log_bin=0;
执行如下语句将恢复记录二进制日志:SET sql_log_bin=1;

15.3 错误日志

错误日志文件包含了当MySQLd启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息.在MySQL中,错误日志也是非常有用的,MySQL会将启动和停止数据 库信息以及一些错误信息记录到错误日志文件中.

15.3.1启动和设置错误日志

在默认情况下,错误日志会记录到数据库的数据目录下.如果没有在配置文件中指定文件名,则文件名默认为hostname.err.例如,MySQL所在的服务器主机名为MySQL-db,记录错 误信息的文件名为MySQL-db.err.如果执行了FLUSH LOGS,错误日志文件会重新加载.
错误日志的启动和停止以及指定日志文件名,都可以通过修改my.ini(或者my.cnf)来配置.错误日志的配置项是log-error.在[MySQLd]下配置log-error,则启动错误日志.如果需要指定文件名,则配置项如下:

[mysqld]
log-error=[path / [file_name] ]

path为日志文件所在的目录路径,file_name为日志文件名.修改配置项后,需要重启 MySQL服务以生效.

15.3.2查看错误日志

通过错误日志可以监视系统的运行状态,便于及时发界故障、修复故障.MySQL错误日志是以文本文件形式存储的,可以使用文本编辑器直接查MySQL错误曰志.如果不知道日志文件的存储路径,可以使用SHOW VARIABLES语句查询错误日志的存储路径.
SHOW VARIABLES语句如下:SHOW VARIABLES LIKE 'log_error';

15.3.3删除错误日志

MySQL的错误日志是以文本文件的形式存储在文件系统中的,可以直接删除.
对于MySQL 5.5.7以前的版本,flush logs可以将错误日志文件重命名为filename.err_old,并创建新的日志文件.但是从MySQL 5.5.7开始,flush logs只是重新打开日志文件,并不做曰志备份和创建的操作.如果日志文件不存在,MySQL启动或者执行flush logs时会创建新的曰志文件.
在运行状态下删除错误日志文件后,MySQL并不会自动创建日志文件.flush logs在重新加载日志的时候,如果文件不存在,则会自动创建.所以在删除错误日志之后,如果需要重建曰志文件,需要在服务器端执行以下命令:mysqladmin -u root -p flush-logs
或者在客户端登录MySQL数据库,执行flush logs语句:flush logs;

15.4 通用查询日志

通用查询日志记录MySQL的所有用户操作,包括启动和关闭服务、执行査询和更新语句等.本小节将为读者介绍通用查询日志的启动、查看、删除等内容.

15.4.1启动和设置通用查询日志

MySQL服务器默认情况下并没有开启通用查询日志.如果需要通用查询日志,可以通过修改my.ini(或my.cnf)配置文件来开启.在my.ini(或my.cnf)的[MySQLd]组下加入log选项,形式如下:
[mysqld] log[=path / [filename]]
path为日志文件所在目录路径,filename为日志文件名.如果不指定目录和文件名,通用査询日志将默认存储在MySQL数据目录中的hostname.log文件中.hostname是MySQL数据库的主机名.这里在[MySQLd]下面增加选项log,后面不指定参数值.格式如下:
[mysqld] Log

15.4.2 查看通用查询日志

通用查询日志中记录了用户的所有操作.通过查看通用查询日志,可以了解用户对MySQL进行的操作.通用查询日志是以文本文件的形式存储在文件系统中的,可以使用文本编辑器直接打开通用日志文件进行查看,Windows下可以使用记事本,Linux下可以使用vim、gedit等.

15.4.3删除通用查询日志

通用查询日志是以文本文件的形式存储在文件系统中的.通用查询日志记录用户的所有操作,因此在用户查询、更新频繁的情况下,通用查询日志会增长得很快.数据库管理员可以定期删除比较早的通用日志,以节省磁盘空间.本小节将介绍通用日志的删除方法.
可以用直接删除日志文件的方式删除通用查询日志.要重新建立新的日志文件,可使用语句MySQLadmin -flush logs.

15.5 慢查询日志

慢查询日志是记录查询时长超过指定时间的日志.慢查询日志主要用来记录执行时间较长的查询语句.通过慢查询日志,可以找出执行时间较长、执行效率较低的语句,然后进行优化.本小节将讲解慢查询日志相关的内容.

15.5.1启动和设置慢查询日志

MySQL中慢查询日志默认是关闭的,可以通过配置文件my.ini或者my.cnf中的log-slow-queries选项打开,也可以在MySQL服务启动的时候使用--log-slow-queries[=file_name] 启动慢查询日志.启动慢查询日志时,需要在my.ini或者my.cnf文件中配置long_query_time 选项指定记录阈值,如果某条查询语句的查询时间超过了这个值,这个查询过程将 被记录到慢查询日志文件中.
在my.ini或者my.cnf开启慢查询日志的配置如下:

    [mysqld]
    log-slow-queries[=path / [filename]]
    long_query_time=n

path为日志文件所在目录路径,filename为日志文件名.如果不指定目录和文件名称,默认存储在数据目录中,文件为hostname-slow.log,hostname是MySQL服务器的主机名.参数n是时间值,单位是秒.如果没有设置long_query_time选项,默认时间为10秒.

15.5.2查看慢查询日志

MySQL的慢查询日志是以文本形式存储的,可以直接使用文本编辑器查看.在慢查询日志中,记录着执行时间较长的查询语句,用户可以从慢查询日志中获取执行效率较低的查询语句,为查询优化提供重要的依据.

借助慢查询日志分析工具,可以更加方便地分析慢查询语句.比较著名的慢查询工具 MySQL Dump Slow,MySQL SLA、MySQL Log Filter.MyProfi.关于这些慢查询分析工具的用法,可以参考相关软件的帮助文档.

15.5.3删除慢查询日志

和通用查询日志一样,慢查询日志也可以直接删除.删除后在不重启服务器的情况下,需要执行MySQLadmin -u root -p flush-logs重新生成日志文件,或者在客户端登录到服务器执行flush logs语句重建日志文件.

15.6 综合案例——MySQL曰志的综台管理

15.7 专家解惑

疑问1:平时应该打开哪些曰志?
曰志既会影响MySQL的性能,又会占用大量磁盘空间.因此,如果不必要,应尽可能少地开启日志.根据不同的使用环境,可以考 虑开启不同的日志.例如,在开发环境屮优化查询效率低的语句,可以开启慢查询日志;如果需要记录用户的所有查询操作,可以 开启通用查询曰志;如果需要记录数据的变更,可以开启二进制日志;错误日志是默认开启的.

疑问2:如何使用二进制日志?
二进制日志主要用来记录数据变更.如果需要记录数据库的变化,可以开启二进制曰志.基于二进制日志的特性,不仅可以用来进行数据恢复,还可用于数据复制.在数据库定期备份的情况下,如果出现数据丢失,可以先用备份恢复大部分数据,然后使用二进制日志恢复最近备份后变更的数据.在双机热备情况下,可以使用MySQL的二进制日志记录数据的变更,然后将变更部分复制到备份服务器上.

疑问3:如何使用慢查询日志?
慢查询日志主要用来记录查询时间较长的日志.在开发环境下,可以开启慢查询日志来记录查询时间较长的查询语句,然后对这些语句进行优化.通过配置long query time的值,可以灵活地掌握不同程度的慢查询语句.

第16章 性能优化

MySQL性能优化就是通过合理安排资源,调整系统参数使MySQL运行更快、更节省资源.MySQL性能优化包括查询速度优化、数据库结构优化、MySQL服务器优化等.本章将为读者讲解以下几个内容:性能优化的介绍、查询优化、数据库结构优化、MySQL服务器优化.

16.1 优化简介

优化MySQL数据库是数据库管理员和数据库开发人员的必备技能.MySQL优化,一方面是找出系统的瓶颈,提高MySQL数据库整体的性能;另一方面需要合理的结构设计和参数调整,以提高用户操作响应的速度:同时还要尽可能节省系统资源,以便系统可以提供更大负荷的服务.本节将为读者介绍优化的基本知识.
MySQL数据库优化是多方面的,原则是减少系统的瓶颈,减少资源的占用,增加系统的反应速度.例如,通过优化文件系统,提高磁盘1\O的读写速度;通过优化操作系统调度策略,提高MySQL在高负荷情况下的负载能力;优化表结构、索引、查询语句等使查询响应更快.

在MySQL中,可以使用SHOW STATUS语句査询一些MySQL数据库的性能参数.
SHOW STATUS语句语法如下:SHOW STATUS LIKE 'value';
其中,value是要查询的参数值,一些常用的性能参数如下:

• Connections:连接MySQL服务器的次数.
• Uptime:MySQL服务器的上线时间.
• Slow_queries:慢查询的次数.
• Com_select:查询操作的次数.
• Com_insert:插入操作的次数.
• Com_update:更新操作的次数.
• Com_delete:删除操作的次数.

如果查询MySQL服务器的连接次数,可以执行如下语句:SHOW STATUS LIKE 'Connections';
如果查询MySQL服务器的慢查询次数,可以执行如下语句:SHOW STATUS LIKE 'Slow_queries';
查询其他参数的方法和两个参数的查询方法相同.慢查询次数参数可以结合慢查询曰志,找出慢查询语句,然后针对慢查询语句进行表结构优化或者查询语句优化.

16.2 优化查询

查询是数据库中最频繁的操作,提高查询速度可以有效地提高MySQL数据库的性能.本节将为读者介绍优化查询的方法.

16.2.1分析查询语句

通过对查询语句的分析,可以了解查询语句的执行情况,找出查询语句执行的瓶,从而优化查询语句.MySQL中提供了EXPLAIN语句和DESCRIBE语句,用来分析查询语句.本小节将为读者介绍使用EXPLAIN语句和DESCRIBE语句分析查询语句的方法.
EXPLAIN语句的基本语法如下:EXPLAIN [EXTENDED] SELECT select_options
使用EXTENED关键字,EXPLAIN语句将产生附加信息.select_options是SELECT语句的查询选项,包括FROM WHERE子句等.
执行该语句,可以分析EXPLAIN后面的SELECT语句的执行情况,并且能够分析出所查询的表的一些特征.
explain select * from tasks;
下面对查询结果进行解释.

• id:SELECT识别符.这是SELECT的查询序列号.
• select_type:表示SELECT语句的类型.它可以是以下几种取值:

  SIMPLE表示简单查询,其中不包括连接查询和子查询;
  PRIMARY表示主查询,或者是最外层的查询语句;
  UNION表示连接查询的第2个或后面的查询语句;
  DEPENDENT UNION,连接查询中的第2个或后面的SELECT语句,取决于外面的查询;
  UNION RESULT,连接查询的结果;
  SUBQUERY,子查询中的第1个SELECT语句;
  DEPENDENT SUBQUERY,子查询中的第1个SELECT,取决于外面的查询;
  DERIVED,导出表的SELECT(FROM子句的子查询).

• table:表示查询的表.
• type:表示表的连接类型.下面按照从最佳类型到最差类型的顺序给出各种连接类型:

  system:该表是仅有一行的系统表.这是const连接类型的一个特例.
  const:数据表最多只有一个匹配行,它将在查询开始时被读取,并在余下的查询优化中作为常量对待.const表查询速度很快因为它们只读取一次const用于使用常数值比较PRIMARY KEY 或UNIQUE索引的所有部分的场合.
  eq_ref:对于每个来自前面的表的行组合,从该表中读取一行.当一个索引的所有部分都在查询中使用并且索引是UNIQUE或PRIMARY KEY时,即可使用这种类型.eq_ref可以用于使用操作符比较带索引的列.比较值可以为常量或一个在该表前面所读取的表的列的表达式.
  ref:对于來自前面z的表的任意行组合,将从该表中读取所有匹配的行,这种类型用于索引既 不是UNIQUE也不是PRIMARY KEY的情况,或者查询中使用了索引列的左子集,即索引中左边的部分列组合.ref可以用于使用=或<=>操作符的带索引的列.
  ref_or_null"该连接类型如同ref,但是添加了 MySQL可以专门搜索包含NULL值的行.在解决子查 询中经常使用该连接类型的优化.
  index merge:该连接类型表示使用了索引合并优化方法.在这种情况下,key列包含了使用的索引的清单,keyjen包含了使用的索引的最长的关键元素.
  unique subquery:该类型替换了下面形式的IN子查询的ref:value IN(SELECT primary_key FROM single_table WHERE some_expr),unique subquery是一个索引查找函数,可以完全替换子查询,效率更高.
  index subquery:该连接类型类似于unique_subquery,可以替换IN子查询,但只适合下列形式的子查询中 的非唯一索引:value IN(SELECT key_column FROM single_table WHERE some_expr)
  range:只检索给定范围的行,使用一个索引来选择行.key列显示使用了哪个索引.key_len包含所使用索引的最长关键元素.当使用=、◊、〉、〉=、<、<=、IS NULL、<=〉、BETWEEN或者IN操作符,用常量比较关键字列时,类型为range.

  index:该连接类型与ALL相同,除了只扫描索引树.这通常比ALL快,因为索引文件通常比数据文件小.
  ALL:对于前面的表的任意行组合,进行完整的表扫描.如果表是第一个没标记const的表,这样不好,并且在其他情况下很差.通常可以增加更多的索引来避免使用ALL连接.

• possible keys:指出MySQL能使用唧个索引在该表中找到行.如果该列是NULL,则没有相关的索引.在这种情况下,可以通过检查WHERE子句看它是否引用某些列或适合索引的列来提高查询性能.如果是这样,可以创建适合的索引来提高查询的性能.
• key表示查询实际使用到的索引,如果没有选择索引,该列的值是NULL要想强制MySQL 使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX.USE INDEX或者 IGNORE INDEX.参见 SELECT 语法.
• keyjen:表示MySQL选择的索引字段按字节计算的长度,如果键是NULL,则长度为 NULL.注意通过key len值可以确定MySQL将实际使用一个多列索引中的几个字段.
• ref:表示使用哪个列或常数与索引一起来查询记录.
• rows:显示MySQL在表中进行查询时必须检查的行数.
• extra:表示MySQL在处理查询时的详细信息.

DESCRIBE语句的使用方法与EXPLAIN语句是一样的,并且分析结果也是一样的.
DESCRIBE语句的语法形式如下:DESCRIBE SELECT select_options

16.2.2索引对查询速度的影响

MySQL中提高性能的一个最有效的方式就是对数据表设计合理的索引.索引提供了高效访问数据的方法,并且加快查询的速度,因 此,索引对查询的速度有着至关重要的影响.使用索引可以快速地定位表中的某条记录,从而提高数据库查询的速度,提高数据库的性能.本小节将为读者介绍索引对查询速度的影响.
如果查询时没有使用索引,查询语句将扫描表中的所有记录.在数据量大的情况下,这样查询的速度会很慢.如果使用索引进行查询,查询语句可以根据索引快速定位到待查询记录,从而减少查询的记录数,达到提高查询速度的目的.

16.2.3使用索引查询

索引可以提高查询的速度.但并不是使用带有索引的字段查询时,索引都会起作用.本小节将向读者介绍索引的使用.
使用索引有几种特殊情况,在这些情况下,有可能使用带有索引的字段查询时,索引并没有起作用,下面重点介绍这几种特殊情况.

1.使用LIKE关键字的查询语句
在使用LIKE关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”,索引不会起作用.只有“%”不在第一个位置,索引才会起作用.

2.使用多列索引的查询语句
MySQL可以为多个字段创建索引.一个索引可以包括16个字段.对于多列索引,只有查询条件中使用了这些字段中第1个字段时,索引才会被使用.

3.使用OR关键字的查询语句
查询语句的查询条件中只有OR关键字,且OR前后的两个条件中的列都是索引时,查询中才使用索引.否则,查询将不使用索引.

16.2.4优化子查询

MySQL从4.1版本开始支持子查询,使用子查询可以进行SELECT语句的嵌套查询,即一个SELECT查询的结果作为另一个SELECT语句的条件.子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作.子查询虽然可以使查询语句很灵活,但执行效率不高.执行子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表.然后外层查询语句从临时表中查询记录.查询完毕后,再撤销这些临时表.因此,子查询的速度会受到一定的影响.如果查询的数据量比较大,这种影响就会随之增大.
在MySQL中,可以使用连接(JOIN)查询来替代子查询.连接查询不需要建立临时表,其速度比子查询要快,如果查询中使用索引的话,性能会更好.连接之所以更有效率,是因为MySQL不需要在内存中创建临时表来完成查询工作.

16.3 优化数据库结构

一个好的数据库设计方案对于数据库的性能常常会起到事半功倍的效果.合理的数据库结构不仅可以使数据库占用更小的磁盘空间,而且能够使查询速度更快.数据库结构的设计,需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的 内容.本节将为读者介绍优化数据库结构的方法.

16.3.1将字段很多的表分解成多个表

对于字段较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新表.因为当一个表的数据量很大时,会由于使用频率低的字段的存在而变慢.

16.3.2增加中间表

对于需要经常联合查询的表,可以建立中间表以提高查询效率.通过建立中间表,把需要经常联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询,以此来提高查询效率.本小节将为读者介绍增加中间表优化查询的方法.
首先,分析经常联合查询表中的字段.然后,使用这字段建立一个中间表,并将原来联合查询的表的数据插入到中间表中.最后,可以使用中间表来进行查询了.

16.3.3増加冗余字段

设计数据库表时应尽量遵循范式理论的规约,尽可能减少冗余字段,让数据库设计看起来精致、优雅.但是,合理地加入冗余字段 可以提高查询速度.本小节将为读者介绍通过增加冗余字段来优化查询速度的方法.
表的规范化程度越高,表与表之间的关系就越多,需要连接查询的情况也就越多.

冗余字段会导致一些问题.比如,冗余字段的值在一个表中被修改了,就要想办法在其他表中更新该字段.否则就会使原本一致的数据变得不一致.分解表、增加中间表和增加冗余字段都浪费了一定的磁盘空间.从数据库性能来看,为了提高查询速度而增加少量的冗余大部分时候是可以接受的.是否通过增加冗余来提高数据库性能,这要根据实际需求综合分析.

16.3.4优化插入记录的速度

插入记录时,影响插入速度的主要是索引、唯一性校验、一次插入记录条数等.根据这些情况,可以分别进行优化.本小节将为读者介绍优化插入记录速度的几种方法.

对于MylSAM引擎的表,常见的优化方法如下:

1.禁用索引
对于非空表,插入记录时,MySQL会根据表的索引对插入的记录建立索引.如果插入大量数据,建立索引会降低插入记录的速度.为了解决这种情况可以在插入记录之前禁用索引,数据插入完毕后再开启索引.
禁用索引的语句如下:ALTER TABLE table_name DISABLE KEYS;
其中table_name是禁用索引的表的表名.
重新开启索引的语句如下:ALTER TABLE table_name ENABLE KEYS;
对于空表批量导入数据,则不需要进行此操作,因为MylSAM引擎的表是在导入数据之后才建立索引的.

2.禁用唯一性检查
插入数据时,MySQL会对插入的记录进行唯一性校验.这种唯一性校验也会降低插入记录的速度.为了降低这种情况对查询速度的影响,可以在插入记录之前禁用唯一性检查,等到记录插入完毕后再开启.
禁用唯一性检查的语句如下:SET UNIQUE_CHECKS=0;
开启唯一性检查的语句如下:SET UNIQUE_CHECKS=1;

3.使用批量插入
插入多条记录时,可以使用一条INSERT语句插入一条记录;也可以使用一条INSERT语句插入多条记录.

4.使用LOAD DATA INFILE批量导
当需要批量导入数据时,如果能用LOAD DATA INFILE语句,就尽量使用.因为LOAD DATA INFILE语句导入数据的速度比INSERT语句快.

对于InnoDB引擎的表,常见的优化方法如下:

1.禁用唯一检查
插入数据之前执行set unique_checks=O来禁止对唯一索引的检查,数据导入完成之后再运行set unique_checks=1.这个和MylSAM引擎的使用方法一样.

2.禁用外键检查
插入数据之前执行禁止对外键的检查,数据插入完成之后再恢复对外键的检查.
禁用外键检查的语句如下:SET foreign_key_checks=0;
恢复对外键的检查语句如下:SET foreign_key_checks=1;

3.禁止自动提交
插入数据之前禁止事务的自动提交,数据导入完成之后,执行恢复自动提交操作.
禁止自动提交的语句如下:set autocommit=0;
恢复自动提交的语句如下:set autocommit=1;

16.3.5分析表、检查表和优化表

MySQL提供了分析表、检查表和优化表的语句.分析表主要是分析关键字的分布;检查表主要是检查表是否存在错误:优化表主要是消除删除或者更新造成的空间浪费.本小节将为读者介绍分析表、检查表和优化表的方法.

1.分析表
MySQL中提供了 ANALYZE TABLE语句分析表ANALYZE TABLE语句的基本语法如下:
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]...
LOCAL关键字是NO_WRITE_TO_BINLOG关键字的别名,二者都是执行过程不写入二进制日志,tbl_name为分析的表的表名,可以有一个或多个.
使用ANAL YZE TABLE分析表的过程中,数据库系统会自动对表加一个只读锁.在分析期间,只能读取表中的记录,不能更新和插入记录.ANALYZE TABLE语句能够分析InnoDB、 BDB和MylSAM类型的表.

ANALYZE TABLE显示的信息说明如下:

• Table:表示分析的表的名称.
• Op:表示执行的操作.analyze表示进行分析操作.
• Msg_type:表示信息类型,其值通常是状态(status)、信息(info)、注意(note)、警告(warning)和错误(error)之一.
• Msg text:显示信息.

2.检查表
MySQL中可以使用CHECK TABLE语句来检查表,CHECK TABLE语句能够检查InnoDB 和MylSAM类型的表是否存在错误.对于MylSAM类型的表,CHECK TABLE语句还会更新关键字统计数据.而且,CHECK TABLE也可以检査视图是否有错误,比如在视图定义中被 引用的表已不存在.
该语句的基本语法如下:
CHECK TABLE tbl__name [,tbl_name] ••• [option] ••• option = {QUICK | FAST | MEDIUM | EXTENDED 丨 CHANGED}
其中,tbl_name是表名;option参数有5个取值,分别是QUICK、FAST、MEDIUM.EXTENDED和CHANGED.

各个选项的意义分别是:

• QUICK:不扫描行,不检查错误的连接.
• FAST:只检查没有被正确关闭的表.
• CHANGED:只检查上次检查后被更改的表和没有被正确关闭的表.
• MEDIUM扫描行,以验证被删除的连接是有效的.也可以计算各行的关键字校验和,并使用计算出的校验和验证这一点.
• EXTENDED对每行的所有关键字进行一个全面的关键字查找.这可以确保表是100%一致的,但是花的时间较长.

•  option只对MylSAM类型的表有效,对InnoDB类型的表无效.CHECK TABLE语句在 执行过程中也会给表加上只读锁.
  

3.优化表
MySQL中使用OPTIMIZE TABLE语句来优化表.该语句对InnoDB和MylSAM类型的表都有效.但是,OPTILMIZE TABLE语句只能优化表中的VARCHAR,BLOB或TEXT类型的字段.
OPTILMIZE TABLE语句的基本语法如下:
OPTIMIZE [LOCAL | N0_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]
LOCAL | NO WRITE_TO_BINLOG关键字的意义和分析表相同,都是指定不写入二进制日志;tbl_name是表名.
通过OPTIMIZE TABLE语句可以消除删除和更新造成的文件碎片.OPTIMIZE TABLE语句在执行过程中也会给表加上只读锁.

一个表使用了 TEXT或者BLOB这样的数据类型,如果已经删除了表的一大部分,或已经对含有可变长度行的表(含有VARCHAR,BLOB或TEXT列的表)进行了很多更新,则应使用OPTIMIZE TABLE来重新利用未使用的空间,并整理数据文件的碎片.在多数的设置中,根本不需要运行OPTIMIZE TABLEO即使对可变长度的行进行了大量的更新,也不需要经常运行,每周一次或每月一次即可,并且只需要对特定的表运行.

16.4 优化MySQL服务器

优化MySQL服务器主要从两个方面来优化,一方面是对硬件进行优化;另一方面是对MySQL服务的参数进行优化.这部分的内容需要较全面的知识,一般只有专业的数据库管理员才能进行这一类的优化.对于可以定制参数的操作系统,也可以针对MySQL进行操作系统优化.本节将为读者介绍优化MySQL服务器的方法.

16.4.1优化服务器硬件

服务器的硬件性能直接决定着MySQL数据库的性含么硬件的性能瓶颯直接决定MySQL数据库的运行速度和效率.针对性能瓶颈,提高硬件配置,可以提高MySQL数据库的査询、更新的速度.
本小节将为读者介绍以下优化服务器硬件的方法.

• 配置较大的内存.足够大的内存,是提高MySQL数据库性能的方法之一.内存的速度比磁盘I/O快得多,可以通过增加系统的缓冲区容量,使数据在内存停留的时间更长,以 减少磁盘I/O.
• 配置高速磁盘系统,以减少读盘的等待时间,提高响应速度.
• 合理分布磁盘1/0,把磁盘I/O分散在多个设备上,以减少资源竞争,提高并行操作能力.
• 配置多处理器,MySQL是多线程的数据库,多处理器可同时执行多个线程.

16.4.2 优化MySQL的参数

通过优化MySQL的参数可以提高资源利用率,从而达到提高MySQL服务器性能的目的.本小节将为读者介绍这些配置参数.

MySQL服务的配置参数都在my.cnf或者my.ini文件的[MySQLd]组中.
下面对几个对性能影响比较大的参数进行详细介绍.

• key_buffer_size:表示索引缓冲区的大小.索引缓冲区所有的线程共享.增加索引缓冲区可以得到更好处理的索引(对所有读和多重写).当然,这个值也不是越大越好,它的大小取决于内存的大小.如果这个值太大,导致操作系统频繁换页,也 会降低系统性能.
• table_cache:表示同时打开的表的个数.这个值越大,能够同时打开的表的个数越多.这个值不是越大越好,因为同时打开的表太多会影响操作系统的性能.
• query_cache_size:表示查询缓冲区的大小.该参数需要和query_cache_type配合使用.
  当query_cache_type值是0时,所有的查询都不使用查询缓冲区.但是 query_cache_type=O并不会导致MySQL释放query cache size所配置的缓冲区内存.
  当query_cache_type= 1时,所有的查询都将使用查询缓冲区,除非在查询语句中指定SQL_NO_CACHE,SELECT SQL_NO_CACHE * FROM tbl_name.
  当 query_cache_type=2时,只有在查询语句中使用SQL_CACHE关键字,查询才会使用查询缓冲区.使用查询缓冲区可以提高查询的速度,这种方式只适用于修改操作少且经常执行相同的查询操作的情况.
• sort_buffer_size:表示排序缓存区的大小.这个值越大,进行排序的速度越快.
• read_buffer_size:表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节).当线程从表中连续读取记录时需要用到这个缓冲区.SET SESSION read_buffer_size=n可以临时设置该参数的值.
• read_rnd_buffer_size:表示为每个线程保留的缓冲区的大小,与read_buffer_size相似.但主要用于存储按特定顺序读取出来的记录.也可以用SET SESSION read_rnd_buffer_size=n来临时设置该参数的值.如果频繁进行多次连续扫描,可以增加该值.
• innodb_buffer_pool_size:表示InnoDB类型的表和索引的最大缓存.这个值越大,查询的速度就会越快.但是这个值太大会影响操作系统的性能.
• max_connections:表示数据库的最大连接数.这个连接数不是越大越好,因为这些连接会浪费内存的资源.过多的连接可能会导致MySQL服务器僵死.
• innodb_flush_log_at_trx_commit表示何时将缓冲区的数据写入日志文件,并且将日志文件写入磁盘中.该参数对于innoDB引擎非常重要.该参数有3个值,分别为0、1和2.值为0时表示每隔1秒将数据写入日志文件并将日志文件写入磁盘;值为1时表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘;值为2时表示每次提交事务时将数据写入日志文件,每隔I秒将日志文件写入磁盘.该参数的默认值为1.默认值1安全性最高,但是每次事务提交或事务外的指令都需要把日志写入(flush)硬盘,是比较费时的;0值更快一点,但安全方面比较差;2值日志仍然会每秒写入到硬盘,所以即使出现故障,一般也不会丢失超过1~2秒的更新.
• back_log:表示在MySQL暂时停止回答新请求之前的短时间内,多少个请求可以被存在堆栈中.换句话说,该值表示对到来的TCP/IP连接的侦听队列的大小.只有期望在一个短时间内有很多连接,才需要增加该参数的值.操作系统在这个队列大小上也有限制.设定back_log高于操作系统的限制将是无效的.
• interactive_timeout:表示服务器在关闭连接前等待行动的秒数.
• sort_buffer_size:表示每个需要进行排序的线程分配的缓冲区的大小.增加这个参数的值可以提高ORDER BY或GROUP BY操作的速度.默认数值是2097144(2MB).
• thread_cache_size:表示可以复用的线程的数量.如果有很多新的线程,为了提高性能可以增大该参数的值.
• wait_timeout:表示服务器在关闭一个连接时等待行动的秒数.默认数值是28800.

合理地配置这些参数可以提高MySQL服务器的性能.除上述参数以外,还有innodb_log_buflfer_size、innodb_log_file_size等参数.配置完参数以后,需要重新启动MySQL服务才会生效.

16.5 综台案例——全面优化MySQL服务器

16.6 专家解惑

疑问1:是不是索引建立得越多越好?
合理的索引可以提高查询的速度,但不是索引越多越好.在执行插入语句的时候,MySQL要为新插入的记录建立索引.所以过多的索会导致插入操作变慢.原则上是只有查询用的字段才建立索引.

疑问2:为什么查询语句中的索引没有起作用?
在一些情况下,查询语句中使用了带有索引的字段.但索引并没有起作用.例如,在WHERE条件的LIKE关键字匹配的字符串以“%”开头,这种情况下索引不会起作用.又如,WHERE条件中使用OR关键字连接查询条件,如果有1个字段没有使用索引,那么其他的索引也不会起作用.如果使用多列索引,但没有使用多列索引中的第1个字段,那么多列索引也不会起作用.

疑问3:如何使用查询缓冲区?
查询缓冲区可以提高查询的速度,但是这种方式只适合查询语句比较多、更新语句比较少的情况.默认情况下查询缓冲区的大小为0,也就是不可用.可以修改query_cache_size以调整查询缓冲区大小;修改query_cache_type以调整查询缓冲区的类型.在my.ini中修改query_cache_size 和 query_cache_type的值如下所示:

    [mysqld]
    query_cache_size=512M
    query_cache_type=1

query_cache_type=l表示开启查询缓冲区.只有在查询语句中包含SQL NO_CACHE关键字时,才不会使用查询缓冲区.可以使用FLUSH_QUERY_CACHE语句来刷新缓冲区,清理查询缓冲区中的碎片.