MySQL Cookbook, 2nd Edition

Creating mysql Scripts from Previously Executed Statements

Problem

You want to reuse statements that you 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 statements 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 statements that you’ve already verified as correct. How? First, try the statements “by hand” using mysql in interactive mode to make sure they work properly. Then extract the statements 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 option or the \T command from within mysql. (See Logging Interactive mysql Sessions.)

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

A tee file session log has more context because it contains statement input and output both, not just the text of the statements. 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 the statements you issue, so there are ...