数据备份与还原-16(共22讲)-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

数据备份与还原-16(共22讲)

简介:

目录:
一、数据备份
二、数据还原
三、数据库迁移
四、导出和导入文本文件


一、数据备份:
1,mysqldump 
工作原理:它先查出需要备份的表的结构,再在文本文件中生成一个create语句,然后将表中的所有记录转换成一条insert语句。生成

的.sql文件其实是包含了create语句来恢复表结构、insert语句恢复表数据
使用方法:可以实现备份一个库、多个库、所有库------
mysqldump -u root -p‘123’ dbname1 table1  table2 ... > /home/wuxy/table1.sql
mysqldump -u root -p‘123’ --databases dbname1 dbname2  > /home/wuxy/dbbacpup.sql
mysqldump -u root -p'123' --all-databases > /home/wuxy/all.sql

2,直接复制mysql的数据文件(需停止mysql服务,否则造成数据不一致,但实际情况一般是不允许停止mysql服务的),对Innodb存储引擎

的表不适用,对MyISAM存储引擎的表,这样的备份和还原还是很方便的。但是还原时最好还是相同版本的Mysql数据库,否则可能会存在文

件类型不同的情况。
3.mysqlhotcopy
如果备份时不能停止mysql服务器,可以采用mysqlhotcopy工具,这种备份方式比mysqldump命令快。
工作原理:mysqlhotcopy是一个perl脚本,主要在linux系统下使用,mysqlhotcopy工具使用LOCK TABLES、FLUSH TABLES 和cp来进行快速

备份。其工作原理是:先将需要备份的数据库加上一个读操作锁,然后用FLUSH TABLES将内存中的数据库写回到硬盘中的数据库中,最后把

需要备份的数据库文件复制到目标目录
使用方法:
mysqlhotcopy [options] dbname1 dbname2  backDir/
可以同时备份多个数据库
help 查看参数


二、数据还原:
1、
如果使用mysqldump命令将数据库中的数据备份成一个文本文件,通常这个文件的后缀是sql,需要还原的时候,可以使用mysql命令来还原

备份的数据。
工作原理:备份文件中通常包含create语句和insert语句。mysql命令可以执行备份文件中的create语句和insert语句。通过create语句来

创建数据库和表。通过insert语句来插入备份的数据
使用方法:mysql -u root -p'123' [daname] < backup.sql
2、如果使用的是直接复制数据的备份方法,那么可以直接将备份数据复制到mysql的数据目录下。通过这种方式还原时,必须保证两个

Mysql数据库的主版本号相同,因为只有Mysql数据库的主版本号相同时,才能保证这两个Mysql数据库的文件类型是相同的。而且这种方式

对MyISAM类型的表比较有效,对应InnoDB类型的表则不可用,因为InnoDB表的表空间不能直接复制。
linux操作系统下,数据库目录通常在/var/lib/mysql、/usr/local/mysql/data或者/usr/local/mysql/var/这3个目录下。上述位置只是数

据库目录最常用的位置,具体位置根据安装时设定的位置而定。


三、数据库迁移:
数据库迁移就是指将数据库从一个系统移动到另一个系统上。
大致可以分为3类:
1、相同版本的Mysql数据库之间迁移
2、迁移到其他版本的Mysql数据库中
3、迁移到其他类型的数据库中
详细而言:
1、相同版本的Mysql数据库之间的迁移就是在主版本相同的Mysql数据库之间进行数据库移动。这种迁移的方式最容易实现。
迁移的原因比如有:换了新的服务器、或者是装了新的操作系统。
因为迁移后的Mysql数据库的主版本号相同,所以可以通过复制数据库目录来实现数据库迁移(需停止Mysql服务,否则会造成数据不一致)

,但是只有数据库表都是MyISAM类型的才能使用这种方式。也可以通过mysqldump备份,然后通过mysql命令还原。
2,不同版本的Mysql数据库之间进行数据迁移通常是因为Mysql升级的原因,例如:5.0版本推出以后,改进了4.0版本的很多缺陷,因此需

要升级。高版本的mysql数据库通常会兼容低版本,因此可以从低版本的mysql数据库迁移到高版本的mysql数据库。但是高版本的mysql数据

库很难迁移到低版本的mysql数据库,因为高版本的mysql数据库可能有一些新的特性,这些新的特性是低版本mysql数据库所不具有的。

MyISAM类型的表可以直接复制,也可以使用mysqlhotcopy工具
但是InnoDB类型的表不可以使用这两种方法。最常用的办法是使用mysqldump命令进行备份,然后通过mysql命令将备份文件还原到目标

MySQL数据库。
数据库迁移时要特别小心,最好是使用mysqldump命令来进行备份,避免迁移时造成数据丢失。

3,不同数据库之间迁移是指从其他类型的数据库迁移到Mysql数据库,或者从mysql数据库迁移到其他类型的数据库。例如:某个网站原来

使用的是oracle数据库,因为运营成本太高等原因,希望该用户mysql数据库。或者某个管理系统原来使用的是mysql数据库,希望改用

oracle数据库,这样的不同数据库之间的迁移也经常会发生,但是这种迁移没有普通使用的解决方法。
   mysql以外的数据库也有类似于mysqldump这样的备份工具,可以将mysql数据库中的文件备份成sql文件或者文本。但是,因为不同数据

库厂商没有完全按照SQL标准设计数据库,这就造成了不同数据库使用的sql语句的差异。例如:微软的sql server软件使用的是T-SQL语言

,T-SQL中包含了非标准的SQL语句,这就造成了SQL server和mysql的sql语句不能兼容。
注意:在不同数据库之间的迁移可能会使用一些其他的一些工具,具体的可以百度。

四、表的导出和导入:
在数据库的日常维护中,经常需要进行表的导出和导入操作。mysql数据库中的表可以导出成文本文件、xml文件、或者html文件。相应的文

本文件也可以导入mysql数据库中。
1,用select ... into outfile 导出文本文件
使用方法:select [列名] from table [where语句] into outfile '目标文件' [option];
option常见如下:
fields terminated by '字符串'   ##设置每个字段之间用什么隔开,默认是制表符
fields enclosed by '字符串'     ##用什么字符来括上字段的值,默认是不使用任何字符的
fields optionally enclosed by '字符串'  ##字符型数据(char、varchar、txt类型的字段)用什么引起来,默认是不使用任何字符的
fields escaped by '字符串'      ##设置转移字符,默认的是斜杠
lines starting by '字符串'  ##设置每一行的起始字符,默认是没有任何字符的
lines terminated by '字符串' ##设置每一行的结尾字符,比如:'\r\n'(换行回车),默认的情况下是'\n'(回车)

示例:
select * from test.student into outfile '/home/data/student.txt' fields terminated by '\.' fields optionally enclosed by 

'\"' lines starting by '\>' lines terminated by '\r\n';
2,用mysqldump命令导出文本文件
mysqldump命令可以备份数据库中的数据,但是备份时是在备份文件中保存了create语句和insert语句。
*2.1不仅如此,mysqldump命令还可以导出文本文件。
使用方法如下:
mysqldump -u root -p'123' -T 目标目录 dbname table [option];
option
--fields-terminated-by=字符串   ##设置字符串作为字段的分隔符,默认是制表符(\t)
--fileds-enclosed-by=字符       ##设置用什么字符来括上字段的值
--fields-optionally-enclosed-by=字符  ##字符型数据(char、varchar、txt类型的字段)用什么引起来,默认是不使用任何字符的
--fields-escaped-by=字符        ##设置转移字符,默认的是斜杠
--lines=terminated-by=字符串    ##设置每一行的结尾字符,比如:'\r\n'(换行回车),默认的情况下是'\n'(回车)
示例:
mysqldump -u root -p'123' -T /home/data/ test student "--fields-terminater-by=","--fields-optionally-enclosed-by='"'

*2.2 mysqldump命令导出xml文件
示例:
mysqldump -u root -p'123' --xml test student > /home/data/student.xml
##注意这个语句不能以分号结束

 
3 用mysql命令导出文本文件
mysql命令可以用来登录mysql服务器,也可以用来还原备份文件,同时,mysql命令也可以导出文本文件。
语法形式如下:
mysql -u root -p'123' -e "select 语句" dbname > /home/data/name.txt
示例:
mysql -u root -p'123' -e "select * from student" tets > /home/data/student2.txt ###与select 查询的数据一模一样
同样用mysql命令也可以导出xml文件和html文件
示例:
mysql -u root -p'123' --html -e "select * from department" test > /home/data/department.html
4 用load data infile 方式导入文本文件(这个命令是在数据库中执行的)
mysql中,可以使用load data infile 命令将文本文件导入到mysql数据库中。
基本语法形式如下:
load data [local] infile filename into table tablename [option]
示例:
load data infile '/home/data/student2.txt' into table student fields terminated by ',' optionally enclosed by '"';
注意:
如果文本文件中字段之间是用逗号分隔的,那么导入数据的时候也要指定文本文件的分隔符
同理,如果字符型数据(cahr、varchar、txt)用双引号括起来了,那么导入的时候也要指定

5 用mysqlimport命令导入文本文件
mysql中可以使用mysqlimport命令将文本文件导入到mysql数据库中。
语法形式如下:
mysqlimport -u root -p'123' [--local] dbname file [option]
option选项和mysqldump的选项一样
示例:
mysqlimport -u root -p'123' test '/home/data/student.txt' "--fields-terminated-by-," "00fields-optionally-enclosed-by-''"

常见问题及解答:
1.如何选择备份数据库的方法?
答:根据数据库的存储引擎的类型不同备份表的方法也不一样。对应MyISAM类型的表,可以直接复制Mysql数据文件夹或者使用mysqlhotcopy命令进行备份。复制mysql数据文件夹需要将mysql服务停止,否则可能会出现异常。而mysqlhotcopy命令则不需要停止mysql服务。mysqldump命令是最安全的备份方法,它既适合于MyISAM类型的表,也适用于InnoDB类型的表
2.如何升级mysql数据库?
答:
(1)先使用mysqldump命令备份mysql数据库中的数据,这样做的目的是为了避免误操作引起mysql数据库中的数据丢失。
(2)停止mysql服务,可以直接终止mysql服务的进程,但是最好还是使用安全的方法停止mysql服务,这样可以避免缓冲中的数据丢失。
(3)卸载旧版本的mysql数据库,通常情况下,卸载mysql数据库软件时,系统会继续保留mysql数据库中的数据文件
(4)安装新版本的mysql数据库,并进行相应的配置。
(5)启动mysql服务,登录mysql数据库查询数据是否完整,如果数据不完整,使用之前备份的数据进行恢复。

小结:本节介绍了备份数据库、还原数据库、数据库迁移、导出表和导入表的内容。备份数据库和还原数据库是本节的重点内容。在实际应用中,通常使用mysqldump命令备份数据库,使用mysql命令还原数据库。数据库迁移、导出表和导入表是本节的难点,数据库迁移需要考虑数据库的兼容性问题,最好是在相同版本的mysql数据库之间迁移。导出表和导入表的方法比较多,希望熟悉且多加练习。


追加内容:

一、MyISAM引擎备份和InnoDB引擎备份

由于MyISAM引擎为表级锁,因此在备份时需要防止在备份期间数据写入而导致不一致,所以,在备份时使用--lock-all-tables加上读锁。

示例:

mysqldump -A -F -B --lock-all-tables | gzip /data/backiup/$(date +%F).tar.gz

由于InnoDB引擎为行级锁,因此备份时可以不对数据库加锁的操作,可以加选项--single-transaction进行备份(Option automatically turns off --lock-tables,所以不会锁表),此参数仅对innodb有效,可以获得一致性备份。

示例:

mysqldump -A -F -B --single-transaction | gzip /data/backup/$(date +%F).tar.gz

备注:

1,--single-transaction这个参数仅适用于InnoDB引擎 --master-data=2

2,--single-transaction and --lock-all-tables是互斥的,不可以同时使用



      本文转自Tenderrain 51CTO博客,原文链接:http://blog.51cto.com/tenderrain/1602091,如需转载请自行联系原作者



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章