mysql数据库的备份和还原的总结

时间:2021-07-08 14:53:51

mysql数据库的备份和还原的总结

(来自一运维同事的总结)

1. 备份方式:

  1. 热备:数据库在线进行备份,不影响读和写的在线备份方式!
  2. 温备:数据库在线进行备份,对表备份时先锁定写操作,仅可以执行读操作的备份方式!
  3. 冷备:离线备份,也就是关闭mysql后备份数据库。

2. 备份策略:

  1. 完全备份:备份全部数据。(可能备份某一个库的数据,也可能备份N个库的数据)。
  2. 增量备份:仅备份上次完全备份或增量备份以后发生了变化的数据。
  3. 差异备份:第一次完全备份后,将后续发生变化的数据进行备份,时间长了,会很占磁盘空间。

3. 物理备份和逻辑备份及区别:

  1. 物理:直接复制数据文件,备份和恢复操作都比较简单,能k够跨mysql的版本,恢复速度快,属于文件系统级别。
  2. 逻辑:备份表中的数据和代码,将数据导出至文本文件中,必要时还原。# 备份或恢复都需要mysql服务器进程参与,速度慢,数据转换成文本字符时,丢失浮点数在精度方面使用文本处理工具直接对其处理,可移植性强,恢复简单、备份的结果为ASCII文件,可以编辑,与存储引擎无关,可以通过网络备份和恢复。

一、备份/还原表            # 语法:mysqldump [options] [db_name [tbl_name ...]]

a)     备份单个表:

Mysqldump -uroot -p db_1 tb_1 >/xxx/xxxtb.sql

#备份db_1库的tb_1表到/xx/目录下,并命名为xxtb.sql文件。

b)  备份多个表

Mysqldump -uroot -p db_1 tb_1 tb_2 tb_3 >/xxx/xxxtb.sql

#备份db_1库中的tb_1,tb_2,tb_3表。

c)  恢复表:                    # mysql [OPTIONS] [database]

1.  mysql -p yq_1 < /xxtb.sql  #此时,备份xxtb.sql中的语句将被执行,开始使用xxtb.sql恢复yq_1库中的表。

二、备份/还原数据库 

mysqldump [options] [db_name [tbl_name ...]]

1. 备份单个数据库:         示例:mysqldump -uusername -p yq_1 > /xxx/xxxdb.sql

2. 备份多个数据库:          示例:mysqldump -uusername -p --databases db_1 db_2 > db.sql

3. 备份整个数据库:          示例:mysqldump -uusername -p --all-databases > /root/db.sql

 

还原数据库

mysql -u root -p [dbname] < backup.sq

1. 还原单个数据库:          示例:mysql -uusername -p yq_1 < /db.sql

# 还原时必须要指定要还原的数据库yq_1且须存在(因为单个库备份不备份数据库信息,只备   份数据库中表的信息。),不存在则须手动创建后,才可进行还原。

2. 还原多个数据库:          示例:mysql -uusername -p < db.sql

#还原多个数据库时,无需指定要还原哪些数据库,因为备份中备份了数据库信息,有数据库不存在的话会自动创建,存在则将其覆盖。

3. 还原整个数据库:         示例:mysql -uusername -p < /db.sql

三、单表恢复

有时候会面临这样一些情况,备份了整个数据库,却只需要恢复其中的某个table或某几个tables。

1.备份整个数据库,恢复单张表。

比如备份一个库test,相当于将test库中的所有表备份。mysql也是这样做的,在查看整个sp2p库的备份时,可以发现,整个备份中全部为创建表结构和插入表数据的sql语句,其中对每个表的备份分为2部分,如下

#备份表结构

--

-- Table structure for table `t_banks`

--

......创建表结构语句........

#备份表数据

--

-- Dumping data for table `t_banks`

--

 

 

 

四、使用source命令恢复数据库和表

1. 恢复备份单个表的备份: mysql>source /tb.sql 

注意:使用source恢复单个表,则需要进入所在数据库中,且如果表不存在需手动创建。

  使用source恢复单个库,同理。

  使用source恢复多个库,也无需手动创建数据库。

注意:

1. mysqldump命令备份单个库时(也就是不使用--databases/--all-databases等系列的参数时),只备份单个库下的所有表,注意其备份不包括备份(创建/删除)数据库的操作。

 

例如:

备份好单个库后,还原时,如mysqldump -p db1 > /xxx/db.sql,备份单个库,msyqldump命令只备份db1数据库下的所有表,而不包括创建数据库的CREATE DATABASE语句,因此如果把db1删了使用db.sql还原时会出现:“No database selected”的错误信息,因此在还原前必须先创建db1(也可以自定义数据库名为db2、db3等)数据库,或者可以直接用mysqldump -u root -p --databases db_1>db.sql,这样不仅备份了所有数据库下的表结构,而且包括了创建数据库的语句。