在上一篇文章（MySQL备份与恢复之冷备）中，我们提到了冷备。但是有个问题，我们存储的数据文件是保存在当前本地磁盘的，如果这个磁盘挂掉，那我们存储的数据不就丢失了，这样备份数据不就功亏一篑，劳而无功。所以真实环境中我们多准备几块磁盘，然后再在这些磁盘上搭建LVM，把MySQL的数据目录挂载到LVM上，这样数据就不是存储在当前磁盘上，就可以保证数据的安全性。
示意图
真实环境使用冷备模拟
第一步，需要提前规划好磁盘，这里做模拟，添加两磁盘
第二步，对磁盘进行分区
[root@serv01 ~]# fdisk /dev/sdb [root@serv01 ~]# fdisk /dev/sdc [root@serv01 ~]# ll /dev/sd[bc]1 brw-rw----. 1 root disk 8, 17 Sep 10 18:06 /dev/sdb1 brw-rw----. 1 root disk 8, 33 Sep 10 18:09 /dev/sdc1
第三步，yum安装lvm2
[root@serv01 ~]# yum install lvm2 -y
第四步，创建物理卷
[root@serv01 ~]# pvcreate /dev/sdb1 /dev/sdc1 Physical volume "/dev/sdb1" successfully created Physical volume "/dev/sdc1" successfully created
第五步，创建卷组
[root@serv01 ~]# vgcreate data /dev/sdb1 /dev/sdc1 Volume group "data" successfully created
第六步，创建逻辑卷
[root@serv01 ~]# lvcreate -L 2G -n mydata data Logical volume "mydata" created
第七步，格式化磁盘
[root@serv01 ~]# mkfs.ext4 /dev/data/mydata mke2fs 1.41.12 (17-May-2010) Filesystem label= OS type: Linux 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 First data block=0 Maximum filesystem blocks=536870912 16 block groups 32768 blocks per group, 32768 fragments per group 8192 inodes per group Superblock 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 will be automatically checked every 28 mounts or 180 days, whichever comes first. Use tune2fs -c or -i to override.
第八步，冷备
[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/
第九步，删除数据库文件
[root@serv01 ~]# rm -rf /usr/local/mysql/data/*
第十步，挂载
[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/data-mydata 2.0G 67M 1.9G 4% /usr/local/mysql/data
第十一步，将挂载信息写入配置文件
[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
第十二步，停掉数据库
[root@serv01 ~]# /etc/init.d/mysqld stop ERROR! MySQL server PID file could not be 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 [root@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 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/ -d drwxr-xr-x. 2 mysql mysql 4096 Sep 10 18:17 /usr/local/mysql/data/
第十三步，恢复数据
[root@serv01 opt]# tar -xPvf mysql01.tar.gz
第十四步，启动数据库，登录MySQL，然后查看数据是否丢失
[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) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be 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)
第十五步，使用LVS的快照功能创建快照，快照不需要格式化。
[root@serv01 opt]# lvcreate -L 100M -s -n smydata /dev/data/mydata Logical volume "smydata" created
第十六步，挂载
[root@serv01 opt]# mount /dev/data/smydata /mnt [root@serv01 opt]# 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 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
第十七步，模拟数据丢失和验证快照的数据不会受本身数据的影响
[root@serv01 opt]# cd /mnt [root@serv01 mnt]# ls crm ib_logfile1 mysql-bin.000003 mysql-bin.000008 mysql-bin.000013 mysql-bin.index game larrydb mysql-bin.000004 mysql-bin.000009 mysql-bin.000014 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 ib_logfile0 mysql-bin.000002 mysql-bin.000007 mysql-bin.000012 mysql-bin.000017 test #进入数据目录，创建一个文件 [root@serv01 ~]# cd /usr/local/mysql/data/ [root@serv01 data]# touch aa01.txt #进入快照挂载目录，发现没有这个文件 [root@serv01 mnt]# ls aa01.txt ls: cannot access aa01.txt: No such file or directory
第十八步，备份数据
[root@serv01 mnt]# cd /databackup/ [root@serv01 databackup]# ll total 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]# ll total 0
第十九步，恢复数据，启动数据库，登录MySQL，然后查看数据是否丢失
[root@serv01 data]# tar -xvf /databackup/mysql02.tar.gz [root@serv01 data]# ls mnt [root@serv01 data]# cd mnt/ [root@serv01 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 serv01.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 data]# /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) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be 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)
本文主要是在真实环境实现冷备份，保证数据的安全性，很有实用价值，有需要的朋友可以收藏起来。
PDF- ins XML-Format
Gründe für Sitzungsfehler
So verwenden Sie PHP Sleep
Einführung in Softwareentwicklungstools
So verwenden Sie die Zählfunktion
So erhalten Sie die Adresse der Adressleiste
drücke irgendeine Taste zum Neustart
Was ist der Grund dafür, dass keine Verbindung zum Server hergestellt werden konnte?
