資料庫崩潰恢復表結構的方法

如果資料庫發生崩潰，無法登陸資料庫，想要快速恢復表結構的話有一個很方便的方法。 通過mysqlfrm工具就可以快速解析.frm文件，找到create table 語句。

安裝mysqlfrm

安裝mysqlfrm的話，需要兩個安裝包。mysql-utilities和mysql-connector-python。

可以去官網裡找到對應操作系統版本的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'

...done.