Mysql備份－－之LVM邏輯卷快照備份

Mysql資料庫理論基礎之十三 ---- LVM邏輯卷快照備份

2017-08-12 13:50:02

一、簡介

由MySQL AB公司開發，是最流行的開放源碼SQL資料庫管理系統，主要特點：

1、是一種資料庫管理系統

2、是一種關聯資料庫管理系統

3、是一種開放源碼軟體，且有大量可用的共享MySQL軟體

4、MySQL資料庫伺服器具有快速、可靠和易于使用的特點

5、MySQL伺服器工作在用戶端/伺服器模式下，或嵌入式系統中

InnoDB存儲引擎将InnoDB表儲存在一個表空間内，該表空間可由數個檔案建立。這樣，表的大小就能超過單獨檔案的最大容量。表空間可包括原始磁盤分區，進而使得很大的表成為可能。表空間的最大容量為64TB。

2.1.通過LVM邏輯卷實作MySQL備份及還原（幾乎熱備）：

前提：

1、資料檔案要在邏輯卷上；

2、此邏輯卷所在卷組必須有足夠的空間使用快照卷；

3、資料檔案和事務日志要在同一個邏輯卷上；

2.2.LVM快照備份步驟：

2.2.1、打開會話，施加讀鎖，鎖定所有表；

mysql> FLUSH TABLES WITH READ LOCK; #重新整理表并對表施加讀鎖

mysql> FLUSH LOGS; #滾動日志

2.2.2、通過另一個終端，儲存二進制日志檔案及相關位置資訊；

[root@lamp ~]# mysql -uroot -p -e 'SHOW MASTER STATUS\G' > /path/to/master.info

#不登入mysql用戶端直接檢視位置狀态，并儲存位置資訊到相應目錄

[root@lamp ~]# cat /tmp/master.info

*************************** 1. row ***************************

File: mysql-bin.000005

Position: 107

Binlog_Do_DB:

Binlog_Ignore_DB:

2.2.3、建立快照卷

[root@lamp ~]# lvcreate -L SIZE -s -p r -n LV_NAME /path/to/source_lv #建立快照卷

-s: 指定為所建立的是快照卷s=snapshot快照; -p: 指定權限p=permission許可；

r: 讀權限; -n: 指定快照名稱； LV_NAME: 快照名稱;

/path/to/source_lv: 針對哪個邏輯卷目錄； -L: 指定快照卷大小

4、釋放鎖

mysql> UNLOCK TABLES; #釋放鎖

5、挂載快照卷，并備份；

mount /dev/myvg/mydata-snap /mnt -o ro #挂載快照卷至/mnt目錄，隻讀挂載

cp -a ./* /backup/full-backup-2017-06-06/ #挂載後把資料複制到備份目錄， -a:表示

複制檔案的所有屬性及内容，保留源檔案的所有屬性及權限

6、删除快照卷

umount /mnt #備份完後解除安裝快照卷挂載的目錄

lvremove --force /dev/myvg/mydata-snap #強制移除快照卷

rm -rf mysql-bin.* #把備份目錄中的日志檔案删除掉，節省空間

7、增量備份二進制日志

mysqlbinlog --start-datetime='2017-06-06 10:11:02' mysql-bin.000005 mysql-bin.000006 > /backup/incremental-`date +%F-%H-%M-%S`.sql #備份二進制日志，如果增量備份的二進制日志

含有2個或以上的日志檔案，需要通過指定開始時間點來備份。

2.3.LVM快照備份執行個體示範

2.3.1.首先打開mysql用戶端：

[root@lamp ~]# mysql #打開mysql用戶端

mysql> FLUSH TABLES WITH READ LOCK; #首先重新整理表并施加讀鎖

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH LOGS; #滾動日志

Query OK, 0 rows affected (0.06 sec)

mysql> SHOW MASTER STATUS; #檢視二進制日志位置狀态

+------------------+----------+--------------+------------------+

| mysql-bin.000005 | 107 | | |

1 row in set (0.00 sec)

2.3.2.不退出或關閉此mysql用戶端，重新打開一個伺服器終端，執行位置資訊的儲存；

[root@lamp ~]# mkdir /backup #建立一個備份目錄

[root@lamp ~]# mysql -e 'SHOW MASTER STATUS\G;' > /backup/master-`date +%F`.info

#不登入mysql用戶端，直接通過mysql -e直接編輯資料庫，把檢視二進制日志位置狀态備份

[root@lamp ~]# ls -lh /backup/

total 4.0K

-rw-r--r--. 1 root root 158 Jun 22 15:02 master-2017-06-22.info

2.3.3.對mysql資料目錄執行快照（資料檔案必須是存放在邏輯卷上）

[root@lamp ~]# lvcreate -L 50M -s -p r -n mydata-snap /dev/myvg/mydata

Rounding up size to full physical extent 52.00 MiB

Logical volume "mydata-snap" created

#對/dev/myvg/mydata邏輯卷做快照，大小為50M，

-s:表示snapshot快照; -p:指定權限permission；r:讀的權限read; -n:指定快照卷的名稱

[root@lamp ~]# lvs #檢視邏輯卷，剛建立的快照邏輯卷

LV VG Attr LSize Origin Snap% Move Log Copy% Convert

mydata myvg owi-ao 10.00g

mydata-snap myvg sri-a- 52.00m mydata 0.02

[root@lamp ~]# mount #檢視挂載相關資訊

/dev/sda7 on / type ext4 (rw)

proc on /proc type proc (rw)

sysfs on /sys type sysfs (rw)

devpts on /dev/pts type devpts (rw,gid=5,mode=620)

tmpfs on /dev/shm type tmpfs (rw,rootcontext="system_u:object_r:tmpfs_t:s0")

/dev/sda1 on /boot type ext4 (rw)

/dev/sda3 on /home type ext4 (rw)

/dev/sda5 on /tmp type ext4 (rw)

/dev/sda2 on /usr/local type ext4 (rw)

/dev/mapper/myvg-mydata on /mydata type ext4 (rw)

none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)

sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)

2.3.3.執行完快照後，即可解鎖表；

mysql> UNLOCK TABLES; #解鎖

對快照進行挂載并備份；

[root@lamp ~]# mount /dev/myvg/mydata-snap /mnt -o ro #以隻讀方式挂載剛做的快照卷

[root@lamp ~]# cd /mnt #進入挂載目錄

[root@lamp mnt]# ls

data lost+found

[root@lamp mnt]# cd data/

[root@lamp data]# ls

hellodb ib_logfile1 lamp.pid mysql-bin.000001 mysql-bin.000004 performance_schema testdb ibdata1 jiaowu mydb mysql-bin.000002 mysql-bin.000005 stu

ib_logfile0 lamp.err mysql mysql-bin.000003 mysql-bin.index test

[root@lamp data]# mkdir /backup/full-backup-`date +%F` #建立備份目錄并以時間格式命名

[root@lamp data]# cp -a ./* /backup/full-backup-2017-06-06/ #-a:複制目前目錄的所有内容及其權限屬性到備份的目錄

[root@lamp data]# cd

[root@lamp ~]# umount /mnt #解除安裝挂載的目錄/mnt

[root@lamp ~]# lvremove --force /dev/myvg/mydata-snap #移除剛才建立的快照卷--force強制性

Logical volume "mydata-snap" successfully removed

[root@lamp ~]# cd /backup/full-backup-2017-06-06/

[root@lamp full-backup-2017-06-06]# ls

hellodb ib_logfile1 lamp.pid mysql-bin.000001 mysql-bin.000004 performance_schema testdb ibdata1 jiaowu mydb mysql-bin.000002 mysql-bin.000005 stu

[root@lamp full-backup-2017-06-06]# rm -rf mysql-bin.* #删除相關的二進制日志檔案以便節省空間

hellodb ib_logfile0 jiaowu lamp.pid mysql stu testdb

ibdata1 ib_logfile1 lamp.err mydb performance_schema test

root@lamp ~]# cd /mydata/data

hellodb ib_logfile1 lamp.pid mysql-bin.000001 mysql-bin.000004 performance_schema testdb ibdata1 jiaowu mydb mysql-bin.000002 mysql-bin.000005 stu

[root@lamp data]# cat /backup/master-2017-06-06.info

File: mysql-bin.000005 #記錄的二進制日志檔案

Position: 107 #二進制日志的位置

mysql> FLUSH LOGS; #滾動日志

Query OK, 0 rows affected (0.01 sec)

mysql> USE jiaowu

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> INSERT INTO tutors(Tname) VALUES ('stu0003'); #往表tutors中插入資料,字段Tname 值stu0003

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tutors(Tname) VALUES ('stu0004'); #往表tutors中插入資料,字段Tname 值stu0004

mysql> SHOW MASTER STATUS; #檢視此時二進制日志的位置狀态資訊

| mysql-bin.000006 | 575 | | |

mysql> \q

Bye

[root@lamp data]# mysqlbinlog --start-datetime='2017-06-06 10:11:02' mysql-bin.000005 mysql-bin.000006 > /backup/incremental-`date +%F-%H-%M-%S`.sql

#導出二進制日志檔案bin.000005和bin.000006時間點從2017-06-06 10:11:02開始後的内容

[root@lamp data]# ls /backup/incremental-2017-06-06-17-01-41.sql

/backup/incremental-2017-06-06-17-01-41.sql

[root@lamp data]# service mysqld stop #停止mysqld程序

Shutting down MySQL.. [ OK ]

[root@lamp data]# rm -rf ./* #模拟mysql資料庫資料目錄内容丢失（手動删除）

[root@lamp data]# ls #目錄中的内容全部删除，檢視顯示為空

[root@lamp data]# cp -a /backup/full-backup-2017-06-06/* ./ #複制之前快照卷備份的内容至目前資料目錄，-a:複制檔案的内容及權限屬性。

[root@lamp data]# ll #確定所有檔案的屬主為mysql使用者

total 28712

drwx------. 2 mysql mysql 4096 Jun 2 15:30 hellodb

-rw-rw----. 1 mysql mysql 18874368 Jun 5 14:00 ibdata1

-rw-rw----. 1 mysql mysql 5242880 Jun 5 14:00 ib_logfile0

-rw-rw----. 1 mysql mysql 5242880 Jun 2 15:28 ib_logfile1

drwx------. 2 mysql mysql 4096 Jun 5 14:00 jiaowu

-rw-rw----. 1 mysql root 1853 Jun 2 15:28 lamp.err

-rw-rw----. 1 mysql mysql 6 Jun 2 15:28 lamp.pid

drwx------. 2 mysql mysql 4096 Jun 2 15:30 mydb

drwx------. 2 mysql root 4096 Jun 2 15:30 mysql

drwx------. 2 mysql mysql 4096 Jun 2 15:28 performance_schema

drwx------. 2 mysql mysql 4096 Jun 2 15:30 stu

drwx------. 2 mysql root 4096 Jun 2 15:28 test

drwx------. 2 mysql mysql 4096 Jun 2 15:30 testdb

[root@lamp data]# service mysqld start #開啟mysqld程序

Starting MySQL [ OK ]

[root@lamp data]# mysql -uroot -p #登入mysql用戶端

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.5.28-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE jiaowu

mysql> SELECT * FROM tutors; #查詢tutors表的内容（此時後插入的2行資料沒有）

+-----+--------------+--------+------+

| TID | Tname | Gender | Age |

| 1 | HongQigong | M | 93 |

| 2 | HuangYaoshi | M | 63 |

| 3 | Miejueshitai | F | 72 |

| 4 | OuYangfeng | M | 76 |

| 5 | YiDeng | M | 90 |

| 6 | YuCanghai | M | 56 |

| 7 | Jinlunfawang | M | 67 |

| 8 | HuYidao | M | 42 |

| 9 | NingZhongze | F | 49 |

9 rows in set (0.00 sec)

mysql> SET sql_log_bin=0; #導入二進制日志前，先關閉日志記錄功能0為關閉，1為打開

mysql> SOURCE /backup/incremental-2017-06-06-17-01-41.sql #導入備份的二進制日志資料

mysql> SELECT * FROM tutors; #再次查詢tutors表的内容，此時後插入的2行資料已經生成

| TID | Tname | Gender | Age |

| 10 | stu0003 | M | NULL |

| 11 | stu0004 | M | NULL |

11 rows in set (0.00 sec)

mysql> SET sql_log_bin=1; #二進制日志導入後再打開二進制日志記錄功能

mysql> SHOW MASTER STATUS; #檢視此時的二進制日志位置狀态資訊

| mysql-bin.000001 | 107 | | |

至此通過lvm邏輯卷和二進制cp功能實作了mysql資料庫備份和還原。

本文轉自rshare 51CTO部落格，原文連結：http://blog.51cto.com/1364952/1956229，如需轉載請自行聯系原作者

Mysql備份－－之LVM邏輯卷快照備份

繼續閱讀

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

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

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

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

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

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List