当前位置:Gxlcms > mysql > mysql备份还原库命令方法解析(长文)

mysql备份还原库命令方法解析(长文)

时间:2021-07-01 10:21:17 帮助过:36人阅读

关于mysql数据库备份与还原的方法,这里首先讲到备份的工具:mysqlhotcopy,使用mysqlhotcopy工具可进行快速备份,然后数据还原,使用MySQL命令还原;最后需要导出数据库表。详细的介绍还得阅读本文。

1.数据备份

定期的备份数据库,使得在意外情况发生的时候,尽量的减少损失。

1.使用mysqldump命令备份

mysqldump是MySQL提供的一个数据库备份工具,mysqldump命令执行的时候,将数据库备份成一个文本文件,该文件中包含了多个CREATE 和INSERT语句,使用这些语句可以重新创建表和插入数据;

【使用mysqldump备份单个数据库中】

mysqldump -u user -h host -p password dbname>filename.sql

【使用mysqldump备份数据库中的指定表】

mysqldump -u user -h host -p password dbname[tbname,[tbname…]]>filename.sql

【使用mysqldump备份多个数据库】

mysqldump -u user -h host -p password --databases[dbname,[dbname…]]>filename.sql

使用--databases参数之后,必须指定至少一个数据库的名称,多个数据库之间使用空格隔开;

【备份系统中所有的数据库】

mysqldump -u user -h host -p password --all-databases>filename.sql

提示:如果在服务器上进行备份,并且表均为MyISAM,应考虑使用mysqlhotcopy,因为可以更快的进行备份和恢复;

2.直接复制整个数据库目录

因为MySQL表保存为文件方式,所以可以直接复制MySQL数据库的存储目录以及文件进行备份。

这是一种简单、快速、有效的备份方式,要想保持备份的一致性,备份前需要对相关表执行LOCK TABLES 操作,然后对表执行FLUSH TABLES(确保开始备份前将所有激活的索引页写入硬盘)。这样当复制数据库目录的文件时,允许其他的用户继续查询表。

这种方法对InnoDB存储引擎的表不适用。使用这种方法备份数据最好还原到相同版本的服务器中,不同版本可能不兼容;

3.使用mysqlhotcopy工具快速备份

mysqlhotcopy是一个Perl脚本。

只能运行在数据库目录所在的机器上,并且只能备份MyISAM和ARCHIVE类型的表;

2.数据还原

1.使用MySQL命令还原

mysql -u username -p [dbname] < filename.sql

注意:如果filename.sql文件为mysqldump工具创建的包含创建数据库语句的文件,执行的时候不需要指定数据库名;

如果已经登录到MySQL服务器,还可以使用source命令导入SQL文件。

source filename

提示:执行source命令之前,必须使用use语句选择数据库。不然,恢复过程中会出现错误;

2.直接复制到数据库目录

如果数据库通过复制数据库文件备份,可以直接复制备份的文件到MySQL数据目录下实现还原。

通过该方式还原的时候,必须保持备份数据库和待还原的数据库服务器的主版本号相同。而且这种方式只是对MyISAM引擎的表有效,对于InnoDB引擎的表不可用;

执行还原以前关闭MySQL服务,将备份的文件或者目录覆盖MySQL的data目录,启动MySQL服务。

对于Linux/Unix操作系统来讲,复制完文件需要将文件的用户或者用户组更改为mysql运行的用户和组,通常用户是mysql,组也是mysql;

3.mysqlhotcopy快速恢复

mysqlhotcopy备份之后的文件也可以用来恢复数据库,在MySQL服务器停止运行的时候,将备份的数据库文件复制到MySQL存放的位置(MySQL的data文件夹),重新启动MySQL服务即可。

如果以根用户执行该操作,必须指定数据库文件的所有者

chown -R mysql.mysql /var/lib/mysql/dbname
cp -R /usr/backup/test usr/local/mysql/data

执行完该语句,重启服务器,MySQL将恢复到备份状态

提示:如果需要恢复的数据库已经存在,则在使用DROP语句删除已经存在的数据库之后,恢复才可以成功,另外MySQL不同版本之间必须兼容;

3.数据库迁移

数据库迁移就是把数据从一个系统移动到另一个系统上。数据迁移有以下原因:

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

相同版本的MySQL数据库之间的迁移就是指在主版本号相同的MySQL数据库之间进行数据库移动。

举例:

将www.abc.com主机上的MySQL数据库全部迁移到www.bcd.com主机上:

mysqldump -h www.abc.com -u root -ppassword dbname | mysql -h www.bcd.com -uroot -ppassword

说明:

mysqldump导入的数据直接通过管道符|,传给mysql命令导入到主机www.bcd.com数据库中,dbname为需要迁移的数据库名称,如果需要迁移全部的数据库,可以使用参数 --all-databases

2.不同版本的MySQL数据库之间的迁移

MySQL服务器升级的时候,需要先停止服务,然后卸载旧版本,并安装新版本MySQL,这种更新方法很简单,如果想保留旧版本中的用户访问控制信息,需要备份MySQL中的mysql数据库,在新版本MySQL安装完成之后,重新读入mysql备份文件中的信息;

旧版本与新版本的字符集不同时,迁移过程需要对默认字符集进行修改,不然可能无法正常显示结果;

对于InnoDB引擎的表,一般只能使用mysqldump工具将数据导出,然后使用mysql命令导入到目标服务器上。

从新版本向旧版本迁移数据的时候,需要特别的小心,最好使用mysqldump命令导出,然后导入目标数据库中;

3.不同数据库之间的迁移

数据库迁移可以使用一些工具,例如在Windows系统下,可以使用MyODBC实现MySQL和SQL Server之间的迁移。

MySQL官方提供的工具MySQL Migration Toolkit也可以实现在不同数据库间进行数据迁移;

4.表的导出和导入

MySQL数据库中的数据可以导出成SQL文本文件、xml文件或者HTML文件。

1.使用SELECT…INTO OUTFILE导出文本文件

MySQL数据库导出数据的时候,允许使用包含导出定义的SELECT语句进行数据导出操作。该文件被创建到服务器主机上,因此必须拥有文件写入权限(FILE权限),才能使用此语法。

语法格式:

SELECT columnlist FORM table WHERE condition INTO OUTFILE ‘filename’ [OPTIONS]

[OPTIONS]选项:

● FIELDS TERMINATED BY ‘value’

● FIELDS [OPTIONALLY] ENCLOSED BY ‘value’

● FIELDS ESCAPED BY ‘value’

● LINES STARTING BY ‘value’

● LINES TERMINATED BY ‘value’

说明:filename不能是一个已经存在的文件;

OPTIONS部分语法包括FIELDS部分的语法和LINES子句,其可能的取值有:

FIELDS TERMINATED BY ‘value’:

设置字段之间的分隔字符,可以为单个或者多个字符,默认情况下为制表符‘\t’

FIELDS [OPTIONALLY] ENCLOSED BY ‘value’:

设置字段的包围字符,只能为单个字符,如果使用了OPTIONALLY,则只有CHAR和VERCHAR等字符数据字段被包括;

FIELDS ESCAPED BY ‘value’:

设置如何写入或者读取特殊字符,只能为单个字符,即设置转义字符,默认值为“\”

LINES STARTING BY ‘value’:

设置每行数据开始字符,可以为单个或者多个,默认不使用任何字符

LINES TERMINATED BY ‘value’:

设置每行数据结尾的字符 可以为单个或者多个字符,默认值为‘\n’;

注意:FIELDS和LINES两个子句是可选的,如果同时指定,FIELDS必须位于LINES的前面;

2.使用mysqldump命令导出文本文件

mysqldump工具不仅可以将数据导出为包含CREATE、INSERT的SQL文件,也可以导出为纯文本文件;

mysqldump -T path-u root -p dbname [tables] [OPTIONS]

--OPTIONS选项:

● --fields-terminated-by=value

● --fields-enclosed-by=value

● --fields-optionally-enclosed-by=value

● --fields-escaped-by=value

● --lines-terminated-end-by=value

说明:只有指定了T参数才可以导出为纯文本文件;path表示导出数据的目录;tables为指定要导出表的名称;如果不指定,将导出数据库dbname中的所有的表;

[options]取值:

● --fields-terminated-by=value:

设置字段之间的分隔字符,可以为单个或者多个字符,默认情况下为制表符‘\t’

● --fields-enclosed-by=value:

设置字段的包围字符;

● --fields-optionally-enclosed-by=value:

设置字段的包围字符,只能为单个字符,如果使用了OPTIONALLY,则只有CHAR和VERCHAR等字符数据字段被包括;

● --fields-escaped-by=value:

控制如何写入或者读取特殊字符,只能为单个字符,及设置转义字符,默认为反斜线“\”;

● --lines-terminated-end-by=value:

设置每行数据结尾的字符,可以为单个或者多个字符,默认值为‘\n’

3.使用MySQL命令导出文本文件

mysql是一个功能丰富的工具命令,使用MySQL还可以在命令行模式下执行SQL指令将查询结果导入到文本文件中。相比mysqldump,MySQL工具导出的结果可读性更强。

如果MySQL服务器是一个单独的机器,用户是在一个client上进行操作,用户要把数据导入到client机器上,可以使用mysql -e语句;

使用MySQL导出数据文本文件语句的基本格式如下:

mysql -u root -p --execute=”SELECT语句” dbname > filename.txt

使用MySQL命令还可以指定查询结果的显示格式:

如果某行记录字段很多,可能一行不能完全显示,可以使用--vartical参数,将每条记录分为多行显示;

【将查询结果导出到HTML文件中】

mysql -u root -p --html --execute=”SELECT语句” dbname > filename.html

【将查询结果导出到xml文件中】

mysql -u root -p --xml --execute=”SELECT语句” dbname > filename.xml

4.使用LOAD DATA INFILE方式导入文本文件

LOAD DATA INFILE 语句用于高速的从一个文本文件中读取行,并装入一个表中。文件名称必须为文字字符串。

LOAD DATA INFILE ‘路径+文件名.txt’ INTO TABLE tablename [OPTIONS] [IGNORE number LINES]

注意:如果导出的.txt文件中指定了一些特殊的字符,因此还原语句中也要指定这些字符,以确保还原之后数据的完整性和正确性;

--OPTIONS选项

● FIELDS TERMINATED BY ‘value’

● FIELDS [OPTIONALLY] ENCLOSED BY ‘value’

● FIELDS ESCAPED BY ‘value’

● LINES STARTING BY ‘value’

● LINES TERMINATED BY ‘value’

可以看到LOAD DATA 语句中,关键字INFILE后面的filename文件为导入数据的来源;

tablename表示待导入的数据表名称;

OPTIONS部分语法包括FIELDS部分的语法和LINES子句,其可能的取值有:

FIELDS TERMINATED BY ‘value’:

设置字段之间的分隔字符,可以为单个或者多个字符,默认情况下为制表符‘\t’

FIELDS [OPTIONALLY] ENCLOSED BY ‘value’:

设置字段的包围字符,只能为单个字符,如果使用了OPTIONALLY,则只有CHAR和VERCHAR等字符数据字段被包括;

FIELDS ESCAPED BY ‘value’:

设置如何写入或者读取特殊字符,只能为单个字符,即设置转义字符,默认值为“\”

LINES STARTING BY ‘value’:

设置每行数据开始字符,可以为单个或者多个,默认不使用任何字符

LINES TERMINATED BY ‘value’:

设置每行数据结尾的字符 可以为单个或者多个字符,默认值为‘\n’;

[IGNORE number LINES]

选项表示忽略文件开始处的行数,number表示忽略的行数。执行LOAD DATA语句需要FILE权限;

5.使用mysqlimport命令导入文本文件

使用mysqlimport命令可以导入文本文件,并且不需要登录MySQL客户端。

使用mysqlimport语句需要指定所需的选项、导入的数据库名称以及导入的数据文件的路径和名称。

mysqlimport命令的基本语法如下:

mysqlimport -u root -p dbname filename.txt [OPTIONS]

[options]取值:

● --fields-terminated-by=value:

设置字段之间的分隔字符,可以为单个或者多个字符,默认情况下为制表符‘\t’

● --fields-enclosed-by=value:

设置字段的包围字符;

● --fields-optionally-enclosed-by=value:

设置字段的包围字符,只能为单个字符,如果使用了OPTIONALLY,则只有CHAR和VERCHAR等字符数据字段被包括;

● --fields-escaped-by=value:

控制如何写入或者读取特殊字符,只能为单个字符,及设置转义字符,默认为反斜线“\”;

● --lines-terminated-end-by=value:

设置每行数据结尾的字符,可以为单个或者多个字符,默认值为‘\n’

● --ignore-lines=n

忽略数据文件的前n行;

注意:mysqlimport命令不能指定导入数据库的表名称,数据表的名称由导入文件名称决定,即文件名作为表名,导入数据之前该表必须存在。

相关推荐:

mysql 数据库 备份还原命令

Mysql数据库备份和还原命令备忘

以上就是mysql备份还原库命令方法解析(长文)的详细内容,更多请关注Gxl网其它相关文章!

人气教程排行