MySQL一次惊心动魄地数据强制恢复

最新推荐文章于 2024-10-07 16:13:47 发布
最新推荐文章于 2024-10-07 16:13:47 发布
阅读量466 收藏
点赞数
文章标签： 数据库 python awk
原文链接：https://yq.aliyun.com/articles/638452
版权

摘要：Crash恢复的正确方式是：备份文件（逻辑或物理）+ binlog进行恢复；然而并不是所有的运维人员都知道怎么进行正确的备份，甚至连逻辑备份和物理备份的区别是什么都不知道？更不知道备份过程中需要考虑数据的一致性与服务可用性的问题？或者连备份工具都不会使用，所以当你问：有备份吗？回答：没有或者无效

本次分析案例我们将以如下背景情况进行开展：

  1. 数据库因存储空间不够导致数据库服务宕掉，而在数据库宕机后，只拷贝了单个cy库所属的目录进行了文件层面的备份，就将其他文件全部清空，当想重新启动数据库时发现数据库服务启动。
  2. MySQL使用5.7.17版本，使用innodb存储引擎，开启了独立表空间。

也就是说当前的救命稻草是：每个表的.frm和.ibd文件。

强制还原步骤

详细过程

数据量


恢复结果

在客户服务器上面结果如下：

在我的服务器上面结果如下：

[root@toberoot mysql]# /alidata/mysql/bin/mysqld --initialize-insecure --datadir=/alidata/mysql/data/  --user=mysql
[root@toberoot mysql]# service mysqld start
Starting MySQL.                                            [  OK  ]
[root@toberoot mysql]# mysql -V
mysql  Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86_64) using  EditLine wrapper

[root@toberoot mysql]# cd ~/home/cy02/
[root@toberoot cy02]# ll
total 178140
-rw-r--r-- 1 mysql mysql     9022 Aug 14  2017 base_dict.frm
-rw-r--r-- 1 mysql mysql    98304 Mar 14 15:00 base_dict.ibd
-rw-r--r-- 1 mysql mysql     8822 Mar  2 11:14 biz_advise.frm
-rw-r--r-- 1 mysql mysql    98304 May 14 14:39 biz_advise.ibd
-rw-r--r-- 1 mysql mysql     8850 Mar  2 11:14 biz_bank.frm
-rw-r--r-- 1 mysql mysql    98304 Mar  2 11:14 biz_bank.ibd
-rw-r--r-- 1 mysql mysql     9580 Mar  2 11:14 biz.frm
-rw-r--r-- 1 mysql mysql     9286 Mar  2 11:14 biz_gift.frm
-rw-r--r-- 1 mysql mysql    98304 Apr 24 14:08 biz_gift.ibd
-rw-r--r-- 1 mysql mysql     8890 Mar  2 11:14 biz_gprs_bind.frm
-rw-r--r-- 1 mysql mysql     8745 Mar  2 11:14 biz_gprs_bind_his.frm
-rw-r--r-- 1 mysql mysql   180224 May 15 09:40 biz_gprs_bind_his.ibd
-rw-r--r-- 1 mysql mysql   180224 May 15 09:42 biz_gprs_bind.ibd
-rw-r--r-- 1 mysql mysql    98304 May 15 14:33 biz.ibd
-rw-r--r-- 1 mysql mysql     8845 Aug 14  2017 biz_msg_template.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 biz_msg_template.ibd
-rw-r--r-- 1 mysql mysql     8881 Aug 14  2017 biz_take_bank.frm
-rw-r--r-- 1 mysql mysql    98304 May 13 19:26 biz_take_bank.ibd
-rw-r--r-- 1 mysql mysql     9411 Nov 24 13:22 biz_take.frm
-rw-r--r-- 1 mysql mysql   196608 May 15 15:22 biz_take.ibd
-rw-r--r-- 1 mysql mysql     8862 Aug 14  2017 biz_take_wwlt.frm
-rw-r--r-- 1 mysql mysql    98304 May 15 15:22 biz_take_wwlt.ibd
-rw-r--r-- 1 mysql mysql     8854 Aug 14  2017 biz_take_wx.frm
-rw-r--r-- 1 mysql mysql    98304 May 15 11:52 biz_take_wx.ibd
-rw-r--r-- 1 mysql mysql     8925 Aug 14  2017 biz_vip.frm
-rw-r--r-- 1 mysql mysql    98304 Mar 16 09:31 biz_vip.ibd
-rw-r--r-- 1 mysql mysql     8852 Aug 14  2017 biz_wlt.frm
-rw-r--r-- 1 mysql mysql    98304 May 15 18:30 biz_wlt.ibd
-rw-r--r-- 1 mysql mysql     8926 Aug 14  2017 biz_wx_focus.frm
-rw-r--r-- 1 mysql mysql    98304 May 10 16:24 biz_wx_focus.ibd
-rw-r--r-- 1 mysql mysql     9339 Nov 24 13:25 biz_wx.frm
-rw-r--r-- 1 mysql mysql    98304 May 10 16:24 biz_wx.ibd
-rw-r--r-- 1 mysql mysql     8874 Aug 14  2017 biz_wx_walt.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 biz_wx_walt.ibd
-rw-r--r-- 1 mysql mysql     8776 Aug 14  2017 cfg_area.frm
-rw-r--r-- 1 mysql mysql   311296 Aug 14  2017 cfg_area.ibd
-rw-r--r-- 1 mysql mysql     8721 Aug 14  2017 cfg_id_gen.frm
-rw-r--r-- 1 mysql mysql    98304 May 15 18:41 cfg_id_gen.ibd
-rw-r--r-- 1 mysql mysql       61 Aug 14  2017 db.opt
-rw-r--r-- 1 mysql mysql     9053 Aug 14  2017 gprs_model.frm
-rw-r--r-- 1 mysql mysql   212992 May 15 18:47 gprs_model.ibd
-r--r--r-- 1 mysql mysql 79691776 May 16 14:04 ibdata1
-rw-r--r-- 1 mysql mysql     8801 Dec 19 15:00 mbr_coin_chged.frm
-rw-r--r-- 1 mysql mysql  2097152 May 15 17:27 mbr_coin_chged.ibd
-rw-r--r-- 1 mysql mysql     8766 Dec 19 14:53 mbr_coin.frm
-rw-r--r-- 1 mysql mysql    98304 May 15 19:12 mbr_coin.ibd
-rw-r--r-- 1 mysql mysql     9155 May 14 11:55 mbr.frm
-rw-r--r-- 1 mysql mysql   212992 May 15 20:58 mbr.ibd
-rw-r--r-- 1 mysql mysql     8876 Aug 14  2017 mbr_oauth.frm
-rw-r--r-- 1 mysql mysql   475136 May 15 19:37 mbr_oauth.ibd
-rw-r--r-- 1 mysql mysql     9011 Aug 14  2017 mbr_pay.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 mbr_pay.ibd
-rw-r--r-- 1 mysql mysql     8740 Aug 14  2017 mbr_prizen.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 mbr_prizen.ibd
-rw-r--r-- 1 mysql mysql     9147 Dec 19 14:56 mbr_recharge.frm
-rw-r--r-- 1 mysql mysql   294912 May 15 19:11 mbr_recharge.ibd
-rw-r--r-- 1 mysql mysql     8801 Aug 14  2017 mbr_wallet_chged.frm
-rw-r--r-- 1 mysql mysql  9437184 May 15 17:27 mbr_wallet_chged.ibd
-rw-r--r-- 1 mysql mysql     8845 Aug 14  2017 mbr_wallet.frm
-rw-r--r-- 1 mysql mysql   262144 May 15 19:12 mbr_wallet.ibd
-rw-r--r-- 1 mysql mysql    10100 Dec 19 15:47 ord.frm
-rw-r--r-- 1 mysql mysql 31457280 May 15 18:41 ord.ibd
-rw-r--r-- 1 mysql mysql     8940 Aug 14  2017 ord_item.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 ord_item.ibd
-rw-r--r-- 1 mysql mysql     8917 Aug 14  2017 ord_pay_ali.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 ord_pay_ali.ibd
-rw-r--r-- 1 mysql mysql     8924 Dec 19 15:55 ord_pay_coin.frm
-rw-r--r-- 1 mysql mysql   475136 May 15 17:27 ord_pay_coin.ibd
-rw-r--r-- 1 mysql mysql     8926 Aug 14  2017 ord_pay_return.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 ord_pay_return.ibd
-rw-r--r-- 1 mysql mysql     8966 Dec 20 16:50 ord_pay_wlt.frm
-rw-r--r-- 1 mysql mysql  9437184 May 15 17:27 ord_pay_wlt.ibd
-rw-r--r-- 1 mysql mysql     9036 Aug 14  2017 ord_pay_wx.frm
-rw-r--r-- 1 mysql mysql 32505856 May 15 18:41 ord_pay_wx.ibd
-rw-r--r-- 1 mysql mysql     9098 Aug 14  2017 prod_base_args.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 prod_base_args.ibd
-rw-r--r-- 1 mysql mysql     8790 Aug 14  2017 prod_bug_rpt.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 prod_bug_rpt.ibd
-rw-r--r-- 1 mysql mysql     8887 Aug 14  2017 prod_cmd.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 prod_cmd.ibd
-rw-r--r-- 1 mysql mysql     8984 Aug 14  2017 prod_cmd_invoke.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 prod_cmd_invoke.ibd
-rw-r--r-- 1 mysql mysql     9058 Aug 14  2017 prod_coin_rpt.frm
-rw-r--r-- 1 mysql mysql  9437184 May 15 14:24 prod_coin_rpt.ibd
-rw-r--r-- 1 mysql mysql     8798 Aug 14  2017 prod_coin_rpt_log.frm
-rw-r--r-- 1 mysql mysql    98304 May 15 14:24 prod_coin_rpt_log.ibd
-rw-r--r-- 1 mysql mysql     9834 Dec 13 14:12 prod.frm
-rw-r--r-- 1 mysql mysql     8835 Aug 14  2017 prod_gprs_bind.frm
-rw-r--r-- 1 mysql mysql     8793 Aug 14  2017 prod_gprs_bind_his.frm
-rw-r--r-- 1 mysql mysql   196608 May 15 09:42 prod_gprs_bind_his.ibd
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 prod_gprs_bind.ibd
-rw-r--r-- 1 mysql mysql   425984 May 15 09:42 prod.ibd
-rw-r--r-- 1 mysql mysql     8851 Aug 14  2017 prod_instl_imgs.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 prod_instl_imgs.ibd
-rw-r--r-- 1 mysql mysql     9388 Dec 13 14:13 prod_instl_pos.frm
-rw-r--r-- 1 mysql mysql   147456 May 15 16:25 prod_instl_pos.ibd
-rw-r--r-- 1 mysql mysql     9384 Dec 13 14:14 prod_instl_pos_model.frm
-rw-r--r-- 1 mysql mysql   131072 May 15 16:24 prod_instl_pos_model.ibd
-rw-r--r-- 1 mysql mysql     8892 Aug 14  2017 prod_mod_attr.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 prod_mod_attr.ibd
-rw-r--r-- 1 mysql mysql     8873 Aug 14  2017 prod_mod_attr_val.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 prod_mod_attr_val.ibd
-rw-r--r-- 1 mysql mysql     9642 Dec 13 14:11 prod_model.frm
-rw-r--r-- 1 mysql mysql    98304 May 11 11:58 prod_model.ibd
-rw-r--r-- 1 mysql mysql     8815 Aug 14  2017 prod_mod_sku.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 prod_mod_sku.ibd
-rw-r--r-- 1 mysql mysql     9050 Aug 14  2017 prod_onl_log.frm
-rw-r--r-- 1 mysql mysql   163840 May 15 18:42 prod_onl_log.ibd
-rw-r--r-- 1 mysql mysql     9143 Aug 14  2017 prod_sp_args.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 prod_sp_args.ibd
-rw-r--r-- 1 mysql mysql     8876 Aug 14  2017 prod_sp_arg_vals.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 prod_sp_arg_vals.ibd
-rw-r--r-- 1 mysql mysql     9190 Dec 13 14:14 sys_acct.frm
-rw-r--r-- 1 mysql mysql    98304 May 14 11:16 sys_acct.ibd
-rw-r--r-- 1 mysql mysql     8776 Aug 14  2017 sys_acct_res.frm
-rw-r--r-- 1 mysql mysql   229376 May 11 11:52 sys_acct_res.ibd
-rw-r--r-- 1 mysql mysql     9106 Aug 14  2017 sys_res.frm
-rw-r--r-- 1 mysql mysql    98304 Aug 14  2017 sys_res.ibd

获取待恢复表名
[root@toberoot cy02]# ll *.frm |awk '{print $9}'|awk -F '.' '{print $1}' > /alidata/cy_table.txt

python脚本自动生成建表语句
[root@toberoot alidata]# cat py_createtable01.py 
#-*- coding : utf8 -*-
def create_table_test(table_file,sql_file):
    a_file = open(sql_file,'w')
    b_file = open(table_file)
    b_list = b_file.readlines()
    for table in b_list:
        string = "create table {} (id int);".format(table)
        a_file.write(string)
    a_file.close() 
   

if __name__ == '__main__':
    create_table_test(alidata/cy_table.txt','/alidata/cy_sql1.sql')

[root@toberoot alidata]# python /alidata/py_createtable01.py 
[root@toberoot alidata]# head /alidata/cy_sql1.sql 
create table base_dict
 (id int);create table biz_advise
 (id int);create table biz_bank
 (id int);create table biz
 (id int);create table biz_gift
 (id int);create table biz_gprs_bind
 (id int);create table biz_gprs_bind_his
 (id int);create table biz_msg_template
 (id int);create table biz_take_bank
 (id int);create table biz_take
省略。。。

导入测试表结构
[root@toberoot alidata]# mysql -e "show databases"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@toberoot alidata]# mysql -e "create database cy;"
[root@toberoot alidata]# mysql cy < /alidata/cy_sql1.sql 
[root@toberoot alidata]# mysql -e "desc cy.sys_res"
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

开始获取表结构中列的信息

[root@toberoot alidata]# service mysqld stop
Shutting down MySQL..                                      [  OK  ]
[root@toberoot alidata]# yes|cp ~/home/cy02/*.frm /alidata/mysql/data/cy/
cp: overwrite ‘/alidata/mysql/data/cy/base_dict.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_advise.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_bank.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_gift.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_gprs_bind.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_gprs_bind_his.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_msg_template.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_take_bank.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_take.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_take_wwlt.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_take_wx.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_vip.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_wlt.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_wx_focus.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_wx.frm’? cp: overwrite ‘/alidata/mysql/data/cy/biz_wx_walt.frm’? cp: overwrite ‘/alidata/mysql/data/cy/cfg_area.frm’? cp: overwrite ‘/alidata/mysql/data/cy/cfg_id_gen.frm’? cp: overwrite ‘/alidata/mysql/data/cy/gprs_model.frm’? cp: overwrite ‘/alidata/mysql/data/cy/mbr_coin_chged.frm’? cp: overwrite ‘/alidata/mysql/data/cy/mbr_coin.frm’? cp: overwrite ‘/alidata/mysql/data/cy/mbr.frm’? cp: overwrite ‘/alidata/mysql/data/cy/mbr_oauth.frm’? cp: overwrite ‘/alidata/mysql/data/cy/mbr_pay.frm’? cp: overwrite ‘/alidata/mysql/data/cy/mbr_prizen.frm’? cp: overwrite ‘/alidata/mysql/data/cy/mbr_recharge.frm’? cp: overwrite ‘/alidata/mysql/data/cy/mbr_wallet_chged.frm’? cp: overwrite ‘/alidata/mysql/data/cy/mbr_wallet.frm’? cp: overwrite ‘/alidata/mysql/data/cy/ord.frm’? cp: overwrite ‘/alidata/mysql/data/cy/ord_item.frm’? cp: overwrite ‘/alidata/mysql/data/cy/ord_pay_ali.frm’? cp: overwrite ‘/alidata/mysql/data/cy/ord_pay_coin.frm’? cp: overwrite ‘/alidata/mysql/data/cy/ord_pay_return.frm’? cp: overwrite ‘/alidata/mysql/data/cy/ord_pay_wlt.frm’? cp: overwrite ‘/alidata/mysql/data/cy/ord_pay_wx.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_base_args.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_bug_rpt.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_cmd.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_cmd_invoke.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_coin_rpt.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_coin_rpt_log.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_gprs_bind.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_gprs_bind_his.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_instl_imgs.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_instl_pos.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_instl_pos_model.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_mod_attr.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_mod_attr_val.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_model.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_mod_sku.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_onl_log.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_sp_args.frm’? cp: overwrite ‘/alidata/mysql/data/cy/prod_sp_arg_vals.frm’? cp: overwrite ‘/alidata/mysql/data/cy/sys_acct.frm’? cp: overwrite ‘/alidata/mysql/data/cy/sys_acct_res.frm’? cp: overwrite ‘/alidata/mysql/data/cy/sys_res.frm’? [root@toberoot alidata]# 

[root@toberoot alidata]# cat py_createtable01.py 
#-*- coding : utf8 -*-

def create_table_test(table_file,sql_file):
    a_file = open(sql_file,'w')
    b_file = open(table_file)
    b_list = b_file.readlines()
    for table in b_list:
        string = "create table {} (id int);".format(table)
        a_file.write(string)
    a_file.close() 
   
def desc_table_test(table_file,sql_file):
    a_file = open(sql_file,'w')
    b_file = open(table_file)
    b_list = b_file.readlines()
    for table in b_list:
        string = "desc {};".format(table)
        a_file.write(string)
    a_file.close()

if __name__ == '__main__':
    #create_table_test('/alidata/cy_table.txt','/alidata/cy_sql1.sql')
    desc_table_test('/alidata/cy_table.txt','/alidata/cy_sql2.sql')

[root@toberoot alidata]# python py_createtable01.py
[root@toberoot alidata]# head cy_sql2.sql 
desc base_dict
;desc biz_advise
;desc biz_bank
;desc biz
;desc biz_gift
;desc biz_gprs_bind
;desc biz_gprs_bind_his
;desc biz_msg_template
;desc biz_take_bank
;desc biz_take

截取包含列名的报错
[root@toberoot alidata]# grep contains  mysql/dataerror.log > cy_error1.log
报错格式如下：
2018-05-17T07:58:32.926555Z 3 [Warning] InnoDB: Table cy/base_dict contains 1 user defined columns in InnoDB, but 11 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2018-05-17T07:59:03.555492Z 4 [Warning] InnoDB: Table cy/biz_advise contains 1 user defined columns in InnoDB, but 7 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2018-05-17T07:59:03.556045Z 4 [Warning] InnoDB: Table cy/biz_bank contains 1 user defined columns in InnoDB, but 7 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

将表名和列数存放至文件中
[root@toberoot alidata]# awk '{print $6,$15}' cy_error1.log | awk -F '/' '{print $2}' > cy_table_col.txt

根据以上表名和列数生成新的测试表
def create_table_col(table_col_file,sql_file):
    a_file = open(sql_file,'w')
    b_file = open(table_col_file)
    b_list = b_file.readlines()
    # b_list = ['t1 10','t2 20']
    str_list = []
    for table_col_str in b_list:
        table_col_list = table_col_str.split()
    table = table_col_list[0]
    col = int(table_col_list[1])
        string = "create table {} (".format(table)
        str_list.append(string)
    for i in range(1,col+1):
            if i!=col:
                string = 'id{} int,'.format(i)
        else:
                string = 'id{} int);'.format(i)
            str_list.append(string)
    for line in str_list:
        a_file.write(line)
    a_file.close()

create_table_col('/alidata/cy_table_col.txt','/alidata/cy_sql3.sql')

删除这些测试表
[root@toberoot alidata]# vim py_createtable01.py 
[root@toberoot alidata]# python py_createtable01.py 
[root@toberoot alidata]# ll
total 60
-rw-r--r--  1 root  root  16729 May 17 16:00 cy_error1.log
-rw-r--r--  1 root  root   2033 May 17 15:12 cy_sql1.sql
-rw-r--r--  1 root  root   1047 May 17 15:56 cy_sql2.sql
-rw-r--r--  1 root  root   6353 May 17 16:15 cy_sql3.sql
-rw-r--r--  1 root  root   1395 May 17 16:17 cy_sql4.sql
-rw-r--r--  1 root  root    837 May 17 16:03 cy_table_col.txt
-rw-r--r--  1 root  root    699 May 17 15:56 cy_table.txt
drwxr-xr-x  3 root  root   4096 May 17 12:12 install
drwxr-xr-x 11 mysql mysql  4096 May 17 15:44 mysql
-rw-r--r--  1 root  root   1798 May 17 16:17 py_createtable01.py

python代码如下：
def drop_table_test(table_file,sql_file):
    a_file = open(sql_file,'w')
    b_file = open(table_file)
    b_list = b_file.readlines()
    for table in b_list:
        string = "drop table {};".format(table)
        a_file.write(string)
    a_file.close()

drop_table_test('/alidata/cy_table.txt','/alidata/cy_sql4.sql')
    
[root@toberoot alidata]# head /alidata/cy_sql4.sql 
drop table base_dict
;drop table biz_advise
;drop table biz_bank
;drop table biz
;drop table biz_gift
;drop table biz_gprs_bind
;drop table biz_gprs_bind_his
;drop table biz_msg_template
;drop table biz_take_bank
;drop table biz_take

删除数据库的时候直接卡死了，原因未知。也没有报错。
清数据启动服务
ln: failed to create symbolic link ‘/usr/local/mysql/bin/mysqld’: File exists
Starting MySQL.                                            [  OK  ]
[root@toberoot ~]# mysql -e 'create database cy'

开始尝试获取表的结构
[root@toberoot alidata]# mysql cy < cy_sql3.sql
[root@toberoot alidata]# service mysqld stop
Shutting down MySQL..                                      [  OK  ]
[root@toberoot alidata]# cp ~/home/cy02/*.frm /alidata/mysql/data/cy/ -p
cp: overwrite ‘/alidata/mysql/data/cy/base_dict.frm’? ^C
[root@toberoot alidata]# yes | cp ~/home/cy02/*.frm /alidata/mysql/data/cy/ -p
[root@toberoot alidata]# ll /alidata/mysql/data/cy/sys_res*
-rw-r--r-- 1 mysql mysql  9106 Aug 14  2017 /alidata/mysql/data/cy/sys_res.frm
-rw-r----- 1 mysql mysql 98304 May 17 16:44 /alidata/mysql/data/cy/sys_res.ibd

配置文件
[mysqld]
innodb_force_recovery=6
[root@toberoot alidata]# vim /etc/my.cnf
[root@toberoot alidata]# service mysqld start
Starting MySQL.                                            [  OK  ]
表结构成功获取
[root@toberoot alidata]# mysql cy -e 'desc sys_res'
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| ID     | varchar(64)  | NO   | PRI | NULL    |       |
| NAME   | varchar(32)  | NO   |     | NULL    |       |
| CODE   | varchar(128) | NO   |     | NULL    |       |
| URI    | varchar(128) | NO   |     | NULL    |       |
| LOGO   | varchar(64)  | YES  |     | NULL    |       |
| TYPE   | int(11)      | NO   |     | NULL    |       |
| PCODE  | varchar(64)  | YES  |     | NULL    |       |
| SORT   | int(11)      | YES  |     | 0       |       |
| STATE  | int(11)      | NO   |     | NULL    |       |
| ADMIN  | int(11)      | YES  |     | 0       |       |
| REMARK | varchar(64)  | YES  |     | NULL    |       |
| CRTIME | datetime     | NO   |     | NULL    |       |
| UPTIME | datetime     | NO   |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
root@MySQL-01 16:49:  [(none)]> select table_name,table_schema from information_schema.tables where table_schema='cy';
+----------------------+--------------+
| table_name           | table_schema |
+----------------------+--------------+
| base_dict            | cy           |
| biz                  | cy           |
| biz_advise           | cy           |
| biz_bank             | cy           |
| biz_gift             | cy           |
| biz_gprs_bind        | cy           |
| biz_gprs_bind_his    | cy           |
| biz_msg_template     | cy           |
| biz_take             | cy           |
| biz_take_bank        | cy           |
| biz_take_wwlt        | cy           |
| biz_take_wx          | cy           |
| biz_vip              | cy           |
| biz_wlt              | cy           |
| biz_wx               | cy           |
| biz_wx_focus         | cy           |
| biz_wx_walt          | cy           |
| cfg_area             | cy           |
| cfg_id_gen           | cy           |
| gprs_model           | cy           |
| mbr                  | cy           |
| mbr_coin             | cy           |
| mbr_coin_chged       | cy           |
| mbr_oauth            | cy           |
| mbr_pay              | cy           |
| mbr_prizen           | cy           |
| mbr_recharge         | cy           |
| mbr_wallet           | cy           |
| mbr_wallet_chged     | cy           |
| ord                  | cy           |
| ord_item             | cy           |
| ord_pay_ali          | cy           |
| ord_pay_coin         | cy           |
| ord_pay_return       | cy           |
| ord_pay_wlt          | cy           |
| ord_pay_wx           | cy           |
| prod                 | cy           |
| prod_base_args       | cy           |
| prod_bug_rpt         | cy           |
| prod_cmd             | cy           |
| prod_cmd_invoke      | cy           |
| prod_coin_rpt        | cy           |
| prod_coin_rpt_log    | cy           |
| prod_gprs_bind       | cy           |
| prod_gprs_bind_his   | cy           |
| prod_instl_imgs      | cy           |
| prod_instl_pos       | cy           |
| prod_instl_pos_model | cy           |
| prod_mod_attr        | cy           |
| prod_mod_attr_val    | cy           |
| prod_mod_sku         | cy           |
| prod_model           | cy           |
| prod_onl_log         | cy           |
| prod_sp_arg_vals     | cy           |
| prod_sp_args         | cy           |
| sys_acct             | cy           |
| sys_acct_res         | cy           |
| sys_res              | cy           |
+----------------------+--------------+
58 rows in set (0.00 sec)
58张表dump备份出来
[root@toberoot alidata]# mysqldump -B cy -d > /alidata/new_yc_ddl.sql
[root@toberoot alidata]# tail -n 30 /alidata/new_yc_ddl.sql 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sys_res` (
  `ID` varchar(64) NOT NULL COMMENT 'ID',
  `NAME` varchar(32) NOT NULL COMMENT '菜单名称',
  `CODE` varchar(128) NOT NULL COMMENT '菜单编码',
  `URI` varchar(128) NOT NULL COMMENT 'URI',
  `LOGO` varchar(64) DEFAULT NULL COMMENT '图标',
  `TYPE` int(11) NOT NULL COMMENT '@菜单类型（1菜单；2按钮）',
  `PCODE` varchar(64) DEFAULT NULL COMMENT '父菜单',
  `SORT` int(11) DEFAULT '0' COMMENT '排序',
  `STATE` int(11) NOT NULL COMMENT '@@状态（0 无效；1 正常）',
  `ADMIN` int(11) DEFAULT '0' COMMENT '是否管理员菜单（默认0否）',
  `REMARK` varchar(64) DEFAULT NULL COMMENT '备注',
  `CRTIME` datetime NOT NULL COMMENT 'CRTIME',
  `UPTIME` datetime NOT NULL COMMENT 'UPTIME',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='权限_系统资源';
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-05-17 16:52:28


discard和import命令
def discard_table_test(table_file,sql_file):
    a_file = open(sql_file,'w')
    b_file = open(table_file)
    b_list = b_file.readlines()
    for table in b_list:
        string = "alter table {} discard tablespace;".format(table)
        a_file.write(string)
    a_file.close() 


def import_table_test(table_file,sql_file):
    a_file = open(sql_file,'w')
    b_file = open(table_file)
    b_list = b_file.readlines()
    for table in b_list:
        string = "alter table {} import tablespace;".format(table)
        a_file.write(string)
    a_file.close() 

    
discard_table_test('/alidata/cy_table.txt','/alidata/cy_sql5.sql')
import_table_test('/alidata/cy_table.txt','/alidata/cy_sql6.sql')
   
生成sql
[root@toberoot alidata]# python py_createtable01.py 
[root@toberoot alidata]# ll
total 124
-rw-r--r--  1 root  root  16729 May 17 16:00 cy_error1.log
-rw-r--r--  1 root  root   2033 May 17 15:12 cy_sql1.sql
-rw-r--r--  1 root  root   1047 May 17 15:56 cy_sql2.sql
-rw-r--r--  1 root  root   6353 May 17 16:15 cy_sql3.sql
-rw-r--r--  1 root  root   1395 May 17 16:17 cy_sql4.sql
-rw-r--r--  1 root  root   2033 May 17 16:55 cy_sql5.sql
-rw-r--r--  1 root  root   2033 May 17 16:55 cy_sql6.sql
-rw-r--r--  1 root  root    837 May 17 16:03 cy_table_col.txt
-rw-r--r--  1 root  root    699 May 17 15:56 cy_table.txt
drwxr-xr-x  3 root  root   4096 May 17 16:41 install
drwxr-xr-x 11 mysql mysql  4096 May 17 16:47 mysql
-rw-r--r--  1 root  root  57068 May 17 16:52 new_yc_ddl.sql
-rw-r--r--  1 root  root   2489 May 17 16:55 py_createtable01.py

[root@toberoot alidata]# head /alidata/cy_sql5.sql 
alter table base_dict
 discard tablespace;alter table biz_advise
 discard tablespace;alter table biz_bank
 discard tablespace;alter table biz
 discard tablespace;alter table biz_gift
 discard tablespace;alter table biz_gprs_bind
 discard tablespace;alter table biz_gprs_bind_his
 discard tablespace;alter table biz_msg_template
 discard tablespace;alter table biz_take_bank
 discard tablespace;alter table biz_take
[root@toberoot alidata]# head /alidata/cy_sql6.sql 
alter table base_dict
 import tablespace;alter table biz_advise
 import tablespace;alter table biz_bank
 import tablespace;alter table biz
 import tablespace;alter table biz_gift
 import tablespace;alter table biz_gprs_bind
 import tablespace;alter table biz_gprs_bind_his
 import tablespace;alter table biz_msg_template
 import tablespace;alter table biz_take_bank
 import tablespace;alter table biz_take


[root@toberoot alidata]# mysql cy < cy_sql5.sql
ERROR 1036 (HY000) at line 1: Table 'base_dict' is read only
[root@toberoot alidata]# vim /etc/my.cnf
[root@toberoot alidata]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]
[root@toberoot alidata]# vim /etc/my.cnf
[root@toberoot alidata]# mysql cy < cy_sql5.sql

[root@toberoot alidata]# cp /root/home/cy02/*.ibd /alidata/mysql/data/cy/ -rp


[root@toberoot alidata]# mysql cy < cy_sql6.sql
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
[root@toberoot alidata]# vim /etc/my.cnf
[root@toberoot alidata]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]


[root@toberoot alidata]# mysql cy < cy_sql6.sql
ERROR 1036 (HY000) at line 1: Table 'base_dict' is read only
[root@toberoot alidata]# service mysqld stop
Shutting down MySQL..                                      [  OK  ]
[root@toberoot alidata]# rm -rf /alidata/mysql/data/*

[root@toberoot alidata]# service mysqld start
Starting MySQL.                                            [  OK  ]
[root@toberoot alidata]# mysql < new_yc_ddl.sql 
[root@toberoot alidata]# mysql -e 'use cy;select * from sys_res'

开启强制恢复参数
[root@toberoot alidata]# vim /etc/my.cnf
[root@toberoot alidata]# mysql cy < cy_sql
cy_sql1.sql  cy_sql2.sql  cy_sql3.sql  cy_sql4.sql  cy_sql5.sql  cy_sql6.sql  
[root@toberoot alidata]# mysql cy < cy_sql5.sql 
[root@toberoot alidata]# cp /root/home/cy02/*.ibd /alidata/mysql/data/cy/ -rp
[root@toberoot alidata]# mysql cy < cy_sql6.sql

全备份数据
[root@toberoot alidata]# mysqldump -B cy > new_cy_all.sql

查看备份的数据量
-rw-r--r--  1 root  root   26M May 17 17:13 new_cy_all.sql

报错汇总

ERROR 1036 （HY00）

[root@toberoot alidata]# mysql cy < cy_sql5.sql
ERROR 1036 (HY000) at line 1: Table 'base_dict' is read only

解决方法：

  1. 检查是否配置文件中存在innodb_force_recovery参数
  2. 如果存在去除或注释掉，重启服务即可
  3. 还不行就清空数据库导入结构后再继续

ERROR 2013 (HY000)

[root@toberoot alidata]# mysql cy < cy_sql6.sql
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

解决方法：

  1. sql脚本中执行import tablespace的操作需要开启recovery的参数
  2. 重启服务
    在客户服务上，执行以上操作还是报错无法连接，尝试多次都不行。

总结

这次case的教训就是，备份！一定要有周期性的有效备份！
欺骗MySQL进程蒙混过关只能是没办法的办法，而且不能保证成功率。

更多精彩内容，敬请扫描图中二维码


weixin_33894992
关注
mysql 数据备份与恢复使用详解
congge
11-20 7032
mysql数据备份与恢复
第79讲： MySQL Binlog二进制日志恢复误删数据的实践指南
热门推荐
江晓龙的博客
12-18 1万+
根据需求我们得知要恢复误删除的这张表，那么在事件信息中找到创建该表的事件，这个事件的开始标识位就作为我们截取Binlog日志的开始标识位号，结束标识位更好确定了，在事件信息中找到删除表的事件，这个事件的开始标识位也就作为我们截取Binlog日志的结束标识位，开始标识位号是上一个事件的结束表示为号。注意：一定不要将删除表事件的结束标识位号作为截取Binlog日志的结束标识位号，如果截取Binlog日志的结束标识位号是删除表的结束标识位号，就会把删除表的操作在还原时又进行了一遍，数据就不能恢复了。
innodb_force_recovery强制修复MySQL异常关闭问题
weixin_34080903的博客
02-01 258
1、在mysql的配置文件my.cnf里添加以下代码： [mysqld] innodb_force_recovery=1 #可以设置1-6 innodb_purge_thread=0 #如果上边的配置值大于1，此处就要设置为0，否则就会出现InnoDB: Waiting for the background threads to start的错误 配置文件的参数：innodb_force_re...
mysql数据恢复
qian0021514578
11-19 453
MySQL Binary Log也就是常说的bin-log,，是mysql执行改动产生的二进制日志文件，其主要作用有两个： * 数据恢复 * 主从数据库。用于slave端执行增删改，保持与master同步。 1.开启binary log功能      需要修改mysql的配置文件，本篇的实验环境是win7，配置文件为mysql安装目录\MySQL Server 5.1下的my.ini，添加...
【MySQL核心】MySQL无法启动？批量恢复MySQL 物理文件-拯救即将跑路的你
hh的博客
08-12 1690
本文章主要讲解 MySQL 异常宕机等意外情况下导致 ibd 文件损坏，配置mysql强制恢复（） 1-6 级仍然无法启动的情况。下面是恢复整个实例的所有流程和思路。这里恢复的实例是 mysql5.6 的整个流程，5.7和 5.6 类似，8.0 可以直接用ibd2sql进行坏表的恢复。再次提醒：建议 MySQL 开启定时全备、开启row格式binlog、创建从库等冗余操作，防止重要数据丢失！！！操作前请行备份！！！操作前请确认磁盘空间！！！
强制进行innodb恢复(数据库崩溃恢复)
逐月
12-04 595
msyql innodb数据库崩溃恢复
mysql 各种恢复_mysql 恢复方案
weixin_30855745的博客
02-01 631
备份数据mysqldump db_name [tables]mysqldump---database DB1 [DB2 DB3...]mysqldump--all—databasemysqldump --user=root -p xm > /backup/xm.sql以上指令，将xm 库备份到xm.sql 文件中。输入指令后，需要输入mysql 的root 密码。一般我都不用加-us...
MySQL通过Binlog恢复数据
撸码社区的博客
04-18 1万+
Binlog简介 开启Binlog 使用Binlog恢复数据
MySQL执行delete误删除数据恢复
java专栏
04-04 2291
如果MySQL启用了二进制日志（通常用于复制或点时间恢复），你可以使用mysqlbinlog工具来查看日志，并尝试从日志中恢复被删除的数据。如果你的表使用的是InnoDB存储引擎，那么InnoDB会维护一个undo日志，用于在事务回滚时撤销更改。通过分析二进制日志，你可以找到DELETE语句之前的所有插入和更新操作，并手动重放这些操作以恢复数据。恢复数据可能是一个复杂且耗时的过程，因此请耐心操作，并在必要时寻求专业人士的帮助。在尝试恢复数据之前，请务必先备份当前的数据库状态，以防止进一步的数据丢失或损坏。
【大数据】Flink CDC 实时同步mysql数据
最新发布
congge
10-07 9416
Flink CDC 实时同步mysql数据
对于mysql 5.7版本中的找不到data文件夹、不能启动的解决方法
beBESTed的博客
09-28 3131
mysql升级到5.7之后，下载包中就没有了data文件夹，所以mysql虽然能够成功安装，但是就不能正常启动了， 启动的时候，就会报： mysql服务无法启动，服务没有报告任何错误。解决方法如下： 成功安装mysql后，命令行在mysql的bin目录下，输入下面内容： mysqld –initialize-insecure –user=mysql 之后再启动就可以了。
mysql 8 重新初始化，修改密码及连接数据库操作，找回原文件数据
qq1073674233的博客
10-23 2590
mysql 8 重新初始化，修改密码及连接数据库操作，找回原文件数据
mysql数据恢复方法
autoDBA博客
06-27 260
1 存在定期备份 2 临时备份 3 其他方法 恢复数据方法： 下面按照三种场景介绍数据恢复方法： 1 存在定期备份 1.1 启动当天的备份 备份文件一般位于：/data1/backup/mysql<port>/<日期> 目录下 以3999为端口启动（3999表示备份实例），注意：不要自动启动slave 首先需要确定出问题的点位的前一个点位，也就是我们需...
【MySQL】MySQL安装常见得报错及解决方法
d586497的博客
01-19 7496
错误1 make: *** No targets specified and no makefile found. Stop. 1、wget http://ftp.gnu.org/pub/gnu/ncurses/ncurses-5.6.tar.gz 2.、tar zxvf ncurses-5.6.tar.gz 3、 ./configure -prefix=/usr/local -with-shared-without-debug 4、make 5、make install 报错2： Inst
MySQL 超详细安装教程 手把手教你安装 MySQL 到使用 MySQL 最简单的 MySQL 安装方式，这种方式装，卸载也简单（零基础入门 MySQL）
Park33的博客
03-20 661
注释掉 my.ini 文件的最后一句话，加 #号注释。
mysql数据库受损恢复_MySQL数据库损坏如何恢复
weixin_33649132的博客
01-19 372
最后解决的办法：0.备份原/var/lib/mysql文件夹，执行mysqld --initialize1.copy ibdata1及数据库WordPress相关文件夹cp /var/lib/mysql-backup/ibdata1 /var/lib/mysqlcp -r /var/lib/mysql-backup/wordpress /var/lib/mysql2.修改root为mysql用户...
mysql下载、配置、安装使用
star_yang的博客
05-02 1310
本文简单介绍mysql的下载、安装及配置、简单使用。通过本文可以详细的了解mysql如何下载、安装、配置、使用。推荐小白阅读，适合类似楼主菜鸟级别使用哦。
mysql 强制InnoDB恢复 innodb_force_recovery详解
yangydq的专栏
10-26 1万+
mysql5.7 强制innoDB恢复 innodb_force_recovery mysql无法启动 数据库恢复
MySQL二进制日志恢复数据教程
找到错误操作的前一个位置，停止MySQL服务，导出日志，删除错误的SQL，然后按照顺序恢复备份的SQL和基于位置的日志，从而恢复数据。 在实际操作中，可能涉及多个二进制日志文件，需要用空格分隔它们。在整个过程中...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值