Skip navigation links
MySQL 3.23, 4.0, 4.1 Reference Manual :: 3 Tutorial :: 3.5 Using mysql in Batch Mode
3.6 Examples of Common Queries »
Section Navigation      [Toggle]

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

See Section 4.5.1.4, “Executing SQL Statements from a Text File”, for more information.


User Comments

Posted by Yurii Zborovs'kyi on March 6 2003 12:57am[Delete] [Edit]

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 :(

jz

Posted by Musba - on May 19 2003 11:41pm[Delete] [Edit]

Example of a Korn Shell Script

#!/bin/ksh
mysql --user=<user> --password=<password> -h <host> <<!!
SELECT VERSION(), CURRENT_DATE;
quit
!!

Posted by Donald Axel on February 18 2004 2:52pm[Delete] [Edit]

Lines beginning with two dashes are comment lines. Inside a shell script you will use #comment lines in the shell part and if you use a here-document you must use --comment lines inside the here document. Example:
===file petquery.sh===
#!/bin/sh
# This is a comment
mysql -t <<STOP
-- This is a comment inside an sql-command-stream.
use menagerie
select * from pet ;
\q
STOP
test $? = 0 && echo "Your batch job terminated gracefully"

===end-of-file petquery.sh===
DO NOT cut/paste the === lines.
I don't know how to make source listings inside the example.
donald_j_axel(at)get2net.dk

Posted by [name withheld] on September 8 2004 1:11pm[Delete] [Edit]

A more secure way to use the shell.

So that passwords are not embedded in the shell source file create a password file:

echo "batchpassword" > /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 the correct access rights to the database called "mydb".

tc

Posted by Senthil Nathan on October 29 2004 11:57pm[Delete] [Edit]

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 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.

Posted by Jamie Jones on November 22 2004 3:33am[Delete] [Edit]

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 permissions are set so that only the owner can read it

Posted by Frances D on April 29 2006 11:33pm[Delete] [Edit]

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).

Posted by Alexander Simakov on February 2 2007 10:52am[Delete] [Edit]

Hello!

Suppose you want to run script `test.sql' in batch mode.
The most straightforward way, as mentioned at the very
beginning, is to say:

shell> mysql < test.sql

In order to further reduce typing, one wish to invoke

shell> chmod a+x test.sql

and then run `test.sql' as a usual Unix script.
But it doesn`t work.

It is well known that so-called Sha-Bang (#!) symbol
is used in Unix world to specify where actual interpreter
lives. For example, #!/bin/bash

My objective is to create small and convenient wrapper
to run MySQL scripts in the same way as I run other scripts:
just typing `test.sql'.

Here is the program:

=[sql.c]===8<========================================================
/*
* MySQL Sha-Bang wrapper. Public domain.
* Alexander Simakov
*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

int main(int argc, char **argv) {
char buf[512];
int i;

buf[0] = 0;

for (i = 1; i < argc; i++) {
if (i == 1) {
strncat(buf, "mysql ", sizeof(buf) - strlen(buf) - 1);
}

if (i == argc - 1) {
strncat(buf, "< ", sizeof(buf) - strlen(buf) - 1);
}

strncat(buf, argv[i], sizeof(buf) - strlen(buf) - 1);

if (i != argc - 1) {
strncat(buf, " ", sizeof(buf) - strlen(buf) - 1);
}
}

return system(buf);
}
===========8<========================================================

Compile the program
shell> gcc -o sql sql.c

Copy program into the usual location
shell> cp sql /usr/bin/sql

Set execution flag on the script
shell> chmod 755 test.sql

Show script contents
shell> cat test.sql
#!/usr/bin/sql -t

use mysql;
select User,Host from user;

Enjoy!
shell> ./test.sql

+--------+-----------+
| User   | Host      |
+--------+-----------+
| root   | localhost |
| xander | localhost |
+--------+-----------+
P.S.
Note that in order to use batch mode efficiently you need
non-interactive authentification. The best way is per-user
configuration file: ~/.my.cnf

Create file and put a couple of strings:
[client]
password = yourpasswd

NB! Don`t forget to set proper permissions!
shell> chmod 400 ~/.my.cnf

Now mysql will use this password as you default password.

P.S.S.
You can also pass any mysql parmeters in the sha-bang line.
For example:
#!/usr/bin/sql -t
or
#!/usr/bin/sql -X
or even
#!/usr/bin/sql -u someuser -ppassword

That`s it!

---
There are 10 kinds of people: those who understand binary and those who don`t.

Posted by Richard Moor on April 30 2008 12:49am[Delete] [Edit]

re:Using mysql in Batch Mode - Windows XP - Vista:
If you need to specify connection parameters on the command line for using a text file use forward slashes
and print the word source, not the url of your file.
Example:mysql> source C:/inetpub/wwwroot/your folder/sql.txt; unfortunately the reference manual does
not allude to this.
rich: comrefhvac.com

Posted by David Goadby on August 6 2008 11:30am[Delete] [Edit]

Batches in Linux are ok but, running batches in Windows via a DOS box (cmd line) causes a lot of problems if other tasks are running.

I have a large file of SQL (1000 lines, 38Kb) that is generated nightly and run as a batch. For about 10 minutes the CPU utilisation is 100% and other programs slow to a crawl.

Sadly we have no nice for cmd.exe so cannot change it. We fixed the problem by using another program to execute the file as a series of SQL statements. As the program is a proper windows program (Delphi) then it yields from time to time and the CPU utilisation drops back. Ok, the SQL takes longer to run, but it run at 2am, so no one is waiting for it.

Posted by d994498 d994498 on April 11 2009 6:42am[Delete] [Edit]

on windows, there's difference in batch mode(see example):
C:\Documents and Settings\Administrator>mysql -u me -p enagerie < batch.sql
Enter password: *****
species
cat
dog
bird
snake
hamster

C:\Documents and Settings\Administrator>mysql -u me -p enagerie -e "source batch.sql"
Enter password: *****

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

C:\Documents and Settings\Administrator>more batch.sql
select distinct species from pet;

C:\Documents and Settings\Administrator>mysql -u me -p -e "select version()"
Enter password: *****
+------------------+
| version()        |
+------------------+
| 5.1.32-community |
+------------------+

Posted by marlon mendez on September 11 2009 1:02am[Delete] [Edit]

If you're trying to restore or create a database through mysql.exe using NSIS, this is the line you need

ExecWait 'cmd /C "C:\mysql.exe" -h localhost -u root -pMypassword < c:/MyFile.sql'

You got to put the 'cmd /C', without this it will not work. I've coped with grief


Posted by Eric Baker on November 13 2009 5:52am[Delete] [Edit]

Note that Alexander Simakov's example (above) of using "Sha-Bang" to execute sql as a script from the command line does not handle shell command-line expansion of filenames.

For example, the given "test.sql" file attempted to be run as so:

shell> ./t*t.sql

results in:

ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database './test.sql'

{Forward kudos to the caring soul who takes the time to provide a fix and delete this comment as the idea has merit}

Posted by Qing Li on March 26 2010 12:44pm[Delete] [Edit]

在Windows命令行下，输入以下命令
Type below two lines into Windows Command Line:

FTYPE sqlfile=C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe -u %2 -p -e "source %1"
ASSOC .sql=sqlfile

如果使用在批处理文件中，把%1、%2替换为%%1、%%2。
In .bat files, use %%1、%%2 instead of %1、%2

之后，在Windows命令行中，就可以以下格式直接运行.sql文件啦
Then, you can run .sql files directly by typing command in Windows Command Line using below format:

filename.sql username

例如：abc.sql root

会提示输入密码。

C:\WINDOWS\system32>test.sql root
Enter password: ******************

Add your own comment.