基于邏輯卷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)

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

使用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級別的數據庫，那么查詢備份就需要大量的時間。

另外有需要云服務器可以了解下創新互聯cdcxhl.cn，海內外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業上云的綜合解決方案，具有“安全穩定、簡單易用、服務可用性高、性價比高”等特點與優勢，專為企業上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。

網站題目：基于邏輯卷LVM的MySQL、mariadb數據庫備份還原詳細實現-創新互聯
標題來源：http://vcdvsql.cn/article38/ppcsp.html

成都網站建設公司_創新互聯，為您提供微信小程序、企業網站制作、網站改版、App設計、外貿建站、軟件開發

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源：創新互聯

猜你還喜歡下面的內容

bl双性强迫侵犯h_国产在线观看人成激情视频_蜜芽188_被诱拐的少孩全彩啪啪漫画

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