MySQL数据库系统

apache HTTP Server web服务器软件：提供了面向用户的前端应用功能。
在实际的企业网站平台中，为了提供更丰富、更强大的web应用功能，往往还需要有后台数据库、网页编程语言等多种角色的支持

MySQL服务基础
MySQL的编译安装
1：准备工作
rpm -q mysql-server mysql //为避免发生端口、程序冲突，建议先查询MySQL软件的安装情况 确认没有rpm方式安装的mysql-server、mysql软件包
2：源码编译及安装
1：创建运行用户 //默认mysql用户不存在必须手工创建
useradd -M -u 49 -s /sbin/nologin mysql //为了加强数据库服务的权限控制，建议使用专门的运行用户，此用户不需要直接登录到系统，无宿主目录
2：解包
tar zxvf mysql-5.1.55.tar.gz -C /usr/src/ //将源代码包解压到/usr/src/目录下
cd /usr/src/mysql-5.1.55/ //切换到展开后的源代码目录
3：配置
./configure --prefix=/usr/local/mysql --with-charset=utf8 //-prefix 指定安装目录 -with-charset 默认使用的字符集编码
//应与校对规则相对应 utf8_general_ci 与UTF-8字符集对应
--with-collation=utf8_general_ci --with-extra-charsets=gbk,gb2312 //--with-collation 指定默认使用的字符集校对规则
//--with-extra-charset 指定额外支持的其他字符集编码
注意：
在mysql 5.1.55版本源代码包中，对于通过--with-extra-charset配置选项添加的其他字符集编码，还需要对其进行一些小调整，才能在编译后获得
完整的支持,如修改include子文件夹下的config.h文件，添加相应的字符集编译定义即可

vi include/config.h //注意是在源代码目录
#define HAVE_CHARSET_gbk 1
#define HAVE_CHARSET_gb2312 1

4：编译并安装
make && make install
3：安装后的其他调整
1：建立配置文件
在MySQL源码目录中的support-files文件夹下，提供了适合不同负载数据库的样本配置文件，如果不确定数据库系统的应用规模，一般选择https://www.360docs.net/doc/874794859.html,f
文件即可，该文件能够满足大多数企业的中等应用需求，
cp support-files/https://www.360docs.net/doc/874794859.html,f /etc/https://www.360docs.net/doc/874794859.html,f //根据样例建立MySQL系统的/etc/https://www.360docs.net/doc/874794859.html,f配置文件 中型企业、大型企业、小型企业配置文件
2：初始化数据库
cd /usr/local/mysql/bin/ //初始化脚本mysql_install_db 位于安装目录下的bin文件夹中
./mysql_install_db --user=mysql //为了能够正常使用MySQL数据系统，应以运行用户mysql的身份执行初始化任务，以便建立
用户授权库、表、以及test测试库
chown -R root:mysql /usr/local/mysql/ //调整用于存放数据库内容的文件夹归属，以便mysql用户有权限对其进行读写操作
chown -R mysql /usr/local/mysql/var/

3：优化执行路径、程序库路径
为MySQL 系统的各种执行程序添加符号链接，以方便其使用，另外建议为MySQL的库文件、头文件、也添加符号链接，便于在安装其他的用到这些文件的
软件包时能自动搜索
ln -s /usr/local/mysql/bin/* /usr/local/bin/
ln -s /usr/local/mysql/lib/mysql/* /usr/lib/
ln -s /usr/local/mysql/include/mysql/* /usr/include/
4：添加系统服务
目的：希望添加mysqld系统服务，以便通过chkconfig进行管理，可以直接使用源码包中提供的服务脚本，找到support-file文件夹下的mysql.server脚本文件
复制到 /etc/init.d/目录下，并改名为mysql，然后设置执行权限，通过chkconfig命令将其添加为mysqld系统服务
cd /usr/src/mysql-5.1.55/ //进入到安装目录
cp support-files/mysql.server /etc/rc.d/init.d/mysqld //将support-files文件夹下的mysql.server脚本文件 复制到 /etc/init.d/目录下，并改名为mysql
chmod a+x /etc/rc.d/init.d/mysqld //设置执行权限
chkconfig --add mysqld //通过chkconfig 命令添加为mysqld系统服务

这样以后就可以使用service工具直接执行/etc/init.d/mysqld脚本来控制MySQL数据库服务了。
/etc/init.d/mysqld start
/etc/init.d/mysqld status
netstat -anpt | grep mysqld
注意：
MySQL服务器默认通过TCP 3306端口提供服务，通过编辑/etc/https://www.360docs.net/doc/874794859.html,f配置文件中[mysqld]配置段的 port=3306行 可以更改监听端口

访问MySQL数据库
windows 平台：MySQL Manager、MySQL Front、Navicat Lite等软件
Linux 平台：最简单最易用MySQL客户端软件是其自带的MySQL命令工具
1：登陆到MySQL服务器
经过安装后的初始化过程，MySQL数据库的默认管理员账号为root，密码为空。
以未设置密码的root用户登陆本机的MySQL数据库
mysql -u root //-u 选项用于指定认证用户
登陆其他MySQL服务器，
mysql -h ip地址 -u 用户名 -p //-h 指定目标主机地址，有密码的情况 使用-p 选项进行密码校验
输入密码
2：执行MySQL操作语句
验证成功后将会进入到提示符为 mysql>的数据库操作环境，
每一条MySQL操作语句以分号； 表示结束，输入时可以不区分大小写，习惯上将MySQL语句中的关键字部分使用大写
3：退出mysql>操作环境
EXIT或QUIT

使用MySQL数据库
查看数据库结构
特点：每一台MySQL服务器中，支持运行多个库，每一个库相当于一个容器，其中存放着许许多多的表，
1：查看当前服务器中有哪些库
SHOW DATABASES; //列出当前MySQL服务器中包含的库，经过初始化后默认建立三个库 test 、mysql、information_schema
mysql库中包含了用户认证相关的表

2：查看当前使用的库中有哪些表
SHOW TABLES ;

//用于列出当前所在的库中包含的表，注意：在操作之前，需要先使用USE语句切换到所使用的库
USE mysql; //USER 切换到所使用的库
SHOW TABLES; //查看当前库中有哪些表
MySQL数据库的数据文件默认存放在/usr/local/mysql/var/目录下，每个数据库对应一个子目录，用于存储数据表文件，每一个数据表对应为三个文件
后缀名分别为 .frm .MYD .MYI
3：查看表结构
DESCRIBE //显示表的结构
指定库名.表明作为参数
指定表名参数 需要先通过USE语句切换到目标库
USE mysql; //切换到目标库
DESCRIBE user; //查看mysql库中的user表的结构
或者
DESCRIBE https://www.360docs.net/doc/874794859.html,er //查看mysql库中的user表的结构


创建及删除库和表
1：创建新的库
CREATE DATABASE 库名 //用于创建一个新的库 指定库名作为参数
注意：刚创建的数据库是空的，其中不包含任何表，在/usr/local/mysql/var/目录下会自动生成一个与新建的库名相同的空文件夹

2：创建新的表
CREATE TABLE 表名 并定义该表格所使用的各字段。
格式：
CREATE TABLE 表名 （字段1名称 类型，字段2名称 类型,......... ,PRIMARY KEY (主键名)） //主键唯一
创建表之前，应先明确数据表格的结构、各字段的名称和类型等信息，
案例：
在auth库中按如下操作创建users表，其中字段定义部分的DEFAULT用于设置默认的密码字串，PRIMARY 用于设置主键字段名
USE auth;
CREATE TABLE users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT ``,PRIMARY KEY (user_name));
解释：
因为表格中不能拥有同名的用户，因此可将user_name 作为主键
字段定义部分的 null：表示不能为空 default：用于设置默认的密码字串，primary：用于设置主键字段名

3：删除一个数据表
DROP TABLE 库名.表名 //若只指定表名为参数，则需先通过USE语句切换到目标库，
案例：
删除auth库中的users表
DROP TABLE https://www.360docs.net/doc/874794859.html,ers;
4：删除一个数据库
DROP DATABASE 语句：用于删除指定的库，需要指定库名作为参数
案例：
删除名为auth的库
DROP DATABASE auth;

管理表中的数据记录
1：插入数据记录
INSERT INTO //用于向表中插入新的数据记录。
格式：
INSERT INTO 表名（字段1，字段2,..............）VALUES(字段1的值,字段2的值,..............)
案例：
向auth库中的user表插入一条记录，用户zhangsan 对应的密码为 123456
注意:VALUES部分的值应与前面指定的各字段逐一对应
use auth;
INSERT INTO user(user_name,user_passwd) VALUES('zhangsan',PASSWORD('123456')) //密码123456使用password（）函数加密

注意：
在插入新的数据记录时，如果这条记录完整包括表中所有字段的值，则插入语句中指定字段的部分可以省略。
案例：
向auth库中的user表插入一条记录：用户lisi，对应的密码为123456
use auth
INSERT INTO user VALUES('lisi',PASSWORD('12345678'));
2：查询数据记录
SELECT 字段名1，字段名2,....FROM 表名 WHERE 条件表达式 //查找并输出的字段 位置 查找条件
表示所有字段时可以使用通配符 *
若要列出所有数据记录 可以省略WHERE条件子句
案例1：
查看auth库中users表内的所有数据记录，其中密码字串已使用"PASSWORD()" 函数加密，因此不会直接显示出实际的密码内容
select * from https://www.360docs.net/doc/874794859.html,ers; //表示所有字段时可以使用通配符 * 若要列出所有数据记录 可以省略WHERE条件子句
案例2：
查找users表中用户名为张三的记录，输出其中用户名、密码字段的信息，
SELECT user_name,user_passwd FROM https://www.360docs.net/doc/874794859.html,ers where user_name='zhangsan'; //查找并输出的字段 位置 查找条件
解释
在auth库的users表中 搜索 user_name user_passwd 这两个字段中 user_name中出现zhangsan的记录

3：修改数据记录
UPDATE //用于修改、更新表中的数据记录
UPDATE 表名 SET 字段名1=字段值1 WHERE 条件表达式
案例1
修改users表中用户名为lisi的记录，将密码字串设为空值，验证记录内容可以发现lisi用户的密码串值已变成空白
UPDATE https://www.360docs.net/doc/874794859.html,ers SET user_passwd=PASSWORD('') WHERE user_name='lisi';
在MySQL数据库服务器中，用于访问数据库的各种用户信息都保存在mysql库的user表中，熟练的管理员可以直接修改其中的数据记录。
案例2：
将数据库用户root的密码设为123456，当再次使用mysql -u root -p 访问MySQL数据库服务器时，必须使用此密码进行验证
UPDATE https://www.360docs.net/doc/874794859.html,er SET password=PASSWORD('123456') WHERE user='root'
FLUSH PRIVILEGES //刷新用户授权信息
若是在linux命令行环境中执行，还可以使用mysqladmin工具来设置密码，
mysqladmin -u root -p password '123456'

4：删除数据记录
DELETE //用于删除表中指定的数据记录，
格式
DELETE FROM 表名 WHERE 条件表达式
案例1：
删除users表用户名为lisi的数据记录，验证记录内容可以发现lisi用户的数据记录已经消失
DELETE FROM https://www.360docs.net/doc/874794859.html,ers WHERE user_name='lisi'
SELECT * FROM https://www.360docs.net/doc/874794859.html,ers ;
注意：
在MySQL数据库服务器中，默认添加了从本机访问数据库的空用户（user、password均为空），基于数据库安全考虑，应该删除这些用户，
案例：
列出user字段为空的用户记录，并用DELETE语句进行空用户记录的删除
SELECT user,host

,password FROM https://www.360docs.net/doc/874794859.html,er WHERE user=''
DELETE FROM https://www.360docs.net/doc/874794859.html,er WHERE user='';


维护MySQL数据库
维护工作内容主要包括：用户权限的设置、数据库的备份与恢复。
数据库的用户授权
1：授予权限
GRANT //专门用来设置数据库用户的访问权限，当指定的用户名不存在时，GRANT语句将会创建新的用户，否则GRANT语句用于修改用户信息
格式：
GRANT 权限列表 ON 库名.表名 TO 用户名@来源地址 [IDENTIFIED BY '密码']

权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，例如select，insert，update 使用all表示所有权限

库名.表名：用于指定授权操作的库和表的名称，其中可以使用通配符*，例如使用"auth.*" 表示授权操作的对象为auth库中的所有表

用户名@来源地址：用于指定用户名称和允许访问的客户机地址，即谁能连接，能从哪里连接，来源地址可以是域名、ip地址、还可以使用、
%通配符，表示某个区域或网段的所有地址。例如 %https://www.360docs.net/doc/874794859.html, 192.168.1.%

案例：添加一个名为xiaoqi的数据库用户，并允许其从本机访问，对auth库中的所有表具有查询权限，验证密码设为123456
注意：
使用GRANT语句授权的用户记录，会保存到mysql库的user、db、host、tables_priv等相关表中，无需刷新即可生效
GRANT select ON auth.* TO 'xiaoqi''@''localhost' INENTIFIED BY '123456';
验证：
mysql -u xiaoqi -p
SELECT * FROM https://www.360docs.net/doc/874794859.html,ers; //验证授权的访问操作
SELECT * FROM https://www.360docs.net/doc/874794859.html,er; //验证非授权的访问操作
在企业服务器的应用中，数据库与网站服务器有时候是相互独立的，因此在MySQL服务器中，应根据实际情况创建新的用户授权，允许授权用户从网站服务器访问
数据库，
通常的做法是，创建一个或几个网站专用的库，并授予所有权限，限制访问的来源ip地址，
案例：
创建bdqn库，并授权从ip地址为192.168.4.19的主机连接，用户名为dbuser 密码为pwd@123，允许在bdqn库中执行所有操作
CREATE DATABASE bdqn;
GRANT all ON bdqn.* TO 'dbuser''@''192.168.4.19' IDENTIFIED BY 'pwd@123';

2：查看权限
SHOW GRANTS //查看数据库用户的授权信息，通过FOR子句可指定查看的用户对象（必须与授权时使用的对象名称一致）
show grants
格式：
SHOW GRANTS FOR 用户名@来源地址
案例
查看用户dbuser从主机192.168.4.19访问数据库时的授权信息，其中USAGE权限对应的授权记录中包含了用户的连接密码字串
SHOW GRANTS FOR 'dbuser''@''192.168.4.19'

3：撤销权限
REVOKE //撤销指定用户的数据库权限
特点：撤销权限后的用户仍然可以连接到MySQL服务器，

但将被禁止执行对应的数据库操作
格式：
REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址
案例：
撤销用户xiaoqi从本机访问数据库auth的所有权限
REVOKE all ON auth.* FROM 'xiaoqi''@''localhost';
SHOW GRANTS FOR 'xiaoqi''@''localhost'; //确认已撤销对auth库的权限

数据库的备份与恢复
MySQL数据库的备份可以采用多种方式，例如：直接打包数据库文件夹/var/local/mysql/var/，或者使用专用的导出工具，MySQL自带的倾倒工具mysqldump
1：备份数据库
通过mysqldump命令可以将指定的库、表或全部的库导出为SQL脚本，便于该命令在不同版本的MySQL服务器上使用
注意：
当需要升级MySQL服务器时，可以先用mysqldump命令将原有库信息导出，然后直接在升级后的MySQL服务器中导入即可
1：执行导出操作
mysqldump 常用的选项包括 -u：指定数据库用户名 -p：指定密码
格式1：导出指定库中的部分表
mysqldump [选项] 库名 [表名1] [表名2] [表名3]..........> /备份路径/备份文件名
格式2：导出一个或多个完整的库（包括其中所有的表）
mysqldump [选项] --databases 库名1 [库名2].........> /备份路径/备份文件名
格式3：备份MySQL服务器中所有的库
mysqldump [选项] --all-databases > /备份路径/备份文件名
注意：
需要备份整个MySQL服务器中的所有库，使用格式3，当导出的数据量较大时，可以添加 --opt 选项以优化执行速度。
案例：
创建备份文件all-data.sql 其中包括MySQL服务器中的所有库

mysqldump -u root -p --opt --all-databases > all-data.sql
案例：
将mysql库中的user表导出为mysql-user.sql文件，将整个auth库导出为auth.sql文件，所有操作均以root用户的身份进行验证
mysqldump -u root -p mysql user > mysql-user.sql
mysqldump -u root -p --database auth > auth.sql
输入密码
2：查看备份文件内容
通过mysqldump工具导出的SQL脚本是文本文件，其中/*...........*/部分或以--开头的行表示注释信息，使用grep、less、cat等文本工具可以查看脚本
内容
案例：
过滤出auth.sql脚本中的数据库操作语句
grep -v "^--" auth.sql | grep -v "^/" | grep -v "^$"

2：恢复数据库
mysqldump 命令导出的SQL备份脚本，通过mysql命令对其进行导入操作。
命令格式：
mysql　［选项］　［库名］　［表明］　＜　／备份文件／备份文件名
注意：
当备份文件中只包含表的备份，而不包括创建库的语句时，则执行导入操作时必须指定库名，且目标库必须存在

案例：
从备份文件mysql-user.sql中将表导入到test库
mysql -u root -p test < mys

ql-user.sql
输入密码
mysql -u root -p
输入密码
USE test //验证导入结果
SHOW TABLES;
若备份文件中已经包含完整的库信息，则执行导入操作时无需指定库名，
从备份文件all-data.sql恢复其中的所有库
cd /user/local/mysql/var/
mv bdqn auth /tmp/ //转移部分数据库，模拟故障
ls -ld mysql bdqn auth //确认转移
mysql -u root -p < ~/all-data.sql //执行导入恢复操作 ~ 宿主目录
输入密码
ls -ld bdqn auth //确认恢复后的结果