MySQL数据库迁移与MySQL数据库批量恢复

缺乏、安全感 2023-10-04 12:12 3阅读 0赞

### 一、MySQL数据库迁移或备份 ###

将相关数据库数据文件直接迁移方式

###### 1. 了解使用InnoDB引擎创建数据库所产生的文件 ######

使用MySQL InnoDB引擎所产生的文件(三个文件都很重要)

*  .ibd：包含每个table的数据和索引
 *  .frm：描述table的结构等
 *  ibdata1：包含所有table的字典和历史操作
 *

在data文件夹下，每个数据库分别是一个文件夹（如：mosquitto），ibdata1文件会记录MySQL中所有InnoDB数据库中table的字典和历史操作：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXNlZXlvdWE_size_16_color_FFFFFF_t_70]

在mosquitto文件夹总，包含每个table的.ibd文件和.frm文件  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXNlZXlvdWE_size_16_color_FFFFFF_t_70 1]

###### 2. 迁移数据库步骤 ######

**基本要求：**

*  保证MySQL版本基本一致，安装配置基本一致
 *  停止两边的MySQL服务

**1.从A服务器迁移至B服务器**

*  清空B服务器MySQL的data目录下所有文件
 *  拷贝A服务器MySQL的data目录下除了ib\_logfile和.err之外的文件到B服务器data下
 *  启动B服务器的MySQL服务，检测是否发生异常

**2.MySQL重装并导入之前数据库**

*  备份MySQL的data目录下除了ib\_logfile和.err之外的所有文件到其他目录
 *  清空MySQL的data目录下所有文件
 *  拷贝备份的数据文件到data目录下
 *  启动MySQL服务，检测是否发生异常

注意：

迁移时将每个数据库文件夹时一定不能忘记ibdata1文件，若不小心删除ibdata1文件可以继续看以下内容恢复。

### 二、MySQL数据库批量恢复（通过.frm和.ibd文件批量恢复） ###

若不小心删除ibdata1文件，即目前只有.frm和.ibd文件如何恢复数据？同时数据库表格过多又如何批量恢复？

**1. 通过.frm文件批量恢复表结构信息（使用mysqlfrm）**  
mysqlfrm其他版本（windows等）下载地址：https://downloads.mysql.com/archives/utilities/

**（1）mysqlfrm安装**

下载

wget https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5.tar.gz

解压

tar -xvf mysql-utilities-1.6.5.tar.gz
    cd mysql-utilities-1.6.5/

安装(需要Python环境，python2.6及以上)

python ./setup.py build
    python ./setup.py install

检查是否安装成功

mysqlfrm --version

**（2）使用mysqlfrm查看表结构创建语句**

mysqlfrm有–basedir模式以及–server模式，但推荐–server，能够还原最为准确的信息

*  使用方式（启动MySQL服务）  
    格式：mysqlfrm --server=用户名:密码@数据库地址:端口 需恢复的frm文件目录 --diagnostic > 保存SQL文件名.sql

mysqlfrm --server=root:123456@192.168.11.130:3306 /mnt/mysql/mysql\_data/mosquitto/\*.frm --diagnostic > mosquitto\_frm.sql  
过程如图所示：  
![在这里插入图片描述][20210429160816555.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXNlZXlvdWE_size_16_color_FFFFFF_t_70 2]

*  执行所生成的SQL文件

注：mysqlfrm仅显示整个数据库的表结构创建语句，可以显示在控制台也可以输出到文件，不过仍需要手动执行这些表创建SQL语句。

先创建数据库

CREATE DATABASE `mosquitto`

可以查看生成的sql文件手动删除相关无用信息等，可命令行执行或者客户端执行等

命令行

mysql -uroot -p123456 mosquitto < mosquitto_frm.sql

1.  通过.ibd文件批量恢复表数据信息  
    （1）让表结构和表空间脱离

*注：  
若使用不了mysql命令或者执行mysql出现Can’t connect to local MySQL server through socket '/tmp/mysql.sock’等问题时，请查看这一篇文章解决[https://www.cnblogs.com/maogen/p/14353778.html][https_www.cnblogs.com_maogen_p_14353778.html]*

生成

mysql -uroot -p123456 -e " SELECT concat('alter table ', table_name, ' discard tablespace;') 
    FROM information_schema.tables WHERE table_schema = 'mosquitto';" > tem_discard.sql

删除多余信息

sed '/^c/d' tem_discard.sql > discard.sql

执行命令

mysql -uroot -p123456 mosquitto < discard.sql

**（2）将需要恢复的.ibd替换到相关目录下**

cp /mnt/mysql/mosquitto/*.ibd /mnt/mysql/mysql_data/mosquitto/

**（3）权限设置**

chown -R mysql.mysql /mnt/mysql/*

**（4）导入表空间**  
生成

mysql -uroot -p123456 -e " SELECT concat('alter table ', table_name, ' import tablespace;') 
    FROM information_schema.tables WHERE table_schema = 'mosquitto1';" > tem_import.sql

删除多余信息

sed '/^c/d' tem_import.sql > import.sql

执行命令

mysql -uroot -p123456 mosquitto < import.sql

（5）查看是否成功

![在这里插入图片描述][20210429162625953.png]

参考  
[MySQL数据库迁移(数据文件直接迁移)-CSDN][MySQL_-CSDN]

[MySQL直接拷贝数据库文件后出现table xxx doesn’t exist解决方法-Penguin][MySQL_table xxx doesn_t exist_-Penguin]

[Mysql,innodb使用.ibd文件快速批量恢复数据—批量快速-CSDN][Mysql_innodb_.ibd_-CSDN]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXNlZXlvdWE_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210429155614160.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXNlZXlvdWE=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXNlZXlvdWE_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2021042915562637.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXNlZXlvdWE=,size_16,color_FFFFFF,t_70
[20210429160816555.png]: https://img-blog.csdnimg.cn/20210429160816555.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXNlZXlvdWE_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210429160830245.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXNlZXlvdWE=,size_16,color_FFFFFF,t_70
[https_www.cnblogs.com_maogen_p_14353778.html]: http://www.likecs.com/default/index/show?id=125319
[20210429162625953.png]: https://img-blog.csdnimg.cn/20210429162625953.png
[MySQL_-CSDN]: https://blog.csdn.net/haiross/article/details/51282377
[MySQL_table xxx doesn_t exist_-Penguin]: https://www.polarxiong.com/archives/MySQL%E7%9B%B4%E6%8E%A5%E6%8B%B7%E8%B4%9D%E6%95%B0%E6%8D%AE%E5%BA%93%E6%96%87%E4%BB%B6%E5%90%8E%E5%87%BA%E7%8E%B0table-xxx-doesn-t-exist%E8%A7%A3%E5%86%B3%E6%96%B9%E6%B3%95.html
[Mysql_innodb_.ibd_-CSDN]: https://blog.csdn.net/ywg_1994/article/details/102692017