8.5. mysql 高级管理

4

4

持之以恒，方得始终！

权限

grant 创建用户，授权信息，实际更改的是 mysql库下的几张表

user
    可以用 describe user 查看结构
    验证用户是否能登录
    该表是适合所有库，所有表的，里面的权限一般赋予管理员。

host，db
    普通用户的权限一般在 db表中， 访问哪些主机，哪些库。
    host 是 db的补充

tabels_priv
    表级别权限

columns_priv
    列级别权限

procs_priv
    存储过程权限

mysql 使用 这些 授权表的流程：

1. 连接验证

   mysql 基于 user 表， 检查用户是否有连接权限。
   这是基于 用户名，主机名，密码 进行的验证
   主机名可以用 通配符 % ，
   %  所有主机
   %.tangledweb.com  
   如果密码是空，则不要密码

2. 请求验证

    建立连接后，对于所发送的每一个请求，mysql 都会检查是否有执行该请求的权限
    先检查全局权限 user，没有的话，
    再 db， host，没有的话，
    再 tabels_priv
    columns_priv

手动修改这些表，还需要告诉服务，更新了用户及其权限

管理员登录后
    flush privileges;
不登录下
    mysqladmin flush-privileges
或
    mysqladmin reload

提高 MySQL 的安全

1. 创建一个专门用来运行 mysqld 的 MySQL 用户。
2. 保存数据的物理目录，只由 mysql用户有权限，也就是运行 mysqld进程的系统用户。
3. 将其放于防火墙之后，不要用默认 3306 端口。
4. 所有 MySQL用户都必须得有密码，并且定期修改。
5. 保存在脚本中的密码，要注意限制访问的用户，用于web项目中，最好是放在根目录之外。
6. 不要直接保存纯文本的密码，我们需要加密。
7. 不要授予任何用户不必要的权限，我们可以查看 那些授权表来确认，请不要将 process，file，shutdown，reload等权限授予非管理员用户，process可以看其它用户正在做什么，输入了什么，包括他们输入的密码，file 可以来读写系统中的文件。
8. grant权限授予也要小心，因为它允许用户将他们的权限分享给其他人。
9. 限制用户访问 MySQL 的来源主机。
10. 避免在主机名中用通配符。
11. 使用 ip 作为主机名。
12. 防止非管理员用户访问 mysqladmin。
13. 为web项目单独创建用户，只给最少权限。
14. 外部数据入库，严格检查，过滤。

show，describe，explain

show

show tables;
show databases;
show tables from books; // 指定库
show columns from orders from books; // 查看 books库下orders表的所有字段
show columns from books.orders;

// 查看用户所拥有的权限
show grants for jerry;

还有一些其它用法，可以查看MySQL手册。

describe

describe table [column]

字段名称还可以用通配符。

explain

explain table; // 类似 describe

最重要的是，可以分析 select 查询的过程，用来分析慢 sql。

explain
select customers.name 
from customers, orders, order_books, books
where customers.customerid = orders.customerid
and orders.orderid = order_books.orderid
and order_books.isbn = books.isbn
and books.title like "%java%";

取出其中一行的结果

id : 1
select_type: simple
table:orders
type : all
possible_keys:primary
key : null
key_len:null
ref:null
rows:4
extra:

select_type 查询类型，具体看手册。
table 完成查询所需要的表，一行信息，也就是这个表，在查询中如何使用的。
type 执行查询，需要读入这个表的多少行。具体看手册。
rows：粗略的计算扫描了这个表多少行。显然，它的值越小越好。
possible_keys 可能使用的索引。
key 实际用的索引
key_len 索引长度

使用 myisamchk , analyze 检测

myisamchk --analyze pathtomysqldatabase/*.MYI

myisamchk --analyze pathtomysqldatadirectory/*/*.MYI

mysql > analyze table customers, orders, order_books, books;

如果 explain 结果中的 possible_keys 包含一些 null 值，可能需要对表添加一个索引来提高查询性能。

如果在 where 子句中使用的列适合作为索引，可以用 alter table 来为其创建一个新索引

alter table 表名 add index 字段;

MySQL 性能优化

1. 字段类型范围越小越好。
2. null 值越少越好。
3. 主键越短越好。
4. 尽量用固定长度的 char。
5. 简化权限，因为每次执行查询，都需要先检查权限。
6. 一个表，如果用了一段时间，随着更新，删除频繁，数据会变得支离破碎，增加查询耗时，我们可以修复它：

   optimize table 表名;
   或
   myisamchk -r table;
   或
   myisamchk --sort-index --sort-records=1 pathtomysqldatadirectory/*/*.MYI

7. 在需要提高速度的地方使用索引。
8. 不要创建查询不使用的索引，前面的 explain 就可以看到索引是否被使用到。
9. 字段尽可能的给默认值。插入时，是默认值，就不给，简化insert语句。

备份

备份前，可以执行一下 flush tables，确保对索引做的修改写入磁盘。

1. mysqldump

   mysqldump --opt --all-database > all.sql

2. mysqlhotcopy

   mysqlhotcopy database /path/for/backup

3. 维护一个数据库的副本。

恢复

1. 如果有一个破坏了的表，可以用它来修复

   myisamchk -r ....

2. mysqldump备份后，sql文件从新执行一遍。

复制

就是主从服务。

1. 启用二进制日志记录，设置服务id => my.ini

   [mysqld]
   log-bin
   server-id=1

   从服务器也都要设置，id不要相同
2. 为主从复制，在主服务上创建一个用户

   grant replication slave
   on *.*
   to 'rep_slave'@'%' identified by 'password';

   初始成功后，后续可以撤销不必要的权限。
3. 执行初始的数据传输
4. 设置多个从服务器

如有任何侵权行为，请通知我删除，谢谢大家！
个人邮箱：865460609@qq.com