3.5. Using mysql in Batch Mode

In the previous sections, you used mysql interactively to enter queries and view the results. You can also run mysql in batch mode. To do this, put the commands you want to run in a file, then tell mysql to read its input from the file:

shell> mysql < batch-file 

If you are running mysql under Windows and have some special characters in the file that cause problems, you can do this:

C:\> mysql -e "source batch-file" 

If you need to specify connection parameters on the command line, the command might look like this:

shell> mysql -h host -u user -p < batch-file
Enter password: ******** 

When you use mysql this way, you are creating a script file, then executing the script.

If you want the script to continue even if some of the statements in it produce errors, you should use the --force command-line option.

Why use a script? Here are a few reasons:

• If you run a query repeatedly (say, every day or every week), making it a script allows you to avoid retyping it each time you execute it.

• You can generate new queries from existing ones that are similar by copying and editing script files.

• Batch mode can also be useful while you're developing a query, particularly for multiple-line commands or multiple-statement sequences of commands. If you make a mistake, you don't have to retype everything. Just edit your script to correct the error, then tell mysql to execute it again.

• If you have a query that produces a lot of output, you can run the output through a pager rather than watching it scroll off the top of your screen:

  shell> mysql < batch-file | more 

• You can catch the output in a file for further processing:

  shell> mysql < batch-file > mysql.out 

• You can distribute your script to other people so that they can also run the commands.

• Some situations do not allow for interactive use, for example, when you run a query from a cron job. In this case, you must use batch mode.

The default output format is different (more concise) when you run mysql in batch mode than when you use it interactively. For example, the output of SELECT DISTINCT species FROM pet looks like this when mysql is run interactively:

+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

In batch mode, the output looks like this instead:

species
bird
cat
dog
hamster
snake

If you want to get the interactive output format in batch mode, use mysql -t. To echo to the output the commands that are executed, use mysql -vvv.

You can also use scripts from the mysql prompt by using the source command or \. command:

mysql> source filename;
mysql> \. filename



How to measure total batch running time for several SQLs:

# at start of your script file
SET @start=UNIX_TIMESTAMP();

# great job
...
...
...

# at bottom of your script file
SET
 @s=@seconds:=UNIX_TIMESTAMP()-@start,
 @d=TRUNCATE(@s/86400,0), @s=MOD(@s,86400),
 @h=TRUNCATE(@s/3600,0), @s=MOD(@s,3600),
 @m=TRUNCATE(@s/60,0), @s=MOD(@s,60),
 @day=IF(@d>0,CONCAT(@d,' day'),''),
 @hour=IF(@d+@h>0,CONCAT(IF(@d>0,LPAD(@h,2,'0'),@h),' hour'),''),
 @min=IF(@d+@h+@m>0,CONCAT(IF(@d+@h>0,LPAD(@m,2,'0'),@m),' min.'),''),
 @sec=CONCAT(IF(@d+@h+@m>0,LPAD(@s,2,'0'),@s),' sec.');

SELECT
 CONCAT(@seconds,' sec.') AS seconds,
 CONCAT_WS(' ',@day,@hour,@min,@sec) AS elapsed;

# enjoy :)

p.s. Tested & works
p.p.s. No fractions of seconds :(

Example of a Korn Shell Script

#!/bin/ksh
mysql --user= --password= -h  < /etc/security/mysqlpassword
chmod 200 /etc/security/mysqlpassword

Then in your script:

echo "update tablex set x=1 where a=2;" | mysql mydb --user=batchdb --password=`cat /etc/security/mysqlpassword`

This
assumes you have created a user called "batchdb" with that password and

When using mysql in batch mode you can use pipes to write an
interactive but pre-scripted shell script. Be aware that you need to
use the "-n" command line option to flush the buffer otherwise your

N.B. On most Unix systems, by placing the password in the command line
with --password (even the above method for using a password file) you
are making the password visible to local users, who can see the command
string with a "ps" or "w" command.

Whilst some systems can be
set to block this, and others would let you wrap the command in
something that would overwrite what users could see as your command,
the best way to do any automations like this is to create a specific
unix user for the job (or use a user that is already secure) and place
the password in the .my.cnf file for that user - making sure the

For newbies like me:  This exact command allowed me to run a script from outside MySQL (using the DOS command line in Win98):

C:\WINDOWS\Desktop>c:\mysql\bin\mysql -u root -p < "c:\mysql\scripts\20060416_ShowInnodbstatusscript.txt" | more  Note: My text file had two commands:      Use db_name;     Show InnoDB Status;  FYI: I had been plagued by a foreign key error, but was unable figure out how to see the result of my Show Innodb Status command due to my 50-line DOS screen limitation. To get around this problem I    a) created the simple script shown above   b) created a foreign key error while logged into my MySQL user account, then   c) opened an additional DOS window to execute the command shown above.  Running the command outside of MySQL essentially creates a way to view command results one page at a time for folks administering MySQL at the command   line (using Windows). permissions are set so that only the owner can read it read will hang. Here is a code sample in ksh:  ------------------------------------------------- #!/bin/ksh mysql -u username -ppassword -D dbname -ss -n -q |& print -p -- "select count(*) from some_table;" read -p get_row_count1 print -p -- "select count(*) from some_other_table;" read -p get_row_count2 print -p exit ; # echo $get_row_count1 echo $get_row_count2 # exit ------------------------------------------------- (The -q option is optional)  Note:  If you dislike using "-n" then make sure all your read statements are after the exit. the correct access rights to the database called "mydb". Example: ===file petquery.sh=== #!/bin/sh #  This is a comment mysql -t <