数据库崩溃恢复生机表布局的秘技,mysql使用federated实现dblink远程表访谈

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

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

  1. source 端创建测试表
    create table s select * from mysql.user ;

  2. source 端查看测试表的建表语句
    show create table s ;

安装mysqlfrm

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

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

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

wget 

wget 

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已经安装完毕。

<<EOF
CREATE TABLE `s` (
`Host` char(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT
”,
`User` char(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT
”,
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_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_questions` int(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) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT ”,
`authentication_string` text CHARACTER SET utf8 COLLATE utf8_bin,
`password_expired` enum(‘N’,’Y’) CHARACTER SET utf8 NOT NULL DEFAULT
‘N’
) ENGINE=InnoDB DEFAULT CHARSET=latin1

恢复表结构

很简单,一条命令搞定。

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.

EOF

  1. source端创建用户并且授权

GRANT ALL PRIVILEGES ON sources.s TO dex IDENTIFIED BY ‘xiaojun’;
GRANT ALL PRIVILEGES ON sources.s TO
[email protected]
IDENTIFIED BY ‘xiaojun’;
SHOW GRANTS FOR dex;

mysql> GRANT ALL PRIVILEGES ON sources.s TO dex IDENTIFIED BY
‘xiaojun’;
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL PRIVILEGES ON sources.s TO
[email protected]
IDENTIFIED BY ‘xiaojun’;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR dex;
+—————————————————————————————————-+
| Grants for
[email protected]%
|
+—————————————————————————————————-+
| GRANT USAGE ON *.* TO ‘dex’@’%’ IDENTIFIED BY PASSWORD
‘*8FDE30312222738F1CD8AC8AF0EE515A9DB8180E’ |
| GRANT ALL PRIVILEGES ON `sources`.`s` TO ‘dex’@’%’ |
+—————————————————————————————————-+
2 rows in set (0.00 sec)

  1. 查看target端是否安装了FEDERATED存储引擎

mysql> mysql> show engines ;
+——————–+———+—————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————-+————–+——+————+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO |
NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it
disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary
tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL
|
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and
foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+——————–+———+—————————————————————-+————–+——+————+
9 rows in set (0.00 sec)

看到没有安装federated存储引擎(目标端可以是任何的存储引擎比如说myisam或者innodb)

4.1 先来安装federated存储引擎

install plugin federated soname ‘ha_federated.so’;

mysql> install plugin federated soname ‘ha_federated.so’;
ERROR 1125 (HY000): Function ‘federated’ already exists

发表评论

电子邮件地址不会被公开。 必填项已用*标注