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

第26章MySQL数据库服务器配置和管理 MySQL是一个完全开源的关系型数据库管理系统 由瑞典的MySQLAB公司研发 由于体积小 速度快 成本低 开放源代码等的特点 所以自推出后一直受到非常多使用者的喜爱和支持 许多中小型网站或者应用都会使用MySQL作为数据库 26 1数据库简介 数据库技术是计算机软件的一个重要的分支 而关系型数据库则是使用最广泛 也是最成熟的一种数据库技术 目前 市场上的主流关系型数据库产品有Oracle MircosoftSQLServer IBMDB2以及MySQL等 26 1 1数据库技术简介 计算机应用系统数据处理技术的发展经历了程序数据处理 文件数据处理和数据库数据处理3个阶段 发展至今 数据库已成为了计算机应用系统进行数据存储和处理的主要技术手段 从应用的角度来看 数据库技术具有以下主要特点 对数据进行集中管理 提供高效的数据共享 减少数据冗余 提供统一的数据存储和访问标准 保证数据的一致性 提供数据安全管理 方便用户使用 简化应用程序的开发和维护 26 1 2MySQL简介 MySQL是目前在开源社区中最受欢迎的一款完全开放源代码的小型关系型数据库管理系统 于1996年在互联网上发布第一个版本 自此MySQL得到了越来越多使用者的喜爱和支持 并被广泛地应用在Internet上的中小型网站中 26 1 3其他常见的数据库产品 除MySQL以外 市场上常见的关系型数据库产品还包括有Oracle SQLServer和DB2等 这些产品分别由不同的厂家研制 功能 特点也不相同 下面对这些主流的关系型数据库产品分别进行简单介绍 1 Oracle2 SQLServer3 DB2 26 2MySQL数据库服务器的安装 本节分别以MySQL5 0 67 0 rhel5版本的RPM和源代码安装包为例 介绍如何在RedHatEnterpriseLinux上搭建MySQL数据库服务器 如何启动 关闭MySQL数据库服务器 以及如何配置MySQL数据库的开机自动启动 26 2 1如何获得MySQL安装包 RedhatLinuxAS5 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 下面以MySQL5 0 67 0 rhel5版本的为例 分别介绍在RedHatEnterpriseLinux5 2上以RPM和源代码方式安装MySQL的具体步骤 1 RPM安装2 源代码安装 26 2 3启动和关闭MySQL MySQL建议用户在UNIX和Linux上使用mysqld safe命令 而不是mysqld来启动MySQL服务器 因为mysqld safe命令添加了一些安全特性 如当服务器发生错误时自动重启并把运行信息记录到错误日志文件等 该命令的格式如下 mysqld safeoptions1 启动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 3MySQL的基本配置 MySQL采用客户端 服务器的工作模式 用户可以通过MySQL的客户端程序 mysql 远程连接到服务器上进行操作 对MySQL服务器的配置可以通过更改配置文件my conf以及使用MySQL提供的命令工具来完成 26 3 1MySQL客户端程序 mysql命令是MySQL的客户端程序 通过该程序可以连接远端的MySQL数据库 建立连接后便可对数据库进行操作 刚安装完MySQL时 只能通过MySQL的管理员账号 即root 访问数据库服务器 该账号与Linux操作系统的root用户账号是不一样的 它是MySQL的内置账号 默认情况下 root用户的口令为空 用户直接输入mysql命令 即可访问本地的MySQL数据库 26 3 2MySQL配置文件 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 urootpassword新口令 26 3 4MySQL服务器管理程序 mysqladmin mysqladmin命令是MySQL服务器的管理程序 可用于执行检查配置文件 检查服务状态 关闭服务器 创建数据库 删除数据库等的系统管理操作 其命令格式如下所示 mysqladmin options command 26 4数据库管理 通过MySQL客户端程序登录系统后 可以在 mysql 提示符下使用SQL语言或命令对数据库进行管理 每个SQL语句或命令都以 或 g 结束 且不区分大小写 用户可以通过上下方向键选择曾经输入过的历史命令 数据库的操作包括查看 选择 创建和删除等 26 4 1查看数据库 MySQL安装后默认会创建两个数据库information schema和mysql 用户可以通过以下命令查看服务器中可用的数据库列表 mysql showdatabases Database information schema mysql test 3rowsinset 0 00sec mysql 26 4 2选择数据库 如果用户要对某个数据库进行操作 那么用户首先要使用use命令选择该数据库作为当前数据库 其命令格式如下所示 use数据库名称 26 4 3创建数据库 MySQL默认创建的数据库只是用于MySQL服务器本身的管理使用 如果用户要在数据库中保存应用数据 可以自行使用 createdatabase SQL语句创建一个新的MySQL数据库 该SQL语句的格式如下所示 createdatabase数据库名称 26 4 4删除数据库 如果一个数据库已经不再使用 可以使用 dropdatabase 把数据库删除 该SQL语句的语法格式如下所示 dropdatabase数据库名称 26 5数据表结构管理 用户可以使用客户端程序mysql远程登录MySQL数据库服务器对数据表结构进行管理 本节介绍如何通过mysql程序登录MySQL服务器 并进行查看表结构 创建数据表 更改表结构 复制表结构以及删除数据表等操作 26 5 1数据表结构 数据库中的数据都以二维表的形式被保存在不同的数据表中 其中每行表示一条数据记录 每条记录包含多个列 每列表示记录的一个字段 用户可以使用showtables命令查看数据库中有哪些数据表 如下所示 mysql useinformation schema Databasechangedmysql showtables Tables in information schema CHARACTER SETS COLLATIONS COLLATION CHARACTER SET APPLICABILITY COLUMNS 17rowsinset 0 00sec 26 5 2字段类型 字段类型决定了某个字段所能存储的数据的类型 了解各种数据类型的区别以及使用 对于用户合理设计表结构 充分利用空间有着莫大的帮助 MySQL的数据类型可分为3大类 即数字 日期时间和字符串 26 5 3创建数据表 一个数据库可以有多个数据表 数据表是同一类型数据的集合 在MySQL中可以通过 CREATETABLE 语句创建数据表 该SQL语句格式如下所示 CREATE TEMPORARY TABLE表名 字段1字段类型 字段选项 字段约束条件 字段2 表选项 SELECT语句 26 5 4更改表结构 对于已经创建的数据表 用户可以对其进行更改 添加 删除字段 更改字段的名称 类型等 更改数据表结构的SQL语句格式如下所示 ALTERTABLE数据表名更改1 更改2 26 5 5复制数据表 出于备份或测试的要求 经常需要对数据表进行复制 即生成一张与源数据表完全一样的数据表 MySQL提供了一些专门的SQL命令可以快速地完成这一操作 要复制一个数据表的表结构 其语法格式如下所示 CREATETABLE新表名LIKE源表名 26 5 6删除数据表 根据数据量的不同 数据库中的每张数据表都会占用一定的存储空间 所以 如果确定一张数据表不会再被使用 可以执行 DROPTABLE 命令删除该数据表 以释放对存储空间的使用 命令格式如下所示 DROPTABLE数据表名称 26 6数据管理 用户可以通过客户端程序mysql远程连接MySQL数据库服务器上对数据库中的数据进行管理 包括插入数据 更新数据 查询数据以及删除数据等 本节除了介绍MySQL数据库基本的数据管理操作外 还会介绍一些复杂的数据查询以及快速数据复制的技巧 26 6 1查询数据 使用SQL命令SELECT可以查询数据表中的数据 可以查询表中所有的数据 也可以根据特定的条件返回部分数据 还可以通过一些SQL函数进行特定的计算 其命令格式如下所示 SELECT 字段列表FROM数据表WHERE条件 1 查询所有字段的数据2 查询某些字段的数据3 查询满足某些条件的数据4 查询数据的总数 26 6 2插入数据 使用SQL语句INSERTINTO 可以往一张已经存在的数据表中插入新的数据 用户可以明文指定插入记录的值 也可以使用SELECT关键字插入其他数据表中的数据 具体的命令格式如下所示 INSERTINTO表名 字段1 字段2 VALUES 值1 值2 INSERTINTO表名 字段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字句过滤后的记录 其格式如下所示 DELETEFROM表名WHERE查询条件 26 7索引管理 为提高数据的查询速度 可以在一个或多个字段上创建索引 索引采用二叉树的形式组织数据 数据库可以通过索引快速地定位用户需要查找的数据的位置 本节介绍MySQL的索引管理 包括索引的创建和删除 26 7 1创建索引 用户可以在创建数据表的时候 对表中的某些字段创建索引 可以是单个字段 也可以是多个字段 这完全是根据用户实际需要而定 例如 mysql createtabledepartments department idchar 10 notnull department namevarchar 50 notnull manager idchar 10 indexind departments01 department id 创建索引ind departments01 QueryOK 0rowsaffected 0 00sec 26 7 2删除索引 要删除已经创建的索引 可以使用SQL命令DROPINDEX 删除索引不会影响数据表中的记录 其格式如下所示 DROPINDEX索引名ON表名 26 8用户和权限管理 MySQL的用户权限可以通过多个级别进行控制 包括全局权限 数据库级权限 表级权限以及字段级权限 可以用过更改MySQL的底层数据表或使用grant revoke命令 对用户权限进行授权以及回收 26 8 1MySQL权限控制原理 安装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地址 IDENTIFIEDBY 口令 WITHGRANTOPTION 1 授权数据库中所有对象的权限2 授权数据库中个别对象的权限3 控制访问的主机4 授予权限5 创建用户 26 8 4回收权限 revoke是MySQL中用于回收用户权限的管理命令 该命令工作的本质同样是对mysql数据库中的5张授权表中的记录进行更新 以完成用户权限的管理 其格式如下所示 REVOKE权限 字段 ON数据库名 数据表名FROM用户名 域名或IP地址 26 9MySQL备份恢复 MySQL的备份方式有多种 用户可以使用其自带的备份工具mysqldump和mysqlhotcopy 使用SQL语句BACKUPTABLE或SELECTINTOOUTFILE 或者是二进制日志 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备份工具 使用LOCKTABLES FLUSHTABLES cp和scp来完成数据库的备份 mysqlhotcopy只能备份使用MyIASM存储引擎的数据库和表 而且只能运行在数据库服务器上 该命令的格式如下所示 mysqlhotcopydb name path to new directory mysqlhotcopydb name 1 db name n path to new directorymysqlhotcopydb name regex 1 备份数据库2 恢复数据库 26 9 3使用SQL语句备份和恢复 MySQL提供了两种SQL语句可用于完成数据库备份 BACKUPTABLE和SELECTINTOOUTFILE 其中 BACKUPTABLE的原理与mysqlhotcopy差不多 都是先锁表 然后在复制数据文件 而SELECTINTOOUTFILE则是把查询的结果导出成一个普通的文本文件 但仅限于数据 而不会导出表结构 下面是使用SQL语句备份和恢复数据库的一些示例 1 BACKUPTABLE示例2 SELECTINTOOUTFILE示例 26 9 4启用二进制日志 MySQL的二进制日志 binlog 会以事务的形式记录数据库中所有更新数据的操作 它同时也是一种非常灵活的备份方式 可以支持增量备份 要启用二进制日志 需要更改 etc f配置文件 加入以下内容 server id 1log bin binloglog bin index binlog index 26 9 5直接备份数据文件 复制数据文件是最简单的备份方式 为保证数据的一致性 在复制前需要先关闭数据库 如果用户不想关闭数据库 可以执行如下命令锁定数据表 以保证复制过程中不会有新的数据写入表中 flushtable namewithreadlock 对于通过复制方式备份出来的数据文件 恢复时直接把它复制到MySQL的数据目录下即可 26 10MySQL图形化管理工具 phpMyAdmin是一款使用PHP编写的开源MySQL图形化管理配置程序 由TobiasRatschiller开发 目前有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 用户可以执行如下命令手工创建该配置文件 cpconfig sample inc phpconfig 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 11MySQL常见问题处理 本节将介绍在RedHatEnterpriseLinux5 2上安装及配置MySQL服务器中的常见问题以及它们的解决方法 包括如何解决访问phpMyAdmin首页时出现 置文件现在需要绝密的短语密码 警告 通过mysql客户端查询时出现Outofmemory错误以及忘记root用户口令后的解决方法等 26 11 1phpMyAdmin出现 置文件现在需要绝密的短语密码 警告 访问phpMyAdmin首页时出现置文件现在需要绝密的短语密码的警告信息 这是由于用户没有在config inc php文件中设置 cfg blowfish secret 选项 选项值为空 导致出现该警告信息 cfg blowfish secret 选项用于设置cookie的同步口令 用户可以设置任意的值 例如 cfg blowfish secret pass 设置完成后 警告信息将会消失 26 11 2查询时出现Outofmemory错误 用户通过mysql客户端程序连接数据库服务器并执行查询 出现如下的错误信息 mysql Outofmemoryatline42 malloc c 超出内存限制错误mysql needed8136byte 8k memoryinuse 12481367bytes 12189k ERROR2008 MySQLclientranoutofmemory这个错误的原因很简单 是由于客户端没有足够的内存保存MySQL的查询结果而导致 用户可以检查自己系统的内存使用情况 也可以更改SQL查询语句 简化查询结果 26 11 3忘记root用户口令解决方法 MySQL的系统管理员账号为root 拥有数据库中最高级别的权限 如果忘记了root用户的口令 可以通过以下步骤进行处理 1 杀掉mysql进程 2 以 skip grant tables选项启动mysqld 3 进入mysql并更改root用户的口令