Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


MySQL 5.6 リファレンスマニュアル  /  チュートリアル  /  バッチモードでの MySQL の使用

3.5 バッチモードでの MySQL の使用

前のセクションでは、mysql をインタラクティブに使用してクエリーを入力し、結果を表示しました。mysql をバッチモードで実行することもできます。そのためには、実行するコマンドをファイルに記述し、そのファイルから入力を読み取るように mysql に指示します。

shell> mysql < batch-file

mysql を Windows で実行する場合に、ファイル内の一部の特殊文字によって問題が発生するときは、次のように実行できます。

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

コマンド行で接続パラメータを指定する必要がある場合、コマンドは次のようになります。

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

この方法で mysql を使用する場合は、スクリプトファイルを作成してから、そのスクリプトを実行することになります。

スクリプト内の一部のステートメントでエラーが発生してもスクリプトを続行する場合は、--force コマンド行オプションを使用します。

なぜスクリプトを使用するのでしょうか。いくつかの理由を次に示します。

  • クエリーを繰り返し実行する場合 (毎日、毎週など)、スクリプトにすると、実行するたびに入力し直す必要がなくなります。

  • 既存のクエリーのスクリプトファイルをコピーして編集することによって、類似の新しいクエリーを作成できます。

  • バッチモードはクエリーの開発時にも役立ちます。特に、複数行にわたるコマンドまたは複数ステートメントによるコマンドシーケンスを使用する場合に便利です。間違いがあっても、すべてを入力し直す必要はありません。スクリプトを編集して間違いを修正してから、mysql で再度実行するだけで済みます。

  • 多量の出力を生成するクエリーの場合、画面でスクロールアップする出力を見る代わりに、pager を介して出力できます。

    shell> mysql < batch-file | more
    
  • あとで処理できるように出力をファイルに取り込むことができます。

    shell> mysql < batch-file > mysql.out
    
  • スクリプトを配布すると、ほかのユーザーも同じコマンドを実行できます。

  • cron ジョブからクエリーを実行する場合など、インタラクティブには使用できないことがあります。この場合はバッチモードを使用する必要があります。

mysql をバッチモードで実行したときのデフォルトの出力形式は、インタラクティブに使用した場合とは異なり、より簡潔になります。たとえば、mysql をインタラクティブに実行すると、SELECT DISTINCT species FROM pet の出力は次のようになります。

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

これに対し、バッチモードの出力は次のようになります。

species
bird
cat
dog
hamster
snake

バッチモードで、インタラクティブ出力形式のデータを取得するには、mysql -t を使用します。実行したコマンドを出力にエコーするには、mysql -vvv を使用します。

source コマンドまたは \. コマンドを使用すると、mysql プロンプトからでもスクリプトを使用できます。

mysql> source filename;
mysql> \. filename

詳細については、セクション4.5.1.5「テキストファイルから SQL ステートメントを実行する」を参照してください。


User Comments
  Posted by Yurii Zborovs'kyi on March 6, 2003
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
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
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 on September 8, 2004
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
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
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
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
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
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
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
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
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
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
在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: ******************
  Posted by Richard Otter on January 25, 2011
When runnning on Windows and using the
-e "source filePath"
method ...

NOTE:
* filePath seems to need forward slash directory separator chars
* spaces in the filePath seem OK if the entire option is in quotes, as shown above
* not OK are any characters not included in the current non-unicode code page.
  Posted by Robert Arkenin on July 31, 2012
"If you run a query repeatedly (say, every day or every week), making it a script enables you to avoid retyping it each time you execute it."

That's what the event scheduler is for.
  Posted by Attila Pulay on May 20, 2014
An another single-line example: @WinSrv 2008 R2:
It shows up a window, ask for a password, do the sql command (after -e "...") and wait for any key.

C:\Windows\System32\cmd.exe /c ""C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysql.exe" -u {dbUser} -p -D {dbName} -e "select * FROM {TABLE} WHERE {CLMN1} = 2014 and {CLMN2} >= '05';" & pause"
Sign Up Login You must be logged in to post a comment.