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