Batch scripts

from the Artful MySQL Tips List

To run a batch script from the operating system command line, type something like this from the command prompt:

mysql -uusername -ppassword -e "set @1:='some value'; source run.sql;"

This can be expanded as you like. Alternatively, you can put everything in run.sql. This needs only: (i) the mysql program in the current folder, or in the path (ii) run.sql in the current folder Pass params to a MySQL client script from a shell script:

echo SELECT @foo > /tmp/myscript.sql
echo SET @foo := 'bar'; > /tmp/setvar.sql
echo SOURCE /tmp/setvar.sql; SOURCE /tmp/myscript.sql; > /tmp/turnkey.sql
mysql -uUSR -pPWD < /tmp/turnkey.sql
rm /tmp/setvar.sql /tmp/turnkey.sql

In Linux, pass mysql command resultss to a variable:

myvar="$(mysql -uUSR -pPWD --execute='show schemas')"
echo "${myvar}"

Be careful with quotes; the entire $(...) must be enclosed in quotes, and so must be the MySQL command itself, so use different quote chars for the two. Under Windows, the MySQL client script interface can be finicky. Substitute your MySQL username & password for USR and PWD, then this example works:

if not exist c:tmp md c:tmp
echo show tables from test > c:tmptest.sql
"%programfiles%MySQLMySQL Server 5.6binmysql.exe " -uUSR -pPWD -e "source c:tmp/test.sql"

For example to parameterise Load Data Infile:

REM Batch file mysqlLDI.BAT to parameterise MySQL LOAD DATA INFILE command:
REM Call with %1 = input filename, %2 = db.table
echo LOAD DATA INFILE '%1' INTO TABLE %2 LINES TERMINATED BY "rn" > tmpdoit.sql
mysql -uUSR -pPWD < /tmp/doit.sql

To redirect output including errors to a file, tell the OS to redirect stderr to stdout ...

mysql -uUSR -pPWD < /tmp/doit.sql > /tmp/doit.txt 2>&1

To redirect error messages only, use 2>> %logfile% instead.

Last updated 18 Nov 2024

Return to the Artful MySQL Tips page