mysql 数据备份与还原-蒲公英云

`一、数据备份`

1.1 使用mysqldump命令备份

mysqldump是MySQL提供的•个非常有用的数据库备份工具-mysqldump命令执行时，可以将数据库备份成一个文本文件，该文件中实际上包含了多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据。

1.1.1 语法

mysqldump -u user -h host -ppassword dbname [tbname, [tbname...] > filename.sql

user 表示用户名称；
host 表示登录用户的主机名称；
password 为登录密码；
dbname 为需要备份的数据库名称；
tbname 为dbname数据库中需要备份的数据表，可以指定多个需要备份的表；
右箭头符号'>' 告诉mysqldump将备份数据表的定义和数据写入备份文件；filename.sql为备份文件的名称。

1.1.2 示列

-- 备份单个数据库booksdb中的所有表的数据
mysqldump -u root -p booksdb > D:\booksdb.sql
-- mysqldump备份数据库中的某个表
mysqldump -u root -p booksDB books > D:\booksdb.sql
-- mysqldump备份多个数据库
mysqldump -u root -p -databases booksDB test > D:\booksdb.sql
-- 备份服务器中的所有数据库
mysqldump -u root -p --all-database > D:\booksdb.sql

1.2 mysqlhotcopy 快速备份

mysqlhotcopy是一个Perl脚本，最初由Tim Bunce编写并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库目录所在的机器上，并且只能备份MylSAM类型的表.mysqlhotcopy在Unix系统中运行。

mysqlhotcopy db_name_1,... db_name_n /path/to/new_directory

示列

mysqlhotcopy -u root -p test /usr/backup

`二、数据还原`

2.1 使用mysql导入sql文件还原

对于己经备份的包含CREATE, INSERT语句的文本文件，可以使用mysql命令导入到数据库中。

mysql -u root -p booksDB < D:\booksdb.sql

2.2 使用source命令

如果已经登录mysql可以使用source命令导入sql文件。
执行source命令前，必须使用use语句选择数据库.不然，恢复过程中会出现“ERROR 1046 (3D000): No database selected”的错误。

source < D:\booksdb.sql

2.3 mysqlhotcopy 快速恢复

mysqlhotcopy备份后的文件也可以用来恢复数据库，在MySQL服务器停止运行时，将备份的数据库文件复制到MySQL存放数据的位置(MySQL的data文件夹)，重新启动MySQL服务即可。如果以根用户执行该操作，必须指定数据库文件的所有者，输入语句如下：

chown -R mysql.mysql /var/lib/mysql/dbname
-- 复制test数据库到data目录
cp -R /usr/backup/test usr/local/mysql/data

`三、数据库迁移`

数据库迁移就是把数据从一个系统移动到另一个系统上。数据迁移有以下原因：
(1) 需要安装新的数据库服务器。
(2) MySQL版本更新。
(3) 数据库管理系统的变更(如从Microsoft SQL Server迁移到MySQL)。

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

mysqldump -h www.bac.com -uroot -ppassword dbname |
mysql -h www.bcd.com -uroot -ppassword

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

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

-- 现在就版本mysql数据库中导出数据，在把导出的文件使用mysql导入新版本中
mysqldump -h www.bac.com -uroot -ppassword dbname > \booksdb.sql
-- 在新版本中导入数据
mysql -h www.bcd.com -uroot -ppassword < \booksdb.sql

`四、表的导入导出`

4.1 导出表数据

4.1.1 mysqldump命令导出

-- 改语句会在 path 目录下创建tables.sql和tables.txt两个文件
mysqldump -T path -u root -p dbname [tables] [OPTIONS]

OPTIONS可以设置下列值：

-fields-terminated-by=value :设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符”\t”.
-fields-enclosed-by=value :设置字段的包围字符.
-fields-optional ly-enclosed-by=value :设置字段的包围字符，只能为单个字符，只能包括 CHAR和VERCHAR等字符数据字段.
-fields-escaped-by=value :控制如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为反斜线
-lines-terminated-by=value :设置每行数据结尾的字符，可以为单个或多个字符，默认值为

示列

mysqldump命令将test数据库中的person表中的记录导出到文本文件，使用 FIELDS选项，要求字段之间使用逗号间隔，所有字符类型字段值用双引号括起来，定义转义字符为问号”?”，每行记录以回车换行符”\r\n”结尾

mysqldump -T /backup test person -u root -p -fields-terminated-by=, -fields-optionally-enclosed-by= \" -fields-escaped-by=? -lines-terminated-by=\r\n

4.1.2 其他方式

点击查看其他导出数据方法

4.2 导入表数据

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

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

语法

LOAD DATA INFILE 'filename.txt' INTO TABLE tablename [OPTIONS] [IGNORE number LINES]
OPTIONS 选项
    FIELDS TERMINATED BY 'value'
    FIELDS [OPTIONALLY] ENCLOSED BY 'value'
    FIELDS ESCAPED BY 'value'
    LINES STARTING BY 'value'
    LINES TERMINATED BY 'value'

INFILE 后面的filename文件为导入数据的来源；
tablename 表示待导入的数据表名称；
[OPTIONS] 为可选参数选项，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权限。

示列：使用LOAD DATA命令将C:\personl.txt文件中的数据导入到test数据库中的person 表，使用FIELDS选项和LINES选项，要求字段之间使用逗号间隔，所有字段值用双引号括起来，定义转义字符为单引号

LOAD DATA INFILE 'C:\personl.txt* INTO TABLE test.person
FIELDS
TERMINATED BY ','
ENCLOSED BY '\"'
ESCAPED BY '\'
LINES
TERMINATED BY '\r\n';

4.2.2 使用mysqlimport命令导入文本文件

使用mysqlimport可以导入文本文件，并且不需要登录MySQL客户端。mysqlimport命令提供许多与LOAD DATA INFILE语句相同的功能，大多数选项直接对应LOAD DATA INFILE子句。使用mysqlimport语句需要指定所需的选项、导入的数据库名称以及导入的数据文件的路径和名称。

语法

mysqlimport -u root-p dbname filename.txt [OPTIONS] 
OPTIONS 选项
    -fields-terminated-by=value
    —fields-enclosed-by=value
    —fields-optionally-enclosed-by=value
    -fields-escaped-by=vaiue
    -lines-terminated-by=value
    -ignore-lines=n

dbname 为导入的表所在的数据库名称。
注意，mysqlimport命令不指定导入数据库的表名称，数据表的名称由导入文件名称确定，即文件名作为表名，导入数据之前该表必须存在。
［OPTIONS］ 为可选参数选项，其常见的取值有：
• —fields-tenninated-by= ‘value’: 设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符tt\t
• -fields-enclosed-by= ‘value1’: 设置字段的包围字符。
• —fidds-optionally-enclosed-by= ‘value’: 设置字段的包围字符，只能为单个字符，包括CHAR 和VERCHAR等字符数据字段。
• -fields-escaped-by= ‘value’: 控制如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为反斜线
• -lines-terminated-by= ‘value’: 设置每行数据结尾的字符，可以为单个或多个字符，默认值为侦・
• -ignore-lines=n: 忽视数据文件的前n行。 .

示列：

使用mysqlimport命令将C:\backup目录下的person.txt文件内容导入到test数据库中，字段之间使用逗号“,”间隔，字符类型字段值用双引号括起来，将转义字符定义为问号 “？”，每行记录以回车换行符”\r\n”结尾

mysqlimport -u root -p test C:\backup/person.txt —fields-terminated-by=, —fields-optionally-enclosed- by=\" —fields-escaped-by=? —lines-terminated-by=\r\n

mysqlimport选项

mysqlimport常见的选项有：

-columns=column_list, -c column list:该选项采用逗号分隔的列名作为其值。列名的顺序指示如何匹配数据文件列和表列•
-compress, -C:压缩在客户端和服务器之间发送的所有信息（如果二者均支持压缩）。
-d, -delete:导入文本文件前清空表。
-force, -f:忽视错’误.例如，如果某个文本文件的表不存在，继续处理其他文件。不使用—force,如果•表不存在，则mysqlimport退出。
-host=host name, -h host name:将数据导入给定主机上的MySQL服务器。默认主机是
localhosto
-ignore, -i:参见—replace选项的描述。
-ignore-lines=n:忽视数据文件的前n行。
-local, -L:从本地客户端读入输入文件。
-lock-tables, -1:处理文本文件前锁定所有表以便写入。这样可以确保所有表在服务器上保持同步。
-password[=password], -p[password]:当连接服务器时使用的密码。如果使用短选项形式 (-p ),选项和密码之间不能有空格。如果在命令行中-password或・p选项后面没有密码值，
则提示输入一个密码。
-port=port_num, -P port num:用于连接的 TCP/IP 端口号。
-protocol{TCP I SOCKET | PIPE | MEMORY}:使用的连接协议。
-replace, -r —replace和“ignore选项控制复制唯一键值已有记录的输入记录的处理。如果指定-replace,新行替换有相同的唯一键值的已有行；如果指定–ignore,复制已有的唯一健值的输入行被跳过；如果不指定这两个选项，当发现一个夏制键值时会出现一个错误，并且忽视文本文件的剩余部分.
-silent, -s:沉默模式.只有出现错误时才输出信息.
-user=user_name, -u user name:当连接服务器时MySQL使用的用户名。
-verbose, -v:冗长模式。打印出程序操作的详细信息。
-version, -V:显示版本信息并退出。