32. 数据库备份与恢复

一、物理备份与逻辑备份

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

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

二、mysqldump实现逻辑备份

  • 备份一个数据库
    • mysqldump –u用户名称 –h主机名称 –p 待备份的数据库名称[tbname, [tbname...]] > 备份文件名称.sql
  • 备份全部数据库
    • mysqldump –u用户名称 –h主机名称 -p --all-databases > 备份文件名称.sql
    • mysqldump –u用户名称 –h主机名称 -p -A > 备份文件名称.sql
  • 备份部分数据库
    • mysqldump –u用户名称 –h主机名称 –p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql
    • mysqldump –u用户名称 –h主机名称 –p --B [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql
  • 备份部分表
    • mysqldump –u用户名称 –h主机名称 –p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql
  • 备份单表的部分数据
    • mysqldump –u用户名称 –h主机名称 –p 数据库的名称 --where="过滤条件" > 备份文件名称.sql
  • 排除某些表的备份
    • mysqldump –u用户名称 –h主机名称 –p 数据库的名称 --ignore-table=数据库名.表名 > 备份文件名称.sql
  • 只备份结构
    • mysqldump –u用户名称 –h主机名称 –p 数据库的名称 --no-data > 备份文件名称.sql
  • 只备份数据
    • mysqldump –u用户名称 –h主机名称 –p 数据库的名称 --no-create-info > 备份文件名称.sql
  • 备份中包含存储过程、函数
    • mysqldump –u用户名称 –h主机名称 -p --routines --databases 数据库的名称 > 备份文件名称.sql
    • mysqldump –u用户名称 –h主机名称 -p -R --databases 数据库的名称 > 备份文件名称.sql
  • 备份中包含事件
    • mysqldump –u用户名称 –h主机名称 -p --events --databases 数据库的名称 > 备份文件名称.sql
    • mysqldump –u用户名称 –h主机名称 -p -E --databases 数据库的名称 > 备份文件名称.sql

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

example：

# 备份一个数据库
mysqldump -uroot -p test32>test32.sql #备份文件存储在当前目录下
# 备份全部数据库
mysqldump -uroot -p --all-databases > all_database.sql 
mysqldump -uroot -p -A > all_database.sql
# 备份部分数据库
mysqldump -uroot -p --databases test32 template >two_database.sql
# 备份部分表
mysqldump -uroot -p test32 book> book.sql
mysqldump -uroot -p test32 book account > 2_tables_bak.sql
# 备份单表的部分数据
mysqldump -uroot -p test32 student --where="id < 10 " > student_part_id10_low_bak.sql
# 排除某些表的备份
mysqldump -uroot -p test32 --ignore-table=test32.student > no_stu_bak.sql
# 通过如下指定判定文件中没有student表结构：
grep "student" no_stu_bak.sql
# 只备份结构
mysqldump -uroot -p test32 --no-data > test32_no_data_bak.sql
# 只备份数据
mysqldump -uroot -p test32 --no-create-info > test32_no_create_info_bak.sql
# 备份中包含存储过程、函数、事件
mysqldump -uroot -p -R -E --databases test32 > fun_test32_bak.sql

mysqldump常用选项：

  • --add-drop-database：在每个CREATE DATABASE语句前添加DROP DATABASE语句。
  • --add-drop-tables：在每个CREATE TABLE语句前添加DROP TABLE语句。
  • --add-locking：用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。
  • --all-database, -A：转储所有数据库中的所有表。与使用--database选项相同，在命令行中命名所有数据库。
  • --comment[=0|1]：如果设置为0，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。--skip- comments与--comments=0的结果相同。默认值为1，即包括额外信息。
  • --compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skip- disable-keys和--skip-add-locking选项。
  • --compatible=name：产生与其他数据库系统或旧的MySQL服务器更兼容的输出，值可以为ansi、MySQL323、 MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_table_options或者 no_field_options。
  • --complete_insert, -c：使用包括列名的完整的INSERT语句。
  • --debug[=debug_options], -#[debug_options]：写调试日志。
  • --delete，-D：导入文本文件前清空表。
  • --default-character-set=charset：使用charsets默认字符集。如果没有指定，就使用utf8。
  • --delete--master-logs：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-master- data。
  • --extended-insert，-e：使用包括几个VALUES列表的多行INSERT语法。这样使得转储文件更小，重载文件时可 以加速插入。
  • --flush-logs，-F：开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。
  • --force，-f：在表转储过程中，即使出现SQL错误也继续。
  • --lock-all-tables，-x：对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关 闭--single-transaction和--lock-tables。
  • --lock-tables，-l：开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表（例 如InnoDB和BDB），--single-transaction是一个更好的选项，因为它根本不需要锁定表。
  • --no-create-db，-n：该选项禁用CREATE DATABASE /!32312 IF NOT EXIST/db_name语句，如果给出- -database或--all-database选项，就包含到输出中。
  • --no-create-info，-t：只导出数据，而不添加CREATE TABLE语句。
  • --no-data，-d：不写表的任何行信息，只转储表的结构。
  • --opt：该选项是速记，它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选项默认开启， 但可以用--skip-opt禁用。
  • --password[=password]，-p[password]：当连接服务器时使用的密码。
  • -port=port_num，-P port_num：用于连接的TCP/IP端口号。
  • --protocol={TCP|SOCKET|PIPE|MEMORY}：使用的连接协议。
  • --replace，-r –replace和--ignore：控制替换或复制唯一键值已有记录的输入记录的处理。如果指定--replace，新行替换有相同的唯一键值的已有行；如果指定--ignore，复制已有的唯一键值的输入行被跳过。如果不 指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。
  • --silent，-s：沉默模式。只有出现错误时才输出。
  • --socket=path，-S path：当连接localhost时使用的套接字文件（为默认主机）。
  • --user=user_name，-u user_name：当连接服务器时MySQL使用的用户名。
  • --verbose，-v：冗长模式，打印出程序操作的详细信息。
  • --xml，-X：产生XML输出。

运行帮助命令 mysqldump --help ，可以获得特定版本的完整选项列表。
如果运行mysqldump没有--quick或--opt选项，mysqldump在转储结果前将整个结果集装入内存。如果转储大数据库可能会出现问题，该选项默认启用，但可以用--skip-opt禁用。如果使用最新版本的mysqldump程序备份数据，并用于恢复到比较旧版本的MySQL服务器中，则不要使用--opt 或-e选项。

三、mysql命令恢复数据

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

mysql –u用户名称 -p [dbname] < backup.sql

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

  • 单库备份中恢复单库
    • 如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称
      • mysql –u用户名称 -p < 备份文件名称.sql
    • 如果备份文件中不包含了创建数据库的语句，则恢复的时候需要指定数据库名称
      • mysql –u用户名称 -p 数据库名 < 备份文件名称.sql
  • 全量备份恢复
    • mysql –u用户名称 –p < 备份文件名称.sql
  • 从全量备份中恢复单库
    1. sed -n '/^-- Current Database: 数据库名/,/^-- Current Database: `/p' 备份文件名称.sql > 新的备份文件名称.sql
    2. mysql –u用户名称 -p < 备份文件名称.sql
  • 从单库备份中恢复单表
    1. cat 备份文件名称.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE 表名/!d;q' > 新的备份文件名称1.sql
    2. cat 备份文件名称.sql | grep --ignore-case 'insert into 表名 > 新的备份文件名称2.sql
    3. 在mysql客户端执行：source 新的备份文件名称1.sql;
    4. 在mysql客户端执行：source 新的备份文件名称2.sql;

example：

# 单库备份中恢复单库
# 如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称
mysql -uroot -p < test32.sql
# 否则需要指定数据库名称
mysql -uroot -p test32< test32.sql
# 全量备份恢复
mysql –uroot –p < all.sql
# 从全量备份中恢复单库
sed -n '/^-- Current Database: `test32`/,/^-- Current Database: `/p' all_database.sql > test32.sql
mysql -uroot -p < test32.sql
# 从单库备份中恢复单表
cat test32.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sql
cat test32.sql | grep --ignore-case 'insert into `class`' > class_data.sql
# 在mysql客户端执行
source class_structure.sql;
source class_data.sql;

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

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

  • 在Windows平台下，MySQL 8.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服务器。查询相关表的数据是否恢复。需要使用下面的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默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作。在MySQL下执行：

-- 查询secure_file_priv值：
SHOW GLOBAL VARIABLES LIKE '%secure%';
SELECT … INTO OUTFILE 导出文本文件;

参数secure_file_priv的可选值和作用分别是：

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

example：

SHOW GLOBAL VARIABLES LIKE '%secure%';
SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";

6.1.2、使用mysqldump命令导出文本文件

??使用mysqldump命令将指定数据库中指定表中的记录导出到文本文件：mysqldump命令执行完毕后，在指定的目录下生成了导出文件.sql和导出文件.txt文件。打开导出文件.sql文件，其内容包含创建表的CREATE语句。在命令行下执行：

mysqldump -u用户名称 -p -T 导出文件名称 待导出的数据库 待导出的表

example：

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

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

??在命令行下执行：

mysql -u用户名称 -p --vertical --execute="SELECT * FROM 待导出的表;" 待导出的数据库 > 导出文件名称
mysql -u用户名称 -p --xml --execute="SELECT * FROM 待导出的表;" 待导出的数据库 > 导出文件名称.xml"

example：

mysql -uroot -p --vertical --execute="SELECT * FROM account;" test32 > "/var/lib/mysql-files/account_1.txt"
mysql -uroot -p --xml --execute="SELECT * FROM account;" test32>"/var/lib/mysql-files/account_3.xml"

6.2、表的导入

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

??在MySQL下执行LOAD DATA INFILE命令：

LOAD DATA INFILE 待导入的文件名 INTO TABLE 待导入的数据库.待导入的表;

exmple：

SELECT * FROM test32.account INTO OUTFILE '/var/lib/mysql-files/account_0.txt';
DELETE FROM atguigu.account;
LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE atguigu.account;

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

??在命令行下执行：

mysqlimport -u用户名称 -p 待导入的数据库 待导入的文件名

example：

# 在MySQL下执行
SELECT * FROM test32.account INTO OUTFILE '/var/lib/mysql-files/account.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '\"';
DELETE FROM atguigu.account;
# 在命令行下执行：
mysqlimport -uroot -p atguigu '/var/lib/mysql-files/account.txt' --fields-terminated- by=',' --fields-optionally-enclosed-by='\"'

七、数据库迁移

7.1、概述

??数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外，验证迁移数据的完整性和退役原来旧的数据存储，也被认为是整个数据迁移过程的一部分。根据不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL 数据迁移方案大致可以分为物理迁移和逻辑迁移两类。通常以尽可能自动化的方式执行，从而将人力资源从繁琐的任务中解放出来。

7.2、迁移方案

  • 物理迁移
    • 物理迁移适用于大数据量下的整体迁移。使用物理迁移方案的优点是比较快速，但需要停机迁移并且要求 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题。
    • 物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种。
    • 不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装好同版本的数据库软件，创建好相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置好文件组权限，之后在新服务器这边使用 mysqld 命令启动数据库。
  • 逻辑迁移
    • 逻辑迁移适用范围更广，无论是部分迁移还是全量迁移，都可以使用逻辑迁移。逻辑迁移中使用最多的就是通过 mysqldump 等备份工具。

7.3、迁移注意点

  • 相同版本的数据库之间迁移注意点：指的是在主版本号相同的MySQL数据库之间进行数据库移动。
    • 因为迁移前后MySQL数据库的主版本号相同，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于MyISAM引擎的表。对于InnoDB表，不能用直接复制文件的方式备份数据库。
    • 最常见和最安全的方式是使用 mysqldump命令 导出数据，然后在目标数据库服务器中使用MySQL命令导入。
  • 不同版本的数据库之间迁移注意点
    • 旧版本与新版本的MySQL可能使用不同的默认字符集，例如有的旧版本中使用latin1作为默认字符集，而最新版本的MySQL默认字符集为utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对 默认字符集 进行修改 ，不然可能无法正常显示数据。
    • 高版本的MySQL数据库通常都会 兼容低版本 ，因此可以从低版本的MySQL数据库迁移到高版本的MySQL数据库。
  • 不同数据库之间迁移注意点
    • 不同数据库之间迁移是指从其他类型的数据库迁移到MySQL数据库，或者从MySQL数据库迁移到其他类型的数据库。这种迁移没有普适的解决方法。
    • 迁移之前，需要了解不同数据库的架构，比较它们之间的差异。不同数据库中定义相同类型的数据的关键字可能会不同。例如，MySQL中日期字段分为DATE和TIME两种，而ORACLE日期字段只有DATE；SQL Server数据库中有ntext、Image等数据类型，MySQL数据库没有这些数据类型；MySQL支持的ENUM和SET类型，这些SQL Server数据库不支持。
    • 另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的SQL语句有差别。例如，微软的SQL Server软件使用的是T-SQL语句，T-SQL中包含了非标准的SQL语句，不能和MySQL 的SQL语句兼容。
    • 不同类型数据库之间的差异造成了互相 迁移的困难 ，这些差异其实是商业公司故意造成的技术壁垒。但是不同类型的数据库之间的迁移并不是完全不可能。例如，可以使用MyODBC实现MySQL和SQL Server之间的迁移。MySQL官方提供的工具MySQL Migration Toolkit也可以在不同数据之间进行数据迁移。MySQL迁移到Oracle时，需要使用mysqldump命令导出sql文件，然后，手动更改sql文件中的CREATE语句。

八、删库了该怎么办

??传统的高可用架构是不能预防误删数据的。因为主库的一个drop table命令，会通过binlog传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令：

8.1、delete：误删行

  • 数据恢复
    • 使用Flashback工具来恢复数据
    • 原理：修改binlog内容，拿回原库重放。如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来在执行
    • 使用前提：binlog_format=row 和 binlog_row_image=FULL
  • 预防
    • 代码上线前，必须SQL审查、审计
    • 建议可以打开安全模式，把sql_safe_updates参数设置为on。可以加where条件且where后面需要是索引字段，否则可以使用limit，否则就会报错。

8.2、truncate/drop ：误删库/表

??这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份binlog。

  1. 取最近一次全量备份
  2. 用备份恢复出一个临时库
  3. 从日志备份里面，取出最近一次全量备份之后的日志
  4. 剔除误删除数据的语句外，其它语句全部应用到临时库（binlog的恢复）
  5. 最后恢复主库

8.3、延迟复制备库

??如果有非常核心的业务，不允许太长的恢复时间，可以考虑搭建延迟复制的备库。一般的主备复制结构存在的问题是，如果主库上有个表被误删了，这个命令很快也会被发给所有从库，进而导致所有从库的数据表也都一起被误删了。延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N秒的延迟 。比如你把N设置为3600，这就代表了如果主库上有数据被误删了，并且在1小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

8.4、预防误删库/表的方法

  • 权限分离
    • 限制账户权限，核心的数据路，一般都不能随便分配写权限，想要获取写权限需要审批。比如只给业务来发人员DML权限，不给truncate/drop权限。即使是DBA团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。
    • 不同的账号，不同的数据之间要进行权限分离，避免一个账号可以删除所有的库
  • 指定操作规范
    • 比如在删除数据表之前，可以先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。
  • 设置延迟复制备库
    • 简单的说延迟复制就是设置一个固定的言辞时间，比如一个小时，让从库落后主库一个小时。出现误删除操作1小时内，到这个备库执行stop slave，再通过之前的方法，跳过误操作命令，就可以恢复出需要的数据。这里通过CHANGE MASTER TO MASTER_DELAY = N的命令，可以指定这个备库持续保持跟主库有N秒的延迟。

8.5、误删MySQL实例

??对于一个有高可用机制的MySQL集群来说，不用担心rm删除数据了。只是删掉了其中某一个节点的数据的话，HA系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。我们要做的就是在这个节点上把数据恢复回来，再接入整个集群。但如果是恶意地把整个集群删除，那就需要考虑跨机房备份，跨城市备份。

02.SQL与JDBC数据库备份与恢复

相关

标签