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集群和高可用版版主。
ホットAIツール
Undresser.AI Undress
リアルなヌード写真を作成する AI 搭載アプリ
AI Clothes Remover
写真から衣服を削除するオンライン AI ツール。
Undress AI Tool
脱衣画像を無料で
Clothoff.io
AI衣類リムーバー
AI Hentai Generator
AIヘンタイを無料で生成します。
人気の記事
ホットツール
メモ帳++7.3.1
使いやすく無料のコードエディター
SublimeText3 中国語版
中国語版、とても使いやすい
ゼンドスタジオ 13.0.1
強力な PHP 統合開発環境
ドリームウィーバー CS6
ビジュアル Web 開発ツール
SublimeText3 Mac版
神レベルのコード編集ソフト（SublimeText3）
ホットトピック
7480
15
1377
52
77
11
19
33
MySQL：初心者向けのデータ管理の容易さ
Apr 09, 2025 am 12:07 AM
MySQLは、インストールが簡単で、強力で管理しやすいため、初心者に適しています。 1.さまざまなオペレーティングシステムに適した、単純なインストールと構成。 2。データベースとテーブルの作成、挿入、クエリ、更新、削除などの基本操作をサポートします。 3.参加オペレーションやサブクエリなどの高度な機能を提供します。 4.インデックス、クエリの最適化、テーブルパーティション化により、パフォーマンスを改善できます。 5。データのセキュリティと一貫性を確保するために、バックアップ、リカバリ、セキュリティ対策をサポートします。
NAVICATでデータベースパスワードを取得できますか？
Apr 08, 2025 pm 09:51 PM
NAVICAT自体はデータベースパスワードを保存せず、暗号化されたパスワードのみを取得できます。解決策：1。パスワードマネージャーを確認します。 2。NAVICATの「パスワードを記憶する」機能を確認します。 3.データベースパスワードをリセットします。 4.データベース管理者に連絡してください。
Navicatプレミアムの作成方法
Apr 09, 2025 am 07:09 AM
NAVICATプレミアムを使用してデータベースを作成します。データベースサーバーに接続し、接続パラメーターを入力します。サーバーを右クリックして、[データベースの作成]を選択します。新しいデータベースの名前と指定された文字セットと照合を入力します。新しいデータベースに接続し、オブジェクトブラウザにテーブルを作成します。テーブルを右クリックして、データを挿入してデータを挿入します。
MariadBのNAVICATでデータベースパスワードを表示する方法は？
Apr 08, 2025 pm 09:18 PM
Passwordが暗号化された形式で保存されているため、MariadbのNavicatはデータベースパスワードを直接表示できません。データベースのセキュリティを確保するには、パスワードをリセットするには3つの方法があります。NAVICATを介してパスワードをリセットし、複雑なパスワードを設定します。構成ファイルを表示します（推奨されていない、高リスク）。システムコマンドラインツールを使用します（推奨されません。コマンドラインツールに習熟する必要があります）。
MySQL：簡単な学習のためのシンプルな概念
Apr 10, 2025 am 09:29 AM
MySQLは、オープンソースのリレーショナルデータベース管理システムです。 1）データベースとテーブルの作成：createdatabaseおよびcreateTableコマンドを使用します。 2）基本操作：挿入、更新、削除、選択。 3）高度な操作：参加、サブクエリ、トランザクション処理。 4）デバッグスキル：構文、データ型、およびアクセス許可を確認します。 5）最適化の提案：インデックスを使用し、選択*を避け、トランザクションを使用します。
NAVICATでSQLを実行する方法
Apr 08, 2025 pm 11:42 PM
NAVICATでSQLを実行する手順：データベースに接続します。 SQLエディターウィンドウを作成します。 SQLクエリまたはスクリプトを書きます。 [実行]ボタンをクリックして、クエリまたはスクリプトを実行します。結果を表示します（クエリが実行された場合）。
NAVICATは、MySQL/Mariadb/PostgreSQLおよびその他のデータベースに接続できません
Apr 08, 2025 pm 11:00 PM
NAVICATがデータベースとそのソリューションに接続できない一般的な理由：1。サーバーの実行ステータスを確認します。 2。接続情報を確認します。 3.ファイアウォール設定を調整します。 4.リモートアクセスを構成します。 5.ネットワークの問題のトラブルシューティング。 6.許可を確認します。 7.バージョンの互換性を確保します。 8。他の可能性のトラブルシューティング。
NavicatでMySQLへの新しい接続を作成する方法
Apr 09, 2025 am 07:21 AM
手順に従って、NAVICATで新しいMySQL接続を作成できます。アプリケーションを開き、新しい接続（CTRL N）を選択します。接続タイプとして「mysql」を選択します。ホスト名/IPアドレス、ポート、ユーザー名、およびパスワードを入力します。 （オプション）Advanced Optionsを構成します。接続を保存して、接続名を入力します。
