Mysql-12-mysql的備份和恢復

来源:http://www.cnblogs.com/caicairui/archive/2017/08/08/7308662.html
-Advertisement-
Play Games

1.資料庫的備份 備份=拷貝加管理，防止數據的丟失同時記錄用戶的操作記錄。其中最有效的備份是對IT架構進行備份。 原則： （1）資料庫要定期備份，備份的周期應當根據應用數據系統可承受的恢復時間，而且定期備份的時間應當在系統負荷最低的時候進行。對於重要的數據，要保證在極端情況下的損失都可以正常恢復。 ...


1.資料庫的備份

  備份=拷貝加管理，防止數據的丟失同時記錄用戶的操作記錄。其中最有效的備份是對IT架構進行備份。

原則：

（1）資料庫要定期備份，備份的周期應當根據應用數據系統可承受的恢復時間，而且定期備份的時間應當在系統負荷最低的時候進行。對於重要的數據，要保證在極端情況下的損失都可以正常恢復。

（2）定期備份後，同樣需要定期做恢複測試，瞭解備份的正確可靠性，確保備份的有意義的、可恢復的。

（3）根據系統需要來確定是否採用增量備份，增量備份只需要備份每天的增量數據，備份花費的時間少，對系統負載的壓力也小。缺點是恢復的時候需要載入之前所有的備份數據。恢復時間較長。

（4）確保mysql打開了log-bin選項，mysql在做完整恢復或者基於時間點恢復的時候都需要binlog。

（5）可以考慮異地備份。

 

2.邏輯備份和恢復

（1）邏輯備份：也可以成為文件級備份，是將資料庫中的數據備份為一個文本文件，而備份大小取決於文件大小。並且該文本文件是可以移植到其他機器上的，甚至是不同硬體結構的機器。

  • 使用mysqldump命令生成insert語句備份
語法：mysqldump [arguments] >file_name.sql

使用幫助如下：

[root@cai ~]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump –help
例：備份所有資料庫，語句如下：
[root@cai dumpback]# mysqldump -uroot -p123456 --all-databases >/tmp/dumpback/alldb.sql
備份某些資料庫，語法如下：
[root@cai dumpback]# mysqldump -uroot -p123456 --databases aa >/tmp/dumpback/data_aa.sql
備份某資料庫中的表，語句如下：
[root@cai dumpback]# mysqldump -uroot -p123456 aa cairui >/tmp/dumpback/aa_cairui.sql
查看備份內容，語句如下：
[root@cai dumpback]# more aa_cairui.sql

如何保證數據備份的一致性？有以下兩種方法：

①同一時刻取出所有數據

對於事務支持的存儲引擎，如innodb或者bdb等，可以通過控制將整個備份過程在同一事物中，使用 -single-transaction test>test_backup.sql選項

例：[root@cai dumpback]# mysqldump -uroot -p123456 --single-transaction aa >/tmp/dumpback/aa_backup.sql

②資料庫中的數據處於靜止狀態

通過鎖表參數未完成

LOCK-TABLES 每次鎖定一個資料庫的表，此參數預設為true

LOCK-ALL-TABLES 一次鎖定所有的表，適用於dump的表分別處於各個不同的資料庫中的情況。

 

l 生成特定格式的純文本文件備份

①通過select … to outfile from … 命令

參數如下：

fields escaped by [‘name’]：在sql語句中需要轉義的字元

fields terminated by:設定每兩個欄位之間的分隔符

fields [optionally] enclosed by ‘name’:包裝，有optionally數字類型不被包裝，否則全包裝。

lines terminated by ‘name’:行分隔符，即每記錄結束時添加的字元。

例：mysql> select * into outfile '/tmp/tb1.txt'
    -> fields terminated by ','
    -> optionally enclosed by '"'
    -> lines terminated by '\n'
-> from cai limit 50;

②通過mysqldump工具命令導出文本。

用此方法可以生成一個文本數據和一個對應的資料庫結構創建腳本，主要參數：

-T,--tab=name

例：導出sqoop庫的tb1表(http://blog.itpub.net/31386161/viewspace-2130313/錯誤處理方法)

[root@cai ~]# mysqldump -uroot -p123456 -T /tmp sqoop tb1 --fields-enclosed-by=\" --fields-terminated-by=,

（2）邏輯備份的恢復

[root@cai dumpback]# mysql -uroot -p123456 -D cai </tmp/dumpback/aa_cairui.sql

將aa底下的cairui表恢復到cai database下

②純文本文件的恢復

使用mysqllimport工具恢復，此工具可以用於恢復生成txt和sql兩文件，所以保證txt文件對應的資料庫的表存在。

首先恢復表結構，語句如下：
[root@cai tmp]# mysql -uroot -p123456 -D aa </tmp/tb1.sql
恢複數據，語句如下：
[root@cai tmp]# mysqlimport -uroot -p123456 aa --fields-enclosed-by=\" --fields-terminated-by=, /tmp/tb1.txt

 

3.物理備份和恢復

物理備份比邏輯備份速度要快，分為以下兩種：

a。冷備份：這種方式是最直接的備份方式，就是首先停掉資料庫服務，然後cp數據文件，恢復時停止mysql，先進行操作系統級別恢覆文件，然後重啟mysql服務，使用mysqlbinlog工具恢復自備份以來的所有binlog。這種方式雖然簡單，而且對所有引擎支持，但是有一個非常大的弊端就是需要關閉資料庫服務。在當前的大多數信息系統都是不允許長期時間停機的。

b。熱備份：對於不同的存儲引擎方法也不同

 

4.各種備份與恢復方法的具體實現

（1）利用select into outfile實現數據備份與還原

①把需要備份的數據備份出來

mysql> select * from students;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | li   |   36 |
|    2 | wang |   26 |
|    3 | cai  |   12 |

mysql> select * from students where age>30 into outfile '/tmp/students.txt';//將年齡大於30的同學的信息備份出來

發現students。txt是文本文件，不能直接導入資料庫，需要使用load data infile恢復到mysql伺服器端，刪除年齡大於30的用戶，模擬數據被破壞。

mysql> delete from students where age>30;
mysql> load data infile '/tmp/students.txt' into table students;

（2）備份策略：完全備份+增量備份+二進位日誌

①先給資料庫做完全備份

http://www.cnblogs.com/kerrycode/p/4565669.html（錯誤解決辦法）

[root@cai tmp]# mysqldump -uroot -p123456 --single-transaction --master-data=2 --databases hellodb >/backup/hellodb_'data+%F'.sql

②回到mysql伺服器端更新數據

mysql> use hellodb;
Database changed
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb1 values(1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

③先查看完全備份文件裡面記錄的位置

[root@cai backup]# cat hellodb_data+%F.sql | less
-- CHANGE MASTER TO MASTER_LOG_FILE='cai-bin.000001', MASTER_LOG_POS=107;（記錄了二進位日誌的位置）

④回到伺服器端

mysql> show master status;顯示此時的二進位日誌的位置，從備份文件裡邊記錄的位置到我們此時的位置即為增量的部分。
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| cai-bin.000001 |      394 |              |                  |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

⑤做增量備份

[root@cai backup]# mysqlbinlog --start-position=107 --stop-position=394 /application/mysql/data/cai-bin.000001 >/backup/hellodb_'data+$F_%H'.sql

⑥再回到伺服器

mysql> insert into tb1 values(4),(5);
mysql> drop database hellodb;

⑦導出這次二進位日誌

[root@cai backup]# mysqlbinlog --start-position=394 /application/mysql/data/cai-bin.000001 查看刪除操作時二進位日誌的位置
[root@cai backup]# mysqlbinlog --start-position=394 --stop-position=587 /application/mysql/data/cai-bin.000001 >/tmp/hellodb.sql（導出二進位日誌）

⑧先讓mysql離線

mysql>set sql_log_bin=0;關閉二進位日誌
mysql>flush logs;滾動下日誌

⑨模擬資料庫破壞

mysql>drop database hellodb;

⑩開始恢複數據

#mysql </backup/hellodb_2013-09-08.sql導入完全備份文件
#mysql </backup/hellodb_2013-09-08_05.sql導入增量備份文件
#mysql <hellodb.sql//導入二進位文件

（1真正生產環境中，應該導出的是整個mysql伺服器中的數據，而不是單個資料庫，因此應該使用-all-databases。

(2在導出二進位日誌的時候，可以直接複製文件即可，但是要註意的是，備份之前滾動下日誌。

（3利用LVM快照實現幾乎熱備的數據備份與恢復。

 

重要：使用xtrabackup做備份恢復

1.優勢

（1）快速可靠的進行完全備份

（2）在備份的過程中不影響事務

（3）支持數據流、網路傳輸、壓縮，所以它可以有效地節約磁碟資源和網路帶寬。

（4）可以自動備份檢驗數據的可用性。

a。安裝rabackup

[root@cai tools]# tar zxf percona-xtrabackup-2.4.2-Linux-x86_64.tar.gz 
[root@cai tools]# mv percona-xtrabackup-2.4.2-Linux-x86_64 /application/percona-xtrabackup2.4.2
[root@cai tools]# ln -s /application/percona-xtrabackup2.4.2/ /application/xtrabackup
[root@cai tools]#  echo "export PATH=\$PATH:/application/xtrabackup/bin" >> /etc/profile
[root@cai tools]# . /etc/profile

b。全量備份

mysql> create user 'backup'@'%' identified by '123456';創建備份用戶
mysql> grant all on *.* to 'backup'@'%';授權
mysql> flush privileges;使生效
mysql> select * from cairui;
+------+------+
| id   | name |
+------+------+
|    1 | li   |
|    2 | wang |
測試的表為cairui 在cai底下

[root@cai backup]# innobackupex --user=root --password=123456 /data/backup/（備份到backup下）170511 16:46:14 completed OK!表示成功

 

xtrabackup_checkpoints：備份類型、備份狀態和LSN（日誌序列號）範圍信息。

xtrabackup_binlog_info：mysql伺服器當前正在使用的二進位文件及備份這一刻為止的二進位日誌事件的位置。

xtrabackup_logfile：非文本文件，xtrabackup自己的日誌文件。

backup-my.cnf：備份時數據文件中關於mysql的配置。

mysql> delete from bb where age >30;
xtrabackup的增量備份代碼如下：
[root@cai backup]# innobackupex --user=root --password=123456 --incremental /data/backup/--incremental-basedir=/data/backup/2017-05-11_16-57-24

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • iOS繪圖框架CoreGraphics分析
    由於`CoreGraphics`框架有太多的`API`，對於初次接觸或者對該框架不是十分瞭解的人，在繪圖時，對`API`的選擇會感到有些迷茫，甚至會覺得`iOS`的圖形繪製有些繁瑣。因此，本文主要介紹一下`iOS`的繪圖方法和分析一下`CoreGraphics`框架的繪圖原理。 ...
  • iOS 使用矢量圖
    iOS 使用矢量圖 iOS 圖標通常用 PNG 格式的圖片。PNG 圖片放大到超過自身的大小就會模糊。可以使用 PDF 格式的矢量圖，優點是任意改變圖片大小並且保持清晰度。 簡單使用 與 PNG 格式的圖片一樣，在 Xcode 中把 PDF 格式的矢量圖拖進 \ .xcassets (預設是 Ass ...
  • AutoLayout 的一些坑
    1. 給一個 UIView 加約束，希望它顯示在 UITableView 的底部，但是它不顯示，它會出現在 UITableView 的頂部。 錯誤代碼： 如圖： 原因可能是因為 UITableView 的滾動導致的，與 UIScrollView 有一致的地方。 解決： 可以顯示在 self.view ...
  • 監控資料庫DDL操作日誌
    背景 為了監控好生產環境下各個資料庫伺服器上DDL操作日誌，便於運維工程師管控好風險，我們有必要關註當前實例下的所有的DDL操作。 測試環境 Microsoft SQL Server 2012 - 11.0.2218.0 (X64) Jun 12 2012 13:05:25 Copyright (c ...
  • Redis-基本數據類型
    redis redis命令: 1、字元串： set key value//設置 get key //獲取 incr key //原子增長(一次增長1)屬於原子性 incrby key 50 //增長的值自定義 一次設置和檢索多個值 mset key1 value1 key2 value2,..., ...
  • ORA-1652: unable to extend temp segment by 128 in tablespace xxx Troubleshootin
    當收到告警信息ORA-01652: unable to extend temp segment by 128 in tablespace xxxx 時，如何Troubleshooting ORA-1652這樣的問題呢？ 當然一般xxx是臨時表空間，也有可能是用戶表空間。 我們先來模擬一下這個情況，在... ...
  • spring集成mybatis實現mysql讀寫分離
    前言 在網站的用戶達到一定規模後,資料庫因為負載壓力過高而成為網站的瓶頸。幸運的是目前大部分的主流資料庫都提供主從熱備功能，通過配置兩台資料庫主從關係，可以將一臺資料庫的數據更新同步到另一臺伺服器上。網站利用資料庫的這一功能，實現資料庫讀寫分離，從而改善資料庫負載壓力。如下圖所示： 應用伺服器在寫數 ...
  • 處理大文件之記憶體映射
    記憶體映射處理大文件 運行環境： linux 實現語言： C++ 文件大小： 大於10G 1、為什麼要用記憶體映射 a、一般讀寫大文件操作會帶來較多的磁碟IO開銷 b、數據流一次性寫入大量數據到記憶體容易達到記憶體限制 c、效率問題 2、基本概念 2.1 記憶體映射 簡單定義： 一個文件到一塊記憶體的映射。 解 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類