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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

摘要: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进程蒙混过关只能是没办法的办法,而且不能保证成功率。

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


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3天前
|
SQL 关系型数据库 MySQL
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
|
1天前
|
SQL 资源调度 关系型数据库
实时计算 Flink版产品使用合集之在抓取 MySQL binlog 数据时,datetime 字段会被自动转换为时间戳形式如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
8 2
|
2天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
16 0
|
2天前
|
消息中间件 关系型数据库 MySQL
实时计算 Flink版产品使用合集之2.2.1版本同步mysql数据写入doris2.0 ,同步完了之后增量的数据延迟能达到20分钟甚至一直不写入如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
11 1
|
2天前
|
Oracle 关系型数据库 MySQL
实时计算 Flink版产品使用合集之Flink CDC 2.3.0和Flink 1.17,无法从MySQL数据库中抽取数据,是什么原因导致的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
19 1
|
2天前
|
关系型数据库 MySQL 数据处理
实时计算 Flink版产品使用合集之如果在 MySQL 表中为某个字段设置了默认值,并且在插入数据时指定了该字段为 NULL,那么 MySQL 是否会使用默认值来填充这个字段
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
13 0
|
2天前
|
关系型数据库 MySQL Java
实时计算 Flink版产品使用合集之mysql通过flink cdc同步数据,有没有办法所有表共用一个dump线程
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
8 0
|
2天前
|
关系型数据库 MySQL Java
实时计算 Flink版产品使用合集之如果MySQL的binlog保存时间只有三天,那么三天之前的数据是不是会通过Doris的外表手动插入
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
12 0
|
3天前
|
存储 关系型数据库 MySQL
|
4天前
|
前端开发 关系型数据库 MySQL
SpringBoot-----从前端更新数据到MySql数据库
SpringBoot-----从前端更新数据到MySql数据库
11 1

推荐镜像

更多