首页 > 资讯 > 数据库 >数据库备份与恢复

427

分享到

数据库备份与恢复

数据库 mysql sql 2023-08-18 07:08:48 427人浏览八月长安

摘要

数据库备份与恢复一、物理备份和逻辑备份二、mysqldump实现逻辑备份2.1 备份一个数据库2.2 备份全部数据库2.3 备份部分数据库2.4 备份部分表2.5 备份单表的部分数据2.6 排除某些表的备份2.7 只备份结构或只备

数据库备份与恢复

一、物理备份和逻辑备份
二、mysqldump实现逻辑备份
三、MySQL命令恢复数据
四、物理备份：直接复制整个数据库
五、物理恢复：直接复制到数据库目录
六、表的导入与导出
七、数据库迁移
八、删库误操作

在任何数据库环境中，总会有不确定的意外情况发生，比如停电、计算机系统中的各种软硬件故障，认为破坏、管理员误操作等不可避免的情况，可能会导致数据的丢失、 服务器瘫痪等严重的后果。存在多个服务器时，会出现主从服务器之间的数据同步问题。

为了有效防止数据丢失，并将损失降到最低，应定期对Mysql数据库服务器做备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复。主从服务器之间的数据同步问题可以通过复制功能实现。

一、物理备份和逻辑备份

物理备份： 备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，mysql中可以用xtrabackup工具进行物理备份。

逻辑备份： 对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。Mysql中常用的逻辑备份工具为mysqldump。逻辑备份就是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现。

二、mysqldump实现逻辑备份

2.1 备份一个数据库

mysqldump命令执行时，可以将数据库备份成一个文本文件，该文件中实际上包含多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据。

查出需要备份的表的结构，在文本文件中生成一个CREATE语句。
将表中的所有记录转换成一条INSERT语句。

基本语法

mysqldump -u 用户名 -h 主机名称 -p密码 待备份的数据库名称[tbname,tbname...] > 备份文件名称.sq;

说明：
备份的文件并非一定要求后缀名为.sql，例如后缀名为.txt的文件也是可以的。

2.2 备份全部数据库

若想用mysqldump备份整个实例，可以使用 –all-databases 或 -A 参数：

mysqldump -uroot -pxxxx --all-databases > all_database.sqlmysqldump -uroot -pxxxx -A > all_database.sql

2.3 备份部分数据库

使用==-databases==或 -B参数了，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定databases参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在。语法如下：

mysqldump -u user -h host -p -- databases [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql

2.4 备份部分表

比如，在表变更前做个备份。语法如下：

mysqldump -u user -h host -p 数据库的名称 [表名1][表名2]... > 备份文件名称.sql

2.5 备份单表的部分数据

有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用 –where选项了。where后面附带需要满足的条件。
举例说明：

mysqldump -uroot -p seapp student --where="id <10" > studeng_part_id10_low_bak.sql

2.6 排除某些表的备份

如果我们想备份某个库，但是某些表数据量很大或者业务关联不大，这个时候可以考虑排除掉这些表，同样的，选项==–ignore-table==可以完成这个功能：

mysqldump -uroot -p seapp --ignore-table=seapp.student > no_stu_bak.sql#通过如下指定判定文中没有student表结构grep "student" no_stu_bak.sql

2.7 只备份结构或只备份数据

只备份结构的话可以使用==–no-data简写为-d选项；只备份数据可以使用–no-create-info简写为-t==选项。

#只备份结构mysqldump -uroot -p seapp --no-data > seapp_no_data_bak.sql#使用grep命令，没有找到insert相关语句，表示没有备份数据grep "INSERT" seapp_no_data_bak.sql#只备份数据mysqldump -uroot -p seapp --no-create-info > seapp_no_create_info_bak.sql#使用grep命令,没有找到create相关语句，表示没有数据结构grep "CREATE" seapp_no_create_info_bak.sql

2.8 备份中包含存储过程、函数、事件

mysqldump备份默认是不包含存储过程，自定义函数及事件。可以使用==–routines或-R选项来备份存储过程及函数，使用–events或-E==参数来备份事件。

#使用下面的SQL可以查看当前库有哪些存储过程或者函数。SELECT SPECIFIC_NAME,ROUTINE_TYPE,ROUTINE_SCHEMA FROM infomation_schema.Routines WHERE ROUTINE_SCHEMA = "seapp";# 备份数据库的数据、函数以及存储过程mysqldump -uroot -p -R -E --databases seapp > fun_auguigu_bak.sql

三、MySQL命令恢复数据

使用mysqldump命令将数据库中的数据备份成一个文本文件。需要恢复时，可以使用mysql命令来恢复备份的数据。
MySQL命令可以执行备份文件中的CREATE语句和INSERT语句。通过CREATE语句来创建数据库和表。通过INSERT语句来插入备份的数据。
基本语法：

mysql -uroot -p [dbname] < backup.sql

其中，dbname参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。指定数据库名时，表示还原该数据库下的表。此时需要确保MySQL服务器中已经创建了该名的数据库。不指定数据库名时，表示还原文件中所有的数据库。此时sql文件中包含有CREATE DATABASE语句，不需要MySQL服务器中已存在这些数据库。

四、物理备份：直接复制整个数据库

直接将MySQL中的数据库文件复制出来，这种方法最简单，速度也最快。MySQL的数据库目录位置不一定相同：

在windows平台下，MySQL8.0存放数据库的目录通常默认为“C:\ProgramData\MySQL\MySQL Server 8.0\Data”或其他用户自定义目录下
在linux平台下，数据库目录位置通常为/var/lib/mysql
在Mac OSX平台下，数据库目录位置通常为“/usr/local/mysql/data”

但为了保证备份的一致性。需要保证：

备份前，将服务器停止。
备份前，对相关表执行FLUSH TABLES WITH READ LOCK操作。这样当复制数据库目录中的文件时，允许其他客户继续查询表。同时，FLUSH TABLES 语句来确保开始备份前将所有激活的索引页写入磁盘。

这种方式方便，快捷，但不是最好的备份方法。因为实际情况可能不允许停止MySQL服务器或者锁住表，而且这种方法对InnoDB存储引擎的表不适用。对于MyISAM存储引擎的表，这样备份和还原很方便，但是还原时最好是相同版本的MySQL数据库，否则可能会存在文件类型不同的情况。

注意，物理备份完毕后，执行 UNLOCK TABLES来结算其他客户对表的修改行为。

说明：
在MySQL版本号中，第一个数字表示主版本号，主版本号相同的MySQL数据库文件格式相同。

此外，还可以考虑使用相关工具实现备份。比如MySQLHotcopy工具。MySQLHotcopy是一个Perl脚本，它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或者单个表最快的途径，但它只能运行在数据库目录所在的机器上，并且只能备份MyISAM类型的表。多用于mysql5.5之前。

五、物理恢复：直接复制到数据库目录

步骤：

删除备份的数据库中指定的数据
将备份的数据库数据拷贝到数据目录下，并重启MySQL服务器
查询相关表的数据是否恢复，需要使用下面的chown操作。

要求：

必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同。
- 因为只有MySQL数据库主版本号相同时，才能保证这两个MySQL数据库文件类型是相同的。
这种方式对MyISAM类型的表比较有效，对于InnoDB类型的表则不可用
- 因为InnoDB表的表空间不能直接复制
在Linux操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成mysql，命令如下：
```
chown -R mysql.mysql /var/lib/mysql/dbname
```
其中，两个mysql分别表示组和用户；“-R”参数可以改变文件夹下的所有子文件的用户和组；“dbname”参数表示数据库目录。

提示：
Linux操作系统下的权限设置非常严格。通常情况下，MySQL数据库只有root用户和mysql用户组下的mysql用户才可以访问，因此将数据库目录复制到指定文件夹后，一定要使用chown命令将文件夹的用户组变为mysql，将用户变为mysql。

六、表的导入与导出

6.1 表的导出

6.1.1 使用SELECT…INTO OUTFILE导出文本文件

在MySQL中，可以使用SELECT…INTO OUTFILE语句将表的内容导出成一个文本文件。

MySQL默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作。
查询secure_file_priv值：
在这里插入图片描述

如果设置为empty，表示不限制文件生成的位置，这是不安全的设置。
如果这种伪一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录。
如果设置为NULL，就表示禁止在这个MySQL实例上执行select…into outfile操作。

根据上面结果中显示，secure_file_priv变量的值为/var/lib/mysql-files/，导出目录设置为该目录，SQL语句如下：

SELECT * FROM account INFO OUTFILE "/var/lib/mysql-files/account.txt";

6.1.2 使用mysqldump导出文本文件

使用mysqldump命令将数据库中的表中记录导出到文本文件：

mysqldump -uroot -p -T "/var/lib/mysql-files" seapp account

mysqldump命令执行完毕后，在指定的目录/var/lib/mysql-files/下生成了account.sql和account.txt文件。

6.1.3 使用mysql命令导出文本文件

mysql -uroot -p --execute="SELECT * FROM account;" seapp > "/var/lib/mysql-files/account.txt"

6.2 表的导入

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

LOAD DATA INFILE '/var/lib/mysql-files/account.txt' INTO TABLE seapp.account;

6.2.2 使用mysqlimport方式导入文本文件

#导出文件，字段之间使用逗号“，”间隔，字段值用双引号括起来SELECT * FROM seapp.account INTO OUTFILE '/var/lib/mysql-files/account.txt' FIELDS TERMINATED BY ',' ENCLOSED by '\"';#使用mysqlimport命令导入mysqlimport -uroot -p seapp '/var/lib/mysql-files/account.txt' --fields-terminated-by=',' --fields-optionlly-enclosed-by='\"'

七、数据库迁移

7.1 概述

数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外，验证数据完整性和退役原来旧的数据存储，也被认为是整个数据迁移过程的一部分。
数据库迁移的原因是多样的，包括服务器或存储设备更换、维护或升级，应用程序迁移、网站集成、灾难恢复和数据中心迁移。
根据不同的需求可能要采用不同的迁移方案，但总体来讲，MySQL数据迁移方案大致可以分为物理迁移和逻辑迁移两类。通常以尽可能自动化的方式执行，从而将人力资源从繁琐的任务中解放出来。

7.2 迁移方案

物理迁移
逻辑迁移

7.3 迁移注意点

7.3.1 相同版本的数据库之间迁移注意点

指的是在主版本号相同的MySQL数据库之间进行数据库迁移
方式1： 因为迁移前后MySQL数据库的主版本号相同，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移只适用于MyISAM引擎的表。对于InnoDB表，不能用直接复制文件的方式备份数据库。
方式2： 最常见和最安全的方式是使用mysqldump命令导出数据，然后在目标数据库服务器中使用MySQL命令导入。

7.3.2 不同版本的数据库之间迁移注意点

旧版本与新版本的MySQL可能使用不同的默认字符集，如果数据库中有中文数据，那么迁移过程中需要对默认字符集进行修改，不然可能无法正常显示数据。
高版本的MySQL数据库通常都会兼容低版本，因此可以从低版本的MySQL数据库迁移至高版本的MySQL数据库。

7.3.3 不同数据库之间迁移注意点

不同数据库之间迁移是指从其他类型的数据库迁移到MySQL数据库，或者从MySQL数据库迁移到其他类型的数据库。这种迁移没有普适的解决方法。
迁移之前，需要了解不同数据库的架构，比价它们之间的差异。不同数据库中定义相同类型的数据的关键字可能会不同。
另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的SQL语句有差别。
不同类型数据库之间的差异造成了互相迁移困难，这些差异其实是商业公司故意造成的技术壁垒。但是不同类型的数据库之间的迁移并不是完全不可能。例如，可以使用MyODBC实现MySQL和SQL Server之间的迁移。MySQL官方提供的工具MySQL Migration Toolkit也可以在不同数据之间进行数据迁移。MySQL迁移到oracle时，需要使用mysqldump命令导出SQL文件，然后，手动更改sql文件中的CREATE语句。

7.4 迁移小结

在这里插入图片描述

八、删库误操作

传统的高可用架构是不能预防误删数据的，因为主库的一个drop table命令，会通过binlog传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。
为了找到解决误删数据的更高效的方法，我们需要先对和MySQL相关的误删数据，做下分类：

使用delete语句误删数据行。
使用drop table或者truncate table语句误删数据表。
使用drop database语句误删数据库。
使用rm命令行误删整个MySQL实例。

8.1 delete：误删行

处理措施1：恢复数据
使用Flashback工具恢复数据。
原理：修改binlog内容，拿回原库重放。如果误删数据涉及多个事务的话，需要将事务的顺序调过来再执行。
前提：binlog_fORMat和binlog_row_image=FULL。

处理措施2：预防

代码上线前，必须SQL审查、审计。
建议可以打开安全模式，把=sql_safe_updates参数设置为on。强制要求where条件且where后需要是索引字段，否则必须使用limit。否则就会报错。

8.2 truncate/drop：误删库/表

背景:
delete全表是很慢的，需要生成回滚日志、写redo、写binlog。所以，从性能角度考虑，优先考虑使用truncate table或drop table命令。
使用delete命令删除的数据，你还可以用Flashback来恢复。而使用truncate/drop table 和 drop database命令删除的数据，就没办法通过Flashback来恢复了。因为，即使我们配置了binlog_format=row，执行这三个命令时，记录的binlog还是statement格式。binlog里面就只有一个truncate/drop语句，这些信息是恢复不出数据的。

方案：
这种情况下恢复数据，需要使用全量备份与增量日志结合的方式。
方案的前提：有定期的全量备份，并且实时备份binlog。
举例：有人误删了一个库，时间为下午3点。步骤如下：

取最近一次全量备份。假设设置数据库是一天一备，最近备份数据时当天凌晨2点；
用备份恢复出一个临时库；（注意：这里选择临时库，而不是直接操作主库）
取出凌晨2点之后的binlog日志；
剔除误删除数据的语句外，其他语句全部应用到临时库。
最后恢复到主库。

8.3 预防使用truncate/drop误删库、表

在生产环境中可以通过下面建议的方案来尽量避免truncate/drop误删库、表。

（1）权限分离

限制账户权限，核心的数据库，一般都不能随便分配权限，想要获取写权限需要审批。比如只给业务开发人员DML权限，不给truncate/drop权限。即使使DBA团队成员，日常也都是规定只使用只读账号，必要的时候才使用又更新权限的账号。
不同的账号，不同的数据之间要进行权限分离，避免一个账号可以删除所有库。

（2）制定操作规范
比如在删除数据表之前，必须先对表做改名操作（比如加==_to_be_deleted==）。然后，观察一段时间，确保对业务无影响以后再删除这张表。

（3）设置延迟复制备库
简单的说延迟复制就是设置一个固定的延迟时间，比如1个小时，让从库落后主库一个小时。出现误删操作1小时内，到这个备库上执行 stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。这里通过CNANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有N秒的延迟。比如把N设置为3600，即代表1个小时。

此外，延迟复制还可以用来解决以下问题：