数据库学习-MySQL进阶篇（一）

1. MySQL管理简介

(1) MySQL组件

(2) MySQL的常规管理

(3) 访问控制与安全性

• 文件系统安全性：保证账户不能访问数据目录。如何设置用于运行MySQL服务器的用户账户，如何设置数据目录使得它隶属该用户，以及如何使用该用户的权限来启动运行服务器。
• MySQL服务器安全性：MySQL安全系统工作原理，便于在创建用户账户时，能给与合适的服务器访问权限。

(4) 数据库维护、备份、恢复

• 预防性维护
• 数据库备份：mysqldump生成的备份文件会更有用。
• 崩溃恢复：如何利用备份文件恢复以及如何利用二进制日志恢复最近一次备份后发生的更改。
• 数据库迁移
• 数据库复制：主从服务器设置。

2. 数据目录

数据目录存储所有的数据库以及提供服务器运作信息的状态文件和日志。

(1) 数据目录位置

查看数据库目录的位置

• 未连接上服务器
  查看服务器启动时读取的选项文件。如Unix系统上查看/etc/my.cnf文件中的
  
  或者调用如下指令找到文件选项文件的位置

mysqld --verbose --help 

• 能连接上服务器时

SHOW VARIABLES LIKE 'datadir'
#或者
mysqladmain variables -- 在我的数据库中测试不通过，希望有大佬看到解答つ﹏⊂

(2) 数据目录结构

树形结构，直接利用Unix或Windows的文件系统层次结构实现的；
一个数据库在数据目录中对应一个数据库目录，数据库中的表、视图、触发器对应于数据库目录中的文件

• 服务器提供的服务器访问方式
  服务器通过监听各种类型的网络连接，对外提供一个通向数据目录的网络接口。想访问数据的客户端建立一个与服务器的连接，然后通过SQL语句传递请求。
• 数据库在文件系统里的表示
  名为mydb在文件系统中对应的数据库目录为datadir/mydb。注意：删除数据库目录无法安全的删除数据库表中的InnoDB表；如果使用数据库目录中有其它不属于数据库目录的文件，在使用DROP DATABASE语句会返回错误。
• 表在文件系统中的表示
  .frm文件包含表结构的描述
  不同存储引擎不同的数据保存和索引信息文件

存储引擎名	结构
InnoDB	系统表空间，由一个或多个大文件构成，形成了逻辑上连续不断的存储空间；除此之外，数据字典(包含了与每个表相关的信息)也保存在系统表空间中。也可以通过配置将每个表放在一个表空间中，这时每个表会包含一个数据和索引的.ibd文件。
MyISAM	每个表都有三个文件，文件名等同于表名，一个.frm文件，一个.MYD文件，存储数据内容，一个.MYI文件，存储索引信息。
MEMORY	只有.frm文件。

• 视图和触发器在文件系统中的表示
  每个视图包含一个同名的.frm文件，保存视图的定义和其它相关的属性。触发器存储在.TRG文件中，其名与所属表的名相同，包含触发器的定义和其他的相关属性；每个触发器还有一个根据触发器名称来命名的TRN文件，其中包含与这个触发器相关联的那个表的表名。
• SQL语句与表文件操作的对应关系

SQL语句	表文件操作
SHOW TABLE FROM db_name	输出结果与db_name数据目录里.frm文件的基本名列表相同
CREATE TABLE	创建一个.frm文件，InnoDB会创建一个数据字典条目，MyISAM会创建.MYD数据文件和.MYI索引文件
ALTER TABLE	会对.frm文件进行重新编码，同时服务器还会更改表的内容(执行CREATE INDEX及DROP INDEX也会)；如果改变了存储引擎，表的内容也会转换到新的存储引擎的格式
DROP TABLE	删除表示表的文件

• 影响表最大长度的因素
  操作系统对文件的最大长度有限制。InnoDB表空间可以由多个文件组成或表空间使用磁盘中的原始分区，可以与磁盘分区本身一样大。
  MySQL对于表的长度还有自己的内部限制。InnoDB系统表空间的最大长度为40亿页，每页16 KB；MyISAM表中.MYD及.MYI文件的最大长度默认为256TB。
• MySQL 状态文件和日志文件
  

(3) 迁移数据目录

• 迁移方法
  任何平台：服务器启动时指定数据目录位置：命令行使用–datadir=dir_name；选项文件中放入下面的内容：
  
  Unix：先运动要迁移的文件按或目录，然后在原来位置生成一个符号链接指向新位置。
  下面显示了不同的迁移实体适用的迁移方法：
  
  
• 迁移注意事项：迁移前备份；停止服务器再迁移；迁移前FLUSH TABLES，保证所有打开的表文件关闭。
• 迁移整个数据目录：迁移时停止服务器；迁移以后使用–datadir重启以明确指示新位置。
• 迁移单个数据库
  Unix系统：停止服务器，迁移数据库(复制到新位置，并删除原来位置的数据库)；数据目录中创建符号链接，让其具有原来数据库的名字并指向新数据库位置；重启服务器。下面的例子把bigdb数据库从/usr/local/mysql/data/迁移到/var/db
  
  windows上：停止服务器；迁移数据库(复制到新位置并删除原位置数据库)；在数据目录中创建一个文件充当符号链接，让服务器可找到迁移以后的数据库目录，该文件的扩展名为.sym，基本名为数据库名；保证重启服务器时启用了符号链接支持功能，默认支持，也可在命令行使用–symbolic-links显示启动或者在选项文件中放入以下内容：
  
• 迁移单个表
  需要满足以下条件才能迁移单个表：Unix系统，MyISAM表，操作系统有可工作的realpath()系统调用，如果有，如下面的结果：
  
  可把表的.MYD数据文件和.MYI索引文件迁移；在原来的数据库目录创建符号链接，分别指向新位置，.frm文件需要留在原来的数据库目录。
• 迁移InnoDB系统表空间：初次配置表空间，使用innodb_data_home_dir和innodb_data_file_path系统变量将表空间的各个组成文件罗列在某个选项文件中。
  迁移步骤：停止运行的服务器；迁移表空间文件；修改配置的选项文件，反映文件钱以后的新位置；重启服务器
• 迁移状态文件和日志文件：停止服务器，使用可指定文件新位置的选项–pid-file=xxx进行重启或者在某个选项文件中包含如下内容：
  

3. MySQL的基本管理

(1) 保护新安装的MySQL

安装新的MySQL后应该确保服务器只能被授权用户访问。因此要了解安装过程中会创建那些用户账户，为需要的账户设置密码，删除不需要的账户。
安装过程会在数据目录中生成两个数据库： 数据库mysql：包含了用于控制客户端对服务器进行访问的各种权限表。第一次安装后，其初始状态是任何人无需身份认证可连接服务器。数据库test：可用于测试。

• 为初始的MySQL账户创建密码
  mysql数据库的权限表中中设置以下两种账户：用户名为root的账户，超级用户，可做任何事情；用户名为空白的“匿名”用户，任何人都可连接服务器而不用显式地创建账户，权限较少，对于匿名用户，强烈建议删除，删除匿名用户指令如下：

DROP USER ''@'localhost';

已有的账户都在mysql数据库的user表中，该表里的每一行包含Host值，User值等。默认账户都没有密码。因此第一步是为这些用户创建密码。可以使用SET PASSWORD指令或者使用UPDATE语句直接修改user表，如下所示：

SET PASSWORD FOR 'root'@'localhost' =PASSWORD('123456');
UPDATE user SET Password=PASSWORD('123456') WHERE User='root';FLUSH PRIVILEGES;

• 为附加服务器设置密码
  在已安装MySQL服务器的计算机上安装新的MySQL(如不同版本)，这是新安装的服务器可能会加载旧的选项文件，导致不使用密码无法登录新服务器的root用户，可以使用–skip-password来实现：

mysql -u -root --skip-password

(2) 安排服务器的启动和关闭