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

佚名 6年前 (2018-07-14) Mysql 1719人围观抢沙发百度已收录

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

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

安装mysqlfrm

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

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

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

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

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

wget https://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 TABLE mysql.user ( Host char(60) COLLATE utf8_bin NOT NULL DEFAULT '', User char(32) COLLATE utf8_bin NOT NULL DEFAULT '',Select_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Insert_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Update_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Delete_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Create_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Drop_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Reload_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Shutdown_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Process_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', File_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Grant_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', References_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Index_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Alter_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Show_db_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Super_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',Create_tmp_table_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Lock_tables_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Execute_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Repl_slave_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Repl_client_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Create_view_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Show_view_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Create_routine_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Alter_routine_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Create_user_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Event_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Trigger_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Create_tablespace_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', ssl_type enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '', ssl_cipher blob NOT NULL, x509_issuer blob NOT NULL, x509_subject blob NOT NULL, max_questionsint(11) unsigned NOT NULL DEFAULT '0', max_updates int(11) unsigned NOT NULL DEFAULT '0', max_connections int(11) unsigned NOT NULL DEFAULT '0',max_user_connections int(11) unsigned NOT NULL DEFAULT '0', plugin char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',authentication_string text COLLATE utf8_bin, password_expired enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', password_last_changedtimestamp NULL DEFAULT NULL, password_lifetime smallint(5) unsigned DEFAULT NULL, account_locked enum('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'