Issues recovering a .frm file using mysqlfrm

Question

Hopefully someone can help me, I've researched many of the recovery answers on stackoverflow.

I did the mistake of copying the data folder from mysql and pasting it in the new wamp 2.5 mysql/mysl5.6.17/data folder.

When I click on a table it gives "table doesn't exist". This below shows the what's showing

3688 [Warning] InnoDB: Cannot open table craigmedia/wp_eg_grids from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb troubleshooting.html for how you can resolve the problem.

I have my database folder that contains the .frm files.

I've been trying to use mysqlfrm to recover the table, as explained by this link: https://dba.stackexchange.com/questions/71596/restoring-mysql-tables-from-ibd-frm-and-mysqllogbin-files

However when I put the information into the mysqlfrm the results are show below:

1.mysqlfrm --server=root@localhost --port=445 --user=root C:/wamp/bin/mysql/mysql5.6.17/data/craigmedia/wp_eg_grids.frm > wp_eg-grids.txt


Source on localhost: ...connected

ERROR: Cannot read wp_eg_grids.txt. You must have read privileges to the file or path and it must exist. Skipping this argument.
ERROR: Cannot read .frm file from >.frm.

Execution of utility: 'mysqlfrm --server=root@localhost --port=445 --user=root C:/wamp/bin/mysql/mysql5.6.17/data/craigmedia/wp_eg_grids.frm > wp_eg-grids.txt' ended with return code '1' but no error message was streamed to the standard error, please review the output from its execution.

Then I tried this.

2. mysqlfrm --server=root@localhost:3306 c:/wamp/bin/mysql/mysql5.6.17/data/craigmedia/wp_eg_grids.frm --port=3307 --user=root

WARNING: Using a password on the command line interface can be insecure.

Source on localhost: ...connected.
Spawning server with --user=root.
Starting the spawned server on port 3307 ...

The console has detected that the utility 'mysqlfrm' ended with an error code. You can get more information about the error by running the console command 'show last error'.

Execution of utility: 'mysqlfrm --server=root@localhost:3306 c:/wamp/bin/mysql/mysql5.6.17/data/craigmedia/wp_eg_grids.frm --port=3307 --user=root' ended with return code '1' and with the following error message:
Traceback <most recent call last>:

File "G:\ade\build\sb_0-16088143-1438774726.78\Python-2.7.6-windows-x86-64bit\lib\site-packages\cx_Freeze\initscripts\Console.py", line 27, in <module>
File "scripts\mysqlfrm.py", line 422, in <module>
File ".\mysql\utilities\command\read_frm.py", line 439, in read_frm_files
File ".\mysql\utilities\command\read_frm.py", line 166, in _spawn_server
File ".\mysql\utilities\command\serverclone.py", line 180, in clone_server
File ".\mysql\utilities\command\tools.py", line 254, in get_mysqld_version

I0Error: [Errno 13] Permission denied: 'version_check'

At the moment I'm trying to access one .frm to test, which is the wp_eg_grids.frm and make it into a wp_eg_grids.txt. Can anyone notice what I'm doing wrong or know how to solve this please.

Answer 1

Try using the following syntax for mysqlfrm it had worked for me in a similar situation.

mysqlfrm --server=root:password@localhost:3306 c:/wamp/bin/mysql/mysql5.6.17/data/craigmedia/wp_eg_grids.frm > c:/wamp/bin/mysql/mysql5.6.17/data/craigmedia/wp_eg_grids.txt --diagnostic --port=3307 -vvv --user=root

Turn on diagnostic mode to read .frm files byte-by-byte and generate best-effort CREATE statement.

Answer 2

Your issue may be similar to mine: MySQL spawned server doesn't start.

The issue lies in MySQL starting with a temporary datadir, which by default is the current directory. In this directory, you'll have a new temporary directory (something like 62a77962-9a4b-49d0-b91a-a5e9eb71b894), with correct permissions.

• Linux: if you run as root (the user running mysqlfrm, not the user you start MySQL against), you most probably are in the /root/ directory, which mysql system user can't read (even if the mysql user is root).
• Windows: same issue I think, if you run as administrator, the MySQL user may not have the rights to read the current directory.

The solution is to move (using cd) in a MySQL readable directory, such as /tmp/ on Linux (with all the security issues associated with this world readable directory), or perhaps C:\ on Windows.

I found it by looking at MySQL log (Linux: /var/log/mysql/mysql.log) which stated:

/usr/sbin/mysqld: Can't change dir to '/root/aa9fe487-0c77-4bb4-a829-036fc9919558/' (Errcode: 13 - Permission denied)

Start command was:

/usr/sbin/mysqld --no-defaults --basedir=/usr --datadir=/root/aa9fe487-0c77-4bb4-a829-036fc9919558 --pid-file=/root/aa9fe487-0c77-4bb4-a829-036fc9919558/clone.pid --port=3310 --server-id=101 --socket=/root/aa9fe487-0c77-4bb4-a829-036fc9919558/mysql.sock --tmpdir=/root/aa9fe487-0c77-4bb4-a829-036fc9919558 --user=mysql

Moving in /tmp/ fixed the issue, and mysqlfrm worked as expected.

I would have used a tmpdir option if available, but it isn't, as stated in read_frm.py:

# Since Python libraries correctly restrict temporary folders to
# the user who runs the script and /tmp is protected on some
# platforms, we must create the folder in the current folder
   temp_datadir = os.path.join(os.getcwd(), str(uuid.uuid4()))
   os.mkdir(temp_datadir)

And MySQL doesn't have a real home... or you may not want to mess with the /var/lib/mysql/ directory!