search
HomeDatabaseMysql TutorialMySQL备份与恢复之真实环境使用冷备_MySQL

bitsCN.com

MySQL备份与恢复之真实环境使用冷备

 

一 真实环境使用冷备

 

       在(MySQL备份与恢复之冷备)http://www.bitsCN.com/database/201311/254790.html

中,我们提到了冷备。但是有个问题,我们存储的数据文件是保存在当前本地磁盘的,如果这个磁盘挂掉,那我们存储的数据不就丢失了,这样备份数据不就功亏一篑,劳而无功。所以真实环境中我们多准备几块磁盘,然后再在这些磁盘上搭建LVM,把MySQL的数据目录挂载到LVM上,这样数据就不是存储在当前磁盘上,就可以保证数据的安全性。

 

二 示意图

 

 

MySQL备份与恢复之真实环境使用冷备_MySQL

 

 

三 真实环境使用冷备模拟

 

第一步,需要提前规划好磁盘,这里做模拟,添加两磁盘

 

第二步,对磁盘进行分区

[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)

bitsCN.com
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you alter a table in MySQL using the ALTER TABLE statement?How do you alter a table in MySQL using the ALTER TABLE statement?Mar 19, 2025 pm 03:51 PM

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

How do I configure SSL/TLS encryption for MySQL connections?How do I configure SSL/TLS encryption for MySQL connections?Mar 18, 2025 pm 12:01 PM

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

How do you handle large datasets in MySQL?How do you handle large datasets in MySQL?Mar 21, 2025 pm 12:15 PM

Article discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?Mar 21, 2025 pm 06:28 PM

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

How do you drop a table in MySQL using the DROP TABLE statement?How do you drop a table in MySQL using the DROP TABLE statement?Mar 19, 2025 pm 03:52 PM

The article discusses dropping tables in MySQL using the DROP TABLE statement, emphasizing precautions and risks. It highlights that the action is irreversible without backups, detailing recovery methods and potential production environment hazards.

How do you create indexes on JSON columns?How do you create indexes on JSON columns?Mar 21, 2025 pm 12:13 PM

The article discusses creating indexes on JSON columns in various databases like PostgreSQL, MySQL, and MongoDB to enhance query performance. It explains the syntax and benefits of indexing specific JSON paths, and lists supported database systems.

How do you represent relationships using foreign keys?How do you represent relationships using foreign keys?Mar 19, 2025 pm 03:48 PM

Article discusses using foreign keys to represent relationships in databases, focusing on best practices, data integrity, and common pitfalls to avoid.

How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?Mar 18, 2025 pm 12:00 PM

Article discusses securing MySQL against SQL injection and brute-force attacks using prepared statements, input validation, and strong password policies.(159 characters)

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

Hot Tools

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function