Linux命令：MySQL系列之十四--MySQL備份與還原（LVM邏輯卷快照備份重要章節）

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

 前提：

      1、數據文件要在邏輯卷上；

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

      3、數據文件和事務日志要在同一個邏輯卷上；

步驟：

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

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

       mysql> FLUSH LOGS;   #滾動日志

2、通過另一個終端，保存二進制日志文件及相關位置信息；

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

#不登錄mysql客戶端直接查看位置狀態，并保存位置信息到相應目錄

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個或以上的日志文件，需要通過指定開始時間點來備份。

  實例：通過實際操作，實現lvm邏輯卷快照備份及恢復mysql操作；

        首先打開mysql客戶端：         

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

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

Your MySQL connection id is 11

Server version: 5.5.28-log Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

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> 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;  #查看二進制日志位置狀態

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000005 |      107 |              |                  |

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

1 row in set (0.00 sec)

不要退出或關閉此mysql客戶端，重新打開一個服務器終端，執行位置信息的保存；

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

[root@lamp ~]# mysql -e 'SHOW MASTER STATUS\G;' > /backup/master-`date +%F`.info  #不登錄mysql客戶端，直接通過mysql -e直接編輯數據庫，把查看二進制日志位置狀態保存至備份目錄下

[root@lamp ~]# ls /backup/

master-2017-06-06.info

對mysql數據目錄執行快照（所以說通過邏輯卷備份數據庫，前提必須是數據文件存放在邏輯卷上）

[root@lamp ~]# lvcreate -L 50M -s -p r -n mydata-snap /dev/myvg/mydata  #對/dev/myvg/mydata邏輯卷做快照，大小為50M，-s:表示snapshot快照，-p:指定權限permission

r:讀的權限read,-n:指定快照卷的名稱。

  Rounding up size to full physical extent 52.00 MiB

  Logical volume "mydata-snap" created

[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)

執行完快照后，即可解鎖表；

mysql> UNLOCK TABLES;    #解鎖

Query OK, 0 rows affected (0.00 sec)

對快照進行掛載并備份；

[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

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

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

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

hellodb  ib_logfile0  jiaowu    lamp.pid  mysql               stu   testdb

ibdata1  ib_logfile1  lamp.err  mydb      performance_schema  test

root@lamp ~]# cd /mydata/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]# cat /backup/master-2017-06-06.info 

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

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

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

    Binlog_Do_DB: 

Binlog_Ignore_DB: 

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

Query OK, 1 row affected (0.00 sec)

mysql> SHOW MASTER STATUS;  #查看此時二進制日志的位置狀態信息 

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000006 |      575 |              |                  |

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

1 row in set (0.00 sec)

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

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

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

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> 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為打開

Query OK, 0 rows affected (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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 |

|  10 | stu0003      | M      | NULL |

|  11 | stu0004      | M      | NULL |

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

11 rows in set (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;  #查看此時的二進制日志位置狀態信息

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |      107 |              |                  |

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

1 row in set (0.00 sec)

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