数据库崩溃恢复表结构的方法

数据库崩溃恢复表结构的方法

如果数据库发生崩溃，无法登陆数据库，想要快速恢复表结构的话有一个很方便的方法。 通过mysqlfrm工具就可以快速解析.frm文件，找到create table 语句。

安装mysqlfrm

安装mysqlfrm的话，需要两个安装包。mysql-utilities和mysql-connector-python。

可以去官网里找到对应操作系统版本的rpm安装包。mysql-utilities，mysql-connector-python.

这里演示centos7版本的安装方式：

wgethttps://downloads.mysql.com/archives/get/file/mysql-utilities-1.6.5-1.el7.noarch.rpm

wgethttps://downloads.mysql.com/archives/get/file/mysql-connector-python-2.1.6-1.el7.x86_64.rpm

rpm -ivh mysql-connector-python-2.1.6-1.el7.x86_64.rpm

rpm -ivh mysql-utilities-1.6.5-1.el7.noarch.rpm

which mysqlfrm
/usr/bin/mysqlfrm

mysqlfrm已经安装完毕。

恢复表结构

很简单，一条命令搞定。

1、进入到想要恢复的表的目录里面。
cd /data/mysql/mysql3306/data/mysql
2、指定user，basedir，port(找一个没有用的端口）
mysqlfrm --user=mysql --basedir=/usr/local/mysql mysql:user.frm --port=3333

CREATE TABLEmysql.user(Hostchar(60) COLLATE utf8_bin NOT NULL DEFAULT '',Userchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',Select_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Insert_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Update_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Delete_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Create_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Drop_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Reload_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Shutdown_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Process_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',File_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Grant_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',References_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Index_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Alter_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Show_db_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Super_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Create_tmp_table_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Lock_tables_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Execute_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Repl_slave_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Repl_client_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Create_view_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Show_view_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Create_routine_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Alter_routine_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Create_user_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Event_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Trigger_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Create_tablespace_privenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',ssl_typeenum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',ssl_cipherblob NOT NULL,x509_issuerblob NOT NULL,x509_subjectblob NOT NULL,max_questionsint(11) unsigned NOT NULL DEFAULT '0',max_updatesint(11) unsigned NOT NULL DEFAULT '0',max_connectionsint(11) unsigned NOT NULL DEFAULT '0',max_user_connectionsint(11) unsigned NOT NULL DEFAULT '0',pluginchar(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',authentication_stringtext COLLATE utf8_bin,password_expiredenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',password_last_changedtimestamp NULL DEFAULT NULL,password_lifetimesmallint(5) unsigned DEFAULT NULL,account_lockedenum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (Host,User) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

...done.