MySql的备份还原

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 备份数据是数据库管理最常用的操作。为了保证数据库中数据的安全,数据管理员需要定期进行数据备份。一旦数据库遭到破坏,便可通过备份的文件来还原数据库。因此,数据备份是一项很重要的工作。数据备份使用mysqldump命令备份,mysqldump命令可以将数据库中的数据备份成一个文本文件。

备份数据是数据库管理最常用的操作。为了保证数据库中数据的安全,数据管理员需要定期进行数据备份。一旦数据库遭到破坏,便可通过备份的文件来还原数据库。因此,数据备份是一项很重要的工作。

数据备份

使用mysqldump命令备份,mysqldump命令可以将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。

Mysqldump命令的工作原理很简单。它先查出需要备份的表的结构,再在文本文件中生存一个CREATE语句;然后,将表中的所有记录转换成一条INSERT语句,这些CREATE语句和INSERT语句都是还原时使用的,还原数据时就可以使用其中的CREATE语句来创建表,使用其中的INSERT语句来还原数据。

备份一个数据库

使用mysqldump命令备份一个数据库的基本语法如下:

mysqldump -uusername -p dbname table1 table2 …… > BackupName.sql

其中,dbname参数表示数据库的名称;table1和table2参数表示表的名称,没有该参数时将备份整个数据库;BackupName.sql参数表示备份文件的名称,文件名前面可以加上一个绝对路径。通常将数据库备份成一个后缀名为sql的文件。mysqldump命令备份的文件并非一定要求后缀名为.sql,备份成其他格式的文件也是可以的,例如,后缀名为.txt的文件。但是,通常情况下是备份成后缀名为.sql的文件。

ps:文件开头记录了MySQL的版本、备份的主机名和数据库名。文件中,以“--”开头的都是SQL语言的注释。以“/*!40101”等形式开头的都是与MySQL有关的注释。40101是MySQL数据库的版本号,这里就表示MySQL4.1.1。文件中没有创建数据库的语句,因此,student.sql文件中的所有表和记录必须还原到一个已经存在的数据库中。还原数据时,CREATE TABLE语句会在数据库中创建表,然后执行INSERT语句向表中插入记录。

备份多个数据库

mysqldump命令备份多个数据库的语法如下:

mysqldump -uusername -p --databases dbname1 dbname2 > BackupName.sql

这里要加上“databases”这个选项,然后后面跟多个数据库的名称。

备份所有数据库

mysqldump命令备份所有数据库的语法如下:

mysqldump -uusername -p --all-databases > BackupName.sql

使用“--all-databases”选项就可以备份所有数据库了。

直接复制整个数据库目录

MySQL有一种最简单的备份方法,就是将MySQL中的数据库文件直接复制出来。这种方法最简单,速度也最快。使用这种方法时,最好将服务器先停止。这样,可以保证在复制期间数据库中的数据不会发生变化。如果在复制数据库的过程中还有数据写入,就会造成数据不一致。

这种方法虽然简单快捷,但不是最好的备份方法。因为,实际情况可能不允许停止MySQL服务器。而且,这种方法对INNODB存储引擎的表不适用。对于MyISAM存储引擎的表,这样备份和还原很方便。但是还原时最好是相同版本的MySQL数据库,否则可能会存储文件类型不同的情况。

ps:在MySQL的版本号中,第一个数字表示主版本号。主版本号相同的MySQL数据库的文件类型会相同。例如,MySQL5.1.39和MySQL5.1.40这两个版本的主版本号都是5.那么这两个数据库的数据文件拥有相同的文件格式。

使用mysqlhotcopy工具快速备份

如果备份时不能停止MySQL服务器,可以采用mysqlhotcopy工具。使用mysqlhotcopy工具备份要比mysqldump命令快。

mysqlhotcopy工具是一个Perl脚本,主要在Linux操作系统下使用。mysqlhotcopy工具使用LOCK TABLES、FLUSH TABLES 和 cp来进行快速备份。其工作原理是,先将需要备份的数据库加上一个读操作锁,然后用FLUSH TABLES将内存中的数据写回到硬盘上的数据库中,最后把需要备份的数据库文件复制到目标目录。使用mysqlhotcopy的命令如下:

[root@localhost~]#mysqlhotcopy [option] dbname1 dbname2 …… backupDir/

其中,dbname1等表示需要备份的数据库的名称;backupDir参数指定备份到哪个文件夹下。这个命令的含义就是将dbname1、dbname2等数据库备份到backDir目录下。mysqlhotcopy工具有一些常用的选项,这些选项的介绍如下:

--help:用来查看mysqlhotcopy的帮助;

--allowold:如果备份目录下存在相同的备份文件,将旧的备份文件名加上_old;

--keepold:如果备份目录下存在相同的备份文件,不删除旧的备份文件,而是将旧文件更名;

--flushlog:本次备份之后,将对数据库的更新记录到日志中;

--noindices:只备份数据文件,不备份索引文件;

--user=用户名:用来指定用户名,可以用-u代替;

--password=密码:用来指定密码,可以用-p代替。使用-p时,密码与-p紧挨着,或者只使用-p,然后用交换的方式输入密码,这与登录数据库时的情况是一样的;

--port=端口号:用来指定访问端口,可以用-P代替;

--socket=socket文件:用来指定socket文件,可以用-S代替。

ps:mysqlhotcopy工具不是MySQL自带的,需要安装Perl的数据接口包,Perl的数据库接口包可以在MySQL官方网站下载,网址是http://dev.mysql.com/downloads/dbi.html。mysqlhotcopy工具的工作原理是将数据库文件拷贝到目标目录。因此mysqlhotcopy工具只能备份MyISAM类型的表,不能用来备份InnoDB类型的表。

数据还原

管理员的非法操作和计算机的故障都会破坏数据库文件。当数据库遇到这些意外时,可以通过备份文件将数据库还原到备份时的状态,这样可以将损失降低到最小。

使用mysql命令还原

使用mysqldump命令可以将数据库的数据备份成一个文本文件,通常这个文件的后缀名是.sql。需要还原时,可以使用mysql命令来还原备份的数据。

备份文件中通常包含CREATE语句和INSERT语句。mysql命令可以执行备份文件中的CREATE语句和INSERT语句,通过CREATE语句来创建数据库和表,通过INSERT语句来插入备份的数据。mysql命令的基本语法如下:

mysql -uroot -p  dbname < backup.sql

其中,dbname参数表示数据库名称。该参数为可选参数,可以指定数据库名,也可以不指定;指定数据库名时,表示还原该数据库下的表;不指定数据库名时,表示还原特定的一个数据库,而备份文件中有创建数据库的语句。

ps:如果使用--all-databases参数备份了所有的数据库,那么还原时不需要指定数据库。因为,其对应的sql文件包含有CREATE DATABASE语句,可以通过该语句创建数据库。创建数据库之后,可以执行sql文件中的USE语句选择数据库,然后在数据库中创建表并且插入记录。

直接复制到数据库目录

通过直接复制数据的方式备份的数据,可以直接复制到MySQL的数据库目录下。通过这种方式还原时,必须保证两个MySQL数据库的主版本号是相同的。而且,这种方式对MyISAM类型的表比较有效,对于InnoDB类型的表则不可用,因为InnoDB表的表空间不能直接复制。

使用mysqlhotcopy命令备份的数据也是通过这种方式来还原的。在Linux操作系统下,复制到数据库目录后,一定要将数据库的用户和组变成mysql。命令如下:

chown -R mysql.mysql dataDir

其中,两个mysql分别表示组和用户;“-R”参数可以改变文件夹下的所有子文件的用户和组;“dataDir”参数表示数据库目录。

ps:Linux操作系统下的权限设置非常严格。通常情况下,MySQL数据库只有root用户和mysql用户组下的mysql用户可以访问。因此,将数据库目录复制到指定文件夹后,一定要使用chown命令将文件夹的用户组变为mysql,将用户变为mysql。

数据库迁移

数据库迁移就是指将数据库从一个系统移动到另一个系统上。数据库迁移的原因有多种,可能是因为升级了计算机,或者是部署开发的管理系统,或者升级了MySQL数据库,甚至是换用其他的数据库。根据上述情况,可以将数据库迁移大致分为3类。这3类分别是在MySQL相同版本的数据库之间迁移、迁移到MySQL其他版本的数据库中和迁移到其他类型的数据库中。

MySQL相同版本的数据库之间的迁移

MySQL相同版本的数据库之间的迁移就是在主版本号相同的MySQL数据库之间进行数据库移动,这种迁移的方式最容易实现。

MySQL相同版本的数据库之间进行数据库迁移的原因很多。通常的原因是换了新的机器,或者是装了新的操作系统;还有一种常见的原因就是将开发的管理系统部署到工作机器上。因为迁移前后MySQL数据库的主本版号相同,所以可以通过复制数据库目录来实现数据库迁移。但是,只有数据库表都是MyISAM类型的才能使用这种方式。

最常用和最安全的方式是使用mysqldump命令来备份数据库。然后使用mysql命令将备份文件还原到新的MySQL数据库中。这里可以将备份和迁移同时进行。假设从一个名为host1的机器中备份出所有数据库,然后,将这些数据库迁移到名为host2的机器上。命令如下:

mysqldump -h name1 -u root -password=password1 -all-databases

mysql -h host2 -u root -password=password2

其中,“-password=password1”是name1主机上root用户的密码;同理,password2是name2主机上的root用户的密码。通过这种方式可以直接实现迁移。

不同数据库之间的迁移

不同数据库之间迁移是指从其他类型的数据库迁移到MySQL数据库,或者从MySQL数据库迁移到其他类型的数据库。例如,某个网站原来使用Oracle数据库,因为运营成本太高等诸多原因,希望改用MySQL数据库。或者,某个管理系统原来使用MySQL数据库,因为某种特殊性能的要求,希望改用Oracle数据库。这样的不同数据库之间的迁移也经常会发生,但是这种迁移没有普遍适用的解决方法。

MySQL以外的数据库也有类似mysqldump这样的备份工具,可以将数据库中的文件备份成sql文件或普通文件。但是,因为不同数据库厂商没有完全按照SQL标准来设计数据库,这就造成了不同数据库使用的SQL语句的差异。例如,微软的SQL Server软件使用的是T-SQL语言。T-SQL中包含了非标准的SQL语句,这就造成了SQL Server和MySQL的SQL语句不能兼容。

除了SQL语句存在不兼容的情况外,不同的数据库之间的数据类型也有差异。例如,SQL Server数据库中有ntext、Image等数据类型,这些MySQL数据库都没有;MySQL支持的ENUM和SET类型,这些SQL Server数据库不支持,数据类型的差异也造成了迁移的困难。从某种意义上说,这种差异是商业数据库公司故意造成的壁垒,这种行为是阻碍数据库市场健康发展的。

表的导出和导入

MySQL数据库中的表可以导出成文本文件、XML文件或者HTML文件;相应地,文本文件也可以导入MySQL数据库中。在数据库的日常维护中,经常需要进行表的导出和导入的操作。

用SELECT …… INTO OUTFILE导出文本文件

MySQL中,可以使用SELECT …… INTO OUTFILE语句将表的内容导出成一个文本文件。其基本语法形式如下:

SELECT [列名] FROM table [WHERE语句] INTO OUTFILE '目标文件' [OPTION];

该语句分为两个部分,前半部分是一个普通的SELECT语句,通过这个SELECT语句来查询所需要的数据;后半部分是导出数据的。其中,“目标文件”参数指定将查询的记录导出到哪个文件;“OPTION”参数时可以有常用的5个选项。介绍如下:

FIELDS TERMINATED BY '字符串':设置字符串为字段的分隔符,默认值是“\t”;

FIELDS ENCLOSED BY '字符':设置字符串括上字段的值。默认情况下不使用任何符号;

FIELDS OPTIOINALLY ENCLOSED BY '字符':设置字符串括上CHAR、VARCHAR、和TEXT等字符型字段。默认情况下不使用任何符号;

FIELDS ESCAPED BY '字符':设置转义字符,默认值为“\”;

LINES STARTING BY '字符串':设置每行开头的字符,默认情况下无任何字符;

LINES TERMINATED BY '字符串':设置每行的结束符,默认值是“\n”;

SELECT * FROM student INTO OUTFILE 'D:\student1.txt'
FIELDS TERMINATED BY '\,'OPTIONALLY ENCLOSED BY '\"' LINES STARTING BY '\>'
TERMINATED BY'\r\n';
字段之间用“,”隔开,字符型数据用双引号括起来,每条记录以“>”开头。“TERMINATED BY'\r\n'”可以保证每条记录占一行。

用mysqldump命令导出文本文件

mysqldump命令可以备份数据库中的数据,但在备份时保存了CREATE语句和INSERT语句,不仅如此,mysqldump命令还可以导出文本文件。其基本的语法形式如下:

mysqldump -uroot -pPassword -T 目标目录 dbname table [option];

其中,Password参数表示root用户的密码,密码紧挨着-p选项;目标目录参数时指导出的文本文件的路径;dbname参数表示数据库的名称;table参数表示表的名称;option表示附件选项。这些选项介绍如下:

--fields-terminated-by=字符串:设置字符串为字段的分隔符,默认值是“\t”;

--fields-enclosed-by=字符:设置字符串括上字段的值;

--fields-optionally-enclosed-by=字符:设置字符括上CHAR、VARCHAR和TEXT等字符型字段;

--fields-escaped-by=字符:设置转义字符;

--lines-terminated-by=字符串:设置每行的结束符。

注意这些选项必须用双引号括起来,否则,MySQL数据库系统将不能识别这几个参数。

mysqldump -uroot -p111 -T D:\test student
"--fields-terminated-by=,""-fields-optionally-enclosed-by="
字段之间用“,”隔开,字符型数据用双引号括起来.

ps:其实,mysqldump命令也是调用SELECT……INTO OUTFILE语句来导出文本文件的。除此之外,mysqldump命令同时还生成了student.sql文件,这个文件中有表的结构和表中的记录。

mysqldump命令还可以导出xml格式的文件,其基本语法如下:

mysqldump -uroot -pPassword -xml-X dbname table > D:\name.xml;

其中,Password表示root用户的密码;使用-xml或者-X选项就可以导出xml格式的文件;dbname表示数据库的名称;table表示表的名称;D:\name.xml表示导出的xml文件的路径。

用mysql命令导出文本文件

mysql命令可以用来登录MySQL服务器,也可以用来还原备份文件。同时,mysql命令也可以导出文本文件。其基本语法形式如下:

mysql -uroot -pPassword -e "SELECT语句" dbname > D:/name.txt;

其中,Password表示root用户的密码;使用-e选项就可以执行SQL语句:“SELECT语句”用来查询记录;D:/name.txt表示导出文件的路径。

mysql命令还可以导出XML文件和HTML文件。mysql命令导出XML文件的语法如下:

mysql -uroot -pPassword -xml-X -e "SELECT语句" dbname>D:/name.xml

其中,Password表示root用户的密码;使用-xml或者-X选项就可以导出xml格式的文件;dbname表示数据库的名称;D:/name.xml表示导出的XML文件的路径。

mysql命令导出HTML文件的语法如下:

mysql -uroot -pPassword --html-H -e "SELECT语句" dbname>D:/name.html

其中,使用--html或者-H选项就可以导出HTML格式的文件。

 

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3月前
|
存储 关系型数据库 MySQL
mysql数据库备份与恢复
MySQL数据库的备份与恢复是确保数据安全性和业务连续性的关键操作。
120 4
|
1月前
|
关系型数据库 MySQL Linux
Linux环境下MySQL数据库自动定时备份实践
数据库备份是确保数据安全的重要措施。在Linux环境下,实现MySQL数据库的自动定时备份可以通过多种方式完成。本文将介绍如何使用`cron`定时任务和`mysqldump`工具来实现MySQL数据库的每日自动备份。
101 3
|
1月前
|
监控 关系型数据库 MySQL
Linux环境下MySQL数据库自动定时备份策略
在Linux环境下,MySQL数据库的自动定时备份是确保数据安全和可靠性的重要措施。通过设置定时任务,我们可以每天自动执行数据库备份,从而减少人为错误和提高数据恢复的效率。本文将详细介绍如何在Linux下实现MySQL数据库的自动定时备份。
46 3
|
6月前
|
存储 SQL 关系型数据库
|
2月前
|
安全 关系型数据库 MySQL
MySQL用户备份
【10月更文挑战第2天】MySQL用户备份
68 3
|
2月前
|
存储 关系型数据库 MySQL
MySQL如何备份?
【10月更文挑战第2天】MySQL如何备份?
161 3
|
4月前
|
SQL 关系型数据库 MySQL
在Linux中,如何备份和恢复MySQL数据库?
在Linux中,如何备份和恢复MySQL数据库?
|
4月前
|
SQL 数据可视化 关系型数据库
MySQL 备份可视化巡检系统
MySQL 备份可视化巡检系统
|
4月前
|
SQL 存储 关系型数据库
MySQL备份:mydumper 备份恢复工具生产实战
MySQL备份:mydumper 备份恢复工具生产实战
|
4月前
|
安全 关系型数据库 MySQL
揭秘MySQL海量数据迁移终极秘籍:从逻辑备份到物理复制,解锁大数据迁移的高效与安全之道
【8月更文挑战第2天】MySQL数据量很大的数据库迁移最优方案
797 17

推荐镜像

更多