MySQL管理员手册

MySQL管理员指南            

2001-06 余枫编Mysql数据库介绍    MySQL是一个真正的多用户、多线程SQL数据库服务器。SQL（结构化查询语言）是世界上最流行的和标准化的数据库语言。MySQL是以一个客户机/服务器结构的实现，它由一个服务器守护程序mysqld和很多不同的客户程序和库组成。    SQL是一种标准化的语言，它使得存储、更新和存取信息更容易。例如，你能用SQL语言为一个网站检索产品信息及存储顾客信息，同时MySQL也足够快和灵活以允许你存储记录文件和图像。    MySQL 主要目标是快速、健壮和易用。最初是因为我们需要这样一个SQL服务器，它能处理与任何可不昂贵硬件平台上提供数据库的厂家在一个数量级上的大型数据库，但速度更快，MySQL就开发出来。自1996年以来，我们一直都在使用MySQL，其环境有超过 40 个数据库，包含 10,000个表，其中500多个表超过7百万行，这大约有100 个吉字节(GB)的关键应用数据。Mysql数据库特点1.  使用核心线程的完全多线程。这意味着它能很容易地利用多CPU（如果有）。 2.  可运行在不同的平台上。3.  多种列类型：1、 2、 3、4、和 8 字节长度的有符号/无符号整数(INT)、FLOAT、DOUBLE、CHAR、    VARCHAR、TEXT、BLOB、DATE、TIME、DATETIME、 TIMESTAMP、YEAR、SET和ENUM类型。 4.  利用一个优化的一遍扫描多重联结（one-sweep multi-join）非常快速地进行联结(join)。 5.  在查询的SELECT和WHERE部分支持全部运算符和函数。6.  通过一个高度优化的类库实现SQL函数库并且像他们能达到的一样快速，通常在查询初始化后不应该有任    何内存分配。 7.  全面支持SQL的GROUP BY和ORDER BY子句，支持聚合函数( COUNT()、COUNT(DISTINCT)、AVG()、STD()、    SUM()、 MAX()和MIN() )。 8.  支持ANSI SQL的LEFT OUTER JOIN和ODBC语法。 9.  你可以在同一查询中混用来自不同数据库的表。10. 一个非常灵活且安全的权限和口令系统，并且它允许基于主机的认证。口令是安全的，因为当与一个服    务器连接时，所有的口令传送被加密。 11. ODBC for Windiws 95。 12. 具备索引压缩的快速B树磁盘表。 13. 每个表允许有16个索引。每个索引可以由1～16个列或列的一部分组成。最大索引长度是 256 个字节(在    编译MySQL时，它可以改变)。一个索引可以使用一个CHAR或VARCHAR字段的前缀。 14. 定长和变长记录。 15. 用作临时表的内存散列表。 16. 大数据库处理。我们正在对某些包含 50,000,000 个记录的数据库使用MySQL。 17. 所有列都有缺省值，你可以用INSERT插入一个表列的子集，那些没用明确给定值的列设置为他们的缺省    值。 18. 一个非常快速的基于线程的内存分配系统。 19. 没有内存漏洞。用一个商用内存漏洞监测程序测试过(purify)。 20. 包括myisamchk，一个检查、优化和修复数据库表的快速实用程序。 21. 全面支持ISO-8859-1  Latin1 字符集。 22. 所有数据以 ISO-8859-1  Latin1 格式保存。所有正常的字符串比较是忽略大小写的。 23. DELETE、INSERT、REPLACE和UPDATE 返回有多少行被改变(受影响)。 24. 函数名不会与表或列名冲突。例如ABS是一个有效的列名字。对函数调用的唯一限制是函数名与随后的    "("不能有空格。25. 所有MySQL程序可以用选项--help或-?获得联机帮助。 26. 服务器能为客户提供多种语言的出错消息。 27. 客户端使用TCP/IP 连接或Unix套接字（socket）或NT下的命名管道连接MySQL。 28. MySQL特有的SHOW命令可用来检索数据库、表和索引的信息，EXPLAIN命令可用来确定优化器如何解决一    个查询。    MySQL服务器的启动与停止一、启动服务器的方法启动服务器由三种主要方法：1、 直接调用mysqld。#./mysqld&这可能是最不常用的方法，建议不要多使用。 2、调用safe_mysqld脚本，最好的方法。#./safe_mysqld -O join_buffer=128M -O key_buffer=128M -O record_buffer=256M -O sort_buffer=128M -O table_cache=2048 -O tmp_table_size=16M -O max_connections=2048 &3、调用mysql.server脚本。safe_mysqld脚本安装在MySQL安装目录的bin目录下，或可在MySQL源代码分发的scripts目录下找到。mysql.server脚本安装在MySQL安装目录下的share/mysqld目录下或可以在MySQL源代码分发的support_files目录下找到。如果你想使用它们，你需要将它们拷贝到适当的目录下mysql/bin下。#./mysql.server startSun Solariys开机自动启mysql的方法写一个启动和关闭的批处理文件Web (在路径/etc/init.d 下), 内容如下:#!/bin/shOPT_=$1case "$OPT_" in    start)/bin/echo "$0 : (start)"## Your service startup command goes here.#       /usr/local/apache/bin/apachectl start       /home3/mysql/bin/safe_mysqld -O join_buffer=128M -O key_buffer=128M -O record_buffer=256M -O sort_buffer=128M -O table_cache=2048 -O tmp_table_size=16M -O max_connections=2048 &# NOTE: Must exit with zero unless error is severe.exit 0;;    stop)/bin/echo "$0 : (stop)"## Your service shutdown command goes here.#       /usr/local/apache/bin/apachectl stop# NOTE: Must exit with zero unless error is severe.exit 0;;    *)/bin/echo ''/bin/echo "Usage: $0 [start|stop]"/bin/echo "       Invalid argument ==> /"${OPT_}/""/bin/echo ''exit 0;;esac确认此文件有可执行的权利#chmod 500 web#cd /etc/rc2.d#ln -s ../init.d/web S99mysql在系统启动时，S99mysql脚本将自动用一个start参数调用。注意头字母必须大写。二、停止服务器的方法1、要手工停止服务器，使用mysqladmin：#mysqladmin -u 用户名 -p'密码' shutdown2、 调用mysql.server脚本，最好的方法。#./mysql.server stop3、 直接杀掉OS的进程号#kill -9 进程号这可能是最不常用的方法，建议不要多使用。要自动停止服务器，你不需做特别的事情。只需要加另外一个关闭程序。#cd /etc/rc0.d#ln -s ../init.d/web K01mysql在系统启动时，K01mysql脚本将自动用一个stop参数调用。MySQL目录结构和常用命令一、 数据目录的位置这是默认的mysql目录结构bin        info       libexec    share      varinclude    lib        man        sql-bench   一个缺省数据目录被编译进了服务器，如果你从一个源代码分发安装MySQL，典型的缺省目录为/usr/local/var，如果从RPM文件安装则为/var/lib/mysql，如果从一个二进制分发安装则是/usr/local/mysql/data。作为一名MySQL管理员，你应该知道你的数据目录在哪里。如果你运行多个服务器，你应该是到所有数据目录在哪里，但是如果你不知道确切的位置，由多种方法找到它：1、使用mysqladmin variables从你的服务器直接获得数据目录路径名。查找datadir变量的值，在Unix上，其输出类似于：%mysqladmin -u username -p'*****' variables+----------------------+----------------------+| variable_name        | Value                |+----------------------+----------------------+| back_log             | 5                    || connect_timeout      | 5                    || basedir              | /var/local/          || datadir              | /usr/local/var/      |....2、查找mysql运行的路径%ps -ef | grep mysqld            二、数据目录结构每个数据库对应于数据目录下的一个目录。 在一个数据库中的表对应于数据目录下的文件。 数据目录也包含由服务器产生的几个状态文件，如日志文件。这些文件提供了关于服务器操作的重要信息。对管理特别在出了问题而试图确定问题原因时很有价值。1、数据库表的表示数据库目录中有3种文件：一个样式（描述文件）、一个数据文件和一个索引文件。每个文件的基本名是表名，文件名扩展名代表文件类型。扩展名如下表。数据和索引文件的扩展名指出表使用老式IASM索引或新式MyISAM索引。MySQL文件类型文件类型文件名扩展名文件内容样式文件.frm描述表的结构（它的列、列类型、索引等）数据文件.ISD(ISAM)或.MYD(MyISAM)包含表里所有的数据索引文件.ISM(ISAM)或.MYI(MyISAM)包含数据文件上的所有索引的索引树当你发出一条CREATE TABLE tbl_name时语句定义表的结构时，服务器创建一个名为tbl_name.frm的文件，它包括该结构的内部编码，同时也创建一个空数据和索引文件，初始化为包含指出无记录和无索引的信息（如果CREATE TABLE语句包括索引指定，索引文件反映出这些索引）。对应于表的文件的属主和模式被设置为只允许MySQL服务器用户访问。下面介绍一下常用的mysql命令进入mysql数据库#./mysql -u 用户名 -p'密码'查看所有的数据库mysql> show databases;进入一个特定的数据库mysql> use 数据库名;查看数据库里所有的表mysql> show tables;把表改名mysql> alter table 表名1 rename 表名2;例子：mysql>alter table dept rename dept2;2、 建索引的注意事项：先要把要加索引的字段设为非空mysql> alter table 表名 change 字段名 字段名 字段描述 not null;例子：我们创建这样一个表mysql> create table employee( id int(5) not null, depno int(5), name varchar(20) not null,  cardnumber bigint(15) not null);mysql> alter table employee change depno depno int(5) not null;加索引mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]);例子： mysql> alter table employee add index emp_name (name);加主关键字的索引mysql> alter table 表名 add primary key (字段名);例子： mysql> alter table employee add primary key(id);加唯一限制条件的索引mysql> alter table 表名 add unique 索引名 (字段名);例子： mysql> alter table employee add unique emp_name2(cardnumber);查看某个表的索引mysql> show index from 表名;例子： mysql> show index from employee;删除某个索引mysql> alter table 表名 drop index 索引名;例子： mysql>alter table employee drop index emp_name;MySQL用户管理MySQL管理员应该知道如何设置MySQL用户账号，指出哪个用户可以连接服务器，从哪里连接，连接后能做什么。MySQL 3.22.11开始引入两条语句使得这项工作更容易做：GRANT语句创建MySQL用户并指定其权限，而REVOKE语句删除权限。两条语句扮演了mysql数据库的前端角色，并提供与直接操作这些表的内容不同的另一种方法。CREATE和REVOKE语句影响4个表：授权表内容：user 能连接服务器的用户以及他们拥有的任何全局权限 db 数据库级权限 tables_priv 表级权限 columns_priv 列级权限 还有第5个授权表（host），但它不受GRANT和REVOKE的影响。当你对一个用户发出一条GRANT语句时，在user表中为该用户创建一条记录。如果语句指定任何全局权限（管理权限或适用于所有数据库的权限），这些也记录在user表中。如果你指定数据库、表和列级权限，他们被分别记录在db、tables_priv和columns_priv表中。在下面的章节中，我们将介绍如何设置MySQL用户账号并授权。我们也涉及如何撤权和从授权表中删除用户。一、创建用户并授权GRANT语句的语法看上去像这样：GRANT privileges (columns)    ON what    TO user IDENTIFIED BY "password"    WITH GRANT OPTION　要使用该语句，你需要填写下列部分：privileges 授予用户的权限，下表列出可用于GRANT语句的权限指定符：权限指定符 权限允许的操作：ALTER 修改表和索引 CREATE 创建数据库和表 DELETE 删除表中已有的记录 DROP 抛弃（删除）数据库和表 INDEX 创建或抛弃索引 INSERT 向表中插入新行 REFERENCE 未用 SELECT 检索表中的记录 UPDATE 修改现存表记录 FILE 读或写服务器上的文件 PROCESS 查看服务器中执行的线程信息或杀死线程 RELOAD 重载授权表或清空日志、主机缓存或表缓存。 SHUTDOWN 关闭服务器 ALL 所有；ALL PRIVILEGES同义词 USAGE 特殊的“无权限”权限 columns 权限运用的列，它是可选的，并且你只能设置列特定的权限。如果命令有多于一个列，应该用逗号分开它们.what 权限运用的级别。权限可以是全局的（适用于所有数据库和所有表）、特定数据库（适用于一个数据库中的所有表）或特定表的。可以通过指定一个columns字句是权限是列特定的。user 权限授予的用户，它由一个用户名和主机名组成。MySQL中的一个用户名就是你连接服务器时指定的用户名，该名字不必与你的Unix登录名或Windows名联系起来。缺省地，如果你不明确指定一个名字，客户程序将使用你的登录名作为MySQL用户名。这只是一个约定。你可以在授权表中将该名字改为nobody，然后以nobody连接执行需要超级用户权限的操作。password 赋予用户的口令，它是可选的。如果你对新用户没有指定IDENTIFIED BY子句，该用户不赋给口令（不安全）。对现有用户，任何你指定的口令将代替老口令。如果你不指定口令，老口令保持不变，当你用IDENTIFIED BY时，口令字符串用改用口令的字面含义，GRANT将为你编码口令，不要象你用SET PASSWORD 那样使用password()函数。WITH GRANT OPTION子句是可选的。如果你包含它，用户可以授予权限通过GRANT语句授权给其它用户。你可以用该子句给与其它用户授权的能力。用户名、口令、数据库和表名在授权表记录中是大小写敏感的，主机名和列名不是。举例：创建一个超级用户test1mysql> grant all privilleges on *.* to test1@localhost identified by '123456' with grant option;  创建一个只能查询的用户 test2mysql> grant select on *.* to test2@localhost identified by '9876543';二、撤权并删除用户要取消一个用户的权限，使用REVOKE语句。REVOKE的语法非常类似于GRANT语句，除了TO用FROM取代并且没有INDETIFED BY和WITH GRANT OPTION子句：REVOKE privileges (columns) ON what FROM useruser部分必须匹配原来GRANT语句的你想撤权的用户的user部分。privileges部分不需匹配，你可以用GRANT语句授权，然后用REVOKE语句只撤销部分权限。REVOKE语句只删除权限，而不删除用户。即使你撤销了所有权限，在user表中的用户记录依然保留，这意味着用户仍然可以连接服务器。要完全删除一个用户，你必须用一条DELETE语句明确从user表中删除用户记录：#mysql -u root mysqlmysql>DELETE FROM user     ->WHERE User="user_name" and Host="host_name";mysql>FLUSH PRIVILEGES;　DELETE语句删除用户记录，而FLUSH语句告诉服务器重载授权表。（当你使用GRANT和REVOKE语句时，表自动重载，而你直接修改授权表时不是。）举例：删除用户test1mysql> revoke all on *.* from test2@localhost;mysql> use mysql;mysql> delete from user where user='test' and host='localhost';mysql> flush privileges;MySQL数据库备份在数据库表丢失或损坏的情况下，备份你的数据库是很重要的。已经知道表被破坏，用诸如vi或Emacs等编辑器试图直接编辑它们，这对表绝对不是件好事！备份数据库两个主要方法是用mysqldump程序或直接拷贝数据库文件（如用cp、cpio或tar等）。每种方法都有其优缺点：mysqldump与MySQL服务器协同操作。直接拷贝方法在服务器外部进行，并且你必须采取措施保证没有客户正在修改你将拷贝的表，一般在数据库关闭情况下做。mysqldump比直接拷贝要慢些。mysqldump生成能够移植到其它机器的文本文件，甚至那些有不同硬件结构的机器上。直接拷贝文件可以移植到同类机器上，但不能移植到其它机器上，除非你正在拷贝的表使用MyISAM存储格式。一、使用mysqldump备份和拷贝数据库当你使用mysqldump程序产生数据库备份文件时，缺省地，文件内容包含创建正在倾倒的表的CREATE语句和包含表中行数据的INSERT语句。换句话说，mysqldump产生的输出可在以后用作mysql的输入来重建数据库。Mysqldump参数如下：#mysqldump -u 用户名-p'密码' 数据库名 [表名] > 操作系统下文件名举例：#./mysqldump -u root -p'123456' samp_db>samp.db.txt输出文件的开头看起来象这样：# MySQL Dump 6.0# # Host: localhost    Database: samp_db#---------------------------------------# Server version 3.23.2-alpha-log## Table structure for table 'absence'#CREATE TABLE absence(  student_id int(10) unsigned DEFAULT '0' NOT NULL,  date date DEFAULT '0000-00-00' NOT NULL,  PRIMARY KEY (student_id,date));## Dumping data for table 'absence'#INSERT INTO absence VALUES (3,'1999-09-03');INSERT INTO absence VALUES (5,'1999-09-03');INSERT INTO absence VALUES (10,'1999-09-08');......　文件剩下的部分有更多的INSERT和CREATE TABLE语句组成。输出单个的表：#mysqldump samp_db student score event absence >grapbook.sql#mysqldump samp_db member president >hist-league.sql缺省地，mysqldump在写入前将一个表的整个内容读进内存。这通常确实不必要，并且实际上如果你有一个大表，几乎是失败的。你可用--quick选项告诉mysqldump只要它检索出一行就写出每一行。为了进一步优化倾倒过程，使用--opt而不是--quick。--opt选项打开其它选项，加速数据的倾倒和把它们读回。 二、使用直接拷贝数据库的备份和拷贝方法另一种不涉及mysqldump备份数据库和表的方式是直接拷贝数据库表文件。典型地，这用诸如cp、tar或cpio实用程序。本文的例子使用cp。%cd DATADIR%cp -r samp_db /usr/archive/mysql单个表可以如下备份：%cd DATADIR/samp_db%cp member.* /usr/archive/mysql/samp_db%cp score.* /usr/archive/mysql/samp_db....当你完成了备份时，你可以重启服务器（如果关闭了它）或释放加在表上的锁定（如果你让服务器运行）。要用直接拷贝文件把一个数据库从一台机器拷贝到另一台机器上，只是将文件拷贝到另一台服务器主机的适当数据目录下即可。要确保文件是MyIASM格式或两台机器有相同的硬件结构，否则你的数据库在另一台主机上有奇怪的内容。你也应该保证在另一台机器上的服务器在你正在安装数据库表时不访问它们。建一个sh文件bakmysql.sh#!/bin/shcd /usr/local/mysql/tar cvf  /mount2/mysqlvar.tar var可以设到操作系统自动运行。root用户crontab文件/var/spool/cron/crontabs/root增加以下内容0 2 1 * * /mount2/bakmysql.sh#/etc/rc2.d/S75cron stop#/etc/rc2.d/S75cron start重新击活Sun Solaris自动处理进程三、用备份恢复数据数据库损坏的发生有很多原因，程度也不同。如果你走运，你可能仅损坏一两个表（如掉电），如果你倒霉，你可能必须替换整个数据目录（如磁盘损坏）。在某些情况下也需要恢复，比如用户错误地删除了数据库或表。不管这些倒霉事件的原因，你将需要实施某种恢复。如果表损坏但没丢失，尝试用myisamchk或isamchk修复它们，如果这样的损坏可有修复程序修复。1、 恢复整个数据库Mysqldump参数如下：#mysqldump -u 用户名-p'密码' 数据库名 < 操作系统下文件名举例：先在Mysql里创建另一个数据库mysql> create database test;然后将备份的数据导入#mysqldump -u root -p'123456' test2 < samp.db.txt