Implementation of LVM Logical Volume database backup and restore, binary log file separation and storage

The first thing you need to do is database files, and the binary log files are all placed in separate logical volume storage.

Implement LVM logical volumes, data, binary log files to separate and store one, create logical volume step (1), create new partition

1, check the zoning and see how much space is left.

    [[email protected] backup]# 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    2G  0 part [SWAP]    └─sda4   8:6    0    1G  0 part /app    sr0     11:0    1  8.1G  0 rom  #我这个磁盘200G，用了54G，还剩146G

2, create 1 new partitions as logical volumes

    fdisk /dev/sda     p  查看分区列表    n  创建分区    根据需要填写    t  修改id为逻辑卷    8e     p  再次确认

3, synchronizing partitions

    partprobe #centos7这条命令就可搞定

4. See if the partition is out

    [[email protected] ~]# 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    2G  0 part [SWAP]    ├─sda4   8:4    0  512B  0 part     ├─sda5   8:5    0   20G  0 part /app    └─sda6   8:6    0   10G  0 part     sr0     11:0    1  8.1G  0 rom  

(2) To create a logical volume

1. Add new partition to PV volume

    #加入pv卷        [[email protected] ~]#pvcreate /dev/sda6    #查看PV卷        [[email protected] ~]#pvs        PV         VG Fmt  Attr PSize  PFree         /dev/sda6     lvm2 ---  10.00g 10.00g

2. Add the PV volume to the volume group

    #加入卷组        [[email protected] ~]#vgcreate vg0 /dev/sda6    #查看卷组        [[email protected] ~]# vgs        VG  #PV #LV #SN Attr   VSize   VFree          vg0   1   0   0 wz--n- <10.00g <10.00g

3, create logical volumes that hold data and binary log files

    #1,创建存放数据的逻辑卷    lvcreate -n mysqldata -L 3G vg0    #2,创建存放二进制日志文件的逻辑卷    lvcreate -n binlog -L 2G vg0    #查看创建情况    [[email protected] ~]# lvs    LV        VG  Attr       LSize Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert    binlog    vg0 -wi-a----- 2.00g                                                        mysqldata vg0 -wi-a----- 3.00g                              "注意：PV卷的空间一定不要用完，因为需要有存放快照的空间"

4. Create File system

    mkfs.xfs /dev/vg0/mysqldata    mkfs.xfs /dev/vg0/binlog

(3), Boot automatically mount

1, create two files respectively when mount point

    mkdir -pv /mysql/data    mkdir -pv /mysql/binlog

2. Modify the/etc/fstab file

    blkid 查看两个分区的UUID    #将两分区按照这个顺序填写：UUID 挂载点 文件系统 defaults 0 0 即可     vim /etc/fstab    UUID=d30a59eb-9a20-4376-a6ed-d47cc4d66d05  /mysql/data            xfs     defaults        0 0    UUID=ef9c6374-a9e0-457f-895a-bf456be0ed96 /mysql/binlog           xfs     defaults        0 0

3, Mount

    [[email protected] ~]# mount -a    [[email protected] ~]# 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    2G  0 part [SWAP]    ├─sda4              8:4    0  512B  0 part     ├─sda5              8:5    0   20G  0 part /app    └─sda6              8:6    0   10G  0 part       ├─vg0-mysqldata 253:0    0    3G  0 lvm  /mysql/data      └─vg0-binlog    253:1    0    2G  0 lvm  /mysql/binlog    sr0                11:0    1  8.1G  0 rom  

Second, implement data and binary log separation

1, modify the permissions of two directories

    chown -R mysql.mysql /mysql/

2. Modify the MySQL configuration file

    vim /etc/my.cnf    在[mysqld]这个语句块下修改这两项    [mysqld]    datadir=/mysql/data    log_bin=/mysql/binlog/mysql-binlog

3, restart MARIADB service

    systemctl restart mariadb    systemctl enable  mariadb

4 to see if the port is open

    [[email protected] ~]# ss -ntl |grep 3306    LISTEN     0      50           *:3306                     *:*  

Backup step one, record the current backup location of the binary log

1, lock table prevents data changes during backup

    flush tables with read lock;

2, record binary log file and event location

#1,刷新二进制日志    flush logs;#2,查看当前二进制日志位置    show master status；    MariaDB [(none)]> show master logs;    +---------------------+-----------+    | Log_name            | File_size |    +---------------------+-----------+    | mysql-binlog.000001 |     32644 |    | mysql-binlog.000002 |  31231291 |    | mysql-binlog.000003 |       245 |    +---------------------+-----------+    3 rows in set (0.00 sec)

3. Save the current backup location of the binary log file to a file and use it to restore the view later

#1,创建存放备份数据，以及日志的目录    [[email protected] data]#mkdir  /backup/binlog    [[email protected] data]#mkdir  /backup/data#2，二进制日志文件当前备份位置存放至/backup/binlog目录里    [[email protected] ~]#mysql -uroot -p -e  ‘show master logs‘ >/mysql/binlog/pos-`date +%F`.log    [[email protected] ~]# cat /backup/binlog/pos-2018-03-07.log     Log_name    File_size    mysql-binlog.000001 32644     mysql-binlog.000002 31231291    mysql-binlog.000003 245

Second, create a logical volume snapshot

#1, create snapshot [[[email protected] data] #lvcreate-l 3g-s-P r-n mysqldata-snapshoot/dev/vg0/mysqldata Command resolution:-L: Specify Logical Volume Size-S: Specifies that the logical volume is snapshot-P: Specify permissions, r means only read permission-N: Specify the logical volume name/dav/vg0/mysqldata: To create a snapshot for the logical Volume # #, view the snapshot information [[Email protect                ED] ~]# lvdisplay---Logical volume---lv path/dev/vg0/mysqldata-snapshoot LV Name Mysqldata-snapshoot VG Name vg0 LV UUID ccafob-xlpt-pxh7-k9np-mqp7-wfdm-g Vptha LV Write Access read only LV Creation host, Time ansible-7, 2018-03-07 14:30:04 +0800 LV snaps Hot status Active destination for Mysqldata LV status available # Open 0 L           V size 3.00 Gib current LE 768 cow-table Size 3.00 GiB cow-table LE 768 allocated to snapshot 0.00% snapshot chunk size 4.00 KiB Segments 1 Alloca tion inherit      Read ahead sectors auto-currently set to 8192 Block device 253:4 

Third, release the database lock, restore user access

    unlock  tables;

Four, mount the snapshot logical volume

#1，创建临时挂载目录    [[email protected] data]#mkdir  /snapshoot#2，挂载    [[email protected] data]#mount -o nouuid,norecovery /dev/vg0/mysqldata-snapshoot /snapshoot    "注意:xfs文件系统快照挂载需要指定nouuid,norecovery这两个选项，ext4不需要"

Five, copy the data, to achieve the purpose of backup

    tar cvf  /backup/data/all-`date +%F`.tar /snapshoot

Six, Backup complete delete snapshot logical volume

#1，取消挂载    [[email protected] data]#umount  /snapshoot#2，删除快照逻辑卷    [[email protected] data]# lvremove /dev/vg0/mysqldata-snapshoot    Do you really want to remove active logical volume vg0/mysqldata-snapshoot? [y/n]: y    Logical volume "mysqldata-snapshoot" successfully removed

Third, destroy the database restore

(i), stop the service delete data storage directory

    systemctl stop mariadb    rm -rf /mysql/data/*

(ii) Recovery of full backup data

#1，解压缩    tar vxf /backup/data/all-2018-03-07.tar#2，复制snapshoot目录下的所有文件至/mysql/data/目录下    cp -a snapshoot/* /mysql/data/

(iii) Restore to the latest state using binary logs

1. View the backup location previously stored in the file

    cat /backup/binlog/pos-2018-03-07.log    Log_name    File_size    mysql-binlog.000001 32644     mysql-binlog.000002 31231291    mysql-binlog.000003 245之前备份的位置是mysql-binlog.000002文件的 31231291位置所以我们需要将mysql-binlog.000002日志文件拷贝出来

2, regenerate the binary log file,

    MariaDB [(none)]>flush logs

3. View the current binary log file location

MariaDB [(none)]> show master logs;    +---------------------+-----------+    | Log_name            | File_size |    +---------------------+-----------+    | mysql-binlog.000001 |     32644 |    | mysql-binlog.000002 |    312312 |    | mysql-binlog.000003 |   1212312 |    | mysql-binlog.000004 |       245 |    +---------------------+-----------+    3 rows in set (0.00 sec)

4, copy the relevant binary log files after backup

    cp -a /mysql/binlog/mysql-binlog.000003 /backup/binlog/    cp -a /mysql/binlog/mysql-binlog.000004 /backup/binlog/

5. Remove the content from the previous backup

    mysqlbinlog --start-position=245 mysql-binlog.000003 >bin.sql    mysqlbinlog mysql-binlog.000004 >> bin.sql

(iv) Start-up service

    systemctl start mariadb

(v) Pause logging of binary log functions

    set sql_log_bin=0;

(vi) Restore the data in the binary log

    MariaDB [(none)]> source /backup/binlog/bin.sql

(vii), turn on log binary logging function

    set sql_log_bin=1;

(eight), restore the copy of the binary log file can be deleted, so as not to affect the next recovery

Implementation of LVM Logical Volume database backup and restore, binary log file separation and storage