mysqlfrm读取frm文件获取建表ddl并恢复数据

环境:

OS:Centos 6

[root@dev-env23 mysql-utilities-1.6.5]# python -V
Python 2.6.6

 

说明:

该工具需要使用python环境,目前只支持python2 还不支持python3,若想在python3下使用,可以尝试另外一个工具:dbsake，可以参考:https://www.cnblogs.com/hxlasky/p/17411236.html

 

在python3下安装mysql-utilities会报如下错误

[root@host134 mysql-utilities-1.6.5]# python ./setup.py install --record instfile.log
checking mysql for packages to distribute
packages found: ['mysql', 'mysql.connector', 'mysql.connector.django', 'mysql.connector.fabric', 'mysql.connector.locales', 'mysql.connector.locales.eng', 'mysql.utilities', 'mysql.utilities.command', 'mysql.utilities.common']
scripts found: ['scripts/mysqlrplms.py', 'scripts/mysqldbcompare.py', 'scripts/mysqlserverclone.py', 'scripts/mysqluc.py', 'scripts/mysqldiskusage.py', 'scripts/mysqlauditadmin.py', 'scripts/mysqluserclone.py', 'scripts/mysqlbinlogrotate.py', 'scripts/mysqlreplicate.py', 'scripts/mysqlfailover.py', 'scripts/mysqlbinlogmove.py', 'scripts/mysqlindexcheck.py', 'scripts/mysqldbexport.py', 'scripts/mysqlrplcheck.py', 'scripts/mysqlslavetrx.py', 'scripts/mysqlmetagrep.py', 'scripts/mysqlprocgrep.py', 'scripts/mysqlrplsync.py', 'scripts/mysqlbinlogpurge.py', 'scripts/mysqldiff.py', 'scripts/mysqlrplshow.py', 'scripts/mysqlfrm.py', 'scripts/mysqldbimport.py', 'scripts/mysqlauditgrep.py', 'scripts/mysqlgrants.py', 'scripts/mysqldbcopy.py', 'scripts/mysqlserverinfo.py', 'scripts/mysqlrpladmin.py']
package set {'mysql.connector.locales', 'mysql.utilities.command', 'mysql.utilities.common', 'mysql.utilities', 'mysql.connector.locales.eng', 'mysql.connector', 'mysql', 'mysql.connector.fabric', 'mysql.connector.django'}
MySQL Utilities requires Python v2.6 or v2.7

 

 

 

##################################获取建表ddl#########################

1.下载
下载地址:
https://downloads.mysql.com/archives/utilities/
下载的版本为:
mysql-utilities-1.6.5.tar.gz

 

2.解压
[root@dev-env23 soft]# tar -xvf mysql-utilities-1.6.5.tar.gz
[root@dev-env23 soft]# mv mysql-utilities-1.6.5 /opt/

 

3.安装
cd /opt/mysql-utilities-1.6.5
python ./setup.py install --record instfile.log

1) 使用选项 --record instfile.log 记录安装到系统的文件,在卸载时使用(手动删除).
2) 执行 python setup.py help 查看其他选项及帮助信息.
3) 所有命令会默认安装在 /usr/local/ 中.

 

4.通过frm文件获取到建表语句

[root@dev-env23 mysql-utilities-1.6.5]# mysqlfrm --diagnostic /home/mysql/data/db_yeemiao/ad.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for /home/mysql/data/db_yeemiao/ad.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `db_yeemiao`.`ad` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT, 
  `article_id` bigint(20) DEFAULT NULL comment '文章id', 
  `ad_name` varchar(400) NOT NULL comment '广告名称', 
  `picture` varchar(600) NOT NULL comment '图片', 
  `link_type` int(11) NOT NULL comment '0:外链，1：文章，2：帖子，3：无跳转', 
  `content_link` varchar(2000) DEFAULT NULL comment '内容链接', 
  `type` int(11) NOT NULL, 
  `status` int(11) NOT NULL comment '是否启用：0-启用，1-禁用 默认启用', 
  `is_del` bit(1) NOT NULL comment '是否删除：0未删除,1删除', 
  `start_time` datetime NOT NULL comment '开始时间', 
  `end_time` datetime NOT NULL comment '结束时间', 
  `sort` int(11) NOT NULL comment '排序权重（越小越靠后）', 
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '创建时间', 
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '修改时间', 
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

#...done.

 

若数据库的字符集是utf8mb4的话，不指定server参数的话，字符类型的字段大小是原来大小*4,如原来大小是100的,那么导出来的字符大小就是400，若想解决该问题可以带上server参数,知道新服务器

 

mysqlfrm --diagnostic /tmp/aa/v_child.frm --server=root:mysql@192.168.1.134:13306

 

可以从帮助文档获取使用方式

[root@dev-env23 aa]# mysqlfrm --help

 

  --server=SERVER       connection information for the server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>] (optional) - if
                        provided, the storage engine and character set
                        information will be validated against this server.

 

 

 

 

################数据恢复##########################
1.通过上面获取到的建表语句在新服务器上创建表

2.脱离表空间
alter table ad discard tablespace;
执行该命令后,对应的idb文件会自动删除掉

[root@host134 db_ym]# ls -al
total 16
drwx------ 2 mysql mysql 34 May 17 15:13 .
drwxrwxr-x 6 mysql mysql 238 May 17 14:52 ..
-rw-rw---- 1 mysql mysql 8556 May 17 15:12 ad.frm
-rw-rw---- 1 mysql mysql 67 May 17 14:38 db.opt

 

3.将crash服务器的表的idb文件拷贝到新服务器
cp /tmp/ad.ibd /opt/mysql56/data/db_ym/
chown -R mysql:mysql /opt/mysql56/data/db_ym/

4.加入表空间
alter table ad import tablespace;

 

5.验证
select count(1) from ad;