mysql日常维护

 1.mysql权限管理

1.1mysql账户权限信息被存储在mysql数据库的user 、db、host、tables_priv、column_priv和procs_priv表中，mysql启动时服务器将这些数据库表内容读入内存。推荐grant和revoke语句

例如：

mysql>grant select,update on account.users to user@ip identified by 'passwd';

mysql>flush privileges;

创建成功后查看mysql数据库表的变化

mysql>select * from user where user='user1'\G

mysql>select * from db where user='user1'\G

mysql>select * from tables_priv where user='user1'\G

mysql权限按照user 、db、tables_priv、column_priv检查顺序，如果对应表权限为Y，则不会检查后面的表

1.2.查看或修改账户权限

例如：

mysql>show grants for user1@ip \G

1.3 回收账户权限

mysql>revoke insert on *.* from test3@'%';

mysql>drop user test3@'%'

2.mysql日志管理

mysql服务支持的日志有二进制日志、错误日志、访问日志和满查询日志。

2.1 二进制日志（binlog）

记录所有DDL和DML操作，但不包括数据查询语句，通过mysqlbinlog查看 例如：mysqlbinlog mysql-bin.000005|cat -n

2.1.1 删除binlog，用purge binary logs 例如：

purge binary logs to 'mysql-bin.010';  //删除指定序号之前的二进制日志

purge binary logs before '2016-04-02 22:46:26'; //删除指定时间之前的二进制日志

2.1.2 指定参数设置二进制文件保留天数“expire_logs_days=#”

mysql>set global expire_logs_days=7; //自动清理7天之前的二进制日志文件

2.2 操作错误日志

通过设置“--logs-error=[file-name]” 指定错误日志存放位置，如没有设置，则错误日志默认位于mysql服务的datadir目录下

例如：cat /dataa/master/dbdata/CentOS.err

2.3 访问日志

记录了所有关于客户端发起的链接，查询和更新语句，由于记录了所有的操作，在相对繁忙的系统中建议关闭

在配置文件“--log=[file-name]” 指定访问日志的位置，另外一种方法可以在登陆mysql实例后通过变量启用此日志，如

mysql>set global general_log=on;

mysql>show variables like '%general_log%';  //查询日志位置

2.4 慢查询日志

记录了时间超过参数long_query_time（单位是秒）所设定值的SQL语句日志，对于审核和开发者发现性能问题及时优化有重要意义。

如需启用该日志可以在配置文件中设置 “slow_query_log" ，没有指定文件名，则默认hostname-slow.log作为文件名，并存放在数据目录中

log-slow-queries=/usr/local/mysql....#定义慢查询日志路径

慢查询日志分析工具 mysqldumpslow 

[-s] 排序参数，可选al:平均锁定时间 ar:平均返回记录数 at：平均查询时间；

[-t] 只显示指定的行数；

3. mysql备份与恢复

3.1 备份方式可以通过直接备份数据文件或使用mysqldump命令将数据库数据导出到文本文件，直接备份数据库文件适用于MyISAM和InnoDB存储引擎，由于备份时数据库表正在读写，备份出的文件可能损坏无法使用，不推荐直接使用此方法。

mysqldump -u root test>test.sql //导出整个数据库

mysqldump -u root test TBL_2 >test.TBL_2.sql //导出一个表

mysqldump -u root -d --add-drop-table test>test.sql //只导出数据库表结构 -d没有数据 --add-drop-table 在每个create语句前增加一个drop table

mysql -uroot test<test.sql //恢复数据方法1

mysql -uroot -p

mysql>source /root/test.sql //恢复数据方法2

3.2 另外一种可以实时备份的开源工具为xtrabackup

http://www.percona.com/downloads/提供下载

4.mysql复制

复制功能可以经济高效地提高应用程序的性能、扩展力和高可用性。既支持简单的主从拓扑，也可以 实现复杂、极具可伸缩性的链式集群。

使用mysql复制时，所有对复制表的更新必须在主服务器上进行，否则可能引起主服务器上的表进行的更新与对从服务器上的表所进行的更新产生冲突。

好处：（1）主从数据同步，主服务器故障时，从服务器可作为主服务器接管服务。

（2）负载均衡。实现读写分离，主服务器记录更新、删除、插入等操作；从服务器只查询请求；

（3）数据备份。从服务器可设置在异地，增加容灾的健壮性，为避免传输过慢，可设置参数slave_compressed_protocol 启用binlog压缩传输

mysql使用3个线程来执行复制功能，1个在主服务器上，2个在从服务器上。当执行START SLAVE时，主服务器创建一线程负责发送二进制日志。从服务器创建一个I/O线程，负责读取主服务器上的二进制日志，然后将该数据保存到从服务器数据目录中的中继日志文件中。从服务器的SQL线程负责读取中继日志并重做日志中包含的更新，从而达到主从数据库数据的一致性。#在主从服务器上输入命令查询状态：mysql>show processlist \G 其中time列的值可以显示从服务器比主服务器滞后多长时间。