MySQL学习Day32——数据库备份与恢复

在任何数据库环境中，总会有不确定的意外情况发生，比如例外的停电、计算机系统中的各种软硬件故障、人为破坏、管理员误操作等是不可避免的，这些情况可能会导致数据的丢失、服务器瘫痪等严重的后果。存在多个服务器时，会出现主从服务器之间的数据同步问题。为了有效防止数据丢失，并将损失降到最低，应定期对MySQL数据库服务器做备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复，主从服务器之间的数据同步问题可以通过复制功能实现。

一、物理备份与逻辑备份:

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

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

二、mysqldump实现逻辑备份:

1.备份一个数据库:
mysqldump命令执行时可以将数据库备份成一个文本文件。该文件中实际上包含多个CREATE和INSERT语句,使用这些语句可以重新创建表和插入数据。
mysqldump -u 用户名称 -h 主机名称 -p 密码待备份的数据库名称[tbname,[tbname...]]> 备份文件名称.sql
2.备份全部数据库:
mysqldump -uroot -pxxxxxx --all -databases > all_database.sql
3.备份部分数据库:
mysqldump -u user -h host -p --databases [数据的名称1 [数据库的名称2...]]>备份文件名称.sql
4.备份部分表:
mysqldump -u user -h host -p 數据的名称 [表名1 [表名2...]] > 备份文件名称.sql
5.备份单表的部分数据
有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用--where选项。where后面附带需要满足的条件。
mysqldump -uroot -p atguigu student --where="id <18" > student_bak.sql
6.排除某些表的备份
如果我们想备份某个库，但是某些表数据量很大或者与业务关联不大，这个时候可以考虑排除掉这些表。选项--ignore-table可以完成这个功能
mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql
7.只备份结构或只备份数据
只备份结构的话可以使用--no-data;只备份数据可以使用--no-create-info:
mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql
mysqldump -uroot -p atguigu --no-create-info > atguigu_no_data_bak.sql
8.备份中包含存储过程、函数、事件:
mysqldump备份默认是不包含存储过程、自定义函数及事件的。可以使用--routines或-R选项来备份存储过程及函数，使用--events或-E参数来备份事件。

三、mysql命令恢复数据库:

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

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

四、表的导出和导入

1.表的导出

方式一:SELECT ... INTO OUTFILE;
方式二:musqldump -uroot -p -T 导出路径 数据库名 数据表名
方式三:mysql -uroot -p --execute="select语句" 数据库名>导出文件路径

2.表的导入:

方式一:LOAD DATA INFILE 文件路径 INTO TABLE 数据库名.表名
方式二:mysqlimport导入

五、数据库迁移:

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

2.迁移注意:

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

(2)不同版本的数据库之间迁移:旧版本与新版本的MySQL可能使用不同的默认字符集，例如有的旧版本中使用latin1作为默认字符集，而最新版本的MySQL默认字符集为utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对默认字符集进行修改，不然可能无法正常显示数据。高版本的MySQL数据库通常都会兼容低版本，因此可以从低版本的MySQL数据库迁移到高版本的MySQL数据库。