于吉吉的技術(shù)博客

          建造高性能門戶網(wǎng)

            BlogJava :: 首頁 :: 新隨筆 :: 聯(lián)系 :: 聚合  :: 管理 ::
            65 隨筆 :: 6 文章 :: 149 評論 :: 0 Trackbacks
          mysql有4種不同的日志,分別是二進(jìn)制日志,查詢?nèi)罩?,慢查詢?nèi)罩竞湾e(cuò)誤日志,這些日記記錄著數(shù)據(jù)庫工作的方方面面,可以幫助我們了解數(shù)據(jù)庫的不同方面的蹤跡,下面先介紹二進(jìn)制日志的作用和使用方法,并利用二進(jìn)制日志對數(shù)據(jù)庫進(jìn)行各種維護(hù)和優(yōu)化,其他日志也會(huì)在后面陸續(xù)會(huì)做詳細(xì)的介紹。

          二進(jìn)制日志(bin-log日志)

          上一篇介紹mysql主從配置的blog中,已經(jīng)提過bin-log日志的作用和使用,bin-log日志記錄了所有的DDL和DML的語句,但不包括查詢的語句,語句以事件的方式保存,描述了數(shù)據(jù)的更改過程,此日志對發(fā)生災(zāi)難時(shí)數(shù)據(jù)恢復(fù)起到了極為重要的作用。

          開啟
          mysql默認(rèn)是沒有開發(fā)bin-log日志,首先我們需要開啟bin-log日志,在my.cnf中修改



          指定了bin-log日志的路徑,開啟日志后需要myssqladmin flush log才生效,重啟后我們發(fā)現(xiàn)在剛才設(shè)定的路徑新增了log文件,這就是我們需要的二進(jìn)制日志



          由于日志是以二進(jìn)制方式存儲(chǔ)的,不能直接讀取,需要使用mysql自帶的mysqlbinlog工具來進(jìn)行查看
          語法如下:

          #mysqlbinlog mysql-bin.000002

          現(xiàn)在我們嘗試向test1表插入數(shù)據(jù)



          然后使用mysqlbinlog工具進(jìn)行日志查看

          #mysqlbinlog mysql-bin.000002 -d test



          清理

          如果每天都會(huì)生成大量的二進(jìn)制日志,這些日志長時(shí)間不清理的話,將會(huì)對磁盤空間帶來很大的浪費(fèi),所以定期清理日志是DBA維護(hù)mysql的一個(gè)重要工作

          1)RESET MASTER
          在上面查看日志存放的文件夾中,二進(jìn)制日志命名的格式是以mysql-bin.*,*代表日志的序號,序號是遞增的,其中還有mysql-bin.index是日志的索引文件,記錄了日志的最大序號
          我們執(zhí)行RESET MASTER命名刪除全部日志



          查看刪除后的日志



          可以看到,以前的日志全部被清空,新的日志從00001開始

          2)PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
          執(zhí)行PURGE MASTER LOGS TO 'mysql-bin.******'命令,是將'******'編號之前的所有日志進(jìn)行刪除
          執(zhí)行PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令,是將在'yyyy-mm-dd hh:mm:ss'時(shí)間之前的所有日志進(jìn)行刪除

          3)-EXPIRE_LOGS_DAYS
          此參數(shù)是設(shè)置日志的過期天數(shù),過期的日志將會(huì)被自動(dòng)刪除,這有利于減少我們管理日志的工作量,需要修改my.cnf



          這里我們設(shè)定保存日志為3天,3天之后過期的日志將被自動(dòng)刪除

          恢復(fù)

          bin-log是記錄著mysql所有事件的操作,當(dāng)mysql發(fā)生災(zāi)難性錯(cuò)誤時(shí),可以通過bin-log做完整恢復(fù),基于時(shí)間點(diǎn)的恢復(fù),和基于位置的恢復(fù)

          完整恢復(fù),假定我們每天凌晨2點(diǎn)都會(huì)使用mysqldump備份數(shù)據(jù)庫,但在第二天早上9點(diǎn)由于數(shù)據(jù)庫出現(xiàn)了故障,數(shù)據(jù)無法訪問,需要恢復(fù)數(shù)據(jù),先使用昨天凌晨備份的文件進(jìn)行恢復(fù)到凌晨2點(diǎn)的狀態(tài),在使用mysqlbinlog恢復(fù)自mysqldump備份以來的binlog
          mysql localhost mysql-bin.000001 | mysql -uroot -p
          這樣數(shù)據(jù)庫就可以完全的恢復(fù)到崩潰前的完全狀態(tài)

          基于時(shí)間點(diǎn)的恢復(fù),由于誤操作,比如說刪除了一張表,這時(shí)使用上面講的完全恢復(fù)是沒有用的,因?yàn)槿罩纠锩孢€存在誤操作的語句,,我們需要的是恢復(fù)到誤操作前的狀態(tài),然后跳過誤操作的語句,再恢復(fù)后面操作的語句,假定我們刪除了一張表的誤操作發(fā)生在10:00這個(gè)時(shí)間點(diǎn),我們可以使用下面的語句用備份和binlog將數(shù)據(jù)恢復(fù)到故障前

          mysqlbinlog --stop-date='2010-09-04 9:59:59' /var/log/mysql-bin.000001 | mysql -uroot -p

          然后跳過誤操作的時(shí)間點(diǎn),繼續(xù)執(zhí)行后面的binlog

          mysqlbinlog --start-date='2010-09-04 10:01:00' /var/log/mysql-bin.000001 | mysql -uroot -p

          其中--stop-date='2010-09-04 9:59:59' 和 --start-date='2010-09-04 10:01:00' 其中的時(shí)間是你誤操作的時(shí)間點(diǎn),當(dāng)然了,這個(gè)時(shí)間點(diǎn)你需要你自己計(jì)算的,而且這個(gè)時(shí)間點(diǎn)還可以涉及到的不只是誤操作,還可以有正確的操作也被跳過去了。

          基于位置恢復(fù),由于上面提到的,使用基于時(shí)間點(diǎn)的恢復(fù)可能出現(xiàn),在一個(gè)時(shí)間點(diǎn)里面可能存在誤操作和其他正確的操作,所以我們需要一種更為精確的恢復(fù)方式
          使用mysqlbinlog查看二進(jìn)制,可看到



          其中drop tables test1這個(gè)誤操作的end_log_pos為8879917,幾下這個(gè)id,得出它前后操作的id分別為8879916,8879918
          我們將進(jìn)行位置恢復(fù)操作
          mysqlbinlog --stop-position='8879916' /var/log/mysql-bin.000001 | mysql -uroot -p

          mysqlbinlog 
          --start-position='8879918' /var/log/mysql-bin.000001 | mysql -uroot -p

          第一行是恢復(fù)到停止位置位置的所以事務(wù),第二性是恢復(fù)從給定的起始位置知道二進(jìn)制日志結(jié)束所有事物。

          主從復(fù)制
          mysql的復(fù)制是指將主數(shù)據(jù)庫的DDL和DML操作通過二進(jìn)制日志傳到從服務(wù)器上,然后在從服務(wù)器上對這些日志做重新執(zhí)行的操作,從而使得從服務(wù)器和主服務(wù)器保持?jǐn)?shù)據(jù)的同步,通過二進(jìn)制日志進(jìn)行主從復(fù)制的可以看前一篇《MySQL 主從復(fù)制配置

          ----------------------------------------

          by 陳于喆
          Mail: chenyz@corp.netease.com

          posted on 2010-09-04 18:55 陳于喆 閱讀(17088) 評論(1)  編輯  收藏 所屬分類: mysql

          評論

          # re: 淺析MySql二進(jìn)制日志的應(yīng)用 2016-03-29 11:38 嚴(yán)謹(jǐn)?shù)某绦騿T
          單詞打錯(cuò)了,是expire_logs_days.   回復(fù)  更多評論
            

          主站蜘蛛池模板: 宾阳县| 陆丰市| 宜阳县| 乐都县| 财经| 揭东县| 蚌埠市| 嘉峪关市| 洛宁县| 东至县| 綦江县| 察哈| 蚌埠市| 弋阳县| 进贤县| 宁明县| 永宁县| 平泉县| 肇州县| 佛山市| 丹巴县| 甘泉县| 清水县| 保德县| 丹凤县| 岚皋县| 昭通市| 筠连县| 平湖市| 保定市| 双桥区| 梁平县| 黔东| 黄冈市| 东辽县| 钦州市| 安达市| 宁波市| 义乌市| 永安市| 鹰潭市|