MySQL数据库迁移与备份实录

事情起因的概述

由于公司系统服务进行升级，需要进行开发测试环境分离。对应绩效基线产品服务需要单独部署，但是很不幸的是，当我使用备份的SQL文件进行对开发数据库数据恢复时候出现了MySQL数据库直接挂掉了。

通过查看日志，发现MySQL服务进行多次启动失败并且进入了 “start-limit” 状态，结合AI大模型提供的分析思路存在以下几种可能性：

经过分析之后，最有可能就是磁盘空间不足，导致4G左右SQL数据库挂载不上导致数据库宕机。

查看磁盘空间使用情况

df -h

通过查看磁盘空间的使用情况得知，需要进行修改MySQL的默认数据卷挂载位置，在本次的物理机资源中，默认的根路径资源已经全部使用完，需要将资源的挂载放在空间相对充裕的home路径之下。

为了进一步的明确宕机原因，查看MySQL日志信息进一步排查

本次MySQL的日志存放位置是在 /var/log 目录之下，进一步查看具体的信息

可以从下图中看出，由于挂载空间不足，已经无法支撑数据临时文件的写入导致最终MySQL服务的宕机。

如何针对磁盘空间不足进行挂载区域的修改以及数据的迁移与备份

分析与梳理

首先经过前面的分析，已经得出是由于MySQL使用的数据卷挂载默认是在根目录下，根目录的空间不足导致本次服务的宕机，现在需要修改将数据卷挂载迁移到空间相对充裕的home路径下，以及完成对数据库中开发库的数据备份。

如何修改MySQL数据卷的挂载位置

这里有一点需要注意就是直接可以进行对MySQL默认的数据卷迁移到别的位置，因此可以不用对数据执行mysqldump 进行备份，等迁移卷介绍完再说说如何使用mysqldump 进行备份。

停止MySQL服务

这一步操作其实如果你的MySQL以及宕机了可以省略，因为以及停止了。。。

systemctl stop mysqld

备份 MySQL 配置文件

mkdir -p /home/mysql-backup
cp /etc/my.cnf /home/mysql-backup/my.cnf.bak

迁移 MySQL 数据目录

创建新目录

在 /dev/mapper/centos-home 上创建一个新目录，专门用于存放 MySQL 数据。

mkdir -p /home/mysql-data

迁移数据

现有的 MySQL 数据目录（通常位于 /var/lib/mysql）迁移到新目录 /home/mysql-data

rsync -avz /var/lib/mysql/ /home/mysql-data/  #rsync 是一个可靠的文件传输工具，它会保留文件权限和时间戳，同时进行安全的复制。

修改权限

确保 MySQL 数据目录的权限和所有权正确：

chown -R mysql:mysql /home/mysql-data
chmod 750 /home/mysql-data

修改 MySQL 配置文件

修改 my.cnf 文件

编辑 MySQL 的配置文件 /etc/my.cnf,将 datadir 路径指向新目录 /home/mysql-data：

[mysqld]
datadir=/home/mysql-data
socket=/home/mysql-data/mysql.sock

更新 SELinux（如果启用）-------------这一步是可选的

如果系统启用了 SELinux，可能需要更新文件上下文。使用以下命令让 SELinux 允许 MySQL 在新位置运行：

semanage fcontext -a -t mysqld_db_t "/home/mysql-data(/.*)?"
restorecon -Rv /home/mysql-data

如果没有 semanage 命令，安装它：

yum install policycoreutils-python

重新启动 MySQL 服务

systemctl start mysqld

检查 MySQL 服务状态

systemctl status mysqld

验证迁移结果

使用命令,查看默认的数据库配置文件

grep socket /etc/my.cnf

mysql -u root -p --socket=/home/mysql-data/mysql.sock -e "SHOW VARIABLES LIKE 'datadir';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'datadir';"

除了可以使用上述命令查看配置位置，也可以看看新建的数据库是不是在当前目录下，可以看到存在

或者我们这里可以反推一下查看一下原先默认位置的数据卷信息是否有新建数据库信息，经过查看并没有新建的数据库信息

或者我们也可以查看一下如何现在卷的挂载情况，可以明显的看到home路径的磁盘使用率上去了，根路径的磁盘使用率下来了

删除旧的数据目录

rm -rf /var/lib/mysql

介绍一下使用mysqldump 进行备份

传统的备份方式

由于公司规章制度的限制，不能使用破解版navicate进行数据库迁移，因此本次使用mysqldump进行数据的迁移与备份

全量备份的命令展示

mysqldump -u root --all-databases > /home/databases.sql

这里有一个需要注意的点是如果数据库的配置文件my.cnf。位置做过个性化修改，因此再备份时候需要进行个性化适配。如果数据库没有进行个性化配置，如果你想备份某个库信息直接使用下述命令示例即可：

mysqldump -u root xxxxx > /home/xxxxx.sql

由于110数据库进行个性化配置，所以上述命令执行就会出现：

因此需要查看一下my.cnf配置文件的位置：

grep socket /etc/my.cnf

使用下述命令进行备份SQL文件的生成：

mysqldump -u root -p --socket=/data/mysql/mysql.sock xxxx > /home/xxxx.sql# 这里需要注意一下的就是information_schema和performance_schema虽然是系统自带的只读库在四个自带库的其中两个，使用下面的命令备份得不到完成的库信息，只能使用别的命令进行备份得到结构信息，所以只需要备份自带的mysql、sys 这两个库信息就可以了
mysqldump -u root -p information_schema > /home/mysql-backup/information_schema.sqlmysqldump -u root -p mysql > /home/mysql-backup/mysql.sqlmysqldump -u root -p performance_schema > /home/mysql-backup/performance_schema.sqlmysqldump -u root -p sys > /home/mysql-backup/sys.sql

从下面的文件大小也能看出来information_schema和performance_schema 这两个是备份失败的

使用这个命令之后会提示你输入数据库对应的密码（输入完密码之后会进行备份文件的生成，生成完成之后会得到下述图片中的脚本文件信息）：

使用DataGrip使用脚本文件进行数据的恢复

选择目标库之后使用对应下载好的SQL脚本进行数据的备份：

可以在控制台中看到具体的备份进度和详细的日志信息

等待备份文件执行完成就可以得到对应的备份开发数据库。

但是使用上述方式生成的数据库备份信息不存在数据库的函数相关信息