【MySQL】我在广州学Mysql 系列—— 数据备份与还原

ℹ️大家好，我是练小杰，今天周一，过两天就是元宵节了，今年元宵节各位又要怎么过呢！！
本文主要对Mysql数据库中的数据备份与还原内容进行讨论！！
回顾：👉【MySQL用户管理实例】
数据库专栏：👉【数据库专栏】【Mysql练习题】
想了解更多，主页： 【练小杰的CSDN】

数据备份

在数据库运维中，备份是防止数据丢失和系统故障时快速恢复数据的重要手段。数据被誉为“21世纪的黄金”，在数字经济时代，数据已经成为一种核心生产要素。
数据的价值不仅在于其本身的数量和质量，更在于如何通过数据挖掘和分析来获取洞察，以驱动业务的全流程。

备份的类型

1. 物理备份

定义：直接复制数据库的物理文件和目录，通常是二进制文件，无法进行编辑。
优点：备份和恢复速度快，但只能在相同的MySQL版本和架构之间进行恢复。
适用场景：适用于大数据量的备份，如TB、PB级别。
工具：常见的物理备份方法包括使用文件系统级别的备份工具（如rsync、tar、cp、scp）以及使用MySQL Enterprise Backup等专用工具或系统快照方式。

2. 逻辑备份

定义：导出数据库的逻辑结构和数据，通常以SQL语句的形式保存。
优点：备份文件可以跨数据库管理系统进行恢复，具有良好的可移植性，适合数据迁移和跨平台备份。
适用场景：适用于数据量较小的备份，如MB、GB级别。
工具：常用的逻辑备份工具包括mysqldump和mysqlpump。

3. 完全备份

即备份整个数据库的所有数据，简单直接，适合需要定期备份的用户。

4. 增量备份

定义：只备份自上次备份以来发生变化的数据。
优点：备份速度快，占用存储空间少，适合频繁备份的场景。

5. 差异备份

定义：备份自上次完全备份以来发生变化的数据。
优点：恢复简单，适合在完全备份的基础上进行。

mysqldump命令备份

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

• 备份整个数据库

mysqldump -u [用户名] -p[密码] [数据库名] > [备份文件.sql]

• 备份单个表

mysqldump -u [用户名] -p[密码] [数据库名] [表名] > [备份文件.sql]

• 备份多个数据库

mysqldump -u [用户名] -p[密码] --databases [数据库1] [数据库2] > [备份文件.sql]

• 备份所有数据库

mysqldump -u [用户名] -p[密码] --all-databases > [备份文件.sql]

• 备份时忽略某些表

mysqldump -u [用户名] -p[密码] [数据库名] --ignore-table=[数据库名].[表名] > [备份文件.sql]

• 压缩备份文件

mysqldump -u [用户名] -p[密码] [数据库名] | gzip > [备份文件.sql.gz]

直接复制整个数据库目录

MySQL表保存为文件方式，因此可以直接复制mysql数据库的存储目录及文件进行备份。
但是，InnoDB存储引擎的表不适用。使用这种方法备份的数据最好还原到相同版本的服务器中，因为不同的版本可能不兼容。

• 在 Linux 系统中，直接复制整个数据库目录的步骤如下：

步骤1：停止 MySQL 服务

必须先停止MYSQL，确保数据文件不会被修改。

sudo systemctl stop mysql 
#或者
sudo service mysql stop

步骤 2：找到 MySQL 数据目录

MySQL 的数据目录通常位于以下路径：
Linux系统 : /var/lib/mysql/
Windows系统: C:\ProgramData\MySQL\MySQL Server X.X\Data\
我们还可以通过命令查看 MySQL 数据目录的位置。

SHOW VARIABLES LIKE 'datadir';

步骤 3：复制数据库目录

找到数据目录后，复制整个数据库目录到目标位置。

比如，假设我们的数据库名为 mydatabase，数据目录为 /var/lib/mysql/，用以下命令

sudo cp -r /var/lib/mysql/mydatabase /path/to/backup/

步骤 4：启动 MySQL 服务

sudo systemctl start mysql
##或者
sudo service mysql start

步骤 5：恢复数据库

如果需要恢复数据库，可以按照以下命令操作。

#停止 MySQL 服务
sudo systemctl stop mysql  #将备份的数据库目录复制回 MySQL 数据目录  
sudo cp -r /path/to/backup/mydatabase /var/lib/mysql/#增加文件权限，确保能执行 
sudo chown -R mysql:mysql /var/lib/mysql/mydatabase#启动 MySQL 服务
sudo systemctl start mysql

mysqlhotcopy工具快速备份

mysqlhotcopy是一个Perl脚本，它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。
⚠️注意：只可以备份MyISAM类型的表

mysqlpump工具备份

mysqlpump工具支持并行备份，可以提高备份效率。

• 示例：

mysqlpump -u root -p  --default-parallelism=2 --all-databases > all_databases_backup.sql

数据还原

数据还原（恢复）是数据库管理中至关重要的一个环节，旨在利用备份数据重新构建数据库，使其恢复到某个特定的状态，以应对数据丢失、损坏或其他故障。

使用mysql基础命令还原

对于已经备份的包含CREATE、INSERT语句的文本文件，可以使用mysql命令导入到数据库中。

mysql -u [用户名] -p[密码] [数据库名] < [备份文件.sql]

• 恢复压缩备份

gunzip < [备份文件.sql.gz] | mysql -u [用户名] -p[密码] [数据库名]

• 验证恢复结果，利用SQL查询验证数据是否正确恢复。

SELECT * FROM [table_name];

直接复制到数据库目录实现还原

若数据库通过复制数据库文件备份，可以直接复制备份的文件到到MySQL数据目录下实现还原。
⚠️ 通过这种方式还原时，必须保存备份数据的数据库和待还原的数据库服务器的主版本号相同。而且这种方式只对MyISAM引擎的表有效。对于InnoDB引擎的表不可用。

• 实现还原步骤跟上面备份的步骤类似，主要区别在于步骤3

步骤3：复制备份文件到 MySQL 数据目录

比如，假设你备份的数据库目录是 mydatabase，MySQL 数据目录是 /var/lib/mysql/，可使用以下命令还原，将备份的数据库文件复制到 MySQL 数据目录下。

sudo cp -r /path/to/backup/mydatabase  /var/lib/mysql/

验证还原

#登录 MySQL
mysql -u root -p#检查数据库和数据表是否正常
SHOW DATABASES;
USE mydatabase;
SHOW TABLES;
SELECT * FROM your_table;

mysqlhotcopy快速恢复

mysqlhotcopy备份后的文件也可以用来恢复数据库，在MySQL服务器停止运行时，将备份的数据库文件拷贝到MySQL存放数据的位置（MySQL的Data文件夹中），重新启动MySQL服务即可。
⚠️ 若是以根用户执行该操作，必须指定数据库文件的所有者。

• 基本语法：

mysqlhotcopy [选项] 数据库名 备份目录

• 常用选项：

-u：指定 MySQL 用户名。
-p：指定 MySQL 密码。
--allowold：若备份目录已存在，覆盖它。
--keepold：若备份目录已存在，保留旧备份并创建一个新目录。
--addtodest：将备份添加到现有目录中，而不是覆盖。

安装 mysqlhotcopy

mysqlhotcopy 是 MySQL 客户端工具的一部分，通常随 MySQL 客户端一起安装。如果未安装，可以通过以下方式安装。

• Debian/Ubuntu 系统上

  sudo apt-get install mysql-client

• CentOS/RHEL 系统上

  sudo yum install mysql

数据库迁移

数据库迁移是一项常见但复杂的任务，涉及将数据从一个MySQL实例转移到另一个实例。这可以包括从一个服务器迁移到另一个服务器，或者从一个版本迁移到另一个版本。

相同版本的MySQL数据库之间的迁移

相同版本的MySQL数据库之间的迁移，即在主版本号相同的MySQL数据库之间进行数据库移动。
迁移的过程实质上就是在源数据库备份和目标数据库还原过程的组合。

• 实例：将www.lxj.com主机上的MySQL数据库全部迁移到www.lian.com主机上。那么，在www.lxj.com 主机上执行的命令：

mysqldump –h www.lian.com –uroot –ppassword dbname 
//或者
mysql –h  www.lian.com –uroot –ppassword

不同版本的MySQL数据库之间的迁移

因为数据库升级等原因，需要将较旧版本MySQL数据库中的数据迁移到的较新版本的数据库中。

迁移前的准备

• 首先需要评估版本兼容性：

主要版本升级：从MySQL 5.7迁移到MySQL 8.0，需要特别注意新版本引入的更改和弃用的功能。
次要版本升级： 从MySQL 8.0.21迁移到MySQL 8.0.26，通常较为简单，但仍需检查发布说明以了解潜在的问题。

• 备份数据：使用mysqldump进行逻辑备份

mysqldump -u [源用户名] -p[密码] --databases [数据库名] --routines --triggers --events > backup.sql

• 检查和调整字符集和排序规则

SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation%';

• 测试备份文件的恢复

测试环境中恢复备份文件，确保备份文件的完整性和可恢复性。

mysql -u [测试用户名] -p[密码] < backup.sql

迁移步骤

主要使用mysqldump和mysql命令

1. 在源服务器上导出数据库

mysqldump -u [源用户名] -p[密码] --databases [数据库名] --routines --triggers --events > backup.sql

2. 传输备份文件到目标服务器

使用scp命令(适用于Linux/Mac系统) 或者使用其他文件传输工具，如rsync、ftp等。

 scp backup.sql [用户名]@[目标服务器IP]:/path/to/backup/

3. 在目标服务器上安装目标版本的MySQL

根据目标服务器的操作系统，安装相应版本的MySQL。
确保目标MySQL版本与备份文件兼容。

4. 在目标服务器上创建目标数据库（如果没创建）

CREATE DATABASE [数据库名];

5. 在目标服务器上导入数据库

mysql -u [目标用户名] -p[密码] [数据库名] < /path/to/backup/backup.sql

6. 升级数据字典（可选）

从MySQL 5.7迁移到MySQL 8.0，可能需要运行mysql_upgrade命令来升级数据字典和系统表。

mysql_upgrade -u [目标用户名] -p[密码]

⚠️注意：mysql_upgrade在MySQL 8.0中已被弃用，升级过程由服务器自动处理。

7. 重启MySQL服务：

sudo systemctl restart mysql

不同数据库之间迁移

不同类型的数据库之间的迁移，是指从把MySQL的数据库转移到其他类型的数据库，比如从MySQL迁移到ORACLE，从ORACLE迁移到MySQL和从MySQL迁移到sqlserver等。

实例：从MySQL迁移到ORACLE

迁移之前做的准备跟上述一样，观察版本兼容性，同时做好备份准备，避免迁移出错，下面使用Oracle SQL Developer 工具进行迁移。

1. 下载和安装Oracle SQL Developer

   • Oracle SQL Developer是一个免费的图形化工具，支持从MySQL迁移到Oracle。

2. 配置MySQL JDBC驱动

   • 下载MySQL JDBC驱动（mysql-connector-java.jar）。
   • 在SQL Developer中，导航到Tools > Preferences > Database > Third Party JDBC Drivers，添加下载的JDBC驱动。

3. 连接到MySQL数据库

   • 打开SQL Developer，导航到Connections，点击+号创建新连接。
   • 选择MySQL作为数据库类型，输入连接信息（主机、端口、用户名、密码）。

4. 连接到Oracle数据库

   同样在Connections中，创建到Oracle数据库的新连接。

5. 迁移数据

   • 右键点击要迁移的MySQL连接，选择Migration Wizard。
   • 按照向导步骤选择要迁移的数据库对象（表、视图、存储过程等）。
   • 配置目标Oracle数据库的连接和目标模式。
   • 启动迁移过程，SQL Developer会自动处理数据类型转换和对象创建。

6. 验证迁移结果：

   • 检查Oracle数据库中的表、视图、存储过程等是否正确创建。

   • 比较MySQL和Oracle数据库中的数据行数：

     SELECT COUNT(*) FROM [表名];
     

数据表的导出和导入

SELECT…INTO OUTFILE导出文本文件

MySQL数据库导出数据时，允许使用包含导出定义的SELECT语句进行数据的导出操作。该文件被创建到服务器主机上。

SELECT columnlist  FROM table WHERE condition INTO OUTFILE  'filename' [OPTIONS]

mysqldump命令导出文本文件

mysqldump工具不仅可以将数据导出为包含CREATE、INSERT的sql文件，也可以导出为纯文本文件。

mysqldump -T path-u root -p dbname [tables] [OPTIONS]

mysql基础命令导出文本文件

同时，mysql 也是一个功能丰富的工具命令，还可以在命令行模式下执行SQL指令将查询结果导入到文本文件中。

mysql -u root -p --execute = "SELECT 语句"  dbname > filename.txt

LOAD DATA INFILE方式导入文本文件

LOAD DATA INFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为一个文字字符串。

LOAD DATA  INFILE 'filename.txt' INTO TABLE tablename [OPTIONS] 	[IGNORE number LINES]

mysqlimport命令导入文本文件

使用mysqlimport不需要登录MySQL客户端，但是mysqlimport语句需要指定所需的选项、导入的数据库名称以及导入的数据文件的路径和名称。

mysqlimport –u root –p dbname filename.txt [OPTIONS]

综合案例—数据的备份与恢复

通过以下练习，能够熟悉MYSQL基本的数据备份和还原命令！！

mysqldump命令备份

使用mysqldump命令将suppliers表备份到文件C:\bktestdir\suppliers_bk.sql

mysqldump -u root -p test suppliers > C:\bktestdir\suppliers_bk.sql

mysql命令还原数据

使用mysql命令还原suppliers表到test数据库中

DELETE FROM suppliers;
source C:/bktestdir/suppliers_bk.sql;

SELECT… INTO OUTFILE语句导出数据

使用SELECT… INTO OUTFILE语句导出suppliers表中的记录，导出文件位于目录C:\bktestdir下，名称为suppliers_out.txt

SELECT * FROM test.suppliers INTO OUTFILE "C:/bktestdir/suppliers_out.txt"FIELDSTERMINATED BY ','ENCLOSED BY '\"'LINESSTARTING BY '<'TERMINATED BY '>\r\n';

LOAD DATA INFILE语句导入

使用LOAD DATA INFILE语句导入suppliers_out.txt数据到suppliers表。

LOAD DATA INFILE 'C:/bktestdir/suppliers_out.txt' INTO TABLE test.suppliersFIELDSTERMINATED BY ','ENCLOSED BY '\"'LINESSTARTING BY '<'TERMINATED BY '>\r\n';

musqldump命令导出

使用musqldump命令将suppliers表中的记录导出到文件C:\bktestdir\suppliers_html.html

mysql -u root -p --html --execute="SELECT * FROM suppliers;" test > C:/bktestdir/suppliers_html.html

本文有关Mysql数据备份与还原的相关概念已讲完了，明天再见啦👋
主页：【练小杰的CSDN】😆
ℹ️欢迎各位在评论区踊跃讨论，积极提出问题，解决困惑！！！
⚠️若博客里的内容有问题，欢迎指正，我会及时修改！！