MySQL一次惊心动魄地数据强制恢复-白红宇

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

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

数据库因存储空间不够导致数据库服务宕掉，而在数据库宕机后，只拷贝了单个cy库所属的目录进行了文件层面的备份，就将其他文件全部清空，当想重新启动数据库时发现数据库服务启动。

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 startStarting MySQL.                                            [  OK  ][root@toberoot mysql]# mysql -Vmysql  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]# lltotal 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.txtpython脚本自动生成建表语句[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 stopShutting 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]# lltotal 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.txtdrwxr-xr-x  3 root  root   4096 May 17 12:12 installdrwxr-xr-x 11 mysql mysql  4096 May 17 15:44 mysql-rw-r--r--  1 root  root   1798 May 17 16:17 py_createtable01.pypython代码如下：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 existsStarting MySQL.                                            [  OK  ][root@toberoot ~]# mysql -e 'create database cy'开始尝试获取表的结构[root@toberoot alidata]# mysql cy < cy_sql3.sql[root@toberoot alidata]# service mysqld stopShutting down MySQL..                                      [  OK  ][root@toberoot alidata]# cp ~/home/cy02/*.frm /alidata/mysql/data/cy/ -pcp: 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 startStarting 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:28discard和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]# lltotal 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.txtdrwxr-xr-x  3 root  root   4096 May 17 16:41 installdrwxr-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.sqlERROR 1036 (HY000) at line 1: Table 'base_dict' is read only[root@toberoot alidata]# vim /etc/my.cnf[root@toberoot alidata]# service mysqld restartShutting 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.sqlERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query[root@toberoot alidata]# vim /etc/my.cnf[root@toberoot alidata]# service mysqld restartShutting down MySQL..                                      [  OK  ]Starting MySQL.                                            [  OK  ][root@toberoot alidata]# mysql cy < cy_sql6.sqlERROR 1036 (HY000) at line 1: Table 'base_dict' is read only[root@toberoot alidata]# service mysqld stopShutting down MySQL..                                      [  OK  ][root@toberoot alidata]# rm -rf /alidata/mysql/data/*[root@toberoot alidata]# service mysqld startStarting 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_sqlcy_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.sqlERROR 1036 (HY000) at line 1: Table 'base_dict' is read only

解决方法：

检查是否配置文件中存在innodb_force_recovery参数

如果存在去除或注释掉，重启服务即可

还不行就清空数据库导入结构后再继续

ERROR 2013 (HY000)

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

解决方法：

sql脚本中执行import tablespace的操作需要开启recovery的参数

重启服务
在客户服务上，执行以上操作还是报错无法连接，尝试多次都不行。

总结

这次case的教训就是，备份！一定要有周期性的有效备份！

欺骗MySQL进程蒙混过关只能是没办法的办法，而且不能保证成功率。

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