MySQL完全备份与恢复的示例分析

这篇文章将为大家详细讲解有关MySQL完全备份与恢复的示例分析，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

免费学习推荐：mysql视频教程

文章目录

• 前言

• 一、MySQL 完全备份

• 
  

• 2.1 使用 tar 打包文件夹备份

• 2.2 使用 mysqldump 工具备份

• 1.1 数据库备份的重要性

• 1.2 数据库备份的分类

• 
  

• 1.3 MySQL 完全备份概念解读

• 1.2.1 物理备份

• 1.2.2 逻辑备份

• 1.2.3 完全备份

• 1.2.4 差异备份

• 1.2.5 增量备份

• 1.2.6 备份方式比较

• 1.数据库备份方式精讲

• 
  

• 2.mysqldump 备份演练

• 
  

• 二、MySQL 完全恢复

• 
  

• 2.1 使用 source 命令恢复表

• 2.2 使用 mysql 命令恢复表

• 1.1 source 命令整库恢复

• 1.2 MySQL 命令整库恢复

• 1.恢复整库操作

• 
  

• 2.恢复表操作

• 
  

• 3.MySQL 备份思路

前言

• 随着办公自动化和电子商务的飞速发展，企业对信息系统的依赖性越来越高，数据库作为信息系统的核心，担当者重要的角色

• 数据库备份，是在数据丢失的情况下，能及时恢复重要数据，防止数据丢失的一种重要手段

• 一个合理的数据库备份方案，能够在数据丢失时，有有效地恢复数据，而且也需要考虑技术实现难度和有效地利用资源

一、MySQL 完全备份

1.数据库备份方式精讲

1.1 数据库备份的重要性

生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果

数据库备份的重要性主要体现在：

1. 提高系统的高可用性和灾难可恢复性，在数据库系统崩溃时，没有数据库备份就没法找到数据

2. 使用数据库备份还原数据库，是数据库崩溃时提供数据恢复最小代价的最优方案，如果让用户重新添加数据，代价未免太大

3. 没有数据就没有一切，数据库备份是一种防范灾难的强力手段

使用数据库的过程中，有多种原因造成数据的丢失：

1. 程序错误：指对数据库操作的程序难免有些错误，造成数据丢失

2. 人为错误：指由于使用人员的误操作造成的数据被破坏，还有可能是黑客对系统攻击造成的数据丢失等

3. 计算机失败：指运行数据库的服务器操作系统或软件损坏，有可能造成数据的损坏

4. 磁盘失败：指硬盘等存储数据的硬件设备，长时间运行后可能损坏，造成数
   据丢失

5. 灾难（如火灾、地震）和偷窃：指自然灾害等的发生，有可能造成数据丢失

• 数据丢失会造成严重的经济损失，如携程网2015年5月28日数据库崩溃，每小时损失大概106万美元

• 所以在企业信息系统建设中，数据库的备份管理是非常重要的内容

1.2 数据库备份的分类

备份方式分很多种，从物理与逻辑的角度，备份可分为以下几类：

1.2.1 物理备份

• 指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

• 物理备份又可以分为脱机备份（冷备份）和联机备份（热备份）

• 冷备份（脱机备份）：在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性

• 热备份（联机备份）：在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件

1.2.2 逻辑备份

• 指对数据库逻辑组件（如表等数据库对象）的备份

从数据库的备份策略角度来看，备份又可分为完全备份、差异备份和增量备份

1.2.3 完全备份

• 每次对数据进行完整的备份

• 可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象

• 但它需要花费更多的时间和空间，所以，做一次完全备份的周期要长些

1.2.4 差异备份

• 备份那些自从上次完全备份之后被修改过的文件，即只备份数据库部分的内容

• 它比最初的完全备份小，因为只包含自上次完全备份以来所改变的数据库

• 它的优点是存储和恢复速度快

1.2.5 增量备份

• 只有那些在上次完全备份或者增量备份后被修改的文件才会被备份

1.2.6 备份方式比较

• 完全备份每次都是把整个数据库中的内容进行备份

• 差异备份在第1次添加内容后备份，即备份新添加的内容；第2次添加内容后备份，把第1次添加的内容也进行了备份，它是根据完全备份时的状态决定的

• 差异备份每次都是把完全备份之后所有的修改进行备份，而不管之前是否做过差异备份

• 增量备份只备份每次新修改的内容，第1次创建表3，就对表3备份，第2次添加表4后，因为之前已经备份过表3，所以只备份表4的内容

1.3 MySQL 完全备份概念解读

• MySQL 的备份方式主要有完全备份与增量备份

• 完全备份是对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是增量备份的基础

• 完全备份的优点是备份与恢复操作简单方便，缺点是数据存在大量重复，占用大量的备份空间，备份的时间长

• 在生产环境中，这两种备份方式都会使用，需要制定合理高效的方案达到备份数据的目的，避免数据丢失造成严重的后果

2.mysqldump 备份演练

• MySQL 数据库的备份可以采用两种方式

• 因为数据库实际上就是文件，直接打包数据库文件夹，或者是使用专用备份工具 mysqldump 都可以进行备份工作

2.1 使用 tar 打包文件夹备份

• MySQL 的数据库文件默认都是保存在安全目录的 data 文件夹下，可以直接保存 data 文件夹，因为占用空间较大，可以使用data 打包压缩进行保存
  

yum -y install xz#数据库文件很大，可以使用压缩率较大的xz格式压缩，首选需要安装xz压缩格式工具tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/#对数据库文件夹进行打包操作ls /opt#查看打包命令是否运行成功，已经生成了备份文件du -sh /opt/mysql-2021-02-05.tar.xz #对比打包前后的文件大小，可以看到压缩的备份文件所占用空间很小tar -Jxvf /opt/mysql-2021-02-05.tar.xz -C /usr/local/mysql/data/systemctl restart mysql#如果数据库文件损坏数据丢失，可以解压缩文件，相当于数据恢复

2.2 使用 mysqldump 工具备份

• 上文中演示的是对 MySQL 整个数据库的目录进行压缩的方式，是备份数据库中所有的内容

• mysqldump 是 mysql 用于转存储数据库的实用程序（自带），它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令（如 CREATE、TABLE、INSERT等）

• 使用 mysqldump 可以更加灵活地控制备份的内容，比如某几个表或库都可以单独备份

#开始之前，创建库和表，用作例子mysql -uroot -p123123#输入密码进入数据库，以上为我的密码create database SCHOOL;#创建库use SCHOOL;create table CLASS01 ( id int(2) not null auto_increment, name varchar(10) not null, sex char(5) not null, hobby varchar(10), primary key (id));#创建表结构#字段一：id，最大显示长度2，不能为空，自动递增从1开始#字段二：name，可变长度字段，10，不能为空#字段三：sex，固定长度5，不能为空#字段四：hobby，可变长度，最多10字符#字段四：主键（id）insert into CLASS01 values(1,'wangyi','man','reading');insert into CLASS01 values(2,'wanger','woman','singing');#插入2条数据select * from CLASS01;#检查一下quit#退出数据库

#使用 mysqldump 对某些表进行完全备份，命令格式如下：mysqldump -u[用户名] -p[密码] [选项] [数据库名] [数据表名] > /备份路径/备份文件名.sql#导出的为数据库脚本文件例：对库SCHOOL中的表class进行备份至/opt/目录下mysqldump -uroot -p[密码] SCHOOL CLASS01 > /opt/test01.sql

#对某些库进行完全备份mysqldump -uroot -p[密码] [数据库名] > /目录/备份文件名.sql例：mysqldump -uroot -p123123 SCHOOL > /opt/test02#对多个库进行完全备份mysqldump -uroot -p123123 --databases SCHOOL sys > /opt/test03.sql#对所有库进行完全备份mysqldump -uroot -p123123 --all-databases > /opt/test04.sql#直接备份表结构mysqldump -uroot -p123123 -d SCHOOL CLASS01 > /opt/test05.sqlls /opt/#查看打包命令是否运行成功，已经生成了备份文件

cat /opt/test05.sql#分析 mysqldump 工具生成的备份文件的内容

• 上面使用了 -d 选项，说明只保存数据库的表结构，且表中只有一个库，可以看到先删除了表（同名），再创建了它

• 删除和创建都是最普通的 MySQL 命令，任何一条在 MySQL 中都是可以执行的语句，有了这些语句就可以创建出和现在的表结构相同的表

• 如果不使用 -d 选项，会把数据也进行备份，下面看看数据时如何保存的

cat /opt/test01

• 可以看到与使用 -d 参数的差别是最后出现了 insert into 语句，往数据库中插入了两条数据，也就是说，数据的备份是保存的插入语句操作

• 通过以上分析，很容易理解，备份的本质就是保存数据库当前表结构的创建语句和现有数据的插入语句，有了它们就可以直接恢复数据库的状态

二、MySQL 完全恢复

• 前面介绍了数据库的完全备份，使用 mysqldump 工具可以把数据的创建语句保存在备份的脚本文件中

• 当出现错误时，可以使用以下几种方法恢复它们

1.恢复整库操作

1.1 source 命令整库恢复

mysql -uroot -p123123show databases;drop database SCHOOL;show databases;source /opt/test03.sql#之前备份过的，该备份为SCHOOL和sys两个库的完整备份#注意！选项得加“--databases”才是完整的备份，不然会恢复不了

1.2 MySQL 命令整库恢复

quitmysql -uroot -p123123 -e 'drop database SCHOOL;'mysql -uroot -p123123 -e 'SHOW DATABASES;'mysql -uroot -p123123 < /opt/test03.sql

 mysql -uroot -p123123 -e 'SHOW DATABASES;'

2.恢复表操作

• 恢复数据表同样可以使用 source 命令和 mysql 命令操作

2.1 使用 source 命令恢复表

mysql -uroot -p123123use SCHOOL;show tables;drop table CLASS01;
 source /opt/test01.sql#恢复表show tables;select * from CLASS01;#查看

2.2 使用 mysql 命令恢复表

quitmysql -uroot -p123123 -e 'SHOW TABLES FROM SCHOOL;'mysql -uroot -p123123 -e 'DROP TABLES SCHOOL.CLASS01;'mysql -uroot -p123123 -e 'SHOW TABLES FROM SCHOOL;'mysql -uroot -p123123 SCHOOL < /opt/test01.sqlmysql -uroot -p123123 -e 'SHOW TABLES FROM SCHOOL;'

3.MySQL 备份思路

• MySQL 需要定期实施备份，指定合适的备份计划或策略，并严格遵守

• 除了进行完全备份，开启 MySQL 服务器的日志功能也很重要，完全备份加上日志，可以对 MySQL 进行最大化还原

• 备份文件的名字还需钥使用统一的易于理解的名称，推荐使用库名或表名加上时间的命名规则，在需要恢复数据库时能很容易的定位到相应的所需备份文件

关于“MySQL完全备份与恢复的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。