MySQL 高级管理

深入理解权限系统

• 执行GRANT 语句的时候，影响一个名为mysql的特殊数据库
• 权限信息保存在这个数据库的6个表中。(授权表)
• user、 host、 db 、 tables_priv、colums_priv 、 procs_priv
• 范围字段+权限字段；
• user 和 host 用来确定一个用户是否可以连接MySQL 服务器，以及该用户是否具有任何管理员权限
• db 和 host 确认可以访问哪些数据库
• tables_priv 确定用户可以使用数据库中哪些表
• columns_priv 确定用户可以访问表中的那些列
• procs_priv 确定用户可以执行哪些过程。

user 表

• 列出的权限是全局的

db 表和 host 表

• db表确定哪些用户可以访问哪些主机和哪些数据库
• host 表是db表的补充。如果一个用户从多个主机连接到数据库，在db表中将不会列出该用户的主机名。而用户将在host表中获得一组记录，每个记录指定了每个用户-主机对的权限。

tables_priv表、columns_priv 表 和 procs_priv表

• 分别用来储存表级别的权限，列级别的权限，以及针对存储过程的权限。

访问控制：MySQL 如何使用grant 表

更新权限：修改什么时候生效

• 手动更新grant 表时，MySQL 将不会发现他们已经被修改了
• 我们必须向服务器指出已经对权限进行了修改

flush privileges;

• 或者在操作系统中运行

mysqladmin flush-privileges
// or
mysqladmin reload

提高MySQL数据库的安全性

从操作系统角度保护MySQL

密码

用户权限

• PROCESS 权限用来观察其他用户正在做什么，输入了什么，包括他们输入的密码
• FILE 权限用来读写操作系统中的文件。

获取更多关于数据库的信息

SHOW

SHOW tables FROM books;
// 查看books 数据库中的表

SHOW columns FROM orders FROM books;
SHOW columns FROM books.orders;
// 查看books 数据库中orders 表的列

SHOW grants FOR bookorama;

DESCRIBE

SHOW table [column];

• 若未指定column则给出所有列；

EXPLAIN

EXPLAIN table;
// 类似于 DESCRIBE table;

---

EXPLAIN 
SELECT customers.name
FROM customers,orders, order_item, books
WHERE customers.customerid = orders.customerid 
AND orders.orderid = order_items.orderid
AND order_items.isbn = books.isbn
AND books.title LIKE '%Java%';

• 查看MySQL 如何解释执行一个SELECT 查询。
• 
  
• id 给出了该行所引用的查询SELECT 语句的ID号
• SELECT_TYPE 解释所使用的的查询类型
  
• table 列出了用来完成查询所需要的表
• type 解释了表在查询关联中使如何使用的
  
• rows（粗略的）列出了执行链接所必须扫描的每个表的行数
• possible_keys列出了MySQL可能用来链接表的关键字
• keys ，或者是MySQL 实际使用的表中的关键字，或者是NULL,如果没有使用关键字的话
• key_len 给出所用关键字的长度，用来判别是否只使用了关键字的部分
• extra 显示用来从表中选择列而必须与关键字一起使用的列
  

数据库优化

权限

• 在查询执行之前通过权限查询系统检查该查询的过程，该过程越简单，查询速度越快

表的优化

• 随着表的使用时间，更新和删除操作的发生，数据会变得支离破碎，也会增加在表中的查询所花的时间，用如下语句修复她

OPTIMIZE TABLE tablename;

或者在命令行中输入

myisamchk -r table

使用索引

• 简化索引，不要创建查询不使用的索引

使用默认值

备份MySQL数据库

1. 在复制数据文件时使用LOCK TABLES

LOCK TABLES table lock_type [, table lock_type ...]

• 锁定类型可以使READ 或 WRITE
• 对于备份，只需要READ锁
• 在备份前，必须执行 FLUSH TABLES ，确保对索引所做的修改将写入到磁盘内

2. 在命令行中使用 mysql_dump

mysqladmin --opt --all-databases > all.sql

• 重新构建一个SQL数据库所需的内容都导出到一个名为 all.sql 的文件中

3. 用mysqlhotcopy 脚本

mysqlhotcopy database /path/for/backup

恢复MySQL 数据库

实现复制

• 主服务器写查询，从服务器读查询

设置主服务器

• 为从服务器创建一个用来链接主服务器的用户，这个权限级别被称为复制从服务器；
• 在主服务器上创建一个用户

GRANT replication slave
ON *.*
TO 'rep_slave@%' identified BY 'password'

执行初始的数据传输

• 简单的方法，设置从服务器，运行LOAD DATA FROM MASTER
• 问题在传输数据过程中，它将锁定主服务器上的表，而且需要一定的传输时间

2. 快照

FLUSH tables WITH READ LOCK;
// 进行锁定是因为必须获得快照记录服务器在二进制日志中的位置
SHOW master status;
// 可以看到File 属性和 Position 的值
// 然后
UNLOCK tables;

设置一个/ 多个从服务器

1. 如果获得快照

CHANGE master TO
master-host = 'server',
master-user = 'user',
master-password = 'password',
master-log-file = 'logfile',
master-log-pos = 'logpos';
START slave;

• server 是主服务器名称
• user 和 password 是创建的用户
• logfile 和 logpos 是主服务器运行的SHOW MASTER STATUS 的语句输出

2. 没有快照

LOAD DATA FROM master;