MySQL数据库的日常维护

却尘

1.权限管理
mysql权限管理基于主机名、用户名和数据库表，可以根据不同的主机名、用户名和数据库表分配不同的权限。
mysql的权限信息存储在数据库的user、db、host、tables_priv、cloumns_priv和proc_priv表中，通常使用grant和revoke进行权限分分配。

分配账号：
(1)主机192.168.61.147需要远程访问mysql服务器的account.users表，权限为select和update，可以使用如下命令：
grant select,update ON account.users TO user1@192.168.61.147 IDENTIFIED by 'pass123456';  
flush privileges;

创建成功后，查看数据库表的变化
select * from user where user = 'user1'\G
select * from db where user = 'user1'\G  
mysql权限验证按照 user -> db -> tables_priv -> columns_priv检查的顺序，如果user表中的对应权限为y，则不再检查后面表中的权限。

（2）查看或修改账号权限
如需查看当前用户的权限，可使用SHOW GRANTS FOR命令，如：
show grants for user1@192.168.61.147 \G  
mysql用户登录成功后权限加载到内存中，此时如果在另一个会话中更改了该账户的权限并不会影响之前会话中用户的权限，如需要使用最新的权限，需要重新登录。

（3）回收账户权限
操作回收的账户需要revoke权限。
revoke insert on *.* from test3@'%';
revoke all on *.* from test3@'%';
账户所有权限回收后用户仍可以连接mysql服务器，如需彻底删除用户，使用drop命令。  
drop user test3@'%';

2.日志管理
可通过日志追踪mysql服务器的运行状态，及时发现服务运行过程中的各种问题。
（1）二进制日志
记录了所有的DDL DML但不包括查询语句。
启用二进制日志：通过在配置文件中添加“--log-bin=[file-name]”选项指定二进制文件存放位置。
查看二进制日志：通过mysql提供的工具mysqlbinlog。如：
  mysqlbinlog mysql-bin.00005|cat -n
删除指定序号之前的二进制日志：
  PURGE BINARY LOGS TO 'mysql-bin.010';
删除指定时间之前的二进制日志
  PURGE BINARY LOGS BEFORE '2-18-03-01 22:13:32';
配置文件中指定二进制文件的保留天数：expire_log_days=# .
sql命令行设定二进制文件的保留天数：set global expire_log_days = 7;
  (2)操作错误日志
记录了mysql启动、运行及停止过程中的相关异常信息，在mysql故障定位方面有重要的作用。
配置文件中设定：
  错误日志存放位置：--log-error=[file-name]，默认在mysql的datadir目录下。
（3）访问日志
记录了所有客户端发起的连接、查询和更新语句，由于记录所有操作，建议在数据库繁忙时关闭此设置。
配置文件中设定：
配置文件设置：--log=[file-name] 指定访问日志的位置,默认为主机名作为文件名，存放在数据目录中
可在登录mysql后通过设定变量
  启用此日志：set global general_log=on;
  查询日志位置：show variables like '%general_log%';
  关闭日志：set global general_log=off;
（4）慢查询日志
记录了执行时间超过参数long_query_time所设定的sql语句日志，对sql审核和开发者发现性能问题具有重要意义。
配置文件中设置：slow_query_log 用来指定是否开启慢查询。如果没有指定文件名，默认hostname-slow.log，并放在数据目录中。
log-slow-queries指定日志文件的路径

3.备份与恢复
（1）使用mysqldump，可导出成sql语句或文本文件。适用于MyISAM和InnoDB存储引擎。但由于备份时数据库正在读写，备份出的文件可能损害无法使用，不推荐。
  导出整个数据库   mysqldump -u root test>test.sql
  导出一个表     mysql -u root test TBL_2 > test.TBL_2.sql
  只导出表结构     mysql -u root -d --add-drop-table test>test.sql       //-d 没有数据， --add-drop-table 在create前，先drop table
  恢复数据库     mysql -u root test<test.sql
  恢复数据库的另一种方法      mysql -u root test 登录后，source /root/test.sql
  (2) 使用Xtrabackup在线备份 http://www.percona.com/downlaod

4.Mysql复制
  主从复制好处：健壮、负载均衡、数据备份、
  复制搭建过程：待补充