基于邏輯卷LVM的MySQL、mariadb數據庫備份還原詳細實現

前提是數據庫的數據是放在邏輯卷上的；
數據庫數據和日志分開存放；正常情況下數據和日志是放在兩個獨立的磁盤上，如果是raid的話，那么就無所謂了。

創建分區：

        [root@mysql ~]$fdisk /dev/sda

        Command (m for help): n
        All primary partitions are in use
        Adding logical partition 6
        First sector (153098240-419430399, default 153098240): 
        Using default value 153098240
        Last sector, +sectors or +size{K,M,G} (153098240-419430399, default 419430399): +10G
        Partition 6 of type Linux and of size 10 GiB is set

        Command (m for help): t
        Partition number (1-6, default 6): 6
        Hex code (type L to list all codes): 8e
        Changed type of partition 'Linux' to 'Linux LVM'

        Command (m for help): w
        The partition table has been altered!

        Calling ioctl() to re-read partition table.

        WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
        The kernel still uses the old table. The new table will be used at
        the next reboot or after you run partprobe(8) or kpartx(8)
        Syncing disks.

        [root@mysql ~]$partprobe 
        Warning: Unable to open /dev/sr0 read-write (Read-only file system).  /dev/sr0 has been opened read-only.
        [root@mysql ~]$lsblk
        NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
        sda      8:0    0  200G  0 disk 
        ├─sda1   8:1    0    1G  0 part /boot
        ├─sda2   8:2    0   50G  0 part /
        ├─sda3   8:3    0   20G  0 part /app
        ├─sda4   8:4    0  512B  0 part 
        ├─sda5   8:5    0    2G  0 part [SWAP]
        └─sda6   8:6    0   10G  0 part 
        sr0     11:0    1  8.1G  0 rom  
        loop0    7:0    0  8.1G  1 loop /mnt/cdrom

創建PV，vg，lv：

加入PV、VG和LV；

[root@mysql ~]$pvcreate /dev/sda6
  Physical volume "/dev/sda6" successfully created.
[root@mysql ~]$pvs
  PV         VG Fmt  Attr PSize  PFree 
  /dev/sda6     lvm2 ---  10.00g 10.00g
[root@mysql ~]$vgcreate vg0 /dev/sda6
  Volume group "vg0" successfully created
[root@mysql ~]$vgs
  VG  #PV #LV #SN Attr   VSize   VFree  
  vg0   1   0   0 wz--n- <10.00g <10.00g
[root@mysql ~]$vgdisplay 
  --- Volume group ---
  VG Name               vg0
  System ID             
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  1
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                0
  Open LV               0
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               <10.00 GiB
  PE Size               4.00 MiB
  Total PE              2559
  Alloc PE / Size       0 / 0   
  Free  PE / Size       2559 / <10.00 GiB
  VG UUID               fuGxOy-IVrf-SnWd-C0ie-eb9O-LIWz-sMx17T


[root@mysql ~]$pvs
  PV         VG  Fmt  Attr PSize   PFree  
  /dev/sda6  vg0 lvm2 a--  <10.00g <10.00g



創建的LV，剩余的空間用于放置快照數據；
[root@mysql ~]$lvcreate -n mysqldata -L 2G vg0 
  Logical volume "mysqldata" created.
[root@mysql ~]$lvcreate -n binlogs -L 3G vg0 
  Logical volume "binlogs" created.

[root@mysql ~]$pvs
  PV         VG  Fmt  Attr PSize   PFree 
  /dev/sda6  vg0 lvm2 a--  <10.00g <5.00g
[root@mysql ~]$vgs
  VG  #PV #LV #SN Attr   VSize   VFree 
  vg0   1   2   0 wz--n- <10.00g <5.00g


格式化文件系統：
[root@mysql ~]$mkfs.xfs /dev/vg0/mysqldata 
meta-data=/dev/vg0/mysqldata     isize=512    agcount=4, agsize=131072 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=0, sparse=0
data     =                       bsize=4096   blocks=524288, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal log           bsize=4096   blocks=2560, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
[root@mysql ~]$mkfs.xfs /dev/vg0/binlogs 
meta-data=/dev/vg0/binlogs       isize=512    agcount=4, agsize=196608 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=0, sparse=0
data     =                       bsize=4096   blocks=786432, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal log           bsize=4096   blocks=2560, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
[root@mysql ~]$
[root@mysql ~]$blkid
/dev/sda1: UUID="07deeea1-2041-4e34-98ba-2529dfb30c32" TYPE="xfs" 
/dev/sda2: UUID="a7595dc1-7958-4728-954b-e8dcfb6bca3c" TYPE="xfs" 
/dev/sda3: UUID="3c26d76c-a6a6-4c40-90fd-c2a38520b674" TYPE="xfs" 
/dev/sda5: UUID="7f480b58-5216-4561-a933-43766aa0ff05" TYPE="swap" 
/dev/sda6: UUID="1dGdT7-kPEX-pLCH-id8y-0269-244Y-3hiJcW" TYPE="LVM2_member" 
/dev/sr0: UUID="2017-09-06-10-53-42-00" LABEL="CentOS 7 x86_64" TYPE="iso9660" PTTYPE="dos" 
/dev/loop0: UUID="2017-09-06-10-53-42-00" LABEL="CentOS 7 x86_64" TYPE="iso9660" PTTYPE="dos" 
/dev/mapper/vg0-mysqldata: UUID="6f9f0f27-dba5-4479-adb6-532362d80d38" TYPE="xfs" 
/dev/mapper/vg0-binlogs: UUID="150de97a-7a76-465e-9d6a-1357600fa152" TYPE="xfs"

提供數據目錄，掛載：

[root@mysql ~]$mkdir /data/{mysqldata,binlogs} -pv
mkdir: created directory ‘/data’
mkdir: created directory ‘/data/mysqldata’
mkdir: created directory ‘/data/binlogs’
[root@mysql ~]$ll /data/
total 0
drwxr-xr-x 2 root root 6 Feb 25 10:07 binlogs
drwxr-xr-x 2 root root 6 Feb 25 10:07 mysqldata


[root@mysql ~]$vim /etc/fstab
...
UUID=6f9f0f27-dba5-4479-adb6-532362d80d38   /data/mysqldata/    xfs     defaults    0   0
UUID=150de97a-7a76-465e-9d6a-1357600fa152   /data/binlogs/      xfs     defaults    0   0



[root@mysql ~]$mount -a
[root@mysql ~]$df -Ph
Filesystem                 Size  Used Avail Use% Mounted on
/dev/sda2                   50G  3.5G   47G   7% /
devtmpfs                   474M     0  474M   0% /dev
tmpfs                      489M     0  489M   0% /dev/shm
tmpfs                      489M  7.2M  482M   2% /run
tmpfs                      489M     0  489M   0% /sys/fs/cgroup
/dev/sda3                   20G   33M   20G   1% /app
/dev/loop0                 8.1G  8.1G     0 100% /mnt/cdrom
/dev/sda1                 1014M  158M  857M  16% /boot
tmpfs                       98M     0   98M   0% /run/user/0
/dev/mapper/vg0-mysqldata  2.0G   33M  2.0G   2% /data/mysqldata
/dev/mapper/vg0-binlogs    3.0G   33M  3.0G   2% /data/binlogs
[root@mysql ~]$ll /data/
total 0
drwxr-xr-x 2 root root 6 Feb 25 10:05 binlogs
drwxr-xr-x 2 root root 6 Feb 25 10:05 mysqldata

[root@mysql ~]$chown -R mysql.mysql /data/
[root@mysql ~]$ll /data/
total 0
drwxr-xr-x 2 mysql mysql 6 Feb 25 10:07 binlogs
drwxr-xr-x 2 mysql mysql 6 Feb 25 10:07 mysqldata
[root@mysql ~]$ll /data/ -d
drwxr-xr-x 4 mysql mysql 38 Feb 25 10:07 /data/

提供數據：

vim /etc/my.cnf
[mysqld]
#datadir=/var/lib/mysql
datadir=/data/mysqldata/
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log_bin=/data/binlogs/mysql-bin
innodb_file_per_table


[root@mysql ~]$ll /data/ -d
drwxr-xr-x 4 mysql mysql 38 Feb 25 10:07 /data/
[root@mysql ~]$ll /data/
total 0
drwxr-xr-x 2 mysql mysql 101 Feb 25 10:17 binlogs
drwxr-xr-x 5 mysql mysql 159 Feb 25 10:17 mysqldata



[root@mysql ~]$ll /data/mysqldata/
total 36892
-rw-rw---- 1 mysql mysql    16384 Feb 25 10:17 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Feb 25 10:17 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Feb 25 10:17 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Feb 25 10:17 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Feb 25 10:17 ib_logfile1
drwx------ 2 mysql mysql     4096 Feb 25 10:17 mysql
drwx------ 2 mysql mysql     4096 Feb 25 10:17 performance_schema
drwx------ 2 mysql mysql        6 Feb 25 10:17 test
[root@mysql ~]$ll /data/binlogs/
total 1056
-rw-rw---- 1 mysql mysql   30331 Feb 25 10:17 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1038814 Feb 25 10:17 mysql-bin.000002
-rw-rw---- 1 mysql mysql     245 Feb 25 10:17 mysql-bin.000003
-rw-rw---- 1 mysql mysql      93 Feb 25 10:17 mysql-bin.index

[root@mysql ~]$mysql < hellodb_InnoDB.sql

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     30331 |
| mysql-bin.000002 |   1038814 |
| mysql-bin.000003 |      7655 |
+------------------+-----------+
3 rows in set (0.00 sec)




開始備份之前，需要先加全局讀鎖；
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

刷新日志，記錄二進制日志的位置：

MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     30331 |
| mysql-bin.000002 |   1038814 |
| mysql-bin.000003 |      7698 |
| mysql-bin.000004 |       245 |
+------------------+-----------+
4 rows in set (0.00 sec)

記錄二進制日志的位置：
[root@mysql ~]$mysql -e 'show binary logs'
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     30331 |
| mysql-bin.000002 |   1038814 |
| mysql-bin.000003 |      7698 |
| mysql-bin.000004 |       245 |
+------------------+-----------+
[root@mysql ~]$mysql -e 'show binary logs' > pos.log

使用lv創建數據庫快照：

[root@mysql ~]$lvs
  LV        VG  Attr       LSize Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  binlogs   vg0 -wi-ao---- 3.00g                                                    
  mysqldata vg0 -wi-ao---- 2.00g      

[root@mysql ~]$lvcreate -n mysqldata-snapshot -s -p r -L 2G /dev/vg0/mysqldata
  Using default stripesize 64.00 KiB.
  Logical volume "mysqldata-snapshot" created.



[root@mysql ~]$lvs
  LV                 VG  Attr       LSize Pool Origin    Data%  Meta%  Move Log Cpy%Sync Convert
  binlogs            vg0 -wi-ao---- 3.00g                                                       
  mysqldata          vg0 owi-aos--- 2.00g                                                       
  mysqldata-snapshot vg0 sri-a-s--- 2.00g      mysqldata 0.00             


[root@mysql ~]$lvdisplay 
--- Logical volume ---
  LV Path                /dev/vg0/mysqldata-snapshot
  LV Name                mysqldata-snapshot
  VG Name                vg0
  LV UUID                oQZBaU-IEld-M2wc-IQHo-A8nH-e53J-SrRujn
  LV Write Access        read only
  LV Creation host, time mysql, 2018-02-25 10:25:18 +0800
  LV snapshot status     active destination for mysqldata
  LV Status              available
  # open                 0
  LV Size                2.00 GiB
  Current LE             512
  COW-table size         2.00 GiB
  COW-table LE           512
  Allocated to snapshot  0.00%
  Snapshot chunk size    4.00 KiB
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     8192
  Block device           253:4


當做完快照后，那么就可以繼續讓用戶訪問數據庫了；

MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

修改和破壞操作；
MariaDB [(none)]> delete from hellodb.students;
Query OK, 25 rows affected (0.01 sec)

掛載快照，將數據備份出來；



    直接掛載是掛載不了的，因為兩個設備文件的UUID是一樣的；
    /dev/mapper/vg0-mysqldata: UUID="6f9f0f27-dba5-4479-adb6-532362d80d38" TYPE="xfs" 
    /dev/mapper/vg0-binlogs: UUID="150de97a-7a76-465e-9d6a-1357600fa152" TYPE="xfs" 
    /dev/mapper/vg0-mysqldata--snapshot: UUID="6f9f0f27-dba5-4479-adb6-532362d80d38" TYPE="xfs"

[root@mysql ~]$man mount

nouuid Don''t check for double mounted file systems using the file system uuid.  This is useful to mount LVM snapshot vol‐
              umes, and often used in combination with "norecovery" for mounting read-only snapshots.

[root@mysql ~]$mkdir /mnt/snap

[root@mysql ~]$mount -o nouuid,norecovery /dev/vg0/mysqldata-snapshot /mnt/snap/
mount: /dev/mapper/vg0-mysqldata--snapshot is write-protected, mounting read-only
[root@mysql ~]$df -Ph
Filesystem                           Size  Used Avail Use% Mounted on
/dev/sda2                             50G  3.4G   47G   7% /
devtmpfs                             474M     0  474M   0% /dev
tmpfs                                489M     0  489M   0% /dev/shm
tmpfs                                489M  7.2M  482M   2% /run
tmpfs                                489M     0  489M   0% /sys/fs/cgroup
/dev/sda3                             20G   33M   20G   1% /app
/dev/loop0                           8.1G  8.1G     0 100% /mnt/cdrom
/dev/sda1                           1014M  158M  857M  16% /boot
tmpfs                                 98M     0   98M   0% /run/user/0
/dev/mapper/vg0-mysqldata            2.0G   63M  2.0G   4% /data/mysqldata
/dev/mapper/vg0-binlogs              3.0G   34M  3.0G   2% /data/binlogs
/dev/mapper/vg0-mysqldata--snapshot  2.0G   31M  2.0G   2% /mnt/snap



建議打包，放在遠程存儲上；有些企業使用磁帶機進行備份；
[root@mysql ~]$mkdir /backups
[root@mysql ~]$cd /mnt/snap/
[root@mysql snap]$ls
aria_log.00000001  aria_log_control  hellodb  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  test
[root@mysql snap]$cp -a /mnt/snap/* /backups/



[root@mysql snap]$ll /backups/ -h
total 29M
-rw-rw---- 1 mysql mysql  16K Feb 25 10:17 aria_log.00000001
-rw-rw---- 1 mysql mysql   52 Feb 25 10:17 aria_log_control
drwx------ 2 mysql mysql  272 Feb 25 10:19 hellodb
-rw-rw---- 1 mysql mysql  18M Feb 25 10:19 ibdata1
-rw-rw---- 1 mysql mysql 5.0M Feb 25 10:19 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Feb 25 10:17 ib_logfile1
drwx------ 2 mysql mysql 4.0K Feb 25 10:17 mysql
drwx------ 2 mysql mysql 4.0K Feb 25 10:17 performance_schema
drwx------ 2 mysql mysql    6 Feb 25 10:17 test


刪除快照，否則影響性能；

[root@mysql snap]$umount /mnt/snap/
umount: /mnt/snap: target is busy.
        (In some cases useful info about processes that use
         the device is found by lsof(8) or fuser(1))
[root@mysql snap]$cd
[root@mysql ~]$umount /mnt/snap/



[root@mysql ~]$lvremove /dev/vg0/mysqldata-snapshot 
Do you really want to remove active logical volume vg0/mysqldata-snapshot? [y/n]: y
  Logical volume "mysqldata-snapshot" successfully removed

數據庫出現故障的模擬：
    需要停止數據庫服務；
    [root@mysql ~]$systemctl stop mariadb
    [root@mysql ~]$rm -rf /data/mysqldata/*
數據庫的還原操作：

    [root@mysql ~]$cp -a /backups/* /data/mysqldata/
    [root@mysql ~]$systemctl start mariadb

    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | hellodb            |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+



MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     30331 |
| mysql-bin.000002 |   1038814 |
| mysql-bin.000003 |      7698 |
| mysql-bin.000004 |       442 |
| mysql-bin.000005 |       245 |
+------------------+-----------+
5 rows in set (0.00 sec)


此時只是恢復了一部分的數據，但是不是最新的，要想恢復至最新狀態，那么需要使用
mysql-bin.000004  245和mysql-bin.000005   245之間的二進制完成恢復；

[root@mysql ~]$ls
all_2018-02-24_21:46:13.sql  anaconda-ks.cfg  hellodb_InnoDB.sql    mariadb-bin.000010  r7.sh
all.sql                      binlog.sql       initial-setup-ks.cfg  pos.log
[root@mysql ~]$less pos.log 
Log_name        File_size
mysql-bin.000001        30331
mysql-bin.000002        1038814
mysql-bin.000003        7698
mysql-bin.000004        245


[root@mysql ~]$cd /data/binlogs/
[root@mysql binlogs]$ls
mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.000005  mysql-bin.index

[root@mysql binlogs]$cp -a mysql-bin.00000{4,5} ~


MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)


[root@mysql ~]$mysqlbinlog --start-position=245 mysql-bin.000004 > binlog.sql 
[root@mysql ~]$mysqlbinlog mysql-bin.000005 >> binlog.sql



MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.00 sec)



MariaDB [(none)]> source binlog.sql

此時發現students表是空的；
MariaDB [(none)]> select * from hellodb.students;

注意：
    MySQLdump 的備份是溫備；
    他的效率也不是特別的高，因為他的備份是相當于對MySQL數據庫進行的查詢操作的結果；
    如果是T級別的數據庫，那么查詢備份就需要大量的時間。