MySQL主從配置的一些總結
原創【51CTO獨家特稿】一、做了MySQL主從也有一段時間了，這兩天檢查磁盤空間情況，發現放數據庫的分區磁盤激增了40多G，一路查看下來，發現配置好主從復制以來到現在的binlog就有40多G，原來根源出在這里，查看了一下my.cnf，看到binlog的 size是1G就做分割，但沒有看到刪除的配置，在MySQL里show了一下variables：
作者個人博客：andrewyu.blog.51cto.com
- mysql>show variables like '%log%';
查到了，
- | expire_logs_days | 0 |
這個默認是0，也就是logs不過期，這個是一個global的參數，所以需要執行
- set global expire_logs_days=8;
這樣8天前的log就會被刪除了，如果有回復的需要，請做好備份工作，但這樣設置還不行，下次重啟mysql了，配置又恢復默認了，所以需在my.cnf中設置，
- expire_logs_days = 8
這樣重啟也不怕了。
現在我在生產環境下的做法是將此時間設為0，然后備份mysql日志文件，然后再手動清理此文件。
想要恢復數據庫以前的資料,執行
- mysql>show binlog events;
由于數據量很多，查看起來很麻煩，光打開個文件就要閃半天，所以應該適當刪除部分可不用的日志。
并且如果使用的時間足夠長的話，會把我的硬盤空間都給吃掉。
1、登錄系統，/usr/bin/mysql
使用mysql查看日志：
- mysql>show binary logs;
- +—————-+———–+
- | Log_name | File_size |
- +—————-+———–+
- | ablelee.000001 | 150462942 |
- | ablelee.000002 | 120332942 |
- | ablelee.000003 | 141462942 |
- +—————-+———–+
2、刪除bin-log(刪除ablelee.000003之前的而沒有包含ablelee.000003)：
- mysql> purge binary logs to ′ablelee.000003′;
- Query OK, 0 rows affected (0.16 sec)
3、查詢結果(現在只有一條記錄了)：
- mysql> show binlog events\G
- *************************** 1. row ***************************
- Log_name: ablelee.000003
- Pos: 4
- Event_type: Format_desc
- Server_id: 1
- End_log_pos: 106
- Info: Server ver: 5.1.26-rc-log, Binlog ver: 4
- 1 row in set (0.01 sec)
- (ablelee.000001和ablelee.000002已被刪除)
- mysql> show binary logs;
- +—————-+———–+
- | Log_name | File_size |
- +—————-+———–+
- | ablelee.000003 | 106 |
- +—————-+———–+
- 1 row in set (0.00 sec)
- (刪除的其它格式運用!)
- PURGE {MASTER | BINARY} LOGS TO ‘log_name’
- PURGE {MASTER | BINARY} LOGS BEFORE ‘date’
用于刪除列于在指定的日志或日期之前的日志索引中的所有二進制日志。這些日志也會從記錄在日志索引文件中的清單中被刪除，這樣被給定的日志成為***個。
例如：
- PURGE MASTER LOGS TO 'mysql-bin.010';
- PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';
二、現在手上蠻多項目的數據庫用的是MySQL，由于權限等原因，暫時不方便部署Nagios監控MySQL主從復制，所以我一般在從機上配置了SHELL腳本用來監控MySQL的主從狀態(設置為每十分鐘運行一次)，并且每次出問題時將確切日期寫進錯誤日志，方便事后排查原因，腳本內容如下：
- #!/bin/bash
- #check MySQL_Slave Status
- #crontab time 00:10
- MYSQLPORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ '{print $4}'`
- MYSQLIP=`ifconfig eth0|grep "inet addr" | awk -F[:" "]+ '{print $4}'`
- STATUS=$(/usr/local/webserver/mysql/bin/mysql -u yuhongchun -pyuhongchun101 -S /tmp/mysql.sock -e "show slave status\G" | grep -i "running")
- IO_env=`echo $STATUS | grep IO | awk ' {print $2}'`
- SQL_env=`echo $STATUS | grep SQL | awk '{print $2}'`
- if [ "$MYSQLPORT" == "3306" ]
- then
- echo "mysql is running"
- else
- mail -s "warn!server: $MYSQLIP mysql is down" yuhongchun027@163.com
- fi
- if [ "$IO_env" = "Yes" -a "$SQL_env" = "Yes" ]
- then
- echo "Slave is running!"
- else
- echo "####### $date #########">> /data/data/check_mysql_slave.log
- echo "Slave is not running!" >> /data/data/check_mysql_slave.log
- mail -s "warn! $MySQLIP_replicate_error" yuhongchun027@163.com << /data/data/check_mysql_slave.log
- fi
建議每十分鐘運行一次。
- */10 * * * * root /bin/sh /root/mysql_slave.sh
記得在每臺MySQL從機上分配一個yuhongchun的用戶，權限大些也沒關系，只限定在本地運行，如下所示：
- grant all privileges on *.* to "yuhongchun"@"127.0.0.1" identified by "yuhongchun101";
- grant all privileges on *.* to "yuhongchun"@"localhost" identified by "yuhongchun101";
腳本設計思路：
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集群和高可用版版主。
【編輯推薦】
- Ubuntu MySQL熱備份安裝
- MySQL中創建及優化索引組織結構的思路
- 說說一些有用的MySQL語句
- MySQL從庫集群方案之HAProxy篇
- MySQL安裝詳解(V5.5 For Windows)
