Mysql備份恢復調優 Mysql 備份與恢復 mysqldump,ibbackup,replication 備份分: Hot Backup(熱備) 在線備份,對DB無影響 Cold Backup(冷備) 停止時備份,簡單,拷貝物理文件,離線備份 frm數據庫文件,共享表空間文件,獨立表空間ibd文件,重做日志文件的備份 。 DBA腳本備份,並打包與壓縮。 Warm Backup(溫備) DB運行時備份,對DB操作有影響,會加一個全局讀鎖來保證備份數據的一致性。 邏輯備份是指備份文件內容是可讀的,通常是文本文件,內容一般為SQL語句,可表的實際數據,如mysqldump和select * into OUTFILE。用於數據庫升級,遷移,恢復時間較長。 mysqldump --all-databases > file_name ;備份所有數據庫 mysqldump --databases db1 db2 db3 >dbs.sql ;備份指定的庫 mysqldump --single-transaction test>test_backup.sql; 對test架構進行備份。 select * into OUTFILE ;多用於導出表的數據; select * into outfile 'xxxx' from a; 恢復時用 load data infile 'xxx' into table a; 恢復 mysql -uroot -p<test_backup.sql source ....xxx.sql mysql 可以導出存儲過程,觸發器,事件,數據,但是不能導出視圖。 祼文件備份,指拷貝數據庫物理文件,DB可在運行或者停止狀態。 完全備份 完整備份 增量備份 在上次完全備份的基礎上對更新數據進行備份。 日志備份 對二進制日志備份。它的replication原理就是異步實時進行二進制日志重做。 [mysqld] log-bin 開啟二進制 sync_binlog=1 innodb_support_xa=1 要恢復二進制日志簡單 mysqlbinlog binlog.000001 | mysql -uroot -p test mysqlbinlog binlog.[0-10]* | mysql -uroot -p test 恢復多個 mysqldump中通過添加 -single-transaction選項來取得一致性備份。備份是在一個很長的事務中執行的。 根據自己的策略編寫一個備份的應用程序,可以方便地設置備份的方法及監控備份的結果,且可通過第三方接口實時通知DBA,這樣可做到24*7的備份監控。 DAO(Database Admin Online)系統,DBA可對幾百台mysql數據庫服務器進行備份,監控,硬件,發生問題時飛信接口第一時間通知。 python語言,Django WEB頁面 。 任何時候需要做好遠程備份。 ibbackup 高性能熱備方式 XtraBackup GPL2 免費 linux LVM(Logical Volume Manager,GNU/Linux 邏輯卷管理器)對磁盤分區管理,可將多個磁盤分區建立一個卷組(Volume Group),成為統一的結構。再做LVM快照,非常快。 數據分布 復制不需要太多的帶寬。 讀取負載平衡 多個從服務器,通過DNS的Round-Robin和Linux的LVS實現負載均衡; 復制對備份很有幫助,但是不是備份,不能完全代替備份;通過復制建立的從有助於故障轉移,減少故障的停機時間與恢復時間。 但是擔心主上對數據的誤操作,這時從也跟著處理了,這種情況無法處理。需要對從進行快照處理。從服務器上可以設置權限。 調優 1.選擇合適的CPU; OLTP 對CPU要求不高,復雜的查詢需要的比較,排序,連接等耗CPU操作少。OLAP是CPU密集型的操作。OLTP是I/O密集型操作。 2.內存重要; Percona 的Vadim 對內存的影響作了比較,內存大小最能直接反映數據庫的性能。2GB->22GB 緩沖池的增大,性能極大提升。 mysql> show global status like 'innodb%read%';\G +---------------------------------------+---------+ | Variable_name | Value | +---------------------------------------+---------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 0 | 從物理磁盤預讀的次數 | Innodb_buffer_pool_read_ahead_evicted | 0 | 預計的頁,但是沒有被讀取就從緩沖池中被替換的頁的數量,一般用來判斷預讀的效率 | Innodb_buffer_pool_read_requests | 6592 | 從緩沖池中讀取頁的次數 | Innodb_buffer_pool_reads | 396 | 從物理磁盤讀取頁的次數 | Innodb_data_pending_reads | 0 | | Innodb_data_read | 8671232 | 總共讀入的字節數 | Innodb_data_reads | 408 | 發起讀取請求的次數,每次讀取可能需要讀取多個頁。 | Innodb_pages_read | 395 | | Innodb_rows_read | 0 | +---------------------------------------+---------+ 通常緩沖池的命中率不應該小於99%。即使緩沖池的大小大於數據庫文件大小,也並不意味著無磁盤讀寫了。緩沖池只用來存放熱點數據。 緩沖池的命中率 = Innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_requests+Innodb_buffer_pool_read_ahead +Innodb_buffer_pool_reads ) 平均每次讀取的字節數=Innodb_data_read/Innodb_data_reads 3.硬盤對數據庫性能影響; 當前大多為傳統的機械硬盤,此技術十分成熟,SAS或SATA接口硬盤。二個指標:尋道時間與轉速。3ms的最短尋道時間,15000 rpm。最大問題是讀寫磁頭。 隨機訪問會耗費長時間的磁頭旋轉和定位來查找,因此順序的速度遠高於隨機訪問 。 RAID提高數據庫的性能,數據文件分布不同硬盤上達到負載均衡。 固態硬盤,基於閃存,低延遲低功耗與防震,TB級別存儲方案,無磁善頭。閃存中數據是不可以更新的,只能通過扇區的覆蓋重寫,在處理前需要耗時的擦除操作。 閃存的讀寫是非對稱的,讀遠遠快於寫。 4.合理地設置RAID; RAID:增強數據集成度,增強容錯功能,增加處理量或容量。 RAID 0 :合並硬盤,無冗余,並行 I/O,速度最快。 如一個磁盤損壞,則所有數據丟失。 RAID 1 :二組以上N個磁盤互為鏡像,性能最高,主硬盤損壞時,鏡像硬盤代替工作。數據安全性最高,但是磁盤利用率最低。 RAID 5 :存儲性能,數據安全和存儲成本兼顧的解決方案。用Disk Striping(磁盤分區)技術。至少要三個硬盤。不備份,而是存儲奇偶校驗信息和數據分別存儲不同硬盤,用剩下的數據及奇偶校驗來恢復。 與RAID0有相同的讀速度,但是因奇偶較驗,故寫入慢。用write Back(將寫入的數據放入自身的緩存中)可提高性能。 5.操作系統也很重要; OS,注意windows不區分大小寫,而linux區分; 系統對內存的支持。 6.不同文件系統對數據庫影響; windows NTFS,Solaris ZFS,Linux EXT3/4等。 7.選擇合適的基准測試工具。 sysbench(CPU性能,磁盤IO性能,調度程序性能,內存分配及傳輸速度,POSIX線程性能,OLTP基准測試)