MySQL数据备份和恢复

数据库备份和恢复是日常维护的必需技能,本篇介绍3种MySQL数据备份和恢复的方式以应对不同的场景。

一.整库迁移和备份

适合于所有场景。建议仅做整库迁移时使用。

1.找到MySQL数据存放的路径。

在mysql的配置文件中,有个配置项datadir,就是数据存放的目录。也可在mysql中使用语句 show global variables like ‘%datadir%’;  查看。

2.停源MySQL服务。

3.拷贝源MySQL了数据目录下的ibdata1文件和需要迁移的数据库文件夹(如要迁移的是test数据库,就拷贝test文件夹) 至 目标MySQL的数据目录。

4.启动目标MySQL,就能看到和源MySQL一样的数据库和表及数据了。

 

二.以sql文件备份和恢复

适合于百万级以下数据的场景。

导出数据

这种方式导出,dump文件默认是sql语句,包含建表语句和insert语句。

导入数据

 

三.单表按指定格式文本导出导入数据

导出数据

支持这种方式的导出命令有mysqldump,select … into outfile

使用mysqldump命令

其中mysqldump的 option 参数可以是以下选项:

  • –fields-terminated-by=name(字段分隔符);
  • –fields-enclosed-by=name 字段引用符,比如每个字段用双引号括起来;
  • –fields-optionally-enclosed-by=name(字段引用符,只用在 char、varchar 和 text 等字符型字段上
  • –fields-escaped-by=name(转义字符);
  • –lines-terminated-by=name(记录结束符)。

使用select … into outfile

其中 option 参数可以是以下选项:

  • fields terminated by ‘字符’ 字段分隔符,默认字符为制表符’\t’
  • fields [optionally] enclosed by ‘单字符’ 字段引用符,加上optionally后在数字类型上不会有引用符号
  • fields escaped by ‘单字符’ 转义字符,默认为’\’
  • lines starting by ‘字符’ 每行前都加此字符,默认为空
  • lines terminated by ‘字符’ 行结束符,默认为’\n’

导入数据

支持这种方式导入的命令有load data infile … 和 mysqlimport

使用load data infile … 方式

option 可以是以下选项:

  • FIELDS TERMINATED BY ‘string’(字段分隔符,默认为制表符’\t’);
  • FIELDS [OPTIONALLY] ENCLOSED BY ‘char’   字段引用符,如果加 OPTIONALLY 选项则只会做用在char, varchar和text等字符型字段上,其他类型字段默认不使用引用符
  • FIELDS ESCAPED BY ‘char’(转义字符,默认为’\’);
  • LINES STARTING BY ‘string’(每行前都加此字符串,默认”);
  • LINES TERMINATED BY ‘string’(行结束符,默认为’\n’);
  • IGNORE number LINES(忽略输入文件中的前 n 行数据);
  • (col_name_or_user_var,…) (按照列出的字段顺序和字段数量加载数据);
  • SET col_name = expr,… 将列做一定的数值转换后再加载。

其中 char 表示此符号只能是单个字符,string 表示可以是字符串。FILELD 和 LINES 和前面 SELECT …INTO OUTFILE…的含义完全相同,不同的是多了几个不同的选项。

使用mysqlimport 方式

其中 option 参数可以是以下选项:

  • –fields-terminated-by=name(字段分隔符);
  • –fields-enclosed-by=name(字段引用符);
  • –fields-optionally-enclosed-by=name(字段引用符,只用在 char、varchar 和 text 等字符型字段上
  • –fields-escaped-by=name(转义字符);
  • –lines-terminated-by=name(记录结束符);
  • — ignore-lines=number(或略前几行)。

参数的含义与mysqldump一样,这里不重复了。

 

补充

mysql有个非常有用的参数 –max_allowed_packet,这个参数用于设置数据包最大的容量,当导入或导出时若报错说包数据量太大,可调大这个参数的值。

 


发表评论

电子邮件地址不会被公开。 必填项已用*标注