基于逻辑卷LVM的MySQL、mariadb数据库备份还原详细实现
短信预约 -IT技能 免费直播动态提醒
前提是数据库的数据是放在逻辑卷上的；
数据库数据和日志分开存放；正常情况下数据和日志是放在两个独立的磁盘上，如果是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级别的数据库，那么查询备份就需要大量的时间。免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/57.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
