用ibd文件恢复mysql数据库数据

问题:服务器上mysql数据库损坏

报错:

查看.err文件:

[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

发现mysql.user文件的确丢失了,,,,

现在的首要任务是恢复数据,尝试在无法启动服务的情况下恢复数据

 现状:本地保存有很久之前的备份文件,也就是说待恢复的数据库的结构还在,需要恢复数据

方法一,数据文件和库表结构文件直接拷贝到新服务器,挂载到同样配置的MySQL服务下

  1. 保证Mysql版本一致,安装配置基本一致(注意:这里的数据文件和库表结构文件都指定在同一目录data下)
  2. 停止两边的Mysql服务(A服务器--迁移-->B服务器)
  3. 删除B服务器Mysql的data目录下所有文件
  4. 拷贝A服务器Mysql的data目录下除了ib_logfile.err之外的文件到B服务器data下
  5. 启动B服务器的Mysql服务,检测是否发生异常

参考:https://blog.csdn.net/haiross/article/details/51282377?utm_source=blogxgwz2

报错,未果

方法二,用idb文件单独恢复每一张数据表的数据

具体方法:

1:删除表drop table tablename 
2:在建表语句 末尾加上 ROW_FORMAT=compact;
3:分离表空间ALTER TABLE table_name DISCARD TABLESPACE;
4:复制,覆盖对应的.ibd文件
5:导入表空间ALTER TABLE table_name IMPORT TABLESPACE;

参考:https://blog.csdn.net/wearegouest/article/details/79538618
           http://veryyoung.me/blog/2016/06/21/restore-innodb-data-physical.html

 

待恢复的数据库共有168张表,先对其中一张表测试

1:Navicat数据库表提取MySQL建表语句:选中需要提取结构的表,右击 –>选择对象信息 –> 从弹出的窗口里右下角选择DDL即可
2:删除表后,执行上述创建表sql,末尾加上 ROW_FORMAT=compact;
3:分离表空间ALTER TABLE table_name DISCARD TABLESPACE;
4:复制,覆盖对应的.ibd文件
5:导入表空间ALTER TABLE table_name IMPORT TABLESPACE;

完成后对比,成功!

一张表的数据恢复成功,但是有168张表,考虑用脚本批量恢复

1:获得所有表的建表sql(两种方式可以实现:
     1、mysql mysqldump只导出表结构或只导出数据的实现方法
             mysqldump --opt -d 数据库名 -u root -p > xxx.sql 
             发现mysqldump真强大https://www.jb51.net/article/28855.htm
     2、通过navicat  
          
然后,末尾加上 ROW_FORMAT=compact;(这一步是用手动加入的........)

2:获取该数据库的所有表名,因为后面要用表名遍历
 

"SELECT information_schema.`TABLES`.TABLE_NAME " \
                   "FROM information_schema.`TABLES` " \
                   "WHERE information_schema.`TABLES`.TABLE_SCHEMA = 'rpms'"

学习了一下information_schema 表https://www.cnblogs.com/hfdp/p/5549384.html

3:遍历,对每一个表恢复

完整代码如下:

import pymysql
import shutil

#
#
# 已经建好数据库,完成创建表
#
# 打开数据库连接
db = pymysql.connect("localhost", "root", "password", "dbname")
print("success: connected to db")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute()  方法执行 SQL 查询
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print("Database version : %s " % data)

sql_getTableList = "SELECT information_schema.`TABLES`.TABLE_NAME " \
                   "FROM information_schema.`TABLES` " \
                   "WHERE information_schema.`TABLES`.TABLE_SCHEMA = 'dbname'"
tableList = []
try:
    cursor.execute(sql_getTableList)
    result = cursor.fetchall()

    for tname in result:
        tableList.append(str(tname)[2:-3])
    # print(tableList)
    print("success: get table name list")
except:
    print("Error: unable to fetch data")

print(len(tableList))
# 对每一张表进行如下操作
# 分离表空间
# 覆盖数据文件.ibd
# 导入表空间
basedir = "D:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data\\basedir\\"
targetdir = "D:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data\\targetdir\\"
for tname in tableList:
    print("table name: " + tname)
    try:
        sql_DISCARD = "ALTER TABLE " + tname + " DISCARD TABLESPACE;"
        cursor.execute(sql_DISCARD)
        print(" " + sql_DISCARD)
    except:
        print("Error: DISCARD")

    try:
        shutil.copyfile(basedir + tname + ".ibd", targetdir + tname + ".ibd")
        print(" " + basedir + tname + ".ibd", targetdir + tname + ".ibd")
    except:
        print("Error: move")

    try:
        sql_IMPORT = "ALTER TABLE " + tname + " IMPORT TABLESPACE;"
        cursor.execute(sql_IMPORT)
        print(" " + sql_IMPORT)
    except:
        print("Error: IMPORT")



# 关闭数据库连接
db.close()

 

至此,全部恢复成功,下一步,核对检查恢复的文件。

最后一步,检查,将恢复出来的文件与之前的备份文件做对比

主要比较 表的数目,每一张表的内容,查看所有触发器(select * from information_schema.'TRIGGERS')

都没有问题,从navicat中看到原来备份数据库中有一个自定义函数,在新的数据库中没有,添加进去。

 

其他参考:

Python3 MySQL 数据库连接http://www.runoob.com/python3/python3-mysql.html

python 文件及文件夹操作https://www.cnblogs.com/feeland/p/4463682.html

 


 

©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值