這幾天要折騰mysql服務(wù)器，所以在網(wǎng)上搜羅了一些維護(hù)策略，然后自己總結(jié)實(shí)驗(yàn)，下面是我的總結(jié)經(jīng)驗(yàn)和別人的一些建議。
日志類型：
MySQL有幾個(gè)不同的日志文件，可以幫助你找出mysqld內(nèi)部發(fā)生的事情：
日志文件：記入文件中的信息類型
錯(cuò)誤日志：記錄啟動(dòng)、運(yùn)行或停止時(shí)出現(xiàn)的問(wèn)題
查詢?nèi)罩荆河涗浗⒌目蛻舳诉B接和執(zhí)行的語(yǔ)句
二進(jìn)制日志：記錄所有更改數(shù)據(jù)的語(yǔ)句。主要用于復(fù)制和即時(shí)點(diǎn)恢復(fù)
慢日志：記錄所有執(zhí)行時(shí)間超過(guò)long_query_time秒的所有查詢或不使用索引的查詢
事務(wù)日志：記錄InnoDB等支持事務(wù)的存儲(chǔ)引擎執(zhí)行事務(wù)時(shí)產(chǎn)生的日志
1.啟動(dòng)慢查詢?nèi)罩荆?/p>
MySQL 如果啟用了slow_query_log=ON選項(xiàng)，就會(huì)記錄執(zhí)行時(shí)間超過(guò)long_query_time(默認(rèn)10s)的查詢(初使表鎖定的時(shí)間不算作 執(zhí)行 時(shí)間)。日志記錄文件為slow_query_log_file[=file_name]，如果沒(méi)有給出file_name值， 默認(rèn)為主機(jī)名，后綴為-slow.log。如果給出了文件名，但不是絕對(duì)路徑名，文件則寫入數(shù)據(jù)目錄。
【這個(gè)可以在調(diào)試mysql性能的時(shí)候啟用,可以找出是哪個(gè)sql指令最浪費(fèi)時(shí)間。生產(chǎn)環(huán)境中建議關(guān)閉】
2.生產(chǎn)環(huán)境中關(guān)閉通用查詢?nèi)罩荆?/p>
由 于打開(kāi)通用查詢?nèi)罩臼怯涗浻脩舻乃胁僮鳎谏a(chǎn)環(huán)境中這個(gè)日志的量是非常大的，所以一般情況下都是不打開(kāi)的，myslq默認(rèn)的該日志功能也是關(guān)閉的，在 特殊情況下才進(jìn)行打開(kāi)【一般只有在開(kāi)發(fā)測(cè)試環(huán)境中，為了定位某些功能具體使用了哪些SQL語(yǔ)句的時(shí)候，才會(huì)在短時(shí)間段內(nèi)打開(kāi)該日志來(lái)做相應(yīng)的分析�！�
mysql> set global general_log = 1; #1：?jiǎn)?dòng)通用查詢?nèi)罩荆?：關(guān)閉通用查詢?nèi)罩?/p>
mysql> show global variables like '%general_log%';
+------------------+----------------------------+
| Variable_name | Value |
+------------------+----------------------------+
| general_log | ON | #是否啟用了通用查詢?nèi)罩?/p>
| general_log_file | /var/run/mysqld/mysqld.log | #日志路徑
+------------------+----------------------------+
2 rows in set (0.00 sec)
3.定期備份二進(jìn)制日志和sql數(shù)據(jù)：【本地一份，遠(yuǎn)程日志主機(jī)一份，存儲(chǔ)主機(jī)一份】
在 my.cnf中l(wèi)og-bin = [filename]是啟用二進(jìn)制日志，默認(rèn)以[filename].000001往上記錄的，從啟用log-bin之后【此時(shí)最好用mysqldump 保存當(dāng)前的mysql某個(gè)庫(kù)的數(shù)據(jù)，因?yàn)槎M(jìn)制日志只是記錄了從現(xiàn)在起到最近一次mysql當(dāng)機(jī)重啟中的所有sql語(yǔ)句】，mysql就會(huì)開(kāi)始記錄每一個(gè) sql語(yǔ)句，一旦mysql因各種原因需要重啟，則會(huì)產(chǎn)生新的二進(jìn)制日志，000001的后綴名會(huì)不斷往上自加。若是在mysql當(dāng)機(jī)期間mysql的數(shù) 據(jù)遭到了破壞(如磁盤損壞)，之前的數(shù)據(jù)全部都被破壞了，這時(shí)候這個(gè)備份策略就可以幫你挽回?fù)p失。你可以從二進(jìn)制日志中恢復(fù)從開(kāi)始到最近一次mysql重 啟這段時(shí)間的數(shù)據(jù)�！径M(jìn)制日志中記錄的是每一個(gè)sql語(yǔ)句，可以用mysqlbinlog [filename]查看日志內(nèi)容】
4.sync_binlog全局變量的取值一定要合適：
默 認(rèn)情況下，并不是每次寫入時(shí)都將二進(jìn)制日志與硬盤同步。因此如果操作系統(tǒng)或機(jī)器(不僅僅是MySQL服務(wù)器)崩潰，有可能二進(jìn)制日志中最后的語(yǔ)句丟失了。 要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值，但也是最慢的)，使二進(jìn)制日志在每N次二進(jìn)制日志寫入后與硬盤同步。對(duì)非 事務(wù)表的更新執(zhí)行完畢后立即保存到二進(jìn)制日志中。
下面解釋下sync_binlog:
“sync_binlog”：這個(gè)參數(shù)是對(duì)于MySQL系統(tǒng)來(lái)說(shuō)是至關(guān)重要的，他不僅影響到Binlog對(duì)MySQL所帶來(lái)的性能損耗，而且還影響到MySQL中數(shù)據(jù)的完整性。對(duì)于“sync_binlog”參數(shù)的各種設(shè)置的說(shuō)明如下：
sync_binlog=0，當(dāng)事務(wù)提交之后，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤，而讓Filesystem自行決定什么時(shí)候來(lái)做同步，或者cache滿了之后才同步到磁盤。
sync_binlog=n，當(dāng)每進(jìn)行n次事務(wù)提交之后，MySQL將進(jìn)行一次fsync之類的磁盤同步指令來(lái)將binlog_cache中的數(shù)據(jù)強(qiáng)制寫入磁盤。
在 MySQL中系統(tǒng)默認(rèn)的設(shè)置是sync_binlog=0，也就是不做任何強(qiáng)制性的磁盤刷新指令，這時(shí)候的性能是最好的，但是風(fēng)險(xiǎn)也是最大的。因?yàn)橐坏┫?統(tǒng)Crash，在binlog_cache中的所有binlog信息都會(huì)被丟失。而當(dāng)設(shè)置為“1”的時(shí)候，是最安全但是性能損耗最大的設(shè)置。因?yàn)楫?dāng)設(shè)置為 1的時(shí)候，即使系統(tǒng)Crash，也最多丟失binlog_cache中未完成的一個(gè)事務(wù)，對(duì)實(shí)際數(shù)據(jù)沒(méi)有任何實(shí)質(zhì)性影響。從以往經(jīng)驗(yàn)和相關(guān)測(cè)試來(lái)看,對(duì)于 高并發(fā)事務(wù)的系統(tǒng)來(lái)說(shuō),“sync_binlog”設(shè)置為0和設(shè)置為1的系統(tǒng)寫入性能差距可能高達(dá)5倍甚至更多。
5.如果數(shù)據(jù)庫(kù)有很多的事務(wù)型操作，則建議把二進(jìn)制日志的回滾上限設(shè)置大一些：
對(duì)于事務(wù)表，例如BDB或InnoDB表，所有更改表的更新(UPDATE、DELETE或INSERT)被緩存起來(lái)，直到服務(wù)器接收到 COMMIT語(yǔ)句。在該點(diǎn)，執(zhí)行完COMMIT之前，mysqld將整個(gè)事務(wù)寫入二進(jìn)制日志。當(dāng)處理事務(wù)的線程啟動(dòng)時(shí)，它為 緩沖查詢分配binlog_cache_size大小的內(nèi)存。如果語(yǔ)句大于該值，線程則打開(kāi)臨時(shí)文件來(lái)保存事務(wù)【所以如果 bunlog_cache_size足夠大，就避免了過(guò)多的磁盤的I/O操作,可以把數(shù)據(jù)全部緩存在內(nèi)存中】。線程結(jié)束后臨時(shí)文件被刪除。 【“max_binlog_cache_size”：和"binlog_cache_size"相對(duì)應(yīng)，但是所代表的是binlog能夠使用的最大 cache內(nèi)存大校當(dāng)我們執(zhí)行多語(yǔ)句事務(wù)的時(shí)候，max_binlog_cache_size如果不夠大的話，系統(tǒng)可能會(huì)報(bào)出“Multi- statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage” 的錯(cuò)誤。所以最好也把max_binlog_cache_size也調(diào)大些(具體多大看你的服務(wù)器了)】
6.盡量把max_binlog_size設(shè)置大些
Binlog日志最大值，一般來(lái)說(shuō)設(shè)置為512M或者1G，但不能超過(guò)1G。該大小并不能非常嚴(yán)格控制Binlog大小，尤其是當(dāng)?shù)竭_(dá)Binlog 比較靠近尾部而又遇到一個(gè)較大事務(wù)的時(shí)候，系統(tǒng)為了保證事務(wù)的完整性，不可能做切換日志的動(dòng)作，只能將該事務(wù)的所有SQL都記錄進(jìn)入當(dāng)前日志，直到該事務(wù) 結(jié)束。
7.下面是mysql環(huán)境的情況：
mysql> show variables like '%binlog%';
+--------------------------------+------------+ | Variable_name | Value | +--------------------------------+------------+
| binlog_cache_size | 1048576 |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size| 4294967295 |
| max_binlog_size| 1073741824 |
| sync_binlog| 0|
+--------------------------------+------------+
以上就是匯總的MySQL日志維護(hù)策略，希望對(duì)大家維護(hù)MySQL日志有所幫助。
