Chapter 1. MySQL数据库的数据

MySQL中的数值分整型和浮点型两种。MySQL支持科学记数法。整型可以是十进制，也可是十六进制数。

MySQL支持以单或双引号包围的字符序列。如“MySQL tutorial”、‘Mysql Database’。

MySQL能识别字符串中的转义序列，转义序列用反斜杠(\)表示。下面是一个转义序列列表。

如果字符串本身包含有单双引号，则用以下三种方法中的一种来表示：

字符串可由一个十六进制数表示，如0x61表示字符"a"。由MySQL 4.0开始，字符串值也可用ANSI SQL表示法X‘val‘来表示。如X‘61‘表示字符"a"。

从MySQL 4.1开始，可以为字符串值专门指定一个字符集。

MySQL默认按“年-月-日”的顺序显示日期。

数值型的列类型包括整型和浮点型两大类。

字符串可以用来表示任何一种值，所以它是最基本的类型之一。我们可以用字符串类型来存储图象或声音之类的二进制数据，也可存储用gzip压缩的数据。下表介绍了各种字符串类型：

L+1、L+2是表示数据列是可变长度的，它占用的空间会根据数据行的增减面则改变。数据行的总长度取决于存放在这些数据列里的数据值的长度。L+1或L+2里多出来的字节是用来保存数据值的长度的。在对长度可变的数据进行处理时，MySQL要把数据内容和数据长度都保存起来。

如果把超出字符串最大长度的数据放到字符类数据列中，MySQL会自动进行截短处理。

ENUM和SET类型的数据列定义里有一个列表，列表里的元素就是该数据列的合法取值。如果试图把一个没有在列表里的值放到数据列里，它会被转换为空字符串(“”)。

字符串类型的值被保存为一组连续的字节序列，并会根据它们容纳的是二进制字符串还是非二进制字符而被区别对待为字节或者字符：

在MySQL4.1以后的版本中，不同的数据列可以使用不同的字符集。在MySQL4.1版本以前，MySQL用服务器的字符集作为默认字符集。

非二进制字符串，即我们通常所说的字符串，是按字符在字符集中先后次序进行比较和排序的。而二进制字符串因为与字符集无关，所以不以字符顺序排序，而是以字节的二进制值作为比较和排序的依据。下面介绍两种字符串的比较方式：

二进制字符串与字符集无关，所以无论按字符计算还是按字节计算，二进制字符串的长度都是一样的。所以VARCHAR(20)并不表示它最多能容纳20个字符，而是表示它最多只能容纳可以用20个字节表示出来的字符。对于单字节字符集，每个字符只占用一个字节，所以这两者的长度是一样的，但对于多字节字符集，它能容纳的字符个数肯定少于20个。

1.2.2.1. CHAR和VARCHAR

 

CHAR和VARCHAR是最常用的两种字符串类型，它们之间的区别是：

• CHAR是固定长度的，每个值占用相同的字节，不够的位数MySQL会在它的右边用空格字符补足。

• VARCHAR是一种可变长度的类型，每个值占用其刚好的字节数再加上一个用来记录其长度的字节即L+1字节。

CHAR(0)和VARCHAR(0)都是合法的。VARCHAR(0)是从MySQL4.0.2版开始的。它们的作用是作为占位符或用来表示各种on/off开关值。

如何选择CHAR和VARCHAR，这里给出两个原则：

• 如果数据都有相同的长度，选用VARCHAR会多占用空间，因为有一位用来存储其长度。如果数据长短不一，选用VARCHAR能节省存储空间。而CHAR不论字符长短都需占用相同的空间，即使是空值也不例外。

• 如果长度出入不大，而且是使用MyISAM或ISAM类型的表，则用CHAR会比VARCHAR好，因为MyISAM和ISAM类型的表对处理固定长度的行的效率高。

	在一个数据表里，只要有一个数据列的长度是可变的，则所有数据列的长度将是可变的。MySQL会进行自动地转换。一个例外，CHAR长度小于4的不会进行自动转换，因为MySQL会认为这样做没必要，节省不了多少空间。反而MySQL会把大量长度小的VARCHAR转换成CHAR，以减少空间占用量。

MySQL的日期时间类型有：DATE，DATETIME，TIME，TIMESTAMP和YEAR，下表是这些类型的取值范围和存储空间要求：

	MySQL总是把日期和日期里的年份放在最前面，按年月日的顺序显示。

1.2.3.3. YEAR

 

YEAR是一种单字节的数据列类型，YEAR(4)的取值范围是1901~2155,YEAR(2)的取值范围是1970~2069,但只显示最后两位数。MySQL能自动把两位数字年份转换成四位数字的年份，如97和14分被转换成1997和2014。转换规则是这样的：

• 年份值00~69将被转换成2000~2069；

• 年份值70~99将被转换成1970~1999。

	00被转换成0000,而不是2000。因为数值00也就是0,而0值是YEAR的一个合法取值。

• ISAM表

  • 如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开始，并1为基数递增。

  • 把0插入AUTO_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做，还是以插入NULL值为好。

  • 当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值。

  • 当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况，情况一，如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。

  • 如果自增序列的最大值被删除了，则在插入新记录时，该值被重用。

  • 如果用UPDATE命令更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号从该值开始递增。

  • 如果用replace命令基于AUTO_INCREMENT数据列里的值来修改数据表里的现有记录，即AUTO_INCREMENT数据列出现在了replace命令的where子句里，相应的AUTO_INCREMENT值将不会发生变化。但如果replace命令是通过其它的PRIMARY KEY OR UNIQUE索引来修改现有记录的(即AUTO_INCREMENT数据列没有出现在replace命令的where子句中)，相应的AUTO_INCREMENT值--如果设置其为NULL(如没有对它赋值)的话--就会发生变化。

  • last_insert_id()函数可获得自增列自动生成的最后一个编号。但该函数只与服务器的本次会话过程中生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值，则该函数返回0。

  其它数据表的自动编号机制都以ISAM表中的机制为基础。

• MyISAM数据表

  • 删除最大编号的记录后，该编号不可重用。

  • 可在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值。

  • 可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值。

  • 可使用复合索引在同一个数据表里创建多个相互独立的自增序列，具体做法是这样的：为数据表创建一个由多个数据列组成的PRIMARY KEY OR UNIQUE索引，并把AUTO_INCREMENT数据列包括在这个索引里作为它的最后一个数据列。这样，这个复合索引里，前面的那些数据列每构成一种独一无二的组合，最末尾的AUTO_INCREMENT数据列就会生成一个与该组合相对应的序列编号。

• HEAP数据表

  • HEAP数据表从MySQL4.1开始才允许使用自增列。

  • 自增值可通过CREATE TABLE语句的 AUTO_INCREMENT=n选项来设置。

  • 可通过ALTER TABLE语句的AUTO_INCREMENT=n选项来修改自增始初值。

  • 编号不可重用。

  • HEAP数据表不支持在一个数据表中使用复合索引来生成多个互不干扰的序列编号。

• BDB数据表

  • 不可通过CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n选项来改变自增初始值。

  • 可重用编号。

  • 支持在一个数据表里使用复合索引来生成多个互不干扰的序列编号。

• InnDB数据表

  • 不可通过CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n选项来改变自增初始值。

  • 不可重用编号。

  • 不支持在一个数据表里使用复合索引来生成多个互不干扰的序列编号。

• AUTO_INCREMENT是数据列的一种属性，只适用于整数类型数据列。

• 设置AUTO_INCREMENT属性的数据列应该是一个正数序列，所以应该把该数据列声明为UNSIGNED，这样序列的编号个可增加一倍。

• AUTO_INCREMENT数据列必须有唯一索引，以避免序号重复。

• AUTO_INCREMENT数据列必须具备NOT NULL属性。

• AUTO_INCREMENT数据列序号的最大值受该列的数据类型约束，如TINYINT数据列的最大编号是127,如加上UNSIGNED，则最大为255。一旦达到上限，AUTO_INCREMENT就会失效。

• 当进行全表删除时，AUTO_INCREMENT会从1重新开始编号。全表删除的意思是发出以下两条语句时：

  delete from table_name;ortruncate table table_name

  这是因为进行全表操作时，MySQL实际是做了这样的优化操作：先把数据表里的所有数据和索引删除，然后重建数据表。如果想删除所有的数据行又想保留序列编号信息，可这样用一个带where的delete命令以抑制MySQL的优化：

  delete from table_name where 1;

  这将迫使MySQL为每个删除的数据行都做一次条件表达式的求值操作。

• 强制MySQL不复用已经使用过的序列值的方法是：另外创建一个专门用来生成AUTO_INCREMENT序列的数据表，并做到永远不去删除该表的记录。当需要在主数据表里插入一条记录时，先在那个专门生成序号的表中插入一个NULL值以产生一个编号，然后，在往主数据表里插入数据时，利用LAST_INSERT_ID()函数取得这个编号，并把它赋值给主表的存放序列的数据列。如：

  insert into id set id = NULL;insert into main set main_id = LAST_INSERT_ID();

• 可用alter命令给一个数据表增加一个具有AUTO_INCREMENT属性的数据列。MySQL会自动生成所有的编号。

• 要重新排列现有的序列编号，最简单的方法是先删除该列，再重建该，MySQL会重新生连续的编号序列。

• 在不用AUTO_INCREMENT的情况下生成序列，可利用带参数的LAST_INSERT_ID()函数。如果用一个带参数的LAST_INSERT_ID(expr)去插入或修改一个数据列，紧接着又调用不带参数的LAST_INSERT_ID()函数，则第二次函数调用返回的就是expr的值。下面演示该方法的具体操作：

  先创建一个只有一个数据行的数据表：create table seq_table (id int unsigned not null);insert into seq_table values (0);接着用以下操作检索出序列号：update seq_table set seq = LAST_INSERT_ID( seq + 1 );select LAST_INSERT_ID();通过修改seq+1中的常数值，可生成不同步长的序列，如seq+10可生成步长为10的序列。

  该方法可用于计数器，在数据表中插入多行以记录不同的计数值。再配合LAST_INSERT_ID()函数的返回值生成不同内容的计数值。这种方法的优点是不用事务或LOCK，UNLOCK表就可生成唯一的序列编号。不会影响其它客户程序的正常表操作。

MySQL4.1以前版本的字符集由服务器默认指定，默认值是编译系统时指定的字符集，该字符集也可通过在启动服务器时指定--default-character-set来修改。这种修改会对数据表的索引造成影响，因为索引的顺序是和字符集有关的，修改字符集会使这个已排序的顺序产生错误。要解决该问题，我们要用修改后的字符集的排序顺序重建表的索引。重建索引有以下几种方法：

MySQL4.1以后的版本对字符集的支持好了很多，具有以下新增功能：

MySQL现在还不支持：1,在同一个字符串里混用不同字符集的字符；2,在同一个数据列里混用不同的字符集。

MySQL提供两种字符集来支持Unicode。一个是UTF-8，一种可变长的编码格式，需用1至4个字节来表示一个字符；另一个是UCS2，该字符集中的每个字符需要用两个字节来表示。

• 存放到数据列中的数据类型。

• 数据值的取值范围。

• 考虑性能和处理效率。

  • 数值操作比字符操作快。

  • 小类型的处理速度比大类型快。

  • 不同数据表中固定长度类型和可变长度类型的处理效率是不同的。

    可变长度类型在经过删除和修改操作后容易产生碎片，降低系统性能，需定期运行OPTIMIZE TABLE命令以优化数据表。

    固定长度类型由于有固定的长度，所以容易确定每条记录的起始点，可加快数据表的修复速度。

    在MyISAM和ISAM表中使用固定长度类型数据列有助改善数据库性能。

    在InnoDB表中，固定长度和可变长度数据列类型都以相同方式存储，所以固定长度数据列类型并没有性能优势，反而由于可度长度数据列类型由于占用存储空间较少，所以处理速度会快些。

  • 可索引类型能加快数据的查询速度。

  • 明确指定数据列的NOT NULL属性可使MySQL在检索过程中不用去判断数据列是否是NULL，所以可加快处理速度。

• 数据如何进行比较，是否区分大小写。

• 是否要在数据列上建立索引。

Table 1.5. 算术操作符

Table 1.6. 逻辑操作符

Table 1.7. 位操作符

Table 1.8. 比较操作符

Table 1.9. 操作符优先级(由高至低排列)

Chapter 2. 查询优化

• 没有索引的表是没有排序的数据集合，如果要查询数据需进行全表扫描。有索引的表是一个在索引列上排序了数据表，可通过索引快速定位记录。在MyISAM和ISAM数据表中，数据行保存在数据文件中，索引保存在索引文件中。BDB与InnoDB数据表把数据与索引放在同一个文件中。

• 在多表关联查询中，索引的作用就更大。如果没有索引，在最坏的情况下，全表扫描的次数可能是各表数据行的组合个数，可能是一个天文数字。这样的查询是破坏性的，可能会造成数据库瘫痪。

• 对于使用了MIN()或是MAX()函数的查询，如果相关的数据列上有索引，MySQL能直接找到该最大、最小值的行，根本不用一个一个地去检查数据行。

• 索引加快ORDER BY 和 GROUP BY子句的操作。

• 当在数值型数据列上查询数据，而该列有索引，索引能使MySQL根本不用去读取数据行，直接从索引取值。

• 索引需占用磁盘空间。

• 索引会减慢在索引数据列上的插入、删除和修改操作。

• 索引应该创建在搜索、排序、分组等操作所涉及的数据列上。也就是说，在where子句，关联检索中的from子句、order by或group by子句中出现过的数据列最适合用来创建索引。

• 尽量使用唯一索引，它能使索引发挥最好的效能。

• 尽量用比较短的值进行索引。当对字符串进行索引时，应该指定一个前缀长度，比如对字符串的前10位或20位的字符进行排序，而不用把整个字符串几十个字符用来索引排序。这样能减少磁盘I/O，提高处理速度。最重要的一点是，键值越短，索引缓冲区里容纳的键值也就越多，而MySQL同时保存在内存里的索引越多，索引缓冲区的命中率也就越高。当然，只对数据列第一个字符进行索引是没什么意义的。

• 充分利用最左前缀。所谓最左前缀也就是在复合索引中最边的索引列。如复合索引(a,b,c) ，其中a就是最左前缀。它是使用率最高的索引，需认真选择。

• 不要建太多索引，索引是会消耗系统资源的，要适可而止。

• 索引主要用于<、<=、=、>=、>、BETWEEN等的比较操作中，所以索引应该建立在与这样操作相关的数据列上。

• 利用慢查询日志来找出性能差的查询，通过mysqldumpslow可查看该日志。针对性能差的查询可利用索引来加快查询速度。

• 尽量对同类型的数据列进行比较。如：VARCHAR(5)和VARCHAR(5)是同类型的，CHAR(5)和VARCHAR(5)是不同类型的。

• 尽量让索引的数据列在比较表达式中单独出现，不要把它包含在函数或复杂表达式。否则索引会不起作用。

• 尽量不要在LIKE模式的开头使用通配符。如：%string%。

• 对于MyISAM和BDB数据表，用ANALYZE TABLE语句让服务器对索引键值的分布进行分析，为优化程序提供更有价值的信息。另一个方法是用myisamchk --analyze(适用于MyISAM表)或isamchk --analyze(适用于ISAM表)命令。

• 用EXPLAIN语句来分析查询语句的执行效率。检查查询所使用的索引是不是能够迅速地排除不符合条件的数据行，如果不是，可以试着用STRAIGHT_JOIN强制各有关数据表按指定顺序进行关联。

• 尝试查询的不同写法，比较运行情况。

• 不要滥用MySQL的类型自动转换功能。自动转换会减慢查询的速度并会使有关的索引失效。

• 尽量选用尺寸较小的数据列。这样能节约磁盘空间和加快查询速度。如果较短的数据列上建有索引，则索引的处理速度会进一步提高。

• 针对数据列类型，尽量选择最适用的数据表类型。如固定长度数据列在MyISAM或ISAM数据表中的速度是最快的，所以在这样数据表中尽量使用char类型而不是varchar类型来保存字符串数据。对于InnoDB数据表类型，由于varchar类型可有效减少占用空间，从而减少磁盘I/O，所以使用varchar类型是有利的。对于BDB类型数据表，使用定长和不定长列类型的区别就不大，可任选一种。

• 尽量把数据列声明为NOT NULL，以节约存储空间和加快处理速度。

• 对于取值范围有限的数据列，考虑使用ENUM数据列类型。ENUM数据列类型在MySQL中的处理速度是很快。

• 使用PROCEDURE ANALYSE()语句来分析数据表，它会对数据列的声明提出建议，我们可根据建议进行修改。

  select * from table_name PROCEDURE ANALYSE();select * from table_name PROCEDURE ANALYSE(16,256);    #(16,256)含义是：如果某列的不同取值在16个以上或长度超过256字节，就不提出使用ENUM的建议。

• 用OPTIMIZE TABLE语句对容易出现碎片的数据表进行整理。包含可变长数据列的数据表都会产生碎片，从而占用多余的磁盘空间和影响查询速度。所以要定期运行OPTIMIZE TABLE语句以防止数据表查询性能降低。但该语句只对MyISAM数据表有效。对各种数据表通用的碎片整理方法是这样的：先用工具程序mysqldump导出数据表，再删除数据表后重建，如：

  $ mysqldump --opt db_name table_name > dump.sql$ mysql db_name < dump.sql

• 把非结构化和变化大的数据放在BLOB数据列里，定期用OPTIMIZE TABLE命令优化。

• 人为地给数据表增加一个数据列，以充当索引。做法是这样的，先根据数据表里的其它数据列计算出一个散列值，并保存在一个数据列里，然后通过搜索散列值来检索数据行。注意，该技巧只适用于精确匹配型查询。散列值在大于，小于等的操作中不起作用。散列值可以MD5()(适用于3.23及以上版本)，SHA1()(适用于4.0.1及以上版本)，CRC32()(适用于4.1及以上版本)等函数生成。使用散列值支检索BLOB和TEXT值的做法比直接检索BLOB和TEXT本身的做法快。

• 尽量避免对大尺寸的BLOB值进行检索。如果要检索都应该通过它的上面提到散列值先进行筛选。而不应该盲目地在网络中传送大量BLOB值。

• 如果把BLOB值剥离到另外一个数据表里去，可实现数据表中其它数据列转变成固定长度数据列的话。就即可减少数据表碎片，又可使在原始表中的select *查询不会把大尺寸的BLOB值不必要地通过网络传送。

• 使用LOAD DATA语句要比INSERT语句的加载速度快。

• LOAD DATA比LOAD DATA LOCAL语句的效率高。前者可由服务器直接从本地磁盘读取加载数据，后者需由客户程序去读取文件并通过网络传送到服务器。

• 如果一定要用INSERT语句，应尽量在一条语句中插入多个数据行。

• 如果必须使用多条INSERT语句，则应尽量把它们集中在一起放到一个事务中进行处理，而不是在自动提交模式下执行它们：如：

  BEGIN;INSERT INTO table_name values (...);INSERT INTO table_name values (...);INSERT INTO table_name values (...);...COMMIT;

  对于不支持事务的表，应对表进行写锁定，然后在表锁定期间对表进行INSERT操作，如：

  LOCK TABLES table_name WRITE;INSERT INTO table_name ...;INSERT INTO table_name ...;INSERT INTO table_name ...;...UNLOCK TABLES;

• 利用客户/服务器通信协议中的压缩功能以减少网络传输的数据量。但该压缩会消耗大量的系统资源，所以小心使用。

• 尽量让MySQL插入默认值。不要在INSERT中写太多值，以减少网络传输量和服务器端的语法分析时间。

• 对于MyISAM和ISAM数据表，如果需加载大量数据，应先建立一个没索引的表，加载数据后再创建索引。该方法不适用于InnoDB或BDB数据表。

• 使用ALTER TABLE语句的DISABLE KEYS和ENABLE KEYS命令，如：

  ALTER TABLE table_name DISABLE KEYS;ALTER TABLE table_name ENABLE KEYS;

• 使用myisamchk或isamchk工具。如：

  $ myisamchk --keys-used=0 table_name                   #禁止$ myisamchk --recover --quick --key-used=n table_name  #激活n是用来表明需要激活索引的位掩码，第0位对应第一个索引，如果有三个索引，n值就是7(二进制111)。索引编号可以下命令确定：$ myisamchk --description table_name

   

• 写入请求将按它们到达服务器的顺序进行处理；

• 写操作的优先级要高于读操作。

• LOW_PRIORITY会使写操作的优先级降低到读操作以下，也就是说读操作会阻塞该级别的写操作，SELECT的HIGH_PRIORITY有类似的作用。

• INSERT语句中的DELAYED修饰会使插入操作被放入一个“延迟插入”队列。并返回状态信息给客户，使客户程序可在新数据行还没插入到数据表之前继续执行后面的操作。如果一直有客户读该数据表，新数据行会一直待在队列中，直到数据表没有读操作时，服务器才会把队列中的数据行真正插入到数据表中。该语句可用在以下场合，在一个有冗长查询的数据表中插入数据，而你又不想被阻塞，你就可发出INSERT DELAYED语句，把插入操作放入服务器“延迟插入”队列，你无需等待就马上可进行接下来的操作。

• 当一个数据表里从未进行过删除操作或刚刚对它进行过碎片整理的情况下，用INSERT语句插入的数据行只会被添加到数据表的末尾，而不会插入到数据表的中间位置。这样，对于MyISAM表，MySQL允许在有其它客户正在读操作的时间进行写操作。我们称之这并发插入。要使用该技巧，需注意以下两个问题：

  • 不要在INSERT语句中使用LOW_PRIORITY修饰符。

  • 读操作应用LOCK TABLES ... READ LOCAL而不是用LOCK TABLES ... READ语句来进行数据表读锁定。LOCAL关键字只对数据表中已存在行进行锁定，不会阻塞把新行添加到数据表末尾。

• MyISAM和ISAM数据表的检索速度最快，但如果在检索和修改操作较多的场合，会出锁竞争的问题，造成等待时间延长。

• BDB和InnoDB数据表能在有大量修改操作的环境下提供很好的并发性，从而提供更好的性能。

• MyISAM和ISAM数据表由于进行表级锁定，所以不会出现死锁现象，BDB和InnoDB数据表则存在死锁的可能性。

• 内存里的数据要比磁盘上的数据访问起来快；

• 站数据尽可能长时间地留在内存里能减少磁盘读写活动的工作量；

• 让索引信息留在内存里要比让数据记录的内容留在内存里更重要。

• 增加服务器的缓存区容量，以便数据在内存在停留的时间长一点，以减少磁盘I/0。下面介绍几个重要的缓冲区：

  • 数据表缓冲区存放着与打开的数据表相的信息，它的大小可由服务器参数“table_cache”设置。Opened_tables参数记录服务器进行过多少次数据表打开操作，如果该值变化很大，就可能是数据表缓冲区已满，需把一些不常用的表移出缓冲区，以腾出空打开新的数据表。可用以下命令查看Opened_tables的值：

    SHOW STATUS LIKE ‘Opened_tables‘;

  • 在MyISAM和ISAM数据表中，索引被缓存在“key buffer”里，它的大小由服务器参数“key_buffer_size”来控制。系统默认的大小是8M，如果内存充足的话可适当扩大该值，以使更多索引块缓存在该区里，以加快索引的速度。

  • InnoDB和BDB数据表也各有一个缓冲区，分别叫innodb_buffer_pool_size和bdb_cache_size。InnoDB还有一个日志缓冲区叫innodb_log_buffer_size。

  • 自4.0.1开始，MySQL多了一个缓冲区，叫查询缓冲区，主要用来存放重复执行的查询文本和结果，当再次遇到相同的查询，服务器会直接从缓冲区中返回结果。该功能是内建的功能，如不想支持该功能，可在编译服务器时用configure脚本的--without-query-cache选项去掉该功能。

    查询缓冲区由三个服务器参数控制，分别是：

    1、query_cache_size    控制缓冲区的大小，如果该值为0,则禁用查询缓冲功能。设置方法是在选项文件中设置:[mysqld]set-variable = query_cache_size = 16M     这样就设置了一个16M的查询缓冲区2、query_cache_limit缓冲结果集的最大容量(以字节为单位)，如果查询的结果集大于该值，则不缓冲该值。3、query_cache_type缓冲区的操作模式。0表示不进行缓冲；1表示除SELECT SQL_NO_CACHE开头的查询外，其余的都缓冲；2表示只对以SELECT SQL_ON_CACHE开头的查询进行缓冲。

    默认情况下，按服务器的设置进行缓冲，但客户端也可通过命令改变服务器设置。客户端可直接用SELECT SQL_NO_CACHE和SELECT SQL_CACHE命令来要求服务器缓冲或不缓冲查询结果。如果不想每条查询都写参数，我们也可在客户端用SET SQL_QUERY_CACHE_TYPE = val;来改变服务器的查询缓冲行为。val可取值0，1,2或OFF，ON，或DEMAND。

• 禁用用不着的数据表处理程序。如服务器是从源码创建，就可彻底禁用ISAM，InnoDB和BDB数据表。

• 权限表里的权限关系应尽可能简单，当然了，是要在保证安全的前提下。

• 在从源码创建服务器时，尽量使用静态库而不是共享库来完成其配置工作。静态库的执行速度更快，但如果要加载用户定义函数(UDF)的话，就不能使用静态库，因为UDF机制必须依赖动态库才能实现。

• 对于数据库服务器，内存是最重要的一个影响性能因素。通过加大内存，数据库服务器可把更多的数据保存在缓冲区，可大大减少磁盘I/O，从而提升数据库的整体性能。

• 配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。

• 合理分布磁盘I/O，应把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。

• 配置多处理器，MySQL是多线程的数据库，多处理器可同时执行多个线程。

Chapter 3. 数据库管理

• 在UNIX/Linux系统上，如果用源码编译安装，数据目录的位置默认是在/usr/local/mysql/var中；

• 在UNIX/Linux系统上，如果用二进制发行版安装，数据目录的位置默认是在/usr/local/mysql/data中；

• 在WINDOWS系统上，数据目录的位置默认是在c:/mysql/data中；

• % mysqladmin variables

  如果在一台机器上同时运行多个服务器，则可根据端口的不时来查询每个服务器的数据目录，如：

  % mysqladmin --host=127.0.0.1 --port=port_number variables

  如果--host是localhost，系统则会用一个UNIX套接字去连接数据库服务器，这时要使用--socket选项，所以查询语句变成：

  % mysqladmin --host=localhost --socket=/path/to/socket variables

• mysql> SHOW VARIABLES LIKE ‘datadir‘;

• 在windows NT平台上可以使用“.”作为一条命名管道连接的主机名，用--socket选项给出命名管道的名字，如：

  c:\ mysqladmin --host=. --socket=pipe_name variables

• 配置文件的中[mysqld]段中的datadir=/path/to/datadir设置也可查询到数据目录。

• 在mysqld程序的帮助信息里也有程序编译时默认的数据目录信息，可用以下命令显示：

  % mysqld --help

• 服务器的配置文件，my.cnf；

• 服务器的进程ID(PID)文件；

• 服务器的日志文件和状态文件，这些文件对管理数据库有重要的价值；

• DES密钥文件或服务器的SSL证书与密钥文件。

• ISAM数据表是最原始的数据表，有三个文件，分别是：

  .frm，存放数据表的结构定义；

  .ISD，数据文件，存放数据表中的各个数据行的内空；

  .ISM，索引文件，存放数据表的所有索引信息。

• MyISAM数据表是ISAM数据表的继承者，也有三个文件，分别是：

  .frm，结构定义文件；

  .MYD，数据文件；

  .MYI，索引文件。

• MERGE数据表是一个逻辑结构，代表一组结构完全相同的MyISAM数据表构成的集合。它在文件系统中有二个文件，分别是：

  .frm，结构定义文件；

  .MRG，构成MERGE表的MyISAM数据表清单，每个MyISAM数据表名占一行。也就是说可通过改变该表的内容来改变MERGE数据表的结构。修改前请先刷新缓存(flush tables)，但不建议这样修改MERGE数据表。

• BDB数据表用两个文件来表示，分别是：

  .frm，结构定义文件；

  .db，数据表数据和索引文件

• InnoDB由于采用表空间的概念来管理数据表，所以它只有一个与数据表对应.frm文件，同一目录下的其它文件表示为表空间，存储数据表的数据和索引。

• HEAP数据表是一个存在于内存中的表，所以它的数据和索引都存在于内存中，文件系统中只有一个.frm文件，以定义结构。

• MySQL数据表类型的不同对数据表尺寸的限制：

  • ISAM数据表中单个.ISD和.ISM文件的最大尺寸为4G；

  • MyISAM数据表中单个.MYD和.MYI文件的默认最大尺寸也是4G，但可在创建数据表时用AVG_ROW_LENGTH和MAX_ROWS选项把这个最值扩大到800万TB。

  • MERGE数据表的最大尺寸是它的各组成MyISAM数据表的最大尺寸之和。

  • BDB数据表的尺寸受限于BDB处理程序所允许的.db文件的最大尺寸。这个最大尺寸随着数据表页面尺寸(编译时确定)而变化，但即使是最小的页面尺寸(512字节)，.db文件的最大尺寸也可达2TB。

  • InnoDB数据表的表空间的最大尺寸是40亿个页面，默认的页面尺寸是16K，该值可在8K到64K之间，在编译时确定。InnoDB数据表的最大尺寸也就是表空间的最大尺寸。

• 操作系统对文件的尺寸限制，一般文件系统都对单个文件不得超过2G的限制。该约束会对数据库文件造成限制。InnoDB数据表可通过利用未格式化硬盘作为表空间来绕过该限制。

• 对于数据和索引分开两个文件存放的数据表，其中任何一个文件达到操作系统文件的最大限制，数据库表也就达到最大尺寸。

• 包含AUTO_INCREMENT数据列的表受到该数据列类型最大上限值的限制。

• 由于InnoDB数据表用表空间来管理，一个表空间可同时空纳多个数据表，所以数据表的最大尺寸受系统文件和同一表空间中数据表空间的约束。

Table 3.1. 状态文件和日志文件

Table 3.2. MySQL数据目录及目录中文件位置的调整方法

• 在调整MySQL的数据目录时，要先停止服务器，再把数据目录移动到新的位置。接着，我们可选择在原来目录下创建一个符号链接指向新的位置，或者用启动选择--datadir指向新的数据目录。推荐用创建符号链接的方法，因为如果那个数据目录中有my.cnf文件，相应的服务器还能找到它。

• 数据库只能存在于MySQL数据目录中，所以只能使用符号链接的方法调整它的位置。在Linux系统的操作步骤如：

  1. 关闭服务器；

  2. 把数据库目录拷贝到新的位置；

  3. 删除原来的数据库目录；

  4. 在原来的MySQL数据目录中创建一个同名符号链接指向新的位置；

  5. 重新启动服务器。

  在windows下的操作方法不些不同，操作方法如下：

  1. 关闭服务器；

  2. 把数据库目录移动新的位置；

  3. 删除原来的数据库目录；

  4. 在原来数据目录下建一个同名的.sym文件，在文件中输入数据库新目录的全路径，如c:\mysql\newdir\mydb。这个文件就相当于Linux下的符号链接；

  5. 重启服务器。

     	为了支持符号链接功能，必须用--use-symbolic-links选项启动服务器；或在选项文件的[mysqld]节中添加use-symbolic-links选项。

     	MySQL必须是3.23.16以上版本且是max服务器(mysqld-max或mysqld-max-nt)。

• 要移动数据表，必须满足以下所有条件才行：

  • MySQL的版本必须是4.0或以上的版本；

  • 操作系统必须有一个可用的realpath()调用；

  • 移动的数据表必须是MyISAM类型的数据表。

  在满足以上所有条件后，我们就可把.MYD数据文件和MYI索引文件移到新位置，再在原来位置创建两个同名符号链接指定新的位置。注意，.frm定义文件仍需留在原来的数据库目录中。

  如以上条件不能全部满足，最好不要移动数据表文件。否则一旦你运行ALTER TABLE、OPTIMIZE TABLE、REPAIR TABLE语句对移动过的数据表进行优化或修改，这样数据表就会回到原来的位置，使移动操作失效。因为这些命令的执行过程是这样的：它会先在数据目录中创建一个临时数据表并对这个临时数据表进行优化或修改，然后删除原来的数据表(这里是你为了移动数据表而创建的一个符号链接)，再把临时数据表更名为原来的数据表名称。这样一来，你移走的数据表就和这个数据库完全没有关系了。基于以下的不稳定因素，如无特殊必要，不建议移动数据表。

• InnoDB表空间是通过在选项文件中使用innodb_data_home_dir和innodb_data_file_path选项列出InnoDB表空间组成文件清单的方法来配置的，所以我们可通过修改这些选项来重新安置InnoDB表空间的组成文件。步骤如下：

  • 关闭服务器；

  • 移动组成表空间的文件；

  • 修改选项文件，指出组成表空间的文件的新位置；

  • 重启服务器。

• 状态文件和日志文件的位置可通过选项文件或启动服务器时指定。

Chapter 4. MySQL数据库日常管理

• User，连接数据库的用户名。

• Host，允许连接到数据库服务器的主机名，“%”通配符代表所有主机。

• Password，连接密码，已加密。

• 其它权限列，以“Y”或“N”标识是否有效。

• 使用mysqladmin程序：

  % mysqladmin -h localhost -u root password "password"   #设置在本地以root身分登录的密码% mysqladmin -h remote -u root password "password"      #设置远程主机以root身分登录的密码

  在初始设置时，这两条语句都要运行，以确保数据库本地访问和网络访问的安全。

• 通过set password这条SQL语句设置：

  mysql> set password for ‘root‘@‘localhost‘ = password(‘password‘);mysql> set password for ‘root‘@‘remote‘ = password(‘password‘);

• 直接修改user权限表：

  mysql> use mysql;mysql> update user set password=password(‘password‘) where user=‘root‘;mysql> flush privileges;                            #重载权限表，使修改马上生效

  MySQL使用驻留在内存中的权限表拷贝来进行访问控制，当使用mysqladmin和set password设置密码，MySQL会监察到权限表已被修改，它自动重载该表。而用update的方式，MySQL就监察不到变化，需手动用flush privileges命令刷新内存中的权限表，以使它马上生效。

• Linux平台：

  Linux平台下，每一个进程都需由一个用户来运行，MySQL最好不要以root用户来运行。我们可创建一个mysql用户和mysql组，MySQL服务器程序目录和数据目录由这个用户和组所拥有，其它用户没有任何权限。以mysql用户来运行MySQL服务器。

  % mysqld --user=mysql     #即使以root用户执行该命令，MySQL数据库还是会与mysql用户ID关联。

  为了使服务器在系统启动时自动以mysql用户运行，需配置my.cnf配置文件 ，把user=mysql包含在[mysqld]段中。

  关闭服务器可用% mysql.server stop或% mysqladmin -u root -p shutdown

• windows平台：

  手动方式：直接运行c:\mysqld命令。

  作为服务方式：运行c:\mysqld-nt --install命令，把mysqld-nt安装为windows的服务，此后，每当windows启动时，它就会自动运行。mysqld-nt是一个支持命名管道的MySQL服务器。运行c:\mysqld-nt --remove可把服务删除。手动启动关闭服务的方法是运行c:\net start mysql和c:\net stop mysql命令。

• 用 % kill -TERM PID关闭服务器，用-TERM信息可使服务器在关闭前把内存中的数据写入磁盘。如果服务器没有响应，我们可用% kill -9 PID来强制删除进程，但不建议这样做。这时内存中的数据不会写入磁盘，造成数据不完整。如果你是用mysql_safe脚本启动MySQL服务器的，这个脚本会监控服务器的运行情况并在它被终止时重启服务器，所以如需关闭服务器，要先终止该进程，然后再真正终止mysqld进程。

• 接着用--skip_grant-tables启动服务器。这时MySQL服务器将不使用权限表对连接操作进行验证。你就可在不提供root密码的情况下连接上服务器，并获得root的权限。这样你就可用上面介绍的修改密码的方法重设root用户的密码。注意：连接上服务器后，要马上执行flush privileges命令，使权限表读入内存并生效，以阻止其他的连接。该语句还重新激活grant语句，在MySQL服务器不使用权限表时，grant语句被禁用。

• 修改完root用户密码后，我们就可关闭服务器并重启使所有配置正常运作。

• GRANT语法说明：

  GRANT privileges (columns)          #privileges表示授予的权限，columns表示作用的列(可选)       ON what                       #设置权限级别，全局级、数据库级、数据表级和数据列级      TO account                    #权限授予的用户，用"user_name"@"host_name"这种用户名、主机名格式      IDENTIFIED BY ‘password‘      #设置用户帐号密码      REQUIRE encryption requirements       #设置经由SSL连接帐号      WITH grant or resource management options;   #设置帐号的管理和资源(连接服务器次数或查询次数等)选项

  示例：

  mysql>grant all on db.* to ‘test‘@‘localhost‘ identified by ‘test‘;上例运行后的效果是，test用户只能通过‘test’密码从本机访问db数据库mysql>grant all on db.* to ‘test‘@‘%‘ identified by ‘test‘;上例运行后的效果是，test用户可通过‘test’密码从任意计算机上访问db数据库。‘%’代表任意字符，‘_’代表一个任意字符。主机名部份还可以是IP地址。

  	如果没有给定主机部份，则默认为任意主机，也就是‘test‘和‘test‘@‘%‘是等价的。

• Table 4.1. 访问权限表

  权限	权限说明
  CREATE TEMPORARY TABLES	创建临时数据表
  EXECUTE	执行存储过程(暂不支持)
  FILE	操作系统文件
  GRANT OPTION	可把本帐号的权限授予其它用户
  LOCK TABLES	锁定指定数据表
  PROCESS	查看运行着的线程信息
  RELOAD	重新加载权限表或刷新日志及缓冲区
  REPLICATION CLIENT	可查询主/从服务器主机名
  REPLICATION SLAVE	运行一个镜像从服务器
  SHOW DATABASES	可运行SHOW DATABASES指令
  SHUTDOWN	关闭数据库服务器
  SUPER	可用kill终止线程以及进行超级用户操作
  ALTER	可修改表和索引的结构
  CREATE	创建数据库和数据表
  DELETE	删除数据表中的数据行
  DROP	删除数据表和数据行
  INDEX	建立或删除索引
  INSERT	插入数据行
  REFERENCES	(暂时不支持)
  SELECT	查询数据行
  UPDATE	更新数据行
  ALL	所有权限，但不包括GRANT。
  USAGE	无权限权限

• Table 4.2. 权限作用范围(由ON子句设置)

  权限限定符	作用范围
  ON *.*	全局级权限，作用于所有数据库
  ON *	全局级权限，若未指定默认数据库，其作用范围是所有数据库，否则，其作用范围是当前数据库
  ON db_name.*	数据库级权限，作用于指定数据库里的所有数据表
  ON db_name.tbl_name	数据表级权限，作用于数据表里的所有数据列
  ON tbl_name	数据表级权限，作用于默认数据库中指定的数据表里的所有数据列

• USAGE权限的用法：修改与权限无关的帐户项，如：

  mysql>GRANT USAGE ON *.* TO account IDENTIFIED BY ‘new_password‘;     #修改密码mysql>GRANT USAGE ON *.* TO account REQUIRE SSL;                      #启用SSL连接mysql>GRANT USAGE ON *.* TO account WITH MAX_CONNECTIONS_PER_HOUR 10; #设置资源 

• 拥有WITH GRANT OPTION权限的用户可把自已所拥用的权限转授给其他用户，如：

  mysql>GRANT ALL ON db.* TO ‘test‘@‘%‘ IDENTIFIED BY ‘password‘ WITH GRANT OPTION;这样test用户就有权把该权限授予其他用户。

• 限制资源使用，如：

  mysql>GRANT ALL ON db.* TO account IDENTIFIED BY ‘password‘ WITH MAX_CONNECTIONS_PER_HOUR 10 MAX_QUERIES_PER_HOUR 200 MAX_UPDATES_PER_HOUR 50;允许account用户每小时最多连接20次服务器，每小时最多发出200条查询命令(其中更新命令最多为50条)

  默认都是零值，即没有限制。FLUSH USER_RESOURCES和FLUSH PRIVILEGES可对资源限制计数器清零。

• REVOKE语法说明：

  mysql>REVOKE privileges (columns) ON what FROM account;

  示例：

  mysql>REVOKE SELECT ON db.* FROM ‘test‘@‘localhost‘;删除test帐号从本机查询db数据库的权限

  REVOKE可删除权限，但不能删除帐号，即使帐号已没有任何权限。所以user数据表里还会有该帐号的记录，要彻底删除帐号，需用DELETE命令删除user数据表的记录，如：

  % mysql -u root -pmysql>use mysqlmysql>DELETE FROM user where User=‘test‘ and Host=‘localhost‘;mysql fulsh privileges;

  REVOKE不能删除REQUIRE和资源占用的配置。他们是要用GRANT来删除的，如：

  GRANT USAGE ON *.* TO account REQUIRE NONE;      #删除account帐号的SSL连接选项GRANT USAGE ON *.* TO account WITH MAX_CONNECTIONS_PER_HOUR 0 MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0;       #删除account帐号的资源限制

Table 4.3. 日志启动选项

Table 4.4. BDB和InnoDB数据表日志选项

在服务器正常运行中，会产生大量的日志文件。我们要对这些日志文件进行失效管理，以节省磁盘空间和方便查询。进行日志失效处理的方式主要有以下几种：

• 服务器的监听端口设置

  • TCP/IP端口3306是MySQL服务器默认的网络监听端口，如用--skip-networking选项启动服务器，则不监听TCP/IP端口。可用--port端口另行指定一个监听端口。如服务器主机有多个IP，还可用--bind-address选项对服务器在监听客户连接时使用的IP地址进行设定。

  • 在UNIX系统上，MySQL可在一个UNIX域套接字文件上监听有无本地客户在试图以localhost为主机名进行连接。默认的套接字文件是/tmp/mysql.sock，可用--socket选项指定另外一个套接字文件。

  • 在基于NT的Windows平台上，有-nt的MySQL服务器都支持命名管道。默认的命名管道是MySql，可用--socket选项另行指定。

• 启用或禁用LOAD DATA语句的LOCAL能力

  • 可在MySQL服务器编译时，用configure脚本的--enable-local-infile或--disable-local-infile选项把LOAD DATA语句的LOCAL能力设置为启用或禁用；

  • 在MySQL服务器启动是，可以用--local-infile或--disable-local-infile选项来启用或禁用服务器的LOCAL能力(在MySQL 4.0.2之前的版本里，要用--local-infile=0来禁用它)。

  如果在服务器端禁用了LOCAL的能力，则客户端就不能使用该功能；如服务器启用了LOCAL的能力，客户端默认也是禁止使用的，但可用mysql程序的--local-infile选项启用它。

• 国际化和本地化，国际化是指软件能够在世界多个国家地区使用，而本地化则是指可从国际化软件中选择一套适合本地区的语言和习惯的设置来使用。在MySQL中的国际化和本地化设置有以下几方面内容：

  • 时区，如果时区设置不对，则服务器显示的时间将会和当地时间有冲突。设置方法可通过mysqld_safe脚本的--timezone选项来设置，但最好还是在选项文件里设置，如:

    [mysqld_safe]timezone=US/Central

  • 配置显示信息的语言，MySQL能用多种语言来显示诊断信息与出错信息，默认是英语。查看share/mysql目录下有几个以语言名称作为目录名的目录就可知道有哪些语言可供选择。可用--language启动选项来指定语言，如--language=/usr/local/mysql/share/mysql/french。

  • 配置服务器的字符集，MySQL支持多种字符集，可在share/mysql/charsets目录下查询支持的字符集，也可用show variables like ‘character_sets‘来显示支持的字符集清单。MySQL把latin1作为默认的字符集。可在编译时用--with-charset指定另外一个字符集为默认字符集。如要增加另外的字符集支持，可用--with-extra-charasets选项进行添加。如：

    % ./configure --with-extra-charsets=latin1,gb2312,big5

    --with-extra-charsets有两个特殊的选项，一个是all，代表所有可用字符集；一个是complex，代表所有的复杂字符集(包括多字节字符集和有特殊排序规则的字符集)。

    服务器启动时，使用默认字符集，如需指定另外的字符集，需用--default-character-set选项指明。

    在MySQL 4.1以前，如果在创建好数据表后改变服务器的默认字符集，就需对索引重新排序才能保证索引键值能够正确反映出数据表记录在新字符集下的排列顺序。重新排序的操作命令如下：

    % myisamchk --recover --quick --set-character-set=gb2312       #在执行该语句需关闭服务器，适用于MyISAM数据表也可用：% mysqlcheck --repair --quick                                  #不需关闭服务器，适用于各种数据表或者用：mysql> REPLACE TABLE ... QUICK；

    在客户端，可用--default-character-set选项指定客户程序使用的字符集。--character-sets-dir选项可指出字符集文件的安装目录。

• 升级数据表到4.1，支持多字符集数据表。步骤如下：

  1. 用mysqldump程序备份数据库：

     % mysqldump -p -u root --all-databases --opt > dumpfile.sql--all-databases选项的作用是转储所有数据库；--opt选项的作用是对转储文件进行优化。

  2. 关闭服务器，升级MySQL服务器软件到4.1版。

  3. 用备份文件重新加载数据表：

     % mysql -p -u root < dumpfile.sql

  这样，字符集信息就被分配到每一个数据列中，此后，即使服务器改变了默认的字符集，各数据列的字符集也不会改变。当以后修改某个数据列的字符集时，服务器会自动重索引，以反映最新变化。

• 配置InnoDB表空间。InnoDB表空间在逻辑上是一个连接的存储区域，但实际上是由一个或多个磁盘文件组成。这些文件可以是普通的文件，也可以是一个未格式化的原始硬盘分区。InnoDB表空间通过一系列的配置选项来设置，其中最重要的有以下两个：

  为确保服务器每次启动时都能调用同样的选项，InnoDB的选项最好存放到选文件中。下面是一个例子：

  innodb_data_home_dir = innodb_data_file_path=/usr/loca/mysql/data/idbdata1:10M:autoextend:max:100M说明：InnoDB表空间文件默认存放到了MySQL的数据目录中，名字叫idbdata1；文件长度为10M；可自动扩展，以8M为步长扩展，如有多个数据文件，只允许最后一个文件可自动扩展；规定了最大的可扩展尺寸为100M。

  • innodb_data_home_dir，设置InnoDB表空间各组成文件的父目录，如果没有指出，则默认是MySQL的数据目录。

  • innodb_data_file_path，描述InnoDB主目录中各有关文件，包括文件名，文件长度和一些选项。各文件以分号分隔，各组成文件长度至少为10M。

  把选项写入选项文件后，启动服务器就可自动创建和初始化InnoDB表空间。

  利用原始磁盘分区作为InnoDB表空间可创建一个非常大的表空间，不受操作系统单文件最大容量的限制。并且能有效减少磁盘碎片的产生。要使用原始磁盘分区，需作如下配置：

  • 首先，要进行初始化，在选项文件的[mysqld]中配置：

    innodb_data_home_dir=innodb_data_file_path=/dev/hda1:10Gnewraw     #初始化/dev/hda1这个10G容量的分区

    启动服务器，服务器会对这个10G的分区进行初始化。

  • 接着，关闭服务器，修改配置文件，把newraw改为raw，如：

    innodb_data_home_dir=innodb_data_file_path=/dev/hda1:10Graw

  重新启动服务器，MySQL就会以读/写方式使用该表空间了。在windows平台上配置InnoDB表空间时，windows路径名中的反斜杠可以写成单个的斜线字符(/)。也可写成两个反斜杠(\\)。如：

  innodb_data_home_dir=innodb_data_file_path=c:/mysql/data/ibdata1:10M;d:/ibdata2:20M

  默认情况下，InnoDB的日志文件会存储在MySQL的数据目录，文件名以ib开头。一旦完成InnoDB表空间的初始化，就不能改变组成文件的大小，但可通过添加数据文件或设置自动扩展来增加表空间容量。如需通过增加文件的方法扩大表空间的容量，可按以下步骤进行：

  1. 关闭正在运行的MySQL服务器

  2. 如果InnoDB表空间的最后一个组成文件是可自扩展的，就要先把它改变成一个固定长度文件才能把另一个文件添加到它后面。方法是先计算出该文件的近似大小，重新设置，如：

     innodb_data_file_path=ibdata1:100M:autoextend改成：innodb_data_file_path=ibdata1:150M

  3. 把新的组成文件添加到文件清单的末尾，该文件可以是普通文件，也可以是一个原始硬盘分区。

  4. 重启服务器。

  还有一种方法重新配置InnoDB表空间，就是先备份，再重新配置，最后重新加载备份。具体步骤如下：

  1. 使用mysqldump备份整个InnoDB数据库；

  2. 关闭服务器，删除所有InnoDB表空间、InnoDB日志文件 及InnoDB数据表的.frm文件；

  3. 重新配置InnoDB表空间；

  4. 配置完成后，用备份文件重载数据，生成新的InnoDB数据表。

• 服务器参数变量的设置。MySQL服务器参数可在服务器启动时设置，在MySQL4.0.3及以后的版本中，有些参数也允许在线设置。在MySQL4.0.2及以后的版本里，可以把一个变量名视为一个选项名来设置。如数据表缓冲区的尺寸由服务器参数talbe_cache来设置。如果需把它设置为128,则可以在命令行里增加

  --table_cache=128

  也可在选项文件中设置：

  [mysqld]table_cache=128       

  在命令行选项中‘_‘可写‘-‘，变成：

  --table-cache=128     #这种写法更像一个标准选项

  还有一种是使用--set-variable或-O选项，如：

  --set-variable=table_cache=128or-O table_cache=128在选项文件中可写成：[mysqld]set-variable=table_cache=128

  服务器参数分为全局级和会话级两个级别。全局级参数将影响整个服务器，会话级参数则只影响某给定客户连接上的工作。如果某个变量同时存在于两个级别，则服务器在客户建立连接时用全局变量的值去初始化相应的会话级参数，一旦客户连接建立起来后，对全局参数所作的修改不会影响到相应的会话级参数当前值。设置全局参数和会话级参数的语句：

  全局级：mysql> SET GLOBAL variable = value;mysql> SET @@GLOBAL.variable = value;会话级：mysql> SET SESSION variable = value;mysql> SET @@SESSION.variable = value;默认不带级别限定符的SET语句修改的参数属会话级，如：mysql> SET variable = value;mysql> SET @@variable = value;可用一条SET语句设置多个参数，参数间用逗号分隔，如：SET SESSION variable = value1,value2,value3;

  SESSION和LOCAL是同义语，可用LOCAL代替SESSION，如：@@LOCAL

  具备SUPER权限才能设置全局参数，新设置值的效力将持续到该参数被再次修改或服务器退出。设置会话级参数不用特殊的权限，新设置值的效力将持续到该值被再次修改或连接断开。显示参数的语句如下：

  SHOW GLOBAL VARIABLES;SHOW GLOBAL VARIABLES LIKE ‘TEST‘;SHOW SESSION VARIABLES;SHOW SESSION VARIABLES LIKE ‘TEST‘;如不带限定符，则返回会话级参数，如会话级参数不存在则返回全局级参数。也可用命令行方式显示服务器参数变量，如：% mysqladmin variables

• 下面介绍一些MySQL服务器通用的参数变量：

  • back_log，当多个客户同时连接服务器时，客户处理过程需进入一个队列排队等待服务器处理。该值定义服务器等待处理队列长度的最大值，如果站点访问量大，需加大该值。

  • delayed_queue_size，在实际插入数据表前，来自insert delayed语句的数据行会进入一个队列等待服务器处理。该值定义该队列能容纳的数据行的最大个数。当队列满时，会阻塞后续的语句。加大该值能提高insert delayed语句的执行速度。

  • flush_time，自动存盘间隔。如果系统经常死机或重启，把这个变量设置为一个适当的非零值，使MySQL服务器每隔flush_time称去刷新一次数据表缓冲区，将其中的信息写入磁盘。这将导致系统性能下降，但可减少数据表被破坏或丢失数据的概率。在命令行上用--flush选项启动服务器可使数据表在每次修改后被自动存盘。

  • key_buffer_size，用来容纳索引块的缓冲区的长度。加大该值可加快索引创建和修改操作的速度，该索引缓冲区越大，在内存中找到键值的可能性就越大，读盘次数就越少。MySQL3.23前的版本里，该参数叫key_buffer。3.23版本之后，两种叫法都可以。

  • max_allowed_packet，服务器与客户程序之间通信时使用的缓冲区在最大值。MySQL 4版本之前，该最大值可取16MB，MySQL 4版本以后，该值的最大值是1GB。如果客户端与服务器需传送大容量的数据，如BLOB或TEXT值，就要加大该值。客户端也有一个同名的变量，默认是16MB，该值也要加大。客户端的启动命令为：

    % mysql --set-variable=max_allowed_packet=64M

  • max_connections，允许同时打开的连接数，如果站点繁忙，需加大该值。

  • table_cache，数据表缓存区的尺寸。加大该值可使服务器能够同时打开更多的数据表，从而减少文件打开/关闭操作的次数。

  注意：加大max_connections和table_cache参数的值，会使服务器占用更多的文件描述符。运行多个服务器可绕过该限制。对一些分配给每个客户的资源变量，设置时不能过大，因为当连接数快速增长时会很快耗尽服务器的资源，造成服务器性能下降。

• InnoDB处理程序变量：

  • innodb_buffer_pool_size，InnoDB数据库缓冲池的大小，如果有足够的内存，可把该值设置得大些以减少读盘操作。

  • innodb_log_file_size和innodb_log_files_in_group，前者设置日志文件的长度，后者设置日志文件的个数。InnoDB日志文件的总长度是两者的乘积，它的总长度不得超过4GB。

• 在安装不同版本的程序时，需分开目录存放程序和数据目录。如果同一版本的服务器软件，则程序目录可一样，但数据目录则要不同。可用--basedir=dir_name和--datadir=dir_name两个启动选项指时这两个目录。

• 要为不同的服务器指定不时的--port=port_name(TCP/IP监听端口)，--socket=file_name(套接字文件名)和--pid-file=file_name(进程ID文件)值。

• 如果激活了日志功能，就要为不同的服务器指定不同的日志文件名。

• 在Windows平台上，被安装为服务的多个MySQL服务器必须有不同的服务名。

• 使用--defaults-file选项指定每个选项文件，这样，每个服务器就不会去读/etc/my.cnf这些配置文件，而会使用你所指定的配置文件。

• 可把一些公共的选项放到/etc/my.cnf文件里，再用--defaults-extra-file选项指出特定服务器的特定选项文件。这样就不用在所有的配置文件时重复公共的选项。

• 用mysql_multi脚本启动服务器，它允许我们把所有的选项放到同一个选项文件里。每一个服务器对应该文件中的一个选项组。

  下面介绍用mysql_multi脚本启动多服务器的方法。

  1. 为每个服务器编一个编号xxx，对应选项文件的[mysqldxxx]选项组。mysql_multi本身要用到的选项可放到[mysqld_multi]里。这样/etc/my.cnf选项配置文件看起来就象下面这样：

     [mysqld001]basedir=/usr/local/mysql/001datadir=/usr/local/mysql/001/datamysqld=/usr/local/mysql/001/bin/mysqld_safesocket=/usr/local/mysql/001/mysql.sockport=3306local-infile=1user=mysqladmlog=loglog-update=update-loginnodb_data_file_path=ibdata1:10M[mysqld002]basedir=/usr/local/mysql/002datadir=/usr/local/mysql/002/datamysqld=/usr/local/mysql/002/bin/mysqld_safesocket=/usr/local/mysql/002/mysql.sockport=3307local-infile=1user=mysqladmlog=loglog-update=update-loginnodb_data_file_path=ibdata1:10M...

  2. 配置好选项文件后，就可用以下命令启动服务器：

     % mysqld_multi --no-log start 001,002      #启动001和002两个服务器，并把启动信息发送到控制台，也可用区间的形式给出服务器编号

     用以下命令可查看服务器状态：

     % mysqld_multi --no-log --user=root --password=password report 001

     可用以下命令停止MySQL服务器：

     % mysqld_multi --no-log --user=root --password=password stop 001

• 在windows平台下运行多个MySQL服务器的方式有两种，一种是运行同一个MySQL程序的两个实例，一种是运行多个windows服务，下面分别介绍：

  • 第一种情况需设置两个选项文件，指定不同的数据目录，如：

    c:\mysql\my.cnf1[mysqld]basedir=c:/mysqldatedir=c:/mysql/data1port=3306c:\mysql\my.cnf2[mysqld]basedir=c:/mysqldatadir=c:/mysql/data2port=3307

    在启动服务器时，用--defaults-file选项指出选项文件即可。如：

    c:\> mysqld --defaults-file=c:\mysql\my.cnf1c:\> mysqld --defaults-file=c:\mysql\my.cnf2

  • 在MySQL 4.0.2版本开始，可以把MySQL安装为一个服务，并可指定一个服务名，如：

    c:\> mysql-nt --install service_name在MySQL 4.0.3开始，安装服务还支持--defaults-file=file_name选项

    这样，我们就可把MySQL安装为一系列不同的服务，如果不指定service_name，则安装的服务名默认为MySql，如果指定service_name，则安装的服务名为指定的service_name，并对应选项文件中的[service_name]选项组。以默认服务名运行的服务器还支持一个名为MySql的命名管道，而明确给出服务名的服务器将只监听TCP/IP连接而不支持命名管道--除非还用socket选项明确指定一个套接字文件。

    移除服务需先用mysqladmin shutdown命令停掉服务器，再执行以下命令：

    c:\> mysql-nt --remove                 #移除默认的服务c:\> mysql-nt --remove service_name    #移除指定服务

• 在镜像关系中，一个MySQL服务器扮演主服务器角色，另外一个或多个服务器扮演从服务器角色，从服务器中的数据和主服务器中的数据完全一样。

• 在镜像建立之前，主服务器和从服务器必须进行一次完全同步。同步之后，在主服务器上所做的操作将会在从服务器上再实现，主服务器上的操作不是直接作用于从服务器上的。

• 负责在主、从服务器上传输各种修改动作的媒介是主服务器上的二进制变更日志，该日志记录着主服务器上所有的操作动作。因此，主服务器必须激活二进制日志功能。

• 从服务器必须有足够的权限从主服务器上接收二进制日志文件。镜像协调信息记录从服务器的进展情况，包括，从服务器正在读取的二进制变更日志文件名和它在该文件里的当前读写位置。

• 每个主服务器可以有多个从服务器，但每个从服务器只能有一个主服务器。但MySQL服务器允许把一个从服务器作为另一个从服务器的主服务器，这样就可创建一个镜像服务器链。

• 3.23.x系统版本的从服务器不能与4.x系统版本的主服务器通信。

• 4.0.0版本的从服务器只能与4.0.0版本的主服务器通信。

• 4.0.1或更高版本的从服务器既能与3.23.x系统版本的主服务器通信，也能与4.x系统版本的主服务器通信。但后一种情况要求主服务器的版本号等于或大于从服务器的版本号。

• 要尽可能地让主服务器和从服务器都使用同一版本系统。

• 在选定系统后，尽量使用该系统的最新版本。

• 确定主从服务器的镜像ID号，主从服务器的ID号不能相同。在启动主从服务器时，用--server_id启动选项给出其ID。

• 从服务器必须在主服务器上有一个具备足够的权限的帐户，从服务器将使用该帐户去连接主服务器并请求主服务器把二进制变更日志发送给它。可用以下命令创建这个帐户：

  mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave_user‘@‘slave_host‘ IDENTIFIED BY ‘slave_pass‘;#REPLICATION权限只MySQL4.0.2后版本，之前的版本请用FILE权限。

• 把主服务器上的数据库文件拷贝到从服务器上完成最初同步工作。也可用备份后再加载的方法。在MySQL 4.0.0及以后版本里，还可用在主服务器上运行LOAD DATA FROM MASTER语句来建立从服务器。但有约束条件：

  • 数据表要全部是MyISAM表

  • 为发出这条指令而在连接从服务器时使用的帐户必须有SUPER权限。

  • 从服务器用来连接主服务器的帐户必须具备RELOAD和SUPER权限。注意，这是一个主服务器上的帐户，而用来发出LOAD DATA FROM MASTER语句的帐户是一个从服务器上的帐户。

  • LOAD DATA FROM MASTER语句在执行时需申请一个全局性的读操作锁，这个锁在语句执行期间阻塞主服务器上一切的写操作。

  无论用哪种方法同步数据，都要确保在开始制作备份到给主服务器重新配置好二进制日志功能这段时间，不能在主服务器上发生修改操作。

• 关闭服务器。

• 对主服务器的配置进行修改--把它的镜像ID告诉它并激活其二进制日志功能。在主服务器要读取的选项文件内增加以下内容：

  [mysqld]server-id=master_server_idlog-bin=binlog_name

• 重新启动主服务器，从现在开始，它将把客户对数据库的修改操作记录到二进制变更日志里。如果在此之前已经激活了二进制日志功能，则要在重启前把二进制变更日志备份下来，在重启后再发一条RESET MASTER语句去清除现有的二进制变更日志。

• 关闭从服务器。

• 对从服务器进行配置，使它知道自已的镜像ID，到哪里去找主服务器以及如何去连接主服务器。配置内容如下：

  [mysqld]server-id=slave_server_idmaster-host=master_hostmaster-user=slave_user          #在主服务器上为从服务器建立的帐户msater-password=slave_pass      #在主服务器上为从服务器建立的帐户的密码master-connet-retry=30         #设置连接重试间隔，默认为60秒master-retry-count=100000       #设置重试次数，默认为86400次注：最后两个选项在网络连接不可靠时设置

• 重新启动从服务器。从服务器用两个信息源来确定它自已在镜像工作中的进度位置：一个是数据目录中的master.info文件，另一个是启动选项所给定的配置信息。第一次启动从服务器时，master.info文件不存在，从服务器会根据选项文件中给出的各种master-xxx选项值去连接主服务器。一旦连接成功，从服务器会创建一个master.info文件以保存各种连接参数和它自已的镜像工作状态。如果以后再重启从服务器，从服务器会优先读取该文件，而不是选项文件。所以如果你修改了选项文件的内容，想该选项生效就要删除master.info文件并重启从服务器。

• SLAVE STOP，SLAVE START用于挂起来恢复从服务器上镜像，如当备份时，可用该语句让从服务器暂时停止镜像活动。

• SHOW SLAVE STATUS，在从服务器上查看其镜像协调信息，这些信息可以用来判断哪些二进制变更日志已经不再使用。

• PURGE MASTER，在主服务器上对二进制变更日志进行失效处理。删除所有从服务器都不再使用的二进制变更日志。

• CHANGE MASTER，在从服务器上修改镜像参数。如正在读取主服务器上哪个二进制变更日志，正在写哪个中继日志文件等。

• 一个叫“I/O线程”，负责与主服务器通信，请求主服务器发送二进制变更日志，并把接收到的数据修改命令写入某个中继日志文件；用SLAVE STOP IO_THREAD或SLAVE START IO_THREAD可挂起或恢复该线程。

• 另一个叫“SQL线程”，负责从中继日志中读取数据修改命令并执行。同理，用SLAVE STOP SQL_THREAD或SLAVE START SQL_THREAD可挂起或恢复该线程。

Chapter 5. 数据库安全

• 在重设置文件权限时，请先关闭数据库服务器。

• 用以下命令把MySQL安装程序目录的属主和所属组设置为MySQL管理员帐号的用户名和用户组名。

  % chown -R mysql.mysql /usr/local/mysql

  另外一种方法是把除数据目录外的所有目录属主设置为root所有，如：

  % chown -R root.mysql /usr/local/mysql% chown -R mysql.mysql /usr/local/mysql/data

• 设置安装目录及各有关子目录的权限，允许管理员进行所有操作，只允许其他人进行读和执行访问，设置命令如下：

  #设置mysql目录% chmod 755 /usr/local/mysqlor% chmod u=rwx,go=rx /usr/local/mysql#设置mysql/bin目录% chmod 755 /usr/local/mysql/binor% chmod u=rwx,go=rx /usr/local/mysql/bin#设置mysql/libexec目录% chmod 700 /usr/local/mysql/libexecor% chmod u=rwx,go-rwx /usr/local/mysql/libexec

• 把数据目录及目录中的所有子目录和文件设置为只允许MySQL管理员访问。

  % chmod -R go-rwx /usr/local/mysql/data

  如果数据目录下有选项文件或套接字文件，并一些客户需访问这些文件，则可用以下的权限设置，使客户在没有读权限的前提下使用这些文件：

  % chmod go+x /usr/local/mysql/data

• mysql.sock套接字文件一般放以/tmp目录下，要确保该目录设置了粘着位，使自户只能删除自已创建的文件，不能删除其他用户创建的文件。/etc/my.cnf中公共选项文件，是对所有用户可读的，所以不应把一些敏感信息保存在里面。.my.cnf是用户专用选项文件，要确保只有该用户有权访问。

• 这样设置以后，只有MySQL管理员才能启动服务器。

• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。

• db权限表：记录各个帐号在各个数据库上的操作权限。

• table_priv权限表：记录数据表级的操作权限。

• columns_priv权限表：记录数据列级的操作权限。

• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

• CREATE TEMPORARY TABLES，允许创建临时表的权限。

• EXECUTE，允许执行存储过程的权限，存储过程在MySQL的当前版本中还没实现。

• FILE，允许你通过MySQL服务器去读写服务器主机上的文件。但有一定限制，只能访问对任何用户可读的文件，通过服务器写的文件必须是尚未存在的，以防止服务器写的文件覆盖重要的系统文件。尽管有这些限制，但为了安全，尽量不要把该权限授予普通用户。并且不要以root用户来运行MySQL服务器，因为root用户可在系统任何地方创建文件。

• GRANT OPTION，允许把你自已所拥有的权限再转授给其他用户。

• LOCK TABLES，可以使用LOCK TABLES语句来锁定数据表

• PROCESS，允许你查看和终止任何客户线程。SHOW PROCESSLIST语句或mysqladmin processlist命令可查看线程，KILL语句或mysqladmin kill命令可终止线程。在4.0.2版及以后的版本中，PROCESS权限只剩下查看线程的能力，终止线程的能力由SUPER权限控制。

• RELOAD，允许你进行一些数据库管理操作，如FLUSH，RESET等。它还允许你执行mysqladmin命令：reload，refresh，flush-hosts，flush-logs，flush-privileges，flush-status，flush-tables和flush-threads。

• REPLICATION CLIENT，允许查询镜像机制中主服务器和从服务器的位置。

• REPLICATION SLAVE，允许某个客户连接到镜像机制中的主服务器并请求发送二进制变更日志。该权限应授予从服务器用来连接主服务器的帐号。在4.0.2版这前，从服务器是用FILE权限来连接的。

• SHOW DATABASES，控制用户执行SHOW DATABASES语句的权限。

• SUPER，允许终止线程，使用mysqladmin debug命令，使用CHANGE MASTER，PURGE MASTER LOGS以及修改全局级变量的SET语句。SUPER还允许你根据存放在DES密钥文件里的密钥进行DES解密的工作。

• NONE，默认值，表示不需加密连接。

• ANY，表示需要加密连接，可以是任何一种加密连接。由GRANT的REQUIRE SSL子句设置。

• X509，表示需要加密连接，并要求客户提供一份有效的X509证书。由GRANT的REQUIRE X509子句设置。

• SPECIFIED，表示加密连接需满足一定要求，由REQUIRE子句的ISSUER，SUBJECT或CIPHER的值进行设置。只要ssl_type列的值为SPECIFIED，则MySQL会去检查ssl_cipher(加密算法)、x509_issuer(证书签发者)和x509_subject(证书主题)列的值。这几列的列类型是BLOB类型的。

• max_connections，每小时可连接服务器的次数。

• max_questions，每小时可发出查询命令数。

• max_updates，每小时可以发出的数据修改类查询命令数。

• 删除所有匿名用户。

• 查出所有没有口令用户，重新设置口令。可用以下命令查询空口令用户：

  mysql> SELECT host,user FROM user WHERE password = ‘‘;

• 尽量不要在host中使用通配符。

• 最好不要用user权限表进行授权，因为该表的权限都是全局级的。

• 不要把mysql数据库的权限授予他人，因为该数据库包含权限表。

• 使用GRANT OPTION权限时不要滥用。

• FILE权限可访问文件系统中的文件，所以授权时也要注意。一个具有FILE权限的用户执行以下语句就可查看服务器上全体可读的文件：

  mysql> CREATE TABLE etc_passwd(pwd_entry TEXT);mysql> LOAD DATA INFILE ‘/etc/passwd‘ INTO TABLE etc_passwd;mysql> SELECT * FROM etc_passwd；

  如果MySQL服务器数据目录上的访问权限设置得不好，就会留下让具有FILE权限的用户进入别人数据库的安全漏洞。所以建议把数据目录设置成只能由MySQL服务器读取。下面演示一个利用具有FILE权限的用户读取数据目录中文件权限设置不严密的数据库数据的过程：

  mysql> use test;mysql> create table temp(b longblob);mysql> show databases                   #显示数据库名清单，--skip-show-database可禁止该功能mysql> load data infile ‘./db/xxx.frm‘ into table temp fields escaped by ‘‘ lines terminated by ‘‘;mysql> select * from temp into outfile ‘xxx.frm‘ fields escaped by ‘‘ lines terminated by ‘‘;mysql> delete from temp;mysql> load data infile ‘./db/xxx.MYD‘ into table temp fields escaped by ‘‘ lines terminated by ‘‘;mysql> select * from temp into outfile ‘xxx.MYD‘ fields escaped by ‘‘ lines terminated by ‘‘;mysql> delete from temp;mysql> load data infile ‘./db/xxx.MYI‘ into table temp fields escaped by ‘‘ lines terminated by ‘‘;mysql> select * from temp into outfile ‘xxx.MYI‘ fields escaped by ‘‘ lines terminated by ‘‘;mysql> delete from temp;

  这样，你的数据库就给人拷贝到本地了。如果服务器是运行在root用户下，那危害就更大了，因为root可在服务器上做任何的操作。所以尽量不要用root用户来运行服务器。

• 只把PROCESS权限授予可信用户，该用户可查询其他用户的线程信息。

• 不要把RELOAD权限授予无关用户，因为该权限可发出FLUSH或RESET语句，这些是数据库管理工具，如果用户不当使用会使数据库管理出现问题。

• ALTER权限也不要授予一般用户，因为该权限可更改数据表。

• 当你发送一条GRANT语句时，服务器会在user权限表里创建一个记录项并把你用户名、主机名和口令记录在User、Host和Password列中。如果设置了全局权限，由把该设置记录在相在的权限列中。

• 如果在GRANT里设置了数据库级权限，你给出的用户名和主机名就会记录到db权限表的User和Host列中，数据库名记录在Db列中，权限记录到相关的权限列中。

• 接着是到数据表和数据列级的权限设置，设置方法和上面的一样。服务器会把用户名、主机名、数据库名以及相应的数据表名和数据列名记录到数据表中。

• user权限表里要有相关的SSL数据列。如果安装的MySQL服务器是4.0.0版的，user权限表已包含相关的SSL数据列，否则，我们也可用mysql_fix_privilege_tables脚本升级权限表。

• 服务器和客户程序都已经编译有OpenSSL支持。首先要安装openssl，在编译时MySQL服务器时加--with-vio和--with-openssl选项加上openssl支持。可用以下语句查询服务器是否支持SSL：

  mysql> show variables like ‘have_openssl‘;

• 在启动服务器时用有关选项指明证书文件和密钥文件的位置。在建立加密连接前，要准备三个文件，一个CA证书，是由可信赖第三方出具的证书，用来验证客户端和服务器端提供的证书。CA证书可向商业机构购买，也可自行生成。第二个文件是证书文件，用于在连接时向对方证明自已身份的文件。第三个文件是密钥文件，用来对在加密连接上传输数据的加密和解密。MySQL服务器端的证书文件和密钥文件必须首先安装，在sampdb发行版本的ssl目录里有几个供参考的样本文件：ca-cert.pem(CA证书)，server-cert.pem(服务器证书)，server-key.pem(服务器公共密钥)。把这几个文件拷贝到服务器的数据目录中，再在选项文件里加上以下内容：

  [mysqld]ssl-ca=/usr/local/mysql/data/ca-cert.pemssl-cert=/usr/local/mysql/data/server-cert.pemssl-key=/usr/local/mysql/data/server-key.pem

  重启服务器，使配置生效。

• 要想让某个客户程序建立加密连接，必须在调用这个客户程序时用有关选项告诉它在哪里能找到其证书文件和密钥文件。在sampdb发行版的ssl目录中提供了client-cert.pem(客户证书文件)，client-key.pem(客户密钥文件)，CA证书与服务器使用同样的ca-cert.pem。把他们拷贝到个人目录下，并在.my.cnf选项文件中指出文件位置，如：

  [mysql]ssl-ca=/home/mysql/ca-cert.pemssl-cert=/home/mysql/client-cert.pemssl-key=/home/mysql/client-key.pem

  配置完成后，调用mysql程序运行\s或SHOW STATUS LIKE ‘SSL%‘命令，如果看到SSL:的信息行就说明是加密连接了。如果把SSL相关的配置写进选项文件，则默认是加密连接的。也可用mysql程序的--skip-ssl选项取消加密连接。如果用命令行方式启用加密连接可以这样写：

  % mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

Chapter 6. 数据库的备份、维护和修复

• 以mysql客户端程序连接服务器，并发出LOCK TABLE命令，以只读方式锁住数据表。

  % mysqlmysql> use dbmysql> LOCK TABLE table_name READ;     #以只读方式锁定表mysql> FLUSH TABLE table_name;        #关闭数据表文件，并把内存中的信息写入磁盘

• 保持上面的状态不退出，另开一个shell窗口，用以下命令维护(检查)数据表。

  % myisamchk table_name

  	如果不保持上面状态，退出mysql会话，则表锁定会自动取消。

• 维护完成，切换回mysql状态的shell窗口，发出以下命令解除表锁定。

  mysql> UNLOCK TABLES;

• 进行修复操作需以读/写方式锁定数据表，命令如下：

  % mysqlmysql> use dbmysql> LOCK TABLE table_name WRITE;     #以读/写方式锁定数据表mysql> FLUSH TABLE table_name;

• 保持mysql客户端连接状态，切换到第二个shell窗口，运行修复命令：

  % myisamchk --recover table_name

  	运行修复命令前最好先备份一下数据文件。

• 修复完成后，切换回mysql客户端连接窗口，运行以下命令解除数据表锁定：

  mysql> FLUSH TABLE table_name;     #使服务器觉察新产生的索引文件mysql> UNLOCK TABLE;

• 使用mysqldump备份数据库其实就是把数据库转储成一系列CREATE TABLE和INSERT语句，通过这些语句我们就可重新生成数据库。使用mysqldump的方法如下：

  % mysqldump --opt testdb | gzip > /data/backup/testdb.bak#--opt选项会对转储过程进行优化，生成的备份文件会小一点，后的管道操作会进行数据压缩% mysqldump --opt testdb mytable1,mytable2 | gzip > /data/backup/testdb_mytable.bak#可在数据库后接数据表名，只导出指定的数据表，多个数据表可用逗号分隔

  	--opt选项还可激活--add-drop-table选项，它将会在备份文件的每条CREATE TABLE前加上一条DROP TABLE IF EXISTS语句。这可方便进行数据表的更新，而不会发生“数据表已存在”的错误。

  用mysqldump命令还可直接把数据库转移到另外一台服务器上，不用生成备份文件。重复执行可定期更新远程数据库。

  % mysqladmin -h remote_host create testdb% mysqldump --opt testdb | mysql -h remote_host testdb另外还可通过ssh远程调用服务器上的程序，如：% ssh remote_host mysqladmin create testdb% mysqldump --opt testdb | ssh remote_host mysql testdb

• 通过直接拷贝系统文件的方式备份数据库，在备份时，要确保没有人对数据库进行修改操作。要做到这点，最好关闭服务器。如果不能关闭的，要以只读方试锁定有关数据表。下面是一些示例：

  % cp -r db /backup/db                 #备份db数据库到/backup/db目录% cp table_name.* /backup/db          #只备份table_name数据表% scp -r db remotehot:/usr/local/mysql/data      #用scp把数据库直接拷贝到远程服务器

  	在把数据库直接拷贝到远程主机时，应注意两台机器必须有同样的硬件结构，或者将拷贝的数据表全部是可移植数据表类型。

• 使用mysqlhotcopy工具，它是一个Perl DBI脚本，可在不关闭服务器的情况下备份数据库，它主要的优点是：

  • 它直接拷贝文件，所以它比mysqldump快。

  • 可自动完成数据锁定工作，备份时不用关闭服务器。

  • 能刷新日志，使备份文件和日志文件的检查点能保持同步。

  下面是该工具的使用示例：

  % mysqlhotcopy db  /bakcup/                 #把db数据库备份到backup/db目录里，会自动创建一个db目录

• 使用BACKUP TABLE语句进行备份，该语句最早出现在MySQL 3.23.25版本中，仅适用于MyISAM数据表。用法如下：

  mysql> BACKUP TABLE mytable TO ‘/backup/db‘;         #把mytable数据表备份到/backup/db目录下

  为了执行该语句，你必须拥有那些表的FILE权限和SELECT权限，备份目录还必须是服务器可写的。该语句执行时，会先把内存中的数据写入磁盘，再把各个数据表的.frm(表结构定义文件)、.MYD(数据)文件从数据目录拷贝到备份目录。它不拷贝.MYI(索引)文件，因为它能用另外两个文件重建。BACKUP TABLE语句备份时，依次锁定数据表，当同时备份多个数据表时，数据表可能会被修改，所以备份0完成时，备份文件中的数据和现时数据表中的数据可能会有差异，为了消除该差异，我们可用只读方式锁定数据表，在备份完成后再解锁。如：

  mysql> LOCK TABLES tb1 READ,tb2 READ;mysql> BACKUP TABLE tb1,tb2 TO ‘backup/db‘;mysql> UNLOCK TABLES;

  使用BACKUP TABLE语句备份的数据表可用RESTORE TABLE重新加载到服务器。

• InnoDB和BDB数据库也可用mysqldump和直接拷贝法进行备份。使用直接拷贝法时应注意需把组成InnoDB和BDB数据库的所有文件都拷贝下来，如InnoDB的.frm文件、日志文件和表空间配置文件；BDB的数据文件、日志文件等。

• 使用镜像机制进行备份，我们可用SLAVE STOP语句挂起从服务器的镜像，在从服务器上通过直接拷贝法或其它工具制作备份。备份完成，用SLAVE START重新启动镜像，从服务器重新与主服务器同步，接收备份时主服务器所做的修改。

1. 把需恢复的数据库的整个目录的内容拷贝到其它地方，以备用。

2. 使用最近的备份文件重载数据库。如果使用mysqldump生成的备份，则可使用它们作为mysql的输入重载；如果是通过拷贝数据库目录来备份的，则要关闭数据库服务器，再把备份重新拷贝到数据目录，再重启数据库服务器。

3. 通过二进制日志重做事务，恢复到出错点的数据。具体操作是这样的，用mysqlbinlog把日志转换成ASCII格式，再把它作为mysql的输入，并指定--one-database选项，以便mysql只执行你指定的数据库恢复。如：

   % ls -t -r -l binlog.[0-9]* | xargs mysqlbinlog | mysql --one-database db_name

   但上面命令只适合所有日志文件名具有相同长度的情况。否则需用下面的Perl代码来处理：

   #!/usr/bin/perl -w# sort_num.pluse strict;my @files = <>;             #read all input file@files = sort { my $anum = $1 if $a =~/\.(\d+)$/;         #sort them by numeric extension                my $bnum = $1 if $b =~/\.(\d+)$/;                $anum <=> $bnum;              } @files;print @files;              #print themexit(0);如下使用该脚本：% ls -l binlog.[0-9]* | sort_num.pl | xargs mysqlbinlog | mysql --one-database db_name

   上面讨论的是需所有日志文件的情况，但多数情况下我们只需备份后生成的几个日志文件，这时，可用以下命令来重做。

   % mysqlbinlog binlog.1234 | mysql --one-database db_name% mysqlbinlog binlog.1235 | mysql --one_database db_name...

   如果我们需恢复因执行DROP DATABASE，DROP TABLE或DELETE语句而损坏的数据库，就需在日志文件中删除该语句，否则重做后结果还是一样。所以需把日志转换成ASCII格式并保存起来，再用编辑器打开该文件，删除这些语句后再重做。

   	如果使用文本变更日志，则不需使用mysqlbinlog程序，因为该日志文件本身就是ASCII格式。

Chapter 7. MySQL程序介绍

Chapter 8. MySQL How-To

• -h host_name(--host=host_name)，连接的数据库主机名，如果在本地主机上则可省略。

• -u user_name(--user=user_name)，数据库用户名，在unix系统上，如果系统的登录名与数据用户名一样，则可省略。在windows系统中，可通过设置环境变量USER来给出数据库用户名，如set USER=username。

• -p(--password)，提供数据库用户密码，有该选项mysql就会提示你输入密码。输入的密码以星号显示，以确保安全。也可直接在-p后写上密码(-p和密码间不能有空格)，但这不安全，不推荐。

• Linux/Unix平台下：

  1. 用 % kill -TERM PID关闭服务器，用-TERM信息可使服务器在关闭前把内存中的数据写入磁盘。如果服务器没有响应，我们可用% kill -9 PID来强制删除进程，但不建议这样做。这时内存中的数据不会写入磁盘，造成数据不完整。如果你是用mysql_safe脚本启动MySQL服务器的，这个脚本会监控服务器的运行情况并在它被终止时重启服务器，所以如需关闭服务器，要先终止该进程，然后再真正终止mysqld进程。

  2. 使用--skip-grant-tables参数启动MySQL Server，这时MySQL服务器将不使用权限表对连接操作进行验证。你就可在不提供root密码的情况下连接上服务器，并获得root的权限。

     % mysqld_safe --skip-grant-tables & 

  3. 用以下命令登录服务器，并重设密码：

      % mysql -u root         #不用密码就可连接到服务器mysql> use mysqlmysql> set password for ‘root‘@‘localhost‘ = password(‘password‘);

     	修改MySQL服务器帐户密码有三种方式，你可参考本笔记中数据库日常管理一章中的相关内容。在这种环境下，使用mysaladmin修改密码好象不行，还是提示要输入密码。

  4. 关闭服务器，再用正常方式启动服务器。

• windows平台下：

  1. 用管理员帐号登录服务器，关闭MySQL数据库服务器。

  2. 使用--skip-grant-tables参数启动服务器：

     c:\mysql\bin>mysqld-nt --skip-grant-tables 

  3. 重新打开一个console窗口，用mysql命令登录服务器设置root的新密码：

     c:\mysql\bin>mysqlmysql> use mysqlmysql> set password for ‘root‘@‘localhost‘ = password(‘password‘);

  4. 关闭服务器，再用正常方式启动服务器。