MySQL数据库服务器配置和管理.ppt

第26章 MySQL数据库服务器配置和管理,MySQL是一个完全开源的关系型数据库管理系统，由瑞典的MySQL AB公司研发。由于体积小、速度快、成本低、开放源代码等的特点，所以自推出后一直受到非常多使用者的喜爱和支持，许多中小型网站或者应用都会使用MySQL作为数据库。,26.1 数据库简介,数据库技术是计算机软件的一个重要的分支，而关系型数据库则是使用最广泛，也是最成熟的一种数据库技术。目前，市场上的主流关系型数据库产品有Oracle、Mircosoft SQL Server、IBM DB2以及MySQL等。,26.1.1 数据库技术简介,计算机应用系统数据处理技术的发展经历了程序数据处理、文件数据处理和数据库数据处理3个阶段。发展至今，数据库已成为了计算机应用系统进行数据存储和处理的主要技术手段，从应用的角度来看，数据库技术具有以下主要特点。对数据进行集中管理。提供高效的数据共享。减少数据冗余。提供统一的数据存储和访问标准。保证数据的一致性。提供数据安全管理。方便用户使用，简化应用程序的开发和维护。,26.1.2 MySQL简介,MySQL是目前在开源社区中最受欢迎的一款完全开放源代码的小型关系型数据库管理系统，于1996年在互联网上发布第一个版本。自此MySQL得到了越来越多使用者的喜爱和支持，并被广泛地应用在Internet上的中小型网站中。,26.1.3 其他常见的数据库产品,除MySQL以外，市场上常见的关系型数据库产品还包括有Oracle、SQL Server和DB2等。这些产品分别由不同的厂家研制，功能、特点也不相同，下面对这些主流的关系型数据库产品分别进行简单介绍。1Oracle2SQL Server3DB2,26.2 MySQL数据库服务器的安装,本节分别以MySQL 5.0.67-0.rhel5版本的RPM和源代码安装包为例，介绍如何在Red Hat Enterprise Linux上搭建MySQL数据库服务器，如何启动、关闭MySQL数据库服务器，以及如何配置MySQL数据库的开机自动启动。,26.2.1 如何获得MySQL安装包,Redhat Linux AS5.2自带了5.0.45-7.el5版本的MySQL。用户只要在安装操作系统的时候把该软件选上，Linux安装程序将会自动完成MySQL的安装工作。如果在安装操作系统时没有安装MySQL，也可以通过安装光盘中的RPM软件包进行安装，RPM安装包文件的列表如下所示。mysql-server-5.0.45-7.el5.i386.rpmmysql-5.0.45-7.el5.i386.rpmmysql-connector-odbc-3.51.12-2.2.i386.rpmlibdbi-dbd-mysql-0.8.1a-1.2.2.i386.rpm,26.2.1 如何获得MySQL安装包,26.2.2 安装MySQL,下面以MySQL 5.0.67-0.rhel5版本的为例，分别介绍在Red Hat Enterprise Linux 5.2上以RPM和源代码方式安装MySQL的具体步骤。1RPM安装2源代码安装,26.2.3 启动和关闭MySQL,MySQL建议用户在UNIX和Linux上使用mysqld_safe命令，而不是mysqld来启动MySQL服务器，因为mysqld_safe命令添加了一些安全特性，如当服务器发生错误时自动重启并把运行信息记录到错误日志文件等。该命令的格式如下：mysqld_safe options1启动MySQL2使用ps命令检查MySQL进程3关闭MySQL4检测MySQL服务状态5其他启动关闭方式,26.2.4 开机自动运行MySQL服务,如果用户通过RPM方式进行安装，那么安装完成后，系统默认在开机时会自动启动MySQL服务。如果通过源代码方式进行安装，那么用户可以按照以下步骤来设置MySQL服务的开机自动启动。（1）编写mysql服务的启动关闭脚本，文件名为mysql，并存放到/etc/rc.d/init.d目录下。（2）在系统面板上选择【系统】|【管理】|【服务器设置】|【服务】命令，打开【服务配置】窗口。在其中选择【行动】|【添加服务】命令，弹出【添加服务】对话框。（3）单击【确定】按钮后，服务列表中将添加一个MySQL服务。,26.2.4 开机自动运行MySQL服务,26.3 MySQL的基本配置,MySQL采用客户端/服务器的工作模式，用户可以通过MySQL的客户端程序（mysql）远程连接到服务器上进行操作。对MySQL服务器的配置可以通过更改配置文件my.conf以及使用MySQL提供的命令工具来完成。,26.3.1 MySQL客户端程序,mysql命令是MySQL的客户端程序，通过该程序可以连接远端的MySQL数据库，建立连接后便可对数据库进行操作。刚安装完MySQL时，只能通过MySQL的管理员账号（即root）访问数据库服务器，该账号与Linux操作系统的root用户账号是不一样的，它是MySQL的内置账号。默认情况下，root用户的口令为空，用户直接输入mysql命令，即可访问本地的MySQL数据库。,26.3.2 MySQL配置文件,MySQL的配置文件是/etc/my.conf，该文件默认是不存在的。但在/usr/local/mysq/share/mysql/目录下提供了5个该配置文件的示例文件，如下所示。my-f：my-f：my-f：my-f：my-innodb-heavy-4G.cnf：,26.3.3 更改管理员口令,MySQL安装后，管理员（root）的口令默认为空。为了保证系统的安全，用户应该尽快更改root用户的口令。可以使用mysqladmin命令进行更改，命令的格式如下所示。mysqladmin u root password 新口令,26.3.4 MySQL服务器管理程序：mysqladmin,mysqladmin命令是MySQL服务器的管理程序，可用于执行检查配置文件、检查服务状态、关闭服务器、创建数据库、删除数据库等的系统管理操作。其命令格式如下所示。mysqladmin options command.,26.4 数据库管理,通过MySQL客户端程序登录系统后，可以在“mysql”提示符下使用SQL语言或命令对数据库进行管理。每个SQL语句或命令都以“;”或“g”结束，且不区分大小写，用户可以通过上下方向键选择曾经输入过的历史命令。数据库的操作包括查看、选择、创建和删除等。,26.4.1 查看数据库,MySQL安装后默认会创建两个数据库information_schema和mysql，用户可以通过以下命令查看服务器中可用的数据库列表。mysql show databases;|Database|information_schema|mysql|test|3 rows in set(0.00 sec)mysql,26.4.2 选择数据库,如果用户要对某个数据库进行操作，那么用户首先要使用use命令选择该数据库作为当前数据库，其命令格式如下所示。use 数据库名称;,26.4.3 创建数据库,MySQL默认创建的数据库只是用于MySQL服务器本身的管理使用，如果用户要在数据库中保存应用数据，可以自行使用“create database”SQL语句创建一个新的MySQL数据库，该SQL语句的格式如下所示。create database 数据库名称;,26.4.4 删除数据库,如果一个数据库已经不再使用，可以使用“drop database”把数据库删除，该SQL语句的语法格式如下所示。drop database 数据库名称;,26.5 数据表结构管理,用户可以使用客户端程序mysql远程登录MySQL数据库服务器对数据表结构进行管理。本节介绍如何通过mysql程序登录MySQL服务器，并进行查看表结构、创建数据表、更改表结构、复制表结构以及删除数据表等操作。,26.5.1 数据表结构,数据库中的数据都以二维表的形式被保存在不同的数据表中。其中每行表示一条数据记录，每条记录包含多个列，每列表示记录的一个字段。用户可以使用show tables命令查看数据库中有哪些数据表，如下所示。mysql use information_schema;Database changedmysql show tables-;|Tables_in_information_schema|CHARACTER_SETS|COLLATIONS|COLLATION_CHARACTER_SET_APPLICABILITY|COLUMNS|17 rows in set(0.00 sec),26.5.2 字段类型,字段类型决定了某个字段所能存储的数据的类型，了解各种数据类型的区别以及使用，对于用户合理设计表结构、充分利用空间有着莫大的帮助。MySQL的数据类型可分为3大类，即数字、日期时间和字符串。,26.5.3 创建数据表,一个数据库可以有多个数据表，数据表是同一类型数据的集合，在MySQL中可以通过“CREATE TABLE”语句创建数据表，该SQL语句格式如下所示。CREATE TEMPORARY TABLE 表名(字段1 字段类型 字段选项 字段约束条件,字段2.,.)表选项SELECT 语句,26.5.4 更改表结构,对于已经创建的数据表，用户可以对其进行更改，添加、删除字段，更改字段的名称、类型等。更改数据表结构的SQL语句格式如下所示。ALTER TABLE 数据表名 更改1,更改2.,26.5.5 复制数据表,出于备份或测试的要求，经常需要对数据表进行复制，即生成一张与源数据表完全一样的数据表。MySQL提供了一些专门的SQL命令可以快速地完成这一操作。要复制一个数据表的表结构，其语法格式如下所示。CREATE TABLE 新表名 LIKE 源表名,26.5.6 删除数据表,根据数据量的不同，数据库中的每张数据表都会占用一定的存储空间。所以，如果确定一张数据表不会再被使用，可以执行“DROP TABLE”命令删除该数据表，以释放对存储空间的使用，命令格式如下所示。DROP TABLE 数据表名称;,26.6 数据管理,用户可以通过客户端程序mysql远程连接MySQL数据库服务器上对数据库中的数据进行管理，包括插入数据、更新数据、查询数据以及删除数据等。本节除了介绍MySQL数据库基本的数据管理操作外，还会介绍一些复杂的数据查询以及快速数据复制的技巧。,26.6.1 查询数据,使用SQL命令SELECT可以查询数据表中的数据，可以查询表中所有的数据，也可以根据特定的条件返回部分数据，还可以通过一些SQL函数进行特定的计算，其命令格式如下所示。SELECT*|字段列表 FROM 数据表 WHERE 条件;1查询所有字段的数据2查询某些字段的数据3查询满足某些条件的数据4查询数据的总数,26.6.2 插入数据,使用SQL语句INSERT INTO，可以往一张已经存在的数据表中插入新的数据，用户可以明文指定插入记录的值，也可以使用SELECT关键字插入其他数据表中的数据，具体的命令格式如下所示。INSERT INTO 表名(字段1,字段2,.)VALUES(值1,值2,.);INSERT INTO 表名(字段1,字段2,.)SELECT字段1,字段2,.FROM 源表;1插入一条数据2插入其他表的数据,26.6.3 更新数据,对于数据表中的已有记录，可以使用SQL命令UPDATE对数据进行更新，更新的数据范围可以是表中的所有记录，也可以是经过WHERE子句过滤后的记录，该SQL命令的格式如下所示。UPDATE 表名 SET 字段1=值1,字段2=值2.WHERE 查询条件;,26.6.4 删除数据,要删除表中的数据，可以使用SQL命令DELETE。删除的范围可以是表中的所有记录，也可以是经过WHERE字句过滤后的记录，其格式如下所示。DELETE FROM 表名WHERE 查询条件;,26.7 索引管理,为提高数据的查询速度，可以在一个或多个字段上创建索引。索引采用二叉树的形式组织数据，数据库可以通过索引快速地定位用户需要查找的数据的位置。本节介绍MySQL的索引管理，包括索引的创建和删除。,26.7.1 创建索引,用户可以在创建数据表的时候，对表中的某些字段创建索引，可以是单个字段，也可以是多个字段，这完全是根据用户实际需要而定。例如：mysql create table departments(-department_id char(10)not null,-department_name varchar(50)not null,-manager_id char(10),-index ind_departments01(department_id)/创建索引ind_departments01-);Query OK,0 rows affected(0.00 sec),26.7.2 删除索引,要删除已经创建的索引，可以使用SQL命令DROP INDEX，删除索引不会影响数据表中的记录，其格式如下所示。DROP INDEX 索引名 ON 表名;,26.8 用户和权限管理,MySQL的用户权限可以通过多个级别进行控制，包括全局权限、数据库级权限、表级权限以及字段级权限。可以用过更改MySQL的底层数据表或使用grant/revoke命令，对用户权限进行授权以及回收。,26.8.1 MySQL权限控制原理,安装MySQL后，系统默认会创建一个名为mysql的数据库，系统中所有的用户以及这些用户的访问权限都由该数据库中的5张授权表控制。,26.8.2 用户管理,数据库用户管理是MySQL安全管理的基础，由于MySQL的所有用户信息都被保存在mysql数据库的user表中，所以可以通过对该表进行插入、更新、删除等操作来完成MySQL用户的管理，详细介绍如下所示。1添加用户2更改用户口令3删除用户,26.8.3 用户授权,grant是MySQL中用于授权的管理命令。实际上，这条命令工作的本质就是对mysql数据库中的5张授权表中的记录进行插入、更新，以完成用户权限的管理。grant命令的格式如下所示。GRANT 权限(字段)ON 数据库名.表名TO 用户名域名或IP地址 IDENTIFIED BY 口令 WITH GRANT OPTION;1授权数据库中所有对象的权限2授权数据库中个别对象的权限3控制访问的主机4授予权限5创建用户,26.8.4 回收权限,revoke是MySQL中用于回收用户权限的管理命令，该命令工作的本质同样是对mysql数据库中的5张授权表中的记录进行更新，以完成用户权限的管理，其格式如下所示。REVOKE 权限(字段)ON 数据库名.数据表名 FROM 用户名域名或IP地址,26.9 MySQL备份恢复,MySQL的备份方式有多种：用户可以使用其自带的备份工具mysqldump和mysqlhotcopy，使用SQL语句BACKUP TABLE或SELECT INTOOUTFILE，或者是二进制日志（binlog），还可以直接复制数据文件和相关配置文件。,26.9.1 使用mysqldump备份和恢复,mysqldump是MySQL自带的一个标准的在线备份工具，可以把数据表以SQL的形式导出成SQL脚本文件，是目前最常用的MySQL备份方式。该命令有3种格式，如下所示。第1种是导出指定的数据表，如果不指定tables，那么该命令将导出数据库中的所有表。第2种是导出一个多个指定数据库中的所有数据表。第3种是导出系统中所有数据库。1备份数据表2备份整个数据库3只导出表结构4恢复数据,26.9.2 使用mysqlhotcopy备份和恢复,mysqlhotcopy是一个使用PERL编写的MySQL备份工具，使用LOCK TABLES、FLUSH TABLES、cp和scp来完成数据库的备份。mysqlhotcopy只能备份使用MyIASM存储引擎的数据库和表，而且只能运行在数据库服务器上。该命令的格式如下所示。mysqlhotcopy db_name/path/to/new_directorymysqlhotcopy db_name_1.db_name_n/path/to/new_directorymysqlhotcopy db_name./regex/1备份数据库2恢复数据库,26.9.3 使用SQL语句备份和恢复,MySQL提供了两种SQL语句可用于完成数据库备份：BACKUP TABLE和SELECT INTO OUTFILE。其中，BACKUP TABLE的原理与mysqlhotcopy差不多，都是先锁表，然后在复制数据文件。而SELECT INTO OUTFILE则是把查询的结果导出成一个普通的文本文件，但仅限于数据，而不会导出表结构。下面是使用SQL语句备份和恢复数据库的一些示例。1BACKUP TABLE示例2SELECT INTO OUTFILE示例,26.9.4 启用二进制日志,MySQL的二进制日志（binlog）会以事务的形式记录数据库中所有更新数据的操作，它同时也是一种非常灵活的备份方式，可以支持增量备份。要启用二进制日志，需要更改/etc/f配置文件，加入以下内容：server-id=1log-bin=binloglog-bin-index=binlog.index,26.9.5 直接备份数据文件,复制数据文件是最简单的备份方式，为保证数据的一致性，在复制前需要先关闭数据库。如果用户不想关闭数据库，可以执行如下命令锁定数据表，以保证复制过程中不会有新的数据写入表中。flush table_name with read lock;对于通过复制方式备份出来的数据文件，恢复时直接把它复制到MySQL的数据目录下即可。,26.10 MySQL图形化管理工具,phpMyAdmin是一款使用PHP编写的开源MySQL图形化管理配置程序，由Tobias Ratschiller开发，目前有50多种各国语言版本。使用phpMyAdmin，可以通过Web界面对MySQL进行管理，可以创建、删除数据库，创建、更改和删除数据表，管理数据，执行任何SQL语句，管理用户和权限，备份数据等。,26.10.1 获得phpMyAdmin安装包,phpMyAdmin是一款非常出色的开源MySQL图形化管理配置程序，用户可以通过phpMyAdmin的官方网站http:/www.phpMyA,26.10.2 安装phpMyAdmin,phpMyAdmin是使用PHP编写的Web程序，需要依赖于Web服务器运行。所以在安装phpMyAdmin前需要先安装Apache和PHP，以及在配置Apache中配置PHP模块。关于Apache和PHP的详细安装配置步骤可参考17.4.3 节中的内容，需要注意的是在安装PHP时要使用“-with-mysql”选项，如下所示。./configure-with-apxs2=/usr/local/apache/bin/apxs-with-mysql=/usr/local/mysql,26.10.3 配置phpMyAdmin,phpMyAdmin的配置主要通过修改配置文件config.inc.php完成。解压安装包后默认并不会创建该文件，但在解压目录下会有一个示例文件config.sample.inc.php，用户可以执行如下命令手工创建该配置文件。cp config.sample.inc.php config.inc.php,26.10.4 登录phpMyAdmin,配置完成后，启动Apache服务。在本地浏览器地址栏中输入http:/localhost/phpAdmin/index.php，如果正常，将会看到如图26.5所示的登录页面。可以在其中选择显示的语言。在【登入名称】和【密码】文本框中分别输入MySQL数据库的登录用户和口令，然后单击【执行】按钮。,26.10.4 登录phpMyAdmin,26.10.5 数据库管理,使用phpMyAdmin，可以图形化界面的方式管理数据库，包括查看已有数据库的列表、创建和删除MySQL数据库等的操作。用户可以根据界面中的提示信息一步一步进行操作，而无需输入SQL代码，具体介绍如下所示。1查看数据库2创建数据库3删除数据库,26.10.5 数据库管理,26.10.6 数据表管理,使用phpMyAdmin，可以图形化界面的方式管理MySQL数据库中的表，包括对数据表进行查看、创建、更改和删除等的操作。用户可以根据界面中的提示信息一步一步进行操作，而无需输入SQL代码，具体介绍如下所示。1创建数据表2更改表结构3删除数据表,26.10.6 数据表管理,26.10.7 表记录管理,可以使用phpMyAdmin对数据表中的记录进行查询、更新或删除，此外，对于一些复杂的数据操作，使用SQL语句可能会更加方便，所以在phpMyAdmin中也提供了执行SQL语句的界面，用户可以在其中编写SQL代码并执行。具体的介绍如下所示。1插入记录2查询记录3更新和删除数据4执行SQL语句,26.10.8 用户权限管理,在phpMyAdmin的图形界面中也可以对MySQL服务器的用户进行查看、创建、更改以及删除等的操作，以及对用户进行权限管理，而无需直接对底层的数据表user进行操作。关于用户权限管理的详细介绍如下所示。1查看已有用户2添加用户3更改和删除用户,26.10.8 用户权限管理,26.11 MySQL常见问题处理,本节将介绍在Red Hat Enterprise Linux 5.2上安装及配置MySQL服务器中的常见问题以及它们的解决方法，包括如何解决访问phpMyAdmin首页时出现“置文件现在需要绝密的短语密码”警告，通过mysql客户端查询时出现Out of memory错误以及忘记root用户口令后的解决方法等。,26.11.1 phpMyAdmin出现“置文件现在需要绝密的短语密码”警告,访问phpMyAdmin首页时出现置文件现在需要绝密的短语密码的警告信息，这是由于用户没有在config.inc.php文件中设置“$cfgblowfish_secret”选项，选项值为空，导致出现该警告信息。“$cfgblowfish_secret”选项用于设置cookie的同步口令，用户可以设置任意的值，例如：$cfgblowfish_secret=pass;设置完成后，警告信息将会消失。,26.11.2 查询时出现Out of memory错误,用户通过mysql客户端程序连接数据库服务器并执行查询，出现如下的错误信息。mysql:Out of memory at line 42,malloc.c/超出内存限制错误mysql:needed 8136 byte(8k),memory in use:12481367 bytes(12189k)ERROR 2008:MySQL client ran out of memory这个错误的原因很简单，是由于客户端没有足够的内存保存MySQL的查询结果而导致，用户可以检查自己系统的内存使用情况，也可以更改SQL查询语句，简化查询结果。,26.11.3 忘记root用户口令解决方法,MySQL的系统管理员账号为root，拥有数据库中最高级别的权限。如果忘记了root用户的口令，可以通过以下步骤进行处理。（1）杀掉mysql进程。（2）以-skip-grant-tables选项启动mysqld。（3）进入mysql并更改root用户的口令。,