前提是数据库的数据是放在逻辑卷上的；数据库数据和日志分开存放；正常情况下数据和日志是放在两个独立的磁盘上，如果是raid的话，那么就无所谓了。

创建分区：

[root@mysql~]$fdisk/dev/sdaCommand(mforhelp):nAllprimarypartitionsareinuseAddinglogicalpartition6Firstsector(153098240-419430399,default153098240):Usingdefaultvalue153098240Lastsector,+sectorsor+size{K,M,G}(153098240-419430399,default419430399):+10GPartition6oftypeLinuxandofsize10GiBissetCommand(mforhelp):tPartitionnumber(1-6,default6):6Hexcode(typeLtolistallcodes):8eChangedtypeofpartition'Linux'to'LinuxLVM'Command(mforhelp):wThepartitiontablehasbeenaltered!Callingioctl()tore-readpartitiontable.WARNING:Re-readingthepartitiontablefailedwitherror16:Deviceorresourcebusy.Thekernelstillusestheoldtable.Thenewtablewillbeusedatthenextrebootorafteryourunpartprobe(8)orkpartx(8)Syncingdisks.[root@mysql~]$partprobeWarning:Unabletoopen/dev/sr0read-write(Read-onlyfilesystem)./dev/sr0hasbeenopenedread-only.[root@mysql~]$lsblkNAMEMAJ:MINRMSIZEROTYPEMOUNTPOINTsda8:00200G0disk├─sda18:101G0part/boot├─sda28:2050G0part/├─sda38:3020G0part/app├─sda48:40512B0part├─sda58:502G0part[SWAP]└─sda68:6010G0partsr011:018.1G0romloop07:008.1G1loop/mnt/cdrom

创建PV，vg，lv：

加入PV、VG和LV；[root@mysql~]$pvcreate/dev/sda6Physicalvolume"/dev/sda6"successfullycreated.[root@mysql~]$pvsPVVGFmtAttrPSizePFree/dev/sda6lvm2---10.00g10.00g[root@mysql~]$vgcreatevg0/dev/sda6Volumegroup"vg0"successfullycreated[root@mysql~]$vgsVG#PV#LV#SNAttrVSizeVFreevg0100wz--n-<10.00g<10.00g[root@mysql~]$vgdisplay---Volumegroup---VGNamevg0SystemIDFormatlvm2MetadataAreas1MetadataSequenceNo1VGAccessread/writeVGStatusresizableMAXLV0CurLV0OpenLV0MaxPV0CurPV1ActPV1VGSize<10.00GiBPESize4.00MiBTotalPE2559AllocPE/Size0/0FreePE/Size2559/<10.00GiBVGUUIDfuGxOy-IVrf-SnWd-C0ie-eb9O-LIWz-sMx17T[root@mysql~]$pvsPVVGFmtAttrPSizePFree/dev/sda6vg0lvm2a--<10.00g<10.00g创建的LV，剩余的空间用于放置快照数据；[root@mysql~]$lvcreate-nmysqldata-L2Gvg0Logicalvolume"mysqldata"created.[root@mysql~]$lvcreate-nbinlogs-L3Gvg0Logicalvolume"binlogs"created.[root@mysql~]$pvsPVVGFmtAttrPSizePFree/dev/sda6vg0lvm2a--<10.00g<5.00g[root@mysql~]$vgsVG#PV#LV#SNAttrVSizeVFreevg0120wz--n-<10.00g<5.00g格式化文件系统：[root@mysql~]$mkfs.xfs/dev/vg0/mysqldatameta-data=/dev/vg0/mysqldataisize=512agcount=4,agsize=131072blks=sectsz=512attr=2,projid32bit=1=crc=1finobt=0,sparse=0data=bsize=4096blocks=524288,imaxpct=25=sunit=0swidth=0blksnaming=version2bsize=4096ascii-ci=0ftype=1log=internallogbsize=4096blocks=2560,version=2=sectsz=512sunit=0blks,lazy-count=1realtime=noneextsz=4096blocks=0,rtextents=0[root@mysql~]$mkfs.xfs/dev/vg0/binlogsmeta-data=/dev/vg0/binlogsisize=512agcount=4,agsize=196608blks=sectsz=512attr=2,projid32bit=1=crc=1finobt=0,sparse=0data=bsize=4096blocks=786432,imaxpct=25=sunit=0swidth=0blksnaming=version2bsize=4096ascii-ci=0ftype=1log=internallogbsize=4096blocks=2560,version=2=sectsz=512sunit=0blks,lazy-count=1realtime=noneextsz=4096blocks=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="CentOS7x86_64"TYPE="iso9660"PTTYPE="dos"/dev/loop0:UUID="2017-09-06-10-53-42-00"LABEL="CentOS7x86_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}-pvmkdir:createddirectory‘/data’mkdir:createddirectory‘/data/mysqldata’mkdir:createddirectory‘/data/binlogs’[root@mysql~]$ll/data/total0drwxr-xr-x2rootroot6Feb2510:07binlogsdrwxr-xr-x2rootroot6Feb2510:07mysqldata[root@mysql~]$vim/etc/fstab...UUID=6f9f0f27-dba5-4479-adb6-532362d80d38/data/mysqldata/xfsdefaults00UUID=150de97a-7a76-465e-9d6a-1357600fa152/data/binlogs/xfsdefaults00[root@mysql~]$mount-a[root@mysql~]$df-PhFilesystemSizeUsedAvailUse%Mountedon/dev/sda250G3.5G47G7%/devtmpfs474M0474M0%/devtmpfs489M0489M0%/dev/shmtmpfs489M7.2M482M2%/runtmpfs489M0489M0%/sys/fs/cgroup/dev/sda320G33M20G1%/app/dev/loop08.1G8.1G0100%/mnt/cdrom/dev/sda11014M158M857M16%/boottmpfs98M098M0%/run/user/0/dev/mapper/vg0-mysqldata2.0G33M2.0G2%/data/mysqldata/dev/mapper/vg0-binlogs3.0G33M3.0G2%/data/binlogs[root@mysql~]$ll/data/total0drwxr-xr-x2rootroot6Feb2510:05binlogsdrwxr-xr-x2rootroot6Feb2510:05mysqldata[root@mysql~]$chown-Rmysql.mysql/data/[root@mysql~]$ll/data/total0drwxr-xr-x2mysqlmysql6Feb2510:07binlogsdrwxr-xr-x2mysqlmysql6Feb2510:07mysqldata[root@mysql~]$ll/data/-ddrwxr-xr-x4mysqlmysql38Feb2510:07/data/

提供数据：

vim/etc/my.cnf[mysqld]#datadir=/var/lib/mysqldatadir=/data/mysqldata/socket=/var/lib/mysql/mysql.sock#Disablingsymbolic-linksisrecommendedtopreventassortedsecurityriskssymbolic-links=0log_bin=/data/binlogs/mysql-bininnodb_file_per_table[root@mysql~]$ll/data/-ddrwxr-xr-x4mysqlmysql38Feb2510:07/data/[root@mysql~]$ll/data/total0drwxr-xr-x2mysqlmysql101Feb2510:17binlogsdrwxr-xr-x5mysqlmysql159Feb2510:17mysqldata[root@mysql~]$ll/data/mysqldata/total36892-rw-rw----1mysqlmysql16384Feb2510:17aria_log.00000001-rw-rw----1mysqlmysql52Feb2510:17aria_log_control-rw-rw----1mysqlmysql18874368Feb2510:17ibdata1-rw-rw----1mysqlmysql5242880Feb2510:17ib_logfile0-rw-rw----1mysqlmysql5242880Feb2510:17ib_logfile1drwx------2mysqlmysql4096Feb2510:17mysqldrwx------2mysqlmysql4096Feb2510:17performance_schemadrwx------2mysqlmysql6Feb2510:17test[root@mysql~]$ll/data/binlogs/total1056-rw-rw----1mysqlmysql30331Feb2510:17mysql-bin.000001-rw-rw----1mysqlmysql1038814Feb2510:17mysql-bin.000002-rw-rw----1mysqlmysql245Feb2510:17mysql-bin.000003-rw-rw----1mysqlmysql93Feb2510:17mysql-bin.index[root@mysql~]$mysql<hellodb_InnoDB.sqlMariaDB[(none)]>showdatabases;+--------------------+|Database|+--------------------+|information_schema||hellodb||mysql||performance_schema||test|+--------------------+5rowsinset(0.00sec)MariaDB[(none)]>showbinarylogs;+------------------+-----------+|Log_name|File_size|+------------------+-----------+|mysql-bin.000001|30331||mysql-bin.000002|1038814||mysql-bin.000003|7655|+------------------+-----------+3rowsinset(0.00sec)开始备份之前，需要先加全局读锁；MariaDB[(none)]>flushtableswithreadlock;QueryOK,0rowsaffected(0.00sec)

刷新日志，记录二进制日志的位置：

MariaDB[(none)]>flushlogs;QueryOK,0rowsaffected(0.01sec)MariaDB[(none)]>showbinarylogs;+------------------+-----------+|Log_name|File_size|+------------------+-----------+|mysql-bin.000001|30331||mysql-bin.000002|1038814||mysql-bin.000003|7698||mysql-bin.000004|245|+------------------+-----------+4rowsinset(0.00sec)记录二进制日志的位置：[root@mysql~]$mysql-e'showbinarylogs'+------------------+-----------+|Log_name|File_size|+------------------+-----------+|mysql-bin.000001|30331||mysql-bin.000002|1038814||mysql-bin.000003|7698||mysql-bin.000004|245|+------------------+-----------+[root@mysql~]$mysql-e'showbinarylogs'>pos.log

使用lv创建数据库快照：

[root@mysql~]$lvsLVVGAttrLSizePoolOriginData%Meta%MoveLogCpy%SyncConvertbinlogsvg0-wi-ao----3.00gmysqldatavg0-wi-ao----2.00g[root@mysql~]$lvcreate-nmysqldata-snapshot-s-pr-L2G/dev/vg0/mysqldataUsingdefaultstripesize64.00KiB.Logicalvolume"mysqldata-snapshot"created.[root@mysql~]$lvsLVVGAttrLSizePoolOriginData%Meta%MoveLogCpy%SyncConvertbinlogsvg0-wi-ao----3.00gmysqldatavg0owi-aos---2.00gmysqldata-snapshotvg0sri-a-s---2.00gmysqldata0.00[root@mysql~]$lvdisplay---Logicalvolume---LVPath/dev/vg0/mysqldata-snapshotLVNamemysqldata-snapshotVGNamevg0LVUUIDoQZBaU-IEld-M2wc-IQHo-A8nH-e53J-SrRujnLVWriteAccessreadonlyLVCreationhost,timemysql,2018-02-2510:25:18+0800LVsnapshotstatusactivedestinationformysqldataLVStatusavailable#open0LVSize2.00GiBCurrentLE512COW-tablesize2.00GiBCOW-tableLE512Allocatedtosnapshot0.00%Snapshotchunksize4.00KiBSegments1AllocationinheritReadaheadsectorsauto-currentlysetto8192Blockdevice253:4当做完快照后，那么就可以继续让用户访问数据库了；MariaDB[(none)]>unlocktables;QueryOK,0rowsaffected(0.00sec)修改和破坏操作；MariaDB[(none)]>deletefromhellodb.students;QueryOK,25rowsaffected(0.01sec)

挂载快照，将数据备份出来；直接挂载是挂载不了的，因为两个设备文件的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~]$manmountnouuidDon''tcheckfordoublemountedfilesystemsusingthefilesystemuuid.ThisisusefultomountLVMsnapshotvol‐umes,andoftenusedincombinationwith"norecovery"formountingread-onlysnapshots.[root@mysql~]$mkdir/mnt/snap[root@mysql~]$mount-onouuid,norecovery/dev/vg0/mysqldata-snapshot/mnt/snap/mount:/dev/mapper/vg0-mysqldata--snapshotiswrite-protected,mountingread-only[root@mysql~]$df-PhFilesystemSizeUsedAvailUse%Mountedon/dev/sda250G3.4G47G7%/devtmpfs474M0474M0%/devtmpfs489M0489M0%/dev/shmtmpfs489M7.2M482M2%/runtmpfs489M0489M0%/sys/fs/cgroup/dev/sda320G33M20G1%/app/dev/loop08.1G8.1G0100%/mnt/cdrom/dev/sda11014M158M857M16%/boottmpfs98M098M0%/run/user/0/dev/mapper/vg0-mysqldata2.0G63M2.0G4%/data/mysqldata/dev/mapper/vg0-binlogs3.0G34M3.0G2%/data/binlogs/dev/mapper/vg0-mysqldata--snapshot2.0G31M2.0G2%/mnt/snap建议打包，放在远程存储上；有些企业使用磁带机进行备份；[root@mysql~]$mkdir/backups[root@mysql~]$cd/mnt/snap/[root@mysqlsnap]$lsaria_log.00000001aria_log_controlhellodbibdata1ib_logfile0ib_logfile1mysqlperformance_schematest[root@mysqlsnap]$cp-a/mnt/snap/*/backups/[root@mysqlsnap]$ll/backups/-htotal29M-rw-rw----1mysqlmysql16KFeb2510:17aria_log.00000001-rw-rw----1mysqlmysql52Feb2510:17aria_log_controldrwx------2mysqlmysql272Feb2510:19hellodb-rw-rw----1mysqlmysql18MFeb2510:19ibdata1-rw-rw----1mysqlmysql5.0MFeb2510:19ib_logfile0-rw-rw----1mysqlmysql5.0MFeb2510:17ib_logfile1drwx------2mysqlmysql4.0KFeb2510:17mysqldrwx------2mysqlmysql4.0KFeb2510:17performance_schemadrwx------2mysqlmysql6Feb2510:17test删除快照，否则影响性能；[root@mysqlsnap]$umount/mnt/snap/umount:/mnt/snap:targetisbusy.(Insomecasesusefulinfoaboutprocessesthatusethedeviceisfoundbylsof(8)orfuser(1))[root@mysqlsnap]$cd[root@mysql~]$umount/mnt/snap/[root@mysql~]$lvremove/dev/vg0/mysqldata-snapshotDoyoureallywanttoremoveactivelogicalvolumevg0/mysqldata-snapshot?[y/n]:yLogicalvolume"mysqldata-snapshot"successfullyremoved

数据库出现故障的模拟：需要停止数据库服务；[root@mysql~]$systemctlstopmariadb[root@mysql~]$rm-rf/data/mysqldata/*数据库的还原操作：[root@mysql~]$cp-a/backups/*/data/mysqldata/[root@mysql~]$systemctlstartmariadbMariaDB[(none)]>showdatabases;+--------------------+|Database|+--------------------+|information_schema||hellodb||mysql||performance_schema||test|+--------------------+MariaDB[(none)]>showmasterlogs;+------------------+-----------+|Log_name|File_size|+------------------+-----------+|mysql-bin.000001|30331||mysql-bin.000002|1038814||mysql-bin.000003|7698||mysql-bin.000004|442||mysql-bin.000005|245|+------------------+-----------+5rowsinset(0.00sec)此时只是恢复了一部分的数据，但是不是最新的，要想恢复至最新状态，那么需要使用mysql-bin.000004245和mysql-bin.000005245之间的二进制完成恢复；[root@mysql~]$lsall_2018-02-24_21:46:13.sqlanaconda-ks.cfghellodb_InnoDB.sqlmariadb-bin.000010r7.shall.sqlbinlog.sqlinitial-setup-ks.cfgpos.log[root@mysql~]$lesspos.logLog_nameFile_sizemysql-bin.00000130331mysql-bin.0000021038814mysql-bin.0000037698mysql-bin.000004245[root@mysql~]$cd/data/binlogs/[root@mysqlbinlogs]$lsmysql-bin.000001mysql-bin.000002mysql-bin.000003mysql-bin.000004mysql-bin.000005mysql-bin.index[root@mysqlbinlogs]$cp-amysql-bin.00000{4,5}~MariaDB[(none)]>flushtableswithreadlock;QueryOK,0rowsaffected(0.00sec)[root@mysql~]$mysqlbinlog--start-position=245mysql-bin.000004>binlog.sql[root@mysql~]$mysqlbinlogmysql-bin.000005>>binlog.sqlMariaDB[(none)]>setsql_log_bin=0;QueryOK,0rowsaffected(0.00sec)MariaDB[(none)]>unlocktables;QueryOK,0rowsaffected(0.00sec)MariaDB[(none)]>sourcebinlog.sql此时发现students表是空的；MariaDB[(none)]>select*fromhellodb.students;

注意：MySQLdump的备份是温备；他的效率也不是特别的高，因为他的备份是相当于对MySQL数据库进行的查询操作的结果；如果是T级别的数据库，那么查询备份就需要大量的时间。