8.5. mysql 高级管理

4

4

持之以恒，方得始终！

权限#

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

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

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

tabels_priv
    表级别权限

columns_priv
    列级别权限

procs_priv
    存储过程权限Copy

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

1. 连接验证

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

2. 请求验证

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

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

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

提高 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;Copy

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

describe#

describe table [column]Copy

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

explain#

explain table; // 类似 describeCopy

最重要的是，可以分析 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%";Copy

取出其中一行的结果

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

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;Copy

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

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

alter table 表名 add index 字段;Copy

MySQL 性能优化#

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

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

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

备份#

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

1. mysqldump

   mysqldump --opt --all-database > all.sqlCopy

2. mysqlhotcopy

   mysqlhotcopy database /path/for/backupCopy

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

恢复#

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

   myisamchk -r ....Copy

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

复制#

就是主从服务。

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

   [mysqld]
   log-bin
   server-id=1Copy

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

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

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

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