![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
MySQL Utilities(mysqlfrm)
MySQL以下にある,FRMファイルからTable作成DDLを作成する。
mysqlfrm show CREATE TABLE from .frm files
スペースの問題なのか、直接MySQLのフォルダー”C:\ProgramData\MySQL\MySQL Server 5.6\data\test\”
からコマンド実行したらエラーになってしまったので、検証用に一時的にシンボリックリンク作成して検証。
C:\ProgramData\MySQL>mklink /d test "C:\ProgramData\MySQL\MySQL Server 5.6\data\test"
test <<===>> C:\ProgramData\MySQL\MySQL Server 5.6\data\test のシンボリック リンクが作成されました
C:\ProgramData\MySQL>
C:\>dir C:\ProgramData\MySQL\test
ドライブ C のボリューム ラベルは 既定 です
ボリューム シリアル番号は 14CD-A9DB です
C:\ProgramData\MySQL\test のディレクトリ
2014/07/29 15:14 <DIR> .
2014/07/29 15:14 <DIR> ..
2014/07/11 16:49 9,582 db8.frm
2014/07/29 14:11 440 db8.MYD
2014/07/29 14:11 5,120 db8.MYI
2014/07/29 10:23 8,648 language.frm
2014/07/29 10:23 114,688 language.ibd
2014/07/07 17:43 8,688 lck.frm
2014/07/07 17:55 114,688 lck.ibd
2014/07/23 12:58 8,574 montable.frm
2014/07/23 12:58 98,304 montable.ibd
2014/07/24 15:09 8,556 t.frm
2014/07/24 15:09 98,304 t.ibd
11 個のファイル 475,592 バイト
2 個のディレクトリ 137,676,480,512 バイトの空き領域
C:\>
■mysqlfrmの概要
mysqluc> mysqlfrm --help
MySQL Utilities mysqlfrm.exe version 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
License type: GPLv2
Usage: mysqlfrm.exe --server=[user[:<pass>]@host[:<port>][:<socket>]|<login-path>[:<port>][:<socket>]] [path\tbl1.frm|db:tbl.frm]
mysqlfrm - show CREATE TABLE from .frm files
Options:
--version show program's version number and exit
--license display program's license and exit
--help
--basedir=BASEDIR the base directory for the server
--diagnostic read the frm files byte-by-byte to form the CREATE
statement. May require the --server or --basedir
options to decipher character set information
--new-storage-engine=NEW_ENGINE
change ENGINE clause to use this engine.
--frmdir=FRMDIR save the new .frm files in this directory. Used and
valid with --new-storage-engine only.
--port=PORT Port to use for the spawned server.
-s, --show-stats show file statistics and general table information.
--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.
--user=USER user account to launch spawned server. Required if
running as root user. Used only in the default mode.
--start-timeout=START_TIMEOUT
Number of seconds to wait for spawned server to start.
Default = 10.
-v, --verbose control how much information is displayed. e.g., -v =
verbose, -vv = more verbose, -vvv = debug
-q, --quiet turn off all messages for quiet execution.
Introduction
------------
The mysqlfrm utility is designed as a recovery tool that reads .frm files and
produces facsimile CREATE statements from the table definition data found in
the .frm file. In most cases, the CREATE statement produced will be usable
for recreating the table on another server or for extended diagnostics.
However, some features are not saved in the .frm files and therefore will be
omitted. The exclusions include but are not limited to:
- foreign key constraints
- auto increment number sequences
The mysqlfrm utility has two modes of operation. The default mode is
designed to spawn an instance of an installed server by reference
to the base directory using the --basedir option or by connecting to the
server with the --server option. The process will not alter the original
.frm file(s). This mode also requires the --port option to specify a
port to use for the spawned server. The spawned server will be shutdown
and all temporary files removed after the .frm files are read.
A diagnostic mode is available by using the --diagnostic option. This will
switch the utility to reading the .frm files byte-by-byte to recover as
much information as possible. The diagnostic mode has additional limitations
in that it cannot decipher character set or collation values without using
an existing server installation specified with either the --server or
--basedir option. This can also affect the size of the columns if the table
uses multi-byte characters. Use this mode when the default mode cannot read
the file or if there is no server installed on the host.
To read .frm files, list each file as a separate argument for the utility as
shown in the following examples. You will need to specify the path for each
.frm file you want to read or supply a path to a directory and all of the
.frm files in that directory will be read.
# Read a single .frm file in the default mode using the server installed
# in /usr/local/bin/mysql where the .frm file is in the current folder.
# Notice the use of the db:table.frm format for specifying the database
# name for the table. The database name appears to the left of ':' and
# the .frm name to the right. So in this case, we have database = test1
# and table = db1 so the CREATE statement will read CREATE test1.db1.
$ mysqlfrm --basedir=/usr/local/bin/mysql test1:db1.frm --port=3333
# Read multiple .frm files in the default mode using a running server
# where the .frm files are located in different folders.
$ mysqlfrm --server=root:pass@localhost:3306 /mysql/data/temp1/t1.frm \
/mysql/data/temp2/g1.frm --port=3310
# Execute the spawned server under a different user name and read
# all of the .frm files in a particular folder in default mode.
$ mysqlfrm --server=root:pass@localhost:3306 /mysql/data/temp1/t1.frm \
/mysql/data/temp2/g1.frm --port=3310 --user=joeuser
# Read all of the .frm files in a particular folder using the diagnostic
# mode.
$ mysqlfrm --diagnostic /mysql/data/database1
Helpful Hints
-------------
- Tables with certain storage engines cannot be read in the default mode.
These include PARTITION, PERFORMANCE_SCHEMA. You must read these with
the --diagnostic mode.
- Use the --diagnostic mode for tables that fail to open correctly
in the default mode or if there is no server installed on the host.
- To change the storage engine in the CREATE statement generated for all
.frm files read, use the --new-storage-engine option
- To turn off all messages except the CREATE statement and warnings or
errors, use the --quiet option.
- Use the --show-stats option to see file statistics for each .frm file.
- If you encounter connection or similar errors when running in default
mode, re-run the command with the --verbosity option and view the
output from the spawned server and repair any errors in launching the
server. If mysqlfrm fails in the middle, you may need to manually
shutdown the server on the port specified with --port.
- If the spawned server takes more than 10 seconds to start, use the
--start-timeout option to increase the timeout to wait for the
spawned server to start.
- If you need to run the utility with elevated privileges, use the --user
option to execute the spawned server using a normal user account.
- You can specify the database name to be used in the resulting CREATE
statement by prepending the .frm file with the name of the database
followed by a colon. For example, oltp:t1.frm will use 'oltp' for the
database name in the CREATE statement. The optional database name can
also be used with paths. For example, /home/me/oltp:t1.frm will use
'oltp' as the database name. If you leave off the optional database
name and include a path, the last folder will be the database name.
For example /home/me/data1/t1.frm will use 'data1' as the database
name. If you do not want to use the last folder as the database name,
simply specify the colon like this: /home/me/data1/:t1.frm. In this
case, the database will be omitted from the CREATE statement.
- If you use the --new-storage-engine option, you must also provide the
--frmdir option. When these options are specified, the utility will
generate a new .frm file (prefixed with 'new_') and save it in the
--frmdir= directory.
Enjoy!
mysqluc>
■単一ファイルからDDLを作成してみる
mysqluc> mysqlfrm --server=root:Logical06@localhost 'C:\ProgramData\MySQL\test\language.frm' --port=33066 # Source on localhost: ... connected. # Starting the spawned server on port 3306666 ... done. # Reading .frm files # # Reading the language.frm file. # # CREATE statement for C:\ProgramData\MySQL\test\language.frm: # CREATE TABLE `test`.`language` ( `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`language_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 #...done. mysqluc>
■ディレクトリー配下にあるファイルからDDLを作成してみる
mysqluc> mysqlfrm --server=root:Logical06@localhost 'C:\ProgramData\MySQL\test' --port=330666
# Source on localhost: ... connected.
# Starting the spawned server on port 330666 ... done.
# Reading .frm files
#
# Reading the db8.frm file.
#
# CREATE statement for C:\ProgramData\MySQL\test\db8.frm:
#
CREATE TABLE `test`.`db8` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) 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',
`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',
`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',
`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',
`Execute_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',
PRIMARY KEY (`Host`,`Db`,`User`),
KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
#
# Reading the language.frm file.
#
# CREATE statement for C:\ProgramData\MySQL\test\language.frm:
#
CREATE TABLE `test`.`language` (
`language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#
# Reading the lck.frm file.
#
# CREATE statement for C:\ProgramData\MySQL\test\lck.frm:
#
CREATE TABLE `test`.`lck` (
`LC_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`LC_id`),
KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#
# Reading the montable.frm file.
#
# CREATE statement for C:\ProgramData\MySQL\test\montable.frm:
#
CREATE TABLE `test`.`montable` (
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
#
# Reading the t.frm file.
#
# CREATE statement for C:\ProgramData\MySQL\test\t.frm:
#
CREATE TABLE `test`.`t` (
`c1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#...done.