MySQL 学习笔记

整理：Jims of 肥肥世家

[email protected]

Copyright © 2004 本文遵从GNU 的自由文档许可证(Free Document License)的条款，欢迎转载、修改、散布。

发布时间: 2004年11月06日

最近更新: 2005年01月12日

Table of Contents

Chapter 1. MySQL数据库的数据

MySQL数据库是由数据组成的，为了能方便管理和使用这些数据，我们把这些数据进行分类，形成各种数据类型，有数据值的类型，有表中数据列的类型，有数据表的类型。理解MySQL的这些数据类型能使我们更好地使用MySQL数据库。下面对各种数据类型进行简单的介绍。

1.1. 数据值类型(data type)

对MySQL中数据值的分类，有数值型、字符型、日期型和空值等，这和一般的编程语言的分类差不多。

1.1.1. 数值

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

1.1.2. 字符串

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

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

Table 1.1. 转义序列

转义序列 含义
\0 NUL(ASCII的0值)
\’ 单引号
\” 双引号
\b 后退符
\n 换行符
\r 回车符
\t 制表符
\ 反斜杠
\Z Ctrl+Z

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

  • 字符串的引号和字符串两端的引号双同，则双写该引号。如：’mysql’’s test’。

  • 用与字符串的引号不同的引号把字符串引起来，如：”mysql’s test”。

  • 用反斜杠转义引号，如：”mysql\’ test”，’mysql\’ test’。这样就不用理会字符串两端的是单引号还是双引号了。

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

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

1.1.3. 日期和时间

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

1.2. 列类型(column type)

MySQL数据库的表是一个二维表，由一个或多个数据列构成。每个数据列都有它的特定类型，该类型决定了MySQL如何看待该列数据，我们可以把整型数值存放到字符类型的列中，MySQL则会把它看成字符串来处理。MySQL中的列类型有三种：数值类、字符串类和日期/时间类。从大类来看列类型和数值类型一样，都是只有三种。但每种列类型都还可细分。下面对各种列类型进行详细介绍。

1.2.1. 数值类的数据列类型

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

Table 1.2. 数值类数据列类型

数据列类型 存储空间 描述
TINYINT 1字节 非常小的正整数，带符号：-128~127，不带符号：0~255
SMALLINT 2字节 小整数，带符号：-32768~32767，不带符号：0~65535
MEDIUMINT 3字节 中等大小的整数，带符号：-8388608~8388607，不带符号：0~16777215
INT 4字节 标准整数，带符号：-2147483648~2147483647，不带符号：0~4294967295
BIGINT 8字节 大整数，带符号：-9223372036854775808~9233372036854775807，不带符号：0~18446744073709551615
FLOAT 4字节 单精度浮点数，最小非零值：+-1.175494351E-38，最大非零值：+-3.402823466E+38
DOUBLE 8字节 双精度浮点数，最小非零值：+-2.2250738585072014E-308，最大非零值：+-1.7976931348623157E+308
DECIMAL M+2字节 以字符串形式表示的浮点数，它的取值范围可变，由M和D的值决定。

1.2.1.1. 整型数据列类型

MySQL有五种整型数据列类型，即TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT。它们之间的区别是取值范围不同，存储空间也各不相同。在整型数据列后加上UNSIGNED属性可以禁止负数，取值从0开始。

声明整型数据列时，我们可以为它指定个显示宽度M(1~255)，如INT(5)，指定显示宽度为5个字符,如果没有给它指定显示宽度，MySQL会为它指定一个默认值。显示宽度只用于显示，并不能限制取值范围和占用空间，如：INT(3)会占用4个字节的存储空间，并且允许的最大值也不会是999,而是INT整型所允许的最大值。

1.2.1.2. 浮点型数据列类型

MySQL有三种浮点型数据列类型，分别是：FLOAT，DOUBLE和DECIMAL。浮点类数据类型有一个最大可表示值和一个最小非零可表示值，最小非零可表示值决定了该类型的精确度。

MySQL 4.0.2版之后，FLOAT和DOUBLE都可以指定UNSIGNED属性。当指定该属性时，取值范围不平移到正数区间，而只是简单地把浮点类型的负数部份去掉。

浮点类型也有M(1~255)和D(1~30，且不能大于M-2)。分别表示显示宽度和小数位数。M和D在FLOAT和DOUBLE中是可选的，默认，当MySQL版本大于3.23.6时，FLOAT和DOUBLE类型将被保存为硬件所支持的最大精度。DECIMAL的M和D值在MySQL3.23.6后可选，默认D值为0,M值为10。

1.2.1.3. 如何选择数值类数据列类型？

为了节省存储空间和提高数据库处理效率，我们应根据应用数据的取值范围来选择一个最适合的数据列类型。如果把一个超出数据列取值范围的数存入该列，则MySQL就会截短该值，如：我们把99999存入SMALLINT(3)数据列里，因为SMALLINT(3)的取值范围是-32768~32767，所以就会被截短成32767存储。显示宽度3不会影响数值的存储。只影响显示。

对于浮点数据列，存入的数值会被该列定义的小数位进行四舍五入。如把一个1.234存入FLOAT(6.1)数据列中，结果是1.2。

DECIMAL与FLOAT和DOUBLE的区别是：DECIMAL类型的值是以字符串的形式被储存起来的，它的小数位数是固定的。它的优点是，不会象FLOAT和DOUBLE类型数据列那样进行四舍五入而产生误差，所以很适合用于财务计算；而它的缺点是：由于它的存储格式不同，CPU不能对它进行直接运算，从而影响运算效率。DECIMAL(M，D)总共要占用M+2个字节。

1.2.1.4. 数值类数据列的属性

  • ZEROFILL属性适用于所有数值类数据列类型，作用是，如果数值的宽度小于定义的显示宽度，则在数值前填充0。

  • UNSIGNED属性不允许数据列出现负数。

  • AUTO_INCREMENT属性可生成独一无二的数字序列。只对整数类的数据列有效。

  • NULL和NOT NULL属性设置数据列是否可为空。

  • DEFAULT属性可为数据列指定默认值。

1.2.2. 字符串类数据列类型

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

Table 1.3. 字符串类数据列类型

类型 最大长度 占用存储空间
CHAR[(M)] M字节 M字节
VARCHAR[(M)] M字节 L+1字节
TINYBLOD，TINYTEXT 2^8-1字节 L+1字节
BLOB，TEXT 2^16-1字节 L+2
MEDIUMBLOB，MEDIUMTEXT 2^24-1字节 L+3
LONGBLOB，LONGTEXT 2^32-1字节 L+4
ENUM(‘value1’,’value2’,…) 65535个成员 1或2字节
SET(‘value1’,’value2’,…) 64个成员 1,2,3,4或8字节

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

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

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

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

  • 二进制字符串被视为一个连续的字节序列，与字符集无关。MySQL把BLOB数据列和带BINARY属性的CHAR和VARCHAR数据列里的数据当作二进制值。

  • 非二进制字符串被视为一个连续排列的字符序列。与字符集有关。MySQL把TEXT列与不带BINARY属性的CHAR和VARCHAR数据列里的数据当作二进制值对待。

在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，以减少空间占用量。

1.2.2.2. BLOB和TEXT

BLOB是二进制字符串，TEXT是非二进制字符串。两者都可存放大容量的信息。

有关BLOB和TEXT索引的建立：

  • BDB表类型和MySQL3.23.2以上版本的MyISAM表类型允许在BLOB和TEXT数据列上建立索引。

  • ISAM、HEAP和InnoDB表不支持大对象列的索引。

使用BLOB和TEXT应注意的问题：

  • 由于这两个列类型所存储的数据量大，所以删除和修改操作容易在数据表里产生大量的碎片，需定期运行OPTIMIZE TABLE以减少碎片和提高性能。

  • 如果使用的值非常巨大，就需对服务器进行相应的优化调整，增加max_allowed_packet参数的值。对那些可会用到变些巨大数据的客户程序，也需加大它们的数据包大小。

1.2.2.3. ENUM和SET

ENUM和SET都是比较特殊的字符串数据列类型，它们的取值范围是一个预先定义好的列表。ENUM或SET数据列的取值只能从这个列表中进行选择。ENUM和SET的主要区别是：

  • ENUM只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有65535个成员。例如：ENUM(“N”,”Y”)表示，该数据列的取值要么是”Y”，要么就是”N”。

  • SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。

ENUM和SET的值是以字符串形式出现的，但在内部，MySQL以数值的形式存储它们。

  • ENUM的合法取值列表中的字符串被按声明定义的顺序被编号，从1开始。

  • SET的编号不是按顺序进行编号的，SET中每一个合法取值都对应着SET值里的一个位。第一个合法取值对应0位，第二个合法取值对应1位，以此类推，如果数值形式的SET值等于0,则说明它是一个空字符串，如果某个合法的取值出现在SET数据列里，与之对应的位就会被置位；如果某个合法的取值没有出现在SET数据列里，与之对应的位就会被清零。正因为SET值与位有这样的对应关系，所以SET数据列的多个合法取值才能同时出现并构成SET值。

1.2.2.4. 字符串类型数据列的字符集属性

在MySQL 4.1以前的版本，字符串数据列的字符集由服务器的字符决定，MySQL 4.1版以后的版本可对每个字符串数据列指定不同的字符串。如果按默认方式设置，可按数据列、数据表、数据库、服务器的顺序关联字符串的字符集，直到找一个明确定义的字符集。

1.2.3. 日期,时间型数据列类型

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

Table 1.4. 日期，时间类型列

类型 取值范围 存储空间 零值表示法
DATE 1000-01-01~9999-12-31 3字节(MySQL3.23版以前是4字节 ) 0000-00-00
TIME -838:59:59~838:59:59 3字节 00:00:00
DATETIME 1000-01-01 00:00:00~9999-12-31 23:59:59 8字节 0000-00-00 00:00:00
TIMESTAMP 19700101000000~2037年的某个时刻 4字节 00000000000000
YEAR YEAR(4)：1901~2155 YEAR(2)：1970~2069 1字节 0000

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

1.2.3.1. DATE、TIME、DATATIME数据列类型

DATE、TIME和DATATIME类型分别存放日期值、时间值、日期和时间值的组合。它们的格式分别是“CCYY-MM-DD”、“hh:mm:ss”、“CCYY-MM-DD hh:mm:ss”。

DATATIME里的时间值和TIME值是有区别的，DATATIME里的时间值代表的是几点几分，TIME值代表的是所花费的时间。当向TIME数据列插值时，需用时间的完整写法，如12分30秒要写成“00:12:30”。

1.2.3.2. TIMESTAMP数据列类型

TIMESTAMP数据列的格式是CCYYMMDDhhmmss，取值范围从19700101000000开始，即1970年1月1号，最大到2037年。它的特点是能把数据行的创建或修改时间记录下来：

  • 如果把一个NULL值插入TIMESTAMP列，这个数据列就将自动取值为当前的日期和时间。

  • 在创建和修改数据行时，如果没有明确对TIMESTAMP数据列进行赋值，则它就会自动取值为当前的日期和时间。如果行中有多个TIMESTAMP列，只有第一个会自动取值。

  • 如果对TIMESTAMP设置一个确定的日期和时间值，则会使TIMESTAMP的自动取值功能失效。

TIMESTAMP默认的列宽是14,可指定列宽，以改变显示效果。但不论你指定的列宽如何，MySQL都是以4字节来存储TIMESTAMP值，也总是以14位精度来计算。

如果需要把创建时间和最近一次修改时间同时记录下来，可以用两个时间戳来记录，一个记录创建时间，一个记录修改时间。不过需记住两件事，一是要把记录修改时间的TIMESTAMP数据列放在最前面，这样才会自动取值；二是创建一条新记录时，要用now()函数来初始化创建时间TIMESTAMP数据列，这样，该TIMESTAMP数据列就不会再变化。

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的一个合法取值。

1.3. 唯一编号

在数据库应用，我们经常要用到唯一编号，以标识记录。在MySQL中可通过数据列的AUTO_INCREMENT属性来自动生成。MySQL支持多种数据表，每种数据表的自增属性都有差异，这里将介绍各种数据表里的数据列自增属性。

  • 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是数据列的一种属性，只适用于整数类型数据列。

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

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

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

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

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

    1
    2
    3
    delete from table_name;
    or
    truncate table table_name

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

    1
    delete from table_name where 1;

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

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

    1
    2
    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的值。下面演示该方法的具体操作：

    先创建一个只有一个数据行的数据表：

    1
    2
    create table seq_table (id int unsigned not null);
    insert into seq_table values (0);
接着用以下操作检索出序列号：


1
2
update seq_table set seq = LAST_INSERT_ID( seq + 1 );
select LAST_INSERT_ID();



通过修改seq+1中的常数值，可生成不同步长的序列，如seq+10可生成步长为10的序列。

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

1.4. 字符集支持

MySQL4.1以前版本服务器只能使用单一字符集，从MySQL4.1版本开始，不仅服务器能够使用多种字符集，而且在服务器、数据库、数据表、数据列以及字符串常数多个级别上设置不同的字符集。

1.4.1. MySQL4.1以前版本

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

  • 用mysqldump导出数据，再清除表里的内容，最后用导出文件重新导入。数据表的索引将在导入数时重建。该方法适用于所有数据表类型。

  • 删除索引，然后重建。用alter table命令或drop index和create index命令来完成。该方法也适用于所有数据表类型。但该方法需要我们了解重建索引的精确定义。

  • MyISAM数据表的索引可以用myisamchk程序的–recover和–quick选项加上一个用来设定新字符集的–set-character-set选项进行重建。还可以用mysqlcheck程序的–repair和–quick选项或者一个带QUICK选项的REPLACE TABLE语句来重建索引，这种方式较方便。

1.4.2. MySQL4.1以后版本

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

  • 支持服务器同时使用多种字符集。

  • 允许在服务器，数据库，数据表，数据列等多级别上设置不同的字符集。

    • 服务器的默认字符集在编译时选定，但可在启动服务器时用–default-character-set选项来更改。

    • 用ALTER DATABASE db_name DEFAULT CHARACTER SET charset来设置数据库字符集。 如果只有default参数，则使用服务器的字符集。

    • 用CREATE TABLE table_name(…) CHARACTER SET = charset设置数据表字符集。如果charset为default，则使用数据表所在数据库的字符集作为数据表的字符集。

    • 在数据列中，可用CHARACTER SET charset属性来设置数据列的字符集。charset不能是default，如果没有该属性，则默认使用数据表的字符集。允许设置字符集的数据列有char，varchar(不带binary属性)及TEXT类型。

    • 用_charset str转换字符串常数的字符集。如：_utf8 ‘mysql’，_latinl ‘oracle’。该方法只适用于括在引号内的字符串，其它十六进制常数 、字符串表达式等可用CONVERT()函数进行转换，如：SELECT CONVERT( str USING charset)。

  • 通过MySQL提供的函数可进行字符集转换和查询。

  • 新增的COLLATE操作符使我们可按某一种字符集的排序顺序来处理另一种字符集的数据。如：SELECT a from t ORDER BY a COLLATE utf-8；

  • 用SHOW CHARACTER SET命令可显示服务器支持的字符集列表。

  • 当服务器转换到另一种字符集时，会自动对索引进行重新排序。

  • 通过UTF-8和UCS2字符集提供了Unicode支持。

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

1.4.3. 各级字符集的查询方法

  • 服务器级

    1
    2
    SHOW CHARACTER SET；可查出可供使用的所有字符集。
    SHOW VARIABLES LIKE 'character_set'；可查出服务器的默认字符集。

  • 可查出数据库级的字符集。

    1
    SHOW CREATE DATABASE db_name；

  • 两条命令可查出数据表的字符集。

    1
    2
    SHOW CREATE TABLE table_name；
    SHOW TABLE STATUS LIKE 'table_name'

  • 以下几命令可查出数据列的字符集：

    1
    2
    3
    DESCRIBE table_name;
    SHOW COLUMNS FROM table_name;
    SHOW CREATE TABLE table_name;

  • 用CHARSET()函数可确定特定字符串，字符串表达式或数据列值相关联的字符串的字符集。如：SELECT CHARSET(str)。

1.4.4. Unicode支持

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

1.5. 如何选择数据列类型？

选择正确的数据列类型能大大提高数据库的性能和使数据库具有高扩展性。在选择数据列类型时，请从以下几个方面考虑：

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

  • 数据值的取值范围。

  • 考虑性能和处理效率。

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

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

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

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

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

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

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

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

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

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

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

1.6. 表达式操作符

Table 1.5. 算术操作符

操作符 语法 含义
+ a + b 相加
- a - b 相减
- - a 求负
* a * b 乘法
/ a / b 除法
% a % b 求余

Table 1.6. 逻辑操作符

操作符 语法 含义
AND 或 && a AND b 或 a && b 逻辑与，若两个操作数同时为真，则为真
OR 或 || a OR b 或 a || b 逻辑或，只要有一个操作数为真，则为真
XOR a XOR b 逻辑异或，若有且仅有一个操作数为真，则为真
NOT 或 ！ NOT a 或 !a 逻辑非，若操作数为假，则为真

Table 1.7. 位操作符

操作符 语法 含义
& a & b 按位与，若操作数同位同为1,则该位为1
| a | b 按位或，若操作数同位有一位为1,则该位为1
^ a ^ b 按拉异或，若操作数同一位分别为1和0,则该位为1
<< a << b 把a中的各个位左移b个位置
>> a >> b 把a中的各个位右移b个位置

Table 1.8. 比较操作符

操作符 语法 含义
= a = b 若两个操作数相等，则为真
<=> a <=> b 若两个操作数相等，则为真，可用于NULL值比较
!= 或 <> a != b 或 a <> b 若两个操用数不等，则为真
< a < b 若a小于b，则为真
<= a <= b 若a小于或等于b，则为真
> a > b 若a大于b，则为真
>= a > b 若a大于或等于b，则为真
IN a IN (b1,b2,…) 若a等于b1,b2,…中的某一个，则为真
BETWEEN a BETWEEN b AND c 若a在b和c之间(包括b和c)，则为真
NOT BETWEEN a NOT BETWEEN b AND c 若a不在b和c之间(包括b和c)，则为真
LIKE a LIKE b SQL模式匹配，若a匹配b，则为真
NOT LIKE a NOT LIKE b SQL模式匹配，若a不匹配b，则为真
REGEXP a REGEXP b 正则表达式匹配，若a匹配b，则为真
NOT REGEXP a NOT REGEXP b 正则表达式匹配，若a不匹配b，则为真
IS NULL a IS NULL 若a为NULL，则为真
IS NOT NULL a IS NOT NULL 若a不为NULL，则为真

LIKE模式匹配中的“%”匹配任意个字符，“_”匹配一个字符。匹配不区分大小写字符。

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

操作符
BINARY，COLLATE
NOT、！
^
XOR
-(一元求负操作符)、~(一元取反操作符)
*、/、%
+、-
<<、>>
&
|
<、<=、=、<=>、!=、<>、>=、>、IN、IS、LIKE、REGEXP、RLIKE
BETWEEN、CASE、WHEN、THEN、ELSE
AND、&&
OR、||
:=

1.7. 类型转换

在MySQL的表达式中，如果某个数据值的类型与上下文所要求的类型不相符，MySQL则会根据将要进行的操作自动地对数据值进行类型转换。如：

1
2
1 + '2'      会转换成1 + 2 = 3
1+ 'abc'     会转换成1 + 0 = 1 由于abc不能转换成任何的值，所以默认为0

MySQL会根据表达式上下文的要求，把字符串和数值自动转换为日期和时间值

对于超范围或非法的值，MySQL也会进行转换，但转换出来的结果是错误的。出现该情况时，MySQL会提示警告信息，我们可捕获该信息以进行相应的处理。

Chapter 2. 查询优化

数据库是数据的集合，与数学的集合论有密不可分的关系。

为提高查询速度，我们可以：

  • 对数据表添加索引，以加快搜索速度；

  • 通过编程技巧最大限度地利用索引；

  • 优化查询语句，以使服务器最快响应多客户的请求。

  • 研究硬件处理过程，减少物理约束。

2.1. 索引

索引技术是关系数据查询中最重要的技术。如果要加提升数据库的性能，索引优化是首先应该考虑的。因为它能使我们的数据库得到最大性能方面的提升。

索引的优点：

  • 没有索引的表是没有排序的数据集合，如果要查询数据需进行全表扫描。有索引的表是一个在索引列上排序了数据表，可通过索引快速定位记录。在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可查看该日志。针对性能差的查询可利用索引来加快查询速度。

2.2. 查询优化程序

当我们发一条查询命令时，MySQL分对它进行分析，以优化查询。把explain语名放到查询前面可显示查询的执行路线，对优化查询提供有用的信息。以下几个原则可帮助系统挑选和使用索引：

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

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

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

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

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

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

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

2.3. 数据列类型与查询效率

选用适当的数据列类型有助于提高查询命令的执行速度，下面是几点关于如何选择合适数据列类型的建议：

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

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

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

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

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

    1
    2
    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导出数据表，再删除数据表后重建，如：

    1
    2
    $ mysqldump --opt db_name table_name &gt; dump.sql
    $ mysql db_name &lt; dump.sql

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

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

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

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

2.4. 有效地加载数据

有时我们需大量地把数据加载到数据表，采用批量加载的方式比一个一个记录加载效率高，因为MySQL不用每加载一条记录就刷新一次索引。下面介绍几个有助于加快数据加载的操作：

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

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

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

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

    1
    2
    3
    4
    5
    6
    BEGIN;
    INSERT INTO table_name values (...);
    INSERT INTO table_name values (...);
    INSERT INTO table_name values (...);
    ...
    COMMIT;

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

    1
    2
    3
    4
    5
    6
    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命令，如：

    1
    2
    ALTER TABLE table_name DISABLE KEYS;
    ALTER TABLE table_name ENABLE KEYS;

  • 使用myisamchk或isamchk工具。如：

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


1
$ myisamchk --description table_name

2.5. 调度和锁定

在很多客户一起查询数据表时，如果使客户能最快地查询到数据就是调度和锁定做的工作了。在MySQL中，我们把select操作叫做读，把对数据表修改增加的操作(INSERT,UPDATE,REPLACE…)叫做写。MySQL的基本调度策略可以归纳为以下两条：

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

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

MyISAM和ISAM数据表的调度策略是在数据表锁的帮助下实现的，在客户程序要访问数据表之前，需获得相应的锁，在完成对数据表的操作后，再释放该锁。锁的管理通常由服务器管理，也可人为地用LOCK TABLES和UNLOCK TABLES命令来申请和释放锁。写操作时，需要申请一个独占性的锁，也就是说在写操作其间，该表只能由写操作的客户使用。读操作时，客户必须申请一个允许其他客户对数据表进行写操作的锁，以确保客户在读的过程中数据表不会发生改变。但读操作锁不是独占的，可有多个读操作同时作用于同一个数据表。

通过一些修饰符可影响调度策略，如LOW_PRIORITY(用于DELETE,INSERT,LOAD DATA,REPLACE,UPDATE语句)、HIGH_PRIORITY(用于SELECT语句)、DELAYED(用于INSERT和REPLACE语句)。它们的作用是这样的：

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

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

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

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

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

BDB数据表使用页面级操作锁，InnoDB数据表使用数据行级操作锁。所以这两种表的并发性比MyISAM和ISAM数据表这种表级锁的并发性会好很多。其中InnoDB的并发性最好。综上所述，我们可得出以下结论：

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

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

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

2.6. 服务器优化

优化原则：

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

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

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

针对以上几个原则，我们应该调整服务器：

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

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

      1
      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,则禁用查询缓冲功能。设置方法是在选项文件中设置:

      1
      2
      [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机制必须依赖动态库才能实现。

2.7. 硬件优化

为了提高数据运行速度，升级硬件是最直接的解决方案。针对数据库应用的特点，在升级硬件时应考虑以下内容：

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

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

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

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

Chapter 3. 数据库管理

数据库是一个复杂而又关键的系统，为确保系统安全、高效运行，需熟悉数据库内部的运作机制，掌握各种维护工具，并做好日常的管理工作。下面列举几项主要工作职责：

  • 服务器的关闭和启动；

  • 管理用户帐号；

  • 管理日志文件；

  • 数据库备份恢复；

  • 数据库优化；

  • 确保数据库数据安全；

  • 数据库软件升级。

3.1. 数据目录

数据目录是用来存放数据表和相关信息的地方，是数据库的核心。在MySQL中的数据目录根据不同平台的有一些差异：

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

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

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

在服务器启动时，可用–datadir=dir_name来指定数据目录，也可把它写到配置文件中。

我们还可用命令向服务器查询数据目录的位置，数据目录的变量名是datadir，如：

  • 1
    % mysqladmin variables

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

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

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

    1
    % mysqladmin --host=localhost --socket=/path/to/socket variables
  • 1
    mysql> SHOW VARIABLES LIKE 'datadir';

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

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

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

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

    1
    % mysqld --help

数据目录是存放数据文件的地方，每个数据库对应目录的不同文件。InnoDB数据表由于用表空间来管理数据库，所以就没这种对应关系。但也是保存在数据目录中的，在数据目录除保存数据库文件外，还可能会保存以下几类文件：

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

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

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

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

数据目录中的所有数据库全部由服务器(mysqld)来管理，客户端不直接操作数据。服务器是客户使用数据的唯一通道。

在MySQL中，每个数据库其实就是在数据目录下一个子目录，show databases命令相当于列出数据目录中的目录清单。create database db_name命令会在数据目录下新建一个db_name的目录，以存放数据库的数据文件。所以我们也可下面的shell命令方式来建立一个空数据库：

1
2
3
% cd datadir
% mkdir db_name
% chmod u=rwx,go-rwx db_name

同理，删除数据库drop database db_name也就是删除数据目录中一个名为db_name的目录及目录中的数据表文件。我们也可用shell这进行操作：

1
2
% cd datadir
% rm -rf db_name

比较shell方式与drop database方式，drop database db_name命令不能删除db_name目录中创建的其它非数据表文件；由于InnoDB是表空间来管理数据表，所以不能用rm或del命令删除InnoDB的数据表。

3.2. MySQL数据表在系统中表现形式

MySQL数据表类型有：ISAM、MyISAM、MERGE、BDB、InnoDB和HEAP。每种数据表在文件系统中都有不同的表示方式，有一个共同点就是每种数据表至少有一个存放数据表结构定义的.frm文件。下面介绍每种数据表文件：

  • 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数据表在文件系统中表现形式后，我们可知道，创建、修改或删除数据表，其实就是对这些文件进行操作。例如一些数据表(除InnoDB和HEAP数据表外)，我们可直接在文件系统中删除相应的文件来删除数据表。

1
2
% cd datadir
% rm -f mydb/mydb.*

以上命令可删除mydb数据库中的mydb数据表。

3.3. 数据表最大尺寸限制

在MySQL中影响数据表尺寸的因素有很多，下面分别进行介绍：

  • 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数据表用表空间来管理，一个表空间可同时空纳多个数据表，所以数据表的最大尺寸受系统文件和同一表空间中数据表空间的约束。

3.4. 状态文件和日志文件

在MySQL数据目录中还包含着许多状态文件和日志文件，这些文件的文件名都是以主机名加上相关后缀来命名的。下面是这些文件的一个说明列表：

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

文件类型 默认名 文件内容
进程ID文件 hostname.pid MySQL服务器进程的ID
常规查询日志 hostname.log 连接/断开连接事件和查询信息
慢查询日志 hostname-slow.log 记录查询时间很长的命令信息
变更日志 hostname.nnn 创建或修改数据表结构和内容的查询命令信息
二进制变更日志 hostname-bin.nnn 创建或修改数据表结构和内容的查询命令的二进制表示法
二进制变更日志的索引文件 hostname-bin.index 使用中的“二进制变更日志”列表
错误日志 hostname.err 记录“启动/关闭”事件和异常情况

变更日志和二进制变更日志主要用于MySQL数据库服务器的崩溃恢复中，由于变更日志记录了数据库的所有变更操作，所以可以进行事件重放。具体操作请参考相关数据库备份恢复章节。对于变更日志，我们可用–log-long-format选项来让它以扩展方式记录有关事件。扩展方式可记录谁发出查询和什么时候发出查询的信息。可使我们更好地掌握客户端的操作情况。日志记录着查询命令的所有操作，里面可能会有一些敏感信息。所以我们要确保日志文件的安全。

3.5. 调整MySQL数据目录位置

MySQL数据库的数据目录位置，包括目录里的各种文件的位置)可根据实际情况进行调整。调整的方法有两种，一种是使用符号链接；一种用服务器启动选项。下面一个列表说明了数据目录及目录中文件各自适宜采用的方法：

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

调整对象 适用方法
整个数据目录 启动选项和符号链接
数据库目录 符号链接
数据表 符号链接
InnoDB数据表空间 启动选项
PID文件 启动选项
日志文件 启动选项

下面是各种调整方法的具体操作过程：

  • 在调整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数据库日常管理

为了确保数据库平稳可靠运行，我们需进行维护和管理，这是每一位数据库管理员的职责。下面分几个专题分别介绍。

4.1. 数据库安全管理

MySQL数据库通过用户和密码来控制用户对数据库的访问，当我们新安装了一个数据库服务器时，MySQL的权限表设置是很不安全，它默认允许任何人不需要密码就可访问数据库。所以我们安装好服务器后第一件需要做的就是设置用户密码。

在MySQL中的mysql数据库的user数据表中存有用户的帐号信息，在初始状态下已存在root和一些匿名用户，且所有用户都没有设置密码。该数据表的这些用户信息是通过一个mysql_install_db脚本安装的。该表的主要列有：

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

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

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

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

在这种状态下的数据库是极不安全的，我们可用以下命令轻易地访问数据库：

1
2
% mysql -h localhost -u root     #通过本地主机，root用户访问，不需要密码验证
% mysql -h localhost             #通过本地主机，匿名用户访问，不需要密码验证

设置MySQL用户帐号密码的方法有三种：

  • 使用mysqladmin程序：

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

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

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

    1
    2
    mysql> set password for 'root'@'localhost' = password('password');
    mysql> set password for 'root'@'remote' = password('password');

  • 直接修改user权限表：

    1
    2
    3
    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命令刷新内存中的权限表，以使它马上生效。

为root用户设置密码后，如果需以root身份连接数据库，就需验证密码。我们可用以下语句连接数据库：

1
2
% mysql -u root -p
Enter password:                         #输入root的密码

在user表中，user列为空的为匿名用户。它也是没有密码的，我们需为它们设置一个密码，或干脆把它们删除。在windows系统上的本地匿名用户帐号和root用户有着同样的权限，这是一个很大的安全漏洞。应该把它删除或把权限削弱。

4.2. 服务器的启动和关闭

在Linux和windows平台下MySQL服务器的启动方式有很大不同，这里将分开介绍：

  • Linux平台：

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

    1
    % 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命令。

4.3. 连接故障恢复

当由于误删mysql套接字时(/tmp/mysql.sock)，我们就不能通过套接字连接服务器。这时我们可通过tcp/ip来连接服务器，要建立一个tcp/ip连接，需用127.0.0.1代替locahost作为-h参数的值来连接服务器。如：

1
% mysqladmin -h 127.0.0.1 -u root -p shutdown       #关闭服务器再重启会重建套接字

当我们因为忘记root用户密码而不能连接服务器时，重设置密码的步骤如：

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

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

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

4.4. MySQL用户帐号管理

MySQL用户帐号管理主要用grant(授权)和revoke(撤权)两个SQL指令来管理。这两个指令实质是通过操作user(连接权限和全局权限)、db(数据库级权限)、tables_priv(数据表级权限)、columns_priv(数据列级权限)四个权限表来分配权限的。host权限表不受这两个指令影响。下面将会详细介绍用户权限管理的内容。

  • GRANT语法说明：

    1
    2
    3
    4
    5
    6
    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;   #设置帐号的管理和资源(连接服务器次数或查询次数等)选项

    示例：

    1
    mysql>grant all on db.* to 'test'@'localhost' identified by 'test';
上例运行后的效果是，test用户只能通过‘test’密码从本机访问db数据库


1
mysql>grant all on db.* to 'test'@'%' identified by 'test';


上例运行后的效果是，test用户可通过‘test’密码从任意计算机上访问db数据库。‘%’代表任意字符，‘_’代表一个任意字符。主机名部份还可以是IP地址。

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

  • Table 4.1. 访问权限表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    | 权限 | 权限说明 |
    | --- | --- |
    | 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子句设置)

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

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

    1
    2
    3
    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权限的用户可把自已所拥用的权限转授给其他用户，如：

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

  • 限制资源使用，如：

    1
    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语法说明：

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

    示例：

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

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

    1
    2
    3
    4
    % mysql -u root -p
    mysql>use mysql
    mysql>DELETE FROM user where User='test' and Host='localhost';
    mysql fulsh privileges;

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

    1
    2
    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帐号的资源限制

4.5. 日志文件管理

有关MySQL的日志文件前面章节已简要讨论过了，主要有四种日志文件，分别是常规查询日志、慢查询日志、变更查询日志和二进制变更日志。这些日志文件的创建需在启动服务器时用选项指定。

Table 4.3. 日志启动选项

启动选项 激活日志
–log[=file_name] 常规日志文件
–log-bin[=file_name] 二进制变更日志文件
–log-bin-index=file_name 二进制变更日志文件索引文件
–log-update[=file_name] 变更日志文件
–log-slow-queries[=file_name] 慢查询日志文件
–log-isam[=file_name] ISAM/MyISAM日志文件
–log-long-format 设置慢查询日志和变更日志的格式

BDB和InnoDB数据表的日志文件会自动创建不用指定选项。但可用以下选项指时日志文件的存放路径。

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

启动选项 用途
–bdb-logdir=dir_name 存放BDB日志文件的位置
–innodb-log_arch_dir=dir_name 存放InnoDB日志文件的归档目录
–innodb_log_group_home_dir=dir_name 存放InnoDB日志文件的位置

MySQL日志文件选项可在mysqld和mysqld_safe脚本中使用，也可在选项文件my.cnf的[mysqld]中使用。推荐在选项文件中使用，因为每次启动服务器的日志选项基本上都是一致的。

日志的刷新可用mysqladmin flush-logs命令或flush logs语句实现。另外，对MySQL服务器发送一条SIGHUP信号也会刷新日志。错误日志和DBD/InnoDB日志不能用以上方法刷新。

错误日志记录MySQL数据库系统的论断和出错信息，由mysqld_safe脚本创建，文件名默认为hostname.err，也可通过–err-log或选项文件的err-log语句指定另外的名字。如果直接用mysqld程序启动服务器，错误信息会直接输出到输出设备，也就是屏幕。但我们可用重定向方法把错误信息输出到其它地方，如把错误信息输出到/var/log/mysql.err文件中，可以执行以下语句：

1
% mysqld > /var/log/mysql.err 2>&1 &

在windows平台下，MySQL服务器默认把诊断信息写到数据目录的mysql.err文件中，并且不允许另外指定错误日志文件名。如在启动MySQL服务器时给出了–console选项，则MySQL会把诊断信息输出到控制台窗口而不创建错误日志。但如MySQL是作为一个服务运行，则–console选项不起作用。

4.5.1. 日志失效处理

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

  • 日志轮转。该方法适用于常规查询日志和慢查询日志这些文件名固定的日志文件，在日志轮转时，应进行日志刷新操作(mysqladmin flush-logs命令或flush logs语句)，以确保缓存在内存中的日志信息写入磁盘；

    日志轮转的操作过程是这样的：第一次轮转时，把log更名为log.1，然后服务器再创建一个新的log文件，在第二轮转时，再把log.1更名为log.2，把log更名为log.1，然后服务器再创建一个新的log文件。如此循环，创建一系列的日志文件。当到达日志轮转失效位置时，下次轮转就不再对它进行更名，直接把最后一个日志文件覆盖掉。例如：如果每天进行一次日志轮转并想保留最后7天的日志文件，就需要保留log.1–log.7共七个日志文件，等下次轮转时，用log.6覆盖原来的log.7成新的log.7，原来的log.7就自然失效。下面是一个失效处理的shell脚本，以供参考：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    #!/bin/sh
    # shell script ---  rotate_log.sh

    if [ $# -ne 1 ]; then
       echo "Usage: $0 logname" 1&gt;&2
       exit 1
    if

    logfile=$1

    mv $logfile.6 $logfile.7
    mv $logfile.5 $logfile.6
    mv $logfile.4 $logfile.5
    mv $logfile.3 $logfile.4
    mv $logfile.2 $logfile.3
    mv $logfile.1 $logfile.2
    mv $logfile $logfile.1
    mysqladmin -u flush -pflushpass flush-logs     #执行mysqladmin flush-logs会打开一个日志文件----重新生成一个新的日志文件

    该脚本以日志文件名为参数，执行方法如下：

    1
    % rotate_log.sh /usr/local/mysql/data/log

    注意，脚本中的mysqladmin命令是带有-u和-p参数的，因为我们进行日志刷新时需连接服务器。为确保安全，我们建立一个flush用户，密码为flushpass。该用户只有日志刷新的权限(reload权限)。创建该用户的语句如下：

    1
    GRANT RELOAD ON *.* TO 'flush'@'localhost' IDENTIFIED BY 'fulshpass';

    设置好后，我们就可利用系统的自动处理机制定期运行该脚本以生成轮转日志。在Linux系统上的MySQL发行版中带有一个用来安装mysql-log-rotate日志轮转脚本的logrotate工具，如用RPM安装,则在/usr/share/mysql目录，如用二进制方式安装,则在MySQL安装目录的support-files目录，如用源码安装，则在安装目录的share/mysql目录中。

    在windows平台下，不能在线更名，需停掉服务器，再进行。下面是一个进行日志更名的批处理文件：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    @echo off
    REM script name : rotate_log.bat

    if not "%1" == "" goto ROTATE

       @echo Usage: rotate_log logname
       goto DONE

    :ROTATE
    set logfile=%1
    erase %logfile%.7
    rename %logfile%.6 %logfile%.7
    rename %logfile%.5 %logfile%.6
    rename %logfile%.4 %logfile%.5
    rename %logfile%.3 %logfile%.4
    rename %logfile%.2 %logfile%.3
    rename %logfile%.1 %logfile%.2
    rename %logfile% %logfile%.1
    :DONE

    该脚本的执行方法如下：

    1
    c:\rotate_log c:\mysql\data\log

  • 以时间为依据对日志进行失效处理。该方法将定期删除超过给定时间的日志文件，适用于变更日志和二进制日志等文件名用数字编号标识的日志文件。下面是一个用Perl写成的处理脚本：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    #!/usr/bin/perl -w

    # script name: expire_log.pl
    # Usage: expire_log.pl logfile ...

    use strict
    die "Usage: $0 logfile ...\n" if @ARGV == 0;
    my $max_allowed_age = 7;      #max allowed age in days
    foreach my $file (@ARGV)      #chack each argument
    {
       unlink ($file) if -e $file && -M $file &gt;= $max_allowed_age;
    }
    exit(0);

    该脚本需提供一个将被轮转的日志文件名作为参数，如：

    1
    % expire_log.pl /usr/local/mysql/data/log.[0-9]*

    在给脚本参数时请小心，如给出*为参数，则会删除目录中所有更新时间大于7天的文件。

  • 镜像机制。把日志文件镜像到所有的从服务器上。要使用镜像机制，你必须知道主服务器有多少个从服务器，哪些正在运行，并需依次连接每一个从服务器并发出show slave status语句以确定它正处理主服务器的哪个二进制日志文件(语句输出列表的Master_Log_File项)，只有所有的从服务器都不会用到的日志文件才能删除。删除方法是在主服务器上发出以下语句：

    1
    mysql> PURGE MASTER LOGS TO 'last_log.xx';

    上面语句中的last_log.xx是所有从服务器已处理的最小编号日志文件。

4.6. MySQL服务器的一些优化配置

  • 服务器的监听端口设置

    • 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选项来设置，但最好还是在选项文件里设置，如:

      1
      2
      [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选项进行添加。如：

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

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

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

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

      1
      2
      3
      4
      5
      % 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程序备份数据库：

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

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

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

      1
      % mysql -p -u root &lt; dumpfile.sql

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

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

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

    1
    2
    3
    4
    5
    6
    7
    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]中配置：

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

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

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

      1
      2
      innodb_data_home_dir=
      innodb_data_file_path=/dev/hda1:10Graw

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

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

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

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

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

      1
      2
      3
      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数据表。

4.7. 优化服务器

MySQL服务器为我们提供了丰富的参数，以调整服务器满足不同环境的要求。下面分别讨论一下这些参数：

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

    1
    --table_cache=128

    也可在选项文件中设置：

    1
    2
    [mysqld]
    table_cache=128

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

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

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

    1
    2
    3
    4
    5
    6
    --set-variable=table_cache=128
    or
    -O table_cache=128
    在选项文件中可写成：
    [mysqld]
    set-variable=table_cache=128

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    全局级：
    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权限才能设置全局参数，新设置值的效力将持续到该参数被再次修改或服务器退出。设置会话级参数不用特殊的权限，新设置值的效力将持续到该值被再次修改或连接断开。显示参数的语句如下：

    1
    2
    3
    4
    5
    6
    7
    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，该值也要加大。客户端的启动命令为：

      1
      % 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。

4.8. 运行多个MySQL服务器

需运行多个服务器的原因有很多，比如上面提到的可绕过最大文件描述符的限制，还有是进行版本测试和提供专用服务等。运行多个服务器比运行单个服务器复杂很多，需注意以下问题：

  • 在安装不同版本的程序时，需分开目录存放程序和数据目录。如果同一版本的服务器软件，则程序目录可一样，但数据目录则要不同。可用–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选项配置文件看起来就象下面这样：

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      [mysqld001]
      basedir=/usr/local/mysql/001
      datadir=/usr/local/mysql/001/data
      mysqld=/usr/local/mysql/001/bin/mysqld_safe
      socket=/usr/local/mysql/001/mysql.sock
      port=3306
      local-infile=1
      user=mysqladm
      log=log
      log-update=update-log
      innodb_data_file_path=ibdata1:10M

      [mysqld002]
      basedir=/usr/local/mysql/002
      datadir=/usr/local/mysql/002/data
      mysqld=/usr/local/mysql/002/bin/mysqld_safe
      socket=/usr/local/mysql/002/mysql.sock
      port=3307
      local-infile=1
      user=mysqladm
      log=log
      log-update=update-log
      innodb_data_file_path=ibdata1:10M

      ...

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

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

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

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

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

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

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

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

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      c:\mysql\my.cnf1

      [mysqld]
      basedir=c:/mysql
      datedir=c:/mysql/data1
      port=3306

      c:\mysql\my.cnf2

      [mysqld]
      basedir=c:/mysql
      datadir=c:/mysql/data2
      port=3307

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

      1
      2
      c:\&gt; mysqld --defaults-file=c:\mysql\my.cnf1
      c:\&gt; mysqld --defaults-file=c:\mysql\my.cnf2

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

      1
      c:\&gt; 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命令停掉服务器，再执行以下命令：


1
2
c:\&gt; mysql-nt --remove                 #移除默认的服务
c:\&gt; mysql-nt --remove service_name    #移除指定服务

4.9. MySQL服务器镜像配置

通过镜像机制可把数据从一个地方复制到另一个地方，并能实现同步两个或多个地方的数据。MySQL服务器也支持镜像，大提高数据的安全性和稳定性。下面介绍一下MySQL数据中的镜像机制：

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

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

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

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

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

镜像机制在MySQL中还是一个新生事物，最早实现于3.23.15版。各版本间的镜像能力有差异，一般来说，建议大家尽量使用最新的版本，下面列举了不同版本的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。

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

    1
    2
    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告诉它并激活其二进制日志功能。在主服务器要读取的选项文件内增加以下内容：

    1
    2
    3
    [mysqld]
    server-id=master_server_id
    log-bin=binlog_name

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

  • 关闭从服务器。

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

    1
    2
    3
    4
    5
    6
    7
    8
    [mysqld]
    server-id=slave_server_id
    master-host=master_host
    master-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文件并重启从服务器。

以上步骤是镜像所有数据库的操作过程，如果我们想把mysql权限数据保留在主服务器上，排除在镜像机制外的话，可用在选项文件的[mysqld]中加入–binlog-ignore-db=mysql选项，这样，mysql数据库上的操作就不会记录在二进制变更日志里。如要排除其它数据库，只要增加多几个该选项即可。

通过以下几个命令可监控和管理主从服务器：

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

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

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

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

在MySQL4.0.2之后版本中，镜像机制中的从服务器由两个内部线程组成：

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

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

中继日志文件默认的文件为hostname-relay-bin.nnn和hostname-relay-bin.index。可用从服务器的–relay-log和–relay-log-index选项修改。在从服务器中还有一个relay-log.info中继信息文件，可用–relay-log-info-file启动选项修改文件名。

Chapter 5. 数据库安全

安全是一个过程，而不是一个方法，它贯穿在我们使用和维护MySQL数据库的过程中。这不单是系统管理员工作，用户也要有安全的意识，使安全问题得到有效控制。MySQL服务器的安全问题可分为内部安全和外部安全两部份。内部安全问题大都与系统文件有关，我们需确保MySQL程序文件和数据文件的安全。外部安全是指通过网络连接到服务器的安全问题，应该只允许合法用户访问数据库，在一些情况下还可用SSL加密信息传输通道。下分别介绍内部安全和外部安全的防范措施。

5.1. 保护MySQL安装程序文件

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

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

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

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

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

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    #设置mysql目录
    % chmod 755 /usr/local/mysql
    or
    % chmod u=rwx,go=rx /usr/local/mysql
    #设置mysql/bin目录
    % chmod 755 /usr/local/mysql/bin
    or
    % chmod u=rwx,go=rx /usr/local/mysql/bin
    #设置mysql/libexec目录
    % chmod 700 /usr/local/mysql/libexec
    or
    % chmod u=rwx,go-rwx /usr/local/mysql/libexec

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

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

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

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

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

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

5.2. 权限表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

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

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

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

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

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

大家注意到，以上权限没有限制到数据行级的设置。在MySQL只要实现数据行级控制就要通过编写程序(使用GET-LOCK()函数)来实现。

MySQL的版本很多，所以权限表的结构在不同版本间会有不同。如果出现这种情况，可用mysql_fix_privilege_tables脚本来修正。运行方式如下：

1
% mysql_fix_privilege_tables rootpassword            #这里要给出MySQL的root用户密码

最好一下子升级到MySQL 4.0.4版本，因为4.0.2和4.0.3的db表没有Create_tmp_table_priv和Lock_tables_priv权限。

MySQL的权限表定义了两部份内容，一个部份定义权限的范围，即谁(帐户)可以从哪里(客户端主机)访问什么(数据库、数据表、数据列)；另一部份定义权限，即控制用户可以进行的操作。下面是一些常用的权限介绍，可直接在GRANT语句中使用。

  • 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解密的工作。

user权限表中有一个ssl_type数据列，用来说明连接是否使用加密连接以及使用哪种类型的连接，它是一个ENUM类型的数据列，可能的取值有：

  • 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类型的。

user权限表里还有几列是设置帐户资源使用情况的，如果以下数据列中的数全为零，则表示没有限制：

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

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

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

设置权限表应注意的事项：

  • 删除所有匿名用户。

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

    1
    mysql> SELECT host,user FROM user WHERE password = '';

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

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

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

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

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

    1
    2
    3
    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权限的用户读取数据目录中文件权限设置不严密的数据库数据的过程：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    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语句对权限表的修改过程：

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

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

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

删除用户权限其实就是把这些权限表中相应的帐号记录全部删除即可。

5.3. 建立加密连接

加密连接可提高数据的安全性，但会降低性能。要进行加密连接，必须满足以下要求：

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

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

    1
    mysql> show variables like 'have_openssl';

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

    1
    2
    3
    4
    [mysqld]
    ssl-ca=/usr/local/mysql/data/ca-cert.pem
    ssl-cert=/usr/local/mysql/data/server-cert.pem
    ssl-key=/usr/local/mysql/data/server-key.pem

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

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

    1
    2
    3
    4
    [mysql]
    ssl-ca=/home/mysql/ca-cert.pem
    ssl-cert=/home/mysql/client-cert.pem
    ssl-key=/home/mysql/client-key.pem

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

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

可用GRANT语句的REQUIRE SSL选项来强制用户使用加密连接。

使用sampdb发行版的证书可以建立一个加密连接，但由于该文件已公开，所以安全性不好，我们可以在测试成功后自行建立证书或购买商业证书，以提高安全性。如何自行建立SSL证书的文档在sampdb发行版的ssl/README文件里有说明。

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

数据库在运行中，会因为人为因素或一些不可抗力因素造成数据损坏。所以为了保护数据的安全和最小停机时间，我们需制定详细的备份/恢复计划，并定期对计划的有效性进行测试。本章结合MySQL服务器的运行机制和所提供的工具，介绍如何进行数据库的备份、维护和修复。

以下是几点防范的措施：

  • 制定一份数据库备份/恢复计划，并对计划进行仔细测试。

  • 启动数据库服务器的二进制变更日志，该功能的系统开销很小(约为1%)，我们没有理由不这样做。

  • 定期检查数据表，防范于未燃。

  • 定期对备份文件进行备份，以防备份文件失效。

  • 把MySQL的数据目录和备份文件分别放到两个不同的驱动器中，以平衡磁盘I/O和增加数据的安全。

6.1. 检查/修复数据表

对数据表进行维护最好通过发出CHECK TABLE(检查数据表)或REPAIRE TABLE(修复数据表)命令来做，这样MySQL服务器自动进行表锁定以协调数据表中数据的读写一致性问题。也可用myisamchk工具来做数据表的维护，但它直接访问有关的数据表文件，不通过服务器，所以需人为地协调数据表数据的读写一致性问题。使用myisamchk检查数据表的具体操作步骤如下：

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

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

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

    1
    % myisamchk table_name

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

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

    1
    mysql> UNLOCK TABLES;

使用myisamchk修复数据表的具体操作步骤如下：

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

    1
    2
    3
    4
    % mysql
    mysql> use db
    mysql> LOCK TABLE table_name WRITE;     #以读/写方式锁定数据表
    mysql> FLUSH TABLE table_name;

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

    1
    % myisamchk --recover table_name

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

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

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

还可用以下命令锁定所有表，锁定后，所有用户就只能读不能写数据，这样就可使我们能安全地拷贝数据文件。

1
mysql> FLUSH TABLES WITH READ LOCK;

下面是解除锁语句：

1
mysql> UNLOCK TABLES;

6.2. 备份数据库

定期的备份可使我们数据库崩溃造成的损失大大降低。在MySQL中进行数据备份的方法有两种，一种是使用mysqldump程序，一种是使用mysqlhotcopy、cp、tar或cpio等打包程序直接拷贝数据库文件。mysqldump程序备份数据库较慢，但它生成的文本文件便于移植。使用mysqlhotcopy等程序备份速度快，因为它直接对系统文件进行操作，需人为协调数据库数据的备份前后一致性。

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

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

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

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

    1
    2
    3
    4
    5
    % 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

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

    1
    2
    3
    % 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快。

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

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

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

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

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

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

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

    1
    2
    3
    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重新启动镜像，从服务器重新与主服务器同步，接收备份时主服务器所做的修改。

在MySQL中没有为数据库重命名的命令，但我们可用mysqldump转储数据库，再创建一个新的空数据库，把转储文件加载到该新数据库，这样就完成数据库重命名的工作。如：

1
2
3
4
% mysqldump old_db >db.sql          #转储db数据库数据
% mysqladmin create new_db          #新建一个空的数据库
% mysql new_db < db.sql             #把db数据库的数据加载到新的数据库中
% mysqladmin drop old_db            #删除旧的数据库

一个更简单的重命名数据库的方法是直接修改数据库目录名，但该方法不适用于InnoDB和BDB数据库。注意，在更名后，需在权限表中更新相关数据表信息，需执行以下语句：

1
2
3
4
mysql> UPDATE db SET db='new_db' WHERE db='old_db';
mysql> UPDATE tables_priv SET db='new_db' WHERE db='old_db';
mysql> UPDATE columns_priv SET db='new_db' WHERE db='old_db';
mysql> UPDATE host SET db='new_db' WHERE db='old_db';

6.3. 使用备份恢复数据

恢复过程包括两个信息源—备份文件和二进制日志，备份文件可使用数据恢复到执行备份时的状态，而二进制日志可恢复到发生故障时的状态。下面分别介绍如何利用这两个文件恢复一个数据库或恢复单个数据表。

恢复整个数据库的步骤：

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

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

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

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

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    #!/usr/bin/perl -w
    # sort_num.pl

    use strict;

    my @files = &lt;&gt;;             #read all input file
    @files = sort { my $anum = $1 if $a =~/\.(\d+)$/;         #sort them by numeric extension
                    my $bnum = $1 if $b =~/\.(\d+)$/;
                    $anum &lt;=&gt; $bnum;
                  } @files;
    print @files;              #print them
    exit(0);

    如下使用该脚本：
    % ls -l binlog.[0-9]* | sort_num.pl | xargs mysqlbinlog | mysql --one-database db_name

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

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

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

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

恢复使用BACKUP TABLE命令备份的数据表可用RESTORE TABLE语句：

1
2
3
4
备份语句：
mysql> BACKUP TABLE table_name1,table_name2 TO '/backup/table_backup';
恢复语句：
mysql> RESTORE TABLE table_name1,table_name2 FROM '/backup/table_backup';

恢复有外键的数据表，可用SET FOREIGN_KEY_CHECK = 0;语句先关闭键字检查，导入表后再重启它，赋值为1表示检查有效。

恢复InnoDB表空间，当服务器重启时，InnoDB处理程序会执行自动恢复工作，通过选项文件[mysqld]段中的set-variable=innodb_force_recovery=level设置自动恢复的级别，推荐典型的启动值为4。如果需从备份文件恢复，则和上面介绍的方法一样。BDB数据表的恢复也一样，启动服务器时它会进行自动恢复。

Chapter 7. MySQL程序介绍

安装完MySQL后，在MySQL的安装目录下会生成很多有用的程序，下面对这些程序进行一一介绍。

  • libmysqld—-嵌入式MySQL服务器，它不能独立运行，它是一个函数库，可嵌入到其它程序中，使程序具有MySQL服务器的功能。

  • myisamchk和isamchk—-检查和修复数据表、分析键值的分布情况、禁止或启用数据表索引的工具。

  • myisampack和pack_isam—-压缩数据表并生成只读数据表。

  • mysql—-最常用的一个与服务器交互的命令行客户端程序。

  • mysqlaccess—-一个用来测试数据库访问权限的脚本。

  • mysqladmin—-一个用来执行各种系统维护和管理工作的工具。

  • mysqlbinlog—-一个以ASCII格式显示二进制变更日志内容的工具。

  • mysqlbug—-一个用来生成程序漏洞报告的脚本。

  • mysql_config—-当准备编译基于MySQL的程序时，可以利用这个工具程序来确定该程序的编译选项和标志。

  • mysqld—-MySQL服务器程序，MySQL数据的核心。

  • mysqld_multi—-一个用来同时启动和关闭多个MySQL服务器的脚本。

  • mysql_safe—-一个用来启动和监控MySQL服务器的脚本。

  • mysqldump—-一个用来导出数据表内容的工具。

  • mysqlhotcopy—-数据库备份工具。

  • mysqlimport—-一个对数据表批量加载数据的工具。

  • mysql_install_db—-一个初始化系统权限表和数据目录的脚本。

  • mysql.server—-一个用来启动和关闭MySQL服务器的脚本。

  • mysqlshow—-一个用来显示数据库中数据表的工具。

Chapter 8. MySQL How-To

8.1. 连接数据库服务器

1
$ ./mysql -h host_name -u user_name -p

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

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

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

连接成功后，mysql数据库服务器会显示一些欢迎信息。接着就可用mysql>use database_name命令打开指定的数据库。

$ ./mysql -h host_name -u user_name -p database_name命令可直接打开指定数据库。

8.2. 更新用户密码

1
mysql>update user set password=passowrd('your passowrd') where host='%';

刷新权限设置：mysql>flush privileges;

8.3. MySQL读取配置文件的顺序

my.cnf是MySQL数据库的配置文件，它存在多个地方，在/etc目录，数据目录和用户主目录都有。放在不同位置，里面的选项有不同的作用范围，下面是MySQL读取配置文件的顺序和作用。

1
2
3
4
5
mysql 读取配置文件的顺序:
/etc/my.cnf              Global options.
DATADIR/my.cnf           Server-specific options.
defaults-extra-file      The file specified with the --defaults-extra-file option.
~/.my.cnf                User-specific options.

8.4. 重设置MySQL管理员密码的方法

有时我们会因为设置原因或时间长了忘记了数据库管理员的密码，使得我们被关在MySQL服务器外。MySQL服务器提供了一种方法可使我们在服务器上重设密码。在windows和linux/unix平台上操作稍有不同，下面分别介绍：

  • Linux/Unix平台下：

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

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

      1
      % mysqld_safe --skip-grant-tables &

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

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

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

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

  • windows平台下：

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

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

      1
      c:\mysql\bin&gt;mysqld-nt --skip-grant-tables

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

      1
      2
      3
      c:\mysql\bin&gt;mysql
      mysql> use mysql
      mysql> set password for 'root'@'localhost' = password('password');

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

8.5. NULL值

NULL是空值，代表什么也没有。它不能与值进行比较操作和算术操作，也不能和NULL进行比较，因为两个空值比较是没有意义的。我们可用“is NULL” 或 “is not NULL”来判断是不空值。如：

1
2
mysql> select * from test where mytest is NULL;
mysql> select * from test where mytest is not NULL;

在MySQL3.23以后的版本有一个新的比较操作符“<=>”，它可对NULL值进行相等比较。如：

1
2
mysql> select * from test where mytest <=> UNLL;
mysql> select * from test where not (mytest <=>);

如果查询后排序中的数据中包含NULL，则从MySQL4.0.2开始，有NULL值的数据行总是出现在查询结果的开头，即使设置的desc参数。4.0.2以前版本，如果设置了asc，则出在查询结果的开头，如果设置了desc，则出现在查询结果的结尾。

8.6. 使用SQL变量

MySQL3.23.6以后的版本允许通过查询结果来设置变量，设置好的变量可在以后使用。变量用@name定义，赋值方式用@name:=value。下面是一个在查询语句中进行赋值和使用变量的例子：

1
2
mysql> select @name:=id from test where mytest="test";
mysql> select * from test where mytest=@name

8.7. 改变默认提示符

用mysql登录进数据库后，MySQL数据的默认提示符是“mysql”，我们可设置它根据用户打开的数据库名而变化，如：

1
2
3
4
mysql>prompt \d>\_
none>use test
test>use mysql
mysql>

prompt为设置命令，\d代表当前数据库，_代表一个空格。

8.8. 非优化的全数据表DELETE操作

为了清空数据表，又需知道删除的行数和保持AUTO_INCREMENT序列的值，需用以下的删除语句：

1
# delete from table_name where 1;

8.9. MySQL事务处理示例

MYSQL高级特性 – 事务处理下面以两个银行账户之间的转账为例子进行演示。要使用MySQL中的事务处理，首先需要创建使用事务表类型(如BDB = Berkeley DB或InnoDB)的表。

1
2
3
4
CREATE TABLE account (
account_id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
balance DOUBLE
) TYPE = InnoDB;

要在事务表上使用事务处理，必须要首先关闭自动提交：

1
SET AUTOCOMMIT = 0;

事务处理以BEGIN命令开始：

1
BEGIN;

现在mysql客户处在于服务器相关的事物上下文中。任何对事务表所做的改变在提交之前不会成为永久性的改变。

1
2
UPDATE ACCOUNT SET balance = 50.25 WHERE account_id = 1;
UPDATE ACCOUNT SET balance = 100.25 WHERE account_id = 2;

在做出所有的改变之后，使用COMMIT命令完成事务处理：

1
COMMIT;

当然，事务处理的真正优点是在执行第二条语句发生错误时体现出来的，若在提交前终止整个事务，可以进行回滚操作：

1
ROLLBACK;

下面是另一个例子，通过MYSQL直接进行数学运算：

1
2
3
4
SELECT @first := balance FROM account WHERE account_id = 1;
SELECT @second := balance FROM account WHERE account_id = 2;
UPDATE account SET balance = @first - 25.00 WHERE account_id = 1;
UPDATE account SET balance = @second + 25.00 WHERE account_id = 2;

除了COMMIT命令外，下列命令也会自动结束当前事务：

1
2
3
4
5
6
7
8
9
ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP TABLE
LOCK TABLES
RENAME TABLE
TRUNCATE
UNLOCK TABLES