MySQL主从配置的一些总结

【51CTO独家特稿】 一、 做了MySQL主从也有一段时间了，这两天检查磁盘空间情况，发现放数据库的分区磁盘激增了40多G，一路查看下来，发现配置好主从复制以来到现在的binlog就有40多G，原来根源出在这里，查看了一下my.cnf，看到binlog的 size是1G就做分割，

【51CTO独家特稿】一、做了MySQL主从也有一段时间了，这两天检查磁盘空间情况，发现放数据库的分区磁盘激增了40多G，一路查看下来，发现配置好主从复制以来到现在的binlog就有40多G，原来根源出在这里，查看了一下my.cnf，看到binlog的 size是1G就做分割，但没有看到删除的配置，在MySQL里show了一下variables：

作者个人博客：andrewyu.blog.51cto.com

<ol class="dp-sql"><li class="alt"><span><span>mysql>show variables </span><span class="op">like</span><span> </span><span class="string">'%log%'</span><span>; </span></span></li></ol>

查到了，

<ol class="dp-sql"><li class="alt"><span><span>| expire_logs_days | 0 | </span></span></li></ol>

这个默认是0，也就是logs不过期，这个是一个global的参数，所以需要执行

<ol class="dp-sql"><li class="alt"><span><span class="keyword">set</span><span> </span><span class="keyword">global</span><span> expire_logs_days=8; </span></span></li></ol>

这样8天前的log就会被删除了，如果有回复的需要，请做好备份工作，但这样设置还不行，下次重启mysql了，配置又恢复默认了，所以需在my.cnf中设置，

<ol class="dp-sql"><li class="alt"><span><span>expire_logs_days = 8 </span></span></li></ol>

这样重启也不怕了。

现在我在生产环境下的做法是将此时间设为0，然后备份mysql日志文件，然后再手动清理此文件。

想要恢复数据库以前的资料,执行

<ol class="dp-sql"><li class="alt"><span><span>mysql>show binlog events; </span></span></li></ol>

由于数据量很多，查看起来很麻烦，光打开个文件就要闪半天，所以应该适当删除部分可不用的日志。

并且如果使用的时间足够长的话，会把我的硬盘空间都给吃掉。

1、登录系统，/usr/bin/mysql

使用mysql查看日志：

<ol class="dp-sql">
<li class="alt"><span><span>mysql>show </span><span class="keyword">binary</span><span> logs; </span></span></li>
<li><span>+—————-+———–+ </span></li>
<li class="alt"><span>| Log_name | File_size | </span></li>
<li><span>+—————-+———–+ </span></li>
<li class="alt"><span>| ablelee.000001 | 150462942 | </span></li>
<li><span>| ablelee.000002 | 120332942 | </span></li>
<li class="alt"><span>| ablelee.000003 | 141462942 | </span></li>
<li><span>+—————-+———–+ </span></li>
</ol>

2、删除bin-log(删除ablelee.000003之前的而没有包含ablelee.000003)：

<ol class="dp-sql">
<li class="alt"><span><span>mysql> purge </span><span class="keyword">binary</span><span> logs </span><span class="keyword">to</span><span> ′ablelee.000003′; </span></span></li>
<li><span>Query OK, 0 <span class="keyword">rows</span><span> affected (0.16 sec) </span></span></li>
</ol>

3、查询结果(现在只有一条记录了)：

<ol class="dp-sql">
<li class="alt"><span><span>mysql> show binlog events\G  </span></span></li>
<li><span>*************************** 1. row ***************************  </span></li>
<li class="alt"><span>Log_name: ablelee.000003  </span></li>
<li><span>Pos: 4  </span></li>
<li class="alt"><span>Event_type: Format_desc  </span></li>
<li><span>Server_id: 1  </span></li>
<li class="alt"><span>End_log_pos: 106  </span></li>
<li><span>Info: Server ver: 5.1.26-rc-log, Binlog ver: 4  </span></li>
<li class="alt"><span>1 row <span class="op">in</span><span> </span><span class="keyword">set</span><span> (0.01 sec)  </span></span></li>
<li><span>(ablelee.000001和ablelee.000002已被删除)  </span></li>
<li class="alt"><span>mysql> show <span class="keyword">binary</span><span> logs;  </span></span></li>
<li><span>+—————-+———–+  </span></li>
<li class="alt"><span>| Log_name | File_size |  </span></li>
<li><span>+—————-+———–+  </span></li>
<li class="alt"><span>| ablelee.000003 | 106 |  </span></li>
<li><span>+—————-+———–+  </span></li>
<li class="alt"><span>1 row <span class="op">in</span><span> </span><span class="keyword">set</span><span> (0.00 sec)  </span></span></li>
<li><span>(删除的其它格式运用!)  </span></li>
<li class="alt"><span>PURGE {MASTER | <span class="keyword">BINARY</span><span>} LOGS </span><span class="keyword">TO</span><span> ‘log_name’  </span></span></li>
<li><span>PURGE {MASTER | <span class="keyword">BINARY</span><span>} LOGS BEFORE ‘</span><span class="keyword">date</span><span>’  </span></span></li>
</ol>

用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。

例如：

<ol class="dp-sql">
<li class="alt"><span><span>PURGE MASTER LOGS </span><span class="keyword">TO</span><span> </span><span class="string">'mysql-bin.010'</span><span>; </span></span></li>
<li><span>PURGE MASTER LOGS BEFORE <span class="string">'2008-06-22 13:00:00'</span><span>; </span></span></li>
</ol>

二、现在手上蛮多项目的数据库用的是MySQL，由于权限等原因，暂时不方便部署Nagios监控MySQL主从复制，所以我一般在从机上配置了SHELL脚本用来监控MySQL的主从状态(设置为每十分钟运行一次)，并且每次出问题时将确切日期写进错误日志，方便事后排查原因，脚本内容如下：

<ol class="dp-sql">
<li class="alt"><span><span>#!/bin/bash </span></span></li>
<li><span>#<span class="keyword">check</span><span> MySQL_Slave Status </span></span></li>
<li class="alt"><span>#crontab <span class="keyword">time</span><span> 00:10 </span></span></li>
<li><span>MYSQLPORT=`netstat -na|grep <span class="string">"LISTEN"</span><span>|grep </span><span class="string">"3306"</span><span>|awk -F[:</span><span class="string">" "</span><span>]+ </span><span class="string">'{print $4}'</span><span>` </span></span></li>
<li class="alt"><span>MYSQLIP=`ifconfig eth0|grep <span class="string">"inet addr"</span><span> | awk -F[:</span><span class="string">" "</span><span>]+ </span><span class="string">'{print $4}'</span><span>` </span></span></li>
<li><span>STATUS=$(/usr/<span class="keyword">local</span><span>/webserver/mysql/bin/mysql -u yuhongchun -pyuhongchun101 -S /tmp/mysql.sock -e </span><span class="string">"show slave status\G"</span><span> | grep -i </span><span class="string">"running"</span><span>) </span></span></li>
<li class="alt"><span>IO_env=`echo $STATUS | grep IO | awk <span class="string">' {print $2}'</span><span>` </span></span></li>
<li><span>SQL_env=`echo $STATUS | grep SQL | awk <span class="string">'{print $2}'</span><span>` </span></span></li>
<li class="alt"><span> </span></li>
<li><span> </span></li>
<li class="alt"><span>if [ <span class="string">"$MYSQLPORT"</span><span> == </span><span class="string">"3306"</span><span> ] </span></span></li>
<li><span><span class="keyword">then</span><span> </span></span></li>
<li class="alt"><span>echo <span class="string">"mysql is running"</span><span> </span></span></li>
<li><span><span class="keyword">else</span><span> </span></span></li>
<li class="alt"><span>mail -s <span class="string">"warn!server: $MYSQLIP mysql is down"</span><span> yuhongchun027@163.com </span></span></li>
<li><span>fi </span></li>
<li class="alt"><span> </span></li>
<li><span>if [ <span class="string">"$IO_env"</span><span> = </span><span class="string">"Yes"</span><span> -a </span><span class="string">"$SQL_env"</span><span> = </span><span class="string">"Yes"</span><span> ] </span></span></li>
<li class="alt"><span><span class="keyword">then</span><span> </span></span></li>
<li><span>echo <span class="string">"Slave is running!"</span><span> </span></span></li>
<li class="alt"><span><span class="keyword">else</span><span> </span></span></li>
<li><span>echo <span class="string">"####### $date #########"</span><span>>> /data/data/check_mysql_slave.log </span></span></li>
<li class="alt"><span>echo <span class="string">"Slave is not running!"</span><span> >> /data/data/check_mysql_slave.log </span></span></li>
<li><span>mail -s <span class="string">"warn! $MySQLIP_replicate_error"</span><span> yuhongchun027@163.com </span></span></li>
<li class="alt"><span>fi </span></li>
</ol>

建议每十分钟运行一次。

<ol class="dp-sql"><li class="alt"><span><span>*/10 * * * * root /bin/sh /root/mysql_slave.sh  </span></span></li></ol>

记得在每台MySQL从机上分配一个yuhongchun的用户，权限大些也没关系，只限定在本地运行，如下所示：

<ol class="dp-sql">
<li class="alt"><span><span class="keyword">grant</span><span> </span><span class="op">all</span><span> </span><span class="keyword">privileges</span><span> </span><span class="keyword">on</span><span> *.* </span><span class="keyword">to</span><span> </span><span class="string">"yuhongchun"</span><span>@</span><span class="string">"127.0.0.1"</span><span> identified </span><span class="keyword">by</span><span> </span><span class="string">"yuhongchun101"</span><span>; </span></span></li>
<li><span><span class="keyword">grant</span><span> </span><span class="op">all</span><span> </span><span class="keyword">privileges</span><span> </span><span class="keyword">on</span><span> *.* </span><span class="keyword">to</span><span> </span><span class="string">"yuhongchun"</span><span>@</span><span class="string">"localhost"</span><span> identified </span><span class="keyword">by</span><span> </span><span class="string">"yuhongchun101"</span><span>; </span></span></li>
</ol>

脚本设计思路：

1、此脚本应该能适应各种各样不同的内外网环境，即IP不同的环境；

2、让脚本也顺便监控下MySQL是否正常运行；

三、innodb_buffer_pool_size的设置。

这个参数定义了InnodDB存储引擎的表数据和索引数据的最大内存缓冲区大小。和MyISAM存储引擎不同，MyISAM的key_buffer_size只缓存索引键，而innodb_buffer_pool_size却是同时为数据块和索引块 做缓存，这个特征和Oracle是一样的，这个值设得越高，访问表中数据需求的I/O就越少。在一个专用的数据库服务器，可以设置这个参数达机器物理内存的80%，我现在一般的做法是配置成物理内存的 1/4，比如8G内存的生产数据库，我一般会配置成2G左右。

四、测试了很长一段时间的MySQL的负载均衡，最后综合了老男孩和其它技术高手的意见，最终决定还是用LVS+Keepalived来作为MySQL的负载均衡，这是因为后端机器超过10台时，LVS的性能还是最好的；如果在3-5台左右，HAProxy也可以很轻松的搞定工作。

五、大家都很清，磁盘I/O总会成为数据库的性能瓶颈，这时候我们应该如何在生产环境下选择合适的RAID级别呢？

1、如果数据读写都很频繁，可靠性要求也很高，最好选择RAID10；

2、如果数据读很频繁，写相对较少，对可靠性有一定要求，可以选择RAID5；

3、如果数据读写都很频繁，但可靠性要求不高，可以选择RAID0。

4、对于核心业务的数据库主从同步，建议从机的备份时间往后延迟一段时间，通常的做法是延迟一天左右。

作者介绍：

余洪春（抚琴煮酒），《构建高可用Linux服务器》一书作者，一拍网系统架构师、资深项目管理工程师，ChinaUnix集群和高可用版版主。