Mysql之一：mysqldump和LVM邏輯卷快照

#####################################

常見的資料備份類型和備份工具

mysqldump備份工具

使用LVM邏輯卷快照備份

資料的重要性我就不多說了……，常見的資料備份類型是根據不同的備份性質和要求來劃分的，他們分别是：根據業務是否可線上、根據實體和邏輯、根據備份範圍，下面就來簡單的掃盲一下：

根據業務是否可線上

熱備份：讀寫操作不受影響

溫備份：僅可以執行讀操作

冷備份：離線備份，讀寫操作均不能進行

根據實體和邏輯

實體備份：直接複制資料檔案，速度快(在檔案系統級别完成，不需要mysql程序參與)，移植性差。

邏輯備份：将資料導出至文本檔案中，速度慢(需要mysql程序參數)，移植性好，丢失浮點數精度，可以友善使用文本處理工具。

根據備份範圍

完全備份：備份全部資料

增量備份：僅備份上次完全備份或增量備份以後變化的資料(比增量備份多占據空間，但是還原的時候友善)

差異備份：僅備份上次完全備份以來變化的資料

這裡特意盜用一幅圖來更加直覺的闡述增量備份和差異備份的差別：

完全+增量(還原時間長，複雜，占用空間小)

完全+差異(還原時間短，簡單，占用空間大)

他們之間沒有限制，比如完全線上實體備份……

另外備份出的資料要經常測試是否可用，和制定好緊急還原方案………

備份的對象有資料、配置檔案、二進制日志、事務日志等……

各個備份工具功能和性能對比如下圖所示：

備份政策：周完全+每日增量(二進制日志)

完全備份

<code>mysql> create database soulboy;</code>

<code>Query OK, 1 row affected (0.00 sec)</code>

<code>mysql> use soulboy;</code>

<code>Database changed</code>

<code>mysql> create table tb1 (</code><code>id</code> <code>int auto_increment primary key,name char(30));</code>

<code>Query OK, 0 rows affected (0.01 sec)</code>

<code>mysql></code>

<code>mysql> insert into tb1 (name) values (</code><code>'kobe'</code><code>);</code>

<code>mysql> </code><code>select</code> <code>* from tb1; </code><code>#新庫</code>

<code>完全備份，比例中表類型是InnoDB</code>

<code>mysqldump -uroot -p --master-data=2 --single-transaction --all-databases > </code><code>/backup/alldatabases</code><code>.sql</code>

<code>mysqldum隻能對MyISAM引擎做溫備份，備份之前必須鎖定表,如下：</code>

<code>mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > </code><code>/root/alldatabases</code><code>.sql</code>

<code>##########################mysqldump選項###########################</code>

<code>-u </code><code>#指定使用者名</code>

<code>-p </code><code>#指定使用者密碼</code>

<code>-h </code><code>#指定主機位址</code>

<code>-A|--all-databases </code><code>#備份所有資料庫</code>

<code>--databases </code><code>#備份指定資料庫</code>

<code>--single-transcation </code><code>#基于此項可以實作對InnoDB表做熱備份，但不需要使用</code>

<code>--lock-all-tables </code><code>#執行備份時為所有表請求加鎖</code>

<code>-E|--events </code><code>#備份事件排程器代碼</code>

<code>--opt </code><code>#同時啟動各種進階選項</code>

<code>-R|--routines </code><code>#備份存儲過程和存儲函數</code>

<code>--flush-logs </code><code>#備份之前重新整理日志</code>

<code>--triggers </code><code>#備份觸發器</code>

<code>--master-data=2 </code><code>#該選項将會記錄binlog的日志位置與檔案名并追加到檔案中，如果為1将會輸出CHANGE</code>

第一天

<code>mysql> insert into tb1 (name) values (</code><code>'wade'</code><code>);</code>

<code>mysql> </code><code>select</code> <code>* from tb1;</code>

<code>####滾動二進制日志，進入第二天</code>

<code>mysql> FLUSH LOGS; </code>

<code>####備份第一天二進制日志為SQL格式并重命名為firstdays.sql</code>

<code>mysqlbinlog </code><code>/mydata/data/mysql-bin</code><code>.000008 > </code><code>/backup/firstday</code><code>.sql</code>

第二天

<code>#####插入新資料</code>

<code>mysql> insert into tb1 (name) values (</code><code>'nash'</code><code>);</code>

<code>#####備份第二天二進制日志為SQL格式并重命名為secondday.sql</code>

<code>#####注意生産環境中務必将二進制日志存儲在不同于資料目錄的裝置中</code>

<code> </code><code>mysqlbinlog </code><code>/mydata/data/mysql-bin</code><code>.000009 > </code><code>/backup/secondday</code><code>.sql</code>

模拟故障

<code>cd</code> <code>/mydata/data</code>

<code>#####删除資料目錄中所有檔案</code>

<code>cd</code> <code>/usr/local/mysql</code>

<code>#####初始化資料庫</code>

<code>scripts</code><code>/mysql_install_db</code> <code>--user=myslq --datadir=</code><code>/mydata/data/</code>

<code>#####殺死僵死程序</code>

<code>ps</code> <code>aux | </code><code>grep</code> <code>mysql</code>

<code>service mysqld restart</code>

資料還原

<code>#####檢視目前使用的二進制日志檔案以及所處位置</code>

<code>show master status;</code>

<code>#####禁止還原過程中記錄二進制日志</code>

<code>mysql> </code><code>set</code> <code>sql_log_bin=0;</code>

<code>#####還原完全備份</code>

<code>mysql> </code><code>source</code> <code>/backup/alldatabases</code><code>.sql;</code>

<code>#####還原第一天備份</code>

<code>mysql> </code><code>source</code> <code>/backup/firstday</code><code>.sql;</code>

<code>#####還原第二天備份，全部資料完全到此結束</code>

<code>mysql> </code><code>source</code> <code>/backup/secondday</code><code>.sql;</code>

<code>#####開啟二進制日志記錄功能</code>

<code>mysql> </code><code>set</code> <code>sql_log_bin=1;</code>

<code>#####測試資料是否還原成功</code>

鎖表并滾動日志

<code>mysql> FLUSH TABLES WITH READ LOCK;</code>

<code>#####滾動二進制日志檔案</code>

<code>mysql> FLUSH LOGS;</code>

<code>#####即時點恢複時候需要使用，記錄LVM時刻二進制日志檔案和所處位置</code>

<code>mysql -e </code><code>'SHOW MASTER STATUS\G'</code> <code>> </code><code>/backup/master-</code><code>`</code><code>date</code> <code>+%F`.info</code>

資料目錄和事務檔案建立LVM快照

<code>#####對mydata目錄進行快照</code>

<code>lvcreate -L 200M -s -p r -n mydata-snap </code><code>/dev/myvg/mydata</code>

<code>#####以隻讀方式挂載鏡像檔案</code>

<code>mount</code> <code>/dev/myvg/mydata-snap</code> <code>/mnt</code> <code>-o ro</code>

<code>#####将鏡像檔案内容複制到/save目錄中</code>

<code>mkdir</code> <code>/save</code>

<code>#####删除鏡像檔案</code>

<code>umount</code> <code>/mnt</code>

<code>lvremove --force </code><code>/dev/myvg/mydata-snap</code>

<code>#####删除二進制日志檔案</code>

<code>rm</code> <code>-rf </code><code>/save/mysql-bin</code><code>.*</code>

解鎖并插入資料

<code>mysql> UNLOCK TABLES;</code>

<code>#####檢視目前表内容</code>

<code>#####插入新資料，模拟第一天</code>

<code>insert into tb1 (name) values (</code><code>'firstday'</code><code>);</code>

<code>#####滾動二進制日志檔案，模拟第二天</code>

<code>flush logs; </code>

<code>insert into tb1 (name) values (</code><code>'sencondday'</code><code>);</code>

檢視快照時間和目前二進制日志資訊

<code>#####檢視快照時刻的二進制日志記錄資訊</code>

<code>cat</code> <code>/backup/master-2013-09-19</code><code>.info </code>

<code> </code><code>File: mysql-bin.000005</code>

<code> </code><code>Position: 107</code>

<code> </code><code>Binlog_Do_DB:</code>

<code>Binlog_Ignore_DB:</code>

<code>#####檢視使用目前二進制日志和所處位置 </code>

<code>mysql> SHOW MASTER STATUS; </code>

基于多個二進制日志生成單個SQL格式檔案

<code>#####檢視快照時刻所處位置的時間點</code>

<code>[root@localhost save]</code><code># mysqlbinlog --start-position=107 /mydata/data/mysql-bin.000005</code>

<code>/*!40019 SET @@session.max_insert_delayed_threads=0*/;</code>

<code>/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;</code>

<code>DELIMITER /*!*/;</code>

<code>#130919 11:46:29 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.28-log created 130919 11:46:29 #時間</code>

<code>#####基于時間做限定來合并多個日志檔案為一個SQL格式的檔案</code>

<code>mysqlbinlog --start-datetime=</code><code>'2013-09-19 11:46:29'</code> <code>/mydata/data/mysql-bin</code><code>.000005 </code><code>/mydata/data/mysql-bin</code><code>.000006 > </code><code>/backup/incremental-</code><code>`</code><code>date</code> <code>+%F`.sql</code>

<code>service mysqld stop</code>

<code>#####删除數目目錄下所有檔案</code>

<code>rm</code> <code>-rf </code><code>/mydata/data/</code><code>*</code>

還原快照時刻資料

<code>#####複制快照時刻資料到資料目錄</code>

<code>cp</code> <code>-a </code><code>/save/</code><code>* </code><code>/mydata/data/</code>

<code>service mysqld start</code>

<code>#####檢視目前表内資料</code>

還原增量資料

<code>#####關閉二進制日志記錄功能</code>

<code>#####還原增量備份的資料</code>

<code>mysql> </code><code>source</code> <code>/backup/incremental-2013-09-19</code><code>.sql</code>

<code>#####檢視表内資料，還原成功</code>

<code>| 4 | firstday |</code>

<code>| 5 | sencondday |</code>

本文轉自 ftmoonfans 51CTO部落格，原文連結:http://blog.51cto.com/soulboy/1298999

Mysql之一：mysqldump和LVM邏輯卷快照

繼續閱讀

SQL優化SQL語句優化的目的

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離