如何在Linux命令行下快速监控mysql？

其实很简单，就是利用linux下的watch 工具来做监控，方法如下：

[yejr@localhost imysql]# watch -d -n 10 "egrep 'MySQL thread|Log|Modified db pages' innodb_status.3249 "    

Every 10.0s: egrep 'MySQL thread|Log|Modified db pages' innodb_status.3249           Thu Apr  9 10:01:12 2009

MySQL thread id 6, query id 71 localhost root Sending data

Log sequence number 2703 3443241402

Log flushed up to   2703 3442763607

Modified db pages       83325

或者：

[yejr@localhost imysql]#watch -d -n 10 "mysqladmin ext|egrep Innodb_data"  

Every 10.0s: mysqladmin ext|egrep Innodb_data                                                                            Thu Apr  9 10:03:55 2009

| Innodb_data_fsyncs                | 4144699     |

| Innodb_data_pending_fsyncs        | 0           |

| Innodb_data_pending_reads         | 0           |

| Innodb_data_pending_writes        | 0           |

| Innodb_data_read                  | 5567172608  |

| Innodb_data_reads                 | 298413      |

| Innodb_data_writes                | 4492881     |

| Innodb_data_written               | 18549422080 |

参数解释：

-d --differences

-n --interval

 

如何更改MySQL的默认字符集？

方法1、 用 SET 语法来指定，不加 "GLOBAL" 的话就只对本次会话有效

SET [GLOBAL] character_set_client     = utf8;

SET [GLOBAL] character_set_connection = utf8;

SET [GLOBAL] character_set_database   = utf8;

SET [GLOBAL] character_set_results    = utf8;

SET [GLOBAL] character_set_server     = utf8;

方法2、 也用SET语法，只对本次会话有效

SET NAMES 'utf8';

方法3、） 直接修改 my.cnf，增加一行内容，然后重启 MySQL，使之全局生效

default-character-set = utf8

 

如何快速对调字段里面的某些列？

问题：表 t 有个字段叫做 c，现在想要把 c 里面的第 10 和 第 11 列位置对调一下，咋办啊？

答案：用下面的办法吧，不过本例只对ascii字符有作用，中文或其他的就另外想办法。

mysql>set @pos1 = 10;

mysql>set @pos2 = 11;

mysql>UPDATE t SET c = CONCAT(

LEFT(c,@pos1-1),   -- 第 10 列以前的值

SUBSTR(c,@pos2,1),  -- 第 11 列的值

SUBSTR(c,@pos1+1,@pos2-@pos1-1),  -- 第 10 到第 11 列之间的值

SUBSTR(c,@pos1,1),  -- 第 10 列

RIGHT(c, LENGTH(c) - @pos2)); -- 第 11 列之后的值

 

为何会出现大量unauthenticated user？

看下手册中的解释是：unauthenticated user refers to a thread that has become associated with a client connection but for which authentication of the client user has not yet been done。意即：有一个线程在处理客户端的连接，但是该客户端还没通过用户验证。

原因可能有：

1 服务器在做DNS反响解析，解决办法有2：

1）在 hosts 中添加客户端ip，如

192.168.0.1  yejr

2）MySQL启动参数增加一个skip-name-resolve，即不启用DNS反响解析

2、服务器的线程还处于排队状态，因此可以加大 back_log

 

数据不算大，备份却非常慢？

环境
硬件：DELL 1950, 146G SAS 15K RPMS * 2, 8G Ram
软件：2.6.9-55.ELsmp x86_64, mysql 5.1.x

现象
2个库，其中1个业务库下有20多个表，表文件大小总量不到2G。

另一个为日志库，下400多个表，大致是每天会产生5个表，其中有一个表较大，约400MB，总量约40多GB。

每次备份耗时较长，最严重的一次花了5个多小时才完成。

业务库为当前活动库，日志库则主要用作备份，每天日志归档，过期数据表很少有读写请求。

InnoDB Buffer Pool总共分配了2G，从系统命令 top 结果来看，mysqld 只分配了1.7G 内存，buffer pool 并没有全部耗尽。

SHOW ENGINE INNODB STATUS 结果中也看到了，buffer pool 确实没用完，还有不少空闲的。

备份时，观察 vmstat 结果，发现 bi 和 bo 的量较大，而且两个的值基本相当，备份其中一个表约 500MB，耗时46 秒。

按照这个耗时计算，全部备份出来也不需要5个多小时，这是为什么呢？

分析

大家先分析下，看是什么原因，稍后给出答案 :)

原因

其实问题原因很简单，但一般人不容易想到。那就是，那些历史的日志表，由于长时间不读取，大部分数据没有在innodb buffer中。所以，每次备份时，大部分数据都要产生大量的物理读，然后再产生物理写，然而该服务器只有2块硬盘，I/O性能有限，所以备份非常慢。

这时候，我们可以有几种解决办法：
1. 删除过期日志表，或者放到线下的归档数据库上
2. 如果innodb buffer还有大量空闲的话，可以不定期执行select * from table，将这部分数据load到buffer中，减少备份时的物理I/O，提高速度

 

磁盘空间满了之后MySQL会怎样？

当磁盘空间爆满后，MySQL会发生什么事呢？又应该怎么应对？

 

会发生什么事

当磁盘空间写满了之后，MySQL是无法再写入任何数据的，包括对表数据的写入，以及binlog、binlog-index等文件。

当然了，因为InnoDB是可以把脏数据先放在内存里，所以不会立刻表现出来无法写入，除非开启了binlog，写入请求才会被阻塞。

当MySQL检测到磁盘空间满了，它会：

每分钟：检查空间是否得到释放，以便写入新数据。当发现有剩余空间了，就会继续写入数据，一切照旧。

每十分钟：如果还是发现没剩余空间，则会在日志中写入一条记录，报告磁盘空间满（这时候只写入几个字节还是够的）。

 

应该怎么办

那么，当发现磁盘空间满了之后，我们应该怎么处理呢，建议：

提高监控系统检测频率，预防再次发生；

及时删除不用的文件，释放空间；

若有线程因磁盘满的问题被阻塞了，可先杀掉，等到下一分钟重新检测时它可能又可以正常工作了；

可能因磁盘满导致某些线程被阻塞，引发其他线程也被阻塞，可把导致阻塞的线程杀掉，其他被阻塞的线程也就能继续工作了。

 

例外

有个例外的情况是：

当执行 REPAIR TABLE 或者OPTIMIZE TABLE 操作时，或者执行完 LOAD DATA INFILE 或 ALTER TABLE 之后批量更新索引时，这些操作会创建临时文件，当执行这些操作过程中mysqld发现磁盘空间满了，就会把这个涉及到的表标记为crashed，删掉临时文件（除了 ALTER TABLE 操作，MySQL会放弃正在执行的操作，删除临时文件，释放磁盘空间）。

 

备注：当执行这些命令过程中mysqld进程被意外被杀掉的话，其所生成临时文件不会自动删除，需要手工删掉才能释放磁盘空间。