MySQL backup and recovery-use cold backup in the real environment (2) _ MySQL

This article mainly introduces how to use cold backup in the real environment of MySQL backup and recovery. if you need it, refer to the previous article (cold backup of MySQL backup and recovery. However, there is a problem. The data files we store are stored on the current local disk. if this disk fails, the data we store will not be lost, so that backup data will not suffer a loss, work hard. Therefore, in the real environment, we need to prepare several more disks and then build an LVM on these disks to mount the MySQL data directory to the LVM. in this way, the data is not stored on the current disk, to ensure data security.

Cold backup simulation in real environment

The first step is to plan the disk in advance. here we will simulate and add two disks.

Step 2: partition the disk

[root@serv01 ~]# fdisk /dev/sdb[root@serv01 ~]# fdisk /dev/sdc[root@serv01 ~]# ll /dev/sd[bc]1brw-rw----. 1 root disk 8, 17 Sep 10 18:06 /dev/sdb1brw-rw----. 1 root disk 8, 33 Sep 10 18:09 /dev/sdc1 

Step 3: install lvm2 in yum

[root@serv01 ~]# yum install lvm2 -y

Step 4: create a physical volume

[root@serv01 ~]# pvcreate /dev/sdb1 /dev/sdc1  Physical volume "/dev/sdb1" successfully created Physical volume "/dev/sdc1" successfully created

Step 5: Create a volume Group

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

Step 6: create a logical volume

[root@serv01 ~]# lvcreate -L 2G -n mydata data Logical volume "mydata" created

Step 7 format the disk

[root@serv01 ~]# mkfs.ext4 /dev/data/mydata mke2fs 1.41.12 (17-May-2010)Filesystem label=OS type: LinuxBlock size=4096 (log=2)Fragment size=4096 (log=2)Stride=0 blocks, Stripe width=0 blocks131072 inodes, 524288 blocks26214 blocks (5.00%) reserved for the super userFirst data block=0Maximum filesystem blocks=53687091216 block groups32768 blocks per group, 32768 fragments per group8192 inodes per groupSuperblock backups stored on blocks:  32768, 98304, 163840, 229376, 294912Writing inode tables: done    Creating journal (16384 blocks): doneWriting superblocks and filesystem accounting information: doneThis filesystem will be automatically checked every 28 mounts or180 days, whichever comes first. Use tune2fs -c or -i to override.

Step 8: cold backup

[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 testgame ib_logfile1 mysql-bin.000002 mysql-bin.000006 mysql-bin.000010 mysql-bin.000014 performance_schemahello larrydb mysql-bin.000003 mysql-bin.000007 mysql-bin.000011 mysql-bin.000015 serv01.host.com.erribdata1 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 9: delete database files

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

Step 10: Mount

[root@serv01 ~]# mount /dev/data/mydata /usr/local/mysql/data/[root@serv01 ~]# df -hFilesystem  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/data-mydata   2.0G 67M 1.9G 4% /usr/local/mysql/data

Step 2: write the mounting 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 2: Stop the database

[root@serv01 ~]# /etc/init.d/mysqld stop ERROR! MySQL server PID file could not be found![root@serv01 ~]# ps -ef | grep mysqldroot 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.pidmysql 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=3306root 1885 1490 0 18:18 pts/0 00:00:00 grep mysqld[root@serv01 ~]# pkill -9 mysql[root@serv01 ~]# ps -ef | grep mysqldroot 1888 1490 0 18:18 pts/0 00:00:00 grep mysqld[root@serv01 ~]# chown mysql.mysql /usr/local/mysql/data/ -R[root@serv01 opt]# ll /usr/local/mysql/data/total 0[root@serv01 opt]# ll /usr/local/mysql/data/ -ddrwxr-xr-x. 2 mysql mysql 4096 Sep 10 18:17 /usr/local/mysql/data/

Step 2: restore data

[root@serv01 opt]# tar -xPvf mysql01.tar.gz 

Step 2: Start the database, log on to MySQL, and check whether the data is lost.

[root@serv01 opt]# /etc/init.d/mysqld startStarting MySQL SUCCESS! [root@serv01 ~]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.29-log Source distributionCopyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use larrydb;Database changedmysql> 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)

Step 2: Use the LVS snapshot function to create snapshots without formatting.

[root@serv01 opt]# lvcreate -L 100M -s -n smydata /dev/data/mydata  Logical volume "smydata" created

Step 2: Mount

[root@serv01 opt]# mount /dev/data/smydata /mnt[root@serv01 opt]# df -hFilesystem  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 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 2: simulate data loss and verify that the snapshot data is not affected by the data itself

[Root @ serv01 opt] # cd/mnt [root @ serv01 mnt] # lscrm ib_logfile1 mysql-bin.000003 mysql-bin.000008 mysql-bin.000013 mysql-bin.indexgame larrydb mysql-bin.000004 mysql-bin.000009 mysql-bin.000014 performance_schemahello mysql mysql-bin.000005 mysql-bin.000010 mysql-bin.000015 serv01.host.com. erribdata1 mysql-bin.000001 mysql-bin.000006 mysql-bin.000011 mysql-bin.000016 serv01.host.com. pidib_logfile0 m Ysql-bin.000002 mysql-bin.000007 mysql-bin.000012 mysql-bin.000017 test # enter the data directory and create a file [root @ serv01 ~] # Cd/usr/local/mysql/data/[root @ serv01 data] # touch aa01.txt # enter the snapshot Mount directory and find that this file does not exist [root @ serv01 mnt] # ls aa01.txtls: cannot access aa01.txt: No such file or directory

Step 2: back up data

[root@serv01 mnt]# cd /databackup/[root@serv01 databackup]# lltotal 976-rw-r--r--. 1 root root 995761 Sep 10 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 be 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]# lltotal 0 

Step 2: restore data, start the database, log on to MySQL, and check whether the data is lost.

[root@serv01 data]# tar -xvf /databackup/mysql02.tar.gz[root@serv01 data]# lsmnt[root@serv01 data]# cd mnt/[root@serv01 mnt]# mv ./* ../[root@serv01 mnt]# cd ..[root@serv01 data]# lscrm ib_logfile0 mysql  mysql-bin.000004 mysql-bin.000008 mysql-bin.000012 mysql-bin.000016 serv01.host.com.errgame ib_logfile1 mysql-bin.000001 mysql-bin.000005 mysql-bin.000009 mysql-bin.000013 mysql-bin.000017 serv01.host.com.pidhello larrydb mysql-bin.000002 mysql-bin.000006 mysql-bin.000010 mysql-bin.000014 mysql-bin.index testibdata1 mnt  mysql-bin.000003 mysql-bin.000007 mysql-bin.000011 mysql-bin.000015 performance_schema[root@serv01 data]# /etc/init.d/mysqld startStarting MySQL SUCCESS! [root@serv01 data]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.29-log Source distributionCopyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use larrydb;Database changedmysql> 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 mainly implements cold backup in the real environment to ensure data security and has great practical value. if you need it, you can add it to your favorites.