Written by arstercz - 2014-05-27

MySQL管理事项规范

规范相对简单(后续待完善)，没有特别细致的描述利弊。本章事项包括，帐号，数据存储，复制设置，监控设置,日志信息，常用操作等。此要求尽量使用集中化，有规律的设置方式管理数据库。

1.帐号安全

帐号设置包括安全和访问两部分。安全要求数据库不能有空帐号、空密码帐户出现(新装实例包含root空密码,空帐户等)，如下：

mysql [localhost] {root} ((none)) > select user,host,password from mysql.user;   
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
| root | z10       |          |
| root | 127.0.0.1 |          |
|      | localhost |          |
|      | z10       |          |
+------+-----------+----------+

这是很严重的安全隐患，新装实例后，DBA需确保不含有空帐户，root密码需要设置，如下：

> use database_name;
> drop user ''@`localhost`;
> drop user ''@`z10`;

>set password for root@`localhost` = password('xxxxx');

不要忽略掉use database_name(应用连接的库名称)，保证数据更新不会被replication规则过滤掉，而影响权限的一致性。如果没有主从环境，还请继续保持use 的良好习惯。

应用连接帐号设置：首先保证用户名和应用有关联，不要用毫无干系的名字命名应用帐号。比如db_zh_app@.......,表示zh工程里的app子工程，帐号应用关系一目了然。MySQL用户由user和host两部分组成，前者决定名字，后者决定来源。帐号密码设置统一使用以下规范:

> use database_name;
> GRANT [privileges] ON database_name.* TO user@`....`;
> SELECT PASSWORD('string_pass');
> SET PASSWORD FOR user@`....` = PASSWORD('string_pass');
> flush privileges;

确保用户拥有足够但过够的权限，比如只读，就指给select,不要给super,alter,with grant等高级权限。牵扯到更新数据(包括帐号更改)的查询语句杜绝出现密码相关的信息(query查询语句会记录到/userhome/.mysql_history文件中) 。set password 语句会记录到.mysql_history文件中(注意该文件的权限)，更新到binary日志中采用md5处理后后的41字符长的密码串(不是明文)。设置帐号后记得flush权限。

帐号访问：怎么访问是一个相对灵活的主题，更多适用于测试或不太核心的业务.除了host来源，需要注意以下一点: 各个开发平台具有不同的host来源，即意味着多个网段需要有访问实例的权限，同时主机iptables也需要做相关的更改。这种需求即意味着一个帐户的更改可能会带起所有帐户的更改，管理起来很麻烦，且容易混乱。出现这种需求实例需要限制为统一来源，即保证每次只做一到两次修改就可以满足开发人员的需求，可以通过iptables转发特性，ssh转发,rinetd(注)来实现：

                   /  db1  
  user --> rinetd  -> db2
                   \  db3

  user   -- rinetd  公网
  rinetd -- db      内网
  db中新创建帐号host来源为内网地址。

注： http://www.boutell.com/rinetd/

2.数据

数据包括文件系统和存储两方面：文件系统：采用XFS文件系统(XFS系统对大文件支持相对较好)，数据库文件存储在XFS文件系统中。存储: 数据存储分区和根分区相互独立，不要放到同一个分区，MySQL errlog和数据存储独立。

3.复制

不要给过多的权限, slave连接的master为实IP地址，VIP为对外服务的地址。 GRANT REPLICATION SLAVE ON . TO ‘replica’@’xxx.xx.xx.xx’

[用户由user@host组成]

   The REPLICATION CLIENT privilege enables the use of SHOW MASTER STATUS and SHOW SLAVE STATUS.
   The REPLICATION SLAVE privilege should be granted to accounts that are used by slave servers to connect to the current server as their master. Without this privilege, the slave cannot request updates that have been made to databases on the master server.

4.监控

明确监控对象，监控所需要的权限，不要给过高的权限: nagios 只监控Slave信息，GRANT REPLICATION CLIENT ON . TO ‘nagios’@’xxx.xx.xx.xx’; cacti 监控MySQL引擎，使用状态，全局变量参数等，GRANT SELECT, PROCESS, SUPER ON . TO ‘cactidb’@’xxx.xx.xx.xx’;

5.日志说明

日志包括error log, binary log, relay log,general log.分别说明：

error log

mysqld_safe选项开启syslog，将error log重定向到/var/log/message,以方面监控告警；同时和数据分区分开存放，数据分区崩溃时还可以查看error 信息。

binary log

配置文件没有指定binlog的输出路径，默认和DATADIR同路径，序列化命名的前缀以log-bin选项为准。

relay log

slave接收master的更新后由io_thread线程产生relay log, 路径存放同DATADIR

general log

线上不要开启，改选项会记录所有的query语句，磁盘使用非常迅速，通常在排查故障或希望详细了解执行sql的时候开启。路径信息由general_log_file选项指定。

6.常用操作

在线更改表结构信息：

http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html 该工具适用以下场景：

待修改表有频繁的更新或select查询手工alter table 会获得一个表级锁，阻塞后续的更新或select查询操作，阻塞的越多，可能会吃满process资源，出现
```
ERROR: Can't create a new thread。
```
主机内存不足手工alter table 的操作过程为给原表一个表锁，不让更新，然后创建临时表(新表结构)，再从原表拷数据到临时表。如果原表很大，临时表的空间又很有限，从原表拷数据到临时表过程就会在磁盘文件系统上完成，耗时又耗力，可能会影响业务的访问。

3.在线做一些优化分析表操作，比如optimize table, analyze table操作。这两项操作都需要加表级锁来完成，影响同1。

在线修改原理

| create new tale |  --> | alter new table | --> | create trigger(update,insert,delete) | --> | copy rows from old table to new table|  --> | swap tables (short time table lock) | --> | drop old table | --> | drop trigger |

详见：http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

在线清理或归档表数据 http://www.percona.com/doc/percona-toolkit/2.2/pt-archiver.html 归档：可以将选定数据存放到其它实例的相同的表中。清理：delete删除选定的数据归档和清理操作都不会释放表空间，清理完大部分数据后需要释放表空间，可通过optimize table,但耗时较长，会引起表锁，也

可以通过pt-online-schema-change工具通过修改表结构 alter table xxxx engine = ‘old engine’来释放。

适用以下场景:

待清理表有频繁的更新或select查询。 delete from <table> where …. 满足where条件的记录过多的话，也会引起表级锁，后续的更新，select会阻塞。
表太大，delete操作很耗时。表过大，过滤出where条件的记录也需要过长的时间，这个过程会锁表。

在线清理原理

| select rows | --> | split rows into many small fragement | --> | delete small fragement |

如：http://www.percona.com/doc/percona-toolkit/2.2/pt-archiver.html

统计分析查询

http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html 线上用的最多的应该就是pt-query-digest工具，可以对查询语句做统计分析操作，节省很多的人工成本。可以分析slow query log,general log, tcp packets(检测线上读写压力)，memcached(2.2版本开始移除)等

#pt-query-digest slow-query.log > report.log     #分析慢查询

#tcpdump -s 65535 -x -nn -q -tttt -c 500000 -i any port 3301 > mysql.3301.txt               #tcp抓包
#pt-query-digest --type tcpdump --watch-server='172.30.0.39:3301' mysql.3301.txt >3301.log  #分析包

升级percona版本 http://www.percona.com/doc/percona-toolkit/2.2/pt-upgrade.html 不管是大版本还是小版本升级，总会出现或多或少的变化，比如低版本的变量在高版本中换了名字等，原来的配置就需要做相应调整，pt-upgrade工具可以完成在不同版本之间相同query的结果集校验,节省了大量的人力成本。

导出用户权限表

http://www.percona.com/doc/percona-toolkit/2.2/pt-show-grants.html 导出用户信息，grants信息等，在做扩展(批量更新、增加用户)的时候，该功能很有用。

索引使用情况

http://www.percona.com/doc/percona-toolkit/2.2/pt-index-usage.html 该工具用来收集query查询表时的索引使用情况,一些sql审核类的应用蛮有用的，可以考虑集成到应用里。

7. mysql utilities介绍

oracle官方出来的一套工具集，用python写的，除了mysqlfrm可以通过frm文件恢复表结构外，其它工具个人觉得和percona toolkit重合度很大，且没有percona toolkit易用。

# rpm -ql mysql-utilities-1.3.6-1.el6.noarch |less          
/usr/bin/mysqlauditadmin
/usr/bin/mysqlauditgrep
/usr/bin/mysqldbcompare
/usr/bin/mysqldbcopy
/usr/bin/mysqldbexport
/usr/bin/mysqldbimport
/usr/bin/mysqldiff
/usr/bin/mysqldiskusage
/usr/bin/mysqlfailover
/usr/bin/mysqlfrm
/usr/bin/mysqlindexcheck
/usr/bin/mysqlmetagrep
/usr/bin/mysqlprocgrep
/usr/bin/mysqlreplicate
/usr/bin/mysqlrpladmin
/usr/bin/mysqlrplcheck
/usr/bin/mysqlrplshow
/usr/bin/mysqlserverclone
/usr/bin/mysqlserverinfo
/usr/bin/mysqluc
/usr/bin/mysqluserclone

8. LOAD DATA INFILE

需要FILE权限，load data以很快的速度导入数据，看起来蛮省事的，但是存在几个问题：

如果导入数据很多，短时间内会引入很大的流量，磁盘io也会激增，是否会影响线上应用，视数据库压力而定。
master有多少更新，同样会在slave中更新，max allow packets 选项限制了每次传输的大小，如果表很大，数据会分组传输，slave 会落后master(second_behind_master)较长时间，一致性要求比较高的业务会受到影响(如订阅，充值等)。另外master -> slave之间的流量也会激增。
5.1版本会触发LOAD DATA相关的bug, 详见http://dev.mysql.com/doc/refman/5.5/en/replication-features-load-data.html 线上业务建议禁止load data操作。

9. 修改隔离级别

可以在线修改:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

可以在配置 mysqld 中修改, 重启生效:

transaction_isolation =  READ-COMMITTED

← → Top