Mysql Backup and recovery of the real environment using Lengbei (2) _mysql

In the previous article (MySQL backup and restore), we mentioned Lengbei. But there is a problem, we store the data file is stored in the current local disk, if the disk is hung, then we stored data is not lost, so that the backup data is not failed, in vain. So in the real world we prepare a few more disks, then build LVM on these disks, and mount the MySQL data directory on the LVM, so that the data is not stored on the current disk, you can guarantee the security of the data.

Diagram

Using cold standby simulation in real environment

The first step, you need to plan the disk in advance, here do the simulation, add two disks

The second step is to partition the disk

[Root@serv01 ~]# fdisk/dev/sdb
[root@serv01 ~]# FDISK/DEV/SDC] root@serv01 ~]# ll/dev/sd[bc]1 brw-rw
---- . 1 root disk 8, Sep 18:06/dev/sdb1
brw-rw----. 1 root disk 8, Sep 18:09/DEV/SDC1
 

step three, yum installation lvm2

[Root@serv01 ~]# Yum Install lvm2-y

step Fourth, create a physical volume

[Root@serv01 ~]# pvcreate/dev/sdb1/dev/sdc1 Physical volume '/DEV/SDB1 ' successfully created-physical ' volume
 ' /DEV/SDC1 "successfully created

step Fifth, create a volume group

[Root@serv01 ~]# vgcreate data/dev/sdb1/dev/sdc1 
 Volume Group "data" successfully created

Step sixth, create a logical volume

[Root@serv01 ~]# lvcreate-l 2g-n mydata data
 Logical volume "MyData" created

step seventh, format the disk

[Root@serv01 ~]# mkfs.ext4/dev/data/mydata 
mke2fs 1.41.12 (17-may-2010)
filesystem label=
OS type:linux< C4/>block size=4096 (log=2)
Fragment size=4096 (log=2)
stride=0 blocks, stripe width=0 blocks 131072-inodes
, 524288 blocks
26214 blocks (5.00%) reserved for the super user of the "the" "the", "the", block=0 Maximum filesystem, blocks
=536870912 block
groups
32768 blocks/group, 32768 fragments per group 8192 inodes per
group
Sup  Erblock backups stored on blocks: 32768, 98304, 
 163840, 229376, 294912 writing

inode tables:done    
creating Journal (16384 Blocks): Done
writing superblocks and filesystem accounting Information:done this

filesystem WI ll be automatically checked every mounts or
180 days, whichever comes. Use Tune2fs-c or-i to override.

Eighth step, cold standby

[Root@serv01 ~]# ls/usr/local/mysql/data/
CRM Ib_logfile0 mysql-bin.000001 mysql-bin.000005 mysql-bin.000009 mysql-bin.000013 mysql-bin.index Test
game ib_logfile1 mysql-bin.000002 mysql-bin.000006 mysql-bin.000010 mysql-bin.000014 performance_schema
Hello larrydb mysql-bin.000003 mysql-bin.000007 mysql-bin.000011 mysql-bin.000015 serv01.host.com.err
ibdata1 mysql mysql-bin.000004 mysql-bin.000008 mysql-bin.000012 mysql-bin.000016 serv01.host.com.pid

[root@serv01 opt]# tar-cvpzf mysql01.tar.gz/usr/local/mysql/data/

Step nineth, delete the database file

[Root@serv01 ~]# rm-rf/usr/local/mysql/data/*

step Tenth, Mount

[Root@serv01 ~]# mount/dev/data/mydata/usr/local/mysql/data/
[root@serv01 ~]# df-h
Filesystem  Size Used  Avail use% mounted on
/dev/sda2  9.7G 2.4G 6.8G 27%/
tmpfs   188M 0 188M 0%/dev/shm
/dev/sda1  194M 25M 160M 14%/boot
/dev/sda5  4.0G 160M 3.7G 5%/opt
/dev/sr0  3.4G 3.4G 0 100%
/iso/dev/mapper/d Ata-mydata
   2.0G 67M 1.9G 4%/usr/local/mysql/data

Step 11th, write the Mount information to the configuration file

[Root@serv01 opt]# echo "/dev/mapper/data-mydata/usr/local/mysql/data ext4 defaults 1 2" >>/etc/fstab 
[ ROOT@SERV01 opt]# tail-n1/etc/fstab 
/dev/mapper/data-mydata/usr/local/mysql/data ext4 Defaults 1 2

Step 12th, stop the database.

[Root@serv01 ~]#/etc/init.d/mysqld Stop error!
MySQL server PID file could not is found! [Root@serv01 ~]# Ps-ef | grep mysqld Root 1055 1 0 18:05? 00:00:00/bin/sh/usr/local/mysql/bin/mysqld_safe--datadir=/usr/local/mysql/data--pid-file=/usr/local/mysql/data /serv01.host.com.pid MySQL 1332 1055 0 18:05? 00:00:00/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data--plugin-dir=/usr/ Local/mysql/lib/plugin--user=mysql--log-error=/usr/local/mysql/data/serv01.host.com.err--pid-file=/usr/local/ Mysql/data/serv01.host.com.pid--socket=/tmp/mysql.sock--port=3306 Root 1885 1490 0 18:18 pts/0 00:00:00 grep mysqld [ro OT@SERV01 ~]# pkill-9 MySQL [root@serv01 ~]# ps-ef | 

grep mysqld Root 1888 1490 0 18:18 pts/0 00:00:00 grep mysqld [root@serv01 ~]# Chown-R [Root@serv01 opt]# ll/usr/local/mysql/data/total 0 [root@serv01 opt]# ll/usr/local/mysql/data/-D drwxr-xr-x. 2 mysql mysql 4096 Sep 18:17/usr/local/mysql/data/
 

Step 13th, recover the data

[Root@serv01 opt]# TAR-XPVF mysql01.tar.gz 

Step 14th, start the database, log in to MySQL, and then see if the data is missing

[Root@serv01 opt]#/etc/init.d/mysqld start starting MySQL success! [root@serv01 ~]# MySQL Welcome to the MySQL monitor. Commands End With;
or \g. Your MySQL Connection ID is 1 Server version:5.5.29-log Source distribution Copyright (c), and Oracle and/or its Affiliates.

All rights reserved. Oracle is a registered trademark to Oracle Corporation and/or its affiliates.

The other names may is trademarks of their respective owners. Type ' help, ' or ' \h ' for help.

Type ' \c ' to clear the current input statement.
mysql> use Larrydb;
Database changed mysql> show tables; +-------------------+
|
Tables_in_larrydb | +-------------------+
| Class | |
Stu |
+-------------------+ 2 rows in Set (0.00 sec) mysql> SELECT * from class; +------+--------+
| CID |
CNAME | +------+--------+
| 1 | Linux | | 2 |
Oracle |
+------+--------+ 2 rows in Set (0.01 sec) mysql> select * from Stu; +------+---------+------+
| Sid | sname |
CID | +------+---------+------+
| 1 | Larry01 |
1 || 2 | larry02 |
2 |

 +------+---------+------+ 2 rows in Set (0.00 sec)

the 15th step is to use the LVS snapshot feature to create snapshots that do not need to be formatted.

[Root@serv01 opt]# lvcreate-l 100m-s-n smydata/dev/data/mydata Logical volume ' smydata ' created 
 

Step 16th, Mount

[Root@serv01 opt]# mount/dev/data/smydata/mnt
[root@serv01 opt]# df-h
filesystem  Size Used avail Use%-Moun Ted on
/dev/sda2  9.7G 2.4G 6.8G 27%/
tmpfs   188M 0 188M 0%/dev/shm
/dev/sda1  194M 25M 160M 1 4%/boot
/dev/sda5  4.0G 161M 3.7G 5%/opt
/dev/sr0  3.4G 3.4G 0 100%
/iso/dev/mapper/ Data-mydata
   2.0G 98M 1.8G 6%/usr/local/mysql/data
/dev/mapper/data-smydata
   2.0G 98M 1.8G 6%/mnt

step 17th, simulate data loss and verify that the snapshot's data is not affected by its own data

[Root@serv01 opt]# cd/mnt
[root@serv01 mnt]# ls
CRM  ib_logfile1 mysql-bin.000003 mysql-bin.000013 mysql-bin.index
game  larrydb  mysql-bin.000004 mysql-bin.000009 Performance_schema
Hello mysql  mysql-bin.000005 mysql-bin.000010 mysql-bin.000015 serv01.host.com.err
Ibdata1 mysql-bin.000001 mysql-bin.000006 mysql-bin.000011 mysql-bin.000016 serv01.host.com.pid
mysql-bin.000002 mysql-bin.000007 mysql-bin.000012 mysql-bin.000017 test

#进入数据目录, create a file
[root@serv01 ~]# cd/usr/local/mysql/data/
[root@serv01 data]# touch aa01.txt

#进入快照挂载目录, found no this file
[root@serv01 mnt]# ls Aa01.txt
ls:cannot Access aa01.txt:No such file or directory

Step 18th, backing up your data

[Root@serv01 mnt]# cd/databackup/
[root@serv01 databackup]# ll Total
976
-rw-r--r--. 1 root 995761 Sep 1 0 17:47 mysql01.tar.gz
[root@serv01 databackup]#/etc/init.d/mysqld status
 success! MySQL running (2198)
[root@serv01 databackup]# tar-cvzf mysql02.tar.gz/mnt

[root@serv01 mnt]# rm-rf/usr/local /mysql/data/*
[root@serv01 mnt]#/etc/init.d/mysqld Stop
 error! MySQL server PID file could not is found!
[Root@serv01 mnt]# pkill-9 mysql
[root@serv01 mnt]# ps-ef | grep mysqld | grep grep-v

[root@serv01 mnt]# CD/ usr/local/mysql/data/
[root@serv01 data]# ll Total
0
 

Step 19th, restore the data, start the database, log in to MySQL, and then see if the data is missing

[Root@serv01 data]# tar-xvf/databackup/mysql02.tar.gz [root@serv01 data]# ls mnt [root@serv01 data]# cd mnt/[Root@ser V01 mnt]# mv./*.
/[Root@serv01 mnt]# CD ... [Root@serv01 data]# ls CRM ib_logfile0 mysql mysql-bin.000004 mysql-bin.000008 mysql-bin.000012 mysql-bin.000016. Host.com.err game ib_logfile1 mysql-bin.000001 mysql-bin.000005 mysql-bin.000009 mysql-bin.000013 mysql-bin.000017 Serv01.host.com.pid Hello larrydb mysql-bin.000002 mysql-bin.000006 mysql-bin.000010 mysql-bin.000014 mysql-bin.index Test ibdata1 mnt mysql-bin.000003 mysql-bin.000007 mysql-bin.000011 mysql-bin.000015 performance_schema [root@serv01 da 
ta]#/etc/init.d/mysqld start starting MySQL success! [root@serv01 data]# MySQL Welcome to the MySQL monitor. Commands End With;
or \g. Your MySQL Connection ID is 1 Server version:5.5.29-log Source distribution Copyright (c), and Oracle and/or its Affiliates.

All rights reserved. Oracle is a registered trademark of Oracle Corporation and/orits affiliates.

The other names may is trademarks of their respective owners. Type ' help, ' or ' \h ' for help.

Type ' \c ' to clear the current input statement.
mysql> use Larrydb;
Database changed mysql> select * from class; +------+--------+
| CID |
CNAME | +------+--------+
| 1 | Linux | | 2 |
Oracle |
+------+--------+ 2 rows in Set (0.00 sec) mysql> select * from Stu; +------+---------+------+
| Sid | sname |
CID | +------+---------+------+
| 1 | Larry01 | 1 | | 2 | larry02 |
2 |
 +------+---------+------+ 2 rows in Set (0.00 sec)

This article is mainly in the real environment to achieve cold backup, to ensure data security, very practical value, the need for friends can be collected.