MySQL Cookbook

Creating mysql Scripts from Previously Executed Queries

Problem

You want to reuse queries that were issued during an earlier mysql session.

Solution

Use a tee file from the earlier session, or look in mysql’s statement history file.

Discussion

One way to create a batch file is to enter your queries into the file from scratch with a text editor and hope that you don’t make any mistakes while typing them. But it’s often easier to use queries that you’ve already verified as correct. How? First, try out the queries “by hand” using mysql in interactive mode to make sure they work properly. Then, extract the queries from a record of your session to create the batch file. Two sources of information are particularly useful for creating SQL scripts:

• You can record all or parts of a mysql session by using the --tee command-line option or the \T command from within mysql. (See Recipe 1.30 for more information.)

• Under Unix, a second option is to use your history file. mysql maintains a record of your queries, which it stores in the file .mysql_history in your home directory.

A tee file session log has more context because it contains both query input and output, not just the text of the queries. This additional information can make it easier to locate the parts of the session you want. (Of course, you must also remove the extra stuff to create a batch file from the tee file.) Conversely, the history file is more concise. It contains only of the queries you issue, so there are fewer extraneous lines to delete to obtain the queries you want. Choose whichever source of information best suits your needs.