Retrieve lost file using Vi in MySQL

Question

I would like to know how to retrieve a file using Vi in MySQL. I logged in using:

mysql -uuser -p -hserver -A database

Then I do:

\e

The editor opens and I type my query of 200 lines, then I :wq and \G (if I save the file it says: /tmp/sql9SbYQZ saved) and I see the result.

Now, if I make a mistake or run a different query and I try to type \e again, the query is lost.

ll /tmp/sql9SbYQZ
ls: /tmp/sql9SbYQZ: No such file or directory

Is there a way to retrieve the lost file?

Answer 1

Here's what I added to my .vimrc in order to save the current query in case i made a mistake.

nmap <F7> :w! /tmp/query.sql\| wq!<CR>

This will create a map to the F7 key (you can change it of course). So every time you open a file either using edit or \e, you change it use the F7 key.

This will save a backup of your current query to /tmp/query.sql and then save and close the temporary file. This way, if you make a mistake, you just re-open the backup file and try again.

Here's also a link you might like: http://vim.wikia.com/wiki/Open_the_last_edited_file

Answer 2

With the vi/m editor used with mysql, crontab, and many others, the work is done in a tmp file, as you see from your messages.

Edit (Big doah!, remove cruft about ls -l /tmp/..., you already did that!)

In the future the solution is to tell vim to w the buffer to a file name of your chosing, i.e.

 w! /home/you/scripts/mysql2.sql

Then close the editor with

 q

Note you may not need the ! after w.

I hope this helps.

Answer 3

Here is something you can try:

In Linux, do the following

$ cd
$ cp .mysql_history mystuff.txt
$ vi mystuff.txt

You should see the file .mysql_history. The mysql client records all queries and commands executed. Hopefully, your query is in there.

Give it a Try !!!