备份和恢复

备份和恢复

MySQL数据库的日常管理中,通常会进行数据库的备份和恢复。

MySQL数据库备份和恢复的方法

MySQL数据库中的备份和恢复组件为存储在MySQL中的关键数据提供了重要的保护手段。

使用SQL语句备份和恢复表数据

在MySQL5.5中,可以使用select into … outfile语句将表数据导出到一个文本文件中进行备份。并可以使用load data … infile语句恢复先去备份的数据。这种方法有一点不足,就是只能导出或导入数据的内容,而不包括表的结构,若表的结构文件损坏,则必须先设法恢复原来表的结构。

select into … outfile 语句

导出备份语句select into … outfile的语法格式为:

1
select * into outfile 'file_name' [character set charset_name] export_options | into dumpfile 'file_name'

其中,export_options的格式为:

1
2
3
4
5
[fields
[terminated by 'string']
[[optionally] enclosed by 'char']
[escaped by 'char']
]

语法说明如下:

  • 这个语句的作用是把select语句选中的所有数据行写入到一个文件中。文件默认在服务器主机上创建,并且文件名不能是已存在的,否则可能将原文件覆盖。如果要将该文件写入到一个特定的位置,则要在文件名前面加上具体的路径。在文件中,导出的数据行会以一定的形式存放,其中空值用”\N”表示。
  • 导出语句中使用关键字outfile时,可以在export_options中加入以下两个自选的子句。它们的作用是决定数据行在备份文件中存放的格式:

    1. fields子句:在fields子句中有三个亚子句,分别是terminated by、[optionally] enclosed by和escaped by。如果指定了fields 子句,则这三个亚子句至少需要指定一个。其中,terminated by用来指定字段值之间的符号,例如,“terminated by ‘,’”,表示指定逗号为两个字段值之间的标志;enclosed by子句用来指定包裹文件中字符值的符号,例如,“enclosed by ‘“”‘”表示文件中字符值放在双引号之间。若加上关键字optionally则表示所有的值都放在双引号之间;escaped by子句用来指定转义字符,例如,“escaped by '*'”“*”指定为转义字符,取代“\”,如空格将表示为“*N”
    2. lines子句:在lines子句中使用terminated by指定一个数据行结束的标志,如“lines terminated by ‘?’”表示数据行以问号为结束标志。

    如果fields和lines子句都不指定,则默认声明是下面的子句:

    1
    2
    fields terminated by '\t' enclosed by '' escaped by '\\'
    lines terminated by '\n'
  • 导出语句中使用的是关键字dumpfile而非outfile时,导出的备份文件里面所有的数据行都会彼此紧挨着放置,即值和行之间没有任何标记。

load data … infile语句

导入恢复语句load data … infile 语句的语法格式是:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
load data [low_priority | concurrent] [local] infile 'infile.txt'
[replace | ignore]
into table tbl_name
[fields
[terminated by 'string']
[[optionally] enclosed by 'char']
[escaped by 'char']
]
[lines
[starting by 'string']
[terminated by 'string']
]
[ignore number lines]
[(col_name_or_user_var, ... )]
[set col_name = expr, ...]

语法说明如下:

  • low_priority | concurrent:若指定low_priority,则延迟该语句的执行;若指定concurrent,则当load data正在执行的时候,其他线程可以同时使用该表的数据。
  • local:若指定了local,则文件会在客户主机上的客户端读取,并被发送到服务器。文件会被给予一个完整的路径名称,以指定确切的位置。如果给定的是一个相对的路径名称,则此名称会被理解为相对启动客户端时所在的目录。若没有指定local,则文件必须位于服务器的主机上,并且被服务器直接读取。与让服务器直接读取相比,使用local的速度会略慢些,这是由于文件的内容必须通过客户端发送到服务器上,
  • file_name:待导入的数据库备份文件,文件中保存了待载入数据库的所有数据行。输入文件可以手动创建,也可以使用其它的程序创建。导入文件时可以指定文件的绝对路径。若不指定路径,则服务器默认在默认数据库的数据库目录中读取。
  • replace | ignore:如果指定replace,则当导入文件中出现与数据库中原有行相同的唯一关键字时,输入行会替代原有行;如果指定ignore,则把与原有行有相同的唯一关键字的输入行跳过。
  • tbl_name:指定需要导入数据的表名,该表在数据库中必须存在,表结构必须与导入文件的数据行一致。
  • fields子句:此处的fields子句和select … into outfile语句中类似,用于判断字段之间和数据行之间的符号。
  • lines子句:terminated by亚子句用来指定一行结束的标志;starting by亚子句则指定一个前缀,导入数据行时,忽略数据行中该前缀和前缀之前的内容。如果某行不包括该前缀,导入数据行时,忽略数据行中的该前缀和前缀之前的内容。如果某行不包括该前缀,则整个数据行被跳过。
  • ignore number lines:这个选项可以用于忽略文件的前几行。例如,可以使用ignore 1 lines 来跳过第一行。
  • col_name_or_user_var:如果需要载入一个表的部分列,或者文件中字段值顺序与源表中列的顺序不同时,就必须指定一个列清单,其中可以包含列名或用户变量。例如:

    1
    2
    load data infile 'backupfile.txt'
    into table backupfile (cust_id, cust_name, cust_address);
  • set 子句:set 子句可以在导入数据时修改表中列的值。

例如,备份数据库db_school.tb_student。

1
2
3
4
5
select * from db_school.tb_student
into outfile '/Users/mr.cheng/sql/backupfile.txt'
fields terminated by ','
optionally enclosed by '"'
lines terminated by '?';

这里会遇到一个非常让人头疼的问题,不过幸好最后找到了解决办法。详细解决方法可以参考我的另一篇博客《如何解决MySQL导出数据时的权限设置问题?》

然后使用下面的语句将备份数据导入数据库db_school中一个和tb_school表结构相同的空表tb_student_copy中:

1
2
3
4
5
6
# 然后使用下面的语句将备份数据导入数据库db_school中一个和tb_school表结构相同的空表tb_student_copy中:
load data infile '/Users/mr.cheng/sql/backupfile.txt'
into table db_school.tb_student_copy
fields terminated by ','
optionally enclosed by '"'
lines terminated by '?';

需要注意,上面的语句在我的电脑上直接运行是会出错的,出错的原因是刚刚导出的文件不是UTF8编码的,然后我。。。用Stata转码的。

在导入数据时需要特别注意,必须根据数据备份文件中数据行的格式来指定判断的符号。例如,在backupfile.txt文件中字段值是以逗号隔开的,导入数据时也一定要指定用逗号隔开,即与导出语句相对应。

另外需要注意的是,在多个用户同时使用MySQL数据库的情况下, 为了得到一个一致的备份,需要在指定的表上使用lock tables table_name read语句做一个读锁定,以防止备份的过程中,表被其他用户更新;而当恢复数据时,需要使用loack tables table_name write语句做一个写锁定,以防止发生数据冲突。在数据备份或恢复之后需要使用unlock tables语句对该表进行解锁。

使用MySQL客户端使用程序备份和恢复数据

MySQL提供了许多免费的客户端实用程序,且存放在MySQL安装目录下的bin子目录中,这些客户端实用程序可以连接到MySQL服务器进行数据库的访问,或者对MySQL执行不同的管理任务。其中,mysqldump和mysqlimport就分别是两个常用于实现数据库备份和恢复的实用工具。

使用MySQL客户端实用程序的方法

首先进入bin目录(实际上没有必要的,似乎安装MySQL的似乎这个路径就已经被添加到了环境变量中,所以可以全局使用):

1
cd '/usr/local/mysql-8.0.11-macos10.13-x86_64/bin/'

使用mysqldump备份数据

  1. 备份表:
    1
    mysqldump [options] database [tables] > file_name

例如,使用mysqldump备份db_school.tb_student:

1
mysqldump -h localhost -u root -p12345 db_school tb_student > ~/Desktop/file.sql

然后就会得到一个sql文件,运行这个sql文件即可恢复数据库。

  1. 备份数据库:
    1
    mysqldump [options] --databases [options] DB1 [DB2 DB3] > file_name

例如,备份数据库基础数据:

1
mysqldump -u root -p12345 --databases 基础数据 > ~/Desktop/基础数据.sql

  1. 备份整个数据库系统:
    1
    mysqldump [options] --all-databases [options] > file_name

例如:

1
mysqldump -u root -p12345 --all-databases > ~/Desktop/alldata.sql

需要注意的是,尽管使用mysqldump程序可以有效地导出表的结果,但是恢复数据的似乎,倘若所需恢复的数据量很大,备份文件中众多的SQL语句会使恢复的效率降低。因此,可以在mysqldump命令中使用--tab=选项来分开数据和创建表的SQL语句。--tab=选项会在选项中“=”后面指定的目录里分别创建存储数据内容的txt文件和包含创建表结构的sql文件。另外,该选项不能与--databases选项或者--all-databases选项同时使用,并且mysqldump必须运行在服务器主机上。

例如,将数据库db_school中所有表的结构和数据分别存放备份:

1
sudo mysqldump -u root -p12345 db_school tb_student  --tab='/Users/mr.cheng/sql/'

需要注意的是,这个似乎需要导入数据,因此需要导入到MySQL具有权限的文件夹sql里面(我把这个文件夹的权限分配给了MySQL)。

使用mysql命令恢复数据

例如,假设db_school数据库遭到破坏,需要使用db_school.sql进行恢复:

1
2
mysqldump -u root -p12345 --databases db_school > ~/Desktop/db_school.sql
mysql -u root -p12345 db_school < ~/Desktop/db_school.sql

再例如,只恢复一个表:

1
2
mysqldump -u root -p12345 db_school tb_student > ~/Desktop/tb_student.sql
mysql -u root -p12345 db_school < ~/Desktop/tb_student.sql

使用mysqlimport程序恢复数据

倘若只是恢复数据表中的数据,可以使用mysqlimport程序完成。这个程序提供了load data … infile语句的一个命令行接口,它发送一个load data infile命令到服务器来运作,其大多数选项直接对应load data … file语句:

1
mysqlimport [options] database textfile ...

语法说明如下:

  1. 常见的选项有:
    • -d / –d:在导入文本文件之前清空表中的所有数据行;
    • -l / –lock-tables:在处理任何文本之前锁定所有的表,以保证所有的表在服务器上同步,但对于InnoDB类型的表这不必进行锁定。
    • –low-priority / –local / –replace / –ignore:分别对应load data … infile语句中的low_priority / local / replace / ignore关键字。
  2. textfile: 存储备份数据的文本文件名。使用mysqlimport命令恢复数据的时候,mysqlimport会剥去这个文件的扩展名,并使用它觉得向数据中哪个表导入文件的内容。例如,file.txt / file.sql / file都会被导入到名为file的表中,因此备份文件需要根据需要恢复表命名。另外,在该命令中需要指定备份文件的具体路径,若没有指定,则选取文件的默认位置。即MySQL安装目录下面的data目录下。

例如:

1
mysqlimport -u root -p12345 --low-priority --replace db_school /Users/mr.cheng/sql/tb_student.txt

不过由于外键的约束,上面的命令无法执行。

二进制文件的使用

在MySQL的实际操作中,数据库管理员不可能无时无刻地都在备份数据。因此,但数据遭遇丢失或者被破坏的时候,只能恢复已经备份的文件,而在这之后更新的数据就无法恢复了。遭遇到这种情形时,可以考虑使用更新日志,这是因为更新日志可以实时记录下数据库中的修改、插入和删除的SQL语句。

在MySQL5.5中,更新日志已经被二进制日志取代。二进制日志是一种更加有效的格式,并且是事务安全的方式,包含了更新日志中可用的所有信息,如关于每个更新数据库的语句的执行时间信息,而不包含没有任何修改数据的语句。这些语句以时间的形式保存,记录了数据的更改。

由于二进制日志包含了数据备份后进行的所有更新,因此二进制的主要目的在于数据恢复时最大可能地更新数据库。

开启日志文件

my.cnf文件中的[mysqld]标签下添加一行:

1
log-bin = /Users/mr.cheng/sql/二进制日志

/Users/mr.cheng/sql/二进制日志.txt是我设定的日志文件路径和名称。

加入该选项后,重启MySQL,服务器启动的时候会加载该选项,从而启动二进制日志。如果filename中有扩展名,扩展名会被忽略。MySQL服务器会为每个二进制文件名后面自动添加一个数字编号扩展名,每次启动服务器或刷新日期的时候,都会重新生成一个二进制日志文件,扩展名中的数字编号依次递增。如果未给出文件名,默认为主机名。

使用mysqlbinlog工具处理日志

  1. 查看二进制日志文件:
    例如:
    1
    sudo mysqlbinlog ~/sql/二进制日志.000001

sudo是因为这个文件夹的权限被分配给了MySQL。

  1. 使用二进制日志恢复数据:

【假如】系统管理员在本周一下午五点下班前,使用mysqldump进行了数据库db_school的一个完全备份,备份文件为alldata.sql。接着,从本周一下午5点开始启用日志,日志文件中保存了本周一下午5点至本周三上午9点的所有更改信息,在周三上午九点运行了一条日志刷新语句,即flush logs;,此时数据库自动创建了一个新的日志文件,直至周五上午十点公司数据库服务器系统崩溃。要求恢复到崩溃前的状态。

第一步,使用sql文件将数据库恢复到周一下午五点的状态:

1
mysqldump -u root -p12345 db_school < alldata.sql

第二步,使用mysqlbinlog工具将数据库恢复到周三的状态:

1
mysqlbinlog bin_log.000001 | mysql -u root -p12345

最后,使用mysqlbinlog将数据库恢复到周五的状态:

1
mysqlbinlog bin_log.000002 | mysql -u root -p12345

由于二进制文件会占用很大的硬盘资源,因此需要及时清除没用的二进制日志文件。可以使用下面的这条sql语句清除所有的日志文件:

1
reset master;

如果需要删除部分日志文件,可以使用purge master logs语句来实现:

1
2
3
purge {master | binary } logs to 'log_name';
-- 或
purge {master | binary} logs before 'date';

第一条语句用于删除指定的日志文件,第二条则用于删除指定日期前的日志文件。

# MySQL

评论

程振兴

程振兴 @czxa.top
截止今天,我已经在本博客上写了604.4k个字了!

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×